Excel - data bars reference

Question

Excel Conditional Formatting. I am losing my mind.

I spent hours looking online for an answer, but nothing useful for my case came up. 

So, I need to track the progress of every single one of the values in this sheet by adding data bars (as you see in one cell), and then this is supposed to happen for another 19 sheets that look like this one. Very simple formula, I add a number and it refers to the cell in the same row, next column.

I understand I cannot use relative references with data bars to copy&paste the conditional formatting down the column.

It doesn't automatically update the reference cell. 

So, am I really supposed to add every single one manually??

Answer 1

I think your expectations about how data bars work are not correct. Don't set the minimum and maximum like that. It does not make sense to do that. That's not how this works, so Excel does not allow this. 

Data bars visualise the relative size of values in a range of cells. Data bars are not applied to a single cell. If you do apply a data bar conditional format to a single cell, that cell will always have a complete fill. 

Look at my example above. When the data bar is set to the first cell, the whole cell is blue. Only when the conditional format is applied to more cells, you see that the data bars differ in length, showing the relative value of the whole set of cells in a blue bar.

So, again, here are the steps:

• Create the formula in the first row,
• then apply data bars with the default settings.
• Then copy down.

You can see the steps in my animated screenshot above.  The length of the data bars in each cell will be relative to the number compared to all other values in that the same format applies to.

If you don't want to use that, you can enter a minimum and a maximum number for the lower and upper bounds of your data bars, instead of having Excel calculate that dynamically. You could set the minimum to zero and the maximum to a fixed number. Or set the maximum to a cell that has the desired upper bound. The cell address for these limits are always absolute references. See the difference between the default settings with Excel's calculation on the numbers in the range and the manual settings with minimum and maximum.

Answer 2

Thank you Teylyn. 

Copying down the cells doesn't work for me.

Here's a snapshot of my initial formatting. As you can see the column "I" contains the totals. I need to manually update the column H to track the progress towards the totals in column I. I create the formatting rule so that my data bars go from a minimum of 0 to a maximum that equals the number in the next cell in column I. So in this case that 25 is correctly represented.

However when I copy down the cell, the reference to the cell $I$6 doesn't update. 

And if I replace $I$6 with $I6 in the formatting rule, it says that relative references are not allowed in data bars.