In the Excel PivotTable Options dialog box, there are lots of settings that you can turn on or off. Most of the settings work the way you’d expect, but the “Show the Values Row” option might not seem to do anything. You turn it on and off, and nothing changes. Here’s an explanation of how, and when, the “Show the Values Row” pivot table option works, and you can see the steps in the video below.
Category: Layout
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 “How to Troubleshoot Pivot Items Missing From Excel Pivot Table”
Remove Sum Of From Pivot Table With Macro
When you put value fields into a pivot table, it automatically adds text to the headings, such as “Sum of Quantity”, if the field name is Quantity. You can manually change those headings, to remove the extra text, if there are only a few of them. If there are lots of fields to change, it’s quicker to remove that text with a macro.
Continue reading “Remove Sum Of From Pivot Table With Macro”
Change Pivot Table to Outline Layout With VBA
When you create a new pivot table in Excel, it uses the Compact Layout, by default. That layout has all the row fields in a single column, with each field slightly indented from the previous field. You can read more about the Compact Layout on my Contextures website.
NOTE: If you have my PivotPower Premium add-in installed, you can select a layout in your Preference Settings. That makes it easy to apply all your favourite settings to a new pivot table, or any existing pivot tables.
Continue reading “Change Pivot Table to Outline Layout With VBA”
Get Total Amount from Specific Pivot Table
If you have multiple copies of a pivot table in a workbook, on different sheets, you can use the GETPIVOTDATA function to pull the total amount from a specific pivot table. In this example, there are 3 copies of the pivot table in a workbook, each with a different layout and filter applied.
The sheets are named consistently, starting with “PT_”, and the pivot table location is the same on each sheet.
Continue reading “Get Total Amount from Specific Pivot Table”
Remove Sum Of in Pivot Table Headings
When you add fields to the Values area, they are set as either Sum or Count, and the field is automatically renamed. For example, Quantity becomes “Sum of Quantity”, or “Count of Quantity”. These long field names can make the columns wider than necessary, but if you try to change those names, Excel might show an error message — “PivotTable field name already exists.”
Compare Top and Bottom Products
In this video, a pivot table summarizes the products sales over a two year period. With a Value Filter, you can quickly show the top products, and compare top and bottom products.
Continue reading “Compare Top and Bottom Products”
Show Fiscal Year and Month Totals
Show Text in Pivot Table Values Area
Usually you can only show numbers in a pivot table values area, even if you add a text field there. In the screen shot below, the Max of Region ID is in the Values area.
Instead of the numbers 1, 2 or 3, we’d like to see the name of the region – East, Central or West.
Format Date Field Subtotals
When you show subtotals for a pivot table date field, the dates might not be formatted like the rest of the dates. We’ll take a look at why this happens, and how you can fix it.
For example, in the screen shot below,
- the date (in row 4) is formatted as d-mmm-yy
- the subtotal date (in row 8) is formatted as mmm/yy
