Pivot Table in Excel: Create and Explore

What Is a Pivot Table in Excel?

A Pivot Table is a data analysis tool in Excel. Its main purpose is to summarize and analyze a large amount of data and present it in a short and structured format. The rearranging and summary help users understand complex data.

A simple pivot table looks like this:

sample pivot table


Different Parts of Pivot Table

  • Rows Area: This is mainly the row headers of a Pivot Table. It generally has at least one field. It is also possible to have no fields in this area. Multiple fields form a hierarchy of data.

rows area in a pivot table

  • Columns Area: This holds the column headers. Unlike the Rows Area, it needs at least one field to function. It can have multiple fields, creating a hierarchy.

columns area in a pivot table

  • Values Area: This contains the field of actual data for analysis. When compared with a normal table, these are the values or frequencies of column or row headers.

values area in a pivot table

  • Filter Area: This is an optional section of a pivot table. It is a drop-down list that can filter the data in the Values Area.

filter area in a pivot table


How to Create a Pivot Table in Excel

The following figure contains sales information about a store’s different locations, the cashier’s receiving date, and customer names. It also contains whether the customer is a new or recurring customer.

sales information analysis dataset

The above dataset is a bit complex to analyze because it contains raw data. It has 205 rows of entries. The Pivot Table can manipulate the presentation of these data to satisfy individual scenarios.

Create a Pivot Table from this data.


Method 1 – Using Insert Tab from the Ribbon

To create a Pivot Table from our existing dataset, follow these steps.

  • First, select a cell inside the dataset.
  • Go to Insert >> Tables >> PivotTable.

inserting pivot table steps

  • In the box that appears, select whether you want the table on the existing sheet or a new sheet.

selecting location for pivot table

  • Click on OK.
  • A new sheet will open up (based on the option you have selected). It will contain the pivot table. The sheet will have the PivotTable Fields window on the right.
  • To create Row Values and Column Values, drag those fields into their respective ones. We have dragged Cashier to the Rows field and Bill to the Values field.

This is the simplest form of a pivot table. The table displays how much each cashier has charged.

dragging fields to create pivot table


How to Create Pivot Tables from Other Sources in Excel

The method above creates a pivot table within the workbook. If you select the new sheet option, it usually creates the table in a previous sheet.

What if the dataset belongs to a different workbook or other data source? We have the following dataset in another workbook.

dataset in an external source for pivot table

Note: The dataset should start from cell A1 in the external source for this method.

Using these steps, we will use this to create a pivot table in our current workbook.

  • Go to Insert >> Tables.
  • Click on the drop-down arrow under PivotTable.
  • Select From External Data Source from there.

selecting option for external source

  • Click on Choose Connection in the box that pops up.

choosing connection for external source

  • In the External Connections box, select Browse for More.

browsing connection for external source

  • Navigate to your external workbook and open it.
  • Now select the sheet you want to load data from and click OK.

choosing sheets from external connection workbook

  • Close all the boxes after that.
  • Drag your preferred fields into the PivotTable Fields.

The pivot table is created from an external Excel file, and it describes the salaries of different players.

pivot table created from external excel file


Method 2 – Using Recommended PivotTables Option

Excel can pick up the pattern of a dataset and recommend a custom Pivot Table with fields. You don’t have to drag fields while creating a Pivot Table manually. This feature is available with the Recommended PivotTable tool.

Here is how we can use this tool to create the same Pivot Table described in the first method using the same dataset.

  • Select a cell inside the dataset.
  • Go to Insert >> Tables >> Recommended PivotTable.

using recommended pivottables feature

  • On the left of the Recommended PivotTable box, you can find several pivot table formats. Select the one you prefer and click OK.

Excel recommending pivot table types

Depending on the option you select, the pivot table will appear on a new sheet or the existing sheet.

pivot table created from recommended pivottables


How to Add Field to a Pivot Table in Excel

We have already mentioned dragging fields in a pivot table. The beauty of the pivot table is that you can drag and rearrange fields if you don’t like the summarization it gives initially.

To add a field to the pivot table:

  • Click on the field name in the PivotTable Fields
  • Without releasing the mouse button drag it to the desired field (row, value, column, or filter).
  • Release the button after that.

Here is a pivot table we have created from our dataset that shows the total bills by location.

initial pivot table fields

Dragging and dropping the cashier column will divide the location-based bills divided by cashiers.

cashier field in rows

Taking it to the columns area will reorganize the same values.

cashier field in columns

Putting the cashier field in the values area will add a column showing how many cashiers attended bills in each location.

cashier field in values

Dragging the cashier field to the filters area will create a filter over the pivot table. You can select bills by each cashier using this filter.

For example, we wanted to find out sales by Benjamin from our dataset. Selecting Benjamin from the filter will give us that.

cashier field in filters


How to Show Different Calculations in Value Fields

A pivot table usually shows the sum of different counts of numeric values. Unless it is a non-numeric value. In those cases, the pivot table shows the count results.

The pivot table we have created shows the sum values of bills for the cashier.

pivot table created from recommended pivottables

We can convert them into % values of the total bill in the original dataset. Selecting Show Values As from the context menu after right-clicking.

changing to % values from context menu

Then you can select % of Grand Total, and the pivot table will display calculations in percentages.

sum of bill in percentages


How to Create a Two-Dimensional Pivot Table in Excel

Let’s go back to the pivot table we have previously created, showing bills by each cashier.

pivot table created from recommended pivottables

The raw dataset of this table also contained customer names, their bills, dates, the store they had purchased from, and whether or not they were new or existing customers.

But the pivot table shows only the cashiers with the bills they have charged. If we want more information out of this, we need to create more dimensions in the table.

For example, we may need to know how much bills were paid based on the store locations. Follow these steps to see how we can do that.

  • Select a cell from the dataset.
  • Go to Insert >> Tables >> PivotTable.

inserting pivot table steps

  • Select where you want the table to appear in the following box and click OK.
  • Drag the “Store” field in the Columns area beside dragging Cashier to Rows and Bill to Values. This will create the two-dimensional pivot table.

dragging fields for two dimensional pivot tables


How to Create Multi-Level Pivot Table in Excel

In the previous sections, we covered single- and two-dimensional pivot tables. A pivot table can also have more dimensions.

For example, we can classify the number of new and old customers each cashier billed in the row labels. With column labels, we can classify bills in each store location.

We need to follow these steps to create such pivot tables.

  • Select a cell in the dataset and go to Insert >> Tables >> PivotTable.

inserting pivot table steps

  • In the sheet where the pivot table appears, you will find the PivotTable Field
  • Drag “Bill” to the “Values” area, “Store” to the Columns area, “Cashier” and “Status” to the Rows area to create the multi-level pivot table.

dragging fields for multidimensional pivot tables

Multiple Value Field:

You can drag the same field twice in an area. Dragging “Bill” twice to the Values field will create two of the same values to occur in the pivot table.

creating duplicate values in pivot table


How to Remove a Field from a Pivot Table

While creating multi-dimensional tables, we have seen how we can add extra fields to a pivot table. However, we can remove a field from a pivot table in two ways when it seems unnecessary.

In the following figure, we have a filtered version of the pivot table we created previously.

location filtered in pivot table

Using the two methods, we will remove customers’ (existing or new) status and the second set of bills.

  • Unchecking the field from the PivotTable Analyze window can remove the field from the pivot table.

removing fields by unchecking

  • Dragging the field from a particular area in PivotTable Fields also removes the field from the table.

removing fields by dragging


How to Change Summary Calculation of Pivot Tables

The pivot table shows the sum values of numbers and can also display other parameters, such as averages, counts, percentages, etc.

Let’s go back to the pivot table we previously created.

bills by cashier and location

It shows the total sales in each store billed by each cashier. Changing these values to average will count average bills by cashiers in each store location.

Follow these steps to change the summary calculation of a pivot table in Excel.

  • Right-click on a value field on the pivot table.
  • Select Value Field Settings from the context menu.

selecting value field settings from context menu

  • In the Value Field Settings box, select Average under the Summarize value field by.

selecting average value to summarize

  • After clicking OK, the sum values will change to the average in the pivot table.

pivot table summary value changed to average

Besides showing averages, it can also display count values. This indicates how many times a bill was attended to by each cashier in each store location.

summary of count values in pivot table

It can also show the maximum bills in each store location for different cashiers.

summary of maximum values in pivot table

The same goes for minimum values too.

summary of minimum values in pivot table


How to Use PivotTable Analyze Tab in Excel

Selecting a cell inside a pivot table creates two additional tabs in the ribbon: PivotTable Analyze and Design. You can access different tools from the PivotTable Analyze tab.

