Function Problems

How to Use a Conditional Formula to Display Serial Numbers in Microsoft Lists

Tauseeq Magsi Oct 8, 2026 868 views

Question details

The user needs to set up a conditional formula in Microsoft Lists that displays a serial number only when a specific status column is marked as YES.

How to Use Conditional Formulas to Display Serial Numbers in Microsoft Lists

Product: Microsoft Lists
Device & OS: not provided
Scenario: Configuring a calculated column to dynamically output a serial number based on a YES/NO status selection in another list column.
Observed behavior: Standard Excel cell references (like C2) do not work in Lists, and standard Yes/No columns fail to evaluate properly in this specific IF formula configuration.

Before you start

Ensure you have Owner or Designer permissions for the Microsoft List so you can add and modify new columns.

Solution 1Recommended

Use a Choice Column and Calculated Column

Use a Choice column instead of a standard Yes/No column to ensure the conditional IF formula processes the text condition correctly.

Microsoft Lists formulas differ from Excel formulas because they reference column names rather than specific cell grids. Additionally, standard Yes/No columns output boolean values (True/False) which can cause errors when formulas try to match the exact text "YES".

Create a Choice column

Click 'Add column' in your list and select 'Choice'. Name the column (e.g., 'Serial_new?') and set the choices exactly to 'YES' and 'NO'.

Add a Calculated column

Click 'Add column' again and select 'Calculated (calculation based on other columns)' to create the output column for your serial numbers.

Enter the IF formula

In the formula box, enter =IF([Serial_new?]="YES",[Serial],""). Replace '[Serial_new?]' and '[Serial]' with your exact column names. This checks the Choice column and outputs the Serial value if YES is selected.

Set the returned data type

Under the formula input area, choose the appropriate data type returned from this formula (e.g., 'Single line of text' or 'Number'), and click 'Save'.

Use a Choice Column and Calculated Column

Returning a Blank Value: The two quotation marks ("") at the end of the formula ensure that if the selection is 'NO', the calculated column will remain neatly blank rather than displaying an error.

Free Microsoft Office alternative

A Free Alternative to Microsoft Office for Data Management

While Microsoft Lists is great for web-based lists, if you prefer managing datasets with flexible standard formulas and direct cell referencing (like C2 or B2), WPS Spreadsheet offers a powerful, lightweight, and completely free alternative to Microsoft Excel.

1. Download WPS Office: Install WPS Office for free on your preferred operating system.
2. Open WPS Spreadsheet: Launch WPS Spreadsheet and import your existing CSV or Excel datasets easily.
3. Use Standard Formulas: Apply familiar IF formulas like =IF(C2="YES", B2, "") directly without worrying about database column types.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Uses standard, familiar spreadsheet formulas and grid cell references.Lightweight application that runs fast on Windows, Mac, Linux, and mobile.Completely free to use with an interface that requires zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I use Excel cell references like C2 in Microsoft Lists formulas?

Microsoft Lists functions like a database rather than a flexible grid. Therefore, it requires formulas to reference entire column names (e.g., [ColumnName]) instead of individual cell coordinates.

Why does my standard Yes/No column fail in the IF function?

A standard Yes/No column in Microsoft Lists stores values as booleans (True or False) under the hood. If your calculated formula is explicitly looking for the text string "YES", the boolean value will fail to trigger a match. Using a Choice column circumvents this issue.

How do I format the output of a calculated column in Lists?

When creating or editing a calculated column, scroll below the formula box to find the setting 'The data type returned from this formula is'. You can select options like Single line of text, Number, Currency, or Date and Time to format your output.

Excel & Data Problems