Category Archives: 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 … Continue reading

Posted in Layout | Leave a comment

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 … Continue reading

Posted in Layout | Leave a comment

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 … Continue reading

Posted in Layout | Leave a comment

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 … Continue reading

Posted in Layout | Leave a comment

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 … Continue reading

Posted in Layout | 14 Comments

Compare Top and Bottom Products

An Excel pivot table is a great way to summarize a large amount of data, and with a Top 10 filter, you can show the top products, or the worst selling ones In this video, a pivot table summarizes the … Continue reading

Posted in Layout | 1 Comment

Show Fiscal Year and Month Totals

In a pivot table, you can show totals for each calendar year or month: either group the dates,

Posted in Layout | 4 Comments

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, … Continue reading

Posted in Layout | 4 Comments

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  … Continue reading

Posted in Layout | Leave a comment

How to Show Missing Items in Pivot Table

When you create a pivot table from your source data, it only shows the items that have data. For example, if you put Customers and Products in the Row area, it only lists the items that each customer has bought. … Continue reading

Posted in Layout | 5 Comments