Inheritance Tracker template for Excel


Problem

Text to Columns is a handy Excel feature that lets you split a cell based on a given character. This is especially useful when provided with a set of cells that contain multiple values. For example, a cell containing the text “Mostly Harmless” might be split by a space ” ” resulting in two columns containing the text “Mostly” and “Harmless”.

What a great time saver, well played Excel.

However, what if I need to split by line? Checkmate. Excel can’t do it.

I was recently on a project where I needed this exact feature to deal with a large report that was formatted similar to this:

Notice that Column B lists multiple names per cell which means I cannot sort, filter or run pivot tables. While a simple example like that can be resolved manually by splitting to columns and transposing the resulting cells into a single column, this issue becomes tiresome when dealing with larger sets of data. We’re at a roadblock.

Desired Outcome

Ideally, the data would be provided with repeating values and each value described on its own line, as below:

Solution

I’ve written an Excel add-in to perform this task, in the interest of never having to deal with this time consuming problem again.

  1. Firstly download the add-in installer
  2. Once installed, open a new Excel window and select Options from the File menu
  3. Select the Add-ins view, check that you can see Text to Rows in the list of available add-ins and click on the Go… button, this will show an add-ins dialog
  4. Use this dialog to select the Text to Rows add-in
  5. Check that the add-in has given you a shiny new button in your Data Tools menu

Planned Features

The next planned feature is:

  • Progress bar for long-running actions – This add-in should provide more user feedback when applied to a large range