Three Ways to Fill Blank Cells within Excel Spreadsheets

Paperwork
JohnnyLye_iStock_paperwork
Share this content
Tags

Periodically, you may have a need to fill in gaps within an Excel spreadsheet. One way to do so is to manually fill in each cell, but in this article, I'll show you three alternatives. First I'll use a formula, then Excel's Find and Replace function, and finally the often-overlooked Go To Special feature.

Let's say you have a spreadsheet that looks like Figure 1. You'd like to replace the blank cells in cells B3, B6, and B7 with the words No Response. To do so, you could add this formula in cell C2, and then copy it down through cell C7:

=IF(B2=""",No Response",B2)

In this case, the two double quotes determine if the cell is blank. If so, the IF statement returns the words No Response; otherwise, it returns the present contents of cell B2. Next, select cells C2 through C7 and press Ctrl-C. Right-click on cell B2 and then choose Paste Special. Double-click on Values to replace the original values. At this point, you can erase cells C2 through C7.

Figure 1: You can use an IF statement to populate blank cells.

A longer version of this formula would take this form:

=IF(ISBLANK(B2)",No Response",B2)

As you can see, ISBLANK returns TRUE if a cell is blank, or FALSE if it isn't.

Of course, in this case you don't necessarily need to use a formula. As shown in Figure 2, you can select cells B2 through B7, and then press Ctrl-H to display the Replace dialog box. Leave the Find What field blank and enter the words No Response in the Replace With field, and then click Replace All. This will automatically fill in the blank cells with the word No Response.

Figure 2: Find and Replace allows you to fill in blank cells.

A third way you can fill in these blank cells is to click once on cell A1, and then press Ctrl-A to select the list. Press Ctrl-G to display the Go To dialog box, and then click the Special button. Double-click on Blanks, which will result in just the blank cells being selected. Type the words No Response, and then press Ctrl-Enter. Doing so will put the words No Response in all of the selected cells at once, as shown in Figure 3.

Figure 3: The Go To Special command allows you to select Blanks, while Ctrl-Enter fills multiple cells.

About David Ringstrom, CPA

David Ringstrom

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.

Replies

Please login or register to join the discussion.

avatar
By Saurav
Jun 26th 2015 01:11

Liked the use double quote "" in place of if(isblank

Thanks (0)
avatar
By Mandy
Jun 26th 2015 01:11

Now I want cake.

Thanks (0)
avatar
By Barbara Newman
Jun 26th 2015 01:11

Go To Special - I love that command! Thank you

Thanks (0)
avatar
By laschwill
Jun 26th 2015 01:11

Excellent! Thank you!

Thanks (0)
avatar
By Harshi
Jun 26th 2015 01:11

Thank you :)

Thanks (0)
avatar
Sep 30th 2016 13:47

I've been working with Microsoft products for years, but have never used the Go To Special command; quite useful!! Thanks!!

Thanks (0)
avatar
By SWAID
Feb 1st 2018 04:24

Really liked the third option. Thanks a lot David. Really appreciate you sharing it

Thanks (0)
avatar
Apr 19th 2018 08:41

Helpful information! Thank you!

Thanks (0)