This Excel Tip is about how to align shapes:

• Suppose you have added 3 buttons and they're out of line.
• How can you align them ?

Here's a screen shot of what we have, and what we want to have:

Here's how to do it

May 2009 Stats update from ExcelExperts.com

Suppose you have data in rows, and you want to convert it to columns, how can you do that in Excel ?

• Use the TRANSPOSE function
• TRANSPOSE will flip data 90 degrees to convert row data to column data and vice versa.

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

There are certain message boxes that will popup by default when you're running code.

In VBA you can use events to capture certain things that happen on a workbook

Finding broken links can be tricky if you don't know how they're caused.

In this Excel Tip, I will show you how to recreate a broken link, and give you some insight on how to fix them.

The most common type of broken links are range names..

To recreate a broken link, follow these steps:

This tip is about removing non-numeric characters from a cell's value:

Here's how to select and copy all the text in a cell:

This is a handy tip that a lot of people don't know:

• You can actually format the text within a cell

Here's a screen shot of what it looks like:

Here's how to do it:

You often need to create a copy of the sheet you're currently working on

• One way is to saveAs another file name, then delete the extra sheets... but there is a better way

Steps:

Adding a link to an email address is simple in Excel:

• All you have to do is to type in the email address into the cell, and it automatically adds a hyperlink that will launch your mail program and compose a mail addressed to the address you typed in

You can then right click and edit the hyperlink, adding a subject, or changing the text that is displayed whilst keeping the link the same.

This tip is something you would never discover on your own, and can be a massive annoyance reducer:

• Pressing F2 toggles between scrolling into the formula and scrolling on the sheet

It's not immdiately obvious what I am talking about, so here's an example.

Here's our data in Excel:

Here's a tip on how to find the Nth largest value in a range, and use it to sort a list of numbers:

Excel files seem to just get bigger and bigger often balooning in size at the blink of an eye, but how do you find out where all the extra file size is coming from ?

• There is a way to separate out each object in your file, and examine it's contribution to the overall file size

Here's a screen shot of our innocent-looking Excel file:

Today's tip is a simple one on how to move a chart

• By default, Excel adds a chart to the current worksheet
• You can easily move a chart off a sheet and onto it's own chart worksheet

Here's a screen shot of how our chart looks in Excel:

You want to use a special character in Excel, but you don't know how to find it:

There is a way to get some simple information quickly and without having to write formulae :

• Use the statusbar (the thing at the bottom)

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

When you add a chart, it's not obvious at all how to construct it so that new additions to your data are automatically added to your graph

Today's tip is one of the most useful tips I will ever write.

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:

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: