You fill in the blanks!

Figure 1: Neat data is not always "Analysis-Friendly" data

There are occasions when you will want to fill all empty/blank cells in a column. Often times, data is formatted in such a manner so that it is easier to read. Figure 1, for example, shows a portion of what an Accounting Report might produce for expenses over a given period of time. While it is very clean and easy to read, using this data to perform other analysis will require some work. If, for example, you wanted to use this data to show expenses by month or quarter, that would be difficult.

To make the data more “Analysis-Friendly”, then, the data needs to be converted to a “table” or “database” with records. To do so, we must make sure each row (or record) contains the expense (or similar category that is being grouped by the summary report). In our example file (attached), column B needs to contain the category for each row of data (date and amount).

Of course, we can do this the hard, manual way. We can retype the category for each subsequent row. No way! An easier way would be to copy the category heading and fill or paste in the subsequent rows. Better, but still some work.

There is actually an easier way.  Come on; of course there is!

1. Select the column in which you want to fill the blank cells
2. Press Ctrl-G (Go To)
3. Select “Special”
4. Select “Blanks” Option and hit “OK”
5. The first blank cell will be selected
6. Type “=a1”, where a1 is the populated cell immediately above the first blank (and selected) cell
7. Press CTRL-Enter (you must hold the Ctrl Key when pressing Enter)