Blogs

60. Excel Tips - Pivot Tables for Dummies
Submitted by Nick on 9 March, 2009 - 15:23
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 blog
- 2 comments
- Read more
- 101609 reads

10. Excel For Finance Tips - What is Quantitative Easing ?
Submitted by Nick on 9 March, 2009 - 14:31
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 blog
- 2 comments
- Read more
- 14789 reads

9. Excel For Finance Tips - What is Leverage ?
Submitted by Nick on 9 March, 2009 - 12:14
What is Leverage ?
- Nick's blog
- Login or register to post comments
- Read more
- 10668 reads

8. Excel For Finance Tips - What is Yield ?
Submitted by Nick on 9 March, 2009 - 12:13
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 blog
- Login or register to post comments
- Read more
- 8107 reads

7. Excel For Finance Tips - What is a Basis Point ?
Submitted by Nick on 9 March, 2009 - 12:12
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 blog
- Login or register to post comments
- Read more
- 24264 reads

6. Excel For Finance Tips - What is IRR ?
Submitted by Nick on 9 March, 2009 - 12:10
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 blog
- Login or register to post comments
- Read more
- 12131 reads

5. Excel For Finance Tips - What is Present Value ?
Submitted by Nick on 9 March, 2009 - 12:09
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 blog
- Login or register to post comments
- Read more
- 7048 reads

4. Excel For Finance Tips - What is a discount factor ?
Submitted by Nick on 9 March, 2009 - 12:08
Finance is all about money, and working out today's value for money arriving in the future.
- Nick's blog
- Login or register to post comments
- Read more
- 18038 reads

3. Excel For Finance Tips - Continuous Compounding
Submitted by Nick on 9 March, 2009 - 12:07
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 blog
- Login or register to post comments
- Read more
- 34633 reads

2. Excel For Finance Tips - Future Value
Submitted by Nick on 9 March, 2009 - 12:00
What is £1 worth today ?
... well, clearly it's £1 !
- Nick's blog
- Login or register to post comments
- Read more
- 7916 reads

1. Excel For Finance Tips - Calculate Compound Interest
Submitted by Nick on 9 March, 2009 - 11:59
The very first tip is also the basis of finance
- Nick's blog
- Login or register to post comments
- Read more
- 23513 reads

6. VBA Tips - ScreenUpdating - Make your code run faster
Submitted by Nick on 9 March, 2009 - 11:53
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:
- Nick's blog
- Login or register to post comments
- Read more
- 40221 reads

5. VBA Tips - Time Your Code
Submitted by Nick on 9 March, 2009 - 11:51
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 blog
- 2 comments
- Read more
- 31433 reads

4. VBA Tips - Finding things using VBA
Submitted by Nick on 9 March, 2009 - 11:51
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 blog
- 2 comments
- Read more
- 30635 reads

3. VBA Tips - Reset the Used Range
Submitted by Nick on 9 March, 2009 - 11:50
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 ?
- Nick's blog
- 1 comment
- Read more
- 75195 reads

2. VBA Tips - FOR loop
Submitted by Nick on 9 March, 2009 - 11:47Loops 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 blog
- 1 comment
- Read more
- 4315 reads

1. VBA Tips - Worksheet Events
Submitted by Nick on 9 March, 2009 - 11:38
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 blog
- Login or register to post comments
- Read more
- 16749 reads

59. Excel Tips - Reverse a name
Submitted by Nick on 9 March, 2009 - 11:23
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 blog
- 4 comments
- Read more
- 54464 reads

58. Excel Tips - Does a cell contain a string?
Submitted by Nick on 9 March, 2009 - 11:22
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 blog
- 1 comment
- Read more
- 14480 reads

56. Excel Tips - Using the LEN function in Excel
Submitted by Nick on 9 March, 2009 - 11:21
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:
- Nick's blog
- Login or register to post comments
- Read more
- 23899 reads

55. Excel Tips - Capture Screen Shot of your Excel
Submitted by Nick on 9 March, 2009 - 11:19
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 blog
- Login or register to post comments
- Read more
- 43667 reads

54. Excel Tips - Launch Excel using the keyboard
Submitted by Nick on 9 March, 2009 - 11:16
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 blog
- Login or register to post comments
- Read more
- 7141 reads

53. Excel Tips - Ways to Calculate in Excel
Submitted by Nick on 9 March, 2009 - 11:14
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 blog
- Login or register to post comments
- Read more
- 8893 reads

52. Excel Tips - Random Sort in Excel
Submitted by Nick on 9 March, 2009 - 11:13
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:
- Nick's blog
- 1 comment
- Read more
- 16477 reads

51. Excel Tips - Calculation in Excel
Submitted by Nick on 9 March, 2009 - 11:12
Calculation in Excel is a big topic, but I am going to try to give you the essentials about calculating formulae.
- Nick's blog
- 1 comment
- Read more
- 10096 reads

50. Excel Tips - Tell The Time in Excel
Submitted by Nick on 9 March, 2009 - 11:11
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 blog
- 2 comments
- Read more
- 14463 reads

49. Excel Tips - Toggle Date Format
Submitted by Nick on 9 March, 2009 - 11:10
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 blog
- Login or register to post comments
- Read more
- 9945 reads

48. Excel Tips - Add 2 strings together in Excel
Submitted by Nick on 9 March, 2009 - 11:08
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
- Nick's blog
- 4 comments
- Read more
- 87344 reads

47. Excel Tips - Calculate the LAST Day of the month
Submitted by Nick on 9 March, 2009 - 11:07This 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 blog
- 2 comments
- Read more
- 14314 reads

46. Excel Tips - Calculate the 1st Day of the month
Submitted by Nick on 9 March, 2009 - 11:06This 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.
- Nick's blog
- 1 comment
- Read more
- 47541 reads

Recent comments
5 years 50 weeks ago
6 years 36 weeks ago
6 years 47 weeks ago
6 years 50 weeks ago
6 years 51 weeks ago
7 years 5 weeks ago
7 years 13 weeks ago
7 years 13 weeks ago
7 years 13 weeks ago
7 years 13 weeks ago