Whitelist this newsletter

The Excel Addict - Help with Excel 2007

Publication Date: February 3, 2010

This newsletter is being sent to 14,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,

Francis Hayes - Get help from a Microsoft Excel AddictAs I am preparing this newsletter tonight, the winter winds are howling outside my window. With half of the winter season behind us and very little snow so far, I think we are going to make up for it over the next day or two. The forecast is calling for 35 cm of snow and 90 km/h winds.

On the bright side, maybe we'll get a 'snow day' tomorrow!

I have an important follow up to a tip I gave you last week. In my tip How To Really Delete A File, I may have left the impression that holding down the Shift key while deleting a file would delete it permanently so no one could ever see it. The fact is, when you delete a file in this way (in fact, even when you empty the Recycle Bin), the files are still physically on your hard drive. However, they are marked for deletion and may or may not be overwritten by changes you've made since you deleted the files. Most users will not be able to access these deleted files, however, with the right software, these files could possible be recovered from the hard drive.

As you may have seen in the email I sent you earlier this week, the Excel Addicts Supporting Haiti campaign raised $5,000 to help out the earthquake victims. I will be making the $2,500 donation to the Red Cross in the next week or so after I receive a cheque for the proceeds of my ebook sales. The Canadian Government will be matching this donation with another $2,500. Again, I want to thank everyone who supported me in this campaign.

Keep on Excelling,
Francis J Hayes (The Excel Addict)

Please type 'The Excel Addict' in the Subject field of your emails.
If you reply to this message, please delete the newsletter content
below before sending. To remove the background color in
Outlook 2007, click 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) Control The Order Pages Are Printed (XL2003/XL2007)
2) Create Dynamic Formula References Using Values From Cells (XL2003/XL2007)
3) Display Your Favourite Folders On Top

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) Count How Many Times A Specific String Of Text Occurs In A Range (XL2000-XL2007)
2) Customize Your 'Open' and 'Save As' Dialog Boxes (XL2003-XL2007)
3) How To Really Delete A File

You can still find last week's newsletter here.

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

"As soon as you truly commit to making something happen, the 'how' will reveal itself." -- 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
Control The Order Pages Are Printed (XL2000-XL2007)

Like most Excel users, if your data does not fit on one page, at least as far as it's width is concerned, you've probably figured out how to force it to fit either by changing the 'Scale to Fit' option on the Page Layout tab in Excel 2007 or the Page(s) Wide option in the Page Setup dialog to '1'.

Force Excel to print one page wide

Sometimes, however, you may not want to squeeze everything on one page, either because it prints too small or because the width of your report is such that it is not practical to print one page wide.

By default, when you print a worksheet that is more than one page wide, Excel prints from the top down and then left to right.

Down then over is Excel 2007's default printing order

If your worksheet contains data that spans more than one page width you may prefer to print your report starting at the top left page, going right and then down.

Force Excel 2007 to print pages across then down

In situations like this, you can control the order in which worksheet pages are printed

1) In Excel 2007, click the Page Layout tab. In the Page Setup group, click the Dialog Box Launcher (the small arrow in bottom-right corner of group); In Excel 2003, from the File menu select Page Setup;
2) Click the Sheet tab, and in the Page Order section, choose 'Down, then over' for top to bottom printing or 'Over, then down' for left to right printing. You will see a preview of the printing direction.
Print pages across then down


Pull answers from your lists with these 45 LOOKUP formulas

Microsoft Excel Tip #2

Create Dynamic Formula References Using Values From Cells (XL2003-XL2007)

Maybe you've wondered about this, maybe you haven't, but I'll bet that once you see this you will think of ways you can use this tip.

Wouldn't it be cool if you could create a formula reference to another sheet or workbook* based on values (i.e. sheet name, workbook name or cell reference) stored in cells on your worksheet? Change the value in the cells and your formula would adjust accordingly.

That is the purpose of Excel's INDIRECT function. It reads a text string and evaluates it to create a range reference to be used in a formula.

This formula...


... uses the value in cell A1 of the current sheet as the workbook name, the value in cell A2 as the sheet name, and the value in cell A3 as the cell reference. This allows you the flexibility of changing the result returned by the formula by changing the values in any of the cells. 

Use cell values to create references in your formulas in Excel 2003-2007

Notice the use of ampersands, double quotes, single quotes and an exclamation mark to complete the formula. Everything inside the parenthesis is converted to a text string =[Book7.xls]Sheet1!B3 and evaluated as a range reference. 

Pay close attention to the single quotes before the first square bracket and before the exclamation mark -- they are easy to forget.

How to use Excel's INDIRECT function to create a formula using cell values

PRACTICE: If you want to practice this in your own workbook, copy the following formula into your worksheet and replace the underscores with your own cell references. 


The biggest drawback with the INDIRECT function is it's inability to access closed workbooks.

Also note that the INDERECT function does not work with dynamic named ranges such as in this example =OFFSET('Raw Data'!$A$2,0,0,COUNTA(AllNames)-1,1)

If you do need to use dynamic named ranges with the INDIRECT function, a possible solution is DINDIRECT, a User Definded Function (UDF) written by Dick Kusleika at Daily Dose of Excel.

101 Excel Tips revealed in this book
"I purchased your eBook and so did my boss.  Even though we are both experienced Excel user in the field of engineering and construction surveying, we find that your tips are top-notch and to the point; well above the standard fodder you find in the range of Excel books available on the market. Thank you" -- Richard J., Las Vegas, NV

Click Here to Download Excel Secrets eBook

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.

If you're not 100% satisfied, simply request a full refund from ClickBank.com

Non-Excel Tip

Display Your Favourite Folders On Top
When you look at all the folders in your My Documents folder, you probably see a hodge-podge of familiar and unfamiliar names. Often when you install a new program it will add a folder to your My Documents folder (e.g. ArcSoft ToGo, My Skype Pictures, etc...). Over time there can be dozens of subfolders created in your My Documents folders.

In most cases, you are using only a few of these folders regularly. Looking for a particular folder in this mess can be frustrating. You can make it a lot easier to find your favourite folders by moving them to the top of the My Documents folders.

You can do this by simply adding a number prefix to the beginning of each folder's name (i.e. 01. Finances, 02. Homework, 03. Recipes, etc...) and when they are sorted, your favourite folders will be on top. If you don't put the 0 before numbers 1 to 9, you folders won't sort properly when you have 10 or more folders.

Reorder your favorite folders by renaming them

This same trick can be used to force specific filenames to always be at the top of the list (i.e. 01. Chequing Account.xlsx, 02. Savings Accounts.xlsx, 03. Credit Cards.xlsx, etc...).

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