How to Conditionally Format Nonblank Cells by Category in Excel
Question details
The user wants to apply different fill colors to nonblank cells in a specific range based on the text category listed in a reference column.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing categorizations where rows require distinct formatting for non-empty cells across multiple columns.
- Observed behavior
- Automatically assigning fill colors to nonblank cells based on multiple predefined categories and corresponding formatting rules.
Ensure your data range is well-defined and that the category column contains exact text matches for the rules you plan to set up.
Use the AND Formula in Conditional Formatting
Create a custom formula rule for each category to check if a cell is not blank and matches the specified category in the reference column.
By combining the AND function with conditional formatting, you can evaluate two conditions simultaneously: whether the cell contains data, and whether the corresponding row belongs to a specific category.
Using mixed references (like $A6) is critical here. It locks the category column reference while allowing the formatting to apply dynamically across all other columns in your range.
Highlight the range of cells where you want the formatting to appear (for example, C6:AT25).
Go to the Home tab on the ribbon, click on 'Conditional Formatting', and select 'New Rule'.
Click on 'Use a formula to determine which cells to format'.
In the formula box, type =AND(C6<>"",$A6="CATEGORY1"). Replace 'C6' with the top-left cell of your selection, '$A6' with the first cell of your category column, and 'CATEGORY1' with your actual category name.
Click the 'Format' button, choose your desired fill color from the Fill tab, and click OK. Repeat this entire process for CATEGORY2, CATEGORY3, etc., using different colors for each.

Easily Apply Advanced Conditional Formatting with WPS Spreadsheet
WPS Spreadsheet offers full support for advanced conditional formatting formulas, allowing you to highlight nonblank cells by category effortlessly. It boasts a familiar interface and is highly compatible with your existing data files.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data categories.
- 2. Highlight the data range: Select the cells you want to conditionally format, ensuring you start from the top-left cell of your data.
- 3. Access Conditional Formatting: Navigate to the Home tab, click on the 'Conditional Formatting' icon, and select 'New Rule'.
- 4. Apply the formula: Choose 'Use a formula to determine which cells to format', input your =AND() formula, and define your fill color.
- 5. Manage multiple rules: Repeat for other categories, and use 'Manage Rules' to prioritize or edit your existing formatting conditions.

Frequently Asked Questions
Why is my conditional formatting formula highlighting the wrong cells?
This usually happens due to incorrect relative or absolute cell references. Ensure you are locking the category column with a dollar sign (like $A6) so the formula always checks column A, while leaving the evaluated cell reference relative (like C6).
Can I apply conditional formatting across multiple sheets?
Conditional formatting rules are specific to the worksheet they are created in. You cannot directly reference another sheet in the rule without using the INDIRECT function or setting up named ranges.
How do I edit or delete a conditional formatting rule?
Select any cell in your formatted range, go to Home > Conditional Formatting > Manage Rules. From this dialog box, you can edit the existing formula, change the formatting style, or delete rules you no longer need.
Does conditional formatting slow down my spreadsheet?
Applying highly complex volatile formula rules over hundreds of thousands of cells can impact performance. It is recommended to apply rules strictly to your actual data range rather than entire columns (like A:A).




