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.
Hi fellow Excel Addict,
Each 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.
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.
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.
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.
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.
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 .????
The ? adds a blank space for each insignificant digit.
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
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.
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;
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 Justify. In Excel 2003, from the Edit menu, select Fill, and Justify. The text will be distributed into the selected cells.
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 Justify. In 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.
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.
In Excel 2003 and 2007, that's not a
problem. It seems to work perfectly.
Try out the
secrets in my
ebook for two months. If
they don't save you 10
the price of the book... If you're not
request a full
refund from ClickBank.com There's
absolutely no risk
Try out the
secrets in my
ebook for two months. If
they don't save you 10
the price of the book...
If you're not 100% satisfied...
request a full
refund from ClickBank.com
There's absolutely no risk to you.
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.
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.
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