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 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.
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.
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.
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.
The result is as follows.
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.
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.
And you can see the result as below.
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.
In the popup window you can choose items other than sum, for example you can choose count.
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.
You can see the result as below.
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.