52

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?

2
  • There is no way to deactivate error checking only for a certain column. You have to deactive number stored as column in general or to ignore the error indicator.
    – nixda
    Dec 31, 2012 at 6:21
  • there is a way to de-activate check in cell, and this is persisted in XLSX but not in XLS though Mar 5, 2013 at 12:28

6 Answers 6

74
  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.

2
  • 8
    another tip, while in that cell, you can press Ctrl+Shift+Down arrow, then you will select all sells until the last cell with a value :) Oct 13, 2015 at 15:00
  • This only works for 1 specific error at a time. If a cell has a different error than you ignored for the group, you'll have to go back and ignore that 2nd error, too. In my case, I had numbers stored as text as well as dates stored as text. Each error had to be ignored separately, but could be cell-group ignored as this answer states. Nov 3, 2021 at 18:11
16

Uncheck this option:

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

4
  • 3
    I want to ignore multiple without ignoring globally. This is specifically mentioned in the question.
    – Iszi
    Oct 21, 2015 at 16:45
  • Select the entire column or contiguous columns; go back to top of columns while keeping selection and select "igore error" from displayed error box.
    – dbsdba
    Oct 26, 2015 at 10:59
  • Nevertheless, I found this answer useful because none of the other solutions work if I want to ignore errors in entire columns when the errors don't apply to the top row (there's no warning icon/dropdown list if you select a column and the top cell doesn't have the error that applies to the rest of the column). So this answer should remain, since it does pertain to the title, which is most significant in determining which searches will lead to this topic.
    – Adi Inbar
    Oct 5, 2016 at 20:24
  • About once a year I find myself reading this answer again. I wish I could upvote it every time. Thanks again! Feb 13, 2019 at 15:00
11

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.

Image

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.
5
  • 1
    This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review
    – Dave M
    Mar 22, 2018 at 12:14
  • We always appreciate the contributions from our community members, but please do not post comments as an answer. Please see the following Stack Exchange guidance regarding this scenario: Why do I need 50 reputation to comment? What can I do instead? Thanks for your help!
    – Run5k
    Mar 22, 2018 at 12:19
  • 1
    @DaveM This does provide an answer to the question. Moreover, this is the only fully working solution. What is lacking in your opinion?
    – root
    Mar 22, 2018 at 15:16
  • 2
    This is more a comment on the answer by @JosephSerido rather than a completely new answer
    – Dave M
    Mar 22, 2018 at 23:19
  • @root This adds to an existing answer that has 41 up votes (at this time) It would seem to be better as a comment on that answer
    – Dave M
    Apr 10, 2018 at 14:56
0

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

1
  • 2
    Changing format only does nothing at first glance. You also have to re-enter all values or use the text in columns option. But in this moment all binary values will lose their leading zeros. And thats what the OP wants to avoid. Unfortunately thats not a solution.
    – nixda
    Dec 31, 2012 at 6:16
0

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

1
  • 1
    I've stated in the question that I don't want to globally disable checking - I just want to dismiss the errors found within a certain range of cells.
    – Iszi
    Nov 30, 2017 at 15:16
0

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.

1
  • 1
    I don't want to disable the error checking. I just want a way to tell Excel "this bunch is okay, quit bugging me about them". This was stated in the original Question post.
    – Iszi
    Nov 30, 2017 at 15:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .