Converting Spreadsheets from Microsoft Excel

Introduction

Don't just import your existing spreadsheets, convert them.

Once you have Resolver One up and running, it's easy to import your Microsoft Excel spreadsheets; just select File > Import From Excel (With Formulae):

Resolver One supports most MS Excel features, so your existing documents should work immediately. However, if you stop there, then you're missing the full power of Resolver One.

This tutorial is about not merely importing, but converting your existing spreadsheets, making them easier to read and maintain, and less prone to errors. It covers common difficulties and demonstrates key ways Resolver One can enhance your spreadsheets.

If you get stuck, feel free to ask on our support forums.

Download and open the example spreadsheet

For this tutorial, you'll need the sample spreadsheet from the Resolver Exchange:

1. Click to download Excel Examples

2. Open the tutorial.xls in MS Excel or a compatible spreadsheet and take a look.

   The spreadsheet uses (strictly for illustrative purposes) formulae to calculate tax for a list of employees. The first worksheet lists employee reference numbers and departments. The second (shown below) adds pay grade and tax code information. With the help of some dynamic lookups it calculates how much tax they pay:

   

   The first worksheet makes extensive use of VLOOKUP and INDEX and MATCH functions. Unless you know exactly what these do, the formulae are hard to understand. Worse, they contain several hardcoded cell range references—these may break when you add more data.

3. Close Excel and open Resolver One.

Moving this spreadsheet to Resolver One lets us simplify it. This makes it easier to understand and maintain, and makes it less likely that errors will be introduced when new employees are added.

Import from Excel

The first step in converting an Excel spreadsheet to Resolver One is to import it. The File menu has options to import from Excel, with or without formulae. In our case we want to import with formulae.

1. Select File > Import From Excel (With Formulae).

2. Navigate to the file tutorial.xls and click Open.

   The imported spreadsheet looks very similar to the original:

   

So far we have the spreadsheet loaded and working as if in MS Excel. Now it's time to enhance it using the full power of Resolver One.

Column Level Formulae and Headers

At the heart of the example spreadsheet is the big table in the 'Employee Data' worksheet. It has one row per employee, like this:

Formulae supply the values in the 'Dept' and 'Tax Paid' columns. When you add a new employee, you have to remember to paste the formulae into the right cells—rather easy to forget. There are several steps towards fixing this.

Use the Output Pane to Debug

The red crosses in cells (see the image above) indicate the location of any errors. Hovering the mouse pointer over the cell tells you about the particular error. Usually this is a function that is missing because it was either not compatible with Resolver One or involved VBA code—both easily resolved.

The quickest way to locate errors is to work through the messages in the output pane at the bottom of the user interface. In addition to print statements, this shows error messages as full Python tracebacks giving you the problem line number or cell reference:

Use the pane to rapidly debug even the most complex spreadsheets comprising multiple worksheets. As you fix issues, the number of tracebacks should decrease until you are error free.

Using Worksheet Indexing to replace Index Match

The first worksheet lets you enter an employee name in cell E3 and see how much tax they paid in cell E4.

The formula in E4 uses common Excel functions MATCH to find the required row, and INDEX to index a specific column in that row: =INDEX('Employee Data'!A1:F22,MATCH(E3,'Employee Data'!B1:B22,False),6). There is a simpler way, using Resolver One's header indexing.

The INDEX and MATCH functions will be available soon, but you're almost always better using Resolver One's header indexing.

1. Select cell E3.

2. Select Data > Edit Names to open the Edit Names window.

3. Name the range "employee" and click OK.

4. Turn the Name column into a header column.

5. Replace the formula in cell E4 with =<'Employee Data'>['Tax Paid', employee].

   This isn't just shorter, it's also easier to understand and doesn't hardcode any cell or range locations.

Conditional Formatting from User Code

Excel conditional formatting doesn't import into Resolver One, but it's easy to add with a simple bit of Python in the Post-formulae user code section.

The original Excel spreadsheet uses conditional formatting to highlight employees who pay more than $15,000 in tax.

1. In the Post-formulae user code section, enter the following Python code:

for row in workbook['Employee Data'].ContentRows:
    if row['Tax Paid'] > 15000:
        row.Cells['Tax Paid'].BackColor = Color.LightPink

2. To apply the changes, click outside the coding pane, or press F9.

   The effect is identical to the conditional formatting applied in the Excel spreadsheet.

Further Improvements

We've now converted the example spreadsheet from Excel to Resolver One. However, there's still room for improvement:

• How about some pre-formulae user code to automatically copy the reference numbers from the 'Employees' sheet into 'Employee Data'? That way the reference numbers only need be entered once.
• Better still, the data could be entered and stored in a single master workbook and accessed using RunWorkbook. This would enable reuse by other workbooks.
• It would also be useful to have some validation user code to raise errors if data is missing.

Implementing all this is left as an exercise for the reader. If you come up with something elegant, we'd love you to submit it to the Resolver Exchange.