Chart & Visualization Issues

How to Set Fixed Minimum and Maximum Values for Excel Data Bars

Phi Hung VoPhi Hung Vo Nov 10, 2026 868 views

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.

How to Set Fixed Minimum and Maximum Values for Excel Data Bars
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.
Before you start

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.

Solution 1Recommended

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.

1
Open Conditional Formatting Rules Manager

Select the cells containing your data bars. Navigate to the 'Home' tab, click on 'Conditional Formatting' in the Styles group, and select 'Manage Rules'.

2
Edit the Data Bar Rule

In the Rules Manager dialog, select the Data Bar rule you want to modify, and then click the 'Edit Rule' button.

3
Set Fixed Values

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).

4
Apply the Changes

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.

Customize Data Bar Rules in Conditional Formatting
Understanding Data Bar Scaling: Values that exceed your defined fixed maximum will display as a full-length bar, while values below the minimum will display no bar at all.
Efficient Data Visualization

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. 1. Highlight Data: Select the range of cells where you want to apply or edit data bars.
  2. 2. Access Conditional Formatting: Go to the Home tab and click on Conditional Formatting > Manage Rules.
  3. 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.
Fully compatible with Microsoft Excel (.xlsx) formats and conditional formatting rules.Intuitive UI for quick data visualization and chart management.Lightweight and free to use with all essential spreadsheet functions.
QA img-9

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.