# How to make a pivot table in Excel on Mac: step by step for Microsoft 365

> Make a pivot table in Excel on Mac: select your data, choose Insert > PivotTable, pick where it goes, then tick the fields to summarize. Plus fixes.

URL: https://www.lazur.app/how-to/make-a-pivot-table-in-excel
Updated: 2026-11-05

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.

Applies to: Excel for Mac in Microsoft 365 (version 16). Time: 5 min. Difficulty: Easy.

## 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

1. **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.
2. **Click a cell in the data.** Click any single cell inside the range or table. Excel works out the full range from there.
3. **Choose Insert > PivotTable.** (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.
4. **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.
5. **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.
6. **Change how values are summarized.** (Values > Value Field Settings) 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.

![Chart, cool backgrounds and graph. How to make a pivot table in Excel on Mac](https://www.lazur.app/content-images/howto/make-a-pivot-table-in-excel/how-to-make-a-pivot-table-in-2-c7d241.jpg)

| 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 |

_Common layouts and where each field goes_

- **1,048,576** rows a single Excel worksheet can hold, so a pivot table can summarize a lot of data ([source](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3))
- **16,384** columns per worksheet in current Excel versions ([source](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3))

## How 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.

![Man, computer and trading. How to make a pivot table in Excel on Mac](https://www.lazur.app/content-images/howto/make-a-pivot-table-in-excel/how-to-make-a-pivot-table-in-3-8ec664.jpg)

## Shortcuts

- 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.

## With Lazur

Lazur is a voice assistant for the Mac that lives in the notch. Hold ⌥ ⌘ and ask; it sees your screen, answers out loud, points at buttons and walks you through apps. Hold fn to dictate. Free for 3 days, then $18/month: https://www.lazur.app/download

- “where's the pivot table button?”: It's under the Insert tab, second from the left. I've circled it. (Points at it)

## FAQ

### 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

1. [Create a PivotTable to analyze worksheet data](https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576), Microsoft Support, checked 2026-11-05
2. [Excel specifications and limits](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3), Microsoft Support, checked 2026-11-05
3. [Create a pivot table in Numbers on Mac](https://support.apple.com/guide/numbers/tan7f62056cf/mac), Apple Support, checked 2026-11-05