How to make a Pivot Table

Now that we know what a Pivot Table is, it's time to learn how to make one! In the following tutorial, we'll start with a basic data set, learn how to create a Pivot Table based on our data set, go over the basic features of one- and two-dimensional Pivot Tables, and then examine some more advanced options for Pivot Table creation and manipulation.

Preparing the data

Before we dive into making our Pivot Table, it's important to ensure that our input data is in the proper format. Pivot Tables are always generated based off of an initial table of Input Data, which can be any standard sheet containing rows and columns built in Excel. However, our Pivot Table will be most useful if our input data is in what we call flat file format. This will ensure that our data is structured in such a way that it will be easy to read and understand once inserted into a Pivot Table.

What is flat file format? Simply put, it's a table format in which data is broken down to its most granular level. Each column heading across the top of a table in flat file format should represent a type of data rather than a value. And each row should contain a distinct data point that makes up the data set.

That may seem a bit complicated, so let's take a look at a sample table to help explain things. The following is an example of a poorly formatted input table:

Although this is a perfectly reasonable summary table to create in Excel, it violates the first rule of flat file format: each column heading does not contain a type of data, but rather a data value. January, February, March, and April are not types or descriptors of data; they are months!

Our bad example above could be fixed by creating a Month column and breaking out individual data rows like so:

Notice that now, each column represents a data type (Customer name, Month, and Sales) rather than a value; and each row contains a distinct data point that makes up the data set.

Here are a couple of additional considerations when preparing your data for Pivot Tables:

• Use input data in flat file format
  • Build your data from top to bottom, not across. Each column should represent a type or characteristic of your data, and each row should represent an individual data point.
  • Make sure Each column has a single heading, and all pieces of data within that column are of the same type. For example, if you have a Date column, it's important to ensure that every value within that column is a date.
• Ensure that each column has a header; this header should accurately describe its contents and is easy to understand
• Remove all total and subtotal rows from your input data; otherwise, your Pivot Table will count them as individual data points
• Eliminate blank cells from your data set; this isn't necessary, but will help your Pivot Table identify data types

Making the Pivot Table

Now that our data is in the right format, we can move on to creating the pivot table itself. For this tutorial, we've expanded on the sample data set above, adding in some more granular detail on items ordered, quantity, price, and date:

To create a Pivot Table based off of this data, we'll first place our cursor anywhere within the data set itself. Then, we'll go to the Insert section on the Ribbon and select the PivotTable option:

The Create PivotTable dialogue box will open asking us to specify some options. Since Excel has already selected our data and the rest of the options work by default (e.g., we want our Pivot Table report to be placed in a new sheet), we can go ahead and just press OK on this screen.

Our Pivot Table is ready to go! Notice that Excel has created a new sheet; there is now a Pivot Table graphic on the left-hand side of the screen; and a PivotTable Field List dialogue has appeared on the right.

Note that our PivotTable Field List contains a summary of all the columns Excel identified within our input data set. Below, it also contains four sections:

• Report filter. This section allows us to filter our table by one or more criteria. For example, we can only show data in our Pivot Table for the month of January.
• Column labels. This section allows us to summarize data across columns, placing data labels along the top of the screen.
• Rows labels. This section allows us to summarize data across rows, placing data labels along the side of the screen.
• Values. This section allows us to specify what we're summarizing — for example, total sales or number of items ordered.

This may all seem a little complex, so let's move on to a real-life example of Pivot Tables in action to clarify things.

Creating a one-dimensional summary

Now that our Pivot Table is created, let's start by creating a basic summary of total sales by customer. First, add the Customer field to the summary. You can do this by either clicking the checkbox next to the Customer field in the PivotTable Field List, or by dragging the Customer field into the Row Labels section.

Once we've done this, notice a few things: first, the Customer field has appeared in the Row Labels section of our sidebar. Second, a list of customer names has appeared within our Pivot Table.

A list of customer names is nice to have, but not particularly useful for data analysis purposes. Let's make things more useful by dragging the Total Price field into the Values section of our Pivot Table:

Now we're getting somewhere! Our table has summed up the values in the Total Price column, segmented by customer. Notice that Excel has also automatically generated a Grand Total row that shows the total sales in our dataset: $730,000. Let's take our table one step further by dragging the Quantity field into the Values section as well:

Like magic, we have a summary of total price paid and total quantity of products ordered by customer. We didn't need to write any formulas or copy and paste any values — our Pivot Table has done all of the work for us!

Creating multi-dimensional summaries

The value of Pivot Tables doesn't stop at one-dimensional summaries. We can also create multi-dimensional summaries that cut our data based on two values rather than one. Here's how:

