sodcalculator

10 Tech How-To

How to Summarize Excel Data with PivotTable Fields

Guide 10-43145 · Updated · Step by step

PivotTables are Excel’s most powerful tool for summarizing large datasets without writing a single formula. You drag fields into specific areas—Rows, Columns, Values, and Filters—and Excel instantly groups, counts, and totals your data. This guide walks you through creating your first PivotTable and using its fields to answer questions like “What were total sales by region?” or “How many units did each product sell last quarter?”

What You’ll Need

  • A computer with Microsoft Excel (2016 or later; the steps are nearly identical in Excel for Microsoft 365, Excel for Mac, and Google Sheets—though menus differ slightly).
  • A dataset formatted as a table or a range with headers in the first row. Each column should contain one type of data (e.g., dates, text, numbers) and each row should be one record (e.g., one sale, one order).
  • No blank rows or blank columns within the data range—Excel needs a continuous block to detect it correctly.

Step-by-Step

  1. Prepare your data. Ensure every column has a unique header (e.g., “Date”, “Region”, “Product”, “Units Sold”). Remove any blank rows or columns inside the range. If you have a table, you can skip this step—Excel tables are PivotTable-ready.
  2. Select any cell inside your data range. Click once on any cell that contains data. This tells Excel where your data lives.
  3. Insert the PivotTable. Go to the Insert tab on the ribbon and click PivotTable. In the dialog box, Excel will automatically select your data range—verify it’s correct. Choose whether to place the PivotTable in a New Worksheet (recommended) or an existing one, then click OK.
  4. Open the PivotTable Fields pane. After inserting, you’ll see an empty PivotTable on the left and a PivotTable Fields pane on the right. The pane shows a list of all your column headers (these are your fields) and four drop zones below: Filters, Columns, Rows, and Values.
  5. Add a field to Rows. Drag a categorical field (like “Region” or “Product”) into the Rows area. This creates one row for each unique value in that field. For example, dragging “Region” gives you a row for North, South, East, and West.
  6. Add a numeric field to Values. Drag a numeric field (like “Units Sold” or “Revenue”) into the Values area. By default, Excel sums the numbers. The PivotTable now shows the total for each row category. For instance, total units sold per region.
  7. Change the summary function if needed. If you want an average, count, or max instead of a sum, click the field in the Values area (it appears as “Sum of Units Sold”), choose Value Field Settings, and select a different function like Average or Count. Click OK.
  8. Add a field to Columns. To compare categories side-by-side, drag another field (like “Quarter”) into the Columns area. Now each column shows a different quarter, and the intersection shows the value for that region and quarter.
  9. Add a field to Filters. Drag a field (like “Year”) into the Filters area. This adds a drop-down above the PivotTable. Select a specific year to filter the entire summary. You can also check “(All)” to show everything.
  10. Rearrange fields. Drag fields between areas to change the layout. For example, move “Region” from Rows to Columns to flip the table orientation. Excel updates instantly.
  11. Refresh when data changes. If you edit or add data in the source range, right-click the PivotTable and choose Refresh (or press Alt+F5). This recalculates the summary.
  12. Format the result. Use the Design tab to apply a table style, or right-click numbers and choose Number Format to show currency or percentages.

Common Mistakes

  • Dragging text fields to Values. If you drag a text field like “Product Name” to Values, Excel will count it by default (Count of Product Name). That’s useful for counting occurrences, but if you meant to sum numbers, you dragged the wrong field.
  • Forgetting to refresh. PivotTables don’t update automatically when you change the source data. If your numbers look stale, refresh.
  • Including blank rows/columns. If your data range has a blank row or column, Excel may not detect the full range, leading to missing data. Keep the range contiguous.
  • Not using a table. If you add new rows below your data, a PivotTable based on a range won’t include them unless you refresh and expand the range. Converting your data to a Table (Ctrl+T) makes the PivotTable automatically include new rows on refresh.
  • Misunderstanding the default sum. Excel sums numeric fields by default, but if your numbers are formatted as text, it will count them instead. Ensure numeric columns contain numbers, not text.

Tips

  • Use the Recommended PivotTables option. On the Insert tab, click Recommended PivotTables to see pre-built summaries Excel suggests based on your data. It’s a great starting point.
  • Double-click any value to drill down and see the underlying rows that make up that number. This is a quick way to audit your data.
  • Group dates. If you have a date field, drag it to Rows, then right-click any date and choose Group. You can group by month, quarter, or year—perfect for time-based summaries.
  • Sort the summary. Click the drop-down arrow in the Row Labels header to sort by value (e.g., largest to smallest) without changing the source data.
  • Add multiple fields to Values. Drag “Units Sold” and “Revenue” both to Values to see counts and sums side by side. Right-click and choose Value Field Settings to customize each.
  • Use the PivotTable Options. Right-click the PivotTable, choose PivotTable Options, and uncheck “Show grand totals for rows/columns” if you don’t want them.

Troubleshooting

  • The PivotTable Fields pane is empty. This happens if you clicked outside the PivotTable. Click anywhere inside the PivotTable to bring the pane back. If it still doesn’t show, go to PivotTable Analyze > Field List.
  • Excel says “PivotTable field name is not valid.” This usually means your source data has blank column headers or merged cells. Fix the headers—make sure every column has a unique, non-blank name.
  • Your numbers are showing as counts instead of sums. Check if the column contains text (e.g., “$10” instead of 10). Select the column and convert to numbers using Text to Columns or the Convert to Number warning.
  • New rows you added aren’t showing up. Refresh the PivotTable. If you’re using a range (not a table), you may need to change the source range: PivotTable Analyze > Change Data Source.
  • The PivotTable is too slow. If you have hundreds of thousands of rows, consider using Power Pivot (available in Excel 2016+ Pro) or summarizing the data with formulas before building the PivotTable.
  • You accidentally deleted the PivotTable. No problem—just re-insert it from the Insert tab. The source data is untouched.

Frequently Asked Questions

Why does my PivotTable show “Count of” instead of “Sum of”?

Excel defaults to Sum for numeric fields, but if the column contains any text or blank cells, it switches to Count. Clean the data so all values are numbers, or manually change the value field setting to Sum.

How do I add a calculated field (e.g., profit = revenue – cost)?

Click anywhere in the PivotTable, go to PivotTable Analyze > Fields, Items & Sets > Calculated Field. Give it a name, enter a formula using the existing fields (e.g., =Revenue – Cost), and click Add. It appears as a new field in the Values area.

Can I use a PivotTable to count unique values?

Yes. Drag the field you want to count to Values, then change the value field settings to Distinct Count (available in Excel 2013 and later). This counts each unique item once, not every occurrence.

How do I remove grand totals?

Right-click the PivotTable, choose PivotTable Options, go to the Totals & Filters tab, and uncheck “Show grand totals for rows” and “Show grand totals for columns”.

Why is my date field not grouping by month?

Dates must be recognized as dates, not text. Select the date column, go to Data > Text to Columns > Delimited > Next > Next > choose Date and finish. Then refresh the PivotTable and try grouping again.

Can I create a PivotChart from my PivotTable?

Yes. Click anywhere in the PivotTable, go to PivotTable Analyze > PivotChart, and choose a chart type. The chart updates automatically when you change the PivotTable fields.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →