How to set up an efficient spreadsheet design

Excel is without doubt a very powerful spreadsheet application and arguably the best in the world. However, many people often design their spreadsheets with no foresight at all.

This means most spreadsheets have poor foundations and have limited life spans. Perhaps the number one rule when designing a spreadsheet is, we should start with the end in mind and never assume you will not need to add more data or formulae to your spreadsheet, because, the chances are that you will. A good spreadsheet should have about 80% planning and 20% implementation.

While this can seem extremely inefficient in the short run, I can assure you that the long term gain will far outweigh the short-term pain. Remember that spreadsheets are about giving correct information to the user, not possible erroneous information that looks good. Let's look at how a spreadsheet should be set up efficiently.

Rows (Shame on Microsoft)
Formatting (The least important Aspect)
Layout (THE most important Aspect)
Formulae (This is what it is all about)
Speeding up Re-calculations (how to, do's & dont's)
Array Formulas (what so called formulae gurus Wont Tell You)
UDFs or Custom Functions (Rarely required)
Volatile Functions (Avoid these)
Lookup Functions (How to speed up)
Microsoft's Tips For Optimizing Speed (from the horses mouth)
Outside Links

The very 1st thing you SHOULD know about Excel is that ALL versions contain far more rows than the Application can ever hope to handle. Why Microsoft continue to mislead its users by supplying far more rows than it can handle remains a mystery. My best guess is that, like most car speedometers, the upper range exceeds the cars capabilities.

Even with newer versions, the expectations far exceed the Applications capabilities. In fact, the problems (misleading of its users) gets worse. For Example, Excel 2007 now has 1 million rows and if you fill just 1 column using all rows, save a close, you will very likely never be able to open the Workbook again. BUYER BEWARE AND DON'T FORGET ABOUT MS ACCESS!

Spreadsheet Formatting

