Use Pivot Table in Microsoft Excel
Use Pivot table in Microsoft Excel can give you more ease. Pivot table is a very useful feature of Microsoft Excel. Pivot Tables mostly use in large datasets such as average, minimum, maximum, total figures, etc. You can summarize and examine useful and relevant data with Pivot Tables.
Function of Pivot Table
Using this feature you 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.
How to Create Pivot Table in Microsoft Excel Works?
Let us tell you 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 Pivot Table 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 check. Moreover, 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 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.
