Thursday, February 25, 2021
Home Excel Sheet How to Use Pivot Table in Microsoft Excel?

How to Use Pivot Table in Microsoft Excel?

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.

Pivot Table in Microsoft Excel

  • 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..


Pivot Table in Microsoft Excel

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.


PivotTable field

  • To add fields, drag the Sales field to Values. Excel auto-calculates the sum of all sales in the whole data set.

sample data

  • Drag the Color field to the Row area. The excel will auto-calculate the sales of each color.

sample sales data

  • 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.

Pivot Table in Microsoft Excel

  • To do Number formatting, right-click any cell in the Sales field. Select Number format.


  • In the Number format, select the currency option.


Pivot Table in Microsoft Excel

  • 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.

sample data

  • 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…

Pivot Table in Microsoft Excel

  • In the Grouping window, Select Group by Years only.

PivotTable in Microsoft Excel

  • Now you can see the Pivot Table that groups Sales by Date and Color.

PivotTable in Microsoft Excel

You can achieve accuracy, formatting, and much more with Pivot Tables.

Learn more “How to remove duplicates in Microsoft Excel?”


Please enter your comment!
Please enter your name here

Most Popular

Recent Comments