How to Keep WPS Office Excel Data Bars at 100 Percent When Values Exceed 100%
Question details
The user needs to keep data bars at 100 percent full when the selected range also contains values above 100 percent.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has percentage cells with data bar conditional formatting and wants 100 percent to remain the full bar even if other results exceed 100 percent.
- Observed behavior
- With default minimum and maximum scaling, a 100 percent value no longer fills the bar when a higher value such as 257 percent becomes the maximum.
Open your workbook in WPS Spreadsheets and select the percentage cells that contain the data bar rule. Check that your percentages are stored as decimal values, where 100% equals 1, before editing the rule.
Set a fixed maximum of 1 for the data bar rule
Use this method when your percentage range contains values above 100% and you want 100% to remain the full bar.
Excel data bars normally scale between the minimum and maximum values in the selected range. If one result reaches 257%, a 100% result is no longer treated as the maximum, so it does not fill the bar. Setting a fixed maximum of 1 makes 100% the full bar.
Click and drag to select the cells that contain the data bar percentages, or press Ctrl + A to select the full table if needed.
Go to the Home tab, click Conditional Formatting, and choose Manage Rules from the dropdown menu.
In the Conditional Formatting Rules Manager, select the data bar rule and click Edit Rule.
Under Edit the Rule Description, set Maximum to Number and enter 1 in the value box, because WPS Spreadsheets stores 100% as 1.
Set Minimum to Number 0 if appropriate, click OK, then click Apply or OK to confirm. Values above 100% will display a full bar, while lower percentages will be proportionally scaled.

Keep Data Bars at 100 Percent in WPS Spreadsheets
Use WPS Office Spreadsheets to edit the data bar rule and set a fixed maximum of 1, so 100% always fills the bar even when other values exceed 100%.
- 1. Open the workbook and select cells: Open your file in WPS Spreadsheets and select the percentage cells that contain the data bars.
- 2. Access Manage Rules: Go to Home > Conditional Formatting > Manage Rules.
- 3. Edit the data bar rule: Select the data bar rule and click Edit Rule.
- 4. Set maximum to Number 1: Set Maximum to Number and enter 1, and set Minimum to 0 if needed.
- 5. Apply and verify: Click OK, then Apply, and confirm that 100% now fills the bar.

Frequently Asked Questions
Why does my 100% data bar not fill when another cell is 257%?
Data bars scale between the minimum and maximum values in the selected range by default. When the maximum is 257%, a 100% value is no longer the maximum, so it shows a partial bar. Set the rule maximum to Number 1 to make 100% the full bar.
How do I open Manage Rules in WPS Office Spreadsheets?
Select the formatted cells, then go to Home > Conditional Formatting > Manage Rules.
Does WPS Spreadsheets store 100% as 1?
Yes, 100% is stored as the decimal value 1. Set the data bar maximum to Number 1 to make 100% the full bar.
Should I set the minimum to 0?
If your values start from 0%, set Minimum to Number 0 so lower percentages scale proportionally from zero. If your range has a different meaningful minimum, you can adjust it to match your data.
Will these data bar rules work if I open the file in Microsoft Excel?
Yes, WPS Spreadsheets saves conditional formatting rules in the .xlsx format, and Microsoft Excel can read them.




