38

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?

  • 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 '12 at 6:21
  • there is a way to de-activate check in cell, and this is persisted in XLSX but not in XLS though – Arioch 'The Mar 5 '13 at 12:28
54
  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.

  • 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 :) – chrispepper1989 Oct 13 '15 at 15:00
9

Uncheck this option:

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

  • 3
    I want to ignore multiple without ignoring globally. This is specifically mentioned in the question. – Iszi Oct 21 '15 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 '15 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 '16 at 20:24
  • About once a year I find myself reading this answer again. I wish I could upvote it every time. Thanks again! – Devil's Advocate Feb 13 '19 at 15:00
7

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.
  • 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 '18 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 '18 at 12:19
  • @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 '18 at 15:16
  • 2
    This is more a comment on the answer by @JosephSerido rather than a completely new answer – Dave M Mar 22 '18 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 '18 at 14:56
0

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

  • 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 '12 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
    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 '17 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
    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 '17 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.