How To Create A Pivot Table In Excel?

How To Create A Pivot Table In Excel?

7 mins read5.3K Views Comment
clickHere
Rashmi
Rashmi Karan
Manager - Content
Updated on Nov 8, 2023 17:49 IST

A pivot table is a powerful tool in Excel that allows you to summarize and analyze large amounts of data. It is essentially a way of reorganizing and manipulating data to make it easier to understand and interpret. This tutorial talks about Pivot Table in Excel. You will learn how to use it to arrange your data and make it more presentable. 

2022_04_How-to-Create-pivot-tables-in-excel-1.jpg

A pivot table in Excel is one of the most powerful tools and will always be very useful when analyzing data. Excel pivot tables allow you to analyze massive information quickly, count it, compare patterns, and validate data trends. With pivot tables, the possibilities are endless. In this article, we will show you what pivot tables are for in Excel and how to create pivot table in Excel.

Explore MS Excel Courses

Pivot Table – Definition

A pivot table is your data summary. The data is arranged in a chart form.  A pivot table allows you to report and explore trends basis the information you have provided. Pivot tables come in handy when working with massive datasets.

In other words, pivot tables are flexible and practical reports to process data and information in our Excel spreadsheets.

Must Read – What is MS Excel?

Use of Pivot Table in Excel 

Pivot tables in Excel help us work with large volumes of information. We can filter the data, view the database fields, and easily change the columns and rows within a spreadsheet.

Excel pivot tables significantly help us to:

  • Manage large amounts of data or information more straightforwardly.
  • View the data by categories or sub-categories.
  • Work with custom or more advanced Excel calculations and formulas.
  • Delve into the details of the databases or information that we want to work on.
  • Expand and contract information and highlight results.
  • Filter, sort, and group information.
  • Get different data summaries: Move rows to columns and vice versa (“Pivot”).
  • Present more professional reporting.
  • Pivot Tables are dynamic: Adding and removing fields is very easy.
  • You can work in several dimensions: You can use three dimensions: row, column, and filter.
  • It has multiple views and calculations. It facilitates changing the views of the data.
Tutorial – VLOOKUP in Excel – Shiksha Online
Tutorial – VLOOKUP in Excel – Shiksha Online
VLOOKUP, or vertical lookup, is one of Excel’s most commonly used functions for data analysis. This function belongs to the Search and Reference group, which has other search functions in...read more
COUNT Function in Excel – Shiksha Online
COUNT Function in Excel – Shiksha Online
Read on the article to understand how COUNT, COUNTA, and COUNTIF functions work in MS Excel.

Creating a Pivot Table in Excel

Creating a pivot table is not as complicated as it seems. However, knowing all the details of this process is essential to get the most out of it.

  • Prepare the data.
  • Insert your data on the excel sheet.
  • Highlight your cells to create a pivot table.
  • Drag and drop a field in “Row Labels”/ “Column Labels.” 
  • Drag and drop a field in  “Values.” 
  • Fine-tune your calculations.
IF Functions in MS Excel – Shiksha Online
IF Functions in MS Excel – Shiksha Online
Learning Microsoft Excel is all about adding more and more formulas and functions to your toolkit. To excel in MS Excel, you need to learn more and more formulae and...read more
TRIM Function in Excel – Shiksha Online
TRIM Function in Excel – Shiksha Online
If you are dealing with datasets from Excel, you know that you have to deal with characters that you’d rather have removed, such as unnecessary spaces. This happens especially if...read more
How to Combine Text Strings in Excel – Shiksha Online
How to Combine Text Strings in Excel – Shiksha Online
Microsoft Excel facilitates complicated calculations and data-heavy evaluations. While analyzing data, you may want to combine several elements into a single result. Excel’s CONCATENATE and & functions make it easy...read more

Preparing the Data 

It is vital that before creating a pivot table, you make sure you have tabular data. The data should be organized in rows and columns where each column has a title.

To create a pivot table, we follow these steps:

Step 1 – Insert your data on the Excel sheet

  • Click any cell in the source data and go to the Insert tab. 
  • Click the PivotTable button inside the Tables group. You can also choose the Recommended PivotTables option to check for other options. Excel offers other previews to insert your dynamic periodic table. You can select the preferred design. 

Step 2 – Highlight the cells to create a pivot table

  • When you click on Pivot Table, you will see that Excel is throwing recommendations through Create PivotTable dialogue box

