Excel 2000, 2002, 2003, 2007 Tips

The Excel Addict - Help with Excel 2007

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.  

Greetings from The Excel Addict

Hi fellow Excel Addict,

The Excel Addict Donation to Haiti Earthquake ReliefI 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.

View The Excel Addict Francis J Hayes's profile on LinkedIn   Find out what The Excel Addict is doing NOW

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.  

"Each man should frame life so that at some future hour, fact and his dreamings meet." - Victor Hugo

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.

Microsoft Excel Tip #1

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.

A new trick to convert formulas to valuesWell, 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.

If, rather than 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.

Microsoft Excel Tip #2

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.

Excel Text functions sample

LEFT Function: In this example, to extract the region code, which is the left-most character, you can use the LEFT function.

=LEFT(text_string, #_of_characters)

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 formula.

Excel LEFT text function

RIGHT Function: To extract the expense code, which is comprised of the 4 right-most characters, you can use Excel's RIGHT function.

=RIGHT(text_string, #_of_characters)

In cell E2 you would enter the formula =RIGHT(B2,4) and this would return the expense code 5095.

Excel RIGHT text function

MID Function: To extract the branch code, which is in the middle of this text string, you can use Excel's MID function

=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 expense code 320.

Excel MID text function

NOTE: Some people may suggest that using Text to Columns would be an easier solution for this example. I agree, however, the purpose of this tip is to illustrate some of Excel's text functions. I am not necessarily suggesting that this is the easiest way to split a string of text.

101 Excel Tips revealed in this book
"I purchased your eBook and so did my boss.  Even though we are both experienced Excel user in the field of engineering and construction surveying, we find that your tips are top-notch and to the point; well above the standard fodder you find in the range of Excel books available on the market. Thank you" -- Richard J., Las Vegas, NV

Click Here to Download Excel Secrets eBook

Try out the secrets in my ebook for two months. If they 
don't save you 10 times the price of the book...

If you're not 100% satisfied...

...simply request a full refund from ClickBank.com

There's absolutely no risk to you.

Non-Excel Tip

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.

Here's how:

Add your own shortcuts to the SendTo menu1) 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?

Pull answers from your lists with these 45 LOOKUP formulas

My goal: To reach One Million Excel Users

Subscription Information

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