Nick's blog

Nick's picture

01-May-2009 - ExcelExperts.com Update


May 2009 Stats update from ExcelExperts.com

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:

format-text-in-a-cell

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

Steps:

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:

helpful-hints-on-the-statusbar

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:

freeze-panes

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 ?:

show-formulae

 

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:

 Separate-Text

and we want this:

Separate-Text

Steps:

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:

mm/dd/yyyy

Syndicate content