Highlighting Personal Bests in Excel

I keep track of some exercises in Excel, and I wanted to be able to highlight any cell that represents a personal best to date. I don’t want to just highlight the max value in a range, though. If I set a PB I want to highlight it, and then if I set a new PB later, I want the old one(s) to stay highlighted, and I want the new one to be highlighted as well. You can do this with conditional formatting.

I use this rule for spreadsheets where my attempts are arranged in columns (rule assumes the data starts at row 1):

=INDIRECT("R" & ROW() & "C" & COLUMN(), FALSE) > MAX(INDIRECT("R1" & "C" & COLUMN() & ":R" & ROW()-1 & "C" & COLUMN(), FALSE))

… and I use this rule where attempts are arranged in rows (rule assumes the data starts at column 1):

=INDIRECT("R" & ROW() & "C" & COLUMN(), FALSE) > MAX(INDIRECT("R" & ROW() & "C1:R" & ROW() & "C" & COLUMN()-1, FALSE))

Specify the highlight color you want to use when the formula hits, and you’re good to go. For example, here’s how it might look with column-based data. The orange cells are personal bests to date: