You’re probably thinking “of course it’s easy, just copy it.” But sometimes you have data laid out like the example below. The customer name and sales rep have only been entered when the data changes. This way they act like headings and make the report easier to read. But if you want to sort this list, filter it, or use the insert subtotals as described in last month’s tip, you need all of the information on every line. That’s a tedious process when done line by line.

Customer Sales Rep Date Invoice/CM # Amount Due
Aldred Builders, Inc. DGROSS 02/15/2016 10129 4426.94
03/15/2016 10220 66.72
03/15/2016 10345 42.39
03/22/2016 10360 13.73
Snowden Interior Design SPRICHARD 01/26/2016 10209 7102
02/16/2016 10325 59.98
DBECK 03/11/2016 10306 184.29
Chapple Law Offices DGROSS 03/01/2016 10256 693.7
03/14/2016 10341 37.1
03/15/2016 10313 199.96
03/15/2016 10335 180.09
Archer Scapes and Ponds BNUNNLEY 02/23/2016 10209 7374.69
03/04/2016 10329 59.98
03/15/2016 10317 49.99
03/15/2016 10320 -49.99

Combining a few Excel tricks turns this into an amazingly easy process. The basic steps are:

  1. Select all of the empty cells
  2. Enter a formula in each of those cells at once that points to the cell above it
  3. Convert the result of the formula into text.

To select the empty cells, use the Go To command either by pressing the F5 key or pressing Ctrl+G. In the Go To window click the Special button, then choose Blanks and click OK. You should now have only the blank cells selected. If it selected cells outside of the range you wanted, manually select the entire range, then repeat those steps to select just the empty cells within that range.

To enter the formula, with the empty cells still selected, type an equal sign and then press the up arrow key. In the sample spreadsheet above you would see =A2 in the first blank cell. You should see something similar in your spreadsheet. Now press Ctrl+Enter, that is, hold down the Control key (usually shortened to Ctrl) and then press Enter. That will enter the formula into the first cell and simultaneously copy it into all of the other selected cells.

Now you just need to convert the formulas to text. Manually select the entire area that contains the new formulas. In most cases it’s easiest to select all the columns or press Ctrl+A to select the entire spreadsheet. Now copy the selected the cells (Ctrl+C or right click and choose Copy). Then right click anywhere in the selected area, choose Past Special, and then Paste Values. Your formulas have now been replaced with the text, numbers, dates, etc. that were the result of each formula.

One warning about the last step is that every formula in the copied range will be converted to a value. So if you have some formulas that you wanted to preserve you would need to be careful not to select those cells before performing the Copy/Paste Special step.


If You Liked This Post: