How to Fix Excel for Mac Slicers Not Working with Power Query Data Models
Question details
User needs a way to make slicers, PivotTable filters, and chart field buttons work in Excel for Mac when the workbook relies on Power Query data.
- Product
- Excel for Mac
- Device & OS
- macOS
- Scenario
- Filtering PivotTables or charts using slicers connected to a Power Query Data Model on a Mac computer.
- Observed behavior
- Slicers, PivotTable filters, and chart field buttons are unresponsive or do not work because Excel for Mac lacks full support for the Data Model and Power Pivot.
Ensure you have access to a Windows environment with Excel, as you will need to modify the original Power Query load settings before the file can function correctly on a Mac.
Load Power Query Results to a Standard Excel Table
Bypass the unsupported Mac Data Model by loading your Power Query results directly into a standard worksheet table.
Excel for Mac currently lacks full support for the Data Model (Power Pivot). When a workbook created in Windows uses the Data Model, Mac users cannot interact with its slicers or filters.
To fix this, you must change the query load destination from the Data Model to a standard Excel Table, provided you do not require complex DAX measures or relationships.
Launch Excel on a Windows PC and open the workbook containing the original Power Query configuration.
Navigate to the 'Data' tab on the top ribbon and click on 'Queries & Connections' to open the side panel.
Right-click your specific query in the panel and select 'Load To...' from the context menu.
In the Import Data dialog box, select 'Table' instead of 'Only Create Connection' or 'Add this data to the Data Model'.
Select an existing or new worksheet to place the table, then click 'OK'. Build your PivotTable and Slicers from this new standard table so they function on both Windows and Mac.
Use WPS Office for Seamless Mac and Windows Data Analysis
If you frequently share files between Mac and Windows and encounter compatibility issues with advanced Excel features like the Data Model, consider switching to WPS Office. It provides a lightweight, highly compatible spreadsheet environment that handles standard PivotTables and charts flawlessly across all operating systems without the heavy overhead of unsupported data models.
- 1. Download WPS Office: Visit the official WPS website to download and install WPS Office for Mac.
- 2. Open WPS Spreadsheets: Launch the application and open your existing .xlsx workbook.
- 3. Create standard PivotTables: Select your standard table data, go to the 'Insert' tab, and click 'PivotTable' to analyze data seamlessly.
Frequently Asked Questions
Why do my Excel Slicers say they are disabled on Mac?
Excel for Mac does not fully support the Power Pivot Data Model. If your Slicers are connected to a PivotTable built from the Data Model, they will be disabled or unresponsive on a Mac device.
Can I use Power Query on Excel for Mac?
Yes, Excel for Mac has recently introduced support for Power Query, allowing you to refresh and create queries. However, loading those queries into the Data Model is still restricted and causes issues with interactive elements like slicers.
Will I lose my DAX measures if I switch to a standard table?
Yes. Standard Excel tables do not support DAX measures or relationships between multiple tables. If your analysis relies heavily on DAX, you will need to use Excel for Windows or rebuild your calculations using standard Excel formulas.
How do I create a Slicer from a standard table on Mac?
Click anywhere inside your PivotTable that is sourced from a standard table. Go to the 'PivotTable Analyze' tab on the ribbon, click 'Insert Slicer', check the fields you want to filter, and click 'OK'.
