How to Apply Spreadsheet Conditional Formatting Based on Another Column Value
Question details
The user needs to apply conditional formatting to cells in one specific column only when the corresponding cell in a different reference column contains a specific value, such as 1.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Highlighting specific records or cells in a dataset dynamically based on criteria met in an adjacent or different column.
- Observed behavior
- The user requires a custom conditional formatting formula that correctly locks the reference column while allowing the row number to adjust dynamically.
Identify the target column you want to apply the formatting to and note the letter of the reference column containing your criteria. Ensure you know the exact starting row of your data range (excluding headers) to ensure your custom formula aligns correctly.
Use a Custom Formula for Conditional Formatting
Apply a custom formula using a mixed cell reference to lock the condition column while allowing the row to remain relative.
To format a column based on the values in another column, you must use a formula-based conditional formatting rule. By using a dollar sign ($) before the column letter, you lock the reference column, forcing the formatting rule to evaluate that specific column for every cell in your selected range.
Highlight the data range in your target column (e.g., the 'Expediente' column) where you want the formatting to appear. Make sure to start your selection from the first row of your actual data, typically row 2 if row 1 contains headers.
Navigate to the 'Home' tab on the top ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown menu.
In the New Formatting Rule dialog box, select the option labeled 'Use a formula to determine which cells to format'.
In the formula bar, type your condition. For example, if your reference column is J and your data starts at row 2, type =$J2=1. The dollar sign ($) is crucial as it locks column J.
Click the 'Format' button to choose your preferred fill color, font style, or borders. Once configured, click 'OK' to save and apply the rule.
Use WPS Spreadsheet for Advanced Conditional Formatting
WPS Spreadsheet provides a highly intuitive interface for handling complex conditional formatting and data visualization tasks. It fully supports custom formulas and mixed references, allowing you to highlight data intelligently based on specific criteria.
- 1. Select Data Range: Open your workbook in WPS Spreadsheet and highlight the target column where you want the format applied.
- 2. Create a New Rule: Navigate to Home > Conditional Formatting > New Rule, and select the formula option.
- 3. Apply Formula and Format: Enter your reference formula (e.g., =$J2=1), choose your highlight color, and click OK.
Frequently Asked Questions
Why is my conditional formatting applied to the wrong rows?
This usually happens if the row number in your custom formula does not match the first row of your selected range. If your highlighted selection starts at row 2, your formula must also reference row 2 (e.g., =$J2=1). If the formula references row 1, the formatting will be offset by one row.
How can I format an entire row based on one column's value?
Instead of selecting just one target column, highlight your entire dataset (excluding headers). Then, apply the same formula with the locked column reference (e.g., =$J2=1). Because the column is locked but the rows are relative, the format will apply to all cells across the entire row when the condition is met.
Can I apply conditional formatting based on text instead of numbers?
Yes. If you want to check for a specific text string instead of a number, simply enclose the text in double quotes within your formula. For example, to format cells when column J says 'Completed', use the formula =$J2="Completed".
How do I edit or delete an existing conditional formatting rule?
Go to the 'Home' tab, click 'Conditional Formatting', and select 'Manage Rules'. Choose 'This Worksheet' from the 'Show formatting rules for' dropdown. Select your rule and click 'Edit Rule' to modify the formula, or 'Delete Rule' to remove it entirely.
