In this lesson, you will learn how to use Pivot Table in Excel.
How to insert the Pivot Table?
I have prepared sample sales data for an imaginary company.
The table contains columns such as:
- Date
- Product
- Category
- Customer
- CRM Customer Group
- Region
- State
- City
- Sales Manager
- Sales Rep
- Price
- Quantity
- Sales
To insert a pivot table, go to the Insert tab and click the Pivot Table button. You can also use the keyboard shortcut ALT + N + V.
To create a pivot table, select the range of cells you want to analyze. Excel will automatically identify the correct range in most cases. Click OK to create the empty pivot table on a new worksheet.
Populate Pivot Table With Data
To populate a pivot table with data, drag and drop the column labels into the four areas on the right side of the screen. You can experiment with different combinations to see how the pivot table changes.
Here are the four areas:
- Rows
- Columns
- Values
- Filters
The possibilities for creating different tables based on the same data set are endless. The only limitation is your imagination.
Here are some common rules to follow when creating a pivot table:
- Filters: Use this area to filter the data in your pivot table. For example, you could filter by region, date, or customer.
- Rows: This area shows the values of the columns you drag into it. For example, if you drag the “Region” column into the “Rows” area, your pivot table will show the sales for each region.
- Columns: This area shows the values of the columns you drag into it. For example, if you drag the “Product” column into the “Columns” area, your pivot table will show the sales for each product.
- Values: This area shows the calculations that are performed on the data in your pivot table. For example, you could calculate the total sales or the average sales.
The more you use pivot tables, the more familiar you will become with the different areas and how to use them.
You can put two different columns in the same area. This will create a more detailed pivot table, but it may also make it more difficult to read.
Let’s see an example: I created a basic report of sales products for customers.
Excel calculated many useful numbers in the pivot table. You can see the sales of each product for each customer. The pivot table also shows the total sales for each row and column.
Adding more categories of products to the pivot table will make it more detailed, but it may also make it more difficult to read.
Let’s see how to create better reports.
Formatting Pivot Table
Right-click on the data values and choose “Number Format” to format your pivot table.
I’ll choose the Currency format to show the values as sales revenues. I’ll reduce the decimal places to 0 to save space.
Number formatting makes the report easier to read.
Sort Pivot Table
To improve visibility even further, sort the pivot table by clicking on the bottom row and selecting “Sort Largest to Smallest”.
Sort the data in the last column in descending order.
This is how the report looks now.
The pivot table is now easier to read because the sales are sorted in descending order. This makes it easier to see the top-performing products and regions.
You can also sort the pivot table by other criteria, such as date or customer.
Keeping your pivot table in a logical order makes it look more professional and helps you to communicate your findings more effectively.
Pivot table filtering
A pivot table can be a very complex report, so you may need to filter the data. To do this, you can add filters to the pivot table.
In this example, I added filters for Region, State, and City.
The filter appears at the top of the pivot table. I filtered the data to show only sales from the Central region in Kansas.
The pivot table shows only sales from the Central region in Kansas. The data is sorted in descending order.
Slicer
Another way to filter a pivot table is to use a slicer.
A slicer is a tool that allows you to quickly filter the data in a pivot table by one or more fields.
To insert a slicer, click on a cell in the pivot table and then go to the Analyze tab.
You will see a window with the options for inserting slicers.
Choose any product you want. I will click on “Product” to see the list of products.
The slicer shows me some useful information even before I select anything. I can see that one product is grayed out, which means that there is no data for that product. I can select a product to see the filtered data quickly.
I can click on each product in the slicer to see how the pivot table data changes. The slicer filters the table very quickly.
To select more than one product, hold down the Ctrl key and click on the other products.
You can add more slicers to filter the data even further. To do this, click on a cell in the pivot table and then use the Insert Slicer button again.
I added another slicer to filter by customer.
My data is filtered the way I want it. If you don’t like the slicer, you can remove it by right-clicking it and selecting “Remove”.
You can also keep the slicer and remove its filter. To do this, click the Clear Filter button in the slicer.
Updating (refreshing pivot table)
Pivot tables do not automatically update when the source data changes. To update a pivot table, right-click it and select “Refresh”.
Updating the data in a pivot table takes just a second. The formatting will remain the same.
