by contextures.com
When you type an equal sign, then click on a pivot table value cell, a GetPivotData formula is automatically created. We’ll see how to make those formula more flexible, so you can quickly change the results.
Continue reading “Refer to Value Cells in GetPivotData Formula”
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”
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”
Last week, I asked for your input in my survey on pivot table number formats. There were 3 questions in the survey:
In Excel, you can use array formulas to find MIN IF and MAX IF. For example:
Continue reading “Find MIN IF and MAX IF From Excel Pivot Table”
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.
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.
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”
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:
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.
