How to Set Fixed Minimum and Maximum Values for Excel Data Bars
Question details
The user wants to set a fixed scale (minimum and maximum values) for data bars created via conditional formatting, rather than having them automatically scale based on the highest and lowest values in the range.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Visualizing data against a defined, absolute target (e.g., a scale of 0 to 100) using conditional formatting data bars.
- Observed behavior
- By default, data bars scale dynamically relative to the lowest and highest values present in the selected conditional formatting range, which may distort how close individual values are to a fixed absolute target.
Ensure you have already applied a Data Bar conditional formatting rule to your desired cell range before attempting to edit its minimum and maximum scale limits.
Customize Data Bar Rules in Conditional Formatting
Use the Manage Rules feature to edit the minimum and maximum types to fixed numbers, allowing precise control over how the bars scale in relation to your custom targets.
Setting absolute minimum and maximum values prevents the bars from adjusting whenever new highest or lowest values are entered, keeping your data visualization consistent.
Select the cells containing your data bars. Navigate to the 'Home' tab, click on 'Conditional Formatting' in the Styles group, and select 'Manage Rules'.
In the Rules Manager dialog, select the Data Bar rule you want to modify, and then click the 'Edit Rule' button.
Under the 'Edit the Rule Description' section, change both the Minimum and Maximum 'Type' dropdowns to 'Number'. Enter your desired fixed values in the 'Value' boxes (for example, type 0 for Minimum and 100 for Maximum).
Click 'OK' to close the Edit Formatting Rule dialog, and then click 'Apply' and 'OK' in the Rules Manager to save and apply your changes.

Set Data Bar Values Easily with WPS Spreadsheet
WPS Spreadsheet provides a seamless and intuitive interface for applying and customizing conditional formatting, including fixed-value data bars. It is perfectly suited for managing complex data visualization tasks and is fully compatible with Excel.
- 1. Highlight Data: Select the range of cells where you want to apply or edit data bars.
- 2. Access Conditional Formatting: Go to the Home tab and click on Conditional Formatting > Manage Rules.
- 3. Set Number Limits: Edit the Data Bar rule, set the Type to 'Number' for both Minimum and Maximum, input your target limits, and click OK.

Frequently Asked Questions
Can I use percentages instead of fixed numbers for data bar limits?
Yes. In the Edit Rule dialog, you can change the Minimum or Maximum Type to 'Percent' or 'Percentile' to scale the data bars based on relative percentages rather than absolute numerical targets.
Why are my data bars making the text in the cell hard to read?
Data bars are designed to display in the background of the cell text. If readability is an issue, you can check the 'Show Bar Only' option in the Edit Rule dialog to hide the text entirely, or change the data bar fill to a lighter or gradient color.
What happens if a cell value in my range is negative?
By default, conditional formatting handles negative values by drawing the data bar in the opposite direction originating from a center axis. You can customize the negative value colors and axis position by clicking the 'Negative Value and Axis' button in the Edit Rule dialog.




