What is Pivot Table in Excel?

Let’s begin the post with a truth – Pivot Tables 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 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 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, 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, 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 –

• Filters
• Columns
• Rows
• Values

Here we are creating a report wherein the rows are the regions, columns are the sales, 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 their 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.

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 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, %, 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 Calculated Fields allow 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:

Pivot Chart

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.

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 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 to generate useful reports.
• Pivot tables are extremely flexible and can be rapidly rearranged according to your requirements. 
• Unlike the Excel formula, 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, filtering, and ranking data. 

How Lio can help you

Lio is a great platform that can help entrepreneurs, homemakers, students, businessmen, managers, shop owners and many others. This mobile application helps to organize business data and present them in an eye-catching manner. 

Lio is a great platform for small business owners and can track a wholesome record of employee information for better employee management, customer data, etc. You can handle those data with ease.

If you want to be a professional, then you must save time, you need to learn to arrange all the business strategies in one place. In that case, Lio can be your partner.

Entrepreneurs can also allow multiple authorized users of their office to access the information from various locations within minutes.

Lio is definitely for the win and using it for your business is only going to make your journey smooth and easy to track.

Step 1: Select the Language you want to work on. Lio on Android

Step 2: Create your account using your Phone Number or Email Id.

Verify the OTP and you are good to go.

Step 3: Select a template in which you want to add your data.

Add your Data with our Free Cloud Storage.

Step 4: All Done? Share and Collaborate with your contacts.

Conclusion

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