Locked lesson.

Upgrade

Quick reference

PivotTable Recap

A recap on how to create a basic PivotTable.

When to use

We use PivotTables when we want to easily organize and analyze large datasets. 

Instructions

PivotTables are one of the best tools in Excel for analyzing and making sense of large amounts of data. PivotTables allow us to take thousands of rows of data and organize them into a dynamic table which we can manipulate in different ways to extract the results we need. 

It's important to ensure that our data is clean before we attempt to analyze it with PivotTables. We also recommend formatting the dataset as an Excel table first and giving it a meaningful name. 

In this example, our data is already formatted as a table called 'SalesData'. 

Create a PivotTable

  • Click anywhere in the data. 
  • From the Insert tab, in the Tables group, click PivotTable. 
  • From the PivotTable from table or range dialog box, ensure the Table/Range is correct and select New Worksheet. 
  • Click OK. 

This will create a blank PivotTable report on a new worksheet. 

Notice the PivotTable Fields pane on the right-hand side and the new contextual ribbons: PivotTable Analyze and Design. 

The PivotTable Fields Pane

The PivotTable Fields pane shows the column headings from our dataset in the top half and then 4 field areas below: Filters, Columns, Rows and Values. 

We can drag and drop any of the column headings into any of the areas at the bottom to build our PivotTable report in different ways. 

Analyzing Data

When thinking about which fields to drag into which areas, we need to think about what type of analysis we want to do. What questions do we need answered?

Maybe we want to see a summary of the 'Total Revenue' by 'Country'. 

  • Drag the Country field to the Rows area. 
  • Drag the Total Revenue field to the Values area.

The report will show a list of all countries and a sum of the total revenue. 

Pivoting the Fields

The best thing about PivotTables is their flexibility. If we decide we need to do a different analysis, we simply need to pivot the fields or remove them. 

For example, maybe now we want to see the 'Total Costs' by 'Country'. We can drag the 'Sum of Total Revenue' field out of the Values area to remove it and replace it with the 'Total Costs' field. 

We now want to see a report that shows the total costs by country and item type. 

  • Drag the Item Type field into the Columns area. 

We can stack fields on top of each other in the same area. For example, we could move the 'Item Type' field above the 'Country' field in Rows to achieve a different report layout. 

  • Drag Sales Channel to the Columns area. 

  • Drag the Sales Channel field to the Filters area. 

We can now filter our PivotTable by Online or Offline sales. 

  • Drag the Sales Channel field back to Columns. 

Basic PivotTable Formatting

Number Formatting

We can apply number formatting so numbers in the PivotTable display as we want them to. 

  • Right-click on any number in the PivotTable. 
  • Click Number Format. 
  • Choose Currency format and ensure the decimal places are set to 2. 

Turn off Grand Total

We can choose if we want to display the Grand Total in our PivotTable. By default, the Grand Total is turned on. 

  • From the Design tab, in the Layout group, click the drop-down next to Grand Totals. 
  • Choose Off for Rows and Columns. 
Rename Rows and Column Labels
  • Double-click in the column label field and type a new name. 

PivotTable Styles

We can make our PivotTable more interesting to look at by adding a PivotTable style. 

  • Click in the PivotTable. 
  • From the Design tab, in the PivotTable Styles group, click the drop-down to expand the Styles gallery. 
  • Choose a PivotTable style. 
  • Customize the style by making selections in PivotTable Style Options. 

Hints & tips

  • If you want Excel to recommend a Pivot Table for you, remember to click Recommended Pivot Tables to see some suggestions. 
