Pivot Charts in action

1. Pivot Charts in action

Let’s build our first PivotChart using a disaggregated dataset of Spotify's top songs per year. Notice that year is stored in a date field. We will click anywhere within the cells containing data and press CTRL+A to select the entire table. Then, we will insert the PivotChart from the menu. Excel confirms the underlying dataset and asks where to place the chart. We will proceed with the default settings. In a new sheet, placeholders for the PivotTable and PivotChart will appear. Let's see how they work. We will start by visualizing the sum of songs per date field. When we drop the "date" into the Rows, as it’s formatted as a real date, Excel automatically translates it into years and quarters. For now, we'll just keep “Years”. From here, we can modify the chart just as we would with a standard chart. Let’s click on it and, using the Design menu, change the type to a line chart. Now, let's add the genre to the Columns in the PivotTable and see what happens. The line chart is now split by genre! The PivotChart is linked to the PivotTable, so any changes made to either the chart or the table will be reflected in both, so if we filter on e.g. hip hop only, both table and the chart will react. Let’s revert that for now by pressing CTRL+Z. Let's create another instance of the PivotTable below by copying the existing one. Here, we'll also insert a new PivotChart. In this new PivotTable we will remove the “Years” and the “Sum of songs” from the Rows and Values and move the “Genre” field from Columns to Rows. We will add popularity and energy to the Values. Since we want to visualize averages, we need to right-click on the measure and change the Summarize Values By to average. Now, we have two PivotCharts. Let's migrate them to a Dashboard sheet. We select two charts, press CTRL+X to cut and then, in Dashboard sheet, we press CTRL+V to paste. Next, let's work on the look and feel of the dashboard. We want to remove these gray buttons from our pivot charts, so we will right-click on them and select “Hide All Field Buttons on Chart”. Let’s add chart titles: "# Songs per genre" and "Genre stats." In the View menu, we’ll remove the headings and gridlines by unticking them. We will also remove the outlines of our charts by right-clicking on them and selecting the “No Outline” option. To make the dashboard interactive, we’ll click on any chart and use the Insert menu to add a slicer based on "Genre" and a Timeline based on "date". In the Timeline, we’ll change the period denomination to years. Let’s test it out! Clicking on the slicer and timeline only works on the first chart! This is because we need to connect both visualizations to these filters. Let’s click on the slicer and, in the Slicer menu, change the Report Connections, ensuring that all underlying PivotTables are linked to it. We’ll do the same with the timeline filter. And we can now play with our mini dashboard. What if we were to print it out? First, we select the cells that we’re interested in, then we go to Page Layout menu, Print Area and Set Print area. We’ll now navigate to the File tab, and in the left menu, we’ll select Print which provides us with multiple options. By default, the setting is set to print active sheets and has a portrait orientation. Let’s change it to the Landscape orientation here. Let’s try scaling our sheet; we can do this by clicking on the dropdown under No scaling. We want to update this to Fit Sheet on One Page. Now we can see that this visual is ready to be printed. We can also change the print area. There are various options, such as “print active sheets”, “entire workbooks”, and “print selection”. Now, onto you!

2. Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.