by contextures.com
When you create a pivot table, Excel automatically makes the value fields either a SUM or a COUNT, depending on the type of data in that field. You’re not stuck with that function though — if you don’t like the summary function that Excel selected, you can change it.
Unfortunately, you can’t add hyperlinks to a pivot table, even if there are hyperlinks in the pivot table’s source data. In the screen shot below, the Hyperlink command is not available, because a pivot table cell is selected.
Continue reading “Create Fake Hyperlinks in Excel Pivot Table”
One of the advantages of summarizing your data in a pivot table is that you can filter the results, to focus on specific items.
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.
