Blogs

Nick's picture

60. Excel Tips - Pivot Tables for Dummies



Today’s tip is an introduction to Pivot tables and Pivot table summaries.

Pivot tables are an amazing way to summarise data. They are extremely versatile, and can provide many types of summary seamlessly. They can save hours of trying to write formulae to create summaries.

Nick's picture

10. Excel For Finance Tips - What is Quantitative Easing ?


What is Quantitative Easing ?

 

Simply put:

It's printing money !

It's a sneaky way of stealing money from cautious savers who carefully tucked money away for a rainy day, and giving it to reckless borrowers.

Nick's picture

9. Excel For Finance Tips - What is Leverage ?


What is Leverage ?

Nick's picture

8. Excel For Finance Tips - What is Yield ?



What is Yield ?

Remember IRR ?... well, simply put, it's another name for that.

It's the return you get from a series of cash flows, but applies mostly to Bonds. Lets prove that using Excel functions.

Nick's picture

7. Excel For Finance Tips - What is a Basis Point ?



What is a Basis Point ?

A basis point is quite simply 0.01 of 1% So if an interest rate is 5.00% and it increases to 5.01%, that corresponds to 1 basis point increase. What is it used for ? Profit margins for fixed income products are measured in basis points.

Lets look at an example in Excel:

Nick's picture

6. Excel For Finance Tips - What is IRR ?


What is IRR ?

IRR stands for Internal Rate of Return but don't let that confuse you.

It is simply the percentage return over a specific period of an investment. It is widely used for investment appraisal.

What happens is this:

Nick's picture

5. Excel For Finance Tips - What is Present Value ?



In our previous tip on Future Value, we learnt that you could take an amount today, and grow it by an interest rate, and that it would be larger in the future... we called that amount in the future "Future Value", and we proved that the FV excel function was doing just that.

Nick's picture

4. Excel For Finance Tips - What is a discount factor ?



Finance is all about money, and working out today's value for money arriving in the future.

Nick's picture

3. Excel For Finance Tips - Continuous Compounding



So we looked at interest compounding and we found that even though the quarterly compounded rate was lower, the fact that it was compounded 4 times a year meant that the annual rate ended up higher.

Nick's picture

2. Excel For Finance Tips - Future Value


What is £1 worth today ?


... well, clearly it's £1 !

Nick's picture

1. Excel For Finance Tips - Calculate Compound Interest


The very first tip is also the basis of finance

Nick's picture

6. VBA Tips - ScreenUpdating - Make your code run faster


What can you do to make VBA code run faster? Well, when you run VBA code in Excel, you will notice that the screen flickerseach time an action is performed. Lets take a simple example:

screenupdating-excel-1 

Nick's picture

5. VBA Tips - Time Your Code


How do you time how long it takes for VBA code to run ?

Simple..

Step1: At the start of your code, create a variable called "Start" (or whatever)

Set it = Timer (that takes a snapshot of the time)

 

Step2: At the End of your code, you can use a messagebox to display how long it took.

MsgBox Timer - Start & " Seconds"

Nick's picture

4. VBA Tips - Finding things using VBA


Don't use VLOOKUP, INDEX, or MATCH from VBA when finding things on a worksheet with VBA.

 

  • The problem with these is that when the function doesn't find something, it will return a VBA error which you then have to trap and handle successfully.
  • This is a very messy way of coding, and prone to errors.

The best way to look for things on a sheet is to use .Find

Nick's picture

3. VBA Tips - Reset the Used Range


An annoying feature of Excel is not resetting the the used range. What is the used range ? - well, it's meant to be a square around all populated cells.

- How can you select the Used Range for this example ?

reset-used-range-VBA-1

Nick's picture

2. VBA Tips - FOR loop

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.

Nick's picture

1. VBA Tips - Worksheet Events


Using VBA, there is the functionality for Excel to track most of what a user is doing to a workbook.

Armed with this information, a system designer can make things happen that help make the user's life easier.

Today, we're going to program a simple example of tracking selection change events. i.e. when you select something different from what is currently selected, an event is triggered.

Nick's picture

59. Excel Tips - Reverse a name


How do you reverse a name in Excel ?

This tip is very useful if you have a list of names in "Surname FirstName" format and you want to reverse that quickly.

Well, in Excel, there are a set of useful string manipulation functions you can use for this: LEN, FIND, MID, LEFT, RIGHT

Nick's picture

58. Excel Tips - Does a cell contain a string?


How do you find out whether a cell contains a certain string ?

- In VBA, it's easy.. use the INSTR function, and test whether this is greater than 0.

 

In Excel, you need to use both the LEN and the SUBSTITUTE functions.

=LEN(SUBSTITUTE(StringCell,StringToLookForCell,"",1))<>LEN(StringToLookForCell)

Nick's picture

56. Excel Tips - Using the LEN function in Excel


Here’s an Excel tip on the LEN function.

Generally speaking, LEN returns the amount of characters that a cell displays.

However, lets look at a few examples and exceptions:

 

1. Normal text:

Len-function-excel

Nick's picture

55. Excel Tips - Capture Screen Shot of your Excel


This is one of the most useful Excel tips that you can use in emails or presentations– How to Capture a Screen Shot of your Excel.

 

There is a quick and easy way of capturing an Excel screen shot without buying commercial software.

Step1: Launch PAINT by going: Start => All Programs => Accessories => Paint

Nick's picture

54. Excel Tips - Launch Excel using the keyboard


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:

Nick's picture

53. Excel Tips - Ways to Calculate in Excel


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.

Nick's picture

52. Excel Tips - Random Sort 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:
random-sort-in-excel

Nick's picture

51. Excel Tips - Calculation in Excel


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

Nick's picture

50. Excel Tips - Tell The Time 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:

Nick's picture

49. Excel Tips - Toggle Date Format


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 + ;

Nick's picture

48. Excel Tips - Add 2 strings together in Excel


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:

Add-2-strings-together-excel

Method2: use CONCATENATE

Nick's picture

47. Excel Tips - Calculate the LAST Day of the month

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

Nick's picture

46. Excel Tips - Calculate the 1st Day of the month

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.

Syndicate content