Excel 2000, 2002, 2003, 2007 Tips

The Excel Addict - Help with Excel 2007

Publication Date: March 17, 2010
 


This newsletter is being sent to 15,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 ReliefSpring is almost here in Newfoundland. In fact, the weather we've been getting around here in the last few weeks is better than our usual spring weather. A very tame winter and an nice, early spring is a great way to start off a new year. It's almost like we didn't even have a winter this year (except for three moderate, one-day snowstorms).

I hope wherever you are on planet earth, you are having a great week too. Learn something new in Excel and make your week even better.

What I love about Excel is that, because
there is almost no limit to the things you can do with Excel, it allows us to use our imagination and be creative in ways that ordinary Excel users would never think of.

Have you discovered something new and amazing about Excel lately? Tell me about it.

My goal is to help 1 million people discover their passion for Excel and have them pass on that passion to others. I've got 985,000 more to go. You can help by passing along this newsletter to anyone you know who uses Excel.

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) Whats The Difference Between The SEARCH and FIND Functions? (XL2000-XL2007)
2) Create Your Own Custom Sort Order (XL2002-XL2007)
3) Instantly Lock Your Desktop (XP/Vista)


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) 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)


You can still find last week's newsletter here.

You can access even more tips on my website by going to my members' page.  

"Dream as if you'll live forever, live as if you'll die today.”
-- James Dean





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

Whats The Difference Between The SEARCH and FIND Functions? (XL2000-XL2007)


SEARCH(find_text,within_text,start_num)

FIND(find_text,within_text,start_num)


Both the SEARCH function and the FIND function are used to find the starting position of one text string (or character) within another text string (reading left to right).

On the surface, Excel's SEARCH and FIND functions appear to be identical.

The difference, however, is that the FIND function is case sensitive whereas the SEARCH function is not.

If the FIND function does not find a match, it will return an #VALUE! error.

If you don't want the #VALUE! error to appear, add the IFERROR function to your formulas in Excel 2007 and the ISERROR function in earlier versions.

Here are some examples:

SEARCH and FIND functions in Excel 2003-2007


Microsoft Excel Tip #2

Create Your Own Custom Sort Order (XL2002-XL2007)

Sorting is an integral part of data analysis in Excel. Normally you sort text in ascending or descending alphabetical order and you sort numbers from highest to lowest or lowest to highest. This allows you to better understand relationships in your data.

But have you ever wanted to sort your data by some other order that wasn't in alphabetical or numeric order? Maybe your company wants to present its data by region. For example, in Canada, a company may want to present its data by province (west to east NOT in alphabetical order). You could sort the data in alphabetical order and then move the rows of data into the your custom order...but there is a much easier way.

In Excel you can create a custom list and use that list to sort your data in your own custom order. 

First you need to create your own custom list:

Create custom lists in Excel to use for sorting or to AutoFill a range1) In a blank range, enter the values of your custom list in the order that you want from top to bottom;

2) Next, select the cells you just typed;

3) In Excel 2007, click the Office Button, Excel Options, and click the Popular category and in the 'Top options for working with Excel', click 'Edit Custom Lists'. In Excel 2000-2003, click Tools, Options, and click the Custom Lists tab in the Options dialog;

5) In the Custom Lists dialog box, click Import, and then click OK twice.

Please note that the maximum length for a custom list is 255 characters, and the first character must not begin with a number.

Sort your data based on a custom list:

1) Select the range of cells you want to sort, or select a single cell if you want to sort the entire range;

2) In Excel 2007, on the Data tab, in the Sort &Filter group, click Sort. In Excel 2000-2003, from the Data menu select Sort;

3) For Excel 2007 users, in the Sort dialog, select the Column you want to Sort by from the first dropdown and in the Order dropdown select Custom List... In the Custom Lists dialog, select your custom list and click OK. For Excel 2000-2003 users, in the Sort dialog, select the column you want to Sort by from the first dropdown, then click the Options... button. From the 'First key sort order' dropdown, select your custom list and click OK.

You will notice that custom lists for day-of -the-week and month-of-the-year are already provided.

Note that Custom Lists are not just for sorting. These lists are also user for AutoFilling cells. Type the first value from your custom list into any cell and drag the Fill Handle to fill in all other values. You can even AutoFill your custom list up, down, left or right. How cool is that?

Sort your data in Excel based on your oen custom order



101 Excel Tips revealed in this book
Microsoft Excel 2000 - 2003

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

Instantly Lock Your Desktop (XP/Vista)


When you step away from your computer for a while and don't want to close the documents you are working on, but also don't want anyone else to have access to your computer before your screensaver kicks in, the best option is to lock your desktop.

Keyboard shortcut to instantly lock your DesktopPressing the Windows logo key + L, allows you to quickly password protect your computer. Any programs that are running when you lock the Desktop stay open and let you get back to work immediately after you enter your password.

(This tip assumes that your computer isn't set up to bypass the user password prompt)
 






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