Conditional formatting data bars

[megera716]

I don't know why I can't figure this out but I've googled every incarnation I can think of and I can't find what I'm looking for.

I have this set of numbers in columns J:M. I want a data bar in column N that is a percentage of spend vs budget (Column L/Column K), with a label of the actual dollars remaining from Column M:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Team[/TD]
[TD]Budget[/TD]
[TD]Spend[/TD]
[TD]Remaining[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tech[/TD]
[TD]1416[/TD]
[TD]709[/TD]
[TD]707[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Reporting[/TD]
[TD]758[/TD]
[TD]280[/TD]
[TD]478[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project Mgmt[/TD]
[TD]608[/TD]
[TD]510[/TD]
[TD]98[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is this impossible or am I just having a brain fart?

 

[Eric W]

Data Bars can't handle relative references, making what you ask pretty tricky. The best I came up with is by following this procedure (assuming the data shown is in J1:M4):

1) Put this formula in N2:

=IFERROR(L2/K2*100,0)

and drag it down as needed.

2) Select column N, click Conditional Formatting > Data Bars > and pick one.

3) Click Conditional Formatting > Manage Rules > select the rule you just added > Edit Rule. Check the Show Bar Only box, and set the Minimum and Maximum Type boxes to Percent. This gets you the percent of spending / budget as a percent data bar. (If you leave the Show Bar Only box unchecked, you can format the number in the box as a percentage. But if you want the Remaining amount instead, check the box and proceed to step 4.)

4) If you want the Remaining amount to be shown in the same box as the data bar (and I'm not sure why, the amount is already clearly listed in M), select cells M2:M4, right click and select Copy. Now select N2:N4, right click, move the mouse to Paste Special > then to the arrow on the right of that line, then select the bottom right icon { Linked Picture (I) } and click on that. This creates a picture of the M2:M4 cells which overlays the bars in N2:N4. So it will copy everything, the value as well as the cell formatting from M2:M4. So M2:M4 must NOT have any fill color, or it will cover the data bars. And no, this is not ideal. To edit the cells under the picture, you'll need to move it. If you add rows to the table, you'll need to delete and replace the picture. You can create the picture a thousand rows long if you want to start, that may prevent you from having to change it later.


Hope this helps.