What Is the Use of Pivot Table in Excel (13 Useful Examples)

Excel Pivot Table is an excellent data analysis tool. It is a tool to find patterns, trends, and comparisons in data by summarizing and analyzing them. Using this feature, you can analyze tons of data with just a few mouse clicks. Besides, Excel Pivot Tables use a pivot cache that holds a replica of the original data source, which makes Pivot Tables work faster. In this article, you will learn how to create a Pivot Table in Excel and how to use it effectively to analyze data.

The following image gives a brief idea of what a Pivot Table looks like. We have immense data in Excel, described by the left image, and we see a summary of the table by clients’ names in the right-side image.

How to Create Pivot Table in Excel

Creating a Pivot Table in Excel is easy. But it depends on which operating system you are using. In this article, we will assume that we are all using the Windows operating system. We are using Office 365 to prepare this write-up.

Now just do the following.

🔀 Steps:

• Click on any cell in the data ⇒ go to the Insert tab.
• Click on the PivotTable button ⇒ Select the New Worksheet or Existing Worksheet radio button.
• Press OK ⇒ Drag and drop the fields.

How Does a Pivot Table Work in Excel?

(We are continuing from the step we have shown in the last image.)

After pressing OK, a new sheet appears to the left of the current sheet. If you go to this sheet, you will see that the sheet looks like the following image.

Here you will find the following regions.

Excel considers the topmost row of your data as a header row and will accumulate the header names in this region. Here, we have the following fields: Year, Month, Date, Region, Client, and Amount.

Below Pivot Table Fields, you will find a region that has the following areas.

Now, how to use these components to create a report?

Excel Pivot Table gives us a brief direction on that. It says, “To build a report, choose fields from the Pivot Table Field List”.

For example, if we want to get the month-wise total amount for each client from 2019 to 2022, we have to select or drag the Client field to the Rows area first, and then we will select or drag the Year and Month fields one after another. After that, we will select the Amount field.

What Is the Use of Pivot Table in Excel: 13 Useful Examples

Now, we will discuss the use of a Pivot Table in Excel with the help of some examples. Look at the following dataset. Here we have the sales data of a company.

Now, assuming that you have to make a report on it. To be honest, there are more than 700 rows in this report and it is never easy to answer a question like- “Which client has placed the maximum amount of orders?” at a glance. But with the Excel Pivot Table, it is a matter of some simple mouse clicks.

Let us first prepare a Pivot table with this data as shown above and then follow the next sections where we have discussed 13 important uses of Excel Pivot Table.

1. Getting Sum of Amount by a Specific Criterion, i.e., Year, Month, Region, or Client at Once

To do this, I can create a Pivot Table first, then drag the Amount field to the Values area and other attributes to the Rows area. (We will perform some operations using this dragging method later)

Or, Excel has an impressive feature which is Recommended PivotTables just beside the PivotTable button. We can use this feature also.

• After clicking on the Recommended PivotTable button, Excel will show some sample Pivot Tables. Just like the following image.

So, I can easily answer the question.

• For example, we can say that the total order amounts by January, July, and December are $80533752, $48857452, and $35136882.
• A similar approach goes for the sum of the amount by region, client, etc.
• If I click on any of the right-side Pivot Table previews and then press the OK button, a new sheet will be opened to the left of the current sheet.

• If I click on the + signs in each month-row, I get year-wise (same month, different years) amounts too.

2. Use of Value Field Settings & Sort: Find Maximum Value and Corresponding Data

“Which client has ordered the most till 2022?”

• Here we will work with two variables: Client and Amount.
• To get the answer, we must place the Client field in the Rows area and the Amount field in the Values area. However, since Excel chooses the Sum operation for Values in a Pivot Table by default, we have to change these settings to see the maximum amount of order.
• For that, select a cell from the Values field (i.e., Sum of Amount column here) ⇒ go to the PivotTable Analyze tab ⇒ click on the Field Settings option. You will find this command in the Active Field group.

• Now, from the Value Field Settings window, choose the Max option from the Summarize value field by section ⇒ Then press OK.

• You can also utilize the Show Values As section to format amounts in the desired way.

• After applying all these, the Pivot Table looks like the following image.

So, the highest amount of orders is $1,998,763.

But who is the highest-order placer? It is not clear from the current Pivot Table. To find this,

• Click on any cell in the Amount column and right-click on your mouse.
• Then choose Sort from the context menu and select Sort Largest to Smallest.

• As a result, the Pivot Table will look like this.

So, the maximum order is placed by Timefall Inc. (which is $1,998,763.00).

Read More: Excel Data for Pivot Table Practice

3. Use of Count Operation: Find How Many Times Each Client Placed an Order

• To answer this, just select the Client and Amount fields, and then from Value Field Settings, select the Count option.

• Now, see the following image.

• So, the Sol company placed orders 254 times, which is the highest.

4. Add and Use Slicers in Pivot Table

A Slicer is a Pivot Table feature for filtering purposes. In this part, we will see how to create a Pivot Table slicer and use it.

Workaround to Create Pivot Table Slicer:

• After creating the Pivot Table, just click on a Pivot Table cell ⇒ Then go to the PivotTable Analyze tab ⇒ Then click on Insert Slicer from the Filter group ⇒ Then select filtering options from the pop-up window. Each selection will create a separate slicer.

• For instance, we have selected Year, Month, and Client. So here are our slicers.

How to Use Pivot Table Slicer:

• Go to Year Slicer first. Select 2022, you will see that Month and Client Slicers will be edited according to this. Then Select a Month, e.g., July.
• The Clients who haven’t placed any order in July 2022 will be greyed out in the Client slicer.
• Now, you can select a Client and see the corresponding data.

• To apply multiple criteria, click on the Multi-Select icon or press ALT+S ⇒ Then choose any number of criteria you want.

• If you have multiple Pivot Tables and want to connect the Slicer Filter to a specific one, then click on the Slicer ⇒ Then go to the Slicer tab ⇒ Then click on Report Connections and select your desired Pivot Table from the pop-up ⇒ Then press OK.

How to Clear Filter and Remove Slicer:

To clear the filter created with Slicer, click on the cross icon. Or press ALT+C.

• To remove a Slicer, just select it and press DELETE.

Read More: Pivot Table Test for Interview

5. Add and Use a Timeline Slicer in Excel Pivot Table

Instead of using a slicer, you have this easier way in Excel Pivot Table for time-based filtering. The concept of the slicer and timeline is quite similar.

But note that you can use this timeline feature only when you have date data in the Pivot Table. However, you cannot use the timeline feature for a time in HH: MM format, i.e., 2:00 PM, 6:30 AM, etc.

How to Add a Timeline in Excel Pivot Table:

• To add a Timeline to your Pivot Table worksheet, select a cell in a Pivot Table and choose Insert ⇒ Filter ⇒ Timeline.
• You can also get this feature from the PivotTable Analyze tab’s Filter group.

• Mark the Date box and press OK.

• Now, you will have a timeline slicer like the following image.

• To change the date range from month criteria to other date criteria, click on the arrow sign at the top-right corner of the timeline.
• For example, if you select Year criteria, the timeline will look as follows.

How to Use Timeline Slicer in Excel Pivot Table:

Let’s say, you want to know the total order placed by a company from January to July of 2022.

• For that, select the 2022 part from the timeline bar ⇒ Then select MONTHS.

• Then select the JAN part from the timeline, press the SHIFT key, hold it, and select the JUL part.
• Thus, you will get to know the total order placed by a company from January to July of 2022.

• To clear this filter, click on the cross icon or press ALT+C.

• To remove the Pivot Table timeline, follow this workaround.
• Click on the timeline and then right-click the mouse ⇒ Then select the Remove Timeline option from the command list.
• Or, simply press the DELETE key.

6. Use of Calculated Field: Applying Formula in Excel Pivot table

Another awesome feature of Excel Pivot Table is the use of the Calculated Field. Here I will show it with an easy example.

• Suppose you want to offer a 10% discount to the clients and need to know the discounted amounts. To do this task, we can use the Calculated Field option.

• Click on any cell of the table ⇒ Then go to the PivotTable Analyze tab ⇒ From the Calculations group, select the Calculated Field option of Fields, Items & Sets drop-down.

• In the appeared pop-up, type a suitable name for the new field ⇒ From the Fields menu, select the field name that suits the newly added field ⇒ Press Insert Field ⇒ In the Formula box, type a suitable formula ⇒ Press the Add button ⇒ Finally press OK.

• As a result, you will see that a new column with desired output appears.

7. Use of Calculated Item Command

Have you noticed in the last section that a command named Calculated Item was just below the Calculated Field command in the list?

Note one thing, if you want to use this feature, you have to select a non-Value field first. It doesn’t support a Value field. That is why the command was greyed out previously (you can check again).

What’s the purpose of this option? Look at the following image.

