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. 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.
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, save time by filling 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. Let’s look at the steps.
Select Blank Cells
Copy Values from Above
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
Be careful if the range contained 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 is a fast and easy way to repeat existing data and to quickly change downloaded data so you can get your work done in Excel.
Discover more Excel shortcuts, tips and tricks here.
By Dawn Bjork, MCT, The Software Pro®
Microsoft Certified Trainer, Technology Speaker, Software Consultant