Introduction to Pivot Tables – Part 1

Welcome to the first blog post in a series of three blog posts introducing Excel Pivot Tables. Pivot Tables are one of Excel’s most powerful features but they are perhaps also one of its most confusing to understand at first.

In this series of blog posts, I’ll aim to get you started with Pivot Tables, explain some of the basic functionality, and the importance of preparing your data. For this first blog post, we’ll start by explaining what a Pivot Table is, how to access the functionality, and get you started with a simple example.

What is a Pivot Table?

The purpose of a pivot table is to analyze large amounts of data and present it in a table format. To help you carry out this analysis, Excel provides you with various commands that allow you to manipulate and summarize data in different ways.

When you’re first starting out with PivotTables, you’ll probably only want to analyze data on a worksheet within your workbook. However, it’s worth noting that Excel allows you to pull data in from external sources such as a database for analysis.

Where to find the Pivot Table Functionality

OK, let’s jump in. The Pivot Table commands used to access Pivot Table functionality are on the Insert tab of the Ribbon; see the image below.
Pivot Table Commands on the Ribbon

Pivot Table Commands on the Ribbon

You will notice three commands in the highlighted area: PivotTable, Recommend PivotTables, and Table. In this blog post, we are going to start with the PivotTable command because this will take you through the process of building a PivotTable from the ground up. However, it’s worth mentioning what the other two commands are at this point for the sake of clarity.
  • Recommend PivotTables – If you click a cell in your table or list of data that you wish to analyze, then click the Recommend PivotTables command, Excel will then make certain assumptions and create a new PivotTable on a new worksheet, formatting the PivotTable as well as summarizing the data based on the assumptions it has made. This is great because it can save you a lot of time. However, for the purposes of this blog, it won’t help you learn the fundamentals.
  • Table – The Table command is not specifically a PivotTable command but rather a command that formats a list of data into a format that allows it to be manipulated in a similar way to a database table. Doing this is beyond the scope of this series of blog posts.

Preparing the Data

I like to prepare my data before creating a PivotTable. Preparing your data can save you a lot of confusion, and, therefore, a lot of time when creating PivotTable.

Think of each row in your list of data as a record. Each record should contain common attributes. For example, if it’s a list of addresses, you might have a street number, address line 1, address line 2, city, state, and postcode attributes. Each of these attributes of the address can be stored in different columns of your list. Each row should be a unique record that you want to analyze with your pivot table.

With the above in mind, I would recommend the following as a minimum.

  • Make the first row for each column a heading with a meaningful title. If you haven’t got a heading already, highlight the first row and insert a new row and add your headings.
  • Secondly, make sure that each cell has a formatted table type, for example, Currency, Number, Date and so on. Formatting the datatype is easily done by highlighting the cells in each column, (except the heading), right clicking and Cell Formats from the menu, and then selecting your data type from the category. There are other ways to do this, but I will leave it for another post. The formatting might not carry through to your Pivot Table but by doing this it will highlight errors.
  • Thirdly, where possible make sure each cell contains some data if possible/if relevant. Sometimes the lack of data in a cell is something you might be trying understand.

A Closure Look

Let’s take a closer look by creating our first PivotTables. In this example, I’m going to use some sales data for a sales team over a three-month period as shown in the image below.

Preparing Some Data

List - Sales Data

The list of data in the image above has four columns, “Month”, “Sales Person”, “Devices” and “Value”. The list of data already has headings and contains 48 rows of data from row 2 to 49. Each row represents the amount of sales made by a salesperson in a specific month for a specific device type.

The data type of each column is as follow;

  • Month – The Month column is data type “Date”.
  • Sales Person – The Sales Person column is data type “Text”.
  • Devices – The Devices column is datatype “Text”.
  • Value – The Value column is datatype “Accounting”.

Making the PivotTable

Now that the data is prepared, let’s make the PivotTable.  Select the range of cells that contain the data for the PivotTable, including the headings. In this example rows 1 to 49, and columns A to D, as shown in the image below.

Selected Range of Cells for a PivotTable

Now click the PivotTable command on the Insert tab of the Ribbon, shown in the image below.

PivotTable Command

When you do this, you should see a pop-up menu called “Create PivotTable,” as in the images below, for both Windows and Mac.

Create PivotTable pop-up menu Windows

Create PivotTable – Windows

Create PivotTable pop-up menu - Mac

Create PivotTable – Mac

This pop-up menu has a couple of different sections. The first is “Choose the data you want to analyze,” which offers a couple of different options: “Select a table or range” or “use an external data source.” For now, we’re only interested in “Select a table or range.” In the Textbox with the title “Table/Range” (highlighted in the image above), you see the range of cells that we selected earlier: A1:D49. You can change the range of cells at this point if desired.

The other option in the “Choose the data you want to analyze” section allows you to pull data into Excel from an external data source, as mentioned earlier in this post. Best not to bother with this option for now if you are not familiar with this type of activity.

The second section of the “Create PivotTable” pop-up menu is “Choose where to place the PivotTable.” The choices here are a bit easier: “New worksheet” or “Existing worksheet.” For this example, let’s go ahead and choose “New worksheet.”

Click the “Ok” button on the “Create PivotTable” pop-up menu. When you do this, you are taken to a new worksheet with a couple of new objects, as shown in the image below.

Blank PivotTable Worksheet

