Advertisement
  1. Business
  2. Microsoft Excel
Business

How to Create Your First PivotTable in Microsoft Excel

by
Difficulty:BeginnerLength:MediumLanguages:
This post is part of a series called How to Do Your Excel PivotTables Right (Mastery Guide).
How to Use PivotTables to Analyze Your Excel Data

We all deal with data overload. More than ever, we need easy ways to take a large dataset and find meaning inside of it. If you work with spreadsheets in Microsoft Excel, PivotTables are an ideal solution for data overload. Few tools are as easy to use and powerful as PivotTables.

With a PivotTable, you can point Excel to a list of data and easily summarize it in a few clicks. You can quickly segment your data, calculating and summarizing it by dragging and dropping fields.

PIvotTable Summary
This PivotTable was used to summarize a lengthy list of client billings into a single, easy-to-read report.

In this tutorial, I'll teach you how to create a PivotTable in Excel. At the end of this tutorial, you'll have the skills to summarize data in spreadsheets using one of Excel's most powerful features.

What's a PivotTable?

A PivotTable is a tool to make sense of a huge list of data. When you have a large spreadsheet with many rows, it's helpful to summarize your data in an Excel PivotTable.

Here's a simple example: you have a big list of clients you billed as part of your freelance work. Each row is a project or product we've billed a client for. At the end of the year, how can I review my work and see how I spent my time?

Data List
My spreadsheet contains a list of clients that I've billed for work over the last year, but it's tough to derive meaning from the data.

From a spreadsheet, it can be overwhelming to understand your data. What if I wanted to know the total amount I billed each client? Sure, I could filter by each client and sum it up, but there's a much easier way to do it.

A PivotTable helps us summarize and understand data. In the example below, I created an Excel PivotTable out of the spreadsheet, and summed up my billings per client. 

Excel PivotTable Example
PivotTable in Excel, example of client billings shown right.

The PivotTable, shown on the right, is a great way to summarize the data. I've shown each "Client" as its own row, and summed up my "amount billed." 

Notice that in the original spreadsheet, I've done several different rounds of work for a client named "Schoen-Jacobson." The PivotTable sums up my billings to them on a single line, which is very convenient.

You can drag and drop fields to slice the data and view it in a variety of ways. In the examples below, I've used the same PivotTable with different row types to understand my data better.

PivotTable in Excel Views
The same Excel PivotTable with different settings can summarize my data in different ways; in this case, I can see how my work is divided amongst clients, years, and types of work I've done.

In short, PivotTables are a tool to tame and understand the data in your spreadsheets with better. Learning how to create PivotTables in Excel will help you make sense of your data. Let's get started.

How to Create an Excel PivotTable (Watch & Learn)

Sometimes, you have to see a tool like PivotTables in action to think about how you can use it. Check out the video below to learn how to create a PivotTable in Excel quickly:

 

If you want a step-by-step guide on creating your first PivotTable in Excel, keep reading the tutorial to find out more. I'll walk you through how to create a PivotTable and understand your data better.

1. Prepare Your Excel PivotTable

If you want to follow along with this tutorial, download the example workbook that's included with this tutorial. I've filled in some data in a workbook so that you can create a PivotTable in Excel and follow along with this tutorial.

If you're using your own data, no problem. Before we create a PivotTable, we need to make sure that our data will work well in a PivotTable. Here are my keys to preparing data for a PivotTable:

  1. Each row of the data should be a record of its own. Basically, you can use each line for your data.
  2. Make sure that each column has a header, and ensure there are no blank columns in your data.

2. Create the PivotTable in Excel

We're ready to create our first PivotTable. Start by highlighting the columns that contain your data, and then find the Insert tab on the Excel ribbon.

With your data highlighted, click on the PivotTable button on the far left of the Insert tab to create your first PivotTable.

Insert PivotTable into Excel
Highlight the columns that contain your data and click on PIvotTable on the Insert tab to create your first PivotTable.

Now, a new window will pop up to finish creating the PivotTable in Excel. I typically leave these settings at their default, which will create a PivotTable on a sheet of its own. Go ahead and press OK, and Excel will create the PivotTable.

PIvotTable settings in Excel
You've already selected the table that includes your data. Leave the bottom option set to New Worksheet to place the PivotTable on its own tab of the Excel workbook.

3. Drag and Drop Your Fields

Once you've created the PivotTable, Excel will take you to the new sheet to build your PivotTable. I think of this screen as a drag-and-drop report builder. Here's how to read this menu:

On the right side of the screen is a menu labeled PivotTable Fields. You'll see each of the columns from your data in the list, which are called fields in a PivotTable.

At the bottom of the menu are four boxes: Filters, Columns, Rows, and Values. You can drag and drop any field into any of the boxes. 

PivotTable Report Builder
Excel PivotTable Report Builder.

Let's start off by dragging Client down into the Rows box. Click and drag Client from the fields list, and drag it down into the white box below Rows.

PIvotTable Rows in Excel
When we drag Client to the Rows box, the Excel PIvotTable puts each client on its own row.

Now we're getting somewhere! Because we placed the Clients field in the Rows box, the PivotTable put each client on its own row. 

This is how PivotTables work: you can drag and drop the fields into the various boxes, and the PivotTable will show that field in the box you drag it to. You can show a field as a Filter, Row, Column, or Value.

In the screenshot below, here's another quick example. I dragged and dropped the Project Type to the Columns field. Now, our Excel PivotTable shows each of the project types in our original data as a column of its own.

Columns PivotTable in Excel
This time, I dragged the Project Type into the columns field. This shows each of the project types in a column of its own.

I'll go ahead and undo these changes, and move Client back to the Rows box.

What if we wanted to know how much we billed each client? Remember that we have that in our original data, in the Amount Billed column. We need to add the amounts to the table as well.

Since we want to see those amounts as numeric values, let's drag Amount Billed to the Values box (lower right corner).

Client Count in Excel
Client Count in Excel PivotTable.

Now, we have a listing of the number of times that we've billed each client for work. The second column counts up each time we've worked with a client, and shows the count total.

However, we don't want to see the count of the projects we did for each client. We want to know the total dollars, or the sum of what we billed them instead.

Let's right click on the numbers in the second column. Find the option that reads Summarize Values By and choose Sum. You'll see now that the amounts are being summed up, and we can see the total dollars for each client.

Change to sum
After you've dropped the Amount Billed into the Values field, right click on the data and choose Summarize Values by > Sum to sum up the amounts per client instead of counting them.

We've built our first useful PivotTable, summing up our billings by client.  Now, let's add a second field to see even more detail.

What if we wanted to segment our data even further? Let's say that we wanted to see not only the client, but also the type of work that we did for that client. 

To do that, let's take the Project Type field and drag it down into the Rows box, and drag it above Client. 

Project Type added in Excel PivotTable
Project Type added in Excel PivotTable.

Now, our PivotTable has two levels of detail: it sums our work up by project type (Accounting, Legal Work, Screencasts, Training, and Tutorials), and then shows the client for each type of work. We've added two levels of detail to the Excel PivotTable so that we can see top clients for each type of work.

By now, you should have some ideas of how you might be able to use PivotTables to assist your freelance work or small business. If you have spreadsheets with data and need to understand them, try throwing it into a PivotTable and rearranging fields to understand the data more.

Recap and Keep Learning

PivotTables are one of the most powerful tools I know of in Excel. If you want to keep learning Excel skills, check out the tutorials below:

Do you have any questions about how to use a PivotTable? If so, let me know in the comments below.

Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.