Wednesday, April 21, 2021
HomeExcel SheetHow to Use Pivot Table in Microsoft Excel?

How to Use Pivot Table in Microsoft Excel?

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

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 Pivot Table fields pane. All the fields are empty

Pivot Table field

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

whole data set

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

auto calculate

  • 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

Pivot Table in Microsoft Excel

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

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 applied to all the Sales field

currency symbol

  • You can sort the Sales by value. Right-click any data in the Sales field and select Sort

sales by value

  • Sort the data from largest to smallest

sort data

  • The data is in sorted form

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…

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?”

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments