How To Create A Pivot Table In Excel?

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. 

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.

• Pivot Table – Definition
• Use of Pivot Table in Excel
• Creating a Pivot Table in Excel
• Format Your Pivot Table

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.

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.

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.

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.