formulastreak

##### in a cell: what it means and how to fix it

#####

Not a formula error: the column is too narrow, or a date is negative.

A cell full of ##### isn't a broken formula. Excel shows it when the column isn't wide enough for the value, and for negative dates or times, which can't be shown.

Common causes, ranked

  1. 1

    The column is too narrow

    Long numbers and dates don't fit, and Excel won't cut a number off.

    Fix: Widen the column: double-click or drag the right edge of the column header. Or show fewer decimals, or a shorter date format.

  2. 2

    A negative date or time

    Subtracting a later date from an earlier one gives a negative number, which can't be shown as a date.

    Fix: Check the order of the subtraction, or format the result as a number instead of a date.

    Shows the error

    =A2-B2

    Fixed

    =B2-A2

Should you hide it?

This isn't an error you hide: fix the cause above and it goes away.

Excel vs Google Sheets

This page follows Microsoft's guidance for Excel; we haven't found an official Google page about it.

Sources

Practise this in 5 minutes

Fixing ##### is taught in Lesson 26: Dates (Unit 6, Text and dates), on a live sheet with instant feedback. Pro

Open “Dates”

Get the formulastreak newsletter

Bite-sized formula tips you can use at work the next day, plus a heads-up when new free tools and lessons land. It arrives now and then, never in a flood.

No spam, unsubscribe in one click. We store your email, this consent and when you gave it. Details in our privacy policy.

Need a quick reference? The formula cheat sheet is free to read and print.