Example with Excel Pivot Table

Pivot Table is an amazing feature of Microsoft Excel. Using a Pivot Table, we can easily summarize our large dataset according to our criteria. In this article, we will demonstrate 9 suitable features of Pivot Table as an example of how you can use a Pivot Table in Excel. If you are also curious about it, download our practice workbook and follow us.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


What Is Pivot Table in Excel?

Pivot Table is an amazing data analysis tool of Microsoft Excel. This tool helps us to analyze our data according to our desired requirements. Moreover, we can summarize our data in several types of categories and subcategories. Besides it, Pivot Table has all the features of a conventional Excel table.


How to Create Pivot Table in Excel

To demonstrate the generation process of a Pivot Table, we are going to use a dataset that has 11 shipment information of an electric supplier. Our dataset is in the range of cells B5:H15.

The steps to create a Pivot Table are given below:

📌 Steps:

  • First of all, select the range of cells B4:H15.
  • Now, in the Inset tab, click on the drop-down arrow of the Pivot Table option from the Table group and select the From Table/Range option.

Creating a Pivot Table From insert tab of Excel

  • As a result, a small dialog box called PIvot Table from table or range will appear.
  • Then, set the destination of the Pivot Table. For our dataset, we choose the New worksheet option.
  • Finally, click OK.

Defining the data range for creating the Pivot Table

  • You will notice a new worksheet will be created, and the Pivot Table will appear in front of you.

  • Input the fields in the four areas of the Pivot Table to get the value in it.

Inputting fields in different Pivot Table area

So, we can say that we are able to create a Pivot Table in Excel and ready to demonstrate the examples further.


How Does a Pivot Table Work

In the Pivot Table Field window, there are four areas. They are Filter, Columns, Rows, and Values. Above them, we have the field name list where all the column headings of our main table remain listed. We can input a field in those areas once to display the corresponding data in our Pivot Table. Insertion of a field in the different areas results in different outputs in our Pivot Table.

For example, if we placed the Region and Category in the Rows area and the Revenue field in the Value area the Pivot Table shows us a result like the image shown below.

Output in Pivot Table for combination of field

But, if we just take any of a field from the Rows area to the Column area, we will notice the output will be completely changed and the Pivot Table displays us a new output.

Output in Pivot Table for second combination of field

Read More: What is a Pivot Table in Excel – Make a Pivot Table Manually!


Example That Will Give You a Detailed Idea About Excel Pivot Table

To demonstrate the examples, we consider a dataset of 11 deliveries of an electric product supplier. The information on every shipment is in the range of cells B5:H15. We will show you several types of Pivot Table operations in our article.

📚 Note:

All the operations of this article are accomplished by using the Microsoft Office 365 application.


1. Inserting Fields into Analyze Data in Pivot Table

Inputting different fields in the Pivot Table sections provides us with different types of output. We will add the fields Quantity, Cost, and Revenue in our Pivot Table against the Category. The procedure is shown below:

📌 Steps:

  • As we want to display all of those data with respect to the Category field so, at first, we will place the Category field.
  • For that, drag the Category field from the field list into the Rows area. The entitles of the Category field will show row-wise.

  • Now, drag the Quantity field in the Value area. The value of quantity will show in column B.

Inputting the fields in the areas of the Pivot Table

  • Similarly, input the Cost and Revenue field in the Value area.
  • You will get all the fields in the Pivot Table.

Inserting Fields into Analyze Data in Pivot Table

Thus, we can say that we are able to show the first example in Excel Pivot Table.

Read More: What is a Pivot Table in Excel – Make a Pivot Table Manually!


2. Nesting Multiple Fields in a Single Section

In this example, we are going to show the nesting fields in a single area. In our Pivot Table, we have the Revenue value with the Category field.

We will input the Region field in the Rows area to form a nested filed situation. The process is described as follows:

📌 Steps:

  • First, drag the Region field from the field name list into the Rows area above the Category field.
  • As a result, you will see that the region name will show first, and inside every region, the corresponding category will display.

Nesting Multiple Fields in a Single Section of Excel Pivot Table

