Excel 2000, 2002, 2003, 2007 Tips

The Excel Addict - Help with Excel 2007

Publication Date: March 10, 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 ReliefJust yesterday 'Spreadsheet Tips From An Excel Addict' reached another special milestone. There are now over 15,000 Excel 'addicts' from more than 100 countries around the world reading this newsletter.

I want to thank you for sharing this newsletter with your friends and colleagues. Everybody can use more Excel knowledge.

Wouldn't you like to help others discover these 'amazing, hidden powers' of Excel? If you can, let others know about this newsletter through a company newsletter, Facebook, Twitter or any other means, it will be greatly appreciated by me AND THEM.

Does your company publish a periodic newsletter? Please contact me at fhayes(AT)theexceladdict(DOT)com to find out how your company can include 'Spreadsheet Tips From An Excel Addict' for free.

As you probably know, my goal is to share this information with 1 million Excel users. I've got 985,000 more to go. So I could use a little help.

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) Round Numbers To The Nearest Multiple (XL2000-XL2007)
2) Split Text Into Multiple Columns (XL2002-XL2007)
3) Disable That Annoying Desktop Cleanup Message (Win XP)

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.  

"Identify your problems but give your power and energy to solutions." -- Tony Robbins

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

Round Numbers To The Nearest Multiple (XL2000-XL2007)

Please Note: If you are using a version of Excel prior to 2007 you will need to install the Analysis Toolpak first. Click ToolsAdd-ins, select Analysis Toolpak and click OK.

If you need to round numbers to some specified increment, such as rounding prices to the nearest 25¢, the MROUND function in Excel can help you.

Round values in Microsoft 2000 - 2003 Excel to the nearest multipleExample:



The MROUND function will round a number to the nearest multiple of the second argument. In this example, it will round the value in cell C3 to the nearest 25¢.

Caution: For the MROUND function to work, both arguments of the function must have the same sign. Therefore, if cells referenced by MROUND have a mix of positive and negative amounts, you will need to multiply the second argument by the sign of the first argument using the SIGN function.

In the above example, =MROUND(C3,0.25*SIGN(C3)) will work for both positive and negative values.

Microsoft Excel Tip #2

Split Text Into Multiple Columns (XL2002-XL2007)

In last weeks newsletter I showed you how you could extract a substring from text in a cell using Excel's LEFT, RIGHT and MID functions. What if you want to split the original text into columns? 

Have you ever had data in your worksheet where all of the information was in one column? Maybe it was data you copied from another source such as a text file, a webpage or a Word document. Maybe it was data you imported from a database or some other application.

That information is not much good in one column. In order to manipulate this data to make it useful for sorting, filtering or performing calculations, you need to split the data into separate columns.

Fortunately Excel provides a very easy and efficient way to split data into columns, whether you have 10 rows or 10,000 rows. The feature is called Text to Columns.

Split a column of date into multiple columns in Excel 2000-2007

Text to Columns works best on data from a plain text file. The data must have some consistency to its layout. For example, each record (row) must be separated by a line break. The cells within each row should be separated by a standard character (e.g. comma, space or tab) or have a fixed number of characters for each column.

Let's take a look at how it works.

1) First you need to examine your data to determine how it can be split. Is each field (i.e. column) separated by a comma, space, etc... or does each field have the same width as the one above and below it? In this example, the fields in each row are separated by commas.

2) Next you need to select the cells you want to split into columns. This must be a single range and can be only one column wide. Make sure there are enough empty columns to the right of the selected column to accommodate the split. Insert new columns if necessary;

3) To open the 'Text to Columns' dialog: In Excel 2007, click on the Data tab and in the Data Tools group click on the 'Text to Columns' button. In Excel 2000-2003, click on the Data menu and click 'Text to Columns'. This will open up a 3-step wizard to guide you through the process;

3) In step 1 of the wizard you choose which type of data you have. Delimited = each field is separated by a standard character (i.e. comma, space, tab, etc...). Fixed width = each field has the same number of characters as the one above and below it.. In our example, we choose Delimited and click the Next> button;

4) In step 2 of the wizard, you tell Excel which delimiter is used in your data. Based on this example, you would select Comma as the delimiter. You will see a preview of how your data will be split. If everything looks OK then click Next>;

5) In Step 3, the default format for each column is General. General will convert numeric values to numbers, date values to dates and everything else to Text. If you like, you can set a different format for any column. Notice that as you click on each option, you will see an explanation displayed to the right. If a column has numeric data with leading zeros that you want to keep, you will need to set the format to Text. You can also choose 'Do not import column (skip)' for columns of data you do not need.

6) Click Finish and your data will be split into columns

Right-click to download this Text to Columns practice file.


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

Disable That Annoying Desktop Cleanup Message (Win XP)

How to disable the Desktop Cleanup MessageHow many times have you seen the message "
There are unused icons on your desktop"?

This feature is set to run every 60 days and tells you which icons you haven't used in 60 days. If you like your Desktop icons the way they are and don't want to be bothered with this message again, here's a way to disable it.

1) Right-click a blank area on the desktop and click Properties to open the Display Properties dialog;
2) Click the Desktop tab;
3) Click Customize Desktop to open the Desktop Items dialog;
4) Clear the 'Run Desktop Cleanup Wizard every 60 days' check box;
5) Click OK, OK to close the dialog boxes.

To run the Wizard manually at any time, right-click a blank area of the Desktop, click Properties, Customize Desktop, Clean Desktop Now.

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