Under the Column Labels, we have listed East, North, South, and West regions (colored in green). These are called Items. Using the Calculated Item command, we can perform various calculations between the associated values of the corresponding Items.

• For example, If I want to know the difference in sales between North and South regions, I have to select a cell inside the Pivot Table and select the Calculated Item option from the Fields, Items, and Sets drop-down of the PivotTable Analyze tab.

• As a result, the following window will pop up. We now will give a suitable name (North ~ South) ⇒ Then select a suitable field (Region here) ⇒ Then create a formula in the following manner.
• Select an item (North) ⇒ double click on it or press the Insert Item button ⇒ Then type a minus (-) sign ⇒ Then again select Item (South) and press the Insert Item button or double click on the Item name ⇒ Then press Add and OK.

• The following image shows that we have got the difference between the two regions.
• After applying the accounting format to them, the result looks like this.

8. Insert a Pivot Chart to Visualize Data

You can also create a chart from the Pivot Table you have created. It is called a PivotChart.

• To insert a PivotChart, click on any cell in the table ⇒ From the PivotTable Analyze tab, click on PivotChart.

• Then select a suitable Chart type from the pop-up window ⇒ Then press OK.
• For instance, we have selected 3D Clustered Bar from the Bar section.

• The graph we have got looks like the following image.

Read More: A Pivot Table Example in Excel with Real Data

9. Generate Multiple Pivot Tables in Different Sheets for Each Row Label

Suppose you want to create different Pivot Tables from the existing one for all the clients separately. To do that, follow the workaround below.

• Drag the Client field in the Filters area of the PivotTable Fields pop-up.

• Then click on a cell inside the table and go to the PivotTable Analyze tab ⇒ Then from the Options drop-down, select the Show Report Filter Pages option.

• Click on the OK button of the pop-up.

• Finally, you will see that sheets are generated by Excel for each client having a separate Pivot Table for each for them.

10. Audit Data in Pivot Table

Assuming that you are showing your boss the data using Pivot Table, suddenly he wants to cross-check the results if they are okay or not! So, you need to audit the data for your boss this time.

• To do this, just double-click on the value you want to cross-check. A new sheet will be generated automatically.
• In this sheet, you can see the data details.

11. Format Pivot Table for Error or Empty Values

You can format the Pivot Table in such a way that it will show a specified text if there is any error or empty value.

• To do that, click on any cell in the table and right-click on your mouse ⇒ Click on PivotTable Options.

• In the appeared pop-up, mark For error values show and For empty values show boxes, and type suitable text in the boxes. Then press OK.

12. Get Amount in % of Grand Total, Row Total and Column Total

• To get the amount in % of Grand total & Row/Column total, select any cell in the Pivot Table ⇒ go to PivotTable Analyze tab ⇒ Click on Field Settings ⇒ Go to Show Values As section in the appeared pop-up ⇒ select a suitable option and press OK.

• If you scroll down the options in this list, you will also see more commands like Difference from, Running Total, etc.

13. Group Data by Date in Excel Pivot Table

To group data by date in the Excel Pivot Table, you have to do the following.

• Go to the PivotTable Analyze tab and select the Group Selection command.
• In the Grouping pop-up, set the Starting and Ending date/year/month ⇒ Set the interval and press OK.

Quick Notes

• Refresh Pivot Table After Change in Source Data: To reflect the change in source data to a Pivot Table, click on the Refresh button located in the Data tab, or the PivotTable Analyze tab.

• Change Source Data Range: To change the source range of the Pivot Table, select any cell of the Pivot Table, and then select the Change Data Source option.

Then select source data again from the following pop-up.

• Change Pivot Table Location: To change the Pivot Table location, use the Move PivotTable button.

• Clearing Pivot Table Data: Use the Clear All button from the PivotTable Analyze tab.

• Select from Pivot Table: To select a Pivot Table or a portion of the table, use the following options from the PivotTable Analyze tab.

• Keeping Column Width Updated with Refresh: To do this, go to PivotTable Options from the context menu after right-clicking your mouse, and mark the Autofit column widths on update checkbox ⇒ Then press OK.

Download Practice Workbook

To understand better, we would recommend downloading the following Excel workbook that has source data for practice and a Pivot Table already created. Practice with it while reading this article!

Conclusion

Excel Pivot Table is a huge feature that can enormous data analysis. If the uses described here are enough to satisfy your needs, leave us a comment. If you still face problems in using Excel Pivot Table, then don’t hesitate to let us know.

<< Go Back to What is Pivot Table in Excel  | Pivot Table in Excel | Learn Excel