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
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.
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.
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.
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
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.
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.
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.
Share your thoughts in the comments
Please Login to comment...