Nick's Excel Tip Of The Day - Part 2

Nick's picture

44. Excel Tip Of The Day - Enter Array Formula

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:

Enter-array-formula-excel 

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

Enter-array-formula-excel 

... 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:

Nick's picture

43. Excel Tip Of The Day - Function debugging - using F9


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 ?: debug-large-function-excel

 

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

 

debug-large-function-excel 

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.

debug-large-function-excel

 

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

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

Training Video - Function Debugging

Nick's picture

42. Excel Tip Of The Day - Deleting Sheets

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:

Nick's picture

41. Excel Tip Of The Day - Data Validation for percentages

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.

data-validation-percentage 

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

 

Training Video - Data Validation for percentages