Importing Legacy Spreadsheets Just Got Easier

By Dean Zarras

As easy as it is to create ClearFactr plans from scratch, we know that being able to use a legacy spreadsheet model with ClearFactr is crucial. By using our Importer in "ClearFactr mode", many models are handled perfectly. But due to the infinite ways that people build models and label their content, importing some models can prove more challenging.

Today we're announcing a significant new feature that will enable nearly any legacy model to be imported quickly and painlessly. Rather than have ClearFactr attempt to discern what cells in the model represent the row and column labels (and thus enabling the Self-Describing Spreadsheet to achieve its full benefits), the importer’s newly featured “Excel mode” will leave things exactly as it finds them.

Then with a few swipes of the mouse, you can highlight a column or row of cells and designate them as the headers. It’s super simple, and super powerful.

Let’s walk through a few quick example. Say you’ve imported a model, like this:

null

In Excel mode, the Importer will initially leave the plan like this:

null

Notice the text cells that describe the numerical cells of the model. We want the labels at 'row 2' to be the ClearFactr column headers, and the values in column 'A' to be the ClearFactr row headers. There are also a few cells that could be treated as section labels.

Let's start with some obvious section labels such as "Numbers for Year 2015", "INCOME" and "EXPENSES." We'll select a cell ("Numbers for Year 2015", in column 'B') and right click to bring up this menu:

null

We'll select the "Move Cell to Section Label" option at the bottom, and repeat the process for each cell we want to convert, resulting in this:

null

Note the formula bar's description of the "active cell" near the lower right corner:

null

It's not too descriptive yet, but not far off from Excel's version, either:

null

Next, let's improve the column labels. We'll select the values in 'row 2', from columns 'B' through 'P.' and then right click again and select 'Move Cells To Columns' at the bottom of the menu:

null

Now the plan looks like this:

null

Notice that ClearFactr has automatically spanned the monthly columns with their year, and other columns wrap their titles.

Lastly, we’ll select the values in what's now the 'Personal Budget' column (which we'll later delete -- it winds up being empty), and select the "Move Cells to Attributes" menu item:

null

The plan is now done, and has been transformed into this:

null

Returning to that Monthly Total cell... we've just transformed the formula from this:

null

into this:

null

Better yet, here's the Describe Plan report showing a portion of the summary of the entire plan:

null

And now, of course, the fun begins... What affects your total accumulated savings the most, and by how much? Run a 1-click Factor Analysis to see:

null

Want to examine the Monthly Total Income vs. Expenses as a graph? 1-click...

null

For more detailed help and instruction on these important new features, please see our online help article. And as always, please let us know what you think!

Spending too much time worrying about spreadsheets...
and not enough about your business?