Highlight Actual Values Above Forecast Using Conditional Formatting in Excel
Question details
The user needs to dynamically compare actual values against forecast values in Excel and visually highlight actual values that exceed a specified percentage of the forecast.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Reviewing financial, sales, or performance data where actual metrics must be evaluated against projected targets using conditional cell highlighting.
- Observed behavior
- The user requires a formula-driven formatting rule to highlight cells based on mathematical comparisons with corresponding forecast cells.
Ensure your forecast and actual values are aligned in parallel columns or rows, and note the starting cell references for both data sets (e.g., C2 for forecast and D2 for actuals).
Use the Greater Than Highlight Cells Rule
Apply a custom formula within the default 'Greater Than' conditional formatting preset to dynamically compare actuals against forecast percentages.
This method uses built-in presets but replaces the static comparison value with a formula, allowing Excel to calculate the threshold for each row dynamically based on the forecast column.
Highlight the range containing your actual values. Ensure that the first data cell (for example, D2) is the active cell in your selection.
Navigate to the Home tab on the Excel ribbon, click on Conditional Formatting, select Highlight Cells Rules, and then choose Greater Than.
In the dialog box, enter the formula to calculate the forecast percentage threshold. To highlight actuals that are at least 10% higher than the forecast in C2, type =110%*C2.
Choose a formatting style from the drop-down menu (such as Light Red Fill with Dark Red Text) and click OK to apply the rule.
Create a Custom Conditional Formatting Rule
Use the 'New Rule' option for greater flexibility, allowing you to apply custom formatting styles that aren't available in the standard presets.
Highlight Variances with Conditional Formatting in WPS Spreadsheet
WPS Spreadsheet offers powerful, intuitive conditional formatting features that fully support custom formulas. You can seamlessly compare actual performance to forecasts and apply automated color-coding without switching software.
- 1. Highlight Actual Values: Open your dataset in WPS Spreadsheet and select the column containing your actual values.
- 2. Access Conditional Formatting: Go to the Home tab and click on the Conditional Formatting icon in the main toolbar.
- 3. Set the Condition: Select Highlight Cells Rules, then click on Greater Than from the context menu.
- 4. Apply Formula and Format: Enter your forecast formula (e.g., =110%*C2) in the value box, select your desired highlight color, and click OK.
Frequently Asked Questions
Why is my conditional formatting highlighting the wrong cells?
This usually happens when the active cell during your selection doesn't match the row referenced in your formula. Ensure that if you start your selection at row 2 (e.g., D2), your formula specifically refers to row 2 (e.g., C2).
Can I highlight the entire row instead of just the actual value cell?
Yes. Select your entire data table (excluding headers) and create a New Rule using a formula. Use absolute column references in your formula, such as =$D2>($C2*1.1), to ensure the entire row highlights when the condition is met.
How do I highlight actual values that fall below the forecast?
Select your actual values range, navigate to Home > Conditional Formatting > Highlight Cells Rules > Less Than, and enter the cell reference for your forecast, such as =C2, or a percentage like =90%*C2.
