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.
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.
Step 4: Mark the radio button of “Blanks” in the “Go To Special” screen and click “OK” button.
Now you can see that only the empty cells are marked with light blue color.
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.
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.
