How to copy down a rank formula for converting pivot table values to ranks

[ChrisMacGC]

Hello

I have a pivot table of prices with 46 columns (product names) and 275 rows (dates). I want to generate the rank of each price in each row. E.g. for row 1 (date 1) I'd like to visualise the rank of the first price (product) compared to the other products in that row, then the rank of the second price, the third, etc. Then I'd like to also copy the formula down the 275 rows to do the same for all 275 dates. Is there a way to do this? I've used =RANK(number, ref, [order]) to do this for the first cell (price) but when I try to copy the formula down, the 'ref' changes to the new row values, but the 'number' stays fixed as the number in the first row. Is there a way around this?

Thank you!

Chris

[#2]

Hi @ChrisMacGC 

if the cell reference does not change during copy, I assume that GETPIVOTDATA function is used in your file. And this function uses per default static references.

If this is the case in your file, you could turn off GETPIVOTDATA. Just put your active cell somewhere within your pivot table and select from the menu "PivotTable Analyze | Options | Generate GetPivotData"

(this option should be unchecked then)

Alternatively, when creating your RANK formula, do not click into the pivot table (which causes the GETPIVOTDATA function to be used), but instead just enter the cell reference of the first value. This one should update when you copy it down to all the other rows.