Hence, we can say that we are able to show the second operation example in Excel Pivot Table.

Read More: How to Show Zero Values in Excel Pivot Table: 2 Pro Tips


3. Placing Slicer for Pivot Table

Slicer is another feature of Excel. We can use a Slicer for easy data filtration. The steps to insert a slicer are given below:

📌 Steps:

  • First, in the Pivot Table Analyze tab, select the Insert Slicer option from the Filter group.

Insert Slicer in Excel Pivot Table from the Analyze tab

  • As a result, a small dialog box titled Insert Slicers will appear.
  • After that, select the field name for which you want to insert the Slicer. We checked the Region field.
  • Finally, click OK.

  • You will see that the Region Slicer will appear.

Created new Slicer

  • Now, select any of the regions, and you will see the corresponding category in the Pivot Table.

Placing Slicer for Excel Pivot Table Example

Therefore, we can say that we are able to show the third operation example in Excel Pivot Table.

Read More: Excel Pivot Table Formatting (The Ultimate Guide)


4. Sorting Data

In the following example, we are going to sort out the dataset in the Pivot Table. Our Pivot Table is now showing the in random.

We want to sort our Pivot Table from the highest revenue to the lowest revenue. The steps are described below:

📌 Steps:

  • Firstly, click on the drop-down arrow located at the bottom corner of the Row Labels.
  • Now, select the More Sort Options option from the Context Menu.

Choosing suitable option to sort data in Pivot Table

  • As a result, the Sort (Category) dialog box will appear.
  • Then, choose the Descending (Z to A) by filed and change the filed option Category to Sum of Revenue.
  • At last, click OK.

Defining criteria for data sorting in Pivot Table

  • You will see the highest revenue cell is displayed at the top and the lowest one at the bottom.

Sorting Data in Excel Pivot Table Example

In the end, we can say that we are able to show the fourth operation example in Excel Pivot Table.


Similar Readings


5. Filtering Data According to Requirement

Now, we are going to demonstrate the data filtering procedure in a Pivot Table. The procedure has a similarity to the conventional Excel table. The process is shown below step-by-step:

📌 Steps:

  • At the beginning of the process, click on the drop-down arrow allocated at the bottom corner of the Row Labels.

  • As a result, the Context Menu will appear.
  • Afterward, check those entities which you want to keep. We checked only for TV and Air Condition to see their data.
  • Finally, click OK.

Choosing the entities for filtering in Pivot Table

  • You will get the data of only those two items.

Filtering Data According to Requirement in Excel Pivot Table

At last, we can say that we are able to show the fifth operation example in Excel Pivot Table.


6. Updating Data in Pivot Table

Here, we are going to show you the updating process of a Pivot Table. For that, we will add a new data series to our dataset. After data addition, the range of our dataset is in the range of cells B5:B16.

The steps of data updating are explained as follows:

📌 Steps:

  • First of all, in the Pivot Table Analyze tab, click on the drop-down arrow of the Change Data Source and select the Change Data Source option from the Data group.

Opening Change Data Range dialog box to update data in Pivot Table

  • As a result, the Change Pivot Table Data Source will appear.
  • Now, select the new data range in the Table/Range field.
  • Then, click OK.

Changing new data range

  • You will notice that our previous Pivot Table updated with the new data.

Updating Data in Excel Pivot Table Example

Thus, we can say that we are able to display the sixth operation example in Excel Pivot Table.


7. Getting Top 3 Values from Table

In the following example, we will display the top 3 costly shipments. The steps to getting the top 3 entities are shown below:

📌 Steps:

  • At first, click on the drop-down arrow allocated at the bottom corner of the Row Labels.

  • As a result, the Context Menu will appear.
  • After that, select the Top 10 option from the Value Filter group.

Choosing proper option to get top 3 data

  • Another dialog box called Top 10 Filter (Category) will appear.
  • For getting the top 3, reduce the number from 10 to 3.
  • Then, set the last field as the Sum of Cost.
  • Finally, click OK.

Defining criteria for top 3 data

  • You will get those three items.

