Remove Old Items – Excel Pivot Table Drop Down

After you create an Excel pivot table, the source data usually changes. New records are added, and old records might be changed or deleted.

Later, when you refresh the pivot table, you should see a summary of your updated data, but sometimes there’s a problem – old data sticks in the drop down lists.

Example: Region Names Changed

To show this pivot table problem, I made short video, which you can see in the next section.

My sample file has data from a fictional sales company, and the source data was changed:

  • Central region was merged into the East region.
  • Sales records were changed from Central to East

After making those changes, I refreshed the pivot table. As expected, the Central region’s name disappeared from the Region headings.

However, Central still shows up in the Region drop down.

old region name in pivot table drop down list

Video: Clear Old Pivot Table Items

In this short video, I show how you can clear the old Region name from the pivot table drop down list.

Doing this will also prevent old items from appearing in this pivot table, in the future.

Video: Clear Old Items – Default Settings

In Excel 365, and Excel 2019, it’s even easier to avoid old items in pivot table drop downs.

Instead of changing this setting for every pivot table that you create, you can change it once, in your Excel default settings for Pivot Tables.

In the video below, I show the steps for changing an individual pivot table, like I did in the previous video.

Then, at the 2:57 mark, I show how to change the default setting, in Excel 365. You can skip to that section, if you’d like!

Prevent Old Items in Pivot Table

To stop old items from showing in an existing pivot table, follow the steps below.

NOTE: This setting will affect all the pivot tables that use the same pivot cache.

  • First, right-click a cell in the pivot table
  • Next, in the right-click pop-up menu, click on PivotTable options
  • In the PivotTable Options dialog box, click on the Data tab
  • In the Retain Items section, there is a drop down for “Number of items to retain per field”
    • By default, that is set to Automatic.
  • Click the drop down arrow, and select None from the drop down list.
  • Click OK, then refresh the pivot table.

Prevent Old Items in Pivot Table

Get the Excel Workbook

To get the sample file, go to the Clear Old Items page on my Contextures site.

That page also has Excel macros that you can use, to

  • change the Retain Item settings for all pivot tables in the workbook
  • change Excel’s default settings for pivot tables (Office 365 or Excel 2019 and later)

More Pivot Table Tutorials

Show and Hide Pivot Items

Refresh Pivot Table

Grouping Data

Pivot Filters, Multiple

Pivot Filter Macros

_______________________

Remove Old Items – Excel Pivot Table Drop Down

Remove Old Items - Excel Pivot Table Drop Down

_______________________

Quick Count Unique (Distinct) Items in Excel Pivot Table

It’s easy to get a sum in a pivot table, or a total count. But how can you count unique items in an Excel pivot table?

For example, if you’re analyzing sales data, you might need to show these types of counts:

–How many unique products were sold in each store?
–How many distinct people made sales in each region?

Continue reading “Quick Count Unique (Distinct) Items in Excel Pivot Table”

Fix Excel Pivot Table Time Rounding Problem

If you add times to an Excel Pivot Table, and format the time to show tenths of a second or hundredths of a second, zeros might appear after the decimal point.

The decimals for tenths of a second or hundredths of a second are rounded to zero, and changing the pivot table number format does not fix the problem.

Pivot Table Zero Decimals

In the screen shot below, I’ve highlighted the problem in the pivot table time column.

  • maximum time for the A team is 5:15:25 – 5 minutes, and 15 and 25 hundredths seconds
  • pivot table shows the time as 5:15:0 – 5 minutes, and 15 seconds

Instead of rounding the decimal to 2, it rounded to zero.

Pivot Table Time Rounding Problem

Video: Fix Pivot Table Time Rounding Problem

In the video below, I show how this time rounding problem can happen, and the steps to fix the problem.

There are written steps below the video, and more details on my Contextures site, on the Pivot Table Time Fields page.

Video Timeline

  • 0:00 Introduction
  • 0:12 Build a Pivot Table
  • 0:55 Format the Time
  • 1:38 Fix the Time Problem
  • 2:43 Format the New Field

Fix Pivot Table Time Rounding Problem

To fix the pivot table times, so they show tenths of seconds and hundredths of seconds, you can use a simple workaround.

