Formula Errors

How to Compare Stock Quantities in Two Columns Using Excel Formulas

Elise Williams Oct 14, 2026 871 views

Question details

The user wants to compare their website's stock quantities with a supplier's stock quantities in a spreadsheet and automatically highlight significant differences.

How to Compare Stock Quantities in Two Columns Using Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Inventory management requiring the comparison of stock quantities between two different datasets to identify discrepancies.
Observed behavior: Needs a method to automatically flag items where the quantity difference exceeds 50% between supplier stock and local website stock.

Before you start

Ensure your inventory data is organized with the supplier stock and your website stock in separate, easily identifiable columns, and identify any blank or zero values in the supplier column to prevent calculation errors.

Solution 1Recommended

Use Conditional Formatting to Highlight Stock Differences

Apply a conditional formatting rule using the ABS function to automatically highlight rows with more than a 50% stock variance.

This method calculates the absolute percentage difference between two cells. Using the ABS function ensures that both positive and negative differences (e.g., whether the supplier has more stock or you have more stock) are treated equally and flagged when the threshold is crossed.

Select the data range

Highlight the specific cells or entire rows you want to apply the formatting to (for example, select range A2:F100).

Open Conditional Formatting

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

Enter the custom formula

Choose the option 'Use a formula to determine which cells to format' and input the formula: =ABS(E2-F2)/E2>50% (assuming Column E contains supplier stock and Column F contains your stock).

Set the highlight format

Click the 'Format' button, navigate to the Fill tab, choose a prominent color like red or yellow to highlight the variance, and click 'OK' twice to apply the rule.

Use Conditional Formatting to Highlight Stock Differences

Handling Division by Zero Errors: If the supplier stock (Column E) contains zeroes or blanks, the formula will return a #DIV/0! error. You can modify the formula to handle these cases, for example by wrapping it with an IFERROR or an IF statement checking for zero values.

Advanced Spreadsheets for Inventory

Manage Inventory Easily with WPS Spreadsheet

WPS Spreadsheet offers powerful conditional formatting, advanced formulas, and complete compatibility with Excel, making it perfect for comparing stock lists and managing inventory smoothly.

1. Open your inventory file: Launch WPS Spreadsheet and open your inventory Excel file containing the supplier and website stock.
2. Access Conditional Formatting: Select your data range, navigate to the Home tab, and click on Conditional Formatting > New Rule.
3. Apply the comparison formula: Select 'Use a formula to determine which cells to format', input =ABS(E2-F2)/E2>50%, and set your preferred highlighting color.

100% compatible with Microsoft Excel (.xlsx) formats and conditional formatting rules.Free, lightweight, and fast to load large inventory datasets.Intuitive ribbon interface that requires no learning curve to apply complex formulas.

microsoft office alternative - wps office

Frequently Asked Questions

How can I compare two columns and extract the exact numeric difference instead of highlighting?

You can insert a new column next to your stock data and use a simple subtraction formula like =E2-F2 to calculate the exact numeric difference between the supplier stock and your stock.

Can I change the 50% threshold in the conditional formatting formula?

Yes, simply modify the 50% in the formula =ABS(E2-F2)/E2>50% to any other percentage you need, such as 25% or 0.25, to adjust the sensitivity of the highlight rule.

Why is my conditional formatting applying to the wrong rows?

This usually happens if the relative formula references are not aligned with your selected range. Ensure the row numbers in your formula (like E2 and F2) exactly match the very first row of your highlighted data range before applying.

Will this formula work if my columns are not right next to each other?

Absolutely. You just need to ensure you update the column letters in the formula (e.g., swapping E and F for B and Z) to match the actual locations of your supplier stock and your website stock.

Excel & Data Problems