Create and Format Multiple Pivot Tables and Pivot Charts: Part 2

Locked lesson.

Upgrade

Quick reference

Create and Format Multiple Pivot Tables and Pivot Charts: Part 2

Learn how to create and format multiple PivotTables and PivotCharts from one dataset. 

When to use

In this context, we create multiple PivotTables and Pivot Charts as part of the preparation phase of building an interactive dashboard. 

Instructions

Let's continue building the PivotTables and Pivot Charts to add to our dashboard. 

Pivot Chart 3: Total Revenue by Item Type (Bar Chart)

Our next Pivot Chart will show the Total Revenue by Item Type.  

  • Hold down CTRL and click and drag the 'Avg Sales by Country' worksheet to the right. 
  • Rename the worksheet, 'Total Rev Item Type'. 
  • Delete the Map Chart. 
  • Click in the PivotTable. 
  • From the PivotTable Fields pane, remove all fields. 
  • Drag the 'Total Revenue' field to the Values area. 
  • Drag the 'Item Type' field to the Rows area. 

  • Apply Number formatting as desired. 
Remove the Grand Total
  • Click in the PivotTable. 
  • From the Design tab, in the Layout group, click Grand Totals. 
  • Select Off for Rows and Columns. 
Create the Pivot Chart (Bar Chart)
  • From the PivotTable Analyze tab, in the Tools group, click PivotChart. 
  • Click on Bar. 
  • Click OK. 
Format the Chart

We can now apply formatting as desired. Our dashboard is going to have a dark theme so we will bear this in mind when formatting our Pivot Charts. 

  • Right-click on the grey field buttons. 
  • Click Hide all Field buttons on chart. 
  • Delete the Legend. 
  • From the Design tab, in the Chart Styles group, select Style 1 from the gallery. 
  • Click on the bars and press CTRL+1. 
  • Reduce the Gap Width to 35. 

  • Add a Chart Title, 'Total Revenue by Item Type'. 
  • Change the chart color if desired. 
  • Right-click on the bars and click Add Data Labels.
  • Click on the data labels and press CTRL+1. 
  • From the Data Labels area, expand the Number category. 
  • From Category, select Custom from the drop-down menu. 
  • In the Format Code field, type "#,##0,,"M".

  • Delete the horizontal axis. 

Pivot Chart 4: Profit by Year and Channel (Column Chart)

Create the PivotTable
  • Hold down CTRL and click and drag the 'Total Rev by Item Type' worksheet to the right. 
  • Rename the worksheet, 'Profit by Year and Channel'. 
  • Delete the Bar Chart. 
  • Click in the PivotTable. 
  • From the PivotTable Fields pane, remove all fields. 
  • Drag the 'Total Profit' field to the Values area. 
  • Drag the 'Years' field to the Rows area. 
  • Drag the 'Sales Channel' field to the Columns area. 
  • Apply Number Formatting as desired. 
Create the Pivot Chart (Column Chart)
  • From the PivotTable Analyze tab, in the Tools group, click PivotChart. 
  • Click on 2D Clustered Columns. 
  • Click OK. 
Format the Chart

We can now apply formatting as desired. Our dashboard is going to have a dark theme so we will bear this in mind when formatting our Pivot Charts. 

  • Right-click on the grey field buttons. 
  • Click Hide all Field buttons on chart. 
  • Click the + icon to the right-hand side. 
  • Click Legend and then Top. 
  • Click the + icon to the right-hand side. 
  • Turn on Chart Title. 
  • Change the title to 'Profit by Year and Sales Channel'. 
  • From the Design tab, in the Chart Styles group, select Style 12 from the gallery. 
  • Click on the bars and press CTRL+1. 
  • Reduce the Gap Width to 35. 

  • Right-click on the bars and click Add Data Labels. 
  • Format the data labels as millions. 
  • Delete the vertical axis. 

 

