Three Ways to Fill Blank Cells within Excel Spreadsheets
by Terri Eyden on
By David H. Ringstrom, CPA
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:
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:
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 the author:
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at firstname.lastname@example.org or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.
You may like these other stories...
It's not a reality—yet—but accounting software is poised to eliminate accountants. We are at a tipping point for many similar professions: online education replacing professors, legal software replacing...
Whenever I speak to accountants about creating a cloud practice, the most common question is, “How do I charge my clients?” Ten years ago, maybe even five years ago, if I would’ve posed this question...
While reputational risk is the No. 1 nonfinancial concern among corporate directors, cybersecurity/IT risk is gaining steam. In fact, both private companies and organizations with more than $1 billion in revenue felt they...
Upcoming CPE Webinars
In this session Excel expert David Ringstrom helps beginners get up to speed in Microsoft Excel. However, even experienced Excel users will learn some new tricks, particularly when David discusses under-utilized aspects of Excel.
FRF for SMEs Series--Measurement and Disclosure Principles for various Consolidations and Business Combinations, Part 4B
This webcast will focus on accounting and disclosure policies for various types of consolidations and business combinations.
In this session we'll review best practices for how to generate interest in your firm’s services.
Meet budgets and client expectations using project management skills geared toward the unique challenges faced by CPAs. Kristen Rampe will share how knowing the keys to structuring and executing a successful project can make the difference between success and repeated failures.