Ignore multiple "number stored as text" errors at once

Question

I have a spreadsheet where a certain column has a lot of data which generates "number stored as text" errors. I want the numbers to be stored as text in this column for certain formatting purposes. Is there a way for me to quickly dismiss all these errors at once, or tell Excel to ignore this error for the entire row as a rule, without entirely disabling the error for the whole sheet or program?

Answer 1

1. Select the top-left first cell in the sheet that has the green triangle indicating the error
2. Scroll to the last bottom-right cell that has the error. Hold Shift and select that last cell
3. Go back to that first cell, there will be a clickable icon to do something about the error
4. Click on it and then click "Ignore Error"

This will ignore all the errors in the selection you have. But you must start with the first error to get the pop-up to ignore them.

Answer 2

Uncheck this option:

File > Options > Formulas > Error Checking Rules > Numbers formatted as text or preceded by an apostrophe

Answer 3

Here's a more precise version of the answer by @JosephSerido:

1. Select the range of cells where you want to ignore the error.
2. Use Tab, Shift+Tab, Enter, Shift+Enter to navigate within the selected range to a cell that has this error. Screenshot below where the first cell doesn't contain this error, so step 2 is required.
3. The clickable icon to ignore the error appears on the top left or top right of the selected range, depending on the scrollbar position. Don't confuse it with another icon on the bottom right.

Alternative method:

1. Select one cell with the error.
2. Press Ctrl+A once or twice, until the range you want is selected.
3. Click the icon, like in step 3 in the method above.

Answer 4

Excel for Mac:

Excel Menu > Preferences > Formulas and Lists > Error Checking >

Clear the 'Turn on background error checking' check box OR just 'Numbers Formatted as text' OR whatever may be the hindrance.

Answer 5

In Excel, I believe you can fix the problem by selecting the entire column and selecting a format change.

Answer 6

If you want it to apply to the whole work book (that is what I need for special work books) I use File>Options>Error Checking> uncheck the "Enable Background Error Checking" box. Save.

Excel 2010