Chart & Visualization Issues

How to Set a Different Maximum for Each Excel Data Bar

Muhammad TalhaMuhammad Talha Oct 20, 2026 871 views

Question details

The user needs to assign different maximum values to individual rows or categories when using conditional formatting data bars.

How to Set a Different Maximum for Each Excel Data Bar
Product
Excel
Device & OS
not provided
Scenario
Creating a customized visual data report where different rows require independent scale limits for their data bars.
Observed behavior
Excel's native conditional formatting applies a single maximum value across the entire selected range of a data bar rule, making it impossible to dynamically set row-specific maximums within one rule.
Before you start

Identify the distinct categories or rows in your dataset and determine the specific maximum numerical value you want to assign to each.

Solution 1Recommended

Create Separate Conditional Formatting Rules

Since a single rule cannot handle multiple maximums, you must create an individual conditional formatting rule for each category or row.

By applying a unique rule to each specific range, you can manually define the exact maximum value for that subset of data without affecting the rest of the worksheet.

1
Select the target data

Highlight the specific cell or range of cells for your first category (for example, cells in column E).

2
Navigate to Data Bars

Go to the Home tab on the ribbon, click on Conditional Formatting, hover over Data Bars, and select More Rules from the dropdown menu.

3
Set the maximum value

In the New Formatting Rule dialog box, change the Maximum type to Number or Formula. Enter the specific maximum value for that category or select the cell containing the maximum limit.

4
Apply and repeat

Click OK to apply the rule. Repeat this entire process for each remaining category, ensuring you set the corresponding maximum value for each new selection.

Create Separate Conditional Formatting Rules
Use Absolute References: If you are referencing a cell for your maximum value, use absolute references (e.g., $C$2) so the reference remains fixed if you copy the format to other cells.
Advanced Data Visualization

Use WPS Spreadsheet for Better Rule Management

WPS Spreadsheet offers a highly intuitive Conditional Formatting Rules Manager, making it much easier to duplicate, edit, and manage multiple data bar rules across large datasets.

  1. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing workbook containing the data.
  2. 2. Access Conditional Formatting: Select your first category range, click Conditional Formatting on the Home tab, and choose Data Bars > More Rules.
  3. 3. Set custom limits: Input your specific maximum limit for the category and click OK.
  4. 4. Duplicate rules efficiently: Go to Conditional Formatting > Manage Rules. Select your newly created rule, use the duplicate function, and simply adjust the 'Applies to' range and maximum value for subsequent categories.
Fully compatible with Microsoft Excel conditional formatting formats.Streamlined Rules Manager for fast rule duplication and editing.Lightweight, free alternative that easily handles large data visualization tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Can I set a dynamic maximum for each row within a single data bar rule?

No, native conditional formatting rules assign a single minimum and maximum value for the entire selected range. To use varying maximums, you must create separate rules for each row or category.

Why do my data bars look incorrect when I copy the formatting down?

If your rule uses a relative cell reference (like C2) for the maximum value, it will shift when copied. You must use an absolute reference (like $C$2) to lock the maximum value to a specific cell.

How do I show only the data bar without the numbers in the cell?

Select the cells containing the data bars, navigate to Conditional Formatting > Manage Rules, click Edit Rule, and check the box labeled 'Show Bar Only' before clicking OK.