tools and design tab in pivot table

With the PivotTable Analyze tab, we can perform the following tasks.

Changing Pivot Table Name: Inside the PivotTable group, you can change the name of the table from the PivotTable Name box. You can refer back to this table using this name in formulas.

pivot table name field in Excel

Options: After selecting the Options under the PivotTable group, you can get access to the Options box.

pivot table options from Excel ribbon

You can access the following options from this box.

  • The Layout & Format tab can control how the pivot table displays its field values and labels.

layout and format tab in options

  • The Totals & Filter tab contains different grand total display, filter, and sort options.

totals and filter tab in options

  • You can access different fields’ display options from the Display tab.

display tab in options

  • How the pivot table will print can be controlled from the Printing tab.

printing tab in options

  • You can control the source data and the refresh rate of the pivot table from the Data tab.

data tab in options

  • The Alt Text tab allows us to add a title and description for the pivot table.

alt text tab in options

Field Settings: You can find the Field Settings inside the Active Field group. Using the Value Field Settings you can:

  • Change the summary values in the Summarize Value Field By tab.

summarize values by tab in field settings

  • Format how field values are displayed from the Show Values As tab.

show values as tab in field settings

Expand and Collapse Field: After selecting a field in the pivot table, you will get the Expand Field and Collapse Field options in the Active Field group. This only works when there are hierarchies in the multi-level pivot table.

expand and collapse field in the ribbon

Grouping and Ungrouping Fields: Selecting a field enables two more options in the Group tab: Group Selection and Ungroup.

Group Selection groups multiple fields that can be treated as one later on. Ungroup reverses the process.

grouping and ungrouping option in the ribbon

Filter:

  • With the Insert Slicer option from the Filter group, you can add slicers. These slicers work as external graphic filters for the data.
  • Insert Timeline adds a slicer based on dates. The original dataset must contain dates for this option to work.
  • The Filter Connection offers options for different filters to connect to the pivot table.

different filter options in the ribbon

Data Changes in the Pivot Table:

  • You can refresh a pivot table using the Refresh option from the pivot table. Changes in the original dataset will be visible in the pivot table after using this option.
  • With Change Data Source, you can completely change all values to a second dataset and disregard the first one.

refresh and data source changing option in the ribbon

Actions Group:

  • You can clear a pivot table or parts of a pivot table using the Clear options from here.
  • With the Select option, you can select a part or a complete pivot table.
  • Move PivotTable option will move the table to a new location.

actions group in the ribbon

Calculation Options: Different Fields, Items, and Sets options are available under such names in the Calculations group.

calculations group in the ribbon

Plotting Charts and Changing Table:

  • You can create a chart using the organized pivot table data using the PivotChart option from the Tools group.
  • With the Recommended PivotTables option under the same group, you can change the table fields to a preset format.

tools group in the ribbon

Additional Display Options: You can toggle the Field List, Headers, +/- buttons on and off using the options available in the Show tab.

different display options in the ribbon


How to Insert a Calculated Field and Calculated Item in a Pivot Table

Let’s simplify our pivot table for a better understanding of this topic.

locations filtered in a pivot table

The calculated item will create groups in the field with modified new values, and the calculated field will create new columns based on a new formula. We will explain both in this section.

Calculated Field:

Suppose we want to calculate an extra 1% commission in the event of more than $10,000 sold. Other cases are not applicable. We can find the eligible cases and the amount of money using the calculated field very easily.

Follow these steps to add such a calculated field to the pivot table.

  • Select a cell in the pivot table and go to PivotTable Analyze >> Calculations >> Fields, Items, & Sets >> Calculated Field.

selecting calculated field option for pivot table

  • In the Insert Calculated Field box, insert the following formula in the Formula field and click Add.
= IF(Bill>10000,Bill*1%,0)

adding formula for calculated field

  • After clicking OK, the pivot table will now look like this.

new calculated field values added

Calculated Item:

In the same pivot table, let’s assume Benjamin, Elizabeth, and Emily are on one team and the rest are on the other. We want to calculate how much each group has sold. The pivot table can do that using the calculated item feature.

Follow these steps to see how we can do that.

  • Select a cell from the row labels.
  • Go to PivotTable Analyze >> Calculations >> Fields, Items, & Sets >> Calculated Item.

selecting calculated item option for pivot table

  • In the Formula field, type in the following formula and click Add.
