Leading the way in Microsoft Office Development

A few facts about formulas

A formula cannot include more than 1,024 characters.

If you have formulas linking to a workbook and when this workbook is open you press (File > SaveAs) to create a backup, you will automatically change the link formulas to refer to this new file.

It is possible to enter fractions but always precede with a space to ensure that Excel does not interpret it as a date.

When entering your formulas you can press F3 when you are in the formula bar to quickly insert a named range.

To quickly copy a formula down of a cell down that have data in either of its surrounding columns just double click on the fill handle in the bottom right corner of the cell. ???

If you have really complicated formulas you may find it useful to edit the formula directly in the cell. Press F2.

What does the "(Calculate)" mean in the status bar ??

Copying and Pasting

A quick way to copy a large number of formulas that contain relative references without the references changing is to (Edit > Replace) the "=" with "#" before copying and pasting and then (Edit > Paste) the "#" with "=".

You can mess up your links by renaming the source workbook when the dependent workbook is not open.

You can easily create link formulas that refer to cells in other workbooks. If the workbook name in the reference included one or more space you must enclose it (and the worksheet name) in single quotation marks.

Evaluating parts of your formulas

You can use the F9 key to evaluate parts of your formulas. Highlight the portion of the formula that you want to resolve and press the F9 key.

Always press the ESC key afterwards. Be careful not to press the Enter key as this will result in your formula being permanently changed. This can be used to see the values that a range is actually returning.

Format your Formulas

It is possible to enter extra spaces and carriage returns in your formulas to make them easier to read.

You can enter a carriage return by pressing (Alt + Enter).

Formula Auto correct

Excel identifies and suggests corrections for 15 of the most common formula errors. If an incorrect formula has been entered, then a message box will appear with the option to accept or cancel the correction.

You can construct formulas using natural references as well as using named ranges.

Printing your formulas

(Ctrl + "~") - You can toggle between displaying the values and formulas by pressing

Alternatively you could press (Tools > Options)(View, Formulas)

Debugging Formulas

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.

Highlighting all cells that contain a formula using Conditional Formatting

This method uses the XLM language which is really old ??

Enter the formula =GET.CELL(48,INDIRECT("rc",FALSE) in the reference field of the Define Name and call the named range "FormulaInCell"

This can then be used as a condition in the Conditional Formatting

Fixing formula problems

Whenever you insert or move rows and columns at the edge of cell ranges referred to by formulas, the formulas are adjusted automatically.

To help you identify these problems small triangular indicators will appear in the upper left corner of a cell if Excel thinks they may be a problem. When you select the cell a smart tag will appear to display the “Formula Omits Adjacent Cells” menu.

Solving a set of simultaneous equations

Start with equations that are linearly independent so there is a solution

17 = 5x + 3y + 2z

13 = 2x + 4y + z

22 = 3x + 2Y + 5z

Put the coefficients of the unknowns in, e.g. “A1:C3” (i.e. 5,3,2 in A1:C1; 2,4,1 in A2:C2 and 3,2,5 in A3:C3)

Put the constants (17,13,22) in D1:D3

Highlight E1:E3 and enter “=MMULT(MINVERSE(A1:C3,D1:D3)” as an array (press Ctrl + Shift + Enter)

and the solution vector (1,2,3) will appear in E1:E3 meaning x=1, y=2, z=3

If the set of equations does not have a solution then “#VALUE” will appear

This formula returns an array of only the unique items from an expanding column

list “=IF(T(OFFSET(TheList,sArray,,1))=””,N(OFFSET(TheList,sArray,,1)),T(OFFSET(TheList,sArray,,1)) )”

where TheList = OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),)

and sArray = SMALL(IF(MATCH(TheList,TheList,0)=ROW(TheList),ROW(TheList),””),ROW(INDIRECT(!1:”&SUM(N(MATCH(TheList,TheList,0)=ROW(TheList))))))-1”

Note that this is too slow for arrays > 1000 items

Shortcut Keys

(Ctrl + ~) - You can toggle between displaying the values and formulas by pressing

(F2, F9) - Pastes a formula as values.

(Ctrl + Home) - Moves to the beginning of a formula when you are editing it.

(Ctrl + End) - Moves to the end of a formula when you are editing it.