Nick's blog

Nick's picture

01-May-2009 - Update

May 2009 Stats update from

Nick's picture

87. Excel Tips - Turn Rows into Columns - TRANSPOSE

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:

Nick's picture

14. VBA Tips - Turn Off Message Boxes Whilst Running Code

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

Nick's picture

13. VBA Tips - Enable / Disable Events

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

Nick's picture

86. Excel Tips - Find Broken Links

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:

Nick's picture

85. Excel Tips - Remove Non Numeric Characters

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

Nick's picture

84. Excel Tips - Select All Text In A Cell

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

Nick's picture

83. Excel Tips - Format 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:

Nick's picture

82. Excel Tips - Copy Sheet To New Workbook

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


Nick's picture

81. Excel Tips - Add a Link To Email

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.

Nick's picture

80. Excel Tips - Toggle The Screen Lock In Function

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:

Nick's picture

79. Excel Tips - Nth Largest Value In A Range

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

Nick's picture

78. Excel Tips - Why Is My Excel File So Big ?

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:

Nick's picture

77. Excel Tips - Put Chart On A New Sheet

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:

Nick's picture

76. Excel Tips - Get Tick Or Cross

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

Nick's picture

75. Excel Tips - Helpful Hints On The StatusBar

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:


Nick's picture

74. Excel Tips - Dynamic Charting

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

Nick's picture

73. Excel Tips - Fix Corrupt Spreadsheet

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

Nick's picture

12. VBA Tips - Get Rid Of Annoying Message Boxes When Closing Excel

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

Nick's picture

11. VBA Tips - Find The Last Cell

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.

Nick's picture

73. Excel Tips - Copy And Paste The Same Formula

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:

Nick's picture

72. Excel Tips - Use Inverted Commas In Excel Formula

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:

Nick's picture

71. Excel Tips - Display Excel File Path

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:

Nick's picture

10. VBA Tips - Update Statusbar

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.


Nick's picture

9. VBA Tips - Run Code Every Hour, Minute or Second

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:

Nick's picture

70. Excel Tips - Freeze Panes

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

Nick's picture

69. Excel Tips - Show Formulae

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.

Nick's picture

68. Excel Tips - Separate Text

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:



Nick's picture

67. Excel Tips - Remove unwanted characters

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

Nick's picture

66. Excel Tips - American date to proper date.

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:


Syndicate content