How to be More Productive with Excel

The first MS-DOS spreadsheets, VisiCalc and Lotus 1-2-3, introduced the ability to fill in formulae using the Fill Down and Fill Left/Right functions. A formula could be entered once and then copied across a row or down the column. This was at the heart of the productivity benefits of spreadsheets. In essence, it offered a very simple way to program a personal computer.

Even with this most simple facility, spreadsheets could do a lot of the work for users. From my early days of working with spreadsheets, I coined the principle of constructive laziness: "Never do yourself what the computer can for you." In those early days, many people constructing spreadsheets did not take full advantage of this tool, because they saw them as electronic ledgers, and failed to understand the really important principle that only when we change the way we work do we see the true benefits of using technology. The benefits of letting Excel do the work can be not just less work for you, but less errors, too, providing twin ways to be more productive with Excel.

click to enlarge

Excel was available for Windows at its launch in 1987. It developed the original spreadsheet features to enhance their usability and taking advantage of the new mouse-based interface.

The first major enhancement was the AutoFill function. In early versions of Excel, the DOS text-based menus were replaced by Windows menus. However, a small black square at the bottom right hand corner appeared in later versions (version 5 in 1995, from memory) which provided the ability to copy formulae across rows and down columns with a single mouse action. Another very useful innovation was the Autofill button which appeared on the toolbar, based on the analysis that the single most common action carried out was to add up a column of figures.

More recent useful innovations are the automatic validation and correction of formulae, which saves a huge amount of time debugging spreadsheets.

These features are enhanced by the library of in-built functions, which have been part of spreadsheets since the original applications. Excel helps you complete them by providing instant documentation about the required parameters. The Autosum function will even look and try to find the figures you are trying to add up.

The first and most obvious benefits are in productivity. Time savings are achieved directly by enabling formulae to be more quickly, but equally significantly, by reducing errors in entering formulae, and automatically correcting errors when they are made. Errors in complex spreadsheets can be difficult to identify, and can have multiple consequences as they lead to further errors in dependent calculations.

This leads to the second type of benefit: a reduction in errors caused by mistyping. The consequences produce two types of errant results. Errors may produce values which are not credible, where the values are clearly impossible: 45 June 1997 for example. Alternatively, the resulting value may be perfectly credible, but equally incorrect. These are much harder to spot, so anything which reduces the chance of them occurring in the first place is welcome.

So just for once, laziness can be the new productivity: Let the computer take the strain, and Excel write (and check) your formulae for you.