Login to download
  • 00:04 Pivot tables are by far one of the best utilities that we have in Excel for
  • 00:07 analyzing data.
  • 00:07 And as a business analyst,
  • 00:09 it's extremely important that you know not only how to create pivot tables,
  • 00:16 but also know how you can manipulate them in the best way.
  • 00:20 And also understand some of those advanced tips and
  • 00:23 tricks when it comes to really getting the most out of your data analysis.
  • 00:27 And that's pretty much what we're going to cover in this section of the course.
  • 00:31 Now, we're going to start out by doing a pivot table recap, because I understand
  • 00:35 that not everybody coming to this course might be familiar with pivot tables.
  • 00:39 So we're not going to dive straight into the advanced tips and tricks,
  • 00:43 let's just recap so that we're all at the same level and
  • 00:46 we understand the basics of pivot tables.
  • 00:48 Now I'm going to use some data that we've already used in a few lessons in this
  • 00:52 course.
  • 00:53 And this is basically sales data for different regions,
  • 00:56 different countries around the world.
  • 00:58 And I want to delve into this data and do a deeper analysis.
  • 01:02 Now, as I mentioned previously, the most important step, first of all,
  • 01:06 is to make sure that you're starting with a clean dataset.
  • 01:09 And you also want to make sure that you put your data into an Excel table.
  • 01:14 So Ctrl+T to put it into a table.
  • 01:15 And then if you want to go a stage further,
  • 01:18 up to Table Design and give your table a name.
  • 01:22 So you can see here I've called mine SalesData.
  • 01:25 It just makes it a lot easier for you to identify later on.
  • 01:29 So from this point, let's create our pivot table.
  • 01:32 Now there are two places where you can go to insert a new pivot table into your
  • 01:37 workbook.
  • 01:38 Now, if you're already on the Table Design ribbon, which a lot of the time you will
  • 01:42 be because you've normally just created a table and named it,
  • 01:46 we have a Summarize with PivotTable button just here.
  • 01:49 Alternatively, you can go to the Insert tab, and
  • 01:52 we have a PivotTable button in the Tables group.
  • 01:56 Now notice that we also have Recommended PivotTables just here.
  • 02:00 And that is basically where Excel gives you some suggestions of pivot tables you
  • 02:05 might want to create based on your data.
  • 02:07 And the most useful will be listed at the top.
  • 02:10 So looking at my data, Excel has determined that I
  • 02:13 might want to create a pivot table which shows the sum of units sold by region.
  • 02:18 The next one is sum of unit price by region.
  • 02:21 The next one, sum of total cost, total profit, so on and so forth.
  • 02:25 So if I want a really quick way to create a pivot table which already has the fields
  • 02:29 in place, I could choose one of these recommended pivot tables.
  • 02:33 Now we're not going to do that, we're going to create one from scratch.
  • 02:37 So let's just click the PivotTable button.
  • 02:38 Now notice here it's picked up my table range.
  • 02:42 And because I've named my data range, because it's in a table called Sales Data,
  • 02:47 it's automatically picked up my Sales Data table, which is perfect.
  • 02:51 I then choose where I want to put this pivot table.
  • 02:54 Now, in general, I always put mine on a new worksheet because I like to
  • 02:58 keep them separate from the source data, much less confusing.
  • 03:02 So let's click on New Worksheet and click OK.
  • 03:04 And this is where we get our blank pivot table report.
  • 03:07 Now I've got this little floating fields menu just here.
  • 03:10 You might find that you have it docked all the way over to the right-hand side of
  • 03:14 the screen.
  • 03:15 Now, sometimes that's a bit of a pain to have to keep moving your mouse to
  • 03:19 go over there, so you can pick it up and just bring it a little bit closer.
  • 03:22 And obviously, you can lengthen this out and widen the little panel.
  • 03:27 So I'm going to put mine just here so it's a bit closer to my pivot table report.
  • 03:32 So currently, we just have a blank pivot table report that we need to build.
  • 03:37 Now, how you build your pivot table is really determined by the type of analysis
  • 03:42 that you want to do.
  • 03:43 So what is important to you?
  • 03:45 What is important to your manager or
  • 03:47 whoever you're going to present this data to?
  • 03:49 What questions do they want answered by your data?
  • 03:53 All of these things you should determine before creating your pivot table.
  • 03:57 Now in this little floating PivotTable Fields area, we have in the top half of
  • 04:02 the screen all of the column headings from our source data set.
  • 04:06 And then underneath, we have four different areas that we can effectively
  • 04:10 drag and drop these headings into.
  • 04:13 We have Filters, Columns, Rows, and Values.
  • 04:16 So what type of analysis do we want to do of this data?
  • 04:19 Well, maybe the first analysis that I want to do is by Country.
  • 04:23 So I'm going to drag Country down into Rows.
  • 04:26 Now as soon as I do that, take a look at the pivot table report.
  • 04:30 It now has a list of all of the countries in the rows.
  • 04:33 And maybe I simply want to see what the Total Revenue has been for
  • 04:37 each of the countries.
  • 04:39 So I can drag Total Revenue down into Values and I get my results.
  • 04:43 Now, currently these numbers aren't formatted,
  • 04:46 we're going to do that in a moment, but that is a very basic pivot table analysis.
  • 04:51 And what I can do is I can pivot these fields and
  • 04:53 move them around to display my data in different ways.
  • 04:57 So maybe, instead of Sum of Total Revenue, I can remove that field simply by
  • 05:01 dragging it out, maybe I want to see the Total Cost by country.
  • 05:05 Now maybe I also want to see the Total Cost by country and Item Type.
  • 05:10 Now I could drag Item Type into Columns, and
  • 05:13 it's going to list the item types running across in those columns.
  • 05:17 I could even drag Item Type to the Rows area underneath Country to get
  • 05:22 a completely different layout.
  • 05:25 Now my data is organized by country and then by item type.
  • 05:30 Maybe I want to add in the Sales Channel into the Columns area.
  • 05:36 So now I can see my data summarized by country, by item type, and
  • 05:40 also by the sales channel, whether it was offline or online sales.
  • 05:44 I could even move Sales Channel into the Filters area,
  • 05:47 which gives me this handy little filter at the top.
  • 05:49 And I can say, okay, I only want to see all of the online sales.
  • 05:56 Let's click on OK and my pivot table updates.
  • 05:58 So there are so many different ways that you can pivot these fields to perform
  • 06:02 different types of data analysis and really answer the questions that
  • 06:06 are important to the people who are going to be looking at this pivot table report.
  • 06:10 Now just a couple of basic things in here before we move on to the next lesson,
  • 06:14 where we're going to look at some advanced pivot table tips and tricks.
  • 06:18 Remember, if you want to change the formatting of your numbers,
  • 06:22 simply right-click, go to Number Format, and
  • 06:24 then I can say that I want to apply a currency format with two decimal places.
  • 06:28 Let's click on OK, and everything is nicely formatted.
  • 06:32 If I want to remove this Grand Total column, again, I can go up to the Design
  • 06:37 ribbon, into Layout, and I can say, Turn off the Grand Totals for
  • 06:41 rows and columns, and that's going to remove that data.
  • 06:44 I can even rename these row labels and column labels if I want.
  • 06:49 So these column labels are basically showing the sales channel, so it's
  • 06:53 going to make more sense if I double-click and change this to say Sales Channel.
  • 06:58 I can double-click and change these Row Labels to Country/Item Type.
  • 07:02 And if I want to make my report a little bit more readable,
  • 07:06 I could do things like add banded rows.
  • 07:09 I could change the pivot table style to make it look really nice and funky.
  • 07:14 And if I want these subtotals to show at the bottom instead of the top,
  • 07:18 once again, we have a Subtotals dropdown and
  • 07:21 I can say show all subtotals at the bottom of the group.
  • 07:25 So very quickly, within five minutes or so, we've managed to create
  • 07:29 a really good analysis of thousands of rows of data using a pivot table.
  • 07:33 So hopefully, that's a good recap of the basics.
  • 07:36 In the next lesson, we're going to move on to taking a look at some
  • 07:40 more advanced tips and tricks when we're working with our pivot tables.

Lesson notes are only available for subscribers.

Gift this course