Filling in Blank Spaces in your Data

​By Dale Amburgey, Assistant Director, Institutional Research, Embry-Riddle Aeronautical University

At some point in your analytical endeavors, you may have received a data file that resembles this:

blanks1.JPG

                                                    Figure 1 

Normally, you would go to each cell and manually fill in the missing data elements. For a small data set, this wouldn’t be too much trouble. However, for a much larger data set, you may find this to be a cringe-worthy task.

Luckily, there is a tool within Microsoft Excel that will allow you to fill in the missing data very quickly, regardless of the size of the file with which you’re working.

Our sample data file contains course listings from two excellent institutions of higher education. We need to fill in the blanks so that our data are much cleaner for future analysis. In order to do this, we will use a combination of Find & Select and Go To Special within Excel.

Our first task is filling in the college name. First, we must select the range of data we want to modify (in this case A2:A30 as seen in Figure 2). Next, we click on Find & Select and choose Go To Special. (Figure 3).
blanks2.JPG
                                                Figure 2

blanks3.JPG
                                               Figure 3

This will open the Go To Special window, where the next step is to select Blanks (Figure 4).

blanks4.JPG
                                            Figure 4

After Blanks is selected, our data file should look like the following (Figure 5):

blanks5.JPG
                                                    Figure 5

At this point, we want to enter the address for the first cell that contains the data we want to fill. In this example, we will enter =A2 into the formula bar. After we enter the cell address, the final step is to hit CTRL + Enter (Figure 6).

blanks6.JPG
                                                 Figure 6

Next, we will perform the same steps to fill in the course subject names. The process is the same, regardless of the number of data items with which you are working. The following images illustrate the steps and outcomes for filling in the Course Subject data:

First, we select the second range of data to modify. In this case, we select B2:B30 (Figure 7). After we select Find & Select, then Go To Special and then select Blanks, we should see the output in Figure 8. At this point, we will enter =B2 in the formula bar (Figure 9) and CTRL + Enter (Figure 10) to complete the process of filling down.
blanks7-8.JPG
                                            Figure 7                                                                                            Figure 8

blanks9-10.JPG
                                            Figure 9                                                                                            Figure 10

This tip allows the end user to expeditiously fill blanks without having to select each section individually. It is a great time saver when one is faced with a large amount number of blanks within his or her descriptive data.

 

 Comments

 
To add a comment, Sign In
Total Comments: 5
 
Lee posted on 7/16/2015 3:37 PM
Thanks Dale! Great tip - definitely something I will put to use in my Excel files.
Julia posted on 7/16/2015 6:07 PM
I was not aware that you could do this in Excel. Great tip to streamline a manual process, thanks for sharing!
Keith posted on 7/16/2015 6:44 PM
This was an informative tip. Thanks for sharing.
Lisa posted on 7/17/2015 8:34 AM
This is very cool! Thank you for sharing this tip!
Shawn posted on 7/17/2015 9:31 AM
This will be an extremely useful tip for anyone using canned reporting tools; the formatting often leaves blank spaces, requiring a lot of manual intervention. Thanks for sharing!