How to use pivot table in Microsoft Excel?

One of the key features of  Microsoft Excel is Pivot tables, which gives you even more complex reporting capabilities. It’s a table that can display the same set of information from a list with more than one arrangement (view mode). Microsoft Excel users can manage their Pivot Table rows and columns in order to view or summarize data in a variety of ways. The tutorial teaches you how to use pivot table in Microsoft Excel.

To get started with a series of data, we get Excel, the data is in 214 rows and 6 columns, the columns are as follows:

Order ID, Product, Category, Amount, Date, and Country

How to use pivot table in Microsoft Excel?

How to use pivot table in Microsoft Excel?

How add a pivot table

First, choose one of the homes we’ve filled with our own data. In the Insert tab, click the PivotTable button.

How to use pivot table in Microsoft Excel?

A window opens; Excel automatically detects all of our data and writes the range related to them.

The default location for building a pivot table in Excel is new worksheet.

How to use pivot table in Microsoft Excel?

Work with fields in the pivot tables

After the above steps, your table is built into a new sheet. Go to that sheet on the table photo. A column will open next to Excel, which will allow you to use this table.

How to use pivot table in Microsoft Excel?

Follow the steps below to see what happens.

Tick the product and enter it the row labels section.

Tick the Amount and enter it the Values field.

We then tick the country and put in the Report Filters section.

How to use pivot table in Microsoft Excel?

The result is as follows.

How to use pivot table in Microsoft Excel?

We have the ability to filter by country, for example, products that are made in the UK only to participate in the report. In the next section, the name of each product comes with their sum Amount, so that we can see them. So the columns that we put in the values field are placed in front of columns we put in the row labels field.

Sorting

The pivot tables also have the sort feature. Click on the desired column on the table. Enter the option tab and click on the AZ or ZA buttons. It will be active when you are in pivot table sheet.

How to use pivot table in Microsoft Excel?

And you can see the result as below.

How to use pivot table in Microsoft Excel?

Changing the basis of calculations in the Pivot Table

As you can see, the default operation is plus on the values part. If you want to change it, it’s easy and possible. Click the Total column where sum of values are displayed, click on the right mouse button and click the Value Field Settings tab as below.

How to use pivot table in Microsoft Excel?

In the popup window you can choose items other than sum, for example you can choose count.

How to use pivot table in Microsoft Excel?

2 Dimensional Pivot table

If we put the row labels and column labels in the selected columns section, we will have a two-dimensional table. For example, in the row labels field, we’ll put the country column and in the column labels column we put the product column. The rest of the items are like the below.

How to use pivot table in Microsoft Excel?

You can see the result as below.

How to use pivot table in Microsoft Excel?

As we expected, the filter was applied to the category and both the product and country sections have a small arrow next to them, which allows you to sort and filter in manually. Aggregate operations are also performed on the Amount column.

Good job! You learned a brief tutorial how to use pivot table in Microsoft Excel.

How to use pivot table in Microsoft Excel?
4.6 (91.25%) 16 vote[s]

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *