How to set up an efficient spreadsheet design

Category: [Excel] Demo Available

### How to set up an efficient spreadsheet design

**Spreadsheet Formatting **

###

###

**Spreadsheet ****Layout**

###

**Spreadsheet ****Formulae**

###

### Read the text below from the **Microsoft Knowledge Base**

###

**Spreadsheet Speeding up Re-calculations**

###

**Spreadsheet ****Array Formulas**

###

**Spreadsheet UDFs or Custom Functions**

###

**Spreadsheet Volatile Functions **

###

**Spreadsheet ****Lookup Functions**

###

**Microsoft's Tips For Optimizing Speed**

# Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

### Gallery

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

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!**

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. *

See also: Excel Ranges

You can use **Edit>Go to-Special-Last cell** to try and find the last cell in the active sheet, but it is **not very reliable**. The reasons are two-fold:

1. **The last cell is only re-set when you save**. This means if you enter any number or text in say, cell A10 and A20 of a new Worksheet, then delete the content of A20, the **Edit>Go to-Special-Last cell** will keep taking you to A20, until you save.

2. **It picks up cell formatting**. Let's say you enter any text or number in cell A10 and then enter a **valid date** in cell A20 of a new Worksheet. Now delete the date in cell A20 and save. The **Edit>Go to-Special-Last cell** will still take you to A20. This is because entering a date in A20 has caused Excel to **automatically** format the cell from "**General**" to a Date format. To stop from going to A20 you will have to use **Edit>Clear>All** and then save. **So when using VBA you cannot rely on:**

Range("A1").SpecialCells(xlCellTypeLastCell).Select

Below is a method that will find the REAL last used row**Find the last used Row on a Worksheet: **

Sub FindLastRow()Dim LastRow As Long If WorksheetFunction.CountA(Cells) > 0 Then 'Search for any entry, by searching backwards by Rows. LastRow = Cells.Find(What:="*", After:=[A1], _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row MsgBox LastRow End IfEnd Sub

Another common mistake with cell formatting is the changing of alignment of cell data. By default, **numbers are right** aligned and **text is left aligned, **leave it this way! If you Start changing this, you will not be able to tell at a glance if the contents of a cell is text or numeric. It is very common for people to reference cells, that look like numbers, when in reality they are text. If you have altered the default alignment, you will be left scratching your head. *Perhaps the exception to this rule is headings. *

Merged cells are (in my opinion) to be avoided. The Microsoft Knowledge base is full of frequently encountered problems with merged cells. As a good alternative use "**Center across selection**" found on the **Alignment** tab of the ** Format cells** dialog under "**Horizontal**".

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

Excel*Flat File database.***IS**a flat File Application and should be used as such.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.*Less Worksheets/Workbooks is more and makes analyzing/reporting a*__LOT easier.__- All Layouts SHOULD contain 1 single Worksheet in a table format of
**RAW DATA**. From this**raw data**comes your**FINAL data**. . This will help Excel recognize them as headings when you use one of its functions eg;*When putting in headings bold the font***Sort**.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*When putting data into the data area of your spreadsheet try to avoid blank rows and columns.***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.. 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.*Have your data sorted whenever possible*. 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 "*Use real dates for headings and format them appropriately***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.. 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.*Don't put in one cell what could go in more than one cell*. Remember that Excel has far more rows than columns. Excel expects your data to be laid out this way.*Place your headings across columns and your data in rows directly underneath*

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.

See also: Excel Formula Errors.

This is the biggest part of any spreadsheet! Without them you really only have a document. Excel has over 300 built in Functions (with all add-ins installed), but chances are you will only use a handful of these.

**The number one mistake** made in regards to formulae in Excel is the referencing of **entire columns**, this is a big mistake! This forces Excel to look through potentially thousands, if not millions, of cells which it need not be concerned with at all. *Although later versions of Excel are getting smarter at detecting 'dirty cells'*. Assume, for example you have a table of data ranging from cell **A1** down to **H1000**. You may decide that you want to use one or more of Excel's look-up formulas to extract the required information. As your table may be growing, as new data is added, it is common to reference the entire table incorporating all rows. In other words, your reference may look something like "**A:H**", or possibly **A1:H65536**. The reason this is done is so that when new data is added to the table it will automatically be referenced in the formulas. This is a **very bad habit** to form and should be avoided in most cases. This obviously poses the problem that if you do not do this, you will be constantly needing to update your formula references to incorporate the new data as it is added to the table.

Possibly one of the very best ways to overcome this is to familiarize yourself with the use of dynamic named ranges. If you are not familiar with these, check them out here: **Dynamic Ranges ***Please note that for the purpose of being totally generic, the examples of dynamic ranges reference entire columns, there is most likely no need for this in your own spreadsheet*. *I often play it safe and reference about 300 more rows than I think I will need.*

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, i**t 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 "

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.

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.

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.

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.

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

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.

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**

**Excel Trader Package **Technical Analysis in Excel With $139.00 of **FREE software!**

Microsoft Â® and Microsoft Excel Â® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

**Some of our more popular products are below...**

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.