sodcalculator

10 Tech How-To

Pivot Tables on Mac: Step-by-Step Guide

Guide 10-52415 · Updated · Step by step

Pivot tables let you summarize, sort, and analyze large lists of data in Excel for Mac without writing any formulas. You select the rows, columns, and values you want to see, and Excel builds the summary table for you. This guide walks you through creating a pivot table from scratch, adjusting it, and fixing common issues.

What You’ll Need

  • Microsoft Excel for Mac (any recent version, including Office 365 or Excel 2021/2024)
  • A dataset in a single worksheet with column headers in the first row (e.g., Date, Region, Sales)
  • No blank rows or columns within the data range
  • Each column should contain one type of data (numbers in one column, text in another)

Step-by-Step

  1. Prepare your data. Make sure the first row has unique, descriptive headers. Remove any blank rows or columns inside the data. If you have multiple sheets, combine the data into one sheet first.
  2. Select the data range. Click any cell inside your data table. Excel will automatically detect the range when you create the pivot table.
  3. Insert the pivot table. Go to the menu bar and click Insert > PivotTable. A dialog box appears. Confirm the range is correct (it usually is). Choose whether to place the pivot table in a New Worksheet or an Existing Worksheet. Click OK.
  4. Understand the PivotTable Builder. On the right side of the screen, you’ll see the PivotTable Builder panel. It lists all your column headers as fields. Below that are four areas: Filters, Columns, Rows, and Values.
  5. Add fields to Rows. Drag a field (like “Region”) into the Rows area. Each unique value in that field becomes a row in the pivot table.
  6. Add fields to Values. Drag a numeric field (like “Sales”) into the Values area. By default, Excel sums the numbers. If you want a count, average, or other calculation, click the field in the Values area, choose Summarize by, and select the function (e.g., Count, Average, Max).
  7. Add fields to Columns (optional). Drag a field (like “Year”) into the Columns area. This creates columns for each unique value in that field, giving you a cross-tabulation.
  8. Add filters (optional). Drag a field (like “Product”) into the Filters area. This adds a dropdown above the pivot table that lets you show only certain products.
  9. Rearrange fields. You can drag fields between areas anytime. The pivot table updates instantly. To remove a field, drag it back to the field list.
  10. Refresh data. If you change the source data, click anywhere in the pivot table, then go to PivotTable tab (or right-click) and choose Refresh. For automatic refresh, set a timer via Data > Queries & Connections (only in newer versions).
  11. Format and style. Click the pivot table, then go to the PivotTable tab in the ribbon. Choose a style from the gallery, or manually format cells using the Home tab.
  12. Group dates or numbers. Right-click a date field in the Rows area, choose Group, and select months, quarters, or years. For numeric fields, you can group by ranges (e.g., 0-100, 100-200).
  13. Sort and filter within the pivot. Click the dropdown arrow next to a row or column label to sort A-Z or Z-A, or to filter out specific items.

Common Mistakes

  • Blank rows or columns in the source data – Excel may not detect the full range, leading to missing data.
  • No headers – Without unique headers in the first row, Excel treats the first row as data and field names become “Column1”, etc.
  • Dragging a text field into Values – Excel will count it, but if you wanted a sum, you need a numeric field.
  • Forgetting to refresh – Pivot tables are not live; they store a snapshot of the data at creation time.
  • Using the wrong summarization – Default is Sum, but if you have text or need an average, change the summarize by setting.
  • Adding the same field twice – You might accidentally drag “Sales” into both Rows and Values, which creates a huge table.

Tips

  • Use the “Show Values As” feature – Click a field in Values, select Show Values As, and choose % of Grand Total, Running Total, or Difference From. This is great for percentages.
  • Double-click a value – Double-clicking any number in the pivot table creates a new sheet with the underlying detail rows. This is perfect for auditing.
  • Turn off GETPIVOTDATA – When you reference a pivot cell in a formula, Excel inserts GETPIVOTDATA. If you prefer normal cell references, go to Excel menu > Preferences > Tables & Filters and uncheck “Use GetPivotData functions”.
  • Use the PivotTable tab – The ribbon has a dedicated tab with tools for grouping, refreshing, and changing the data source.
  • Keyboard shortcut – Press Command + Option + P to quickly insert a pivot table (in newer versions).
  • Keep source data clean – Use Excel Tables (Insert > Table) for your data. Then when you add rows, the pivot table’s range automatically expands.

Troubleshooting

  • Pivot table is blank – Check that you dragged fields into the Rows and Values areas. If you only added a field to Filters, nothing shows. Also, ensure the source data isn’t empty.
  • Numbers show as text – If your source column has numbers stored as text (green triangle in the corner), the pivot table will count them instead of summing. Select the column in the source, go to Data > Text to Columns, and finish to convert to numbers.
  • Field list is missing – Click anywhere in the pivot table. The PivotTable Builder should reappear. If not, go to PivotTable tab and click Field List.
  • Cannot group dates – This usually happens when the date column has blank cells or text. Fill in all blanks with a date, or remove rows with missing dates.
  • Data range does not include new rows – If you added data after creating the pivot, go to PivotTable tab > Change Data Source and update the range. Using an Excel Table prevents this.
  • Refresh is grayed out – This happens if the pivot table is not selected. Click inside the pivot table first.

Frequently Asked Questions

How do I create a pivot table on a Mac with the old Excel 2011?

In Excel 2011, go to Data > PivotTable. The interface is similar, but the field list is on the right. The steps are the same: drag fields into rows, columns, and values.

Can I create a pivot table from multiple sheets?

Yes, but you need to combine the data first. Use Data > Get Data (or Power Query) to merge tables, or copy and paste all data into one sheet. A pivot table can only use a single contiguous range.

Why is my pivot table showing “Count of” instead of “Sum of”?

This happens when the column contains text or blank cells. Excel defaults to Count for non-numeric data. Check the source column for any text entries or empty cells and fix them.

How do I remove a pivot table?

Click anywhere in the pivot table, then press Delete on your keyboard. If the pivot is on a separate sheet, you can delete the entire sheet by right-clicking the sheet tab and choosing Delete.

Can I use a pivot table to create a chart?

Yes. Click anywhere in the pivot table, then go to Insert > Chart. Excel creates a pivot chart that updates when you refresh or change the pivot table.

How do I change the number format (e.g., currency) in a pivot table?

Right-click a value cell in the pivot table, choose Number Format, and select the format you want. This applies to all cells in that field.

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 →