Nick's blog

88. Excel Tips - Align Shapes
Submitted by Nick on 3 May, 2009 - 10:18
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

01-May-2009 - ExcelExperts.com Update
Submitted by Nick on 1 May, 2009 - 14:12
May 2009 Stats update from ExcelExperts.com

87. Excel Tips - Turn Rows into Columns - TRANSPOSE
Submitted by Nick on 30 April, 2009 - 15:16
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:

14. VBA Tips - Turn Off Message Boxes Whilst Running Code
Submitted by Nick on 22 April, 2009 - 10:25
There are certain message boxes that will popup by default when you're running code.
- 2 comments
- Read more
- 92419 reads

13. VBA Tips - Enable / Disable Events
Submitted by Nick on 22 April, 2009 - 09:56
In VBA you can use events to capture certain things that happen on a workbook

86. Excel Tips - Find Broken Links
Submitted by Nick on 21 April, 2009 - 15:55
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:

85. Excel Tips - Remove Non Numeric Characters
Submitted by Nick on 21 April, 2009 - 13:50
This tip is about removing non-numeric characters from a cell's value:
- 4 comments
- Read more
- 72008 reads

84. Excel Tips - Select All Text In A Cell
Submitted by Nick on 21 April, 2009 - 10:51
Here's how to select and copy all the text in a cell:

83. Excel Tips - Format Text In A Cell
Submitted by Nick on 21 April, 2009 - 10:37
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:

82. Excel Tips - Copy Sheet To New Workbook
Submitted by Nick on 21 April, 2009 - 09:08
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:

81. Excel Tips - Add a Link To Email
Submitted by Nick on 21 April, 2009 - 08:53
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.

80. Excel Tips - Toggle The Screen Lock In Function
Submitted by Nick on 17 April, 2009 - 13:08
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:

79. Excel Tips - Nth Largest Value In A Range
Submitted by Nick on 15 April, 2009 - 14:50
Here's a tip on how to find the Nth largest value in a range, and use it to sort a list of numbers:

78. Excel Tips - Why Is My Excel File So Big ?
Submitted by Nick on 15 April, 2009 - 13:42
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:
- 25 comments
- Read more
- 514028 reads

77. Excel Tips - Put Chart On A New Sheet
Submitted by Nick on 15 April, 2009 - 11:58
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:

76. Excel Tips - Get Tick Or Cross
Submitted by Nick on 15 April, 2009 - 10:59
You want to use a special character in Excel, but you don't know how to find it:

75. Excel Tips - Helpful Hints On The StatusBar
Submitted by Nick on 15 April, 2009 - 10:23
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:
- 2 comments
- Read more
- 8318 reads

74. Excel Tips - Dynamic Charting
Submitted by Nick on 14 April, 2009 - 14:19
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

73. Excel Tips - Fix Corrupt Spreadsheet
Submitted by Nick on 14 April, 2009 - 09:17
Today's tip is one of the most useful tips I will ever write.
- 12 comments
- Read more
- 72795 reads

12. VBA Tips - Get Rid Of Annoying Message Boxes When Closing Excel
Submitted by Nick on 10 April, 2009 - 10:48
Are you annoyed by that message box that pops up asking if you want to save changes when you close your sheet ?

11. VBA Tips - Find The Last Cell
Submitted by Nick on 9 April, 2009 - 15:18
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.
- 5 comments
- Read more
- 40741 reads

73. Excel Tips - Copy And Paste The Same Formula
Submitted by Nick on 9 April, 2009 - 10:55
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:

72. Excel Tips - Use Inverted Commas In Excel Formula
Submitted by Nick on 9 April, 2009 - 10:12
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:

71. Excel Tips - Display Excel File Path
Submitted by Nick on 9 April, 2009 - 08:55
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:
- 50 comments
- Read more
- 449098 reads

10. VBA Tips - Update Statusbar
Submitted by Nick on 8 April, 2009 - 14:21
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.
- 3 comments
- Read more
- 57079 reads

9. VBA Tips - Run Code Every Hour, Minute or Second
Submitted by Nick on 8 April, 2009 - 13:31
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:
- 28 comments
- Read more
- 282674 reads

70. Excel Tips - Freeze Panes
Submitted by Nick on 8 April, 2009 - 10:44
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

69. Excel Tips - Show Formulae
Submitted by Nick on 8 April, 2009 - 10:11
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.

68. Excel Tips - Separate Text
Submitted by Nick on 7 April, 2009 - 18:07
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:

67. Excel Tips - Remove unwanted characters
Submitted by Nick on 7 April, 2009 - 13:16
If you have used Excel for your every day job, I am sure you will have come across this issue:

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