Reverse Data Bar Gradient Fill

Sanjay Gandhi
Sanjay Gandhi used Ask the Experts™
on
Hello,

Excel, Conditional Formatting

I have five values - 1 to 5.
Data bar (Gradient Fill) shows small bar on 1, and full cell bar on 5. I want to show the reverse.
On 1, it should show full bar, and on 5 it should show small bar.
Hope I am clear.

Warm regards,

San.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Christopher RourkeProject & QA Manager @ Experts Exchange

Commented:
Hello San,

In order to invert the data bar display you need to manually set the value minimum and maximum within the conditional formatting rules.

  1. Select at least one cell that has the data bar conditional formatting
  2. From the Conditional Formatting menu select, "Mange Rules..."
  3. Select the Data Bar rule then click on Edit Rule...
  4. Modify the Minimum and Maximum types from Automatic to Number
  5. Set your maximum to 1 & set your minimum to 6 (6 so a result of 5 still shows a small bar)
  6. Click on OK to submit the edited rule
  7. Once returned to the Conditional Formatting Rules Manager, click on Apply to confirm the changes.

Regards,
-Rourke
Sanjay GandhiFounder, Kenhal

Author

Commented:
Hello Rourke,

This does not work. I tried that before posting the question also.

Regards,

San.
Rob HensonFinance Analyst

Commented:
Sanjay, I have managed this as follows:

Set the Data Bar conditional formatting as before but choose White as the colour of the bar and at the bottom right of the Format settings change the bar direction to "Right to left". (3 options - Context, Left to Right, Right to Left).

Then format the cell with a fill colour that you want the bar to be.

As the value of the cell increases, the amount of white space on the right hand end of the cell increases, thus shrinking the coloured area as the value increases.

See attached.
Reverse-Bar-CF.xlsx
Project & QA Manager @ Experts Exchange
Commented:
Hello San,

My apologies, I forgot one critical aspect. From the same interface in which you edited the values for the minimum and maximum click on the button, "Negative Value and Axis..."

For the last option set, Axis settings, select the "None (show negative value bars in the same direction as positive)" radio then click OK.

Regards,
-Rourke

Edit: I have attached an example
Example.xlsx
Sanjay GandhiFounder, Kenhal

Author

Commented:
Hello Rourke,

This works wonders, and just to the point.

Thanks.

------------

Hello Rob,

With the given changes, I could not get through.

Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial