Fixing Excel Data – Text to Rows and Fill Down

Excel spreadsheets are used at almost every level of business.  As such spreadsheets are created by people with different goals and different levels of familiarity.  What many don’t realize is that excel is primarily a data processing and information storage tool.  Excel is much like a database and is most effective when data is stored as one would store information in a database.  The problem is that data in a database isn’t necessarily that presentable.  So many people input information into excel and attempt to format it for presentation.  Many people create groups and merge cells or insert multiple entries into a single cell and then apply special formatting.  The down side is when someone wants to use the graphing and report building features of excel the combined or grouped data isn’t accessible because it is either missing from cells due to cell merging or each entry isn’t accessible on its own because it has been entered into a single cell.  The best method is to treat the data as a database and ensure that each cell in a column has data in it relative to the cells in the row so that a complete record is available.  Then use the grouping and pivot table features to create reports that are presentable and much more functional because the data can be grouped and categorized in two dimensions.

But what do you do if you get  a spreadsheet in which data has been either entered into one cell but not in subsequent rows in some cases or has been merged through rows?  The first thing to do is to select the column with merged data and unmerge it. But then what now.  You can either go through manually massage the data adding rows and extracting the entries entered in a single cell and use a fill down copy where an entry was merged through rows.  But if you have hundreds of rows this can be time consuming.   I have run into this problem frequently enough that I have taken some time out to write a couple functions that spead up the process.

Text To Rows

If you have ever imported data into excel you may be familiar with the text to columns function under the data menu.  If you didn’t specify delimiters or missed one while importing you can select the column with multiple fields of data and tell it what to split the data on and it will automatically split the data across columns.  But what about rows.  The procedure below may be run on any column where multiple entries have been combined into single cells.  The default delimiter is a comma but this may be modified to meet your particular needs.

Fill Down Copy

If you have data in one cell which is either merged through rows or in a single cell with subsequent rows blank you will not be able to group by this column until each record has the corresponding data in each cell in the column.  You could page down and select each populated cell and then double click the little corner box to perform a fill down copy or you can execute a procedure.  The procedure below may be run to copy down data into unpopulated cells.