Fill Data in Multiple Empty Cells

pic1 data fill
msofficeshare logo

File to download:  None

 

Related Article:  Select Displayed Cells for Copying

 

Sometimes I got the below data format when I downloaded data from a system or obtained a file from our clients.  The clients might have used a pivot table to create such data and copy-and-paste-value the data before they sent us the file.

That poses a problem if I want to further analyze the data by using filters, queries, or pivot tables because of the existence of empty cells.  There is an easy way that you can populate the related data in the empty cells all at once.  Please follow the below steps to do it.

pic1 data fill
pic2 data fill

Step 1:  Highlight the area of data that you want to fill the data.  In the above example, I highlighted the cells from A4 to F27.

Step 2:  Click the “Find & Select” icon and choose “Go To…” command.  If you know the shortcut key (CTRL+G), you can just use the shortcut key.

Step 3:  In the “Go To” pop up screen, click the “Special” button.

 

Please note that in the newer versions of Excel, you can instead pick "Go to Special" in Step 2 to bypass Step 3.

pic3 data fill
pic4 data fill

Step 4:  Mark the radio button of “Blanks” in the “Go To Special” screen and click “OK” button.

pic6 data fill

Now you can see that only the empty cells are marked with light blue color.

pic5 data fill

Step 5:  In any one of the cell that is blue highlighted, type in the formula of the cell that you want it to be.  In the example, I use the content of the cell right above it as reference.

 

Step 6:  Press CTRL+ENTER keys together.  Please note that if you just press the ENTER key, only B5 cell will be populated with the data.  You need to press CTRL+ENTER keys together to have all the empty cells populated at the same time.

 

 

pic7 data fill

 

This is the result.  You are recommended to use copy-and-paste-value command to freeze the data instead of leaving the formula in.  Now you can play with the data in pivot tables, filters, or other data mining commands.