How to Use Pivot Table in Microsoft Excel?
Pivot Table is a very useful feature of Microsoft Excel. Pivot Tables are used for large datasets such as average, minimum, maximum, total figures, etc. You can summarize and examine useful and relevant data with Pivot Tables.
Using this feature can increase your efficiency in Excel. Pivot Tables look like reports. Unlike the static reports, you can interact with the data in pivot tables. A pivot table allows you to view your large data set with many different perspectives. It allows you to group your data, make different datasets, filter the data, and create charts.
Let us tell you how to create and use Pivot Tables in Microsoft Excel with the help of an example.
In the given example, the dataset contains 452 records and 5 fields such as Date, Unit, Sales, Color, and region.
- To insert a pivot table, select any cell containing data. In the Insert tab, click Pivot Table.
- Specify the name of your table as Table1. Also, enter H4 in the “Location” to create a Pivot Table in that location in the current spreadsheet..
Note: you can create the pivot table in a new spreadsheet rather than the same worksheet. But for your understanding, we’re placing the pivot table in the same worksheet.
- Excel will display all the PivotTable fields pane. All the fields are empty.
- To add fields, drag the Sales field to Values. Excel auto-calculates the sum of all sales in the whole data set.
- Drag the Color field to the Row area. The excel will auto-calculate the sales of each color.
- Now, you can see the Color and Sales field are checked, and Color is inside a Row field and Sales is in the Values field.
- To do Number formatting, right-click any cell in the Sales field. Select Number format.
- In the Number format, select the currency option.
- Set the decimal places to zero. Select the currency symbol as $ and click Ok. You can see that the currency symbol is applied to all the Sales field.
- You can sort the Sales by value. Right-click any data in the Sales field and select Sort.
- Sort the data from largest to smallest.
The data is in sorted form.
- You can also get the percentage of sales. For this, right-click the data in any Sales cell.
- Select Show Values As > % of grand total.
- You will see another column showing the percentage of sales.
- If you want to examine the data date vise such as years, months
- , or quarters. Right-click the date in the header field and select Group…
- In the Grouping window, Select Group by Years only.
- Now you can see the Pivot Table that groups Sales by Date and Color.
You can achieve accuracy, formatting, and much more with Pivot Tables.
