Open In App
Related Articles

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

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Report issue
Report

How to make and use Pivot Table in Excel – Quick Steps

Step 1: Select any part of your dataset

Step 2: Go to the “Insert” tab and Click on Pivot Table

Step 3: Use the default settings in the Create Pivot Table dialogue box, or adjust them as needed

Step 4: Create a new worksheet with the Pivot Table

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

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

Click on Insert to Create pivot table

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

Click on Pivot table.

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. 

Drag field name into the boxes.

Values Area

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. 

Dragged sales into ValuesSum of sales is calculated.

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

Dragging more Fields into values

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

Sum of Values.

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.

Data set of non- numeric values.

Dragging Fileds into Values.

Fields Dragged to values.

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

Total count of 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. 

Value Dragged to Rows.

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

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

Columns Area

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. 

Fields dragged to columns.

The Fields are dragged to Columns in the below image.

Values dragged to columns.

This area can accommodate many Fields. 

Filter Area

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. 

Dragging product field to Filter

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

Applying filters to Product filed

Below is the final output of the above steps.

Final output.

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

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.

Add Slicers: These help with data navigation.

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.

By following these steps, you can create a useful pivot table that’s easy to understand and helps with decision-making.

How to Refresh a Pivot Table in Excel

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.

Screenshot-(326)

Refreshing a Pivot Table automatically when opening the workbook

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.

Screenshot-(327)

Also Read

FAQs on Pivot Table

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.



Last Updated : 06 Dec, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads