How errors happen in Microsoft Excel

In order to avoid spreadsheet errors such as calculation & Vlookup errors, you should remember the following points (alternatively go straight to the TRAINING page to see how to use all Excel's features):

Relative and absolute referencing (e.g. $A1) can cause problems when you copy and paste. Think carefully about cells you may copy (view a video clip on how copy paste causes spreadsheet errors)
Inserting cells, rows or columns close to the border of a calculation could result in the cells not being included. Explicitly check that the cells are included (view video clips on inserting row errors and inserting cells errors) to avoid vlookup errors and other errors

A simple procedure like deleting cells can cause problems in subsequent cells. Again explicitly check that the formulae around the deleted cell are still accurate (view video clip on spreadsheet errors caused by deleting cells)
Hiding rows or columns may make your spreadsheet prettier but could cause problems in calculations. It is extremely easy to accidentally delete or alter the cells that are hidden without even knowing ( dragging a formulae down can cause this). View the video clip on how hidden cells can cause spreadsheet errors.

One of the most common sources of spreadsheet errors and vlookup errors is as a result of the linking of spreadsheets. Unless all the linked spreadsheets are open at the same time, structural changes in the spreadsheets will cause havoc. Don’t believe us. Look at this video clip of errors due to linked spreadsheets.
The IF functions in Excel is brilliant. Just be sure you know that a little space can cause a major problem. How IF errors occur viewable here.
VLOOKUP is often used in both operational and decision models. But some of the biggest spreadsheet horror stories can be ascribed to this Vlookup function. Make sure you view the VLOOKUP errors clip and avoid making this error.
Hardcoding (putting numbers into a formulae such as $B10+100 ) makes it difficult to understand a model without looking into every cell. Remember most spreadsheets end up being printed out and it is impossible to notice a hardcoded number. Rather let formulae consist of references to cells and have separate input cells.

If at all possible avoid using circular referencing.

To learn more about using Excel safely, why not look at our FREE Training pages or find out about Spreadsheet Professional, the worlds best spreadsheet review and auditing tool!