How to Set a Different Maximum for Each Excel Data Bar
Question details
The user needs to assign different maximum values to individual rows or categories when using conditional formatting data bars.

- 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.
Identify the distinct categories or rows in your dataset and determine the specific maximum numerical value you want to assign to each.
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.
Highlight the specific cell or range of cells for your first category (for example, cells in column E).
Go to the Home tab on the ribbon, click on Conditional Formatting, hover over Data Bars, and select More Rules from the dropdown menu.
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.
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.

Use a Helper Column with Percentages
Standardize the data bars by calculating each row's percentage of its unique maximum in a separate helper column.
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. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing workbook containing the data.
- 2. Access Conditional Formatting: Select your first category range, click Conditional Formatting on the Home tab, and choose Data Bars > More Rules.
- 3. Set custom limits: Input your specific maximum limit for the category and click OK.
- 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.

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.




