How to fill empty cells with 0, with value above/below in Excel

In this article you'll learn a trick to select all empty cells in an Excel spreadsheet at once and fill in blanks with value above / below, with zero or any other value.

To fill or not to fill? This question often touches blank cells in Excel tables. On the one hand, your table looks neater and more readable when you don't clutter it up with repeating values. On the other hand, Excel empty cells can get you into trouble when you sort, filter the data or create a pivot table. In this case you need to fill in all the blanks. There are different methods to solve this problem. I will show you one quick and one VERY quick way to fill empty cells with different values in Excel.

Thus my answer is "To Fill". And now let's see how to do it.

How to select empty cells in Excel worksheets

Before filling in blanks in Excel, you need to select them. If you have a large table with dozens of blank blocks scattered throughout the table, it will take you ages to do it manually. Here is a quick trick for selecting empty cells.

  1. Pick the columns or rows where you want to fill in blanks.
    Select the columns or rows where you want to fill in blanks
  2. Press Ctrl + G or F5 to display the Go To dialog box.
  3. Click on the Special button.
    Press Ctrl + G or F5 to display the Go To dialog box

    Note. If you happen to forget the keyboard shortcuts, go to the Editing group on the HOME tab and choose the Go To Special command from the Find & Select drop-down menu. The same dialog window will appear on the screen.

    Choose Go To Special from the Find & Select drop-down menu to display the Go To Special dialog

    The Go To Special command allows you to select certain types of cells such as ones containing formulas, comments, constants, blanks and so on.

  4. Select the Blanks radio button and click OK.
    Choose Blanks to get only empty cells selected

Now only the empty cells from the selected range are highlighted and ready for the next step.
The empty cells become highlighted

Excel formula to fill in blank cells with value above / below

After you select the empty cells in your table, you can fill them with the value from the cell above or below or insert specific content.

If you're going to fill blanks with the value from the first populated cell above or below, you need to enter a very simple formula into one of the empty cells. Then just copy it across all other blank cells. Go ahead and read below how to do it.

  1. Leave all the unfilled cells selected.
  2. Press F2 or just place the cursor in the Formula bar to start entering the formula in the active cell.

    As you can see in the screenshot above, the active cell is C4.

  3. Enter the equal sign (=).
  4. Point to the cell above or below with the up or down arrow key or just click on it.
    Enter the equal sign and point to the cell above to display the formula in the empty cell

    The formula (=C3) shows that cell C4 will get the value from cell C3.

  5. Press Ctrl + Enter to copy the formula to all the selected cells.
    Press Ctrl + Enter to fill all the blank cells

Here you are! Now each selected cell has a reference to the cell over it.

Note. You should remember that all cells that used to be blank contain formulas now. And if you want to keep your table in order, it's better to change these formulas to values. Otherwise, you'll end up with a mess while sorting or updating the table. Read our previous blog post and find out two fastest ways to replace formulas in Excel cells with their values.

Use the Fill Blank Cells add-in by Ablebits

If you don't want to deal with formulas every time you fill in blanks with cell above or below, you can use a very helpful add-in for Excel created by Ablebits developers. The Fill Blank Cells utility automatically copies the value from the first populated cell downwards or upwards. Keep on reading and find out how it works.

  1. Download the add-in and install it on your computer.

    After the installation the new Ablebits Utilities tab appears in your Excel.

  2. Select the range in your table where you need to fill empty cells.
  3. Click the Fill Blank Cells icon on the Ablebits Utilities tab.
    Install the add-in and find the Ablebits Utilities tab in the Excel window

    The add-in window displays on the screen with all the selected columns checked.
    Click Fill Blank Cells icon to open the add-in dialog window

  4. Uncheck the columns that don't have empty cells.
  5. Select the action from the drop-down list in the bottom-right corner of the window.

    If you want to fill the blanks with the value from the cell above, choose the Fill cells downwards option. If you want to copy the content from the cell below, then select Fill cells upwards.

  6. Press Fill.
    Choose the columns and the action for the add-in to fulfill

Done! :)
Press Fill to fill empty cells in Excel

Besides filling empty cells, this tool will also split merged cells if there are any in your worksheet and indicate table headers.

Check it out! Download the fully-functional trial version of the Fill Blank Cells add-in and see how it can save you much time and effort.

Fill empty cells with 0 or another specific value

What if you need to fill all the blanks in your table with zero, or any other number or a specific text? Here are two ways to solve this problem.

Method 1

  1. Select the empty cells.
  2. Press F2 to enter a value in the active cell.
    Press F2 to enter the value in the active cell of the selected range
  3. Type in the number or text you want.
  4. Press Ctrl + Enter.
    Press Ctrl + Enter to copy the input value from the active cell into all the blanks

