Formula Errors

How to Use Conditional Formatting for a Percentage of Another Cell

Olivia Miller Oct 9, 2026 868 views

Question details

The user wants to automatically apply conditional formatting to a cell or range when its value reaches or exceeds a specific percentage of a fixed reference cell, without having to manually calculate and input the threshold.

How to Apply Conditional Formatting Based on a Percentage of Another Cell

Product: Spreadsheet
Device & OS: not provided
Scenario: Setting up dynamic formatting rules where the threshold is dependent on the value of a separate, fixed cell (e.g., highlighting a milestone when 10% of a target is reached).
Observed behavior: A custom formula rule is required to correctly evaluate relative row values against an absolute fixed cell reference while calculating the percentage dynamically.

Before you start

Identify the exact location of your fixed reference cell (e.g., Z2) and ensure that your target data range does not contain errors that might disrupt formula calculations.

Solution 1Recommended

Use a Custom Formula for Conditional Formatting

This solution applies a custom formula rule to dynamically calculate the percentage of a fixed reference cell and format the first occurrence that meets the threshold.

By utilizing absolute references (e.g., $Z$2), the conditional formatting rule stays locked on your target cell while evaluating multiple rows in your dataset. The COUNTIF function can also be nested to highlight only the first instance that reaches the threshold.

Select the target data range

Highlight the cells you want to evaluate and format. For example, click and drag to select cells from G4 downwards.

Open Conditional Formatting rules

Navigate to the Home tab on the top ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown menu.

Select the formula option

In the New Formatting Rule dialog box, choose the option labeled 'Use a formula to determine which cells to format'.

Enter the custom evaluation formula

Input the formula: =AND(G4>=10%*$Z$2,COUNTIF(G$4:G4,">="&10%*$Z$2)=1). Adjust G4 to match the first cell in your range, 10% to your desired percentage, and $Z$2 to your fixed reference cell.

Set the format and apply

Click the 'Format' button, choose your preferred highlighting style (such as a background fill color or bold text), click 'OK', and then 'OK' again to apply the rule.

Use a Custom Formula for Conditional Formatting

Formula Breakdown: The 'G4>=10%*$Z$2' portion checks if the value meets the threshold. The 'COUNTIF' portion ensures that only the very first cell reaching this milestone is highlighted. If you want to highlight ALL cells that meet the threshold, simplify the formula to: =G4>=10%*$Z$2

Advanced Spreadsheet Formatting

Easily Apply Conditional Formatting with WPS Spreadsheet

WPS Office provides an intuitive and powerful Spreadsheet application that fully supports complex custom formulas and conditional formatting. You can easily highlight critical data trends based on dynamic cell references without hassle.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the data and reference cells.
2. Select your data range: Highlight the specific column or rows that you want to apply the formatting rule to.
3. Navigate to Conditional Formatting: Go to the Home tab, click the 'Conditional Formatting' icon, and select 'New Rule'.
4. Input the custom formula: Select 'Use a formula to determine which cells to format' and paste your percentage formula.
5. Customize and save: Choose a distinctive cell fill color or font style, click 'OK' to save, and immediately view your dynamically highlighted data.

Fully compatible with Microsoft Excel conditional formatting rulesIntuitive user interface for building complex custom formulasLightweight and runs smoothly on all major operating systemsCompletely free to download and use for your daily spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why is the wrong cell being highlighted when I apply the rule?

This usually happens due to incorrect relative and absolute referencing. Ensure your fixed reference cell uses dollar signs (e.g., $Z$2) to lock it, while the evaluated cell (e.g., G4) remains relative without dollar signs so it can adapt to each row.

How do I highlight the entire row instead of just one cell?

To highlight the entire row, you need to lock the column letter of the cell being evaluated in your formula. For example, change G4 to $G4. Make sure your conditional formatting 'Applies to' range covers the entire data table.

Can I use a variable cell for the percentage instead of typing '10%'?

Yes. If your percentage is stored in cell Z3, you can replace '10%' in the formula with an absolute reference to that cell. Your formula would look like this: =G4>=$Z$3*$Z$2.

What does the COUNTIF function do in this specific formula?

The COUNTIF function is used here to identify only the first occurrence that meets the percentage threshold. By checking that the count is exactly 1, it prevents subsequent values that also exceed the threshold from being highlighted.

Excel & Data Problems