Last Updated on
Pivot tables are particularly useful for anyone who needs to analyze data in Excel frequently. Here we look at pivot tables, and how to make one, showing you how to make the most of this tool by filtering and customizing too. If you don’t know what a pivot table is, or how to make the most of one, you will do by the end of this article.
What Is A Pivot Table
A pivot table is a data analysis tool that people can create in Excel. Pivot tables are typically used as a way to share information as they can be used to sort, group, reorganize, count, summarize, average or total data that is stored within a table.
Advanced calculations can be used, as well as columns and rows being flipped into different formats.
They are also a quick way of seeing any inconsistencies in your data or typos, helping your work to be more exact. Mostly, pivot tables are used to find significance in big, detailed, sets of data.
How to Make a Pivot Table in Excel
Step
Open Excel
Open Excel and select an existing workbook that has the data for your pivot table.
Step
Select Any Cell
Select any cell within the data set you want to extract.
Step
In The Tables Group Click On ‘Insert’
In the tables group, click on insert, and on the drop-down menu select ‘PivotTable’.
Step
A Create PivotTable
A ‘Create PivotTable’ box will appear.
Check that the table Excel automatically selected is correct, and then select ‘New Worksheet’ at the end of the box before clicking ‘OK’.
Create the Fields
Next, we look at the steps to create the drag fields within your pivot table. These next steps will start when the Pivot Table Fields menu appears on your screen.
You will drag the fields you wish to have in your pivot table from the menu to the areas at the bottom of the menu, indicating where you would like each title to appear on the worksheet.
Step
Select A Field Title
Select a field title, such as Product or Amount from the list and drag it down to an area below; being filers, columns, rows, or values. The field titles will be extracted from your initial worksheet.
For example, let’s imagine you selected:
- Filter: Country
- Rows: Product
- Values: Sum of Amount
Sort the Table
Now that all of the information you wanted has been transferred onto your pivot table in your chosen format, you will sort the table. Tables will often be sorted by values, such as smallest to largest or vice versa.
Step
Select A Cell
Select a cell in the ‘Sum of Amount’ column.
Step
Right-Click On The Selected Cell
Right-click on the selected cell and select Sort.
Then select whether you want the data sorted from smallest to largest or largest to smallest.
Filter the Table
When sorting the fields you will have decided how you want the data to be filtered.
Here we’ll show you how to use the filter option:
Step
Find The Filter Button
Find the filter button on the worksheet. In our example it would be beside Country.
Step
Click The Filter Button
Click the filter button and from the drop-down menu select how you wish to filter the data – to show one specific section or all of the information together.
Changing the Summary Calculation
The final part of a pivot table that you need to learn is how to change the summary calculation. Excel will add the items by default, to change this.
Step
Select A Cell
Select a cell from the ‘Sum of Amount’ column.
Step
Right-Click On The Cell
Right-click on the cell and select Value Field Settings.
Step
Select The Type Of Calculation
Select the type of calculation you want from the pop-up menu and click ‘OK’.
Final Thoughts
We hope this guide will help you to make pivot tables that can benefit and speed up your daily business analysis and data sharing.
