AI Spreadsheet Checks: A Cell Can Look Empty and Still Count

A workshop organiser asks an AI assistant to count the speaker biographies received for a programme. The spreadsheet shows a few blank spaces and a neat total. Before that total becomes “ready to publish”, there is a less visible question: what does the formula recognise as empty?

In Excel, an untouched cell, a formula displaying nothing and a cell containing a space can require different treatment. A completion check needs a definition of acceptable content as well as working syntax. The distinction matters when a small spreadsheet becomes the basis for chasing contributors or releasing a publication.

Build a small example before checking the full sheet

Consider a fictional programme with five speakers. Column B holds their biographies. In a separate test worksheet, set up these five cases:

• B2: leave the cell empty, with no formula or other content.
• B3: enter the formula ="", which produces empty text.
• B4: enter the numerical value 0.
• B5: enter one ordinary space character.
• B6: enter the fictional biography “An independent potter teaching introductory ceramics workshops.”

The organiser’s requirement is an actual biography that an editor has checked and approved. For this example, assume only B6 has that approval. The numerical zero is not a biography; in a different field, zero might be a perfectly valid answer. These are editorial rules for this fictional programme, not properties Excel can infer from a cell address.

Two counts can include the same cell

Microsoft’s COUNTA reference explains that a referenced range can contribute numbers, text, errors and empty text returned by formulas. Cells with no content do not contribute. For the five specified cells, =COUNTA(B2:B6) has an expected result of 4: B3, B4, B5 and B6.

The COUNTBLANK reference explicitly includes formulas returning empty text and excludes zero values. Here, =COUNTBLANK(B2:B6) has an expected result of 2: B2 and B3. The space in B5 is text content, rather than an empty cell.

Adding those results gives six across a range containing five cells. That is consistent with the documented rules: B3 contributes to both counts. Treating the two functions as mutually exclusive categories would introduce the mistake. Neither total establishes how many biographies have editorial approval.

Inspect the cell, then interpret the result

Microsoft describes ISBLANK as testing whether its reference identifies an empty cell. In this example, =ISBLANK(B2) should return TRUE, while =ISBLANK(B3) should return FALSE. B3 contains a formula even though its displayed result has no visible characters.

Place diagnostic formulas in separate checking cells. Keep the original entries intact while investigating. Replacing B3 with an actual empty cell merely to make a count agree would remove the formula, so first establish why it was there and whether other work depends on it.

A FALSE result is information about cell contents, not evidence that a biography is complete. Record the technical result and the editorial decision separately. This prevents a useful diagnostic from quietly becoming an approval rule.

Treat spaces as a separate problem

A suggested fix might involve stripping whitespace. Microsoft’s TRIM documentation says the function removes ordinary ASCII spaces at the edges and reduces repeated spaces between words. However, it does not remove the non-breaking space character, decimal 160, on its own.

That limit matters if a proposed check claims to handle all apparently empty text. In the test case, =TRIM(B5) should return empty text because B5 contains one ordinary space. Do not assume the same treatment for every invisible character copied from elsewhere.

Ask which characters the proposed rule handles and keep an untouched source column when exploring changes. A cleaning rule should have an explicit purpose. Removing characters to identify a missing biography is different from proving that the remaining words are accurate, appropriate or approved.

Give the assistant examples and expected outcomes

A useful request states the application, the field’s meaning and the cases the proposed solution must distinguish. For example:

“I am using Excel to track five speaker biographies. B2 is empty; B3 contains =""; B4 contains numerical zero; B5 contains one ordinary space; B6 contains a biography. Only B6 has editorial approval. Explain the expected COUNTA, COUNTBLANK and ISBLANK results. Propose separate checks for missing or unsuitable content and a separate approval field. Preserve the source entries and state how your approach handles non-breaking spaces and errors.”

This is a suggested briefing method, not a claim that a particular assistant has passed the task. Compare its explanation with the documented behaviour and try the cases in the Excel version you actually use. A confident explanation still needs to match the specified entries.

The MaryChuks guide to defining what a spreadsheet average counts develops the related question of which records belong in a calculation. Here, the extra issue is representation: two cells that look similarly empty may contain different things.

Keep publication approval visible

For this programme, use a separate status such as “awaiting biography”, “needs review” or “approved”, with an agreed meaning for each. A non-empty biography can still contain a working note or inaccurate wording. Keeping approval explicit makes it possible to explain why a contributor needs attention.

The programme summary could therefore say: “Five speakers expected; one biography approved; four still need attention.” List the four records beneath it with their current reason. This lets a colleague act without interpreting a generic “filled” total or reverse-engineering the formula.

Before relying on the full-sheet summary, change one test case at a time. Replace B3’s formula with a verified biography: a content diagnostic should reflect the new text, while editorial approval should follow the agreed review process. Remove the space from B5 and check that the missing-content result changes as intended. Neither operation should approve another speaker’s record.

Retain the example and its expected outcomes when revising formulas or moving the data into another application. Check the destination’s behaviour rather than assuming identical rules. A useful completion report tells the organiser which biographies are approved, which need attention and what evidence supports those labels.

Sources

• Microsoft Support: COUNTA function.
• Microsoft Support: COUNTBLANK function.
• Microsoft Support: IS functions, including ISBLANK.
• Microsoft Support: TRIM function.

Documentation checked on 26 October 2026; these support pages do not display publication dates. All programme records and expected outputs above are illustrative.