Publication Date: March 17, 2010  

---

If your want to send me an email...

---

   

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.

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:

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:

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

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.

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

---