To begin, follow these steps to add a column in the source data:

  • First, add a new column in the pivot table source data–in this example, TimeCalc
  • Next, in the new column, enter a formula with a simple link to the original time value cell in that row.
    • In the screen shot below, cell D2 has this formula: =C2
    • If the source data is in a named Excel table, the formula should automatically fill down to the last row.
  • Leave the new column in General format – do NOT change it to a time format

formula with link to original time cell

Add New Field to Pivot Table

After you add the new column to the source data, follow the steps below, to update the pivot table.

  • To refresh the pivot table, right-click on a pivot cell, and click the Refresh command
  • The new field will appear in the pivot table field list, where you can drag it to the pivot table’s Values area.
  • To show the maximum times, right-click on one of the new values, click Summarize Values by, and then click Max

Format as Time With 2 Decimals

Next, follow the steps below to format the times:

  • First, right-click on any cell in new pivot value column, and click the Value Field Settings command
  • Click the Number Format button, and click on the Custom category at the left.
  • To show the times with 2 decimal places, format the values with this custom number format: m:ss.00
  • This time format shows tenths of a second, or hundredths of a second.
  • Click the OK button to apply the formatting

The numbers are formatted correctly in the new field, tenths of a second, and hundredths of a second

To complete the changes, you can remove the original time field, which had the rounded tenths of a second or hundredths of a second, showing a zero instead of the correct numbers.

numbers are formatted correctly

Get the Excel Workbook

To get the sample Excel file that I used in the video, go to my Contextures site, on the Pivot Table Time Fields page. The zipped file is in xlsx format, and does not contain any macros.

____________________

Fix Excel Pivot Table Time Rounding Problem

Fix Excel Pivot Table Time Rounding Problem

____________________

4 Ways to Build Pivot Table from Multiple Sheets

In a perfect world, if you need to make a pivot table, the data is nicely organized in a table, and you can connect to that, quickly and easily.

Unfortunately, as you know, things aren’t  always perfect, especially when it comes to data! And sometimes the data is in two or more separate tables, so you need to combine it somehow, before you can build a pivot table..

4 Ways to Combine Data for Pivot Table

There are different ways you can combine data from multiple tables in Excel. For example:

  1. Power Query
  2. VSTACK Formula
  3. Excel Macros
  4. Pivot Table Wizard

Combine Data Videos

In the sections below, there are a couple of short “Combine Data” videos that I’ve made recently.

  • The first video shows how to use the VSTACK function, which is available in Excel 365. It returns multiple ranges in a vertical stack, so it’s easy to combine tables that have identical structures.
  • The second video shows how to combine data using the old Pivot Table Wizard. It creates a pivot table with several limitations, but it might do what you need – if you don’t need anything fancy!

For all 4 methods, you can find detailed steps, and sample files, on my Contextures site, on the Pivot Table from Multiple Sheets page .

create named range for VSTACK formula cell spill range
create named range for VSTACK formula cell spill range

Video: Create Pivot Table from 2 Tables

Here’s the VSTACK function video, in which I combine the data from tables on 2 separate worksheets. It only takes one cell with a formula, to return all the data from the two tables.

I included the headings for the first table too, because pivot table data needs headings!

Video Timeline

  • 00:00 Pivot Table from Multiple Sheets
  • 00:20 VSTACK Function
  • 00:52 VSTACK Formula
  • 01:21 Combined Data
  • 01:39 Named Range
  • 02:11 Add Pivot Table

Pivot Table Wizard

What if you don’t have Power Query, or the Excel VSTACK function. And you don’t want to use Excel macros?

In that case, you can use the old Pivot Table Wizard to do the job. It’s well hidden in newer versions of Excel, but in the video, I’ll show you how to open it, with an Excel keyboard shortcut.

Video Timeline

  • 0:00 Data on 2 Sheets
  • 0:24 Open PivotTable Wizard
  • 0:50 Select Sheet Ranges
  • 1:08 Page Field Settings
  • 1:29 Adjust the Pivot Table
  • 2:04 Show Sum
  • 2:15 Page Field

Get the Sample File

For all 4 methods to combine data, you can find detailed steps, and sample files, on my Contextures site.

Follow this link, to go to the Pivot Table from Multiple Sheets page .

____________________________

4 Ways to Build Pivot Table from Multiple Sheets

4 Ways to Build Pivot Table from Multiple Sheets
4 Ways to Build Pivot Table from Multiple Sheets

____________________________

Move or Lock Pivot Table Field List in Excel