= SUM(Benjamin,Elizabeth,Emily)

adding new group with formula

  • Insert the following formula for group 2.
= SUM(Mia,Noah)

adding second group with formula

  • Click OK, and you will get the total sales of each group.

summary of two groups added


How to Group Pivot Table Items in Excel

Method 1 – Group Row Labels

In the previously created pivot table, we want to add Benjamin, Elizabeth, and Emily to a single group.

locations filtered in a pivot table

We can use the following steps to group them together.

  • Select the cells you want to group.
  • Go to PivotTable Analyze >> Group >> Group Selection.

creating new groups from ribbon

Now the pivot table will group them.

new groups created from the ribbon option

Note: If you want to put the rest into another group, as we did in the previous section, you need to select them and repeat the same process manually.


Method 2 – Group Dates

To understand the date grouping, we slightly modified our pivot table, using the dates as the row labels.

pivot table with date rows

  • Right-click on a row label and select Group from the context menu.

grouping option from context menu

  • If you prefer to shorten the table, select a starting and ending date. In the By field, select Months (as we are grouping the dates by months).

grouping dates by months

After clicking OK, we will get the total bills based on months.

dates grouped by month in pivot table


How to Ungroup Pivot Table Items

Grouping items simplifies the data in a pivot table. If you want the detailed version of the data or to get the previous view back, you can use the following steps to ungroup them.

  • Select a cell inside the row labels.
  • Go to PivotTable Analyze >> Group >> Ungroup.

ungrouping option from the ribbon

Excel will ungroup the data we have grouped before.

dates ungrouped in pivot table

Note: You can ungroup using the option from the context menu too. It will open the same dialog box and you have to follow the same steps.


How to Refresh Pivot Table in Excel

As marked in the following figure, we are making a slight change to a value in our original dataset.

changing a value in the original dataset

The table value doesn’t change after changing this value.

pivot table value is not changing

We need to refresh the pivot table if we want the updated value in the table.

Refresh a pivot table using the following steps.

  • Select a cell inside the pivot table.
  • Go to PivotTable Analyze >> Data >> Refresh.

refresh option for pivot table in the ribbon

The table will now update according to the latest value.

pivot table value is now updating


How to Change Data Source of a Pivot Table in Excel

Here is another dataset in our workbook. We may want the pivot table to get data from this dataset instead of the previous one. We can always create a new pivot table in this scenario.

new data source for pivot table

Having too many pivot tables will make the Excel file unnecessarily large. Have this data in the previously created pivot table only.

We can change the data source of the pivot table to the new dataset using the following steps.

  • Select a cell inside the pivot table.
  • Go to PivotTable Analyze >> Data >> Change Data Source.

change data source option in the ribbon

  • A new box will pop up. Select the second dataset in the Table/Range field here.

selecting new data source

  • The pivot table will revert to its initial view.
  • Drag the new fields to the desired areas to get the pivot table.

pivot table after changing data source


How to Move Pivot Table to a New Location in Excel

  • Select a cell in the pivot table.
  • Go to PivotTable Analyze >> Actions >> Move PivotTable.

option for moving pivot table

  • Select the new cell in the Location.

selecting new location to move the pivot table to

  • Clicking OK will shift the pivot table to the new position.

pivot table changed to a new location


How to Change Pivot Table Field List View

Selecting a cell inside the pivot table will usually open the PivotTable Fields window at the right of the worksheet.

pivot table fields window appearing after seleting the table

Excel provides us with features to turn it on and off, along with changing this view too.

Turning Pivot Table Field List View On/Off:

  • Go to PivotTable Analyze >> Show.
  • Select/Deselect Field List to show/hide the field list.

disabling the pivot table fields view

Changing Field List View:

The PivotTable Fields window has a tool button. Clicking on it opens a drop-down list with options for different views. You can change the preferred layout.

See the figure below for more details.

changing pivot table field list view

 


How to Format Pivot Table in Excel

Show/Remove Subtotals

Subtotals are visible in pivot tables with grouped fields. Take such a table we have previously created where all the cashiers are classified into two groups.

pivot tables divided into two groups

  • To hide/unhide subtotals from a pivot table, select a cell and go to Design >> Layout >> Subtotals. Select the option you prefer.

different subtotal options for pivot table

You will get the default view with the Do Not Show Subtotals option.

  • Selecting to show the subtotals either at the top or bottom will give us something like this:

displaying subtotals for groups in a pivot table


Show/Remove Grand Totals

When we create a pivot table, it contains the “Grand Total” columns and rows by default.

grand totals showing in a pivot table

To turn it off:

  • Select a cell inside the pivot table.
  • Go to Design >> Layout >> Grand Totals.
  • Select Off for Rows and Columns.

turning off grand totals for pivot table

There are other options to explore here, too, like having the grand totals for rows only, columns only, or both.


Remove “Row Labels” and “Column Labels” from Table

You may have noticed the “Row Labels” and “Column Labels” cells with filters on a pivot table.

column labels and row labels in pivot table

These show the table’s row and column headers. Using the filter beside these labels, we can filter out certain fields.

The terms are not presentable, and Excel has features to change that.

To change this:

  • Select a cell inside the pivot table.
  • Go to Design >> Layout >> Report Layout.
  • Click on Show in Outline Form from the drop-down.

selecting outline form to remove row labels and column labels

The “Row Labels” will turn into “Cashier” and “Column Labels” into “Store”.

row labels and column labels cells changed to meaningful headers


Pivot Table Style Options

You can explore different options in the PivotTable Style Options group of the design tab.

  • Checking Row Headers and Column Headers on/off will add or remove formatted headers from the table.

row headers and column headers option in the ribbon

Note: The Row Headers option changes are only visible in some of the table presets.

  • You can have every other row filled with the Banded Rows option checked.

selecting banded rows for pivot table

  • The same goes for columns with the Banded Columns option.

pivot table with banded columns


Change Pivot Table Styles

There are two main styles to choose from in a pivot table:

  • You can access some quick styles in the PivotTable Styles group of the Design tab.
  • Select the downward-facing arrow to access more designs. Create, save, and delete custom styles from here.

changing pivot table styles


Change Date Format in Excel Pivot Table

Rearrange our pivot table for dates as row labels.

pivot table before changing date format

The dates in the pivot table are in “mm/dd/yyyy” format. To change this format, follow these steps.

  • Select all the cells containing dates.
  • Press Ctrl+1 to open the Format Cells dialog box.
  • Select the date style you prefer. We have selected the “dd-mmm” format here.

selecting a date format

The dates will now look like this after clicking OK.

date format changed in pivot table


How to Copy Pivot Table in Excel

We will copy the below pivot table into a new cell, A13.

pivot table with bills of cashier and locations

  • Select the whole table. Select a cell in the table and press Ctrl+A.
  • Select PivotTable Analyze >> Actions >> Select >> Entire PivotTable.

selecting entire pivot table

  • Press Ctrl+C to copy the table to the clipboard.
  • Paste using Ctrl+V after selecting the desired cell to paste it into.

pivot table copied 


How to Sort a Pivot Table in Excel

In this section, we will explain how to sort a pivot table. We can sort a table based on both fields or values.

Both sorting methods are the same. We will demonstrate sorting by values and use the same pivot table for reference.

  • Right-click on a cell of the pivot table.
  • Select Sort from the context menu.
  • Select your preferred sorting option from the list.

different soring option in context menu for pivot table

  • Sorting from smallest to largest option gives us the dataset like the following.

pivot table data sorted

Note: While sorting by labels, you need to right-click on any of the row or column labels.


How to Filter a Pivot Table in Excel

In the pivot table parts, we have mentioned the Filter field. The original dataset contained a record of whether the transaction was from an existing customer or a new one.

status column for filtering in the original dataset

The pivot table doesn’t mention that.

pivot table without status mentioned

The pivot table combines both the data instead because we didn’t mention separating them when we created the table.

We can separate them by adding the status as a field to the row or column labels. Use it as the filter in this section.

To add the status as a filter, drag the “Status” field inside the Filter area of the PivotTable Fields.

dragging status to the filter field

You can now filter existing and new customer bills using this new field.

using filter in the pivot table


Add Slicer in Excel to Filter Pivot Table Quickly

A slicer can add more graphic elements to the pivot table, making it more user-friendly to filter and manipulate data in it.

To add a slicer, follow these steps.

  • Select a cell inside the pivot table.
  • Go to PivotTable Analyze >> Filter >> Insert Slicer.

insert slicer option in the ribbon

  • Select a field inside the Insert Slicers We are selecting “Status” here to filter new and existing customer bills.

