Article Summary
Learn how to create a Pivot Table in Excel with a step-by-step guide to organise data, analyse information, and generate meaningful business insights.
The Pivot Table is one of the most exciting Microsoft Excel features that allows you to organise your data easily. Pivot Table in Excel helps to calculate and analyse data to see comparisons and trends from a large data set.
After you finish this article, you will know how to create a Pivot Table in Excel, understand its significance, analyse business data properly and make effective business decisions for yourself or your organisation.
Never created a Pivot Table in Excel before? No worries, I will guide you throughout the article.
Firstly, you need to know what is the usage of Pivot Table.
Usage of the Pivot Table
The Pivot Table is one of the Excel features that initially looks difficult to use. But, once you get the hang of it, Pivot Tables gradually become really easy and useful.
Therefore, take a look at different scenarios where using the Pivot Table can prove to be convenient and useful.
Compare sales total
Imagine you have a worksheet that contains monthly sales data for three different products – X, Y and Z. Your task is to figure out which of these three products has been bringing the most money.
You can look through the worksheet and manually add the sales figure to a running total every time product X appears. You can do the same for product Y and Product Z until you have the total for all of them.
Now, imagine you have a monthly sales worksheet that has thousands and thousands of rows. Sorting them manually can take an entire lifetime. A Pivot Table can help you automatically aggregate all the sales figures for product X, product Y and product Z, as well as their respective sum, in a minute.
Show product sales as percentage of total sales
A Pivot Table normally shows the totals of each row or column as you create it but it’s not the only figure you can automatically produce.
Let us assume that you’ve entered quarterly sales numbers for product X, product Y, product Z into an Excel sheet and turned this data into a Pivot Table. The table would automatically give you three totals of each column.
What if you want to find the percentage these product sales contributed of all company sales instead of the sales totals?
With a Pivot Table, you can configure each column to give you the column’s percentage of all three column totals, instead of just the column total.
For instance, if product X, product Y, product Z totaled $100,000 in sales and the third product made $50,000, you can edit a pivot table to instead say this product contributed 2% of all company sales.
Simply right-click the cell carrying a sales total and select “Show Values As” > “% of Grand Total” to show product sales as percentages of total sales in a Pivot Table.
Combine duplicate data
Suppose, you’ve just completed a blog redesign where you had to update a bunch of URLs. Your blog reporting software didn’t handle it very well and ended up splitting the “view” metrics for single posts between two different URLs.
Now, in your spreadsheet, you have two separate instances of each individual blog post. In order to get accurate data, you need to combine the view totals for each of these duplicates.
Instead of having to manually search for and combine all the metrics from the duplicates, you can summarize your data with a Pivot Table. There you go! The view metrics from those duplicate blog posts will be aggregated automatically.
Get a head count
Pivot Tables are really helpful for calculating things automatically that you can’t easily do in a basic Excel table. An interesting feature is counting rows that all have something in common.
If you have a list of employees in an Excel sheet and next to their names are the respective departments they belong to, you can create a Pivot Table that shows you the number of employees in each of the departments.
Step By Step Guide to Create a Pivot Table in Excel
Here’s an infographic for a quick step by step guide to create a Pivot Table.
Step 1
The first step is about selecting the cells you want to create a Pivot Table from.
Every Pivot Table in Excel starts with a basic table. Enter your values into a specific set of rows and columns to create that table. The topmost row or topmost column should be used to categorize your values by what they represent.
It is to be noted that your data shouldn’t have any empty rows or columns. It must have only a single-row heading.
Step 2
The second step is to insert the Pivot Table.
You can create a Pivot Table from scratch or try the ones recommended in Microsoft Office.
Select Insert > PivotTable
Step 3
For the third step, you need to choose the data you want to analyse and select a table or range.
Under Choose the data that you want to analyse, select, Select a table or range.
When you have entered all the data into your Excel sheet, you’ll want them to be in such a way that it’s easier to manage in a Pivot Table.
To sort your data, click the “Data” tab in the top navigation bar and select the “Sort” icon underneath it.
In the window that appears, you can sort your data by any column you want and in any order. If you want to sort your Excel sheet by “Total Sales”, for example, select the same column title under “Column” and then select whether you want the order to be from largest to smallest, or from smallest to largest.
Select “OK” on the bottom-right of the Sort window, and you’ll successfully reorder each row of your Excel sheet by the number of views each blog post has received.
Step 4
A range is a collection of cells in Microsoft Excel. 2 or more cells together make up a cell range. The fourth step is to verify the cell range in the Table/Range section.
The cells in a range shouldn’t necessarily be next to each other.
Step 5
The fifth step is about placement.
Under “Choose where you want the PivotTable report to be placed”, select New worksheet to place the Pivot Table in a new worksheet or Existing worksheet and then select the location you want the Pivot Table to appear.
Step 6.
The sixth and final step is about confirmation.
Once you have everything in place, click OK.
How to build a Pivot Table in Excel
In order to add a field to your PivotTable, select the field name checkbox in the PivotTables Fields pane.
It is to be noted that selected fields are added to their default areas: non-numeric fields are added to Rows, date and time hierarchies are added to Columns, and numeric fields are added to Values. If you want to move a field from one area to another, just drag the field to the target area.
Conclusion
If you’re still reading this, you have by now learned the basics of creating a Pivot Table in Excel. However, depending on your nature of work and your need for a Pivot Table, you might not be done yet.
iStudy offers a comprehensive course that encompasses beginner, intermediate and advanced levels of Microsoft Excel. This accredited all-you-need-to-know course will help you to extensively learn every aspect of Microsoft Excel.
The use and importance of Microsoft Excel is only increasing every day. It is gradually establishing itself as an indispensable tool in day to day administrative activities. It is only a matter of time until it becomes a skill that is going to give an edge to anyone in the competitive job market.
FAQs
A Pivot Table is an Excel feature that summarises, organises, and analyses large amounts of data. It allows you to quickly group, filter, calculate, and compare information without changing the original dataset.
Pivot Tables help you analyse data faster, identify trends, create summaries, compare values, and generate reports with minimal effort. They are especially useful for managing large datasets in business and finance.
Select your data range, go to the Insert tab, click PivotTable, choose where you want the Pivot Table to appear, and then drag fields into the Rows, Columns, Values, and Filters areas to organise your data.
Pivot Tables work best with well-organised tabular data that includes clear column headings and no blank rows or columns. Consistent formatting also helps ensure accurate analysis.
Yes. After modifying your source data, simply refresh the Pivot Table to display the latest information. If new rows are added outside the original data range, you may also need to update the data source.
Yes. Excel allows you to create Pivot Charts directly from a Pivot Table, making it easier to visualise trends, compare categories, and present data in a clear and interactive format.
Avoid using blank rows or columns, inconsistent data formats, duplicate headings, incorrect data ranges, and forgetting to refresh the Pivot Table after updating the source data.
No. Pivot Tables are beginner-friendly once you understand the basics. With a little practice, you can quickly learn how to summarise, filter, sort, and analyse data efficiently.