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

Locked lesson.

Upgrade

Quick reference

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

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

In this section, we are going to build an interactive Excel dashboard to analyze and present key metrics in an engaging way. 

The first part of this process is to prepare the Pivot Charts that we want to use on the dashboard. 

In this example, we are using generic sales data to analyze sales for a global megastore. We are going to create 4 PivotTables and Pivot Charts. 

NOTE: Our sales data is already formatted as an Excel table. 

Pivot Chart 1: Profit by Month (Line Chart)

Our first Pivot Chart will show the Total Profit by Order Date. 

  • Click anywhere in the Sales Data. 
  • From the Table Design tab, click Summarize with Pivot Table. 
  • Ensure the correct Table/Range is showing. 
  • Choose New Worksheet. 
  • Click OK. 
Rename the Worksheet
  • Right-click on the new worksheet. 
  • Select Rename. 
  • Type 'Profit by Month'. 
Create the PivotTable
  • Drag the 'Total Profit' field into the Values area. 
  • Drag the 'Order Date' field into the Rows area. 
  • Remove all grouped fields from Rows except 'Order Date'. 

  • 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 (Line Chart)
  • From the PivotTable Analyze tab, in the Tools group, click PivotChart. 
  • Click on Line. 

  • 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. 

  • Click on the chart. 
  • From the Design tab, in the Chart Styles group, select Style 10 from the gallery. 

  • Click on the Legend and press Delete. 
  • Add a Chart Title, 'Profit by Month'. 
  • Change the chart color if desired. 

  • Click on the markers. 
  • Press CTRL+1 to open the Format Data Series pane. 
  • Click the Fill & Line option (bucket icon). 
  • Click Marker. 
  • Expand Marker Options. 
  • Choose Built-in
  • Increase the size of the marker to 5. 
  • Expand Fill. 
  • Select Vary colors by point. 
  • Scroll to the bottom. 
  • Select Smoothed line. 

Pivot Chart 2: Average Sales by Country (Map Chart)

Next, we are going to create a PivotTable and Chart to show the Average Sales for North America. 

When we need to create multiple PivotTables and Charts, it's more efficient to copy the worksheet and modify the information. 

  • Hold down CTRL and click and drag the 'Profit by Month' worksheet to the right. 
  • Rename the worksheet, 'Avg Sales by Country'. 
  • Delete the Line Chart. 
  • Click in the PivotTable. 
  • From the PivotTable Fields pane, remove all fields. 
  • Drag the 'Units Sold' field to the Values area. 
  • Drag the 'Country' field to the Rows area. 

By default, Excel will calculate the SUM of Units Sold. We need to change this to an Average calculation. 

  • In the Values area, click the drop-down arrow next to the field. 

  • Click Value Field Settings. 
  • Choose Average from the list. 
  • Apply the desired Number formatting. 
Apply a Filter

We are only interested in the data for the North America region. We can apply a filter to filter out all unwanted countries. 

  • In the PivotTable, click the drop-down arrow next to Row Labels. 
  • Deselect all countries. 
  • Select only 'Canada', 'Mexico', and 'United States of America'. 
  • Click OK. 
Create the Pivot Chart (Map Chart)

We cannot create a map chart using PivotTable data. 

  • Click in the data and press CTRL+A to select all. 
  • Press CTRL+C to Copy. 
  • Click in a blank cell. 
  • Press CTRL+V to Paste. 
  • Click the Paste Options tag and choose Paste Values. 

  • From the Insert tab, in the Charts group, click Maps and Filled Map.
  • Change the color palette to Colorful Palette 4. 

  • Change the Chart Style to Style 3. 

  • Add the Chart Title, 'Average Units Sold - North America'. 

We now need to point the chart back to the PivotTable data. 

  • Click on the chart. 
  • From the Chart Design tab, click Select Data. 
  • Replace the Chart data range with the PivotTable cell range. 

  • Delete the second table.
Login to download
  • 00:04 In this lesson, we're going to start the first part of the process,
  • 00:07 to build an interactive dashboard.
  • 00:10 So at this point, and based off of the last lesson,
  • 00:13 we should understand what an Excel dashboard is, and why it can be useful.
  • 00:18 So there's quite a lot to building a dashboard, so
  • 00:21 we've split this across numerous different lessons in this section.
  • 00:25 And we're going to start out in this lesson by taking our source data and
  • 00:29 just creating all of the different pivot tables and
  • 00:32 pivot charts that we want to add to our dashboard.
  • 00:36 So let's dive straight in.
  • 00:37 We have our data set just here.
  • 00:39 The first thing I'm going to do and I always advise you to do this,
  • 00:43 is I'm going to put my data set into a table.
  • 00:46 Ctrl+T, yes my table has headers, let's click on OK.
  • 00:51 Now I've got the table design ribbon,
  • 00:53 I'm going to name my table to make it easy to identify.
  • 00:57 So in the properties group, let's click in here and
  • 01:01 we're going to call this SalesData, and press Enter.
  • 01:05 So now we have our source data in a table,
  • 01:08 we can create our first pivot table and corresponding pivot chart.
  • 01:13 Now the first visualization that I want to do here is I want to create a pivot
  • 01:18 table and chart that shows the total profit by month.
  • 01:22 And this is going to be a line chart.
  • 01:25 So let's click in our table.
  • 01:27 I'm going to choose from the table design ribbon, Summarize with Pivot Table.
  • 01:32 That's my table range, that's the data I want to use.
  • 01:35 I want to put it on a new worksheet, so let's simply click on OK.
  • 01:39 So you can see I have Sheet 1 at the bottom.
  • 01:41 Now I'm just going to grab this and move this to the right-hand side of the source
  • 01:45 data, and then we're going to right click and rename this worksheet.
  • 01:48 So this one is going to be profit by month.
  • 01:51 Now I'm not going to go too slowly through this as we've already seen how to
  • 01:55 create pivot tables, but what I basically want in here,
  • 01:57 the fields that I need, are the total profit in the values field.
  • 02:01 And then I want the order date in rows, but I want just the months.
  • 02:05 So I'm going to remove years and quarters so that we just get the months there.
  • 02:10 Let's just quickly format these numbers.
  • 02:14 I'm going to use currency formatting, and we're going to take that down to 0.
  • 02:18 Now I'm also going to remove the grand total.
  • 02:23 So now we have our first pivot table, we can create our line chart.
  • 02:27 So clicking the data up to PivotTable Analyze and into pivot charts.
  • 02:33 I'm going to choose line chart.
  • 02:34 Let's click on OK, and now we can do some tidying up.
  • 02:38 So I'm going to hide all of the field buttons on the charts, and
  • 02:42 I'm also going to apply a chart style from the design ribbon.
  • 02:47 And I think I'm going to use this one.
  • 02:49 Our dashboard is going to have this kind of dark theme.
  • 02:52 So I'm going to go for Style 10.
  • 02:54 I'm going to remove the legend.
  • 02:56 Let's just delete out that text box, and let's give this chart a title.
  • 03:00 And I'm just going to do a tiny little bit of formatting here.
  • 03:03 So let's click on the line, Ctrl+1 will open up the Format Data Series pane.
  • 03:08 I'm going to go across to marker, and I'm going to choose built-in.
  • 03:13 I'm going to make my marker slightly bigger.
  • 03:15 And when it comes to fill, I'm going to choose vary colors by point,
  • 03:21 and I'm also going to use a smoothed line.
  • 03:25 Now once again, these charts are using the theme that I have applied.
  • 03:28 Now, I'm going to use a different theme.
  • 03:30 I'm going to use my own custom theme so
  • 03:33 I get that that nice multi-colored line chart.
  • 03:36 And there we go.
  • 03:37 We could carry on formatting, but that is our first chart complete.
  • 03:40 Now my recommendation is that if you are creating multiple pivot tables and
  • 03:44 pivot charts, you simply copy the worksheet and switch out the fields.
  • 03:48 So we're going to hold down Ctrl, we're going to drag and drop the worksheet, and
  • 03:51 then we're just simply going to rename it.
  • 03:54 Because the next chart that we're going to create is the average sales by country.
  • 04:02 So now I can delete out this chart and just rearrange those pivot table fields.
  • 04:07 So we don't need need Order Date,
  • 04:10 what we want in here is the Units Sold, and we want the Country.
  • 04:15 Now, I don't want to do a sum of the units sold, I want to do an average.
  • 04:19 So we're going to change the Value Filled Settings and choose Average instead.
  • 04:24 Now again, let's apply some number formatting.
  • 04:26 Now this is units sold, it's not a currency value.
  • 04:30 So we're going to go to Number Format and we're just going to choose number, and
  • 04:34 take those decimal places down to 0, let's click on OK.
  • 04:38 Now as I mentioned previously, this is way too much data to put nicely into a chart.
  • 04:43 So what we're going to do here is we're going to apply a filter because maybe
  • 04:48 we're only interested in North America.
  • 04:50 So I'm going to choose Canada, Mexico, and if we scroll down to the bottom,
  • 04:54 we should have United States in there as well.
  • 04:58 So now we have a much smaller dataset to work with.
  • 05:02 Now this is geographical data.
  • 05:03 So I'm going to put this into a map chart.
  • 05:06 Remember, as we discovered earlier,
  • 05:08 you can't create a map chart based off of pivot table data.
  • 05:12 So we need to select our data Ctrl+C, and I'm just going to Ctrl+V to paste it in
  • 05:17 underneath, but we are going to paste the values only to remove that from the chart.
  • 05:23 Now this is going to knock out your number formatting.
  • 05:25 It isn't too much of a problem.
  • 05:27 We can simply change it back to number and take those decimal places down.
  • 05:31 So now we have this, we can go to Insert > Maps, and we're going to insert a filled
  • 05:36 map chart, which should give us exactly what we're looking for.
  • 05:41 Now because we're sticking with our darker theme for our dashboard,
  • 05:44 I'm going to choose this dark gray chart style and then we can add a chart title.
  • 05:49 And of course, if you want to change the colors, you can do that,
  • 05:52 maybe I want to make these pink, or maybe a nice teal color.
  • 05:55 Let's go with that.
  • 05:57 Now remember with these map charts, we want to make sure that we point it back to
  • 06:01 the original pivot table data so that if anything changes, the map chart updates.
  • 06:05 So I'm going to click on the chart, go to chart design, and
  • 06:08 we're going to choose Select Data.
  • 06:10 We're going to remove the data range from this field, and
  • 06:14 we're just going to go in and reselect our pivot table data, click on OK, everything
  • 06:20 looks exactly the same, and then we can delete out that secondary table.

Lesson notes are only available for subscribers.

Gift this course