The first object on the left-hand side is an empty PivotTable. The second object on the right-hand side is a menu called the “PivotTable Builder.”

Let’s take a closer look at the “PivotTable Builder” menu. This is where most of the action happens.

PivotTable Builder menu

You should see five main areas in the PivotTable Builder menu, “Field Name” on the Mac or “PivotTable Fields” on Windows, “Filters,” “Columns,” “Rows,” and “Values.” The PivotTable Builder menu works on a drag-and-drop basis. You drag items from the “Field Name” (Mac) or “PivotTable Fields” (Windows) area to the other four areas. It’s important to note that you can drag a field to one or more other areas in the menu or not use them at all. Equally, each area can contain one or more fields or none at all.

The ensuing table explains what each area is for.

PivotTable Builder Menu AreaDescription
"Field Name" (Mac) or "PivotTable Fields"(Windows)Remember earlier how I said it was useful to give your columns a meaningful heading? Well, this where you're going to see them. Basically, in here, you should see all the fields (columns in the original list) that you can use when building your PivotTable.
FiltersThe filter area works in the same way as normal filtering;ÿif you drag a field into the filter area, this will add a dropdown menu to your PivotTable. You can filter out/select any rows from the original list by placing a tick next to the data in the dropdown menu. If a row in the original list contains matching data in the field you have selected, and you have a tick next to it, it will appear in your PivotTable; otherwise, it is excluded. Very useful when working with dates.
ColumnsThis area determines how your data will be broken down across the columns. The unique values from the column that corresponds to selected fields determine the column headings in the PivotTable
RowsThis area determines how your data will be broken down into the rows. The unique values from the column that corresponds toÿthe selected field determine the row headings in the PivotTable. I'm going to repeat that so you don't think I've typed it wrong. The unique values from the column that corresponds to theÿselected field determine the row headings in the PivotTable.
ValuesThis determines the summarized values that appear below the rows or after the columns. You can specify how the data willÿbe summarized; among the most common methods are "count" and "sum." There are other methodsÿthat we will cover later in this series of blog posts.

While the table above gives a summary of each area, it’s important to note that the number of combinations that can be achieved by dragging fields to different and multiple areas in the PivotTable Builder menu is enormous. This is part of the power of PivotTables, but it can also make it difficult to understand what is happening. So, we are going to start with just one field in each of the four areas, “Filters”, “Column”, “Rows” and “Values”.

In this example, we’re going to drag the fields into the PivotTable Builder menu sections as shown in the table below.

AreaField
FiltersDevice
ColumnsSalesperson
RowMonth
ValuesValue

When you’ve finished, the PivotTable Builder menu should look like the images below for either Mac or Windows.

PivotTable Builder menu Mac

PivotTable Builder menu – Mac

PivotTable Builder menu - Windows

PivotTable Builder menu – Windows

More importantly, we now have a PivotTable based on our original list, and it should look something like the image below.

PivotTable

Okay, this image is a little busy, so let me explain what’s happening. The rows based on the values from the Months column of our original list are highlighted in green. Even though there were multiple rows in our original list, there was only three unique values, June, July, and August.

Next, we have columns highlighted in a sort of sickly green/orange color. The columns are determined by the unique values from the Salesperson column in our original list. In this case, those are Geoff, Joan, Kerry, Lee, Nicky, and a happy accident, a Salesperson called Geof. Notice how Geof has sales for June and July but nothing for August. This is because “Geof” should actually be “Geoff,” but Excel identifies “Geof” as a unique value and creates an additional column.

This highlights both the power of PivotTables but also the need to prepare your data. If you wanted to, you could now go back to your original list, filter the list only to show rows with the value “Geof,” and correct them.

The area highlighted in red is the filter. The Filter (or filters–you can have multiple) is a drop-down list of unique values based on the Device column from the original list. If you click on the Filter, a drop-down menu will appear that looks something like the image below.

Filter Dropdown Menu

You’ll notice from the image above that there are ticks next to every value. If you untick one of the values, then the rows in the original list that contain the value in the Device column won’t be included in the PivotTable.

Finally, the areas highlighted in yellow are the sum of the Value fields for both the rows and the columns in the PivotTable. More importantly, where a Row and a Column intersect on the PivotTable, the values from the Value column in the original list are added together for every row in the original list that contains the same Month and Salesperson values of the intersection on the PivotTable. This concept is perhaps best explained with the help of the image below.

PivotTable Insect

PivotTable Insect

In the image above, where the column “Joan” intersects with the row “July”, the value highlighted in great is the sum of the values in the Value column of the original list for the rows that have the value “July” in the month column and “Joan’ in the Salesperson column. The image below highlights the rows in question from the original list.

Orginal List Highlighted Rows

In Next Blog Post on Pivot Tables

Pivot Tables are a hard concept to start with, so I think that’s enough for now. I hope you found this blog post useful. In the next blog post, we’ll take a look at what you can do when you have multiple fields in one area of the PivotTable Builder menu  and some of the different ways you can summarize the data.

If you want to learn more about Pivot Tables, I recommend myexcelonline.com Xtreme Pivot Course. This is the most complete course on Pivot Tables I have found on the Internet. Click here to learn more.

For other tools and resources for learning more about Excel, check out my resources page.

To get a copy of the example spreadsheet used in this blog post, sign-up for our newsletter below.

728x90

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit