Forum Discussion
Conditional Formatting (Data bars) altered as percentage has exceeded 100%
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!
In Excel Online, the conditional formatting options are more limited compared to the desktop version. As far I know, setting a specific maximum value for data bars in conditional formatting is not directly supported in Excel Online.
However, you can still achieve a similar effect by adjusting your data or using other types of conditional formatting. For example, you could:
- Normalize your data: Scale down all values so that the maximum value is 100% (1 as a decimal). This way, exceeding the target will not result in values greater than 100%.
- Use other conditional formatting options: Instead of data bars, you can use other formatting options such as color scales or icon sets to visually represent your data.
- Create a custom solution using formulas: You can use formulas to calculate the conditional formatting criteria based on your specific needs.
While Excel Online may not offer the exact same features as the desktop version, you can still explore alternative approaches to achieve your desired outcome. Additionally, Microsoft frequently updates Excel Online with new features and improvements, so it's worth keeping an eye on future updates for any enhancements to conditional formatting capabilities.