When you create a pivot table, and select a cell in it, a pivot table field list usually appears, at the right side of the Excel window. See how you can adjust that list’s layout, width, and position. Also, see how we moved pivot fields in the olden days – do you remember the PivotTable Wizard?

Continue reading “Move or Lock Pivot Table Field List in Excel”

Quick Trick – Add or Move Excel Pivot Fields

Here’s a quick trick to add or move Excel pivot table fields, just by typing.

Add or Move Pivot Table Fields

Instead of dragging a pivot field into the layout, you can type its name over an existing pivot field label.

  • Tip: This is a great shortcut if you like to keep your hands on the keyboard while working in Excel!

Here are the steps to add or move pivot table fields on the worksheet:

  • First, change the pivot table to Outline layout or Tabular layout. This trick will not work in Compact layout.
  • Next, click on a cell that contains a pivot field name – a cell where you want a different field to appear
  • In that cell, type the name of the pivot field that you want to add
    • Be careful to type the field name correctly
    • If you make a typo, the field won’t be added
  • Finally, press Enter,  to complete the pivot table  layout change
type over pivot field label on worksheet
type over pivot field label on worksheet

Pivot Field Added

After you press Enter, the pivot table layout changes.

  • The field that you typed moves into the active cell.
  • The existing fields shift down, and the added field takes its new position.
pivot field added to worksheet layout
pivot field added to worksheet layout

More Tips for Moving Labels

The first video above shows how to move pivot fields.

You can use a similar trick to move the pivot items in a pivot table.

The short  video below shows how to move the Excel pivot items, and you can find written steps on the Move Pivot Table Labels page on my Contextures site.

Get the Sample File

To try the  quick trick to add pivot fields, you can use your own pivot table, or download the Move Pivot Labels sample file from my Contextures website.

The zipped Excel file is in xlsx format, and does not contain any macros.

_________________

Quick Trick – Add or Move Excel Pivot Fields

Quick Trick - Add or Move Excel Pivot Fields
Quick Trick – Add or Move Excel Pivot Fields

____________________

Show Percent of Group Total – Excel Pivot Table

In an Excel pivot table, you can use Excel’s built-in custom calculations, for a different view of the data.

For example, in the video below, I set up a pivot table to show what % of a company’s  monthly sales were Binders. Also, what %  of Binder sales was for each colour – red, blue, and black.

Note: You can get the sample Excel file that I used for this video on the Pivot Table Show Values As page, on my Contextures site.

Binder Sales – % By Colour

In the screen shot below, the pivot table has 4 fields in the worksheet layout:

  • Item and Colour in the Row area
  • Month in the Column area
  • Sum of Units sold, in the Values area
pivot table with monthly sales per product colour
pivot table with monthly sales per product colour

Following the steps listed in the section below, I used pivot table custom calculations, to show additional sales details:

  • the % for each colour‘s sales – Black, Blue and Red
  • compared to the item‘s total sales
  • in each month – Jan and Feb

Show % of Item Sales

In the pivot table screen shot below, I’ve added a 2nd copy of the Units field to the values area.

  • Tip: To add a 2nd copy of a pivot field, drag it from the PivotTable Field list, onto the Values area in the field list

For the 2nd copy of the Units field, I followed the steps below, to change that field’s calculation settings

  • Right-click one of the Units value cells
  • In the pop-up menu, click Show Values As
  • In the next pop-up menu, click % of Parent Row Total

In the pivot table, the second Units field changed, to show:

  • the % for each colour‘s sales
  • compared to the item‘s total
  • in each month.

For example,

  • 195 Binders were sold in January
  • 51% (100 units) of those January Binders were Black colour
Show Percent of Subtotal in Excel Pivot Table
Show Percent of Subtotal in Excel Pivot Table

More Pivot Table Info

For more Pivot Table Subtotal tips, videos and examples, visit the following links:

Show Percent of Subtotal in Pivot Table

Pivot Table Show Values As % of Parent Total

Show Percent Of Subtotal In Pivot Table

______________________

Show All Pivot Table Items To Compare Sales Easily-No Data

By default, the Pivot Table shows only the items for which there is data. In the example shown in this video, not all colours were sold to each customer. You may wish to see all the items for each customer, even those items with no data.

Continue reading “Show All Pivot Table Items To Compare Sales Easily-No Data”