Here the Pivot Table chooses the entire data from the dataset, but you can even select the table or a range. In the above image, Excel highlights the selected cells ($A$1:$E$13). 

Press OK once you have selected your data.

You can create a New Worksheet or Existing Worksheet. List all the column data you selected on your dataset. If you choose a new sheet, it will be added to your existing one.

The Pivot Table updates when you add data in source data. You can refresh the Pivot Table to check the updated data.

 

You can assemble your PivotTable on the extreme right of the sheet, ‘PivotTable Fields’. There are four areas of the Pivot Table –

  1. Filters – Filters enable filtering of the entire table by selecting one or more elements
  2. Columns – Columns enable to organize of the information through columns
  3. Rows – Rows enable us to organize the information in rows
  4. Values – Values allow us to visualize values ​​such as sum, maximum, average, count values, etc.

The PivotTable Fields will have the same headings as the source data. 

Now you can select the information you are looking for. We calculate the total number of vehicles sold against different categories in the given example. Once we click on a particular field, the sum total of all the columns appears.

Sum Function in Excel – Learn Through Examples
Sum Function in Excel – Learn Through Examples
In continuation to our previous tutorial on MIN and MAX functions in Excel, we move on to another important basic function, the SUM function. Learn about the SUM function in...read more
MIN and MAX Functions in MS Excel
MIN and MAX Functions in MS Excel
Learn about MIN and MAX functions in MS Excel in this blog.
Introduction to MS Excel – A Beginner’s Guide
Introduction to MS Excel – A Beginner’s Guide
Microsoft Excel has been around for decades and is one of the most desired skills if you are considering a career in data science or analysis. But there are still...read more

Step 3 – Move a field into the “Row Labels” area

You can keep adding the data for different fields to the sheet by clicking those fields. Once you click the fields, the respective data will be added to your Pivot table. You can see that a table of Departments was created when we moved Department to the Rows option.

I will now click on the Equipment Class data to the Rows to enrich my data further. You can see that the Equipment Class data is listed within the Department. The first column from the original data set was Department.

Now I will add the Equipment count for every Department and Equipment Class. A new row is added to the Pivot Table which lists the total number of equipment from every Department. 

Here I have clicked on the Date option. 

Step 4 – Move a field in the “Values” area

Now that you know how you will organize your data, you would now need to add some values by dragging a field into the Values area. The Pivot Table now shows which equipment was sold from which department on what date. By default, text fields are added as rows and numbers are added as values in Pivot Tables.

Format Your Pivot Table

You can easily format your Pivot Table using Pivot Table Tools. When you click on your Pivot Table, a new section appears above the Ribbon, Pivot Table Tools.

To add colour to your Pivot Table, you can choose Design and select the colour formatting of your choice.

You can edit the table furthermore using different options, as highlighted in the below image.

Conclusion

Pivot tables offer tremendous flexibility and versatility in data analysis. They are particularly useful for business analysts, data analysts, and anyone dealing with large datasets in Excel. Learning how to create and work with pivot tables is valuable for making data-driven decisions and extracting insights from your data.

I hope this article on creating Pivot Tables helped you to understand how to work with them. This knowledge will help you understand how to create a Pivot Table and what information you want to convey. To sort and manage your Pivot Table data, you must use functions like Sort, Trim, VLOOKUP, HLOOKUP, etc.


FAQs

What is a pivot table?

A pivot table is an advanced tool for calculating, summarizing, and analyzing data that allows you to see comparisons, patterns, and trends in your data. Pivot tables work a little differently depending on the platform you use to run Excel.

What are the characteristics of a pivot table?

A pivot table allows you to summarize large numbers of values surprisingly quickly into the groups and subgroups you specify. Then you can easily analyze the data, compare values by date or by group, and see important trends.

What should we do first to create a pivot table?

Data Preparation - It is important that before creating a pivot table you make sure you have tabular data, that is, data that is organized in rows and columns where each column has a title.

How is data arranged in a pivot table?

To arrange data in a pivot table - First, select the column you want to sort by. If the table has a header row, you will be able to see the column names in the first list of fields Then select how to sort, either by Text, Number or Date. Finally, choose between Ascending or Descending order.

What are pivot charts in Excel?

Pivot charts are just like regular charts, only they display data from a pivot table. Just like a regular chart, you'll be able to select a chart type, layout, and style to best represent your data.

About the Author
author-image
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio

Comments