WPS Spreadsheet: Use Conditional Formatting for Stock and Open Quantities
Question details
The user needs to color spreadsheet cells or rows based on whether open quantity is greater than stock quantity.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A quantity table uses column B for open quantity and column C for stock, and the user wants visual indicators for inventory differences.
- Observed behavior
- The goal is to display a red fill when open quantity exceeds stock and a green fill when stock exceeds open quantity.
Confirm which columns contain open quantity and stock, and identify the first data row in the target range. In the example, open quantity is in column B, stock is in column C, and the first data row is row 2.
Create formula-based rules for stock and open quantities
Use this method when you want WPS Spreadsheet to automatically color cells or rows according to the relationship between open quantity and stock.
Conditional-formatting formulas are evaluated relative to the first row of the selected range. Keep the column references fixed with $ while leaving the row number relative so the rule adjusts for every row.
Open the worksheet in WPS Spreadsheet and select the cells or full rows you want to format, such as A2:C100.
Go to the Home tab, select Conditional Formatting, and choose New Rule or a formula-based rule. Enter =$B2>$C2, choose a red fill, and click OK or Apply.
With the target range selected, open Home > Conditional Formatting > New Rule again. Enter =$C2>$B2, choose a green fill, and apply the rule.
Check several rows to confirm that rows with open quantity greater than stock are red and rows with stock greater than open quantity are green. Replace B, C, or 2 in the formulas if your columns or starting row differ.
Highlight Inventory Differences Automatically in WPS Spreadsheet
WPS Spreadsheet lets you compare open and stock quantities with formula-based conditional formatting, apply custom colors, and keep inventory differences visible as values change.
- 1. Open WPS Spreadsheet: Launch WPS Office and open an existing workbook or create a new spreadsheet.
- 2. Select the inventory range: Select the table or range containing the open quantity and stock columns.
- 3. Add the comparison rule: Choose Home > Conditional Formatting > New Rule, enter a formula such as =$B2>$C2, and select a red fill.
- 4. Add the second color rule: Create another formula rule with =$C2>$B2 and select a green fill to indicate that stock is greater than open quantity.
Frequently Asked Questions
Which formula highlights rows when open quantity is higher than stock?
Use =$B2>$C2 in a formula-based conditional-formatting rule when open quantity is in column B, stock is in column C, and the first data row is row 2.
How can I show green when stock is greater than open quantity?
Add a second rule from Home > Conditional Formatting > New Rule and enter =$C2>$B2. Set the formatting to a green fill and apply it to the same range.
Can I apply the rule to an entire row instead of only the quantity cells?
Yes. Select the complete row range, such as A2:C100, before creating the rule. The formula will evaluate each row while the formatting is applied across the selected columns.
What should I change if my stock and open quantity columns are different?
Replace B and C with the correct column letters and replace 2 with the first row of your selected data. For example, if open quantity is D and stock is E beginning on row 5, use =$D5>$E5.
