Excel and Filling up blank cells

Recently I got a question on how to arrange the data in Excel for an analysis where some data was missing. Below here you see an example download from the database Joint Ventures & Alliances from SDC Platinum. Not every row has data but each deal starts on a new row with the date variable “Alliance Date Announced”. Each alliance also has a unique Deal number (3rd column):

In some programs (to do an analysis of the data) what you need is to have the data repeated for each row (observation) related to that specific instance and not any rows with blanks (unless a variable for a row/observation contains no data of course). In the following example the data looks like the previous picture, incomplete:

You want it to look as follows:

Excel has an option that allows you to automatically fill in all the blanks. This works as follows:

Step 1: Make sure that you click on a cell inside the data table

Step 2: In Excel click the option in the Excel ribbon: Find & Select > Go To Special

Step 3: In the pop-up mark the option: Blanks

Step 4: You see that Excel now tries to find all the empty cells in the active table and they are highlighted

Step 5: Use your keyboard to type “=
Step 6: Now click the Upwards arrow on your keyboard
Step 7: Use the keycombination: CTRL + Enter

N.B. 1: Please remember that after the final row Excel tends to add another row. In this case you need to delete it again as this would only add a duplicate observation to the table.
N.B. 2: Dates may be filled in as numbers. This is fine because in Excel dates are really numbers (number 1 is 1-1-1900). Just use formatting to make sure these numbers are presented as dates again

Warning: When you use the option to complete data for each observation, be sure that the correct data is duplicated. In the example output from SDC Platinum (below) you see columns that relate to events but which are spread over more rows. It may be an idea to copy parts of the data to separate sheets (for instance using unique event/deal numbers) to duplicate data there and later merge the data tables again. See columns G, I and J.


%d bloggers like this: