Fill Blank Cells for Microsoft Excel

Fill Blank Cells for Excel will copy the contents downwards or upwards from the first populated cell. You can specify if your table has headers or choose to split the merged cells.

Before you start

Keep an eye on the Backup the worksheet option. We always recommend selecting it as Excel doesn't let you cancel changes made by add-ins.

How to use Fill Blank Cells

Start Fill Blank Cells

Select the range in your table where you need to replicate cell values.

Tip. If you click on any cell in your table and run the tool, it will select the entire table automatically.

Run the add-in by clicking the Fill Blank Cells icon in the Transform group on the Ablebits Tools tab:
Fill Blank Cells icon.

Fill blanks upwards or downwards

You can see the Fill Blank Cells dialogue window and its elements:
The Fill Blank Cells window.

  1. Make sure the range at the top of the window contains the cells you want to fill. To expand the selection and select the whole table, click the Expand range icon.
  2. Also, you can edit the range manually or by clicking the Select Range icon and picking the necessary cells:
    Select range to fill blanks in Excel.
  3. Check My table has 1 header row to indicate if your table has a label.
  4. If you have more than one header row, click 1 header row and select the required number of header rows:
    Enter the number of header rows.
  5. Tick Split the merged cells if there are such cells in your table and you want to get them unmerged.
    Note. If the selected area does not include merged cells, the checkbox is grayed out.
  6. Check the boxes next to the columns where you want to fill blanks. If you have a lot of columns in your spreadsheet, use the checkbox at the top of the list to quickly select and unselect them all at once.
  7. We recommend selecting the Back up this worksheet option to keep a copy of your source table.
  8. Pick the way to fill cells in the selected columns:
    Select to fill blank cells upwards or downwards.

    • Choose to Fill cells downwards if you wish to take the value from the cell above and copy it into the blank cells below:
      How to fill blank cells with the values above in Excel.
    • If you want to fill gaps with the value of the cell below, pick Fill cells upwards in the dropdown list:
      How to fill blank cells with the values below in Excel.

Click Fill to get all the blank cells in the chosen range filled with the values from above or below.

Responses

Hello,

I have recently needed to use the fill down option on a column with 6800 rows. The fill down nature was to copy the formula "=Countif" from A2 down to A6801. When I attempted to use the Fill Blank Cell option on the rest of the rows, the result or outcome of the formula on A2 was copied down to A6801 and not the formula itself unlike the Cntl-D fill down option. With that in mind, will you kindly let me know if there is another way in Ablebits to copy down or fill down (or up etc.) a formula and not the result of the formula?

Thank you and have a great day.
Michael

How do I see the Fill Blank Cells window and its elements?

Hello Pat,

Thank you for your question. Please note that the Fill Blank Cells tool is a component of Ultimate Suite. If you have already installed the add-in, just click on the Fill Blank Cells button under Ablebits Tools in your Excel Ribbon and you will get the window of interest displayed. If you have not installed Ultimate Suite yet, please feel free to download its trial version. This is a direct link for you: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial

Fer Roque says:
May 3, 2019 at 5:11 am

Hello,

The function of filling cells in white, is very interesting, however, it is filled with the values of the lower or higher cells.

In my work, I create dynamic tables, where I have to fill in blank cells with the value ZERO, in order to generate sales statistics and averages.

It will be possible that in later versions, add this function, to be able to choose which value you want to insert in the empty cells.
Thank you.

Hello,

Thank you for contacting us and for your feedback.

I have forwarded your idea to our developers. They have added this feature request to their development schedule, but I can't give you an exact timing when the functionality is implemented.

In the meantime, I'd recommend you to look at the following article on our blog:
https://www.ablebits.com/office-addins-blog/fill-blanks-excel/

Feel free to contact us again if we can help in any other way.

when filling blanks, is there a way to also fill with the copied cell's colors?

Hi, Aaron,
Thank you for your comment.
Unfortunately, the tool does not copy the formatting of the cells, only the values. However, I will pass your idea to our developers and we will consider this as a new feature in our coming Ultimate Suite updates. I will let you know when it's implemented.
Thank you!

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.