selecting the field for slicer

  • Click OK, and a slicer will appear on the worksheet.

new slicer added in the worksheet

You can now select a type from this filter and get the same result as the Filter field.

usage of slicer in the pivot table


How to Extract Data from a Pivot Table Quickly

Excel has the GETPIVOTDATA function to extract data from a pivot table. The syntax of the function is:

=GETPIVOTDATA(data_field, pivot_table, [field1, item1], …)
  • data_field: This is the name of the field we extract data from.
  • pivot_table: A reference to the pivot table. This can be a cell, a range, or the whole table.
  • [field1, item1]: Optional fields to describe the data we want to fetch from the table.

Suppose we want to get the following information from our pivot table.

particulars for extracting data from the pivot table

In cell C14, we need to use the formula this way.

=GETPIVOTDATA("Bill",$A$3,"Cashier",$B14,"Store",C$13)

extracting data using the GETPIVOTDATA function

Fill out the formula for the rest of the cells to get all the values.

all data extracted from pivot table

The A3 is the reference point in the pivot table we are using. “Bill”, “Cashier”, and “Store” are the value, row, and column fields of the pivot table.

The argument pairs indicate to match the row of Elizabeth from the cashier and Elk Grove from the store. It returns the bill (from the second argument) of these matches from the pivot table (from the first argument).

Note: You can insert an equal sign (=) and click on a pivot table cell to get the formula directly. The arguments are hard-coded in such a way. You can’t use the fill handle that way without changing them to cell references.


How to Calculate Difference in Pivot Table

This is our pivot table after some slight modifications. We have filtered out two columns and grand totals to make it compact. The table displays the sum of bill values each cashier makes in different stores.

some location filtered form pivot table

We will change the fields between areas to show the difference from its previous entry.

Follow these steps to calculate the difference in the pivot table.

Calculate Difference in Rows:

  • Create another value field for bills in the pivot table.

duplicating bill values in pivot table

With this step, we are copying the same bill values to make changes to those entries.

  • Right-click on a duplicated value or column field. Select Value Field Settings from the context menu.

value field settings in the pivot table context menu

  • In the Value Field Settings dialog box, select the Show Value As tab.
  • Select Difference From in the Show Value As drop-down.
  • Select Cashier as the Base Field and previous as Base Item.

selecting options to calculate difference in pivot table

  • Click OK and you will get the difference value in each row from the previous rows in the duplicate values.

difference calculated in rows of pivot table

The first entries are blank. Because there is no previous value to differentiate from here.

Calculate Difference in Columns:

  • Right-click on a duplicated column or value field. Select Show Values As from the context menu, and then select Difference From.

selecting difference from option from the context menu

  • Select Store in the Base Field and (previous) in the Base Item.

selecting difference calculation option from the dialog box

  • Clicking OK will give the difference in each column.

difference calculated from columns

Notice that the first entries are blank. Because there is no previous value to differentiate from here.


How to Display Percentage Contribution in a Pivot Table

If we go back to the original pivot table we have created, we can see the sum of bill values in each store made by each cashier shown in it.

pivot table of bills by cashier and location with grand total

We dragged the bill values into the field column to create the pivot table. See the percentage contribution of each entry to the total sales now.

We can achieve that using the following steps.

  • Right-click on a value or column field.
  • Select Show Values As and then % of Grand Total from the context menu.

the % of grand total option from context menu

The pivot table will now display the percentage contribution.

pivot table values showing as % of total value


How to Use Pivot Tables for Frequency Distribution in Excel

Here are the steps to do that.

  • Right-click on a value or column field.
  • Select Value Field Settings from the context menu.

selecting value field settings for frequency distribution

  • Go to the Summarize Values By tab in the Value Field Settings dialog box, and select Count.

selecting count for frequency distribution in pivot table

  • Clicking OK will give the total bill counts instead of the total bills.

frequency distribution values in pivot table


How to Create PivotChart from a Pivot Table

  • Select a cell in the pivot table and go to PivotTable Analyze >> Tools >> PivotChart.

pivotchart option to create dynamic chart from pivot table

  • Select the type of chart you prefer from the Insert Chart box.

chart options to select from

  • Clicking OK will give us the pivot chart on the worksheet.

a dynamic pivot chart showing bills by cashiers and locations

Revert the values to sums, the chart changes accordingly.

pivot chart is updating with table


How to Remove a Pivot Table in Excel

