How-to · Excel for Mac
How to make a pivot table in Excel on Mac: step by step for Microsoft 365
To make a pivot table in Excel on a Mac, click any cell in your data, choose Insert > PivotTable, pick New Worksheet, and click OK. Then tick the fields you want in the PivotTable Fields pane and drag them between Rows, Columns and Values.
Quick answer
- Click a cell in your data, then Insert > PivotTable > New Worksheet > OK.
- Tick fields in the PivotTable Fields pane. Text goes to Rows, numbers go to Values.
- Change Sum to Count or Average with Value Field Settings.
- When the source data changes, right-click the pivot table and choose Refresh.
What you need
- Excel for Mac (Microsoft 365, Excel 2021 or Excel 2024)
- Data with one header row and no fully blank rows or columns
How to make a pivot table in Excel on a Mac
Tidy your data first
Each column needs a header in row 1, like Date, Region, Product and Sales. Remove blank rows inside the range. Pivot tables read the whole block, and a blank row cuts it short.
Tip: Press ⌘ + T to turn the range into a table. Tables grow when you add rows, so your pivot table picks up new data on refresh.
Click a cell in the data
Click any single cell inside the range or table. Excel works out the full range from there.
Choose Insert > PivotTable
Open the Insert tab on the ribbon and click PivotTable. The Create PivotTable dialog opens with your range or table name already filled in.
Pick where the pivot table goes
Choose New Worksheet to keep it apart from your data, or Existing Worksheet and click the cell where it should start. Click OK. Excel adds an empty pivot table and opens the PivotTable Fields pane.
Tick the fields you want
In the PivotTable Fields pane, tick each field to add it. By default, text fields go to Rows, dates go to Columns and numbers go to Values. Drag a field between the Filters, Columns, Rows and Values boxes to change the layout.
Change how values are summarized
Excel sums numbers by default. To count or average instead, click the small i next to the field in the Values box. You can also right-click a value and choose Value Field Settings. Then pick Count, Average, Max or Min.
What a pivot table does
A pivot table groups rows that share a value and adds up the numbers for each group. Put Region in Rows and Sales in Values, and you get total sales per region. Add Product to Columns and you get a grid of region by product.
| You want | Rows | Columns | Values |
|---|---|---|---|
| Sales per region | Region | Sum of Sales | |
| Sales per region and product | Region | Product | Sum of Sales |
| Number of orders per month | Date (grouped by month) | Count of Order ID |
1,048,576
rows a single Excel worksheet can hold, so a pivot table can summarize a lot of data
Microsoft: Excel specifications and limitsHow to refresh a pivot table on Mac
Pivot tables do not update on their own. After you change the source data, right-click anywhere in the pivot table and choose Refresh. To update every pivot table in the workbook, use PivotTable Analyze > Refresh > Refresh All.
Excel shortcuts for this on Mac
| To do this | Press |
|---|---|
| Turn a range into a table | ⌘T |
| Select the whole data range | ⌘A |
If it does not work
- "The PivotTable field name is not valid"
- One of your columns has no header. Give every column in row 1 a name, then try Insert > PivotTable again.
- New rows do not show up
- Right-click inside the pivot table and choose Refresh. If rows still go missing, your source is a fixed range. Turn the data into a table (⌘ + T) and point the pivot table at it with PivotTable Analyze > Change Data Source.
- Numbers are counted instead of summed
- The column has text or blank cells mixed in, so Excel treats it as text. Fill the blanks with 0 and make sure the cells are formatted as numbers, then refresh.
Faster with Lazur
Do it faster: ask Lazur while you are in Excel
Hold ⌥ ⌘ and say what you want. Lazur sees your Excel window. Lazur draws a circle and a label around the button or menu you need.
- 1
Hold ⌥⌘ and talk
- 2
Lazur draws a circle and a label around the button or menu you need.
- 3
You stay in Excel. Nothing to copy into a chat window.
Free for 3 days, then $18/month. Download for Mac
You say“where's the pivot table button?”
Lazur · points at itIt's under the Insert tab, second from the left. I've circled it.
Questions people ask
Where is the PivotTable button in Excel for Mac?
It is on the Insert tab of the ribbon. Click a cell in your data first, then choose Insert > PivotTable.
Can I make a pivot table in Numbers instead?
Yes. Numbers for Mac has pivot tables. Select your table, then choose Organize > Create Pivot Table and pick a new or the current sheet. Fields go into Columns, Rows and Values, the same idea as Excel.
Why is my pivot table not updating?
Pivot tables only change when you refresh them. Right-click inside it and choose Refresh. If new rows are still missing, change the source to a table so it grows with your data.
Can Lazur make the pivot table for me?
Lazur does not click inside Excel for you. Hold ⌥ ⌘ and ask, and it shows you where each button is, circles it on your screen, and walks you through the steps one click at a time.
Sources
- Create a PivotTable to analyze worksheet data · Microsoft Support · checked 2026-11-05
- Excel specifications and limits · Microsoft Support · checked 2026-11-05
- Create a pivot table in Numbers on Mac · Apple Support · checked 2026-11-05
Try Lazur free for 3 days
Hold a key and ask. Lazur sees your screen, points at the button and talks you through it, in any Mac app. No credit card.