Tip: Quickly fill blank cells with value above in Excel

Save 5 minutes a day by using ASAP Utilities to quickly fill blank cells using the data in the cells above.

Auto fill empty cells using the data from the cell above

When setting up data in Excel it is good practice to ensure that all cells with labels are filled.
Empty cells can lead to trouble when you sort or filter your data or use it for a pivot table.

The worksheet below is a good example of data that as is, isn't suitable as spreadsheet/table.
In a report, repeating the company name from record to record might be distracting to the reader. However, a simple sort would quickly orphan data - there’s no way to attach a record to its country anymore.

Some worksheets contain cells that have been left blank, in order to make the headings easier to read.

If you want to sort or filter the list or use it a source for a pivot table then you need to fill in the blanks, by copying the value from the first filled cell above the blank.
Instead of doing that manually, we've got a tool for you that easily fill all these empty cells.

Just select the cells and then start:
ASAP Utilities » Fill » Copy values to empty cells below filled cells in selection

Copy values to empty cells below filled cells in selection

Do you recognize any of these situations?

  • I have a spreadsheet that I need to filter for a user name. The problem is that the user name appears on the first record but not on the records underneath. If I filter I only get one record with the name against it.
    Does Excel have a function that lets you copy the column then special paste the data in such a way that blank cells are automatically filled with the data held on the cell directly above?
  • How can I fill in blank cell from cell above?
    I can use Ctrl+" or drag the information down or even copy and paste. However, I have over 75,000 lines of data and little time to accomplish this task. The blank cells are scattered throughout the column.
    Is there a formula or tool that I can use to do this faster?

Just select the cells and then use the following tool:
ASAP Utilities » Fill » Copy values to empty cells below filled cells in selection

This often a quicker alternative with no or less limits than using a combination of Excel's Goto > Special and a formula.

How much time will it save?

It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working with and how often you use this particular tool.
You can easily see how much time ASAP Utilities has saved you so far.

Download

In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial.
Download page

Comments are closed.

Pinterest