Screen real estate is a valuable commodity, and with this tip, you can save using it unnecessarily.

If you autofit the columns with large headings, it will look like this:

However, to get it to this is only a few steps:

Step1:

I am adding this as I looked at the search results, and noticed that people had searched for IRR, and sadly they didn't find anything useful.

Here's a noddy's guide to IRR.

IRR is a function that takes a series of cash flows and works out the effective rate of return if they were to be annually invested.

Let's take a look.
Here's an example of an IRR function call:

VLOOKUP is an extremely useful function and you should know it like the back of your hand.
In a few words, it allows you to look up a value in a table, and return another value on the same row.

In my example, you've bought yourself a massive Buy To Let portfolio, and are wondering when you can retire. On typing in the new prices, you realise this might not be any time soon.

If you need help, the last thing you want to do is to use Microsoft's help (pressing F1)... It's really rubbish.. Sorry MS !

The best place to look (IMHO) is google groups:

Google Groups

- this is where you're most likely to find an answer to your question as it's structured around a Problem => Solution framework, and someone else is sure to have encountered the same problem as you before... and solved it !

..or let Excel do it for you with the RANDBETWEEN function.

=RANDBETWEEN(1,10)

... useful for example if you're writing a card game, you can use =RANDBETWEEN(1,52) to pick a random card.

... or for a random walk analysis on a share price.

Training Video on Random Number Generation:

Did you know that you can get access to a whole load of new functions in Excel 2003 by adding the analysis toolpak ?

Go: Tools=>Addins and select Analysis toolpak

Now you can use functions like EDATE that allow you to add/subtract a specified amount of months to a start date.

Not a lot of people know this trick, but it can be a massive time saver if you have the same information on lots of sheets, and you want to change it.

It's simple when you know how !

Step1:
Select all the sheets that you want to enter a value into or change a value on. You can do this by holding down the CTRL key

Step2:

This is a quirky one.

You can use the excel function call CELL("filename") to retrieve something that should be the file name, but for some odd reason isn't.

This tip is a very quick and easy one

Step1:
Type: "Monday" in cell A1

Step2:
Select cell A1

Step3:
Click on the square in the bottom right of the selection
- Drag it down until A7

You will now have all the days of the week.

Training Video on how to create days of the week in Excel:

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)

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:

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:

This is a follow on from our previous VBA tip on how to record macros.

Do you find yourself doing the same tasks every day ?
Do you want Excel to automate them ?
This tip is about how to record a macro.
Recording macros is the first step to automate tasks, and to learn VBA.
You can get a decent amount of automation done with this simple procedure.

Step1:
Press the following in sequence:
ALT then t then m then r
That will bring up this screen:

Now we're going to learn what VBA SUBS and FUNCTIONS are.

A SUB is a piece of code that runs something
A FUNCTION is a piece of code that returns something

Now lets see it in action with a real life example:

Your wife asks you the time.
You look at your watch
You tell her the time.

What would this look like in code ?

Loops are fundamental to programming, and in this tip, we'll look at FOR loops.

If you have never programmed before then this step by step guide will show you how to write your first program.

Q. What is a loop ?

A. I think this is best explained using an example.

Excel 2007:

Lesson 1 will teach you how to attach some code to a button that will bring up a message box that says Hello!.

Step1:
Make sure your excel options (Office button in top left => Excel Options) are as shown:

Step2:

"Sell Or Rent Out" calculator
- Should you stick with your "Buy To Let" Property, or sell it now ?

More on the "Sell or Rent Out" Calculator.

Today, we have 2 new sections to ExcelExperts.com. Dedicated to those looking for a job in this tough market.

• Finance Interview Questions
• Quant interview questions

This section is on Finance Interview Questions...

Largely Fixed Income.

These are some of my favourite interview questions to ask in a finance interview.

If you have questions you've been asked and you want an answer, Contact ExcelExperts.com  

Newest developments !

• 12-Feb-09 Facebook Texas Holdem Poker Training

Know any poker players ?
- this is for them.

• 17-Feb-09 Redundancy Planning Calculator

Know anyone who has been made redundant recently, or anyone who is worried in case they're made redundant ?
- this is for them.

This is a follow-on from my 2nd series on Excel Tips - Excel Tip Of The Day - Part 2

All NEW tips Here

New Tips added here

Subscribe to new tips

IMPORTANT: Worried about SECURITY? Open Excel in SAFE MODE

This is my Excel for Finance Tip of the day blog. If you want to know how to calculate compound interest in excel, about discount factors, or even convert from simple interest to compound interest, this is the place for you.

I have hosted hundreds of Excel / VBA interviews, and I can work out very quickly how good someone is.

The biggest blunder to make in an interview is to say something like: "Oh, that's easy, I can do that in 5 minutes with the compiler and help files"

Learning VBA is all about building on a few fundamentals. Once you've started, it gets a lot easier. This VBA Training section will teach you those VBA fundamentals.

Hundreds of New VBA Training Videos added here

There definitely won't be VBA tips every day, but I think it's a good idea to separate the VBA tips from the Excel ones.

To Learn VBA, follow this link