Getting rid of the numeric/text error in Excel

I was asked by one of our internal customers if there was a way to avoid the error you get in Excel when you put a number into a cell or column that is formatted for text.

While there’s no way to shut off the numeric/text error in Excel completely, there is a way to clear the error on a number of cells at once rather than one at a time. Here’s how it works…

So here’s a column in Excel that’s been formatted as text:

excel-numeric-error-20170411-1

But when you enter numeric values in there, you see a small green triangle in the upper left corner, accompanied by the yellow warning triangle. The error tells you that the number is being treated as text and won’t be right justified like most numbers would be:

excel-numeric-error-20170411-2

Ideally, you’d be able to turn off that error completely, but I couldn’t find any way to do that. However, you can highlight all the cells that have that error, and then click the dropdown arrow next to the warning symbol. When you select the Ignore Error option, it will clear that error from all the cells at once:

excel-numeric-error-20170411-3

1 Comment

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