How to Apply Conditional Formatting Based on the Sum of Two Excel Cells
Question details
The user needs to highlight a specific cell or row when the sum of two related cells reaches a designated total requirement.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking combined totals, such as individual and group hours, to visually identify when a specific numerical threshold is met.
- Observed behavior
- The standard formatting rules only test a single cell's value, but the goal state requires evaluating the sum of two separate cells to trigger the highlight effect.
Identify the exact columns you want to sum and determine the top-left cell of your selected data range, as this determines how the formula evaluates the rows.
Use a Custom Formula in Conditional Formatting
Create a new formatting rule using a custom mathematical formula to evaluate the sum of two specific cells dynamically.
By utilizing the 'Use a formula to determine which cells to format' option, you can write a standard Excel addition formula. It is critical to use absolute column references (with the $ sign) and relative row references so that the formatting correctly shifts down row by row.
Select the range of cells you want to format. Note the address of the top-left cell in your selection (for example, F55).
Navigate to the Home tab, click on Conditional Formatting, and select 'New Rule' from the dropdown menu.
In the New Formatting Rule dialog box, choose the option labeled 'Use a formula to determine which cells to format'.
In the formula box, enter your condition. Assuming your active cell is F55 and you want the sum of columns F and G to be 200 or greater, type: =($F55+$G55)>=200. If your selection starts at row 1, use =($F1+$G1)>=200.
Click the Format button, go to the Fill tab to choose a highlight color, and click OK twice to apply the rule.
Easily Highlight Cells with Custom Formulas in WPS Office
WPS Spreadsheet provides powerful and intuitive conditional formatting features. You can easily apply custom formulas to highlight combined totals and track your data efficiently.
- 1. Open the workbook: Launch WPS Spreadsheet and open the document containing your data.
- 2. Select the target cells: Highlight the range of rows or columns where you want the conditional formatting to appear.
- 3. Access Conditional Formatting: Go to the Home tab, click on 'Conditional Formatting', and select 'New Rule'.
- 4. Input your custom formula: Choose the formula option and type your summation logic, such as =($F2+$G2)>=200.
- 5. Customize and apply: Click 'Format' to pick a background color, then click 'OK' to instantly see your data highlighted based on the combined cell sums.
Frequently Asked Questions
Why is my conditional formatting highlighting the wrong rows?
This usually occurs when the active cell during rule creation does not match the row referenced in your formula. Ensure the row number in your formula (e.g., $F55) exactly matches the top-left cell of the range you selected before opening the Conditional Formatting menu.
Can I highlight an entire row based on the sum of two specific cells?
Yes. To highlight the entire row, select the entire data range (e.g., A2:Z100) instead of just the two columns. Make sure your formula uses mixed references like =($F2+$G2)>=200 so the columns F and G remain locked while the rows adjust.
Will the highlighting update automatically if I change the cell values?
Yes, conditional formatting is fully dynamic. If you update the numbers in the referenced cells and the new sum meets your criteria, the formatting will automatically apply. If it falls below the criteria, the formatting will immediately disappear.