While a spreadsheet should be easy to read and follow, this should rarely be at the expense of efficiency. I myself am a big believer in 'keep it simple stupid' (K.I.S.S). Far too many people spend about 30%, or more, of their time formatting their spreadsheets. This time (although they don't realize it) often comes at the expense of efficiency and accuracy. Often the overuse of formatting adds size to your Workbook and while it may look like a work of art to you, it may look terrible to another. Some very good universal colors are black, white and grey.

Only ever use the cell format of Text if really necessary. This is because all data entered into the cell becomes text and spreadsheets are all about numbers. Worse still, is any cell housing a formula, that is referencing a Text formatted cell, will also become formatted as Text. Believe me, you do not want formula cells to be formatted as Text! This format is VERY rarely needed, yet frequently used.

If you apply a number format to specific cells try to not apply the format to the entire column. If you do, Excel will assume you are using these cells. This can give unexpected results when locating the last used cell. With 65536 (1 Million in 2007) rows in Excel it wont take too long before you had told Excel that you are using millions of cells, when in reality the number is only in the thousands! Having said not to format entire columns & rows, it should be noted that intermittent & sporadic formatting to individual cells can increase file size.

If you want your formatting to automatically apply to new data, either use the "Format Painter" (paint brush icon on the formatting toolbar). Or go to Tools>Options | Edit and check "Extend list formats and formulas" (checked by default) this will format new items added to the end of a list to match the format of the rest of the list. Formulas that are repeated in every row are also copied. To be extended, formats and formulas must appear in at least three of the five last rows preceding the new row.

Spreadsheet Layout

When setting out you data, follow these golden rules as closely as possible.

Flat File database. Excel IS a flat File Application and should be used as such.
Less Worksheets/Workbooks is more and makes analyzing/reporting a LOT easier. Try and ensure all related raw data is on 1 Worksheet and in 1 workbook. The number 1 issue with excel spreadsheets is users spreading raw data over many worksheets and/or workbooks.
All Layouts SHOULD contain 1 single Worksheet in a table format of RAW DATA. From this raw data comes your FINAL data.
When putting in headings bold the font. This will help Excel recognize them as headings when you use one of its functions eg; Sort.
When putting data into the data area of your spreadsheet try to avoid blank rows and columns. This is because a lot of Excels built-in features will assume a blank row or column is the end of your data. It can also helps with a lot of Excels formulas. Having said this though, it is a good idea to leave at least 4 blank rows above your headings (hide them if you wish), these can then be used for keeping Totals (much better than being at the bottom) and criteria for some of Excels built in features, eg Advanced Filters.
Have your data sorted whenever possible. Excel is very rich in what is known as Lookup & Reference formulas and many of these may rely on your data being sorted in a logical order. It will also greatly speed the calculation process of many functions.
Use real dates for headings and format them appropriately. By this I mean if you want the names of the months as headings type them in as 1/1/2001 , 1/2/2001 , 1/3/2001 etc then format them as "mmmm". This is a very simple procedure that is all too often overlooked by many. If you have real dates as headings life will be much easier further down the road if you need to use them in formulas, if you don't nothing is lost.
Don't put in one cell what could go in more than one cell. Say you have the names of 100 people to put into your spreadsheet, donâ€™t put their full name in one cell. Instead, put the First name in one cell and their surname in the next cell to the right. If you need to place them into one single cell at a later stage, this can be done very easily - the same cannot always be said for the reverse.
Place your headings across columns and your data in rows directly underneath. Remember that Excel has far more rows than columns. Excel expects your data to be laid out this way.

If you can stick to these guidelines you will thank yourself later as you will then easily be able to use many of Excels powerful built in features, which often need the data laid out this way.

Spreadsheet Formulae

Read the text below from the Microsoft Knowledge Base

Calculate Message Remains in Status Bar If 65,536 Formula References

Microsoft Excel calculates during different events like column auto-resizing, when entering anything in any cell, and so forth. However, Excel can only track 65,536 dependencies to unique references for automatic calculation.

After the workbook has passed this limit, Excel no longer attempts to recalculate only changed cells. Instead, it recalculates all cells after each change. This behaviour is by design of Microsoft Excel.

Microsoft Excel will correctly calculate all formulas. However, the "Calculate" message in the status bar indicates that Microsoft Excel can no longer track the formula dependencies and is calculating every formula in the workbook after each change. If you are unsure when the last calculation event took place and you want to be sure your formulas are up to date, simply Start a calculation manually. To do this, press CTRL+ALT+F9 and wait until "Calculating Cells: n%" disappears and "Calculate" returns to the status bar.

Spreadsheet Speeding up Re-calculations

A common problem with poorly designed spreadsheets is that they become painfully slow in recalculating. Many people will suggest that a solution to this problem is putting a calculation into Manual via Tools>Options>Calculations. This is generally very poor advice and fraught with potential disasters. A spreadsheet is all about formulas and calculations and the results that they produce. If you are running a spreadsheet in manual calculation mode, sooner or later you will read some information off your spreadsheet which will not have been updated. In other words your formulas may well be returning old values and not the updated values. This is because when you go into manual calculation you must force Excel to recalculate by pushing the F9 key. As you can imagine, it is very easy to overlook doing this. I liken it to a case where your car brakes are rubbing and slowing down you car. Rather than fix the problem you disconnect the brakes and rely on the handbrake. Most of us wouldn't dream of doing this, but most don't hesitate to put their spreadsheet into manual calculation. The bottom line here is, if you need to run your spreadsheet in manual calculation, you have a design problem. Address it properly and do not apply a 'band-aid' approach.

Below is a list of what is often the worst offenders for slowing down recalculations of spreadsheets.

Array formulas
Sumproduct used for multiple condition summing or counting
UDFs (User-defined functions)
Volatile functions
Lookup Functions

Let's look at each of these now in turn, and see what alternatives we can have for them.

Spreadsheet Array Formulas

A possible alternative for array formulas, are Excel's database functions . The Excel help has some very good examples on how these formulas can be used on large tables of data and are able to return results based on multiple criteria. Another alternative which is too often overlooked is the use of Excel's Pivot table feature. While these may seem very daunting when first encountered, I highly recommend that you familiarize yourself with this powerful Excel feature as once you master them, you will wonder how you survived without them!

Perhaps the biggest problem with array formulae is that they look efficient, but when compared to an alternative, nothing could be further from the truth! An array formulae must follow rules that Excels built in Functions do not have to, that is they must loop through each and every cell they reference (one at a time) and check them off against a criteria. For this reason arrays are best suited to being used on single cells or referencing only small ranges.

Spreadsheet UDFs or Custom Functions

UDFs for those of you that are not aware, are User-defined Functions that are written with Excel VBA which can then be used in a Worksheet in the same way as any one of Excel's built-in worksheet functions. Unfortunately, no matter how good the person is at VBA who has written the UDF, it is very unlikely that it will perform at the same speed as one of Excel's built-in functions, even if it would be necessary to use several nested functions to get the same result. Often a well written and efficient UDF function will incorporate the use of Excel's worksheet functions. By this, I mean you may decide to write a UDF to replace a function which otherwise would require you to use several worksheet functions nested within each other. The most efficient way to replace the use of several nested worksheet functions (if you must) would be to write a UDF that would incorporate the use of all of the worksheet functions necessary. This way instead of having to repeatedly nest the functions on the Worksheet to get the desired result, you could nest the functions once within VBA (by accessing the WorksheetFunction Object) which would then allow you to use this function in the interface, without the need to repeat the nesting. Most of Excels Worksheet Functions can be used in VBA simply by using: WorksheetFunction. or Application. if the one you need is not available, there will be VB equivalent.

Spreadsheet Volatile Functions

Volatile functions are simple functions that will recalculate each time a change of data occurs in any cell on any worksheet. Most functions (non Volatile) will only recalculate if a cell that they are referencing has changed. Some of the most common volatile functions used are undoubtedly the NOW() and TODAY() functions, there are also OFFSET(), CELL(), INDIRECT(), ROWS(), COLUMNS() to name a few more. If you are going to be using the result of these functions frequently throughout your spreadsheet, avoid the temptation of nesting these functions within other functions to get your desired result (especially array formulae and UDF's). Instead, simply type the volatile function into a single cell on your spreadsheet and reference that cell from within other functions. This alone can potentially cut down on the amount of volatile functions by hundreds, if not thousands.

Spreadsheet Lookup Functions

Excel is very rich in Lookup & Reference Formulae, with the most popular probably being VLOOKUP. These functions are all very generic and can be used to extract data from just about any table of data. The biggest mistake made by most, is the forcing of Excel to look in thousands, if not millions of cells superfluously, see: Formulae . As shown by the Formulae link, one of the best ways to overcome this is via the use of Dynamic Ranges .

The other mistake is that the lookup functions (in the case of VLOOKUP, HLOOKUP and MATCH) are told to find an exact match. That is, the optional fourth argument Range_lookup is set to False in both VLOOKUP and HLOOKUP. In the case of MATCH the last optional argument (Match_type) is set to 0 (zero). This means that Excel will need to check all cells until it finds an exact match. If possible, always use True (or omitted) for VLOOKUP and HLOOKUP, or 1 (sort ascending) or -1 (sort descending) in the case of Match. So, whenever possible, sort your data appropriately. Sorting the lookup columns is the single best way to speed up lookup functions.

Another very bad mistake is the double use of the Lookup Function nested within one of Excels Information functions. see example below

=IF(ISNA(VLOOKUP(100,MyRange,2,False)),"",VLOOKUP(100,MyRange,2,False))

This is used to prevent the #N/A when no match can be found. The big problem with this is, it forces Excel to use the VLOOKUP twice! As you can imagine, this doubles the number of Lookup functions used. The best approach (if possible) is to live with the #N/A, or hide it via Conditional Formatting . Or, if this is not an option, place the Lookup in a 'out-the-way' spot on the same Worksheet (eg IV1) and then use:IV1=VLOOKUP(100,MyRange,2,False) Result Cell = IF(ISNA(IV1),"",IV1). This halves the number of Lookup functions needed!

One other common problem is storing the Lookup Function on another sheet to the Table. While the effect of this is not too bad on approximate matches, it can be dramatic on exact matches. Consider placing the Lookup functions on the same Worksheet as the Table, then create a simply reference (eg =Sheet1!IV1) to the cell(s) to get the result into the needed Worksheet. Doing this also opens up another opportunity in that we could now use:

IF(ISNA(Sheet1!IV1),"",Sheet1!IV1)

Last, but far from least, learn how to use Excels Database functions . They are very easy to use and are often much faster than their Lookup & Reference counterpart.

Microsoft's Tips For Optimizing Speed

In Microsoft Excel, recalculation performance is affected by the way data and formulas are arranged on the worksheet. The following list contains tips for optimizing your worksheet to improve recalculation speed:

Organize your worksheets vertically. Use only one or two screens of columns, but as many rows as possible. A strict vertical scheme promotes a clearer flow of calculation.
When possible, a formula should refer only to the cells above it. As a result, your calculations should proceed strictly downward, from raw data at the top to final calculations at the bottom.
If your formulas require a large amount of raw data, you might want to move the data to a separate worksheet and link the data to the sheet containing the formulas.
Formulas should be as simple as possible to prevent any unnecessary calculations. If you use constants in a formula, calculate the constants before entering them into the formula, rather than having Microsoft Excel calculate them during each recalculation cycle.
Reduce, or eliminate, the use of data tables in your spreadsheet or set data table calculation to manual.
If you only need a few cells to be recalculated, replace the equal signs (=) of the cells you want to be recalculated. This is only an improvement if you are calculating a very small percentage of the formulas on your worksheet.
When a certain group of formulas must be recalculated a great number of times, then it may be helpful to replace the equal sign (=) in the formulas that you do not need to recalculate with a unique string that does not appear elsewhere. The formulas without the equal signs will not be recalculated (they are no longer considered formulas). When Microsoft Excel has recalculated the formulas that still contain equal signs, search for the unique string and restore the =.

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software