How to Create a Pivot Table in Excel: A Step-by-Step Guide

Pivot tables are one of the most useful features in Excel. They are used to summarize or aggregate lots of data. The summarization of the data can be in the form of average, count, and other statistical methods. It converts lots of data into a few rows and columns of summary. They make it very easy to view the data from different perspectives and angles and are great for exploring data. 

What is a Pivot Table in Excel?

A Pivot table is a summary of your data package. The word ‘Pivot’ in the Pivot table means to rotate the data in Excel to view it from a different perspective. Creating a Pivot table doesn’t mean adding, subtracting, or changing the data, it simply means reorganizing the data so you can easily work with useful information.

Data Format Tips:

• Use clean, tabular data for the best report.
• Better to organize your data in columns, instead of rows.
• Ensure all columns have headers, with a single row of unique, non-blank labels for each column. Avoid double rows of headers or merged cells.
• Format your data as an Excel table (select anywhere in your data and then select Insert > Table from the ribbon).
• If you have complicated or nested data, use Power Query to transform it (for example, to unpivot your data) so it is organized in columns with a single header row

What are pivot tables used for?

Pivot tables are tools meant to simplify the process of summarizing large datasets efficiently. They enable users to gain insights, visualize, and analyze numerical data comprehensively.

Some of the main functions of a Pivot Table are:

1. Comparing Sales Totals of Different Products
2. Showing Product Sales as Percentages of Total Sales
3. Combining Duplicate Data
4. Getting an Employee Headcount for Separate Departments
5. Adding Default Values to Empty Cells

How to Create a Pivot Table

Step 1: Select any cell inside the data set of which a pivot table has to be created.

Step 2: Go to the Insert tab

Step 3: In the Tables group, click on the Pivot table tool

Step 4: Fill the Dialog Box

A dialog box would open where we have to fill in the two choices for the data to be analyzed and the place where we wish to have the pivot table. After filling in the options, click on OK.

Note: By default the data location of the pivot table will be a new worksheet.

Step 5: In the new sheet, we can see the pivot table and other options

How to Build a Pivot Table Report

On the left side of the sheet, a new empty pivot table has been created where the summary would be shown. On the right side, we can see the FIELD NAME which are the headers of the columns of our data set. FIELD NAME is to be dragged to empty boxes i.e Filters, Columns, Rows, and values to show their corresponding values in the Pivot Table.

Let’s drag the FIELD NAME into the boxes and see their effects individually. 

1. Values Area in Excel

Values sum up all the entries in the FIELD NAME dragged in it. Here, as Sales are dragged here, our pivot table shows the sum of all the sales that took place. 

We can add as many FIELD names as we require in Values. Individual sums would be shown then.

Dragging fields into values will give you the sum of values as a result.

If the entities in the column can’t be summed, it will give us the total count of the entries present in that column.  Here as Country and Product do not contain numeric values, it returned the total count of each column.

Dragging Fileds into Values.

In the below image, you can find the Count of the Values.

The data in the pivot table gets grouped (Row-Wise) by the Field Names dragged to the Rows Area. 

In this example, we have grouped the sales by the countries. 

In the below image, the fields are dragged to Rows.

We can drag as many Fields as we require in this region.

2. Columns Area in Excel

The data in the pivot table gets grouped by(Column-Wise) by the Field Names dragged to Columns Area. As here, row-wise, our data is grouped by Countries and column-wise, it is grouped by Discount Band. 

The Fields are dragged to Columns in the below image.

This area can accommodate many Fields. 

3. Filter Area in Excel

The filter is an important feature in the pivot table. using which we can filter out the data based on the Field dragged into it. Here, we have filtered the total sales based on one particular product that is only that product is considered while calculating the sales. 

You can also apply many features to the Product fields as shown below.

Below is the final output of the above steps.

In this way, using pivot tables, a summary of the data is achieved in the form of a matrix. There are many other tools and features of the Pivot Tables which can be explored. 

How to Design and Improve Pivot Table

Here are the steps to design and improve a Pivot Table:

1. Choose the Right Data: Use data that has the information you need, with clear headers.

2. Make the Pivot Table: Follow the earlier steps to create it

3. Pick Rows and Columns: Decide which data to show as rows and columns.

4. Use Filters: Add filters to focus on specific parts of the data.

5. Do Math: Use built-in or custom calculations for insights.

6. Make it Look Good: Adjust fonts, colors, and formatting.

7. Sort and Group: Organize data the way you want.

8. Dig Deeper: Right-click to see more details.

9. Update Data: If it changes, refresh the table.

10. Add Charts: Include visuals for better understanding.

11. Label Clearly: Make sure everything is easy to understand.

12. Test and Adjust: Review and improve as needed.

13. Explain How to Use: If sharing, provide instructions.

14. Add Slicers: These help with data navigation.

How to Refresh a Pivot Table in Excel

1. Refresh the Pivot Table data manually

To manually refresh the data in a Pivot Table, follow these steps in Excel:

Step 1: Click inside the Pivot Table that you want to refresh to select it.

Step 2: Go to the “Data” tab on the Excel ribbon.

Step 3: Look for the “Refresh” button in the “Data Tools” group. It may also be labeled as “Refresh All” if you have multiple Pivot Tables in your workbook.

Step 4: Click the “Refresh” button.

2. Refreshing a Pivot Table automatically when opening the workbook in Excel

Step 1: Click inside the Pivot Table to select it

Step 2: Go to the “PivotTable Analyze” or “Options” tab on the Excel ribbon, depending on your Excel version.

Step 3: Look for the “Options” group, and within that group, locate and click on “Options” (or “PivotTable Options” in older versions).

Step 4: In the PivotTable Options dialog box that appears, select the “Data” tab.

Step 5: Check the box that says “Refresh data when opening the file.”

Step 6: Click “OK” to save your changes and close the dialog box.

How to Copy a Pivot Table

Here are the steps to copy a Pivot Table:

• Step 1: Select the entire pivot table
• Step 2: Copy the pivot table.
• Step 3: Choose the destination
• Step 4: Paste the pivot table.

How to Delete a Pivot Table

Here are the steps to delete a Pivot Table:

• Step 1:Select the pivot table you want to delete.
• Step 2: Press the “Delete” key

How to Sort a Pivot Table

Here are the steps to sort a Pivot Table:

• Step 1: Select the column or row you want to sort
• Step 2: Sort in ascending or descending order.

Conclusion

In conclusion, making a pivot table in Excel is a great way to understand big sets of data. By following the steps in this guide, you can organize and see your data better, helping you make smart decisions. Learning how to use pivot tables well is important for managing and analyzing data effectively.

Pivot Table – FAQs

Is there any inbuilt functions option to display data in the values area of the pivot table?

No, There is n inbuilt function to display data in the values area of the pivot table you can display the text fields in rows and columns areas, and the count of the data can be easily calculated.

What are the advantages of the Pivot table?

A pivot table can be used to summarize, analyze, explore, and present data in a summary format so that better results can be obtained. Pivot Charts can be a  beneficial part of the pivot table, allowing for comparison easily.

How to delete the pivot table?

Click on the Pivot table. On the Analyze Table, and then pick the entire pivot table and then pick the entire pivot table and set delete all the content.

What are the advantages of using a Pivot table?

A pivot table is an easy and interactive way to quickly summarize large amounts of data. A pivot table can be used to analyze numerical data in detail. The pivot table is specially designed for querying large amounts of data in many simple ways.

How can we group Dates in Pivot Table?

To group dates in an Excel pivot table:

• Right-click on any date in your pivot table
• Select “Group” from the context menu, and then choose the desired time period (e.g., months, quarters, years) for grouping.

How can we add a Calculated Field in a Pivot Table?

To add a Calculated Field in an Excel pivot table, follow these steps:

• Select any cell within your pivot table to activate the PivotTable Tools on the ribbon.
• Go to the “PivotTable Analyze” tab, click “Fields, Items, & Sets,” and then select “Calculated Field.”
• In the “Insert Calculated Field” dialog box, enter a name for your field, define the formula using the fields available in your pivot table, and then click “OK.”

How can we remove Grand Total from a Pivot Table?

To remove the Grand Total from a Pivot Table in Excel, follow these steps:

• Click anywhere inside the Pivot Table to activate the PivotTable Tools on the ribbon.
• Go to the “Analyze” or “Design” tab (depending on your Excel version).
• Click on “Grand Totals” off to the side.
• Choose “Off for Rows and Columns” to remove the grand totals.