Reverse Data Bar Gradient Fill

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.
Sanjay GandhiFounder, KenhalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Christopher RourkeProduct Manager @ Experts ExchangeCommented:
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
0
Sanjay GandhiFounder, KenhalAuthor Commented:
Hello Rourke,

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

Regards,

San.
0
Rob HensonFinance AnalystCommented:
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
0
Christopher RourkeProduct Manager @ Experts ExchangeCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sanjay GandhiFounder, KenhalAuthor Commented:
Hello Rourke,

This works wonders, and just to the point.

Thanks.

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

Hello Rob,

With the given changes, I could not get through.

Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.