Spreadsheet Comprehension through Visualization

Book + Visualization Software: Easy Steps to Design & Check Your Excel Spreadsheets

Easy Steps to Design & Check Your Excel Spreadsheets, by Chan Hock Chuan, Federal Publications, 2001, ISBN 983-58-0630-6. 

Available online at MPH Bookstores http://www.mph.com.my 

 

Why do we need visualization tools to understand spreadsheets?

1) It is well documented that spreadsheet models in actual use do contain many errors.

2) Errors can be very serious and some had led to massive losses. 

3) Although spreadsheet models are relatively easy to develop, they are also very difficult to understand or to debug. 

4) People have difficulties in understanding spreadsheet models, basically because the screen displays (both values and formulas) do not show the cell connections well.

5) Visualization tools help to make the connections clear, and help us understand spreadsheets.

Who needs visualization tools?

Practically everybody who uses spreadsheets can benefit from the visualization tools. If you have to understand a spreadsheet bigger than 5 columns and 20 rows, you will find the visualization tools a great help. If you are trying to understand someone else's spreadsheet, the visualization tools will be a great help.

For example, how can we check the accuracy of the following small spreadsheet model?

One elementary method is to take out our pocket calculator.

Another is to turn on the "show formula" option in the spreadsheet, to get the following display. Now, we can examine the formulas slowly one by one. Trace A2, B2, A3, B3, ... This is quite tedious, particularly if the spreadsheet model is bigger, say 40 rows and 10 columns.

Modern visualization tools can reduce eye (and mental) fatigue. With the special spreadsheet visualization software, you can get a picture like this:

This picture shows the relationships among cells. Notice the only slanting arrow? This helps to identify errors.

Or you can get a picture like this:

The light gray cells contain data. The dark gray cells are outputs. And the pink cell is totally unrelated to any other cells! This is an indication of possible error.

The visualization software has many other features, such as identifying components of the spreadsheet model, identifying inputs, outputs and processing cells, and checking for top-down, left-right design.

How do I use the visualization tools?

The tools come in an add-in package, that can be easily installed onto any Excel 97 or Excel 2000 system. A set of buttons will appear, and usage is as easy as clicking the mouse.

Where can I get this software?

The software comes on a CD, together with a book providing details and illustrations of how to use the software (Easy Steps to Design & Check Your Excel Spreadsheets, by Chan Hock Chuan, Federal Publications, 2001, ISBN 983-58-0630-6 )