How to Combine PivotTable Data from Multiple Sheets on Excel for Mac
Question details
The user needs to combine or relate data from multiple sheets in Excel for Mac so it can be analyzed in one PivotTable.
- Product
- Microsoft Excel for Mac
- Device & OS
- macOS
- Scenario
- The user has related data split across multiple worksheets and wants to create a unified PivotTable for analysis.
- Observed behavior
- Excel for Mac may show different PivotTable and Data Model options than Windows, making the correct combination method version-dependent.
Open the workbook and verify that each source sheet uses the same column headers or a shared key field, such as an ID or product name, so the data can be aligned during consolidation.
Use Power Query to combine multiple sheets in Excel for Mac
Use this method when your version of Excel for Mac includes Power Query and you need a query-based merge using a common field.
In Excel for Mac, click the Data tab and select Get Data (Power Query), then choose From Workbook and select the current workbook.
In the Navigator window, select the sheets you want to import, then click Edit or Open Query Editor to prepare the data.
In the Query Editor, select Merge Queries, choose the common field that appears in both sheets, and complete the merge.
Select Close & Load to load the merged result into a worksheet or Data Model, then go to Insert > PivotTable and build the summary.
Combine sheets with WPS Spreadsheets Data Consolidation
Use this free WPS method when you want to merge data from multiple sheets into a single summary range, then create a PivotTable without a Power Query add-in.
Consolidate Multiple Sheets into a PivotTable with WPS Spreadsheets
Use the built-in Data Consolidate tool in WPS Spreadsheets to combine several worksheet ranges into one summary, then create a PivotTable without subscribing to Microsoft 365 or relying on Power Query availability.
- 1. Download WPS Office: Download WPS Office from the official site and install it on your Mac or Windows device.
- 2. Open the workbook: Launch WPS Spreadsheets and open the existing Excel workbook with the multiple source sheets.
- 3. Consolidate the ranges: Click Data > Consolidate, choose the summary function, and add each sheet range with the Add button.
- 4. Create the combined summary: Click OK to generate the merged summary range on a new worksheet.
- 5. Build the PivotTable: Select the summary range, click Insert > PivotTable, and configure the rows, columns, and values for your report.
Frequently Asked Questions
Can I create a PivotTable from multiple sheets without combining data first?
No. A single PivotTable cannot reliably use unconnected ranges from multiple sheets. Combine the ranges with Power Query in Excel for Mac or with Data > Consolidate in WPS Spreadsheets, then create the PivotTable from the combined result.
Does Excel for Mac include Power Query on all versions?
No. Power Query availability and menu names vary by Excel for Mac version. If Get Data or Power Query is not available on the Data tab, use WPS Spreadsheets Data Consolidation to combine the sheets instead.
Will WPS Spreadsheets open my Excel for Mac workbook?
Yes. WPS Spreadsheets opens and saves .xlsx and .xls files, so you can use the same workbook, consolidate the sheets, and build a PivotTable without converting the file.
What should I do if my source sheets have different column orders?
Arrange the columns in the same order or make sure each sheet contains a common key field. In Excel for Mac, use Merge Queries with the key field; in WPS Spreadsheets, align the layout before using Data > Consolidate.
Why does my PivotTable show only one sheet's data?
The PivotTable is probably using only one source range. Check the data source and confirm the selected range is the combined summary or query result that already contains all sheets.
