Copy Conditional Formatting (Data Bars)

Question

Hi all,

I have a list for which I use the conditional formatting (data bars) for 2 columns (Let's say Column A & B). I want this for each row. When I try to drag down the conditional formatting, the data bars keep referencing the same single cell. I've tried removing the absolute referencing from the condition criteria, however, Excel 2011 keeps automatically adding it back.

Is there any way around this? So far my only option has been to go line by line, which is incredibly tedious.

Thanks.

Answer 1

My question was related to having relative reference points for Data Bars. By further researching the issue, it seems like this is currently not supported for data bars, color scales and icon sets. Those 3 only support an absolute reference.

For example, A1 = 10, B1 = 5. If I do  a default data bar, for A1:B1 the B1 cell will be filled half way.

Now if I have A2 = 5, B2 = 2.5, and I fill down the formatting of the A1:B1 cells, the data bars in the second row reference the largest value (10 in this example), instead of only referencing A2:B2. From what I understand there would be a somewhat tedious workaround by creating an additional column. Otherwise, you have to go row by row, which I am doing currently.

Answer 2

I'm not sure what you mean by:

"When I try to drag down the conditional formatting, the data bars keep referencing the same single cell."

Are you using the Fill Handle to "drag down"? If so, it copies the content of the starting cell along with its formatting. Once you drag the Fill Handle leave the cells selected then click the Fill Options button & select Fill Formatting Only.

If that doesn't answer the question please indicate exactly what steps you're taking along with specific examples to clarify what is happening contrary to what you expect. Complete details are important.

Regards,

Bob J.