Are you annoyed by that message box that pops up asking if you want to save changes when you close your sheet ?

Often in VBA, you're dealing with ranges whose size changes all the time

• New data is added at the end
• How do you find the last cell ?
• How do you add data to the first free cell ?

Well, it just so happens that there is a cunning trick to help you out.

Why is there no easy way to copy and paste the same formula ?

• Copy and paste will change the cell references if the references are not fixed.
• Cut and paste removes the formulae from the original place and changes cell references if not fixed.

So, to explain more... We have this:

Adding inverted commas (quotation marks) to a piece of text is a little fiddly:

• Just typing =""  will return nothing in a cell
• It turns out that you need to use:  =""""  (4 inverted commas) to return " 

Here's how it looks in Excel:

This is an extremely useful tip if you did not already know it:

• There's a way in Excel to display the file path of the current open file.
• You can add it to your toolbar, and then copy it from there whenever you need it.

Here's a screen shot of our Excel:

feel free to improve on this video and use other forms.  I tried embedding the flash video I made, but it didn't work.  Hint Adobe Captivate makes great videos like the one below and is available for free 30 day trial download.

This VBA tip is for people designing systems in Excel:

• You've written some code that takes a while to run
• You don't want your users to think nothing is happening
• This tip shows you how to update them on the code's progress

The example we will use is that of changing a cell's value from 1 to 1000.

In this VBA tip, we will learn how to run a piece of code every hour, minute, second or indeed any time:

• Suppose you have a live feed coming in for a share price
• You want to record your own price history for later analysis
• This tip will show you the code you need for this.

Here's our data:

Do you have a large amount of data that goes off the bottom of the screen ?

• If you do, and you want to keep the headings in view, then this tip is for you

Here's a screen shot of our data in Excel:

Here's how to keep the headings in view

Most regular Excel users have discovered today's functionality by accident, and ask what on earth has happened!

Heve you ever seen your Excel sheet do this ?:

What has happened is that you have changed the view from a normal one to one showing the formulae.

This tip shows you a quick and easy way to separate text without using formulae. Suppose we have the following data:

and we want this:

Steps:

If you have used Excel for your every day job, I am sure you will have come across this issue:

This tip is all about that annoying time when someone gives you some dates for your spreadsheet and they come in all wrong. What has happened ?

You will be painfully aware of it if you work in a multi-regional company, and you are in Europe.

Well... most likely, along the way, someone has been using an American date format:

mm/dd/yyyy

April 2009 Stats update from ExcelExperts.com

This tip is about doing what the Excel CONCATENATE function should be doing, but isn't !

Suppose we want to create a comma-separated list from a range or 2 ranges... How can we do that ?

• Write a customised function in VBA !

Here's out source Data:

This tip shows you how to center text across cells. Suppose you have the following arrangement, and you want the heading to span the whole table:

One option is to merge the cells.. right click => format cells => Alignment => merge cells.

This tip is on how to select the Current Region using a keyboard shortcut:

CTRL + A

Suppose you have the following data:

Pressing CTRL + A will select a square around all the cells that are connected to the current cell.

Hi all I have been extremely busy over the past week doing Excel training videos for the Excel tips and calculators... all recorded with the best free software that money can buy !!

; - >

There are 64 new Excel Training Videos with some gr8 tips and tricks..

Feedback welcome

Nick

Excel Training Videos

This tip teaches you how to paste as a hyperlink.

• This means that you create a hyperlink from an existing cell.

Step1: Create a new workbook.

Step2: Save it

Step3: Copy the cell you want to hyperlink to.

Step4: Select another cell, and click on the Paste ribbon... choose "Paste as hyperlink" Step by step training Video on how to Paste As Hyperlink:

Announcing some new training videos from ExcelExperts.com

Facebook Texas Holdem Poker Training Video:

Buy Or Rent Calculator - Worked Example:

Small numbers look odd in Excel.

A Very small number will look something like this: 3.38E-09

- This is scientific notation meaning 3.48*10^-9 (3.48 times 10 to the power of -9, or 3.48*0.000000001) The reason Excel does this (I think) is to be able to display a small number in a small space.

What is RISK ?

It’s a word we hear a lot these days...

CEO, Investment bank:

UP !

I had a look at interest rates available on the market, and found the 1y rate to be 1.50% and the 5y was at 3.20%

Taking just these 2 rates, and assuming that there's no arbitrage, I calculated the 4y rate in 1 year's time to be 3.63%.

Here's a tip for those of you who need to prepare end of month reports.

There's a way to calculate the last Friday of the month in Excel.

Here's how it looks in Excel:

So first, you enter a date.

Interpolation is a methodology of guessing what the value would be in between 2 points.

Linear interpolation assumes that between the 2 points is a straight line.

Lets have a look at an example:

Suppose we know the Zero Rate for 1 year: 3% We also know the 4y Zero Rate: 8% ... how do we calculate the 2 year Zero Rate ?

ExcelExperts.com has teamed up with YouTube to provide Training videos.
A lot of people loved the Buy or Rent calculator, but they thought it was too complicated.
Well, hopefully these training videos will make it all clear.

There are 2:
1. An introduction to using the calculator
2. A worked example building up the calculation step by step.

Hyperlinks can be like mosquitoes

- Sometimes you can't see them and they get you when you're least expecting.

The reason is that even if you clear the contents of a cell, the hyperlink can remain. Time to blast them with a bit of VBA. It's easily done... Hyperlinks are stored on a sheet by sheet basis, so all you have to do is to loop through the hyperlinks collection of the active sheet and delete them.