How to Use Conditional Formatting to Highlight In-Stock Parts in Excel
Question details
Highlight specific parts on a repair sheet if their corresponding quantity in an inventory list is greater than zero.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking inventory levels on a repair sheet and needing visual indicators for parts that are currently available in stock.
- Observed behavior
- Cells containing part names on the repair sheet are automatically filled with a highlight color when the matching part in the inventory table has a quantity greater than zero.
Ensure your inventory list and repair sheets are correctly formatted as tables or have defined ranges so your lookup formulas can reference them accurately without errors.
Apply Conditional Formatting using a SUMIFS Formula
Best for straightforward lists where the part name is isolated in its own cell on the repair sheet.
Using the SUMIFS function within a conditional formatting rule allows you to cross-reference the part name in your current cell with an external inventory table and sum its quantity. If the quantity exceeds zero, the formatting is triggered.
Highlight the range of cells containing the part names on your repair sheet (for example, A2:A100).
Navigate to the 'Home' tab on the top ribbon, click 'Conditional Formatting', and select 'New Rule'.
Choose 'Use a formula to determine which cells to format'. In the formula box, enter `=SUMIFS(Inventory[Quantity], Inventory[Part], A2)>0`, adapting the table names and starting cell reference to match your actual data.
Click the 'Format' button, go to the 'Fill' tab, choose a highlight color (such as green), and click 'OK' twice to apply the rule.
Extract and Match Part Names using TEXTAFTER
Use this method if the part name is part of a longer text string, such as 'Part needed: mouse'.
Use WPS Spreadsheet for Advanced Inventory Highlighting
WPS Spreadsheet provides robust conditional formatting and advanced formula support to handle complex inventory tracking and repair sheets seamlessly.
- 1. Open your inventory workbook: Launch WPS Spreadsheet and open the document containing your repair sheet and inventory data.
- 2. Access Conditional Formatting: Highlight your target column and click 'Conditional Formatting' under the 'Home' tab.
- 3. Apply your formula rule: Select 'New Rule', input your custom SUMIFS or text extraction formula, set the highlight color, and click 'OK'.
Frequently Asked Questions
Why isn't my conditional formatting formula working across different sheets?
Ensure you are using absolute references (like $A$1:$B$50) or named ranges (like Inventory[Quantity]) when referring to data on a different worksheet, so the reference doesn't shift as the rule applies down the column.
How can I extract part names if my version doesn't support the TEXTAFTER function?
If TEXTAFTER is not supported, you can use the formula `=TRIM(MID(A2, FIND(":", A2) + 1, LEN(A2)))` to extract the part name immediately following a colon.
Can I highlight the entire row instead of just the cell with the part name?
Yes. Select your entire data range before creating the conditional formatting rule, and lock the column reference for the part name cell in your formula (for example, use $A2 instead of A2).
