As I mention in my post on creating pivot charts, you can't create pivot charts in Excel for the Mac. It's, hands down, my greatest aggravation with Excel. Bar none. However, Excel for the Mac has an additional bug that renders any pivot chart created on a PC that includes a report filter utterly useless. You can see the options in the drop-down filter and even select them. In fact, the pivot table (that's created with every pivot chart) updates. However, your pivot chart will not update. I demonstrate this disappointing failure in this short demo.
I have a chart with past and forecasted figures for several series of data. I set the colors of each serie in order to have something nice and meaningful: 1 color per serie, dotted for forecast. Each time I filter my data to see a particular serie, Excel destroys my work to put back its automated layout which are ugly and meaningless. For chart styles, we'll use a simple column chart. At this point, our chart is plotting the full set of source data. Let's filter the pivot chart to show only certain products: banana chocolate and orange chocolate. One way to do this is to add Product as a report filter. You can filter the data in a pivot chart.
Test For Yourself
Don't believe me, Excel for Mac users? Go ahead and test it for yourself with the pivot chart in this Excel file.
Learn More
If you want to learn more about data visualizations that don't suck, check out my Annielytics Dashboard Course offerings.
- Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2016 for Mac More. Less Slicers provide buttons that you can click to filter tables, or PivotTables.
- Let's start with chart filters. Click anywhere in your chart. Click the Chart Filters button next to the chart. On the Values tab, check or uncheck the series or categories you want to show or hide.
Image from troll.me.
- Excel Charts Tutorial
- Excel Charts Useful Resources
This video will show you how to create a scatter plot on Excel 2016 for Mac. I provide examples for creating one line and two lines. Please let me know if you have any questions or want me to cover any other topics as I am open to suggestions! Make sure to like and subscribe for more videos in the future!
- Selected Reading
You can use Chart Filters to edit the data points (values) and names that are visible on the displayed chart, dynamically.
Step 1 − Click on the chart.
Step 2 − Click the Chart Filters icon that appears at the upper-right corner of the chart. Two tabs – VALUES and NAMES appear in a new window.
Values
Values are the series and the categories in the data.
Click the Values tab. The available SERIES and CATEGORIES in your data appear.
Values – Series
Chart Filter Excel 2016 For Macs
Step 1 − Point on any of the available series. That particular series will be highlighted on the chart. In addition, the data corresponding to that series will be highlighted in the excel table.
Step 2 − Select the series you want to display and deselect the rest of the series. Click Apply. Only the selected series will be displayed on the chart.
Values – Categories
Step 1 − Point to any of the available categories. That particular category will be highlighted on the chart. In addition, the data corresponding to that category will be highlighted in the excel table.
Step 2 − Select the category you want to display deselect the rest of the categories. Click Apply. Only the selected categories will be displayed on the chart.
Names
NAMES represent the names of the series in the chart. By default, names are taken from the excel table.
You can change the names of the series in the chart using the names tab in the chart filters. Click the NAMES tab in the Chart Filters. The names of the series and the names of the categories in the chart will be displayed.
You can change the names of the series and categories with select data button, in the lower right corner of the chart filters box.
Names – Series
Step 1 − Click the Select Data button. The Select Data Source Dialog Box appears. The names of the series are at the left side of the dialog box.
To change the names of the series,
Step 2 − Click the Edit button above the series names.
If you want to learn more about data visualizations that don't suck, check out my Annielytics Dashboard Course offerings.
- Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2016 for Mac More. Less Slicers provide buttons that you can click to filter tables, or PivotTables.
- Let's start with chart filters. Click anywhere in your chart. Click the Chart Filters button next to the chart. On the Values tab, check or uncheck the series or categories you want to show or hide.
Image from troll.me.
- Excel Charts Tutorial
- Excel Charts Useful Resources
This video will show you how to create a scatter plot on Excel 2016 for Mac. I provide examples for creating one line and two lines. Please let me know if you have any questions or want me to cover any other topics as I am open to suggestions! Make sure to like and subscribe for more videos in the future!
- Selected Reading
You can use Chart Filters to edit the data points (values) and names that are visible on the displayed chart, dynamically.
Step 1 − Click on the chart.
Step 2 − Click the Chart Filters icon that appears at the upper-right corner of the chart. Two tabs – VALUES and NAMES appear in a new window.
Values
Values are the series and the categories in the data.
Click the Values tab. The available SERIES and CATEGORIES in your data appear.
Values – Series
Chart Filter Excel 2016 For Macs
Step 1 − Point on any of the available series. That particular series will be highlighted on the chart. In addition, the data corresponding to that series will be highlighted in the excel table.
Step 2 − Select the series you want to display and deselect the rest of the series. Click Apply. Only the selected series will be displayed on the chart.
Values – Categories
Step 1 − Point to any of the available categories. That particular category will be highlighted on the chart. In addition, the data corresponding to that category will be highlighted in the excel table.
Step 2 − Select the category you want to display deselect the rest of the categories. Click Apply. Only the selected categories will be displayed on the chart.
Names
NAMES represent the names of the series in the chart. By default, names are taken from the excel table.
You can change the names of the series in the chart using the names tab in the chart filters. Click the NAMES tab in the Chart Filters. The names of the series and the names of the categories in the chart will be displayed.
You can change the names of the series and categories with select data button, in the lower right corner of the chart filters box.
Names – Series
Step 1 − Click the Select Data button. The Select Data Source Dialog Box appears. The names of the series are at the left side of the dialog box.
To change the names of the series,
Step 2 − Click the Edit button above the series names.
The Edit Series dialog box appears. You can also see the cell reference of the name of the first series.
Step 3 − Change the cell reference of the name of the first series. Click OK.
You can see that the name of the first series has changed.
Step 4 − Repeat the steps 2 and 3 for the names of the rest of the series.
Note that the names have changed only in the chart. They have not changed in the Excel table.
Names – Categories
Chart Filter Excel 2016 For Mac Data Entry Form
To change the names of the categories, you need to follow the same steps as for series, by selecting the edit button above the categories names in the select data source dialog-box.