Conditional Formatting and Relative References?

[bradleykus]

Hello,
I am no Excel master to any degree so I may be overlooking a really clear and obvious answer, but I'm trying to solve the following problem:

Basically all I'm doing is tracking a set of videos on a particular YouTube channel and what their projected viewcounts will be in the future. I then want the last video at the bottom of the list to have a data bar that tracks its progress to see how quickly/slowly it is catching up to the first video on the list (just chose the first one for demonstration).

As you can see in this first photo, it works fine; the conditional formatting shows the data bar in cell C14 properly filled up about a fifth of the way, showing that that video's 1,011,901 views are about a fifth of the top video's 4,741,156 views.

First photo: https://www.dropbox.com/s/9dp6vjkx296ien6/Excel 1.png?dl=0

Here is the rule set for that conditional formatting:

Second photo: https://www.dropbox.com/s/4mry0hz67qhn9dc/Excel 2.png?dl=0


Now, however, I want it to copy over for the rest of the spreadsheet, so that it is a running visual tracker as the viewcounts go up. I used the format painter to copy it over for the whole row, as you can see in the first photo above. What I then get is this:

Third Photo: https://www.dropbox.com/s/200ys8bect7jas2/Excel 3.png?dl=0

The data bar exists across the row in every cell (so that part's successful), but it is still referencing the first cell I chose (Cell C2) instead of the top cell in that column. That 3,668,361 in Cell AFI14 should only be about half filled when compared to the top cell in that column, 7,526,668. Here is what the conditional formatting is set to when I click on that cell.

Fourth Photo: https://www.dropbox.com/s/pvnpo8hqtipg68z/Excel 4.png?dl=0


Again, I know what it is doing - it's stacking up each cell against the original cell (C2), instead of using the new column's top cell each time. I thought using relative references would work but I guess conditional formatting doesn't allow the use of relative references - is there a workaround to this? Thank you!

 

[ClaireS]

Try changing your reference cell from $C$2 (always cell C2) to C$2 (always row 2, first column is C, others will adjust).

When creating a CF formula in Excel, you must design the formula for the first cell in the range, but use fixed/partially fixed references in such a way that when you copy the formula (or simply apply it to other cells in the range), it still works in all the other cells.

To fix a reference absolutely: $C$2
To fix the column, but allow the row to change: $C2
To fix the row, but allow the column to change: C$2

Good luck!

 

[bradleykus]

Unfortunately it just says the following:
"You cannot use relative references in Conditional Formatting criteria for color scales, data bars, and icon sets."

Which I'm actually surprised by, but yeah unfortunately removing any one of the dollar signs makes it a relative reference instead of absolute.

 

[ClaireS]

Hmm, I suppose I have never tried to produce a custom CF using data bars etc. How annoying.

You could of course do each one individually...

Would Sparklines in the row below be of any use?

Or take your bottom value, divide it by the top value and do Data bars for those? You could show the bar only and set the Maximum value to 1.

Just a couple of suggestions...