Pivot tables are an essential tool for analyzing larger datasets and data tables. You’ve probably stumbled upon them before and immediately noticed how easy it is to find whatever you’re looking for in such a spreadsheet. It’s one of Excel’s flagship functions, and every aspiring Excel user should know how to create pivot tables. Considering how much data they can store in a more condensed format, it’s always a great skill to have – either for personal use or the company you’re working for.

Unfortunately, pivot tables have a reputation for being complicated and reserved for more advanced users. The truth is, creating the Excel pivot table is very intuitive and easier than you think. But before we show you the practice, let’s start with some theory. You must know what a pivot table is and why you might need to use one.

A pivot table is used to summarize your data in the form of a chart. It’s extremely useful when you have a large set of columns that you have to keep track of and compare their sums. Generally speaking, a pivot table can show you all kinds of descriptive statistics, grouping them together cleanly and intuitively.

By descriptive statistics, I mean count, sum, min, max, variance, standard deviation, etc. Now, with the theory out of the way, let’s focus on applications where you can create your own pivot table. This might come in handy if you don’t have access to Microsoft Excel.

Two applications are perfectly capable of creating pivot tables – Google Sheets and Microsoft Excel. The main difference between the two is that one is completely free, while the other is a part of the Microsoft Office family and costs a pretty penny in official distributions. Is it worth using one over the other? Let’s find out.

Excel is the father of pivot tables and the main tool for spreadsheet calculations in almost every company. The major advantage of using Excel for such tasks is accessibility. Excel has been around much longer than similar tools, which gives it an upper hand. 

Additionally, most people are familiar with Excel, so there’s no problem with file compatibility.  Excel pivot tables are in common usage, so it’s an obvious choice if you want to share your work with other people.

And if you’re afraid that Excel might be too expensive for you, we have some good news. At RoyalCDKeys, you can buy Microsoft Office 2021 Pro Plus Key Retail Global for only a fraction of the retail price. And you’re getting the full package, not only Excel.

Google Sheets is a part of Google’s cloud services which is very convenient for many businesses. Files are easy to share, and multiple people can edit the same document, while version control is way less trouble than Microsoft Excel. However, while some functions are similar to Microsoft Excel, the interface and workflow are completely different.

It means that users accustomed to Microsoft Excel will have a hard time figuring out the options they’re looking for. In summary, you can create a pivot table in any spreadsheet application you’re familiar with. It may be called differently, but the principles are the same. Our tutorial will focus on the most popular tool: Excel. It’s the best choice considering its popularity and ease of access.

Here are all the components that create a Pivot Table:

• Pivot Cache
• Values Area
• Rows Area
• Columns Area
• Filters Area

We’ll explain what all those components mean.

Excel takes a snapshot of your created data and stores it in its memory. So when you’re adding more values to the pivot table, Excel doesn’t go back to the data source but uses the Pivot Cache to analyze data and gives you results instantly. 

Thanks to that memory storage, it won't slow down even when you have thousands of rows worth of data. The only downside of this solution is the increased file size. Pivot Cache replicates the source data, and its copy is stored within it.

This is where your calculations and values are kept. So based on the previously mentioned example, if you want to calculate total sales by region each month, you can create a pivot table as shown above (we’ll cover that later). The area highlighted in orange is the Values Area.

The headings to the left of the Values area are the Rows area. In the example above, the Rows area contains regions as highlighted in red.

Filters are a useful tool to better organize your data set and hide unnecessary columns for better readability. For example, if you only want to see the sales from specific retailers, you can select that option as shown in the image above.

Let’s say you have to create sales data as shown above. As you can see, it shows the product, category, amount, date, and country. Now your boss wants to know a few things from this data:

• What were the total sales of vegetables in the UK in 2016?
• What are the top five products by sales?

You could answer these questions by using Excel functions, but what if your boss comes up with a list of more questions? Then, you would have to come back and create new formulas each time there’s a change. That’s why Excel pivot table layouts are such useful tools for analyzing data. They can answer any data-related question in a matter of seconds, so you don’t have to edit the spreadsheet each time.

Follow these steps to create a pivot table:

1. Click on a random cell.

2. Go to Insert → Tables → Pivot Table.

