Comments on: How to remove blank cells in Excel

Empty cells are not bad if you are intentionally leaving them in right places for aesthetic reasons. But blank cells in wrong places are certainly undesirable. Luckily, there is a relatively easy way to remove blanks in Excel. Continue reading →

Comments page 2. Total comments: 69

  1. When I do this, for some reason ROW(A1) always stays as ROW(A1) in every cell throughout the range, whereas it should be A2, A3, A4, etc. in each cell lower down in the array output range. Not sure what I'm doing wrong.

    1. Hello Joe!
      If I understand your problem right, you are trying to copy =ROW(A1) down to the other cells in column A. Unfortunately, I can’t reproduce the issue, the formula is copied correctly. It does not change only if it looks like =ROW($A$1). Maybe, you are copying a formula, not a cell. Please describe in more detail what steps you take and we’ll try to find the cause of the issue.

  2. Hello!
    I wants to remove formula error(#N/A! or #Num!) cell or formula blank cell, although it's not blank but is it possible? if possible how to do please explain. Thank you.

    1. Hello Al-Amin!
      In order not to show errors in cells, the IFERROR function is used. To learn more about it, please read this article on our blog: https://www.ablebits.com/office-addins-blog/excel-iferror-function-formula-examples/

      You can use our Ultimate Suite for Excel to search for empty cells with formulas. You can select all cells with formulas in a sheet by using Select by Value -> Select Special Cells. Then you can substitute all selected formulas with their values by using Convert Formulas to Values. All invisible and non-printing characters can be deleted with the help of Remove Characters.

      You can install Ultimate Suite in a trial mode and test the tools for 30 days for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
      I hope this information will be helpful to you.

  3. Hey,
    I want to remove blank cell in excel.
    can you guide me how to do it?

  4. I can't get this to work for me, unfortunately. Would I be able to send a screenshot of what I'm doing? Everything looks accurate in the formula but its returning a blank cell.

    Thanks

  5. your presentation with screen shot is very easy to understand. im happy to learned something. Great and Good Job. Thanks

  6. Hi, What formula could be used to sort items without blank cells, if the range is not vertical (A1:A10), but it is horizontal (A1:K1), for example?

  7. Hi there, I am attempting to use this function to remove blank cells but this only seems to work on the cells after the last cell containing data.
    i.e. all blank cells in between data are still there.
    Can you please advise?

    1. Hello Warren,

      Please see the first 3 sections of this tutorial. They explain how to remove blanks in between data.

  8. Hi,
    Your formula is works when the cell is really empty.
    But I make a cell empty (visually ONLY) by formula "=IF(A$2=ISBLANK(0);"";A$2".
    It means the cell is not really Empty :-)
    But I still need the result like in your example "How to extract a list of data ignoring blanks."
    Could you please help me this issuer?

  9. I found that using the following tweaked formula helped me accomplish the results better if trying to remove the blank rows that contain formulas where the blank row actually equals "", which is not truly blank.

    {=IFERROR(INDEX(range, SMALL(IF(range"", ROW($A$1:$A$10),""), ROW(A1))),"")}

  10. VERY Helpful!!!
    I got around Excel not counting a formula as blank because my actual interest is in whether there is a number in the checked cell -- else omit row. Exchanging the "ISBLANK" for "ISNUMBER" fits my need. As well, I created a variable end for the A1:... with an INDIRECT(ADDRESS( ...)) that uses a cell that knows how many rows there are as this changes with each use. Again, works great. Thanks!!!
    John

  11. Extremely helpful tutorial and explanations on How to extract a list of data ignoring blanks. However its not working for me. Not yet. The problem is that my data is dynamically changing almost every day and I have it organised as a table. In a certain column there are new data generated daily and also there are blanks every now and then. So how do I adapt the original formula in order to dynamically (automatically) extract a list of data ignoring blanks in daily changing (adding) data? I believe the solution to this problem would be very appreciated from a broader community. Thank you again for this tutorial and kindest regards!

    Marjan

  12. Hi, Thank you for the exceptional guide and I find it working for simpler tasks. However, I´m working on a larger project right now.

    The goal is to Extract data from a table, only if a certain name is printed. Example if Martin is printed, the idea is for the list to blank/NA() all the info that is not related to Martin.
    Example:
    Name:

    Peter Corn
    Martin Pizza
    Anna Orange
    Isak Pizza
    Fredrik Corn
    Martin Orange
    Peter Corn

    Name: Martin Result:
    Pizza
    Martin Pizza Orange

    Martin Orange

    The way I handle the problem is that as I use formulas to created the table I can´t use blanks, hence I replace it with NA() and isNA in the formula. There are 106 entries and only 5 of them are needed for the list. Hence it is 101 #N/A and 5 results with names.

    I Try to apply the formula as this (In swedish excel ;=,):
    {=IFERROR(INDEX($B$3:$B$98; SMALL(IF(NOT(ISNA($B$3:$B$98));ROW($A$1:$A$10);""); ROW(A1)));"")}

    Unfortunately, no result is shown. The only way I can get a result is if I put in a value at the top of the list, but that is only without the array function. Any ideas on how I can get it to work on this scale with formulas in the cells?

    Regards
    Martin

    1. Martin, this might be a bit old but in reading, you show row($a$1:$A$10) - this is only referring to the top 10 positions in the list not 95 like you need, should change to row($a$1:$A$95)
      95 = 98-3 .

    2. The Example did not, unfortunately, translate well after I pressed send, but the point is only to get out the food chosen by Martin.

  13. Hello raggazzi! (Italian for ""guys and gals" :-)>
    First, I want to say that the ablebits.com website is outstanding; not only in terms of Excel expertise, but also in the clarity of presentation and "style" of written english. Bravo!
    I have one problem, however, with
    "How to extract a list of data ignoring blanks"
    from https://www.ablebits.com/office-addins-blog/remove-blank-cells-excel/
    This is an elegant formula, but for me it only seems to work when I start it exactly as shown, at cell A1. If I attempt to move it, or even simply insert a row above, so that it begins on Row 2 at A2, it doesn't work. I've tried working with relative vs absolute addresses, etc. but no luck. Am I missing something?
    Thanks again,
    Howard

    1. Hi Howard,

      Thank you for your kind words about our blog, it's a good incentive for us to keep it up :)

      Regarding the formula. In fact, in our example, the data starts in cell A2 (A1 is the column header and it's ignored by the formula). The logic is explained in the "How the formula works" section that comes right after the example. To adjust the formula for your data, be sure to change only "range" in the generic formula below, and remember to press Ctrl+Shift+Enter to complete it:

      IFERROR(INDEX(range, SMALL(IF(NOT(ISBLANK(range)), ROW($A$1:$A$10),""), ROW(A1))),"")

      For example, if your data starts in rows 3, i.e. range=$A$3:$A$12 (mind the absolute references!), the formula would go as follows:
      =IFERROR(INDEX($A$3:$A$12, SMALL(IF(NOT(ISBLANK($A$3:$A$12)), ROW($A$1:$A$10),""), ROW(A1))),"")

  14. This does not appear to work with results from formulas. I have used an if condition to blank out many rows that are not needed. Trying to remove those rows using this does not seem to work.

    1. A solution would be to code blanks in your formula results as 'NA()', then replace 'ISBLANK()' with 'ISNA()' in the formula above.

    2. Hi Dustin,
      You are right, this does not work with formula results. In terms of Excel, a cell containing a formula is not blank even if the formula returns an empty string.

  15. Hi there thanks for the guide, how would you apply this to multiple columns? If this is not possible, is there a simple way to combine columns where this approach has been used multiple times. Many thanks.
    Ed

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)