Formatting Issues

How to Apply Conditional Formatting to Compare Stock Levels by Row

Huda Qurayshi Sep 27, 2026 871 views

Question details

The user wants to apply a single conditional formatting rule to highlight current stock cells if their values fall below the minimum stock values in adjacent cells across multiple rows.

How to Compare Stock Levels by Row Using Conditional Formatting

Product: Spreadsheet
Device & OS: not provided
Scenario: Managing inventory or stock levels where a visual alert is needed when current stock drops below the minimum required stock.
Observed behavior: Highlighting cells based on a comparison between two columns across multiple rows without manually creating a new rule for each row.

Before you start

Ensure your stock data is organized in columns (e.g., Current Stock in Column A and Minimum Stock in Column B) and identify the exact starting row of your selected data range.

Solution 1Recommended

Use a Formula with Relative Row References

Applying a single conditional formatting rule using relative row references automatically adjusts the formula for every row in your selected dataset.

By utilizing a formula in the conditional formatting menu, you can compare values across different columns. Using a relative row reference (leaving the row number without a dollar sign) tells the spreadsheet to adjust the rule dynamically as it moves down the column.

Select the target range

Highlight the entire range of cells you want to format, for example, A2:A500 (where A contains your current stock values).

Open Conditional Formatting

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

Enter the comparison formula

Choose 'Use a formula to determine which cells to format'. In the formula box, type =$A2<$B2 (assuming B contains the minimum stock value).

Set the formatting style

Click the 'Format' button, select your desired fill color (like red) or font style to highlight low stock, and click 'OK' to apply.

Use a Formula with Relative Row References

Reference Locking: Using the dollar sign ($) before the column letter locks the column, while leaving the row number without a dollar sign makes it relative, allowing the rule to adapt to each row perfectly.

Solution 2

Copy Formatting with the Format Painter

If you have successfully created a relative-reference conditional formatting rule in one cell, you can quickly duplicate it to the rest of the column.

Efficient Spreadsheet Management

Highlight Low Stock Levels Instantly with WPS Spreadsheet

WPS Spreadsheet offers powerful conditional formatting tools fully compatible with Microsoft Excel formulas. Easily highlight low stock, track inventory, and manage large datasets without paying for expensive software.

1. Open your inventory document: Launch WPS Spreadsheet and open the file containing your stock and minimum stock data.
2. Highlight the stock column: Select the cells you want to monitor, starting from the first row of data.
3. Create a new rule: Go to Home > Conditional Formatting > New Rule in the top ribbon.
4. Input the comparison formula: Choose 'Use a formula to determine which cells to format' and input your comparison formula (e.g., =$A2<$B2).
5. Apply highlight colors: Click the Format button, select a vibrant highlight color, and click OK to apply the rule to your inventory.

100% compatible with Microsoft Excel conditional formatting and formulasFree, lightweight, and fast for managing large inventory datasetsIntuitive interface for creating custom formatting rules effortlessly

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting rule highlighting the wrong rows?

This usually happens if the row number in your formula does not match the first row of your selected range. Ensure that if your selection starts at A2, your formula uses row 2 (e.g., =$A2<$B2). If you accidentally type =$A1<$B1 while selecting A2:A500, the formatting will be offset by one row.

Can I highlight the entire row instead of just the stock cell?

Yes. To highlight the entire row, select the full data range (e.g., A2:F500) instead of just the stock column, and apply the exact same formula =$A2<$B2. The absolute column references ($A and $B) ensure the whole row evaluates the stock condition correctly.

Does this formula work if my columns are not adjacent?

Yes, the columns do not need to be next to each other. As long as you correctly reference the respective column letters in your formula (for example, =$C2<$H2), the relative formula will correctly compare those specific columns for each row.

Document & File Issues