Keep records together when you sort and summarize

A workplace spreadsheet often holds a list, not just a few numbers. Each row is one record, such as one customer, order, or sale. Each column gives a different fact about that record. When the data moves, those facts must stay together.

Sort the full table, not one column

To show the highest sale first, sort the Sales column in descending order. The expected rows are Chen / East / 300, Jordan / West / 180, and Maya / West / 120. If you sort only the Sales cells, the names stay where they were and the table falsely says Maya sold 300. Select the whole table or use a table-aware sort command so every row moves together.

The top row is a header. It names the columns and should stay at the top. In a sort dialog, check the setting that says your data has a header row. Then choose the Sales column and “largest to smallest” or “descending.” Before trusting the result, check at least one row you know.

Count or add only matching records

To count the West records, a formula such as =COUNTIF(B2:B4,"West") returns 2. To add the West sales, use =SUMIF(B2:B4,"West",C2:C4), which returns 300. The first range contains the labels to inspect; the last range contains the numbers to add. The two ranges should cover the same rows.

What a pivot table does

A pivot table creates a new summary from the original list. In this example, choose Region for rows and Sales for values with SUM as the calculation. The summary has East = 300 and West = 300. The original three records remain available so you can check how the totals were formed. A pivot is especially useful when a long list has many repeated categories.

A pivot table, step by step

The three-row example above is small enough to total in your head. Pivot tables earn their keep on longer lists. Here is a hardware store’s sales by region and department:

1. Click any cell in the list. Then choose Insert → PivotTable in Excel or Insert → Pivot table in Google Sheets. Accept the suggested range, A1:C7, and put the pivot on a new sheet.
2. Rows: add Region. Each region now appears once, however many times it is listed.
3. Columns: add Department.
4. Values: add Sales and make sure it says SUM (not COUNT).

North Hardware shows 500 because the pivot combined rows 2 and 6 (400 + 100). The grand total, 1400, matches the sum of the original Sales column. Run that check every time.

Questions first-time users ask

Does a pivot table change my original data?

No. It reads the list and builds a separate summary. You can delete the pivot and the list is untouched.

Why does my pivot show a count instead of a total?

Excel switches to COUNT when the value column contains blanks or text. Check for numbers stored as text, then change the value setting back to SUM.

I changed a number. Why didn’t the pivot update?

In Excel, pivot tables do not recalculate on their own. Right-click the pivot and choose Refresh. Google Sheets updates pivot tables automatically. If you added new rows below the list, you may also need to widen the source range.

Should I use a pivot table or SUMIF?

Use SUMIF when you need one or two specific totals inside an existing report. Use a pivot when you want a full breakdown of every category and do not want to write a formula for each one. Both should give the same numbers, which also makes them a good check on each other.

Try a small office task

Create three columns labeled Employee, Region, and Sales. Enter the records above. Sort by Sales from high to low while keeping the rows together. Then count the West records and add their sales. If your answers are 2 and 300, you are ready for the guided sorting and pivot lessons in FreeJobTools.

Practice in the trainer: Leaderboard Data Sorting · Regional Sales Pivot Table · Summarize Department Spend.

Related guides: sorting without mixing up rows · counting with COUNTIF and SUMIF · looking up values · all guides