3. In the newly opened window, Excel automatically selects the data for you. After clicking OK, a blank pivot table will appear in a new worksheet.

Tip: The default settings should work fine when creating a new pivot table in Excel. However, there are some options you might want to take a look at:

• Table/Range. The default values are fine if you don’t have blank rows or columns. Excel will automatically identify the correct range. Otherwise, you can change this manually if needed.
• If you want your pivot table at a specific location, look under the option “Choose where you want the PivotTable report to be placed.”

4. Click OK, and a new Pivot Table will appear.

After creating a new Pivot Table, you won’t see any data in it. You’d only see the single-line instruction on the left and Pivot Table Fields on the right.

In PivotTable Fields, you must drag the following fields to the different areas.

• Product field to the Rows area.
• Amount field to the Values area.
• Country field to the Filters area.

After setting everything, click Update, and you’ll create a simple pivot table. As you can see, everything is calculated in the summary table automatically. Look at the example below. Now we will focus on bananas, our main export product.

First, we must get our bananas at the top of the list. To do that, you have to sort the pivot table.

1. Right-click any cell inside the “Sum of Amount” column.

2. Select Sort and Sort Largest to Smallest. See the result:

And that’s it. You have sorted your vegetables based on the export value – from largest to smallest.

Now we have to make use of that Country field we added previously. You can filter your pivot table by country. For example, which products do we export the most to France? Click on the filter drop-down and select France. Then you’ll see that apples are the main export product to France. 

Tip: Use the standard filter (triangle next to Row Labels) to show only the amounts of selected products.

By default, Excel is set to count and summarize the pivot table data. However, you can change the type of calculation that you want to use.

1. Click on any cell inside the Sum of Amount table.

2. Right-click and choose Value Field Settings.

3. Choose the type of calculation. In this example, we’ll choose Count.

4. After clicking OK, you’ll see the result.

When you drag a field to the Rows and Columns area, you will create a two-dimensional pivot table. To do that, insert a pivot table as previously mentioned. Then, to get the total amount of each product exported to different countries, you must drag the following fields to different areas.

1. Country field to the Rows area.

2. Product field to the Columns area.

3. Amount field to the Values area.

4. Category field to the Filters area.

Your newly created pivot table should look like this.

You can take this process a step further and create a pivot chart. It’s a bit more complicated than a pivot table, but it will show you another useful feature that Excel offers.

A pivot chart is a visual representation of your previously created two-dimensional pivot table Before you start, make sure your pivot table looks like just in the picture above. In this section, you’ll learn how to create a pivot chart that shows your data in a graphic form.

1. Click any cell in your pivot table.

2. Above, on the PivotTable Analyze tab, select PivotChart.

3. From the Insert Chart box, click ok.

Your pivot chart should look like the picture below. Any changes you make to the pivot table will automatically reflect in the pivot chart and vice versa.

You can decide which elements from your pivot table to show on the pivot chart. Follow the steps below to learn how to filter the information shown on your chart. 

1. Use the standard filters (triangles next to Product and Country). For example, you can use the Country filter to only show the total amount of each product exported to the US.

2. Remove the Country filter.

3. You can filter this chart by category with the Category field in the upper left corner. For example, use the filter to only show the vegetables exported to each country.

You can change the appearance of the pivot chart anytime you like. Here’s how to do it.

1. Select your chart.

2. Go to the Design tab and click Change Chart Type in the Type group.

3. In this example, choose Pie and click OK.

You can experiment with different charts and choose one that works best with all the data in your chart. It’s also a good way of understanding how each of them works.

This article showed you how easy it is to create a pivot table out of the existing data. Excel does almost everything by itself, and all you need to do is provide it with specific information. With pivot tables, you can easily sort the retailers based on the sales figures and then visualize it with a pivot chart. Even the basic data summary using pivot tables and charts will make your life easier as an aspiring data analyst.

There is much more to be written about pivot tables, but it's best to start with the basics. Pivot tables are very versatile due to dynamic updates, where every created table updates automatically. Data summaries paired with some tables and graphs are intuitive to use and pleasant to look at, especially for your clients. Mastering pivot tables will increase the value of your services immensely.