If you download data into Excel from other programs, one of the first things you should do is to check for blank cells and fill them in as needed. Some cells may be blank on purpose and should remain that way. But other blank cells can be troublesome, especially if you want to sort and filter the data or create a PivotTable from the data. For instance, a column in a report may only list the key category once for a group of records, whereas, in Excel, you would typically want the category repeated on each row. Cleaning up data is a key step to creating reliable reporting in Excel.
To learn how to use Excel to clean up downloaded data with blank cells, continue reading this article or watch my video:
This worksheet is an example of data from another source that, as is, doesn’t lend itself perfectly to a spreadsheet. In a report, repeating the region name from record to record might be distracting to the reader. In a worksheet, however, a simple sort by a field other than the region name field would quickly orphan data. That is, there’s no way to attach a record to its region.
When you download data or reports that you want to sort, filter, or analyze with a PivotTable, save time by filling in the blanks right away. Although you might consider typing or copying the entries, this can be very time-consuming, especially with large downloads of data.
There is a quicker way to fill blank cells in Excel worksheets with a few easy tricks. Let’s look at the steps.
How to Fill Blank Cells in an Excel Worksheet
Select Blank Cells
- First, select the range that contains blanks you need to fill. Choose Go To Special from the Find dropdown list in the Editing group on the Home tab.
-OR-
Press [Ctrl] + G for Go To. The F5 function key is another way to launch Go To. Then, pick Special from the dialog box. - Next, select Blanks.
Quick Tip: Notice the additional options under Go To Special, which are helpful with other Excel actions, such as selecting the cells that contain a formula. - When you click OK, Excel will select all the blank cells in the selected range.
Copy Values from Above
In the first selected blank cell (such as A3), enter an equal sign and point to the cell above. As the cell is already selected, you don’t have to actually click A3. Don’t press [Enter].
For our next step, we’ll press [Ctrl] + [Enter], which enters the value or content but doesn’t move down to the next cell. By default, when you press [Enter], Excel moves down a row.- Now press [Ctrl] + [Enter], and Excel will copy the respective formula to all blank cells in the selected range. This keyboard shortcut can be used as a quick way of filling a lot of cells or copying a formula into a range when it is entered rather than copying it separately afterward.
At this point, the range contains literal values (the original values) and formulas that repeat those literal values. To preserve the displayed data, replace the formulas with their results (the repeated literal values).
Paste Values in Excel
Select the entire column range and copy using any technique you prefer. For example, the Excel keyboard shortcut [Ctrl] + C for copy.- Right-click on the selection and choose Paste Values from the shortcut menu or pick Paste Values from the Paste drop-down list in the Clipboard group on the Home tab. You can also find Paste Values from the Paste Special menu option.
- With Paste Values, you just replaced the formulas with literal values.
Be careful if the range contains formulas before the process. You don’t want to overwrite those formulas, just the formulas you added to fill the blank cells. This technique won’t work with every sheet that contains blank cells, but it is a fast and easy way to repeat existing data and quickly update downloaded data so you can get your work done faster and easier in Excel.
Discover more Excel shortcuts, tips, and tricks here.
© Dawn Bjork, MCT, MOSM, CSP®, The Software Pro®
Microsoft Certified Trainer, Productivity Speaker, Certified Speaking Professional
