1. Preparing Your Data for Pivot Tables
You need to arrange your data in a certain format before creating a PivotTable. Learn how to do so in this lesson
Preparing your data or Pivot Tables
- Data entries must be arranged in rows
- Each column must have a heading
- No blank rows should exist in the Pivot Table
- Format As Table ensures that new data entries are automatically added to Pivot Table
Alt + H, T: Format dataset as table
CTRL + Page Up/Page Down: Move to next sheet
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.