Pivot Table Show Values As % of Parent Total

With a pivot table, it’s easy to show a total Sum or Count, or other quick summaries. You can also use the Show Values As options, to show each number compared to other items in the pivot table. There are several options, such as running total, % of Grand Total, and Difference From other amounts. In this example, we’ll show the units sold for each item, as a % of parent total (% of subtotal).

Continue reading

Posted in Calculations | 2 Comments

Create Pivot Table from Existing Cache

When you create a pivot table in Excel, a pivot cache is automatically created. The pivot cache is a special memory area where the pivot table records are saved. If there are multiple pivot tables in a workbook, they might use the same pivot cache, or different pivot caches. Use these macros to create a new pivot table from an existing pivot cache, and choose a specific cache – by cache number or pivot table location.

Continue reading

Posted in Excel Pivot Table | 2 Comments

Pivot Table Compares Weekdays in Fiscal Year

If you’re tracking retail sales year over year, it’s not too helpful to compare sales by calendar date. The dates will fall on different weekdays each year, so slow weekdays will be compared to busy days. To get a better comparison, add a few formulas to the sales data, then use a pivot table to line up the weekdays.

Continue reading

Posted in Excel Pivot Table | Leave a comment

How to Troubleshoot Pivot Items Missing From Excel Pivot Table

Why are a couple of pivot items missing from this pivot table? The source data has these employees marked as “Yes”, but that information isn’t showing up in the pivot table summary. How can we troubleshoot pivot items missing from our pivot table?

Continue reading

Posted in Layout | Leave a comment

Macro to Create a Pivot Cache List in Excel Workbook

In a large Excel file, with several pivot tables, it’s easy to lose track of how many pivot caches there are. Instead of trying to manually figure things out, use this macro to create a pivot cache list.

Continue reading

Posted in Programming | Leave a comment

Refresh an Excel Pivot Table on a Protected Sheet

When you protect a worksheet, you can add a check mark in the options list, to allow the use of pivot tables and pivot charts. However, even with that option turned on, you can’t refresh an Excel pivot table on a protected sheet. Use this macro to turn off the protection and update the pivot table.

Continue reading

Posted in Refresh | Leave a comment

Excel Pivot Table Printing Macro

Usually, pivot tables are used for exploring data in the workbook. Occasionally though, you might need to print out a pivot table, to include in a report. To help with that, use the pivot table printing macro below, and visit my website for more examples.

Continue reading

Posted in Print and Extract | Leave a comment

Pivot Table Report Filter Sort Macro Saves Time

When a pivot field is in the row or column area, you can sort the pivot items in that fields, using the built-in commands. For fields in the Report Filter area, there isn’t an easy way to sort the pivot items. I’ll show you how to sort manually, or use the pivot table report filter sort macro below to do the job quickly and easily

Continue reading

Posted in Sort and Filter | Leave a comment

List All Pivot Fields and Pivot Items in Excel

While you work on a big pivot table in Excel, you might need to document which fields are in the layout. To make that job easy, use this macro to list all pivot fields and pivot items in any pivot table’s row, column and filter areas. Download the free workbook, and use the macro in your own files.

Continue reading

Posted in Programming | Leave a comment

Pivot Table Custom Subtotals in Excel

In a pivot table, subtotals are automatically added to the outer fields, when you add more fields below them. The innermost field doesn’t show subtotals, but you can force them to appear, by creating pivot table custom subtotals. Learn more about pivot table subtotals on my Contextures website.

Continue reading

Posted in Group and Total | Leave a comment