To remove a pivot table, select the entire table first. Select a cell in the table and press Ctrl+A for that. You can select PivotTable Analyze >> Actions >> Select >> Entire PivotTable.

selecting entire pivot table

Go to PivotTable Analyze >> Actions >> Clear >> Clear All.

clearing a pivot table

This will remove the pivot table and all its caches from the workbook.

Note: You can also press Delete after selecting the table. It won’t remove the cache from the workbook.


Issues Related to Pivot Table in Excel


Method 1 – New Data Not Including After Refresh

Solution:

One way to solve this is to change the pivot table’s data source and select the whole range again. However, we have to do this every time we enter data.

Another way is to convert the dataset into a table before turning it into a pivot table. For that, select a cell in the dataset and press Ctrl+T.

transforming a dataset into a table

Click OK and the dataset will turn into a table.

sales data analysis dataset turned into table

Insert a pivot table now. When you insert new data into this table, it will update after a refresh.


Method 2 – Changing Certain Field Names

Here is a simple dataset we have created.

sum of bill column in pivot table

Change the name of the column “Sum of Bill” to “Bill”, Excel won’t let us. Because there is a field with such a name in the pivot table cache.

can’t change the sum of bill to bill

Solution:

Instead of “Bill”, we can insert “Bill “ (a space at the end). The pivot table will not find both the same, and we can also get the desirable cell value.

changing column name to bill with a space


Method 3 – Blank Value in Pivot Table Cells

This is a pivot table we created in a previous example. It shows the total bills of new customers in different branches served by different cashiers.

blank value in pivot table

You can notice that some values here are blank instead of showing anything. The blank value occurs because no such values existed in the original dataset. For example, Elizabeth didn’t serve any new customers at the Broadway branch for that duration. Hence the blank value in the pivot table.

The zero or blank values in the original dataset also cause the same result in a pivot table.

Solution:

In these cases, you can display 0, “null,” or other values. Follow these steps to do so.

  • Right-click on a cell in the pivot table and select PivotTable Options.

pivottable options in the context menu

  • In PivotTable Options, go to the Layout & Format tab.
  • Check For empty cells show and insert a value beside the option’s field.

option to display 0 instead of blank

  • Click OK, and the blank values will turn into those you inserted.

pivot table showing 0 instead of blank values


Method 4 – Grouping One Pivot Table Affects Another

Here are two pivot tables created from the same dataset.

two pivot tables from the same source

We group some labels in one table, it categorizes the labels in the other one too.

actions in one pivot table affecting the other

This happens because both tables use the same cache.

Solution:

  • Select one pivot table and press Ctrl+X to cut the table.
  • Paste it onto a new workbook (It has to be a workbook, not for new sheets).
  • Refresh the pasted table in the new workbook.
  • Now copy and paste it back to the original place.

Now grouping one won’t affect the other, even in the same worksheet.

actions in one pivot table no longer affecting the other


Method 5 – Pivot Table Not Keeping Number Formatting

Solution:

  • Right-click on a value or column field and select Value Field Settings from the pivot table.

selecting value field settings from context menu to change format

  • Click on Number Format in the Value Field Settings

number format option in value field settings

  • Select proper formatting from the Format Cells box now.

selecting a format from format cells dialog box


Method 6 – Refreshing Pivot Table Changes Column Width

Here is a pivot table with an extra column width.

extra gap in pivot table column

When we refresh the table, it automatically fits the column width.

refreshing a pivot table changing column width

However, there can be cases where you want to keep the extra width, and this feature won’t let you do that.

Solution:

  • Right-click on any cell in the pivot table and select PivotTable Options from the context menu.

selecting pivottable options from the context menu

  • Uncheck the Autofit column width on update in the dialog box.

option to fix column width while refreshing a pivot table

Now, refreshing the table won’t change any of its column widths.


Download Practice Workbook


That concludes our discussion on the pivot table feature in Excel. We have covered different parts of a pivot table, how to create different types of pivot tables, and how to create them from different sources. We have also covered interactions of different fields in a pivot table, changing summary calculations, calculating differences, etc.

The article describes different tools for the pivot table available in Excel and how we can change the design and labels in the table. It also includes copying, selecting, moving, and removing a pivot table. In the end, we have discussed some common issues users face regularly with the feature and how to solve them.


Pivot Table in Excel : Knowledge Hub


<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo