Pivot Table is an extremely useful feature of Excel. Using this feature users can manipulate the data table as their wish. If you are curious to know how you can analyze data in Excel using Pivot Table, this article may come in handy for you. In this article, we are going to show how you analyze data in Excel using Pivot Tables with an elaborate explanation.
Download Practice Workbook
Download this practice workbook below.
Pivot Table Overview
As stated before, a Pivot Table is a strong tool to analyze data. A Pivot Table allows users to modify the existing data Table according to their wish. Slicing only the important or necessary columns from the Table and then analyzing them is the main theme of the Pivot Table. Before we delve into the Data analysis, we need to get comfortable with the Pivot Table environment.
- After we initiated the Pivot Table, we will see three separate tabs in the ribbon instantly. Power Pivot, PivotTable Analyze, and Design.
- We also have a side panel of Fields. The fields are basically the columns that we had in the primary dataset.
- We can drag the fields into the below areas, named the Filters, Columns, Row, and Values.
- Dragging the fields on the row will show them on the leftmost most column as rows in the Pivot Table.
- And adding them to the columns will place the values in the column in the Pivot Table.
- You can change the value field settings, whether you want to show the Average value/Maximum/Minimum value etc. In the Value field area.
Steps to Create Pivot Table in Excel
We will initially insert the Pivot Table from the Insert tab.
- From there click on the PivotTable > From Table/Range.
- Then in the new dialog box, click on the Table/Range range box and then select the New Worksheet option.
- Doing this will select the table and will open the Pivot Table in a new sheet.
- Click OK after this.
- Immediately after clicking OK, you will notice that a new worksheet opened with PivotTable Field settings.
- The new worksheet will look something like the below image.
9 Suitable Examples to Analyze Data in Excel Using Pivot Tables
For the demonstration purpose, we are going to use the below dataset. This is hypothetical sales data of an imaginary organization, where we can see the ID of the products that they sold so far, which Region they sold, their Type, Quantity, Cost of production, Ratings, Revenue, and Profit Margin. We will implement a Pivot Table to analyze and extract important insights about the data table.
1. Selecting Various Fields to Analyze Data in Pivot Table
We can add various fields in the below areas to create tables with different shapes and criteria.
For example. We dragged the Region to the row fields and then drag the Revenue to the Value field.
A Pivot Table will be created according to these fields in the Pivot areas.
- We trimmed down the initial table to a minimal shape, where we can see the Revenue total earned in each Region. This helps us tremendously to access how each Region performing.
- We can add more criteria or value fields in the area to evaluate the performance of each every in different sectors.
- For example, we can drag Cost and Quantity in the field to expand our existing table.
- From this table, we can see the total cost of production in each Region. The total Quantity of products produced in each Region is also here.
- This type of table enables us to analyze data more effectively and helps to keep them free from clutter.
- For all of the values fields, the fields are shown as the Sum of the fields. But users can change the field operation very easily.
- For example, we can add the Rating field in the area. The value in the Pivot Table will be shown as the Sum of the Ratings in each Region. But it doesn’t make sense. We are not benefitted from the sum of the Ratings, we need the average Ratings of each Region.
- To change field evaluation settings, we will click on the field in the area, then right-click on the mouse.
- Then from the menu, select Value Field Settings.
- There will be a new dialog box named Value Field Settings.
- In that dialog box, select Average from the Summarize value field by the group.
- Click OK after this.
- Now we will notice that the Ratings are now presented in average value instead of SUM total in the range of cells E4:E11.
- With this data, we can analyze which Region’s product received better customer reviews compared to the others.
- From the table above, it is quite evident that the Asia Regions performed well compared to other Regions.
- Next, we will add the Profit Margin field in the area. As the default value settings are set to Sum, toggle it to Average, to see the Average of the Profit Margins of each Region.
- Doing this will add the profit margin of each Region in the range of cells F4:F12
- But the Profit Margins are in fractions, we need to convert them to percentages.
- To do this, we will select the range of cells F4:F12 and then click on the Percentage icon in the Number group.
- Now we can see the average Profit Margins in the percentages format of each Region in the range of cells F4:F11.
- With this summary table, we can analyze that the Africa Region is performing well in terms of Profit Margin. On the other hand, we can conclude that Middle-East Asia is performing worst in terms of Profit Margin. they are basically losing money selling products.
2. Nesting Multiple Fields
We can group several fields altogether to create a nest of criteria in the Pivot Table. In the nested or grouped field, the first field will present the data first. Then under the first field, all the second-tier fields will be present. Just like the below image.
- To do this, drag the Category field just below the Region field in the Row areas.
- After then, you will notice the Category is going to be nested below each Region field.
- Using the table, we can differentiate not only the Regions from each other but also the inside field of each Region.
- Now we can drag the Region to the second position. Doing it places the Category in the first position in the nesting. As shown in the below image.
3. Filtering Data in Pivot Table
After creating the Pivot Table we can add a filter to the selection to add more flexibility to our analysis.
- In order to do this, drag the Category field in the Filters area.
- Right after dragging the Category field, there will be a Filter icon on the top left corner of the worksheet.
- Click on the Filter icon. And tick mark the Select Multiple Items.
- Then select the Electronics and click OK.
- Now, the table filters out all the entries other than the Electronics, updating the table automatically.
- The updated table is shown below. Here only the Africa, Europe, and South Asia Regions have the products under the Electronics Category. Hence only these Regions are shown here.
4. Sorting Data in Pivot Table
Besides filtering the dataset, we can also sort the data according to our requirements. Like we can sort them alphabetically or from smallest to largest.
- To sort out the Pivot Table, click on the sort icon on top of the Row levels a drop-down menu will appear. from that drop-down menu, click on the More Sort Options.
- After then, a new dialog box will open, where you will select the field name on which you will perform the sort. In this case, we choose Region.
- By default, the sort option is set to Ascending (A to Z) by:
- So the Regions will be organized alphabetically.
- The newly-organized table will somewhat look like this.
- But we can not only Sort the Regions but also by any field by any criteria.
- For example, you can sort the Pivot Table by the total Quantity of products in each Region produced.
- To do this, select the sort icon, and then in the Sort dialog box, select the Sum of Quantity in the drop-down menu.
- After then, click on the More Options at the right bottom of the dialog box.
- In the next dialog box, click on the Grand total in the Sort By option. This means we will basically Sort the table Quantity from smallest to largest. Click OK after this.
- Now you will back to the previous dialog box. Click OK on that box too.
- After clicking on OK, You will notice that the Pivot Table is now sorted according to the smallest Quantity of a product to the largest Quantity of the product.
- This feature helps tremendously while analyzing the table, as the table is already quite organized. We not only have the number of Products arranged by each Region but they also can be Sorted so that we can evaluate which Region has produced more products easily.
And this is how we analyze the data by Sorting them in the Pivot tables in Excel.
- How to Analyze Likert Scale Data in Excel (with Quick Steps)
- Analyse Qualitative Data from a Questionnaire in Excel
- How to Analyze Time-Scaled Data in Excel (With Easy Steps)
- Analyze Time Series Data in Excel (With Easy Steps)
- How to Add Data Analysis in Excel (with 2 Quick Steps)
5. Use of Slicer in Pivot Table
A Slicer is a convenient tool to filter out necessary information efficiently.
- In the next step, we can add a couple of slicers to the Pivot Table.
- In order to add the slicer, click on the Insert Slicer command from the PivotTable Analyze tab.
- After clicking the Insert Slicer command, there will be a new dialog box named Insert Slicers.
- On that dialog box, select the slicers that you are going to add by tick marking the checkboxes. In this case, we checked the Category, Region, and Rating boxes.
- After finishing the ticked marking, click on OK.
- Right after clicking on the OK, there will be 3 different slicers spawned in the worksheet. Each one for every field and the entries in these fields.
- Click on Asia and South Asia in the first slicer. This will eliminate other entries which have Regions other than Asia and South Asia.
- Then select the Electronics and the Home Appliances.
- This will select only products only in that two Category.
- Then choose Ratings 4 and 4.5.
- the table is going to update regularly as we click on Slicer.
- Finally, we got different products and their different performance parameter only from the Asia and the South Asia Regions. The list is further filtered by the only two Category of products. Then we proceed to choose only 4 and 4.5 Ratings. That is how we narrow down products according to performance parameters.
And this is how we analyze the data by adding the slicers in the Pivot tables in Excel.
6. Analyzing Data with PivotChart
We can further enhance our analysis by showing our Pivot Table with some charts. For instance, we can use the stacked bar chart with dual-axis to visualize the Sum of Revenue and the Profit Margin with respect to the Regions.
- We need to visualize how the Profit Margin earned by each Region is related to the revenue generated by them. Are they in proportion to each other, or do they act inversely?
- In order to do this, first of all, we need to go to the Insert tab and then click on the PivotChart. Then from the drop-down menu click on the PivotChart.
- There will be a new dialog box named Insert Chart, where you can choose your favorite types of charts.
- From that option, we choose the Combo option, as we need to show two separate values on the vertical axis.
- In that menu, for the Sum of Revenue, choose the chart type as Clustered Column from the dropdown menu.
- Then for the Average of Profit Margin, choose the Line chart type. And also set this chart as the secondary axis by ticking the box right side of the dropdown menu.
- After clicking OK, we will notice that the new chart is now present. With the two axes, The left side one iis for the Sum of Revenue and the right side one is for the Profit Margin.
From the chart, we can see that the Profit Margin and the Sum of the Revenue are not exactly proportional here. Higher revenue doesn’t mean that the Profit Margin would be higher accordingly.
- The charts have filters by default in it. For example, you can see the Category filter already set in the top left corner of the chart. Click on it and see that all the products Category are now selected.
- If you want to select some Categories and ignore the rest of them, select your desired one and then click OK. For example, we choose Furniture and Home Appliances and then click OK.
- After clicking OK, you will notice that the chart now shows only the Furniture and the Home Appliances values. And only 4 Regions actually have that Category product. That is why the chart is now shrunken and showing only the 4 Region data.
This is how we can analyze and visualize the Pivot Tables in Excel and then narrow down the data using the filter option in the chart.
7. Updating Data in Existing Pivot Table
Updating the data is considered to be routine work in any kind of data analysis project. Suppose we got some new raw sales data, as shown in the highlighted part of the table below. Now creating a new Pivot Table from the scratch can be a tedious thing to do. To resolve this, we will update the data source internally by keeping the original Pivot Table structure intact.
- In the beginning, from the Pivot Table Analysis tab, click on the Change Data Source command.
- Then from the drop-down menu, click on the Change Data Source.
- Then in the Move PivotTable range box, select the full range of the updated table.
- After selecting the full range, you are going to see that the full range is now added to the existing PivotTable, and it’s updated also.
8. Getting Top 10 Values from Pivot Table
You can extract the top 10 or 20 values in a dataset, using the Sort icon on the top left corner.
- In order to do this, first, click on the dropdown icon on top of the Row Levels.
- Then from the drop-down menu, click on the Value Filters > Top 10.
- Ther is a new dialog box whose name is Top 10 Filter (Product Id).
- In the first dropdown box, select whether you want the top 10 value or the bottom 10 value. We choose Top.
- From that window, select whether you want to select the top 10 or 20. We choose 10.
- Then choose Items from the next dropdown box.
- Then choose based on which column are you going to filter the top 10 values. We select Average of Profit Margin.
- Click OK after this.
After clicking OK, you will notice that the Top 10 values according to the Average of the Profit Margin are showing.
And this is how we analyze the data by trimming the top 10 data in the Pivot tables in Excel.
9. Grouping Data in Pivot Table
In the Pivot Table, it is also possible to group data altogether.
- In order to do this, select multiple values by pressing Ctrl, and then right-click on the mouse.
- Then from the context menu, select Group.
- After clicking the Group command, we can see the Product ID which is now together.
- We can also group data by Region. For example, we can group the whole Asia Region under one group. And then group others as the rest of the world group
- To do this, first, create the group as shown before.
- Then select the cell and press F2.
- Doing this will make the cell enter into editing mode.
- Then we enter the group name “Rest of the World”.
- Repeat the same process for Group 2.
- Here we will enter Asia Region, as the name of the group.
- Finally, they all together will look like this.
And this is how we analyze the data by grouping them in the Pivot tables in Excel.
To sum it up, the question “how to analyze data in excel using pivot tables” is answered here with 11 different processes. Those processes can make any kind of data analysis project seamless with the Pivot Table.
For this problem, a workbook is available for download where you can practice these methods.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the ExcelDemy community will be highly appreciable.