Complete Guide to Pivot Tables in Excel for Mac

Quick Answer: Click a cell in your data, choose Insert ▸ PivotTable, confirm the range, pick a new or existing worksheet and click OK. Then tick or drag fields into Rows, Columns, Values and Filters. Microsoft's Mac steps list no single-key shortcut for inserting a PivotTable, and Windows Alt sequences such as Alt, N, V did not run end to end in our Excel for Mac tests. Disclosure: we make Mac Excel Shortcuts app.

How do I create a PivotTable in Excel for Mac?

Select a cell in the data, choose Insert then PivotTable, confirm the range, choose a new or existing worksheet and click OK. Excel adds a blank PivotTable and opens the PivotTable Fields list.

  1. Click any cell inside your data range or Excel Table.

  2. Choose Insert ▸ PivotTable, from the Ribbon or the menu bar.

  3. In the Create PivotTable dialog, confirm the range or table name.

  4. Choose New Worksheet or Existing Worksheet; for an existing sheet, click the cell where the PivotTable should start.

  5. Click OK. A blank PivotTable and the PivotTable Fields list appear.

The menu route and the Ribbon button open the same dialog, which helps when the Ribbon is collapsed on a small MacBook display. These steps follow Microsoft's Create a PivotTable page for Excel on Mac.

What should my data look like before I build a PivotTable?

Microsoft asks for one header row, a unique non-blank header on every column, no blank rows or columns, and consistent data types within each column. Excel Tables also grow as you add rows.

  • One header row. Microsoft says to avoid double header rows and merged cells.

  • Unique headers. Every column needs a non-blank name, because fields are built from them.

  • No blank rows or columns. A gap can cut off the range Excel detects, so check the range in the dialog.

  • One type per column. Do not mix dates and text in the same column.

  • Format as a table. Use Insert ▸ Table so new rows join the source data automatically.

Can Excel suggest a PivotTable for me?

Yes. Choose Insert then Recommended PivotTable, pick the suggested layout that looks closest to what you want, and click OK. Excel builds it on a new sheet, which you can then rearrange.

Recommended PivotTable is a quick way to see what Excel guesses from your column types. Treat it as a starting layout and check the calculation in each value, because Excel picks Sum or Count from the data it sees.

How do I add fields and change what a PivotTable calculates?

Tick a field in the list or drag it into Rows, Columns, Values or Filters. Values default to Sum for numbers and Count for text. Open Field Settings to change the calculation.

Schematic of the four PivotTable areas: Filters, Columns, Rows and Values, with an example field for each.
Schematic of the four PivotTable areas. Not a screenshot.
  1. Tick a field to add it. Excel puts text fields in Rows, date fields in Columns and numbers in Values.

  2. Drag a field between areas to rearrange it, or tick it off to remove it.

  3. Click the arrow next to a field in Values and choose Field Settings.

  4. Use Summarize by for Sum, Count, Average and others, and Show data as for percentages.

  5. To show one value two ways, drag the field into Values twice and set each copy separately.

Worked example: take a CRM export with Date, Sales Rep, Region, Product, Units Sold and Revenue. Put Region in Rows, Product in Columns and Revenue in Values, then check that Values says Sum. Add Date to Filters to narrow to one quarter. The columns here are illustrative, not data we measured.

Why does my PivotTable count instead of sum?

Excel counts when the field holds text or when a numeric column contains blanks or text-formatted numbers. Convert the column to numbers, refresh the PivotTable, and check Summarize by in Field Settings.

Numbers stored as text are common in CSV imports and show a small green triangle in the cell corner. Fix the source column first, then refresh. Changing Summarize by to Sum alone does not help if Excel still reads the cells as text.

How do I refresh a PivotTable after the data changes?

A PivotTable does not update itself when source cells change. Right-click inside it and choose Refresh, or use PivotTable Analyze, the arrow under Refresh, then Refresh All for every PivotTable.

Microsoft documents both routes and lists no keyboard shortcut for either on its Mac page. If the source is an Excel Table, new rows are included in the next refresh.

How do I group dates or numbers in a PivotTable?

Right-click a date or a selection of items in the PivotTable and choose Group, then pick the unit such as months or years. The exact menu labels can vary by Excel build.

We could not open Microsoft's group and ungroup page, so this section gives the route without a keyboard shortcut. Older versions of this guide listed a Shift-Option-Arrow shortcut, which we could not confirm and removed.

Do slicers, calculated fields and the Data Model work on a Mac?

Basic PivotTables and Recommended PivotTable are documented for Excel on Mac. Slicers on ordinary PivotTables are reported to work, while Power Pivot and the Data Model are reported unavailable. Check each on your build.

PivotTable features on Excel for Mac
FeatureStatus on Excel for MacSource
Create a PivotTable (Insert, PivotTable)DocumentedMicrosoft support page
Recommended PivotTableDocumentedMicrosoft support page
Field Settings, Summarize by, Show data asDocumentedMicrosoft support page
Slicers on ordinary PivotTablesReported to workCommunity forum reports; not tested by us
Power Pivot and the Data ModelReported unavailableMicrosoft Q&A community answer, not an official statement
Calculated fieldsNot confirmedNo clear source found
Group dates and numbersNot confirmed by usMicrosoft group page could not be opened

A PivotTable built on Windows with the Data Model can fail to appear as a PivotTable on a Mac, according to a Microsoft Tech Community thread. The location of Power Query and related tools is covered in Where Is It in Excel for Mac? PivotTable, Data Analysis, Solver, Power Query and the Developer Tab.

What are the keyboard shortcuts for PivotTables on a Mac?

Microsoft's Mac PivotTable page lists none for inserting or refreshing one. The Windows Alt, N, V sequence has no working Mac route in our tests, so use the Insert menu or a custom shortcut.

Microsoft documents KeyTips for Excel for Mac from version 16.89, off by default. In our tests on macOS 26.5, KeyTips did not work on Excel 16.114.1, and on earlier builds they opened a dialog but a sequence could not be finished from the keyboard. Results may differ on your build.

Excel for Mac also has a Tools ▸ Customize Keyboard dialog for assigning shortcuts to commands. We did not test assigning one to the PivotTable command, so treat that as an option to try, not a confirmed fix.

How do Windows and Mac PivotTable menus differ?

The PivotTable concepts are the same on both platforms, but several labels differ. Windows users meet Value Field Settings and Summarize Values By, which the Mac calls Field Settings and Summarize by.

PivotTable labels on Windows and Mac
TaskWindows labelExcel for Mac label
Change the calculationValue Field SettingsField Settings
Pick the calculation typeSummarize Values BySummarize by
Open the Create PivotTable dialogAlt, N, VInsert, PivotTable (menu or Ribbon)
Find and ReplaceCtrl+HEdit menu

How does Mac Excel Shortcuts app help with PivotTables?

Mac Excel Shortcuts app, which we make, keeps some Windows shortcut habits working on a Mac, such as Alt, E, S, V on Excel 16.114.1 in our tests. PivotTable insert sequences are also tested on MacBook Pro Mac OS 14+ onwards.

The app cannot invent a shortcut Excel for Mac does not support. Check the app's covered list before relying on a Windows sequence for PivotTables. The trial lasts 7 days or 10 shortcut uses, then it costs $49 once or $4.99 per month. See Mac Excel Shortcuts for the current list.

What mistakes break a PivotTable?

Blank rows, merged header cells, text-formatted numbers and forgetting to refresh are typical reasons a PivotTable looks wrong. Each has a quick fix, listed below, and none needs a formula.

  • Blank rows or columns in the source data. Excel can truncate the detected range, so check it in the Create PivotTable dialog.

  • Merged cells in headers. Microsoft says to avoid them, so unmerge before building.

  • Forgetting to refresh. Editing source data does not update the PivotTable until you refresh.

  • Numbers stored as text. Excel counts instead of summing, so convert the column to numbers first.

FAQs

Can I create a PivotTable from data in another workbook?

Possibly. Microsoft's Mac steps mention only a range or table name, so copy the data into one workbook or table first. Power Query varies by Mac build.

Can I have more than one PivotTable from the same source data?

Yes. Create each from the same range or table. Microsoft's page does not describe sharing a cache on Mac, so we do not make a claim about memory use.

Is there a shortcut to insert a PivotTable on a Mac?

Not one that Microsoft lists. Use Insert then PivotTable from the menu bar or Ribbon, or try assigning your own under Tools then Customize Keyboard.

How do I delete a PivotTable?

Select the whole PivotTable range and press Delete. Microsoft says other data, PivotTables and charts are not affected, so the source data stays in place.

Sources

← Back to all articles

Mac Excel Shortcuts: Excel shortcuts not working on Mac? Alt E S V, Paste Values, the Windows way. Try it free for 7 days.