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

Generate Data Bars with VBA macro.

http://www.mediafire.com/file/gn992e3s9rhg4eo/12_12_16.xlsm

Answer 3

I don't understand. With Data Bars there is no cell reference in the formatting rule. 

• create a formula to a cell
• apply Conditional Formatting > Data Bars to that cell
• copy the cell down.

The animated screenshot shows these steps. Does that not work for you?

Answer 4

Hello Teylyn,

thank you for your reply.

My problem is that when I copy and paste, the formatting keeps referring to the first cell I used to create the formatting rule. Unfortunately the yellow columns have different numbers, and I need the data bars to update the reference at every row.

Replacing $I$5 with $I5 in the formatting rule doesn't work, because, for some reasons beyond my understanding, relative references do not work with data bars formatting.

So, I want to believe there is another way to do this, cause I am not looking forward to manually inserting a few thousands formatting rules.

Answer 5

Hello,

create the cell reference to the cell, then insert the data bar conditional format. Copy the cell down and both the formula and the data bar format will be applied to the cells below. 

If you want to use data bars in the white columns between the yellow data, copy the whole data bar column, then paste to the next white column.