Getting Top 3 Values from Excel Pivot Table

Hence, we can say that we are able to display the seventh operation example in Excel Pivot Table.


8. Data Grouping with Pivot Table

Here, we will demonstrate the data grouping. For that, we keep the Region field in the Rows area. We know that New York and New Jersey are two neighboring states. So, we want to keep them in a group.

The procedure is shown below:

📌 Steps:

  • First, select the range of cells A5:A6.
  • Then, right-click on your mouse, and from the Context Menu, select the Group option.

Selecting proper option for grouping two data

  • You will see both regions will be assigned in a new group and the others will be shown as an individual group.

Data Grouping with Pivot Table Example

So, we can say that we are able to display the data grouping operation example in Excel Pivot Table.


9. Analyzing Data with Pivot Chart

In the last example, we are going to insert a Pivot Chart to visualize the data changing pattern. The steps of inserting a Pivot Chart are given below:

📌 Steps:

  • Firstly, in the Pivot Table Analyze tab, select the Pivot Chart option from the Tools group.

Inserting a Pivot Chart

  • As a result, the Insert Chart dialog box will appear.
  • Now, choose the chart according to your desire. We choose the Clustered Column chart for a better comparison of our dataset.
  • At last, click OK.

  • The chart will appear on the sheet.
  • Modify the chart according to your requirements and add necessary items from the Chart Elements icon.

Analyzing Data with Pivot Chart from Pivot Table

Finally, we can say that we are able to display the Pivot Chart insertion operation example in Excel Pivot Table.


How to Refresh Pivot Table

In this case, we will show you how to refresh a Pivot Table, if any entity of the main data set is changed. We will change a category name from Blander to iPod,

The steps of refreshing are given below:

📌 Steps:

  • First of all, go to the Pivot Table Analyze tab.
  • Then, select the drop-down arrow of the Refresh > Refresh option from the Data group.

Refreshing Pivot Table by Refresh command

  • You will notice the Blander will be replaced with iPod.

Refresh Pivot Table to update data

Thus, we can say that we are able to display the refreshing process in Excel Pivot Table.


How to Move Pivot Table to New Location

Now, we are going to demonstrate the moving approach of a Pivot Table to a new location. The steps of the moving process are shown as follows:

📌 Steps:

  • At the start, go to the Pivot Table Analyze tab.
  • Afterward, click on the Move Pivot Table from the Action group.

Selecting Move Pivot Table command to move the Table

  • As a result, the Move Pivot Table dialog box will appear.
  • Set the destination of your Pivot Table. We want to move one column right, So, choose the Existing Workbook option and select cell B3 as a cell reference,
  • Finally, click OK.

  • You will see the whole Pivot Table will shift one column.

Move Pivot Table to New Location

Hence, we can say that we are able to move the position of our Excel Pivot Table.


How to Remove Pivot Table

In the last case, we will show you how to remove a Pivot Table. The method is described below:

📌 Steps:

  • First, in the Pivot Table Analyze tab, click on the drop-down arrow of Select > Entire Pivot Table option from the Action group.

Selecting suitable command to remove the Excel Pivot Table Example

  • You will get the complete table selected.

  • Now, press the Delete key from your keyboard.
  • You will see the Pivot Table will disappear from the sheet.

Removed Pivot Table from Excel worksheet

Finally, we can say that we are able to remove the Pivot Table from the Excel worksheet.


Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to use Pivot Table in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

8 Comments
  1. great course I am on an exploration journey thanks

  2. Hi, Kawser,
    For PT8, the title asks for the savings account whereas the example is filtering for only checkings. Otherwise great tutorial.
    Many thanks!
    Yun

  3. hi kawser, thank you for your work its wonderfull!! i learned a lot cheers! more power!

  4. Good day Kawser,

    Thank you a lot. Found what I was looking for.

    Regards,
    Zayniddin

  5. Thank you very much for the exercise on Pivot Table. It was quite revealing and interesting. I learnt a lot from it.

  6. Great tutorial, thanks for your help.

Leave a reply

ExcelDemy
Logo