Debugging

If you are checking that formulas are correct, you can create a new window of the same workbook and view the values in one window and the formulas in another window. You can quickly toggle between the values and formulas by pressing ??
If you have a large number of intermediate formulas you can combine them into one large formula. The advantage of this is that recalculation of the spreadsheet is faster.
The quickest way to convert formulas to values is to move the formulas one cell to the right, and then hold down the right mouse button, when you drag them back to the original position. Choose "copy as values" from the shortcut menu ??
If you enter a large formula and it is not correct, press the OK to edit the formula, press HOME to take you to the start of the formula and enter an apostrophe. This will enter your formula as text and allow you to edit it easily


You can examine the components of a large formula by dragging the pointer to highlight part of the formula and pressing the F9 key to evaluate only the highlighted part. Remember to press the ESC key afterwards.
You can quickly select all the cells that contain formulas by choosing (Edit > GoTo > Special) and select formulas ??
The N() worksheet function is a way to include a text description into a cell containing a formula, without it affecting the formula.
You probably won't use the R1C1 notation as your default although it is very useful for checking your copied formulas. Every cell should have the same R1C1 formula.
You can retrieve data from a file without actually opening it (e.g. use the formula "=[File_Name.xls]Sheet1!A1").
You can easily display leading zeros by using a custom number format "000000". This will mean that 6 numbers are entered and any that are not entered will be zero.
You may find it helpful when editing cell references that link to other worksheets to temporarily change the worksheet name to a shorter one. Making changes with a shorter worksheet name is easier and the name can then be changed back afterwards.


Large Formulas

Quite often a formula requires a number of intermediate formulas in order to produce the correct result.
After you have got all your formulas working it is possible to eliminate the intermediate formulas and create one big "mega formula".
Formulas can only contain a maximum of 1024 characters.
If your "Mega formula" is longer than this then you should consider creating a user defined function.



© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

PrevNext