A few seconds and you have all the empty cells filled with the value you entered.

Method 2

  1. Select the range with empty cells.
    Select the range in which you need to fill empty cells
  2. Press Ctrl + H to display the Find & Replace dialog box.
  3. Move to the Replace tab in the dialog.
  4. Leave the Find what field blank and enter the necessary value in the Replace with text box.
    Type in the value in the Replace with text box to fill in blanks with it
  5. Click Replace All.

It will automatically fill in the blank cells with the value you entered in the Replace with text box.

Whichever way you choose, it will take you a minute to complete your Excel table.

Now you know the tricks for filling in blanks with different values in Excel 2013. I am sure it will be no sweat for you to do it using a simple formula, Excel's Find & Replace feature or user-friendly Ablebits add-in.

201 comments

  1. This was very helpful, it saved me so much time as I needed to do this for a file with almost 20,000 rows and 2 columns where I needed to autofill using above cell value!

  2. worked perfectly thank you!!

  3. Hi,
    I have tried the same steps multiple times, however I have a large dump of excel data and when I do this, it fills in all the blanks with "0" instead of the text above

    Eg:
    Jupiter
    Jupiter
    Jupiter
    Saturn
    Saturn
    Saturn - Correct answer

    However, what I am getting is
    Jupiter
    0
    0
    0
    Saturn
    0
    0
    0 - Incorrect

    How do I correct this?

    1. How I do this same work below to upper side ? I mean how work reverse side fill blank cell

      1. Hi! To fill the empty cells with the value below, you can use the data from the second paragraph of this article as an example. Select cells C4:C8, E4,E5,E7,E8, E10, E11. In the formula bar, type =E11 and press Ctrl+Enter.

  4. What formula will I use to automatically fill 0 in blank excel cells that is connected to a server via API which auto updates every minute

  5. how can we do from a table value cells to replaces with 1.

  6. There is an easier way to add a 0 to blank cells - highlight the entire table, go to replace - leave the "find what" box empty and type "0" in the replace with box and hit "replace all"

  7. Thank you for Excellent guidance , its worked

  8. Hi,

    If the vlookup result of min is blank and needs to automatically take the previous non-blank cell value of the result range.

    1. Hi!
      It is impossible to give you exact advice since you have not written your VLOOKUP formula. Also explain what "previous non-blank cell" means. Please provide me with an example of the source data and the expected result.

  9. Is there a way to do this the other way around - zeros to blanks?

    I have a master workbook, and the range B2:B5000 contains codes in cells B2:15 which will be updated periodically.

    Workbook X refers to this range in column B. Excel returns zeros in cells where there is currently no data in the range B2:B5000 in the master workbook.

    In workbook X, I need to extract all the codes into another list and eliminate the zeros so I used the formula below.
    =IFERROR(INDEX($B$2:$B$5000,SMALL(IF(ISTEXT($B$2:$B$5000),ROW($B$1:$B$4999)), ROW(B1))),"")

    This only works if the cells in the range in the master workbook contains alphanumeric strings. Is there a formula I can use that will work if the strings are only numeric?

    1. Hi!
      If you want to extract text and numbers from a range, use the ISBLANK function to exclude empty cells.

      =IFERROR(INDEX($B$2:$B$5000,SMALL(IF(NOT(ISBLANK($B$2:$B$5000)),ROW($B$1:$B$4999)), ROW(B1))),"")

      1. Works perfectly, thank you Alexander!

        1. I've just realised that this doesn't work the way I want unfortunately. Whilst this does extract both text and numbers now, it does not eliminate zeros.

          This is what I require:

          MRK456 should return MRK456
          187456 should return 187456
          0 should return a blank

          Can this formula be tweaked so that it extracts all text and number strings but also onvert any cells with 0 to blank cells?

  10. Thanks a million!

  11. Useful; thanks a lot! It solved my problem :)

  12. Hi - Use this command often. However, today I ran across an error "no cells were found". Changed the format of the number to text and back to number; still getting the message. Any idea on how to bypass?

  13. thank you soooo much.
    very helpful

  14. very very helpful - thank you

  15. I don't know how much I can than you. But, any way, Thanks!

  16. That was brilliant useful, Thank you so much!

  17. Some of blank cells were not filled with the content, they are still blank after using this function ? How to solve this ?

    Thanks
    G

    1. Hi!
      I can't guess which function you used and exactly which cells were left blank. Can you describe it?

  18. Hi Sir/ma'am,
    Please help me, how can fill all blank spaces in a multiple selected rows with zero (0).

    1. Hi!
      Pay attention to the following paragraph of the article above - Fill empty cells with 0 or another specific value.

  19. Great solution, thank you for sharing it! Saved quite some time

  20. Time saver ?

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 :)