This is an Excel tip for those of you who don't like using the mouse, (like me) and prefer to use only the keyboard. This can be a big time saver if you use Excel every day.

Step1: Press the WINDOWS key on your keyboard. This is the key that has the same symbol as your START button:

The start key is normally on the same row as your space bar.

That brings up this menu:

Step2: Press R (to Run a program)

Step3: Type: Excel, and press OK.
NOTE: If you do this once, it will remember what you typed for next time.

Training Video: Launch Excel using the keyboard

This tip is a followon from the previous Excel tip on Calculation

Today, we're going to look at different ways to calculate

There are a few different ways to calculate something in Excel, and you can use each differently.

Q. What do you do if your sheet is not calculating ?
A. The calculation tree works by looking to see if any precedent cells have changed. If the precedent cells have not changed, Excel will not calculate the dependent cells.
If you have a function that builds an object by importing data from a file, it will not calculate unless it's forced to do so. SO.. What do you do if your sheet is not calculating ?... You press CTRL and ALT and F9 TWICE.
CTRL and ALT and F9 forces excel to calculate every cell regardless of whether the precedent cells have changed or not.

Q. How do you calculate selection in Excel ?
A. There's no easy way.. There should be a shortcut combination, but there isn't. The best way to do this is to simply replace = with = and repeat until all cells have been calculated. If none of them are dependent on each other, then you only have to do this once.

Q. How do you calculate Only the active sheet in Excel?
A. This is easy... press SHIFT and F9

Q. How do you calculate the Excel application ?
A. Press F9

Download a spreadsheet to practice different ways to calculate in Excel

Training Video on ways to calculate in Excel:

This tip shows you how to do a random sort of a list in Excel.

Suppose you have a list of people and you need a way to find out who goes first. Maybe you're the HR department of an investment bank and you need to decide quickly who to make redundant.

Here's a screen shot:

Here's the formula you would use:
=INDEX($A$2:$A$11,MATCH(SMALL(COUNTIF($B$2:$B$11,"<"&$B$2:$B$11)
+COUNT($B$2:$B$11),ROW()-ROW($A$2)+1),
COUNTIF($B$2:$B$11,"<"&$B$2:$B$11)+COUNT($B$2:$B$11),0))
.. you need to enter it as an array formula with the array the same size as the list.

Follow this link for an Excel tip on how to modify an array formula.

Download a spreadsheet to practice random sort in Excel

Calculation in Excel is a big topic, but I am going to try to give you the essentials about calculating formulae.

Calculation works by the creation of a calculation tree. This is a map of all cells, their dependents, and their dependents... etc.. To do the fastest calculation, Excel needs to go right to the end of the tree, and calculate those cells first. If on the other hand, Excel were to calculate each cell one by one, it could not guarantee that every cell had calculated correctly.

Lets see how this works... We have 3 cells... Cell1 is dependent on Cell3, and Cell3 is dependent on Cell2. Cell2 is just a number.

The formulae and values look like this:

Now suppose A3 changes to 2:

If we now calculate each cell one by one starting at the top cell, this is what happens:

NOTE: Cell A2 has the wrong value.. It should be 4. The reason it's wrong is that cell A4 should have been calculated first as A2 depends on A4.

The correct calculation should be:

Now Excel could complete this calculation by recalculating each of these cells again. In this case, as there are only 2 dependencies, the calculation would be complete. However, anyone who has designed complex spreadsheets will know that the amount of dependencies can be absolutely HUGE. Hence the most efficient way is for Excel to build a calculation tree, and work backwards.

By building a tree of dependencies, Excel knows to calculate cell A4 first, then A2... hence making the calculation efficient and quick.

Download a spreadsheet to practice calculation in Excel

Here are a couple of useful Time-related tips for Excel.

The first one is the NOW Excel function
Lets see how it works..

Step1:
Enter the following formula in any cell:
=NOW()
For me right now, this returns: 03/02/2009 09:22

NOTE - if the column width is too small, you will see something like this:

All you have to do in this case is to widen the column.
The best way to do this is to select the offending cell, and press in sequence: ALT then O then C then A
- this autofits the column and will now show the time.

Step2:
Format the cell (right click => format cells => custom) to a good format like:
"ddd dd-mmm-yy hh:mm:ss"
For me right now, this returns:
"Tue 03-Feb-09 09:22:17"
NOTE - NOW is accurate to the nearest second.

The NOW() function in Excel is Volatile, which means that it will recalculate when Excel recalculates .
Be careful on using it with too many dependents, as it can cause sluggish calculation speed.

The second part of this tip is that there is a shortcut to put the time directly into a cell..
Press: CTRL and SHIFT and ;
For me right now, this returns: 09:22:00
NOTE - this is the static time for when you press the shortcut keys

Strangely, the TIME function doesn't actually return the time, however you can use it to tell the time by using NOW as an input.

Here's how:
=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
... clearly a better way to do this is to put =NOW() in another cell, and link to that.

Download a spreadsheet to practice time functions in Excel

Training Video: Tell time in Excel

Today's tip is a short one, but one of the most used in finance - Toggling the formats from date to number and back again.

The default date format in Excel is very rubbish, so whenever I enter a date, I always reformat it immediately into something sensible.

Lets show you how..

Step1:
Enter a date in a single cell.
For example - enter doday's date by using the Excel date shortcut for TODAY: CTRL + ;

For me today, this returns: 02/02/2009

Step2:
Press CTRL and # to change it to a good date format (dd-mmm-yy)

Step3:
Press CTRL and SHIFT and # to change it to a number. You might not want to do that for this example, but it is useful to know that CTRL and SHIFT and # formats a cell as a number

Download a spreadsheet to practice how to format dates in Excel

Training Video: Toggle Date Format

Adding 2 strings together is very easy in Excel.

There are a couple of ways.

Method1: use: &" "& NOTE - this adds a space between the 2 strings Here's an example:

Method2: use CONCATENATE

If the CONCATENATE function were designed properly, it would allow you to enter a range instead of each value separately, and it would allow you also to enter a separator.

Consequently, I prefer Method1.

Download a spreadsheet to practice adding strings together in Excel

Video Training on Adding 2 strings together in Excel:

This is a follow-on from tip on how to calculate the 1st day of the month in Excel

Suppose we want to calculate the last day of the month... can we use the same function ?

YES, but we need to be a bit more cunning this time.

=DATE(YEAR(TODAY(),MONTH(TODAY()+1,1)-1

• will return the last day of the month.
• it is basically saying take the 1st day of NEXT month, and subtract 1 day.

Download a spreadsheet to practice calculating the last day of the current month in Excel

It Even works for leap years.

Training video on calculating the last day of the month in Excel:

This tip explains how to Calculate the 1st Day of the month in Excel.

We know how to get today's date... use: =TODAY()

For me right now, this returns 30-Jan-2009 ... so is there a way to get the 1st Day of the month from this ?

YES - use the DATE function.

DATE is a handy function that allows you to construct a date whilst inputting YEAR, MONTH and DAY. Excel also has the functions YEAR(), MONTH() and DAY() which take a date, and return the YEAR, MONTH and DAY respectively.

So lets see this in action.

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

- will return the 1st day of the month. - it takes the year and month from TODAY, and uses 1 as the day.

Download a spreadsheet to practice calculating the 1st day of the current month in Excel

Training video on how to Calculate the first day of the month:

This is a follow-on from tip on entering an array formula in Excel

Once you have entered and array formula in Excel, you will notice that Excel won't let you modify it one cell at a time.

Suppose the matrix we have is now a 3 by 3 matrix, and we have the formula for a 2 by 2. How do we modify the array formula ?

• Step1: Select range F3 to H5
• Step2: Press F2
• Step3: Press- CTRL (and hold it down) SHIFT (and hold it down) ENTER

Download a spreadsheet to practice modifying array formulae

Training Video on how to Modify an Array Formula: