How to Fill an Excel Column Based on a Stock Level in WPS Office
Question details
Use an IF formula in WPS Office Spreadsheets to return N in the reorder column when the stock-level column contains Plenty, and return Y for other stock levels.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Managing inventory stock levels in a spreadsheet and automating a reorder column based on stock status.
- Observed behavior
- The reorder column should display N for Plenty and Y for any other stock level, with optional handling for blank stock-level cells.
Open your inventory workbook in WPS Office Spreadsheets and confirm the stock-level values are in column B and the reorder formula will start in D5. Adjust the cell references if your sheet uses a different layout.
Use =IF($B5="Plenty","N","Y") in the Reorder Column
Use this solution when every non-Plenty stock level should show Y and Plenty should show N.
The formula checks the stock-level value in column B for the current row. The absolute column reference $B keeps the formula tied to column B, while the row number changes as you fill down.
Click cell D5, or the first cell where the reorder result should appear in your worksheet.
Type =IF($B5="Plenty","N","Y") and press Enter.
Use the fill handle at the lower-right corner of D5 and drag it down through the remaining inventory rows, or double-click the fill handle to auto-fill.
Leave the Reorder Column Blank When Stock Level Is Empty
Use this solution when blank stock-level cells should remain blank instead of showing Y.
Create a Stock-Based Reorder Column in WPS Office Spreadsheets
Use WPS Office Spreadsheets to enter the same Excel IF formula and fill a reorder column automatically based on stock levels.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the Excel workbook that contains your stock-level data.
- 2. Locate the reorder column: Click the first cell where the reorder result should appear, such as D5.
- 3. Enter the IF formula: Type =IF($B5="Plenty","N","Y") and press Enter.
- 4. Fill down for all items: Drag the fill handle from the formula cell down through your inventory list to apply the formula to each row.
Frequently Asked Questions
Why does the formula use $B5 instead of B5?
The $B absolute column reference keeps the formula looking at column B even if it is copied or filled across columns, while the row number 5 adjusts as the formula is filled down.
How do I leave the reorder cell blank for empty stock levels?
Use =IF($B5="","",IF($B5="Plenty","N","Y")). The outer IF catches empty cells and returns a blank before checking for Plenty.
Can I use this formula if the stock level is in a different column?
Yes. Change $B5 to the actual stock-level column and row, for example $C5 if the stock level is in column C, then fill the formula down.
What should I do if the cell shows the formula text instead of the result?
The cell may be formatted as text. Select the cell, set the number format to General, click in the formula bar, and press Enter to recalculate.
Does this IF formula work the same in WPS Office and Microsoft Excel?
Yes. WPS Office Spreadsheets supports standard Excel IF formula syntax and .xlsx workbooks, so the formula behaves the same in both applications.
