In this lesson, you will learn how to use pivot tables in Excel.
What is the Pivot Table?
The Pivot table is a powerful analytical tool in the form of a table. The biggest advantage is that you are able to freely 'rearrange' data. Thanks to it, you can see in our data trends, patterns, and comparisons that are difficult to find by browsing them manually. They are indispensable in creating summaries and reports, which is why many employers are looking for people who can use them.
If you have a large amount of data, it can sometimes be difficult to analyze all the information on a worksheet. Pivot tables can make it easier to manage worksheets by summarizing the data and allowing you to manipulate it in different ways.
Unfortunately, many people do not use Pivot Tables, mainly for fear of the advanced nature of their actions. Fortunately, it's not that difficult. Pivot Tables do not require special knowledge, and their construction can be understood even by novice Excel users. It is only necessary to bear in mind a few basic assumptions that I will present in this tutorial.
Data Preparation
Before you start creating a Pivot Table, you should verify that the data you are analyzing is properly organized. Things you should check:
- Column headings - if they are unique, they are placed at the very top, in a single row. Columns can't be empty.
- Rows - each one of them has to have the same number of columns.
- Cells - none of the cells are merged.
- Separation - the data to be analyzed should be separated from the others by at least one empty column and one empty row.
- Data meaning - each row is an item (city, person, product) and each column is information about that item (date, name, amount, size).
- Common parts - in order for the use of the Pivot Table to bring real benefits, at least in some columns there should be repeated elements – according to these elements can be grouped
- Avoid empty rows - blank rows will cause only part of the table to be used in the Pivot Table.
- One data type in a column - the Pivot Table performs some operations only on a specific data type (e.g. only on dates or only on numbers). If you try to group dates and at least one cell will be another type, you won't be able to group them because of the type inconsistency.
- Valid data type in a column - sometimes the data looks correct, but it is not at all. Be careful, especially after getting data from an external source. Make sure that the numbers are real numbers and not text. It matters because Excel is not able to sum up text.
- Subtotals are not allowed. Make sure your table does not contain them and any rows with a summary of parts of the data.
How to insert the Pivot Table?
I have prepared sample sales data for an imaginary company and used it as an example.
My table contains columns such as:
- date
- product
- category
- customer
- CRM customer group
- region
- state
- city
- sales manager
- sales rep
- price
- quantity
- sales
Inserting of the Pivot Table begins with a ribbon. Go to the Insert. Pivot Table is the first button on the left. Press it and select the Pivot Table.
Another possibility is to use the keyboard shortcut ALT + N + V.
To create a pivot table, you just need to choose the proper range of cells. In most cases, Excel identifies the correct range automatically. After double-checking, click OK to get the empty pivot table on the newly created worksheet.
Populate Pivot Table With Data
On the right side of the screen, you can see the labels of your columns (Pivot Table fields) and the 4 areas to decide. This is basically the window where you will be creating your pivot table. Thanks to a few drag and drop moves, a pivot table will group your data and perform calculations for a given data set.
If you are doing this for the first time, it might be a good opportunity to practice a bit. Try to drag your columns and drop them in the areas. See what happens and how your pivot table changes.
As you can notice to this point, there are endless possibilities to create different tables based on the same data set. The only limitation is your imagination.
There are common rules which you may follow when creating the real pivot table:
- Filters: This is where you can put the column that will be the filter on your table. It may be a person, a date, a region etc. After enabling the filter, you will filter the table just for this person/date/region.
- Rows: You shouldn't have problems with this one. This is the core of the table. Just put in the column for which you want to build the table. You would like to check the sales for regions you put in regions, number of orders for states you put in states, the average revenue for the salesman you put in the salesman and so on.
- Columns: If Rows are for WHAT to check, then Columns will be for WHERE to check. Report for regional sales of products – you put products in Columns.
- Values: In most cases, you will use it for sales or numbers of something (orders, measurements, etc.).
The more you try this, the more confidence you will get.
Please note that you can put two different columns in the same area. The pivot table will expand a lot, but reports will be more detailed as well.
Let's see the examples. First, I prepared the basic report of sales products for customers.
You may notice that Excel calculated many useful numbers here. You can see sales of every particular product for customers. Rows and columns are also summed up.
There is a basic pivot table. You could build bigger ones.
After adding additional categories of products, the pivot becomes more detailed but also more difficult to read.
Let's move on to see what you can do to create better reports.
Formatting Pivot Table
The first thing I'd like to improve in my pivot table is number formatting. Right-click on the data values and choose Number Format.
I'd pick the Currency format to identify values properly as sales revenues. To save space in the report, I'm reducing the decimal places to 0.
Number formatting brings benefits. The report is looking much better now. There is better visibility of data.
Sort Pivot Table
Visibility is better, but there is a simple method to increase it even more. Sorting a pivot table is the method.
Click in the very bottom row with your mouse button and pick Sort and 'Sort Largest to Smallest'.
Do the same in the last column.
This is how the report has changed now.
You can spot the difference. It is much better to read this pivot table now. The sales decrease from left to right and from top to bottom. It is easier to pay attention to the data that is closed. It makes you look more professional to keep your pivot table in such order.
Instead of sorting sales data, you can use another kind of sorting or even sort manually.
Pivot table filtering
In such form, this is a very complex report. You may need to filter the data. Let's add some labels to the filter area. I decided to add Region, State and City.
The filter showed up at the very top of the pivot table. I decided to focus on the sales data from the Central region and only for Kansas. Also, I have the possibility of filtering through the City when needed.
This is how my pivot table changed. Only Central/Kansas is here. Please note that the data is still sorted in the correct order.
Slicer
Another way of filtering the Pivot Table is to use the Slicer.
The Slicer is a tool that literally slices your pivot table. Use the Slicer to quickly filter out data you don't need. You can, for example, use a slicer during a presentation, when your boss asks, or to double-check something on the fly. Thanks to Slicer, you can only see data for a particular country, region or customer.
To insert the Slicer, click on a cell on the pivot table. Next, you go to Ribbon to Analyze Tab.
Choose the Insert Slicer button.
You will see a new window Insert Slicers.
Pick whatever you like. Let me click Product to see the list of products.
From the Slicer itself, I can see useful information already. Even before accepting it. I can see one product grayed out. That means that there is no data about this product. I can pick one of the products and quickly see filtered data.
At this point, you can click on each of them and see how your pivot data changes. The Slicer filters your table very quickly. It's amazing!
If you want to pick more than one, just hold the CTRL key and click on another product.
Isn't it enough for you? Add another Slicer, then.
To add a second Slicer, do the same. Click a cell in the pivot table and use the Insert Slicer ribbon button again. Another Slicer will appear.
This time I added the Customer filter.
My data is filtered out as I wanted.
You don't like the Slicer? No problem. You can remove it. Just right-click the Slicer and click the Remove option.
There is also the possibility to keep the Slicer and remove its filter. Then you should just use the Clear Filter.
Updating (refreshing pivot table)
One thing you need to remember is that the Pivot Table does not refresh automatically (by default) every time your source data table changes. This would consume too many Excel resources. To update pivot table data, you just need to right-click your pivot table and choose the Refresh option.
It takes just a second to update the data. Pivot table formatting remains the same.
There is much more you can do with your pivot table:
- Create a pivot table from Multiple Sheets
- Add calculated field
- add pivot average
- and much more