Forum Discussion
Conditional Formatting (Data bars) altered as percentage has exceeded 100%
Hello,
I have an excel set up where the first column shows the target, the 3 column shows what has been achieved so far, and based on that the fourth column will show the percentage complete. I have added conditional formatting to the fourth column in the form of a gradient filled data bar. However as we have exceeded our target in some areas and gone over 100%, that has affected the conditional formatting for all cells in that column - in that a 100% would no longer show a full green line, a full green line would only show if the percentage is 257% (the highest percentage).
Any ideas on how to fix this?
Thanks
You can fix this issue by setting the maximum value for the data bars in your conditional formatting rule.
Here's how you can do it:
- Select the cells in the fourth column that contain the data bars.
- On the Home tab, click on Conditional Formatting and choose Manage Rules.
- In the Conditional Formatting Rules Manager, select the rule for the data bars and click on Edit Rule.
- In the Edit Formatting Rule dialog box, under "Bar Appearance", click on the drop-down list next to "Maximum" and choose "Number".
- Enter 1 as the maximum value (since 100% is equivalent to 1 as a decimal).
- Click OK to close the Edit Formatting Rule dialog box and then click OK again to close the Conditional Formatting Rules Manager.
Now your data bars should show a full green line for 100% and any percentage above 100% will also show a full green line.
You can fix this issue by setting the maximum value for the data bars in your conditional formatting rule.
Here's how you can do it:
- Select the cells in the fourth column that contain the data bars.
- On the Home tab, click on Conditional Formatting and choose Manage Rules.
- In the Conditional Formatting Rules Manager, select the rule for the data bars and click on Edit Rule.
- In the Edit Formatting Rule dialog box, under "Bar Appearance", click on the drop-down list next to "Maximum" and choose "Number".
- Enter 1 as the maximum value (since 100% is equivalent to 1 as a decimal).
- Click OK to close the Edit Formatting Rule dialog box and then click OK again to close the Conditional Formatting Rules Manager.
Now your data bars should show a full green line for 100% and any percentage above 100% will also show a full green line.
Thanks so much NikolinoDE
I was mainly getting stuck because I was using the online version of excel and it wasn't giving me any other options for the rule:
All working great now, thanks again!