Login to download
  • 00:04 So now we have our map chart, we're going to replicate the worksheet again.
  • 00:09 So hold down control, drag the worksheet to the right, to get an exact copy.
  • 00:14 Once again, we're going to rename this tab,
  • 00:18 because this time we want the Total Revenue by Item Type.
  • 00:23 We can delete out this chart, and then we can just rearrange our pivot table fields.
  • 00:29 So this time we're going to remove both of these.
  • 00:32 We want the total revenue by the item type.
  • 00:37 So let's drag that down into rows.
  • 00:39 Once again, we're going to apply at number formatting,
  • 00:42 currency this time, take the decimal places down to zero.
  • 00:46 And this time we're going to insert a bar chart.
  • 00:49 So let's jump up to pivot table, analyze, pivot chart,
  • 00:53 we're going to choose bar, and click on OK.
  • 00:56 Now, once again, let's do some tidying up.
  • 00:58 I'm going to right click and hide all feel buttons on chart.
  • 01:01 I'm going to remove the legend from here.
  • 01:05 going to make this a bit longer and move this across,
  • 01:08 because what I want to do here is widen out these bars.
  • 01:11 So Cntrl 1 to open up format data series.
  • 01:14 And I'm just going to move that gap with, or make that gap with quite a bit smaller.
  • 01:19 So let's take it down to about 35, that'll do.
  • 01:23 Now we're going to keep with our dark themes.
  • 01:26 Let's go to design.
  • 01:27 I'm going to click the chart styles gallery and choose this style just here,
  • 01:32 because this looks pretty cool.
  • 01:34 Once again, if you need to change the colors you can do that from here.
  • 01:37 So, maybe I want some pink bars or some green bars, let's go for
  • 01:41 that nice teal color.
  • 01:43 Now, I'm going to right click and
  • 01:45 I'm going to add some data labels on the outside.
  • 01:48 Now currently these look a little bit messy, because they're very large numbers.
  • 01:52 Now what we can do here is we can select our data labels,
  • 01:56 if we go back over to our Format Data Labels pane on the right hand side,
  • 02:00 we can choose how we want this number to look.
  • 02:03 So we can apply different types of formatting.
  • 02:06 We can even create our own custom format.
  • 02:08 So because these are large numbers, I'm going to format them as millions.
  • 02:12 Now I already have the format that I want to use in here, because once you type it
  • 02:17 in once, it's going to keep it in this little type drop down.
  • 02:20 So the format to format these numbers as millions is this just here.
  • 02:25 So you can type that into format code, and click Add, and
  • 02:28 it means it's going to be available for you to select from the Type drop down.
  • 02:32 Now notice as soon as I do that the effect that has on my data.
  • 02:36 Much cleaner and easier to read.
  • 02:38 So with all of these data labels selected, I'm going to make those bold.
  • 02:43 And then we don't really need this horizontal axis at the bottom.
  • 02:46 So let's just delete that out.
  • 02:49 Final thing to do here is just simply give our chart a title.
  • 02:52 Let's create our final pivot table and pivot chart.
  • 02:55 I'm going to hold down control,
  • 02:56 we're going to drag the worksheet to the right to get a copy.
  • 02:59 We're going to delete out the charts and
  • 03:02 let's rename this tab Profit by Year and Channel.
  • 03:07 So, let's click and we're going to modify our pivot table fields.
  • 03:12 Let's remove these, and we want the profit, and
  • 03:17 we want this by years, and also Sales Channel.
  • 03:22 We're going to put that into columns.
  • 03:24 Once again, let's change our number formatting.
  • 03:27 We want currency format.
  • 03:28 We're going to take it down to 0.
  • 03:30 Click on OK.
  • 03:32 And now we can create our chart.
  • 03:34 So, pivot charts.
  • 03:36 This time we're going to create just a column chart,
  • 03:40 let's click on OK, there it is.
  • 03:43 And we can go through and hide all our field buttons.
  • 03:47 I'm going to move the Legend, so that it's at the top.
  • 03:52 We're going to make sure that we do add a Chart Title.
  • 03:56 And we'll modify that in a moment.
  • 03:58 And once again, we're going to go up to design and
  • 04:01 we're going to use one of these darker chart styles.
  • 04:04 So that is looking pretty cool.
  • 04:06 Now, once again, I'm going to click on one of these bars Cntrl 1,
  • 04:10 to open up Format Data Series, and we're going to change the gap width.
  • 04:14 So we're going to take that all the way down to make these a bit wider.
  • 04:18 We're going to add our data labels.
  • 04:21 And once again, I'm going to format these so
  • 04:24 that we're using custom number formatting.
  • 04:27 And once again, I'm going to use my millions formatting and
  • 04:32 let's make those bold.
  • 04:33 So that means I can now delete out these longer numbers on that axis.
  • 04:39 Now we want to make sure that we do the same for the other bars.
  • 04:41 Let's add data labels.
  • 04:43 Once again, we need to format these.
  • 04:48 Let's go to number, Custom, and
  • 04:52 we're going to choose our millions formatting and make those bold as well.
  • 04:59 Final thing to do here is just simply add a chart title.
  • 05:03 And that is pretty much it.
  • 05:05 We've created all of our pivot tables and
  • 05:07 pivot charts that we want to use in our dashboard.
  • 05:10 The final thing I would do here, and
  • 05:12 this relates back to designing better spreadsheets,
  • 05:15 is I would select all of these pivot table tabs by holding down shift, and
  • 05:19 I'm going to right click and change the tab color, so they're all the same.
  • 05:23 So let's make these a teal color.
  • 05:25 So I know that those are the tabs that contain the pivot tables and pivot charts,
  • 05:30 and it differentiates them from my source data.

Lesson notes are only available for subscribers.

Gift this course