How to Create Pivot Tables in Excel

What Are Pivot Tables?

Pivot Tables are the most powerful tool for analyzing data in Excel. With Pivot Tables, you can uncover discreet relationships. They allow you to compare variables using filters and functions. Simply put, they make reading your data quick and easy. That saves you time and money.

You might have a basic understanding of pivot tables, but how do they work? How can you make one? How can you use pivot tables to speed up your business?

You’ll learn all that and more.

Before You Make Your Pivot Table

The best pivot tables have a good data source. Make sure your spreadsheet is clean and clear. It’s also important that you have an objective in mind before you start. What information do you want to understand? Here’s a short list of things to do beforehand.

• Add descriptive titles to row 1, and only row 1.
• Remove empty columns and rows.
• Convert all values to their proper format (dates, times, currencies, etc.)
• Merge duplicate data. There should be one column per data type and one row per related data set.
• Now you’re ready to get the most out of your data!

I’m using this data set from Excel Campus. It contains sales data for a wholesale food and beverage company. I want to see how each sales team-member is performing, but I want that information separated by the state they made the sale in. That will show me if there are markets where we’re underperforming.

How to Create a Pivot Table in Excel

Making a pivot table is as simple as clicking a button. Getting value and insights is the real challenge. Here is a step-by-step guide to creating your pivot table.

1. Click the Pivot Table button under the Insert tab. It’s second from the left in the menu.

2. You can leave the Table/Range as-is unless you want to only analyze a subset of your data. Excel automatically detects the range of your data. You can see here that my data set has 26 variables (columns A through Z).

3. A new worksheet has been created with an empty pivot table and, on the right-hand side, a menu of the variables from your data set along with the four pivot table functions is showing.

4. Now drag the first variable you want to analyze into the rows box. I’ll choose Salesperson because that is the main variable I want to consider. Right away, the unique values for that column appear in a list.

5. Add another variable into the boxes. Because I’m interested in seeing sales performance, I’ll drag Revenue into the values box. Excel automatically calculates the total revenue sold by each salesperson and displays it next to their name.

6. Continue to add variables to make insights clear. Finally, I’ll add States to the columns box. The pivot table then shows the revenue each salesperson earned, and in which state they made the sale. That allows me to see that Nancy Freehafer had the most revenue, but Jan Kotas in Colorado is falling behind.

Those are the very basics of creating and using pivot tables in Excel, but there are a few other handy tricks that help make pivot tables the most powerful analytical tool in Excel.

Other Useful Tips

These useful tricks will help clarify your analysis. Hopefully, we’ve provided enough information to let you experiment with pivot tables on your own. Find out what works best for your business and start using pivot tables in your decision-making process.

Value Field Settings

When you place a variable in the value field of your pivot table, it automatically sums the values for that variable. If you want to perform a different summary function, you can. All you have to do is click your variable and select value field settings. You can then choose from several options including count, average, and max.

In my example, if I wanted to see how many sales each salesperson made, and not how much revenue they earned, I could switch the value field setting to count. Now my table shows each salesperson and the number of sales they made in each state. 

While Jan Kotas had the lowest overall revenue, Robert Zare’s 3 sales in Utah were the fewest for any salesperson, and Nancy’s 2 sales in Idaho were the fewest in any state. Now I can see we could invest more in Idaho and Utah because the few sales made were enough to make decent gains. Also, we want to leave Colorado where we made the least revenue and only four total sales.

Sort by Value

Another great way to make your data more readable is the sort by value feature. You can access the sort by value dialogue box by clicking “Sort by Value…” in the drop-down menu next to one of your variables.

Then you can select a variable from your values field to sort by. In my example, you can see that Nancy Freehafer made the most sales, and New York and Illinois are tied for the state with the most sales.

More on Sheetgo

At Sheetgo, I wrote for their blog, website and product. To see more of the writing I did while at Sheetgo, visit my author page. You can also check out some of the ghost writing I did, this high ranking blog on the Importrange function, for example, or this piece on Google Sheets templates.