Excel Pivot Table: The Ultimate Guide – What You Need to Know About This Amazing Tool

What is a pivot table?

pivot table excel, create a pivot table, pivot table tutorial

A pivot table is a powerful analysis tool in Excel. It is used to summarize large amounts of data and create charts, graphs, and reports.

Pivot tables are a powerful tool that allows you to quickly analyze data in an excel spreadsheet. They allow you to slice and dice your data in a variety of ways.

A pivot table is a summary of your data, packaged in a chart that lets you report on and explore trends based on your information. Pivot tables are particularly useful if you have long rows or columns that hold values you need to track the sums of and easily compare to one another.

In other words, pivot tables extract meaning from that seemingly endless jumble of numbers on your screen. And more specifically, it lets you group your data in different ways so you can draw helpful conclusions more easily.

The “pivot” part of a pivot table stems from the fact that you can rotate (or pivot) the data in the table to view it from a different perspective. To be clear, you’re not adding to, subtracting from, or otherwise changing your data when you make a pivot. Instead, you’re simply reorganizing the data so you can reveal useful information from it.

Pivot Tables and Their Uses

what is a pivot table, how to use a pivot table, what does a pivot table look like

Pivot tables are a great tool for data analysis that allows you to summarize and analyze large datasets. You can easily convert raw data into actionable insights without the hassle of programming languages.

Pivot tables are a versatile tool that can be used in various ways to help you analyze your data. It’s also an easy and useful way to make interactive dashboards and reports with little or no coding.

We can use a pivot table to create interactive dashboards and reports with little or no coding required. Pivot tables make it easy for us to summarize and analyze large datasets, we can easily convert raw data into actionable insights without any hassle of programming languages.

Pivot tables play an important role in the future of research as they help with the analysis of qualitative studies as well as quantitative ones

Pivot Tables and Data Analysis

data analysis tools for excel, how to make pivot tables in excel

Data analysis is an important aspect of the business. It helps leaders understand how their business is performing. Pivot tables are an easy way to analyze data in Excel and can be used to answer common questions like, “How much revenue did you make last month?”

Excel pivot tables are one of the more popular data analysis tools for Excel. They’re also one of the easiest to use, as they only require simple drag-and-drop functionality.

Create a PivotTable in Excel

Select the cells you want to create a PivotTable from.

Select Insert > PivotTable.

Under Choose the data that you want to analyze, select a table or range. 

In Table/Range, verify the cell range.

Under Choose where you want the PivotTable report to be placed, select a new worksheet to place the PivotTable in a new worksheet or Existing worksheet, and then select the location you want the PivotTable to appear.

Building out your PivotTable

To add a field to your PivotTable, select the field name checkbox in the PivotTables Fields pane.

To move a field from one area to another, drag the field to the target area.

Before you get started:

Your data should be organized in a tabular format, and not have any blank rows or columns. Ideally, you can use an Excel table like in our example above.

Tables are a great PivotTable data source because rows added to a table are automatically included in the PivotTable when you refresh the data, and any new columns will be included in the PivotTable Fields List. Otherwise, you need to either change the source data for a PivotTable or use a dynamic named range formula.

Data types in columns should be the same. For example, you shouldn’t mix dates and text in the same column.

PivotTables work on a snapshot of your data, called the cache, so your actual data doesn’t get altered in any way.

If you have limited experience with PivotTables, or are not sure how to get started, a Recommended PivotTable is a good choice. When you use this feature, Excel determines a meaningful layout by matching the data with the most suitable areas in the PivotTable. This helps give you a starting point for additional experimentation. After a recommended PivotTable is created, you can explore different orientations and rearrange fields to achieve your specific results.

Recommended pivot table

1. Click a cell in the source data or table range.

2. Go to Insert > Recommended PivotTable.

3. Excel analyzes your data and presents you with several options, like in this example using the household expense data.

4. Select the PivotTable that looks best to you and press OK. Excel will create a PivotTable on a new sheet, and display the PivotTable Fields List.

Create a Pivot table as per your choice

1. Click a cell in the source data or table range.

2. Go to Insert > PivotTable.

3. If you’re using Excel for Mac 2011 and earlier, the PivotTable button is on the Data tab in the Analysis group.

4. In the Choose where you want the PivotTable report to be placed section, select New Worksheet or Existing Worksheet. For Existing Worksheet, select the cell where you want the PivotTable placed.

5. Click OK, and Excel will create a blank PivotTable, and display the PivotTable Fields list.

In the Field Name area at the top, select the check box for any field you want to add to your PivotTable. By default, non-numeric fields are added to the Row area, date and time fields are added to the Column area, and numeric fields are added to the Values area. You can also manually drag-and-drop any available item into any of the PivotTable fields, or if you no longer want an item in your PivotTable, simply drag it out of the Fields list or uncheck it. Being able to rearrange Field items is one of the PivotTable features that makes it so easy to quickly change its appearance.

By default, PivotTable fields that are placed in the Values area will be displayed as a SUM. If Excel interprets your data as text, it will be displayed as a COUNT. This is why it’s so important to make sure you don’t mix data types for value fields. You can change the default calculation by first clicking on the arrow to the right of the field name, then selecting the Field Settings option.

Instead of using a calculation to summarize the data, you can also display it as a percentage of a field. In the following example, we changed our household expense amounts to display as a % of Grand Total instead of the sum of the values.

Once you’ve opened the Field Settings dialog, you can make your selections from the Show data as tab.

Simply drag the item into the Values section twice, right-click the value, and select Field Settings, then set the Summarize by and Show data as options for each one.

If you add new data to your PivotTable data source, any PivotTables that were built on that data source need to be refreshed. To refresh just one PivotTable, you can right-click anywhere in the PivotTable range, then select Refresh. If you have multiple PivotTables, first select any cell in any PivotTable, then on the Ribbon go to PivotTable Analyze > click the arrow under the Refresh button and select Refresh All.

If you created a PivotTable and decide you no longer want it, you can simply select the entire PivotTable range, then press Delete. It won’t have any effect on other data or PivotTables or charts around it. If your PivotTable is on a separate sheet that has no other data you want to keep, deleting that sheet is a fast way to remove the PivotTable.

Select a table or range of data in your sheet, and then select Insert > PivotTable to open the Insert PivotTable pane.

You can either manually create your own PivotTable or choose a recommended PivotTable that was created for you. Do one of the following:

On the Create your own PivotTable card, select either a new sheet or an Existing sheet to choose the destination of the PivotTable.

On a recommended PivotTable, select either a new sheet or an Existing sheet to choose the destination of the PivotTable.

If needed, you can change the Source for the PivotTable data before you create one.

In the Insert PivotTable pane, select the text box under Source. While changing the Source, cards in the pane won’t be available.

Make a selection of data on the grid or enter a range in the text box. 

Press Enter on your keyboard or the button to confirm your selection. The pane will update with new recommended PivotTables based on the new source of data.

In the PivotTable Fields area at the top, select the check box for any field you want to add to your PivotTable.

By default, non-numeric fields are added to the Rows area, date and time fields are added to the Columns area, and numeric fields are added to the Values area.

You can also manually drag-and-drop any available item into any of the PivotTable fields, or if you no longer want an item in your PivotTable, simply drag it out of the Fields list or uncheck it. Being able to rearrange Field items is one of the PivotTable features that makes it so easy to quickly change its appearance.

By default, PivotTable fields that are placed in the Values area will be displayed as a SUM. If Excel interprets your data as text, it will be displayed as a COUNT. This is why it’s so important to make sure you don’t mix data types for value fields. You can change the default calculation by first clicking on the arrow to the right of the field name, then select the Value Field Settings option.

Instead of using a calculation to summarize the data, you can also display it as a percentage of a field. In the following example, we changed our household expense amounts to display as a % of Grand Total instead of the sum of the values.

Once you’ve opened the Value Field Setting dialog, you can make your selections from the Show Values As tab.

If you add new data to your PivotTable data source, any PivotTables that were built on that data source need to be refreshed. To refresh the PivotTable, you can right-click anywhere in the PivotTable range, then select Refresh.

If you created a PivotTable and decide you no longer want it, you can simply select the entire PivotTable range, then press Delete. It won’t have any effect on other data or PivotTables or charts around it. If your PivotTable is on a separate sheet that has no other data you want to keep, deleting that sheet is a fast way to remove the PivotTable.

Select OK.