Blog

PIVOT Table – one of the most powerful and commonly used tools of Microsoft Excel. If you are a regular PIVOT user, you don’t need anything else for your quick last-minute analysis. And if you are still not familiar with this magic tool, no worries – you are at the right place. By the end of this blog, you would have been started your permutations and combinations with the Pivot fields.

Why use a PIVOT table?

There are times when you are required to analyze larger amounts of data and produce easy to read and understand reports. So, when we face this situation, what do we do? Hence a feature called the Pivot Table helps us to overcome this issue. So, what does a Pivot Table exactly do?

A pivot table allows you to extract the significance from a large, detailed data set. It calculates, summarizes, and analyzes data that lets you see comparisons, patterns, and trends in your data.

Let’s say you have a sales data for different regions, with a pivot table, you can summarize the data by region and find the average sales per region, the maximum and minimum sale per region, etc.  The beauty of pivot tables is they allow you to interactively explore your data in different ways.

How to create a pivot table?

1. Select the cells you want to create a PivotTable from.

Note: your data shouldn’t have any empty rows or columns. It must have only a single-row heading.

2. Select “Insert > PivotTable”.

3. Under “Choose the data that you want to analyze”, select “Select a table or range”.

4. In “Table/Range”, verify the cell range.
5. Under “Choose where you want the PivotTable report to be placed”, select “New worksheet” to place the PivotTable in a new worksheet or “Existing worksheet” and then select the location you want the PivotTable to appear.
6. Select “ok”.

Drag field

The PivotTable Fields pane appears. To get the total amount of product, drag the following fields to the different areas.

1. Item field to the Rows area.
2. Units field to the values area.
3. Unit cost filed to the values area.
4. Total filed to the values area.

Below you can find the pivot table.

All the details are in a jumbled way no worries we can sort the data in ascending or descending order lets see how:

To get an item sorted in descending or ascending order we have to sort the pivot table. For example, there is a list of items and we are arranging them in descending order of their respective amounts.

1. Click any cell inside the Sum of Amount column.
2. Right-click and click on sort, sort Largest to Smallest.

And the result will be:

So here you can see the items are sorted according to the amount in descending order.

Filter data in a PivotTable with a slicer:

1. Select any cell within the PivotTable, then go to Pivot Table Analyze > Filter > Insert Slicer Slicer.
2. Select the fields you want to create slicers for. Then select OK.
3. Excel will place one slicer on the worksheet for each selection you made, but it’s up to you to arrange and size them however is best for you.
4. Click the slicer buttons to select the items you want to show in the PivotTable.
5. Filter data manually:
6. Select the column header arrow Filter drop-down arrow for the column you want to filter.
7. Uncheck (Select All) and select the boxes you want to show. Then select OK.

How PIVOT helps enhance your Productivity?

Trying the above organization manually is undoubtedly a time-consuming and tedious job. With PIVOT, it’s all on your fingertips where you can present your data in several different patterns for quick visualization.