Create Pivot Tables and Pivot Charts

Locked lesson.

Upgrade

Quick reference

Create Pivot Tables and Pivot Charts

Ask Copilot to analyze a dataset and create a Pivot Table and Pivot Chart. 

When to use

We use Pivot Tables and Charts whenever we want to quickly analyze a dataset and gain insight into key metrics. 

Instructions

Copilot's 'Analyze Data' facility allows us to quickly create Pivot Tables and Pivot Charts. 

Let's set up the scenario. Our manager has asked us to create a report that shows the total sales by invoice data and product. 

Create a Pivot Table

  • Open Copilot. 
  • Type the prompt: 'Create a Pivot Table that shows the total sales by invoice data, total sales and product'. 
  • Press Enter. 

Copilot will create a Pivot Table based on our prompt. 

  • Click Add to a new sheet. 

A new worksheet will be created and the Pivot table added. We can now format the Pivot Table as we usually would using the Design and Pivot Table Analyze ribbons. 

Change the Grouping

In this example, Copilot has listed the full dates in the columns. We can change this grouping to only show the summary by years. 

  • Click the PivotTable Analyze ribbon. 
  • In the Group group, click Group Field. 
  • Deselect everything except Years. 

NOTE: To see just the months, choose Months. We can also modify the grouping by dragging the fields we don't need out of the columns area in the PivotTable Fields pane. 

Create a Pivot Chart

Now our manager has asked us to create a Pivot Chart that shows the count of total sales by region. 

  • Type the prompt: 'Create a Pivot Chart that shows the count of Total Sales by Region'. 
  • Press Enter. 

In this instance, Copilot is suggesting a bar chart. 

  • Click Add to a new sheet. 

Copilot will add both the Pivot Chart and the associated Pivot Table to a new worksheet. 

Hints & tips

  • It's always best to use column names instead of column references in our prompts e.g. 'Region' instead of 'Column C'. 
Login to download
  • 00:04 Did you know the Copilot is also brilliant at analyzing data?
  • 00:08 Let's take a look.
  • 00:09 Now, maybe I want to analyze this data set by the invoice date, so
  • 00:14 I'm going to type in my prompt and get Copilot to create a Pivot Table.
  • 00:19 So a prompt is going to be create Pivot Table that shows
  • 00:23 the Total Sales by Invoice Date, Total Sales and Product.
  • 00:28 And one of the things I find working with Copilot is that it tends to work better
  • 00:33 if you give it column names as opposed to just saying column B, C,
  • 00:36 or D, you tend to get a better result.
  • 00:39 So Copilot is just finishing up here, and
  • 00:42 if I scroll up, I can see that it has produced a PivotTable for me.
  • 00:46 Now, it hasn't added it to the sheet automatically,
  • 00:49 it will put it into the pane.
  • 00:50 You can check it to make sure that it's what you want before adding it
  • 00:54 to a new sheet.
  • 00:55 And if you scroll up, you can see what it's done.
  • 00:57 It says I created a PivotTable that shows the total sales by Invoice Date and
  • 01:02 Product, so we have a bit of information there as well.
  • 01:05 So I'm just looking at this PivotTable, yeah, that pretty much looks like what I
  • 01:09 want, and the description seems to match what I was expecting.
  • 01:13 So we can click on Add to a new sheet, and it's going to do exactly that.
  • 01:17 It's going to insert the PivotTable onto a new worksheet.
  • 01:20 Now, looking at this, I can see I've got a few gaps and
  • 01:23 I actually don't want to break it down into days, I just want to show the years.
  • 01:27 So we're going to do a bit of manual editing here on the PivotTable
  • 01:30 analyze ribbon.
  • 01:31 We're going to click on Group Field and we're just going to change it to years and
  • 01:36 months, first of all, to see what we get.
  • 01:38 Looks pretty good, but I just want the year.
  • 01:41 So let's just drag the months field out the PivotTable, and that's what I want.
  • 01:47 I'm now getting a summary by product and the year, whether it's 2022 or 2023.
  • 01:53 And I can then work with this PivotTable much like I would with any PivotTable I'd
  • 01:57 created the regular way.
  • 01:58 Let's type another prompt.
  • 02:00 This time, we're going to create a Pivot Chart that
  • 02:04 shows the count of total sales by region.
  • 02:08 Now, one thing I have noticed with Copilot is that it does seem to be very PivotTable
  • 02:12 and PivotChart bias.
  • 02:13 A lot of the time when you want it to return a formula,
  • 02:16 it will return a PivotTable or a PivotChart instead.
  • 02:20 So just be aware of that it does tend to favor PivotTables.
  • 02:24 Now, I can see that it has created a PivotChart for me,
  • 02:27 it's just finishing up its final calculations, and
  • 02:30 in a moment, we should see that button Add to a new sheet will become active.
  • 02:34 And of course, as usual,
  • 02:35 we get a little description as to what that PivotChart is showing.
  • 02:39 And I can see that this created a clustered bar chart.
  • 02:42 In general, Copilot will try and choose the best chart type for your dataset.
  • 02:48 So I'm going to say, Add to a new sheet.
  • 02:50 Again, it's going to create a new worksheet, and it's going to add not only
  • 02:54 the PivotChart, but also the corresponding table to the worksheet.
  • 02:57 And I can now go in, I can go to the Design tab, I can change the design,
  • 03:01 the colors, all of the stuff that I'd normally do when I'm creating PivotCharts.
  • 03:06 So let's just change the color to this nice purple color, and
  • 03:10 I'm going to leave it at that.
  • 03:11 So as you can see, really simple to analyze your data set with PivotTables and
  • 03:16 PivotCharts using Copilot.

Lesson notes are only available for subscribers.

Gift this course