1. Preparing Your Data for Pivot Tables

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Pivot Tables

12 lessons , 3 exercises

Preview Course

Overview

You need to arrange your data in a certain format before creating a PivotTable. Learn how to do so in this lesson

Summary

  1. Overview of Pivot Tables (00:04)

    Like a search panel, Pivot Tables are a method of allowing colleagues access information they need from a data set without being familiar with Excel. Pivot Tables require greater knowledge of Excel than a search panel, and are somewhat less customizable. However, they are quick to create as you don’t need to write complex formulas.

  2. Preparing the Data (00:54)

    To create a Pivot Table, individual date records should be arranged in rows, and each column should have a header in the top row. There can be no blank rows, but blank cells are acceptable.

    In this course, we also create an Excel table for our data. This provides names for each column in the data set, and the data set as a whole. It’s an alternative to the Name Manager we use in other courses. Excel tables update automatically when new rows are added to the data, and create a nice formatting. Excel tables are best used when you’re not likely to interact with the data set directly, which is usually the case when you create a Pivot Table.

  3. Creating a Pivot Table (02:00)

    We start by creating an Excel table, by selecting any cell in the data set and using the shortcut Alt, H, T. Then we can create a Pivot Table by selecting a cell in the data set and pressing Alt, N, V to insert a Pivot Table. We choose to put the Pivot Table on a new sheet.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           

    It’s possible to create a Pivot Table without first creating an Excel table. However, you’ll need to update the selected area manually if you add more data to the data set. For this reason, it’s best to use an Excel table to create the Pivot Table.

Transcript

In our course on lookups and database functions, we took this sales data set for a software business and created a search panel that allowed us to access the information we wanted using drop down lists for variables such as sales person, state or month.

This allowed colleagues who are not familiar to Excel to easily access the information they needed from the data set.

Pivot tables are simply an alternative to this type of search panel.

They require a greater knowledge of Excel on the part of the user, and they're not quite as customizable as a search panel.

However, Pivot tables are much quicker than search panels because they don't require you to write any complex formulas.

Over the next dozen or so lessons, we'll learn how to utilize the full power of Pivot tables to gain valuable insights into the performance of our company and individual sales reps.

Our first step is to prepare our data for the Pivot table.

Individual data records should be arranged in rows, and each column of data should have a header in the top row.

Data for Pivot tables must be in this format, and no blank rows can exist, although blank cells are fine.

In previous lessons you might have seen me use the Name Manager to create names for columns or rows of data.

In this course, we're going to use an alternative method called Excel tables.

The tables feature was added in Excel 2007, and basically serves to provide names for each of your columns and the full data set.

It also automatically updates the size of the selected area.

when you add a new data entry. And it formats your data set nicely.

When performing calculations on a data set, I tend to stay away from tables because the syntax is confusing, and for me it makes the formulas harder to follow.

However, with pivot tables, you generally don't interact directly with the data set after the Pivot table is created.

So we can take all the positives of an Excel table, and without the negatives.

To create a table, select any cell on the data set and press Alt + H T to format a table.

Then simply select the style that you want and press Enter.

Confirm that the data selected is correct with the marching ants and click OK.

And this now creates your table.

To create a Pivot table, simply select any cell on the data set and go to Insert, Pivot Table.

First make sure that the data is selected correctly with the marching ants, and then choose where you want your Pivot table to be placed.

I’ll place it on a new worksheet, so I'll click OK and now my Pivot table is created.

If you don't want to use Excel tables, you could just select the entire data set and click Insert Pivot table.

However, the selected area will need to be updated manually after you add new data to the data set.

Also, if you go down this route, make sure the headers are formatted differently to the data underneath before creating the Pivot table.