This Excel tip explains step by step how to enter an array formula. Why do I need array formulae ? I hear you ask. Well, one of the reasons is that you need them for functions that do not return their results in a single cell. Lets take MINVERSE for example. The following screen shot shows the data we're using:

We have a matrix, how do we get the inverse of the matrix ? For a 2 by 2 matrix, the inverse is also 2 by 2

• Step1: Select Range E3 to F4
• Step2: Press F2
• Step3: Enter the following formula: =MINVERSE(B3:C4)
• Step4: Press- CTRL (and hold it down) SHIFT (and hold it down) ENTER

... you can now let go of the CTRL and SHIFT keys. ; - >

Download a spreadsheet to practice entering array formulae on

Training Video on how to enter an Array Formula:

This is a follow-on from the first tip on debugging a large Excel function call

This Excel tip is really one of the most useful ones I can think of. I use it all the time, so make sure you understand it

Suppose you have a large function call, and you want to understand what is going on.

There's a neat way to reduce the function to smaller pieces to make it easier to understand.

Step1: Lets take a function call from my Buy, do Up and Sell Calculator.

This is the function call... it's massive... what on earth is going on ?:

First, select a complete statement in the formula bar... in this case, select: ROW()-5 Maximum_length_of_project

Step2: Now, press F9 - What this does it to evaluate what you have selected. Step3: Repeat for each element of the formula that you want to evaluate. Now you can see what the result of the function is made up from.

Step3: You can keep on reducing the function call until its down to bare bones

NOTE: When you have finished investigating... Don't forget to press ESCAPE, and NOT ENTER !

Training Video - Function Debugging

Easy tip today... it's about how to delete sheets.

To delete a worksheet:

If you have selected the worksheet, press in sequence: ALT the e then l

• If there is data on the sheet, you will be asked to confirm that you want to delete the sheet.
• If no data, the sheet is deleted immediately.
• To delete multiple sheets, select them one by one whilst holding down the CTRL key.
• Note - you must leave at least one sheet undeleted, so if you want to delete all the sheets, insert a dummy sheet, then proceed as before.

Video Training on Deleting Sheets:

This is a follow-on from tip 1 on Data Validation

When adding data validation to cells containing percentages, there is a little trick you need to know. If you're calculating a proportion: 100% being everything, 0% being nothing, you need to set the data validation to DECIMAL with range 0 to 1.

Why ? - Because in this case the percentage is actually a fraction of 1

Training Video - Data Validation for percentages