• Home >>

How most Excel problems start.

In the beginning, someone creates an Excel sheet – and it works. Over time, more and more equations are added, more tabs, more complexity and  & well just more of everything. Soon, the developer moves on leaving the Excel solution working, but undocumented and unrefined.

Years later, the company needs to make a change. But no one remembers exactly how the sheet was set-up. There is no documentation and none of the original developers are around.

The team assigned to  fix  or  update  the Excel solution has no way of understanding the formulas  – which often look like this:

Years after it was written, no one knows what this means.

The solution:
The first step is to understand how the current solution is working. A complex equation, like above, can be understood with visual tools. A chart, graph or other visual aid is much simpler to understand than an equation. So I first represent the Excel functions in a PowerPoint deck or a set of charts or graphs so that everyone in the organization can understand how the Excel solution works now. (These graphics can also be used later to document the current Excel solution).

Next, simplify the code.

Most people who use Excel do not use two modern programming techniques. After all, most Excel users are not programmers. These two techniques are Test Driven Development (TDD) and refactoring. TDD means that you write a formula to test and verify the other formulas in the Excel sheet. The advantage of this is that you catch errors in logic before they get into your final Excel solutions.

It is always faster to resolve a bug if you catch it early. But most people only spot check their Excel formulas. As such, a bug can be hidden in the sheet that may produce bad results  – and go unnoticed for some time. These bugs can cost a company millions in quoting or price calculation solutions.

Test Driven Development catches bugs early and speeds up user acceptance testing. It also aids in refactoring. Refactoring is the process of refining code to make it simpler to understand and more efficient. Professional programmers follow this design template. First get the code working, then refine it.

For Example, both of these equations do the same thing, but which is easier to read?
=if(A1=1,  ”Positive” , if(A1=2,  ”Positive” , if(A1>2,  ”Positive” , “Negative” )))
=if(A1>0,  ”Positive” , ”Negative” )

A programmer or Excel user may get the Excel solution to work with the top formula. But a programmer who uses refactoring will go back and simply his/her solution. They will replace the more complicated upper formula with the simpler lower one. Thus making the formula easier to read and modify. He/she will leave the solution in its simplest form. This aids future development or updates.

This is where most Excel users fail  – they leave the formulas in a more complicated form, thus making updates harder.

The programmer can be confident that the simplified formula works because they use TDD. They already have a test that verifies that the complex (upper) formula works. So when they replace it with the simpler formula, they run the test and verify it still works. That is, the results are the same.

A simple test for the above equations would place the numbers {-10, -1, 0, 1, 2, 3, 4, 10} in cell  A1 and then read the result. The expected result should be {Negative, Negative, Negative, Positive, Positive, Positive, Positive, Positive}. This is a simple example and …  you may have noticed that zero (0) resulted in the word  Negative.  And while zero is not a negative number, the test was written to verify that only positive numbers result in the word  Positive.  So the test works for these examples.

But is the test right?

Test Driven Development has an unexpected benefit. It causes the user to look at the correctness of the formula. In the examples above, should we have tested for zero and set the answer to report  Zero ?

We can’t know the answer because we don’t know what the Excel sheet is designed to do. But TDD forces the user to think about the formula and the range of valid responses. Most non-programmer users of Excel just look for a right answer. They often do not think about what erroneous inputs could throw off the formulas. Test Drive Development prevents this while providing a tool for refactoring (simplifying the formulas).

With the code simplified, the next layer is to use the rich VBA code to make the formulas even simpler, while providing a better user interface. Which quoting solution would you like to use?

normal Excel


Or this (my rewrite)

Enhanced Excel

Believe it or not, both of the above are the same Excel solution. The one on top was the ProQuest 2006 price calculator. The bottom is my rewrite. It is the ProQuest 2007 price calculator. If you were a sales person, which would you rather stare at eight hours a day?

Don’t discount the value of a good interface. Have you ever pushed a door that needed to be pulled. You probability felt a little foolish. Actually, it is the designer who should hang his head in shame. A door should be designed so that it communicates what should be done to open it. Doors that need a  Push  or  Pull  sign are poorly designed. A door shouldn’t need a user manual.

The same is true of Excel solutions. Excel offers rich visual features that allow solutions to be designed to help the user be productive, while at the same time are less fatiguing than a row upon row of numbers.

Professional programmers follow this template. Design a great user interface first, then write to code to support that interface. Amateur programmer start with the code or formulas. This is also true of most Excel users. They start with the formulas. And this gets us all the icky Excel sheets. Fortunately, these amateur designs create a high level of dissatisfaction  – which keeps me working.

Once the solution is working, refined and has a user-friendly interface; the next step is to write the documentation. Documentation is easy once the software is working and simplified. Unfortunately many Excel user never document their solutions and this makes it difficult to update or fix bugs that crop up years later.

Documentation is much shorter when the solution: works right, has been refined, and has an intuitive interface. A good interface screen shot will save pages of written documentation. Refactored (simplified) code is easy to document because it has been simplified already. This documentation can now serve to help the next developer. And a clean, intuitive, simple user interface is easy to document as the interface follows the work flow of the user. So the documentation can serve as a training manual. Unlike other manuals which spell out every function with no work flow (and thus need hundreds of pages), a good user interface can be documented with fewer pages.

In the end, I think that most people love Excel because it is easy to create simple solutions. However, they get themselves in trouble when designing large, enterprise level or mission critical applications.

My tips are these:

1) Design the user interface first. This means interviewing the users and business owners to get the requirements. I use User Stories to develop what are known as  Personas.  These are stories that describe a set of users.

2) Write the test for the formulas and calculations first (known as Extreme Programming  Write the test first  or TDD Test Driven Development).

3) Get the code, formulas and calculations working to support the user interface.

4) Once working, refine the code, formulas and calculations.

5) Lastly, write the documentation.

*) If I am rewriting an existing Excel solution, I add a first step. I make a visual representation of how the existing solution works so that everyone can comment on the desired changes, what works and what does not.
I know this all seems backward, but these are modern, professional programming techniques applied to Excel / VBA applications. For more info on these techniques, please see my library list.