Leading the way in Microsoft Office Development

Linking Cells

Lets suppose that you have a list of column headings above a table and that you want to repeat the column headings on various rows on your worksheet.

The first column headings in row 2 are just entered as text (as you would normally).

The cells you want linked, in this case a set of column headings is entered as an array formula.

Highlight the cells "B5:E5" and enter the formula "=B2:E2" in the formula bar pressing (Ctrl + Shift + Enter).

You can quickly create an array constant from a linked array by editing the array block and pressing F9.

Removing Intermediate Formulas

Using array formulas can remove the need for intermediate formulas.

Lets suppose that we are keeping a record of the amount we spend every day.

The formulas in column "E" calculate the difference between the amount earned and the amount spent.

The formula in cell "E3" is just "=D3-C3" and then copied down to cell "E13" and the formula in cell "E15" calculates the average from these values.

You can remove the whole of column "E" from the calculation by using an array formula. This combines calculating the difference and the average all in a single formula.

The formula in cell "E16" first calculates the difference and then returns an array of differences to the AVERAGE function.

Comparing Two Tables

Lets suppose you have two tables of information and you want to compare the values to see how many match.

The formula in cell "E13" loops through all the cells in the range and compares each element using the IF function.

The results from this are then passed as an array to the SUM function.

Both the ranges must contain the same number of elements, otherwise an error will be returned.

Performing Calculations with Multiple Criteria

Lets suppose you have a table of information and you want to obtain some summary information.

Things to Remember

Array formulas can be used to perform calculations that would otherwise be impossible.

Array formulas help to protect your formulas as you cannot change, clear or move any individual cells that are part of an array block. The only way to change the individual cells to treat the whole block as a single unit and change the whole block at the same time.

You can only delete an array formula by selecting the whole array block first. You cannot delete part of it.

Using an array formula will almost always be quicker than defining a custom function using VBA.