Then, I can change the Value field name in cell C1 to Total Qty, and it will show that amount. The formula shows the correct total for Total Price. If you want to use a cell reference for a value field, use the & operator to concatenate an empty string at the start or end of the reference. The formula returns a #REF! error, even though cell C1 contains the correctly typed field name, "Total Price". In the screen shot below, I entered Total Price in cell C1, and referred to that cell. Now we can see the total price for Carrot in cell A1.Ĭell references work well with the row fields, but if you try to use the same technique with the Value field name – Total Price – the formula returns a #REF! error. Then, you can change cell B1 to a different product, and the formula result changes automatically. Please match the definition to the Type of Reference, and match the example of cell reference to the Type of Reference. The column reference Question: Examples of Absolute, Relative and Mixed Cell References. =GETPIVOTDATA("Total Price",$A$3,"Product",B1) When copied to another cell, both the column and row in the cell reference are adjusted to reflect the new location. To make the formula flexible, we can change the text strings to cell references, and you can see sample formulas here.įor example, type Arrowroot in cell B1, then change the formula so it refers to that cell for the product name. =GETPIVOTDATA("Total Price",$A$3,"Product","Arrowroot") Change Text to Cell References
#Relative cell reference excel definition quizlet how to#
We’ll see how to make those formula more flexible, so you can quickly change the results.įor example, in the screen shot below, I typed an equal sign in cell A1, then clicked on the Arrowroot total price in the pivot table.Įxcel created a GetPivotData formula, and shows "Total Price" as the first argument: When you type an equal sign, then click on a pivot table value cell, a GetPivotData formula is automatically created.