Let’s begin the post with a truth – Pivot Table in Excel is undoubtedly the most underutilized tool in Microsoft Excel. It is so powerful that it allows you to analyze more than 1 million rows of data with just a few mouse clicks. It also highlights and presents key information to the user in an easy-to-read table.
But what exactly is a Pivot table and why is it so popular, and more importantly, how to create a Pivot Table. Let’s go through all these questions one by one in this post.
What is a Pivot Table in Excel?
A Pivot Table in Excel is a dynamic report that you can generate in MS Excel. It builds an engaging and interactive view of your data. This table helps you drill down and analyze data with minimal effort and with little to no knowledge of the complex formula behind it.
You can easily segregate your data set into different categories. You can also visualize this data in several charts such as a pie chart, bar chart, and more. Unlike Static tables, a pivot table in Excel helps you interact and study your data in several ways.
How to Create a Pivot Table in Excel?
Before you can create a pivot table in Excel, you need to identify the data set that you wish to analyze. In the example below, we have highlighted how you can create Pivot Tables with some sample data.
The below sample sales data table contains 91 records with 7 fields of information: Customer, Region, Order Date, Sales, Cost, Month, and Year.
Then select all of the data, including the headers for each column which may contain different data types. Proceed to select Insert and choose the Pivot Table option. This will create a Pivot Table.
Step 1: Organize Your Source Data
Before you can create a pivot table in Excel, you need to get all your information organized in an Excel spreadsheet. By importing data to an Excel spreadsheet, you can save valuable time. Here’s how to do it:
- Open a new Excel spreadsheet, then select the “Data” tab at the top.
- Select the import option that fits your data. Your data is probably in a CSV file format, but several file types can be imported depending on the software that is being used.
Step 2: Create a Pivot Table
After importing the raw data into Excel, you can create a pivot table to organize and analyze the data. Click inside your dataset. We have a table of sales data of a few companies spread across different regions and over different years in the below example.
- Go to Insert > Pivot Table
- Place the Pivot Table in a New or Existing Worksheet.
- Drag and Drop the fields.
One of the benefits of using a Pivot table is the ability to create and choose fields that you wish to analyze. This selected field can be a Filter, Column Row, or Independent Value. The selected field can be placed in one of four areas –
Here we are creating a report wherein the rows are the regions, columns are the sales, and the values inside the pivot table will be the sum of sales (based on the grouping of year and region).
Remember how our data looked back in the initial step? If not, take a second and scroll back up. It’s much easier to analyze the information now. Each region is only listed once, and its total sales are calculated. You can even see the grand total sales from all the regions. We have converted the raw data from the initial spreadsheet into an organized pivot table.
How to convert text to number in Excel
VLOOKUP in Excel: What do you need to know
Step 3: Pivot the Data
In case you want to see the total sales each month over different years, you don’t need to start from scratch. Just uncheck the region box and click on the customer field instead. Make sure the “Month” and “Year” fields appear in the “Rows” section on the bottom right of your screen.
Conditional Formatting Cells in Excel Pivot Tables
The simplest way to highlight data in your pivot table is by using Conditional Formatting rules.
This can help you easily visualize your data. For example, if a specific criterion is satisfied, you can convert that cells to a particular color. This is a good way to visualize your results and also assists you in identifying different variables effectively.
To apply conditional formatting in your Pivot Table:
- Choose a particular cell in your data set
- Select Home> Conditional Formatting> New Rule
- Select Apply the rule to the third option – “All cells showing “Sum of SALES” values for “REGION” and “YEAR”
- Choose the rule type as – “Format only cells that contain”
- You can alter the conditions in the description by selecting the Cell Value>Greater Than>Select the condition.
- Proceed to select the cell format. You can choose a color and click OK.
Conditional formatting can help create patterns and trends in your data more apparent.
Using Calculated Item in a Pivot Table
A really neat and interesting feature of the Excel Pivot Table is that it lets you perform a diverse array of mathematical calculations with your item list. You can use /,+,*, or, %, compute average or summation, or more. To apply this feature:
- Choose a particular dataset that you wish to perform a mathematical computation on
- Choose the Pivot Table and select Options > Fields, Items & Sets> Calculated Item
- Name the calculated column as Year on Year Variance
- Set the formula to use the years: ‘2020’-‘2021’. Click OK.
- The calculations have now been applied.
Pivot Table and Calculated Field
Pivot Table in Excel Calculated Fields allows you to do mathematical calculations with your field list. Pivot Table calculated fields can be used to calculate percentage increases in sales, margin calculations, cost of goods sold, etc.
- Click on the pivot table and go to Options > Fields, Items, & Sets > Calculated Field.
- Give the new field name Profit.
- Set the Formula for the Calculated field as (SALES-COST)/SALES.
- Right-click on the new column and select Number Format.
- Select Percentage and click OK.
As you can see, in the below table, the Profit is calculated and displayed as a percentage.
Drill Down and Analyze Data
While performing a detailed analysis of the given data in a pivot table, you will easily know what makes up a specific value. This requires simply double-clicking a particular cell.
This will result in opening a new sheet that contains multiple rows of data, which, in turn, displays the breakdown of that particular value. For example, double-clicking on the Grand Total of Sales in the year 2020 reveals the below details:
A Pivot Chart is an effective visual aid used for presentations and to measure performance and compare it to your sales or relevant management strategy. To work on this –
- Click on any cell in your Pivot Table.
- Then click on the PivotChart icon from the PivotTableAnalyze tab.
- Choose a chart from the list.
In the example below, the North region performed extremely well in 2021 in comparison with the other regions. This can be easily identified through the generated charts.
How to use If Formula in Excel
How to apply Round Off Formula in Excel
Pivot Table Reports
The Pivot Table function of Excel has excellent report generation capabilities. You can select three types of reports – Compact, Outline, and Tabular.
You can choose any of the above report layouts by clicking on the table and then going to PivotTable Tools > Design > Report Layouts.
Now, choose between Classic, Outline, and Tabular layouts.
Key Benefits of Pivot Table
- A basic pivot table in Excel is very easy to set up and customize with a few clicks. You do not need to learn complicated formulas.
- You can create a pivot table in a few minutes and have a large dataset organized for analysis and generate useful reports.
- Pivot tables are extremely flexible and can be rapidly rearranged according to your requirements.
- Unlike Excel formulas, it will not lock you into a particular view.
- Results generated by a pivot table have a high degree of accuracy. In fact, the pivot table can effectively highlight errors in your dataset.
- Pivot tables are designed to constantly update when new data are included. All you need to do is click the refresh button, and the pivot table gives the latest analysis.
- Additionally, a pivot table can automatically apply consistent number and style formatting, even as data changes.
- Lastly, pivot tables contain several tools for sorting, data filtering, and ranking data.
Microsoft Excel is a spreadsheet application that was developed and published by Microsoft. It is part of the Microsoft Office productivity software suite. One can add rows and columns to enter and store as much data as they want.
Rows and columns meet in a space called a cell, where we can perform a variety of tasks ranging from simple ones like calculating addition and enabling macros to more complicated ones like concatenation in Excel.
The next time you hear the word “Pivot Table”, do not scream in frustration. Despite their reputation, Pivot Tables are not as complicated as you think. With data being the “oil” of modern times, you need tools like Pivot Tables to help you:
- Analyze and visualize large data sets with Pivot Charts.
- Compare, summarize and make sense of multiple records in a spreadsheet.
- Report and present those insights in an easy-to-understand format
EasySheet is a tech blog that features amazing Excel hacks, tips, and tricks. Our goal is to make Excel accessible to beginners and provide them with solutions using EasySheet.
If you require assistance with Microsoft Office products, such as MS PowerPoint, Excel, Word, ,Publisher, or anything else, we are the best place to turn.