# Blogs

## 14. Excel Tips - IRR - internal rate of return

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:

## 13. Excel Tips - VLOOKUP

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.

## 12. Excel Tips - Need Help with Excel ?

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:

- 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 !

## 11. Excel Tips - Think of a number between 1 and 10

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

## 10. Excel Tips - Get access to a whole load of new functions - Analysis Toolpak

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.

## 9. Excel Tips - Change values on multiple sheets

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:

## 8. Excel Tips - Get file path with an excel formula

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.

## 7. Excel Tips - Create all the days of the week in 5 seconds

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:

## 6. Excel Tips - Debugging large Excel Function calls

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)

## 5. Excel Tips - Display function arguments

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:

## 4. Excel Tips - Autofit selection

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:

## 3. Excel Tips - Easily paste special values - Hidden Menu

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:

## 2. Excel Tips - Create numbers 1 to 10 easily

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:

## 1. Excel Tips - How many days until Christmas ?

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:

## 5. Learn VBA - What does my recorded Macro code actually mean ?

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

## 4. Learn VBA - Record a Macro

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:

## 3. Learn VBA - SUBS and FUNCTIONS explained

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:

You look at your watch
You tell her the time.

What would this look like in code ?

## 2. Learn VBA - FOR Loops

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.

## 1. Learn VBA - Introduction to VBA Editor

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:

## 05-Mar-09 NEW CALCULATOR - "Sell Or Rent Out" calculator - designed for Buy to Let investors

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

## 18-Feb-09 Two NEW sections on Interview questions

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

## Nick's Finance 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

## Nick's Excel Tip Of The Day - Part 3

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

All NEW tips Here

## Nick's Excel For Finance Tip Of The Day

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.

## Nick's guide to Excel / VBA Interview Questions

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"

## Nick's step by step guide to learn VBA

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.

## Nick's VBA Tip Of The Day

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.