Formula Errors

Fix Microsoft Lists Calculated Yes/No Column Returning Blank

Tauseeq Magsi Oct 10, 2026 869 views

Question details

A calculated column in Microsoft Lists returns a completely blank field when its output data type is set to Yes/No, even though the same formula evaluates correctly when the output type is set to Text.

How to Fix Microsoft Lists Calculated Yes/No Column Returning Blank

Product: Microsoft Lists
Device & OS: not provided
Scenario: Attempting to display a Boolean (Yes/No) result or a checkmark in a Microsoft Lists calculated column using an IF/AND formula.
Observed behavior: The formula evaluates perfectly when the output type is Single line of text, but displays nothing (remains blank) when switched to the Yes/No output type due to a product bug or limitation.

Before you start

Before proceeding, temporarily change your calculated column's output type to 'Single line of text' to verify that your formula contains no syntax errors and successfully returns the expected text.

Solution 1Recommended

Use Single Line of Text as a Formula Workaround

Since the Yes/No output type failing is a known product limitation in Microsoft Lists, switching your output to Text and adjusting the formula is the most reliable workaround.

Microsoft Lists frequently struggles to map boolean logic (TRUE/FALSE) outputs to the native Yes/No data type, resulting in blank cells. By using text outputs, you bypass this bug entirely.

Open Column Settings

Navigate to your Microsoft List, click the dropdown arrow next to your calculated column's header, and select 'Column settings' followed by 'Edit'.

Change the Output Data Type

Scroll down to the 'The data type returned from this formula is' section and select 'Single line of text'.

Update Your Formula

Modify your formula to output literal text strings instead of booleans. For example, change it to: =IF(AND([Active],[TextField]="XXXX"), "Yes", "No"). Click 'Save' to apply the changes.

Use Single Line of Text as a Formula Workaround

Formatting Tip: If you want visual indicators, you can use conditional formatting on this new text column to color-code the 'Yes' and 'No' results, mimicking a native Boolean field.

Solution 2

Leverage Power Apps for Advanced Logic

If your workflow strictly requires native Boolean controls (like toggle switches or checkboxes), you should customize the list form using Microsoft Power Apps.

Free Microsoft Office alternative

Manage Data Without Glitches in WPS Office

Tired of unexplained bugs and data type limitations in Microsoft Lists? WPS Office provides a robust, lightweight, and completely free Spreadsheet alternative. Handle complex IF/AND formulas, Boolean logic, and data validation effortlessly without worrying about missing or blank results.

1. Download and Install: Get WPS Office for free from the official website and install it on your device.
2. Import Your Data: Export your Microsoft List to CSV/Excel and open it instantly in WPS Spreadsheet.
3. Apply Reliable Formulas: Use standard IF functions and Boolean logic; WPS Spreadsheet reliably displays TRUE/FALSE or custom text without blanking out cells.

Processes complex logical formulas and Boolean outputs with zero display errors.Fully compatible with Microsoft Excel (.xlsx) formats for seamless data migration.Free, lightweight, and incredibly fast compared to web-based list applications.Familiar user interface requires no learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Microsoft Lists formula return completely blank?

If your formula is structurally correct, returning blank is a known limitation in Microsoft Lists when mapping certain calculated outputs to specific data types, particularly the Yes/No (Boolean) data type.

Can I use TRUE or FALSE in a calculated column formula?

Yes, you can use TRUE or FALSE inside your logical statements. However, trying to output these native Boolean values into a column configured to return a Yes/No type often triggers the bug. It is safer to output 'Yes' or 'No' as text.

Is there a way to display a checkmark instead of text?

Yes. Set your calculated column output to 'Single line of text' and copy-paste a Unicode checkmark symbol (✓) directly into your formula's output strings, such as: =IF([Condition], "✓", "").

Excel & Data Problems