With our data set above, let's first remove the Quantity field from the summary by dragging it from the Values section back into the field list. Then, we'll take the Item field from the field list and drag it into the Column Labels section like so:

Notice that Excel responds by creating a two-dimensional summary table. Now, customer names are on the left-hand side of the screen, and item types are listed along the top. At the intersection of each customer name and item type, we see the total amount of the given product ordered by the customer in our whole data set. Excel automatically creates Grand Total rows to sum up totals for both rows and columns. And the grand total for the entire table aligns with the number we saw when working with a one-dimensional summary: $730,000.

Our multi-dimensional summaries don't stop with just two columns. Try dragging some of our other columns into the Row Labels or Column Labels sections. Or, drag our Quantity field back into the Values section. Our Pivot Table is infinitely customizable depending on how you'd like to summarize values!

Filtering our data

There's one last key piece of Pivot Table functionality that we haven't yet examined: Filters. Filters allow us to choose which pieces of data to either include or exclude in our Pivot Table summary. Here's how we use them:

Let's start by resetting back to our standard Pivot Table, which shows an overall summary of total sales by customer:

Now, grab the Item field from the field list sidebar and drag it into the Report Filter section of our Pivot Table, like so:

You'll notice that a new dropdown appears at the top of our Pivot Table. This dropdown will allow us to filter our report on individual items rather than seeing an aggregate summary of the entire input table. To see how it works, select Lollipops from the dropdown and press OK:

Our report now only includes sales of Lollipops to customers. Notice that our customer list has shrunk (since only two customers purchased lollipops in our data set) and our new grand total is down to $165,000:

We can also filter based on multiple values. Try opening up the filter, then clicking the checkbox that says Select multiple items. Doing so will allow us to check boxes to filter based on multiple items rather than a single criteria. Below, we've filtered on customers who have purchased either Lollipops, Cakes, or both:

Try playing around with filters some more to get a feel for how they work — and how you can use them to quickly and easily gather insights from Pivot Table data. Notice that we can drag as many fields as we want into the Report Filter section, so these filters can stack on top of each other practically infinitely.

Summarizing things other than SUMs

So far, we've been summarizing all of the data in our Pivot Table using the SUM function. But that's only the beginning of what we can do with Pivot Tables. We can also summaries fields using functions like COUNT, AVERAGE, MAX, and MIN. Combined, these functions create an infinite variety of uses for Pivot Tables.

Let's test this functionality by starting with our standard summary of total sales by customer:

You'll notice that in the Values section of our Pivot Table, the Total Price field we've added displays as Sum of Total Price. This is because by default, Excel will take the SUM of any field added to the Values section of a Pivot Table. But this function is just a default. Let's try changing it to COUNT to count total orders rather than sum their total sales.

First, click the small arrow next to Sum of Total Price. A dialogue will appear with several options; let's select Value Field Settings from the list.

In the Value Field Settings dialogue that appears, notice that SUM is currently selected. Let's change the selected function to COUNT, then press OK:

Notice that now instead of displaying the SUM of everything in the Total price column, Excel now shows the COUNT. We've just counted number of orders by customer!

Try playing around with the other options in the Value field settings dialogue. You'll see that we can also summarize fields using other functions like AVERAGE, MAX, and MIN. This feature allows us to easily adjust our Pivot Table to summarize data in any number of useful ways.

Grouping column headings

Let's say that we wanted to summarize sales by customer and month in our Pivot Table. Let's drag the Date field into the Column Labels section like so:

We've successfully summarized sales by customer and date. But the above output isn't particularly useful, is it? It shows our customers' sales by date rather than summarizing things on a monthly level. Is there a way to fix this problem?

There is — by using a Pivot Table feature called Column Groups. Let's take a look at how to leverage Column Groups to more succinctly summarize sales by customer over time.

With sales summarized by customer and date, right-click any date's header label and select the Group option from the list that appears.

A Grouping dialogue will appear that asks us how we'd like to group the columns in question. In this case, we'll ensure that the Starting at and Ending at dates include our entire data range (which they do by default); then, we'll click the Months option to summarize our dates on a monthly basis.

Click OK, and you'll see that Excel has now automatically grouped our dates by month. Our summary Pivot Table is now much more useful!

Dates are the most commonly grouped columns, but it's also possible to "bucket" numerical columns by grouping them in segments. Try dragging the Total price field to the Column labels section and grouping it by price range!

Those are the basics of how to make a Pivot Table in Excel! Questions or other thoughts on what we've outlined below? Be sure to let us know in the Comments section at the bottom of this page.

Comments