Formatting Issues

How to Keep WPS Office Excel Data Bars at 100 Percent When Values Exceed 100%

Chanuka GeekiyanageChanuka Geekiyanage Nov 13, 2026 868 views

Question details

The user needs to keep data bars at 100 percent full when the selected range also contains values above 100 percent.

Keep Excel Data Bars at 100 Percent When Values Exceed 100% in WPS Office
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the percentage cells

Click and drag to select the cells that contain the data bar percentages, or press Ctrl + A to select the full table if needed.

2
Open Manage Rules

Go to the Home tab, click Conditional Formatting, and choose Manage Rules from the dropdown menu.

3
Edit the data bar rule

In the Conditional Formatting Rules Manager, select the data bar rule and click Edit Rule.

4
Set Maximum to Number 1

Under Edit the Rule Description, set Maximum to Number and enter 1 in the value box, because WPS Spreadsheets stores 100% as 1.

5
Set Minimum and apply

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.

Set a fixed maximum of 1 for the data bar rule
Result: 100% values will fill the entire data bar, and values above 100% will also display a full bar instead of stretching the scale.
WPS Spreadsheets formatting

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. 1. Open the workbook and select cells: Open your file in WPS Spreadsheets and select the percentage cells that contain the data bars.
  2. 2. Access Manage Rules: Go to Home > Conditional Formatting > Manage Rules.
  3. 3. Edit the data bar rule: Select the data bar rule and click Edit Rule.
  4. 4. Set maximum to Number 1: Set Maximum to Number and enter 1, and set Minimum to 0 if needed.
  5. 5. Apply and verify: Click OK, then Apply, and confirm that 100% now fills the bar.
Set a fixed maximum of 1 to keep 100% data bars fullPreserves conditional formatting in Microsoft Excel-compatible .xlsx filesFree, lightweight, and easy to useFamiliar ribbon interface makes Manage Rules easy to findAvailable on multiple platforms including Windows, macOS, Android, and iOS
QA img-9

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.