Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to modify Map chart to reference Pivot Table and filter using Slicer. Let’s get started
Traditionally, it is impossible to use Map, Treemap, Waterfall, Sunburst charts inside Pivot Table. However, to use the above-mentioned chart inside Pivot Table, it is required to know how to modify the data.
Note, Maps and Treemap Charts are only available to Microsoft Office 2019 version and Office 365 users. In addition, there is need for internet connection to create Maps
In the caption below, we tried to insert Maps based on the Pivot Table data. However, we can see that we are unable to create this type of chart inside a PivotTable. We need to close the Insert Chart dialogue box
What we are going to do is simple:
- Copy the Pivot Table data in columns A and B and Paste as Values in columns D and E using Alt + HVV
2. Click inside any cell in columns D and E: In the Charts group of the Insert tab, click on Maps
3. In the contextual Design tab of the Charts Tool tab, click on Select Data
4. In the Horizontal (Category) Axis Labels of the Select Data Source dialogue box, click on Edit
5. In the Axis Label dialogue box, replace the two D with A (column A represent the List of States in the Pivot Table)
6. Click OK
7. In the Legend Entries (Series) of the Select Data Source dialogue box, click on Edit
8. Replace all the instances of E with B (column B represent the Sum of Population for each States in the Pivot Table)
9. Click OK and Click OK to close the Select Data Source
10. Delete columns D and E
TREEMAP Chart
In the caption below, we have similar challenge unlike the Maps
To use Treemap inside Pivot Table, repeat steps 1 to 10 performed for the Maps
From the caption below, we can see the final output of Treemap inside Pivot Table
