Excel 2000, 2002, 2003, 2007 Tips

The Excel Addict - Help with Excel 2007

Publication Date: April 7, 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 ReliefEach week I try to provide a variety of tips to help you discover some of the possibilities hidden within this amazing application. I don't claim to know everything about Excel, even though I have been using it for more than 18 years. I realized a long time ago that Excel is so powerful that I would never know everything that is possible with it. I too am making new discoveries every day.

I'll bet that you probably know things about Excel that I don't. So tell me some of the things you like best about Excel. Also, what are some of the amazing things you've seen others use Excel for? I've seen people use Excel for many amazing things. There seems to be no limit.

Here's a YouTube video that I found that will amaze you.

An amazing video of drawing using Microsoft Excel

To your SUCCESS !
Francis Hayes (The Excel Addict)

P.S. If you sent me an email last week that I didn't reply to, it's because I was swamped with hundreds of emails. Over the next few days I plan to catch up on all the ones I didn't get to.

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) Align Decimals In Numbers With Variable Decimal Places (XL2000-2007)
2) Justify Your Text (XL2003/XL2007)
3) Add Folders To Your Bookmarks Toolbar (IE / Firefox)

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) Formulas To Determine Quarters For Calendar And Fiscal Years (XL2000-2007)
2) Separate Sorted Groups Of Data With Borders (XL2003/XL2007)
3) Easily Duplicate A File In Windows Explorer (Windows XP)

You can still find last week's newsletter here.

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


"If you have integrity, nothing else matters.  If you don't have integrity, nothing else matters." -- Alan Simpson

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

Align Decimals In Numbers With Variable Decimal Places (XL2000-2007)

I received this email from Keith asking "Is it possible in Excel to align a numeric column on the decimal point regardless of the number of digits before and after the decimal point?  I can't find a way to do it.  I am informed that it can be done in MS Word."

Yes, it can also be done in Excel using a custom number format.

Align decimals in Excel 2003-2007

For example, to line up all the decimal points for 1.23, 2, .0085, 6239.879, -1.5, add a question mark (?) in the number format code to the right of the decimal for the maximum number of insignificant decimal place you'll need. Since the maximum number of decimal places in this example is four (i.e. 0.0085), to display these numbers exactly as entered and align all of the decimal points, use the number format .???? 

Custom number format to align decimals in Excel 2003-2007

The ? adds a blank space for each insignificant digit.

Pull answers from your lists with these 45 LOOKUP formulas

Microsoft Excel Tip #2

Justify Your Text (XL2003/XL2007)

Because Excel is used for such a wide variety of tasks, you may occasionally find yourself typing paragraphs of text into your worksheets. Since Excel allows word wrapping in a cell, you can easily type a paragraph or more into one cell. However, there may be times when word wrapping is not practical because the increased row height also affects the adjacent cells. One way to avoid this is to type your paragraph into several cells down the column. If you have ever done this, no doubt, you have also had to make changes to your text or re-adjust the column width and then found yourself cutting and pasting text from one row to the next to make it fit on each line.
Another example would be if you copy some text from a web page or elsewhere and paste it into your worksheet. You may find that all of the text is pasted into one cell and extends to the right off the screen but you want to spread the text over several cells down a column.
Well, you'll be happy to know that there is a little-known option, practically hidden in Excel, that can be a godsend whenever you find yourself trying to redistribute text over several rows. It's called Justify.
Now let's see how Justify works...
For Justify to work, your text must be in one or more cells in a single column. You can spread the text to display across several columns and rows but the actual text will be in the leftmost column only. You can also include blank cells (i.e. paragraph breaks) in your text.
To better understand how this works (it's really simple, believe me!) please follow along with these examples.

Text Distributed In A Single Column

1) Open a new workbook;

2) Type a couple of sentences in cell A5 or copy some text from a web page and paste (right-click the cell and select Paste Special, Text, OK) it into cell A5. Go ahead. Try it now. When you Paste Special, Text, the text should be in one column but will overflow into the blank columns to the right;

3) Adjust the width of column A to 50;

Spread text across multiple cells in Excel 2007

4) Now, highlight cells A5:A15;

5) In Excel 2007, on the Home tab, in the Editing group, click the Fill tool (the small tool with the downward pointing arrow) and select JustifyIn Excel 2003, from the Edit menu, select Fill, and Justify. The text will be distributed into the selected cells.

Justify text in Excel 2007

To see that work again, change the width of column A and repeat step 5. Isn't that cool?

Text Distributed Across Multiple Columns

As I said earlier, the text must be all in one column but, rather than adjusting the column width as in the example above, you can also choose to have the text display across several columns. Try this.

1) Resize column A to it's original width 8.43;

2) Highlight cells A5:E15;

3) In Excel 2007, on the Home tab, in the Editing group, click the Fill tool and select JustifyIn Excel 2003, from the Edit menu, select Fill, and Justify. Notice that, although the text is still contained only in the cells in column A, it now appears distributed across the columns you selected.

Justify text across cells and rows in Excel 2007

Now, whenever you need to edit the text, it is a simple process to redistribute it so it fits in the range you've selected. This sure beats all that cutting and pasting. 

A few things to keep in mind about using Justify

 - The maximum number of characters you can have in one cell, for Justify to work, is 255. Extra characters will be truncated.

 - If the range you select isn't large enough to accommodate the text when justified, you will get a warning message allowing you to abort if necessary, otherwise it may overwrite data in the cells below. 

 - When re-justifying (if that's a word), versions of Excel prior to 2003 may erroneously omit a space when rejoining text that was previously split. 

Justify problem in earlier versions of Excel

In Excel 2003 and 2007, that's not a problem. It seems to work perfectly.

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

Add Folders To Your Bookmarks Toolbar (IE / Firefox)

Do you use Internet Explorer's Links Toolbar or Firefox's Bookmarks Toolbar? These toolbars make it quicker to access
your most frequently visited sites rather than going through the Favorites (IE) or the Bookmarks (FF) menus.

If the toolbar is not visible in your browser, click View, Toolbar. and select Favorites (IE) or Bookmarks (FF). Drag the left edge of the toolbar to resize it. If you don't want to keep the delete default links that are included on the toolbar, simply right-click and select Delete.

There's a limit to how many links you can add to this toolbar and still have them visible, so when the toolbar is full, the extra bookmarks extend off the right of the screen. You can access these hidden links by clicking the arrows at the right edge of the toolbar.

You can make these toolbars much more useful by organizing related bookmarks into folders. Yes, you CAN add folders to your bookmarks toolbar. You're not limited to just individual links.

Organize your frequently accessed links in folders on your bookmarks toolbar

To add a folder to the toolbar, right-click, select New Folder and give it a name.

To move 
links already on your toolbar to a folder, drag them using the left mouse button and drop them on the folder.

You can also drag folders around the toolbar to arrange them exactly as you want.

The easiest way to add a new link to your bookmarks toolbar is to simply select and drag the URL from the Firefox address bar and drop it onto the folder. In Internet Explorer, you will need to drag the web page icon to the left of the URL and drop it onto the folder.

Organizing your favorite links into folders on your bookmarks toolbar will make for a much better browsing experience.

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