1. Preparing Your Data for Pivot Tables

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 this first lesson on PivotTables, we'll learn how to format our data for PivotTables, and create a new PivotTable. In our course on LOOKUPS and database functions, we took this sales status set for a software business, and created a search panel that allowed us to access the information we wanted using dropdown lists for variables such as salesperson, state, or month.

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

PivotTables 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, PivotTables are much quicker than search panels because they don't require you to write any complex formulas.

Over this course, will learn how to utilize the full power of PivotTables to gain valuable insights into the performance of our company and individual sales reps.

Our first step is to prepare our data for the PivotTable.

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

Data for PivotTables must be in this format. And while no blank rows can exist, blank cells are fine.

In previous lessons, we 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 its syntax is confusing and I find it makes formulas harder to follow.

However, with PivotTables, you generally don't interact directly with the data set after the PivotTable is created. So we can take all the positives of an Excel table without the negatives.

To create a table, select any cell in the data set and press Alt, HT to format a table.

Then simply select the style you want, and press enter. Confirm that the data set is correct, with the marching ends, and click OK.

This creates our table.

To create a PivotTable, simply select any cell on the dataset, navigate to the insert tab and select PivotTable.

First, make sure the data is selected correctly with the marching ends, and then choose where you want your PivotTable to be placed. We'll place it on a new worksheet. We'll click OK, and now the PivotTable is created.

If you don't want to use Excel tables, you could just select the entire data set and click insert PivotTable. 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 than the data underneath, before creating the PivotTable.

Excel Excel for Business Analytics Learning Plan
Data Analysis
Pivot Tables

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial