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)
And that’s it.  Each blank cell will be filled with the correct category or the nearest populated cell above it.
Of course we’re all here to learn to do things more or most efficiently.  To that end, there is an even easier way.  Well, you still perform the steps outlined above.  But this time, after selecting the desired area of the spreadsheet (step 1), we can record steps 2 through 7 with the Macro Recorder.  Of course we could write the VBA code from scratch, but why?  Running the Macro Recorder is much, much easier, not to mention quicker.  After you’ve done this, you can run the macro after selecting the region and, in one click of the mouse, perform all the steps at once.
For an example, and hopefully easy to understand directions, see the attached file: Attached File: Fill the Blanks
Advertisements

About bbluford
I am an executive finance professional with a love for process and application development (MS Access, Excel, Quickbooks), mostly as it relates to Accounting and Business Functions. I also love to write and share ideas with other people in this world. I'm an admitted Gym Rat who works out excessively. The best summation of me is that I love to teach and to learn.

2 Responses to You fill in the blanks!

  1. Chirs says:

    Wow! You don’t know how long i been doing a work around to this. This is great!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: