Cap an Excel Insurance Calculation and Highlight the Limit
Question details
The user wants to limit a calculated insurance amount and highlight when the limit is reached.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- An insurance calculation should be capped at $1,500, with a visual alert when it reaches the limit.
- Observed behavior
- The displayed amount should not exceed the chosen limit, and conditional formatting should signal when the limit is reached.
Identify the cell containing the uncapped insurance calculation and confirm the maximum amount. The examples below use B2 for the calculated amount, C2 for the result, and $1,500 as the limit.
Cap the amount and highlight the maximum in Excel
Use this approach when the result should remain at $1,500 once the calculated amount reaches or exceeds that value.
In C2, enter =MIN(B2,1500). Replace B2 with the cell containing your calculated amount, or replace it with the calculation itself.
If you have multiple amounts, drag the fill handle from C2 down through the rows you want to calculate.
Select the result cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Equal To.
Enter 1500, choose a highlight format, and click OK. A capped result of $1,500 will now be highlighted.

Return zero at the limit and highlight the affected result
Use this approach when the result should be zero as soon as the uncapped amount reaches or exceeds $1,500.
Cap an insurance amount in WPS Spreadsheets
Use a MIN formula and conditional formatting in WPS Spreadsheets to keep an insurance calculation at or below its limit and highlight the capped result.
- 1. Open the spreadsheet: Open WPS Spreadsheets and use Open to select your Excel workbook.
- 2. Add the cap formula: In C2, enter =MIN(B2,1500), using B2 as the uncapped amount. Adjust the cell reference and limit for your sheet.
- 3. Select the result cells: Copy the formula down if needed, then select the cells containing the capped results.
- 4. Highlight the limit: Choose Home > Conditional Formatting > Highlight Cells Rules > Equal To, enter 1500, select a format, and confirm the rule.
- 5. Save the workbook: Use File > Save As and select an Excel workbook format such as XLSX.

Frequently Asked Questions
How do I change the $1,500 limit?
Replace 1500 in the formula with your new limit, then edit the conditional formatting rule under Home > Conditional Formatting > Manage Rules to match.
Can I use a cell for the limit instead of typing 1500?
Yes. If D1 contains the limit, use =MIN(B2,$D$1). For a formula-based formatting rule on C2, use =C2>=$D$1.
Why doesn't my zero result show that the limit was reached?
A zero result no longer contains the threshold value. Create a formula-based conditional formatting rule that checks the uncapped amount instead, such as =$B2>=1500 when formatting C2.
Does the MIN formula stop the original calculation?
No. It limits the displayed result, but the original calculation still runs. For example, =MIN(B2,1500) displays 1500 even if B2 contains a larger amount.




