Getting rid of the Excel numeric/text error completely

Last month, I shared a tip about how to clear out the numeric/text error in Excel without having to correct it one cell at a time. I mentioned there was no blanket way to turn it off, but I was… WRONG! A colleague pointed out an option setting in Excel that allows you to ignore that error completely.

Go into your Excel spreadsheet and select File > Options > Formulas:

excel-numerictexterror-20170516-1

Down under Error Checking Rules, you’ll see a checkbox for turning off the error for Numbers formatted as text or preceded by an apostrophe.

Now, when you make that change and save your spreadsheet, all future uses of Excel will maintain that setting. You need to determine if that’s the type of behavior you want to have across the board, and reset it back to “normal” if necessary. However, this is a lifesaver when you’re dealing with things like zip codes or phone numbers. 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s