So you've inherited a really large spreadsheet from some plonker who never liked, but has now left the company without leaving any documentation.

The spreadsheet is now broken and you have been tasked to fix it. There are massive functions:

Where do you start?!

Well fortunately help is at hand.

Step1:
Go: Insert=>function (click on the offending cell, and go: ALT then I then F)

Step2:
Well, there's nothing particularly new there, but here's the cunning part.
You can now drill into the function call to debug it by going into the formula bar and clicking on one of the imbedded functions, like so:

Aha.. now you can see more.

Step3:
It gets better.. You can drill down even further to the most imbed function, and work backwards.

There are more ways to debug large function calls, and I will go over these in later "Tip of the day" posts.

Nick

Training Video on Debugging a large function call:

One of the most useful tips I have is on how to easily display the arguments of a function.

To demonstrate this:

Step1
In cell A1, type: =vlookup (don't press ENTER)

Step2
Press CTRL + SHIFT + A

- hey presto, the function arguments appear.

Training Video on how to display the function arguments:

Autofitting your selection is an extremely useful piece of functionality.

Lets say that you have a large piece of text in the first row, but then very small text in subsequent rows.

You can save valuable screen real estate by autofitting the selection.

Here's how much screen space you use when you autofit the column:

Now, select everything apart from the first cell... then press in sequence:
"ALT" then "O" then "C" then "A"
Now look how much less screen space you use when you autofit the selection:

Training Video on how to Autofit your Selection:

A neat trick that most people don't know (even some professed experts) is a quick way of pasting values.

Step1:
Type anything in cell A1

Step2:
Right click on the right hand side of selection, and drag slightly to the right

A Hidden menu will appear:

Training Video on easily Pasting Values:

You can easily create a list of numbers by:

Step1:
In cell A1, type: 1

Step2:
In cell A2, type: 2

Step3:
Select the range A1 to A2

Step4:
Click and drag down to A10 the square in the bottom right of your selection

.. you can drag this as far as you want, and it will keep on incrementing the number

Video Training on creating numbers 1 to 10:

This example will show you how to calculate the number of days between now and christmas.

Step1:
In a cell A1 in a new workbook, press: CTRL + ;
That will put today's date in the cell

Step2:
In cell A2 type: 25/dec
Excel will correctly guess the year

Step3:
In cell A3 type: =A2-A1

Step4:
Press CTRL + SHIFT + # to format the cell as a number

So from today, there are 6 days until Christmas

Example File

Festive greetings !

Nick