Debra – Page 20 – Excel Pivot Tables

Show Percent Difference in Pivot Table Amounts

With one of the built-in custom calculations in a pivot table, you can quickly show the percent difference between two amounts. In this example, forecast amounts have been entered for each product, for each month. When they become available, the actual amounts are entered in the workbook.

Continue reading “Show Percent Difference in Pivot Table Amounts”

Grouping Shows Items Before and After Date Range

When you group a date field, Excel automatically creates creates groups for all the dates before the start date and after the end date. These items start with a “<” or a “>” symbol, such as “<2012-01-01” and “>2013-12-31”

Usually, you will only see these dates in the filter drop downs.

Continue reading “Grouping Shows Items Before and After Date Range”

Change All Number Formats in Pivot Table

Last week, I asked for your input in my survey on pivot table number formats. There were 3 questions in the survey:

Which number format do you use most often in pivot tables?
How many decimal places do you usually set?
If you use Number format, do you usually include the 1000s separator?

Continue reading “Change All Number Formats in Pivot Table”

Find MIN IF and MAX IF From Excel Pivot Table

In Excel, you can use array formulas to find MIN IF and MAX IF. For example:

What was the lowest total order price for the Carrot Bars product?
What was the highest price for that product?

Continue reading “Find MIN IF and MAX IF From Excel Pivot Table”

Show Zero in Empty Pivot Table Cells

When you set up a pivot table, there might be a few blank cells in the Values area. In the example below, no Banana Bars were sold at stores 3062 or 3659.

Continue reading “Show Zero in Empty Pivot Table Cells”

Duplicate Items Appear in Pivot Table

When you create a pivot table it groups the items from your data, and calculates a total for each group. In the screen shot below, you can see the total sales for each category, in each city in the West region.

Continue reading “Duplicate Items Appear in Pivot Table”

Create Pivot Chart from Data in Excel 2013

With a new feature in Excel 2013, you can create a pivot chart right from the source data – you don’t have to build a pivot table first.

Continue reading “Create Pivot Chart from Data in Excel 2013”

Prevent Subtotals from Appearing

If you add more than one field to the Rows or Columns area in a pivot table, some of the fields automatically get subtotals. In the screen shot below:

the City field has a subtotal, because it is an outer field – there is at least one field after it in the layout. Outer fields get subtotals by default.
the Category field does not have a subtotal, because it is an inner field – there is no field after it in the layout. The innermost field in the Rows or Columns area doesn’t get a subtotal.

Continue reading “Prevent Subtotals from Appearing”

Add All Remaining Fields to Pivot Table

After you create a pivot table, you can add or remove fields by using the check boxes in the field list. Text fields are automatically added to the Row Labels area, and numeric fields go into the Values area.

Continue reading “Add All Remaining Fields to Pivot Table”

Changing Pivot Chart Affects Excel Pivot Table

After you create a pivot table, you can insert a pivot chart, based on that pivot table. However, if you change the layout of the pivot chart, it will also change the pivot table’s layout.

For example, if I remove City from the chart in the screen shot below, the City field will also be remove from the pivot table.

Continue reading “Changing Pivot Chart Affects Excel Pivot Table”