如何在Excel中按自定义数值规则设置单元格颜色格式

Hey there! Let's walk through exactly how to set up that conditional formatting you need—this is totally doable with Excel's built-in tools, and we'll make sure the rules work as expected by ordering them correctly.

Step 1: Select Your Target Cell Range #

• First, highlight the entire set of cells where you want this color formatting to apply. Just click and drag across the cells to select them.

Step 2: Open the Conditional Formatting Rule Builder #

• Head to the Home tab in Excel's ribbon. Look for the Conditional Formatting button (it’s in the Styles group). Click it, then choose New Rule from the dropdown menu.

Step 3: Create the "Value > 75" Rule (Blue Fill) #

• In the New Formatting Rule window, select Format only cells that contain under the "Select a Rule Type" section.
• Under "Format only cells with", set the first dropdown to Cell Value, the second to greater than, and type 75 in the input box.
• Click the Format button, switch to the Fill tab, pick your preferred blue color, then hit OK twice to save this rule.

Step 4: Create the "Value > 50" Rule (Red Fill) #

• Go back to the Conditional Formatting menu and select New Rule again (or use Manage Rules to add it directly to the list—either works).
• Repeat the same setup: choose Format only cells that contain, set Cell Value > 50.
• Click Format, pick your red fill color, then hit OK.
• Critical note: If you use Manage Rules, make sure the "Value >75" rule is positioned above this one. Excel applies rules from top to bottom, so stricter conditions need priority (otherwise, cells with values over 75 would get red instead of blue).

Step 5: Set the Default Black Format (For Values ≤50) #

• Excel’s default font color is already black, but if you want to explicitly enforce this (to override any existing formatting), add one more rule:
  • Create a new rule for Cell Value ≤ 50.
  • Click Format, go to the Font tab, set the color to black, then save the rule.
  • Make sure this rule is at the bottom of the list in Manage Rules so it doesn’t interfere with the blue/red formatting.

Quick Pro Tip #

Always order your conditional rules from the most specific condition to the least specific. That way, cells that meet multiple criteria (like a cell with 85, which is >75 and >50) will use the formatting from the topmost rule—exactly what you want here.

内容的提问来源于stack exchange，提问作者Dean Johns