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.
Change Selected Pivot Fields to SUM
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.
Create Fake Hyperlinks in Excel Pivot Table
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”
Remove Pivot Table Filters with VBA
One of the advantages of summarizing your data in a pivot table is that you can filter the results, to focus on specific items.
Refer to Value Cells in GetPivotData Formula
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”
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?
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.
