Publication Date: March 3, 2010
This newsletter is being sent to 14,000+ Excel 'addicts' around the world
using Aweber, the best solution for managing your email campaigns.
Hi fellow Excel Addict,
I have spent more than eighteen years working with Microsoft Excel and learning many of its hidden tricks and shortcuts. And I just love helping others discover the seemingly limitless, capabilities that most users never find. My experience has also shown me that there are many Excel users (a surprisingly high percentage) who are completely satisfied to just plod along in Excel with what little knowledge they have. They will brush off any suggestions for improving how they are doing something, "No thanks, I prefer to do it my own way."
It's easy to say, "Oh well, that's their problem" but just think about - the time and money, through lost productivity, must be staggering for many businesses.
I'm glad YOU'RE not in that group. I know that you are interested in expanding your knowledge of Excel just by the fact that you are reading my tips every week. I get a lot of satisfaction out of sharing my knowledge with you. And I get even more satisfaction when I hear how these tips are helping you work in Excel. So, thank you.
One of the taglines I use on my website is, "Double, triple, even quadruple your productivity in Microsoft Excel." I honestly believe that just by learning some tips and shortcuts for your most common, time-consuming tasks in Excel, you can easily double, triple or even quadruple your productivity. A perfect example is Excel's VLOOKUP function. This one function can often do in seconds what most Excel users spend hours doing manually.
I would like to hear about your experience with other Excel users. What was your experience in getting help with Excel in the past? Have YOU tried to help other Excel users? Were they receptive to your suggestions? Please share your story with me.
Keep on Excelling and have a great week.
To your SUCCESS !
Francis Hayes (The Excel Addict)
If your want to send me an email...
• Please type 'The Excel Addict' in the Subject field of your emails.
• If you reply to this email, please delete the newsletter content before sending.
• Remove background color in Outlook 2007 - Options, Page Color, No Color.
This week's tips..
1) A Clever 'Change Formulas to Values' Trick (XL2000-XL2007)
2) Formulas To Extract Characters From Text In A Cell (XL2002-XL2007)
3) Adding Your Favourite Text Editor To The 'Send To' Menu (WinXP)
If you're having trouble displaying graphics in this email, I have posted an online HTML version of this week's newsletter here.
Last week's tips were...
1) Show Day Names With Your Dates (XL2000-XL2007)
2) Color Your Sheet Tabs (XL2002-XL2007)
3) Bring Order To Your Programs Menu (WinXP)
You can still find last week's newsletter here.
You can access even more tips on my website by going to my members' page.
Make sure you save a copy of your spreadsheet before trying these tips, just in
case you make an error or the procedure doesn't produce the results you want.
A Clever 'Change Formulas to Values' Trick (XL2000-XL2007)
When you're new to Excel you quickly find a need to convert a range of formulas to their fixed values. Maybe for a while the only way you knew was to type the value in each cell, overwriting the formulas. Then someone showed you a much easier way: Edit, Copy, Edit, Paste Special, Values, OK. You thought, "Wow! That is awesome." It saved you lots of time.
Well, here's another cool trick you may not have seen before.
1. Select the range you want to convert;
2. Move the cursor to the edge of the range;
3. Using your right mouse button, drag the range either to the left or right and then back to the original location and release the right mouse button;
4. You'll see a popup menu. Select Copy Here as Values Only and your formulas will be replaced with their fixed values.
overwriting the original formulas, you want to copy the formula results
to another range, right-drag the range to the new location and when you
release the right mouse button, choose Copy
Here as Values Only.
Formulas To Extract Characters From Text In A Cell (XL2002-XL2007)
There may be times when you need to extract certain characters from text within a cell. For example, a general ledger code such as W-105-0805 may be comprised of a character representing the region, a 3-character subcode representing the branch and a 4-character subcode representing the expense category. If you are required to extract each of these subcodes into separate columns, knowing Excel's string functions can save you a whole lot of time.
• The LEFT function returns a desired number of characters from the beginning of a text string.
• The RIGHT function returns a desired number of characters from the end of a text string.
• The MID function returns a desired number of characters starting at a specified position within the text string.
Assuming your general ledger codes are in column B and you want to extract the subcodes to columns C, D and E.
In cell C2 you would enter the formula =LEFT(B2,1)
and this would
return the region code W.
Here the region code is just one character. In other cases you can
extract any number of characters by changing the #_of_characters
attribute in the
In cell E2 you would enter the formula =RIGHT(B2,4)
and this would
return the expense code 5095.
=MID(text_string, start_pos, #_of_characters)
The difference with the MID function is
that you specify at
which character position in the text string you start. In cell D2 you
would enter the formula =MID(B2,3,3)
which says, from the text string in cell B2, starting at the third
character, return three characters. This would return the
Adding Your Favourite Text Editor To The 'Send To' Menu (WinXP)
There may be times when you want to open small text files in a simple text editor rather than have it open in Microsoft Word or some other humongous word processing program. (Personally I use Textpad from Textpad.com). You can also use this with Notepad or WordPad.
A quick way to open any type of text file in your favourite text editor is simply by right-clicking on the file, choosing SendTo, and then selecting the shortcut to your text editor. But first, you have to create a shortcut to your text editor in the SendTo folder.
1) From Windows Explorer browse to C:Documents and Settings\username\SendTo. The right Explorer pane will list the shortcuts currently on the SendTo menu;
2) Now, click Start, All Programs, and find your text editor shortcut (you will find NotePad in the Accessories group);
3) Right-click and Copy the text editor shortcut;
4) Switch back to Windows Explorer window. Right-click and Paste in the right pane. You should now see the shortcut to the text editor in the SendTo folder.
Now let's see how it works.
1) Browse to find a text file in Windows Explorer;
2) Right-click the file, choose SendTo, and select the shortcut for your text editor. The text file should open in the text editor.
Now, anytime you want to open a text file in that particular text editor rather than the default program (i.e. MS Word), these few quick clicks will do it for you.
Note that some newer versions of text editors add a shortcut to the right-click menu (or Open With on the right-click menu) when they are installed. If you already have a shortcut to your text editor on your right-click menu, then you don't need to add a shortcut to the SendTo menu. You just have to remember to use it.
Can you see other applications for this neat trick? Open CSV files in Notepad? Open HTML files in Notepad? Open photos in an image editor rather than the default Picture Viewer?
Spreadsheet Tips From An Excel Addict is available only to subscribers of my newsletter.
If this newsletter was forwarded to you and you would like to get your own copy, please visit TheExcelAddict.com or send a blank email to theexceladdict(AT)aweber(DOT)com
If you would like to share this newsletter with others...
1) Forward this newsletter by email, but first delete the unsubscribe link at the very bottom so you don't get accidentally unsubscribed
2) Ask your friend/colleague to visit TheExcelAddict.com or send a blank email to theexceladdict(AT)aweber(DOT)com
3) Post a link to TheExcelAddict.com in a company newsletter or website
|"Spreadsheets Tips From
An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630