Whitelist this newsletter

The Excel Addict - Help with Excel 2007

Publication Date: February 10, 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,

Last week as I was preparing my newsletter, a snow storm was brewing. It was a pretty big storm (big enough for a Snow Day -- WOO HOO!) but not nearly as big as the monster storm that hit the US last weekend.

The storm necessitated a slight change of my plans for the weekend and some unplanned strenuous exercise.

Snow Storm Feb 2010Snow Storm Feb 2010

Here are a couple of pics outside my house during and after the storm last week.

I often get emails from readers suggesting alternate ways of accomplishing my tips. That's what I love about Excel, usually there are several ways of doing everything. It's not always practical for me to include in my tips every possible way of performing a task in Excel. So I usually show you the one(s) that I use and like -- I do often provide alternatives. You just need to find the one(s) that you like and use them.

Once in a while someone will email me to recommend a different, and sometimes better, way of doing something that I have been doing 'my way' for years. This past week, Rob Francis sent me such an email.

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) Print Pages In Any Order You Want - Part 2 (XL2003/XL2007)
2) Inserting A New Sheet Should Be This Simple (XL2000-XL2007)
3) Getting Better Google Results 

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

You can still find last week's newsletter here.

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

"Come to the edge. We can't. We're afraid. Come to the edge. We can't. We will fall! Come to the edge. And they came. And he pushed them. And they flew.” -- Guillaume Apollinaire

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
Print Pages In Any Order You Want - Part 2 (XL2000-XL2007)

Print your Excel pages in any orderIn last week's newsletter I showed you how you could tell Excel to print the pages of a large spreadsheet across and then down rather than the default down then across. That prompted a tip from one of my subscribers, Rob Francis, with another similar tip the even I didn't know.

Believe it or not, you can print your pages in any order you want. Instead of printing a report as pages 1-2-3-4, you can print pages 4-1-3-2 in that order
without having to reorganize your worksheet. "Why would I want to do that?" you ask. How should I know! It's your report!  Just kidding!

Let's just say that for some strange reason your boss asked you to print the report in that order (some bosses are weird like that). Instead of rearranging your spreadsheet and moving the data from page 4 to the top of your worksheet and moving the data from page 2 to the bottom, all you have to do is, select the areas you want to print, in the order you want them to print.

In this example you need to print pages 4-1-3-2 in that order:

Before you begin, you may want to save your workbook if you don't plan on keeping this new print order. That way, if you make any changes to the current print area, you can close and reopen your workbook without saving those changes.

Also, you may find this easier to do this in Page Break Preview mode so, in Excel 2007, click the Page Break Preview button on the bottom right of the Status Bar or, in Excel 2000-2003, from the View menu select Page Break Preview;

1) First, drag to select the range that makes up page 4;

2) Next, hold down the CTRL key and drag to select the range that makes up page 1;

3) With the CTRL key still down, drag to select page 3 then page 2;

4) At this point you can either reset the Print Area (Excel 2007: Page Layout>Print Area>Set Print Area. Excel 2000-2003: File>Page Setup) or you can tell Excel to print the selection
(Excel 2007: Office Button>Print>Print>Selection>OK. Excel 2000-2003: File>Print>Selection>OK).

This can be very useful when you sometimes want to print an ad hoc report -- maybe just small sections of your current worksheet. Remember to save your workbook before changing the Print Area if you aren't planning on keeping the new print area.

Pull answers from your lists with these 45 LOOKUP formulas

Microsoft Excel Tip #2

Inserting A New Sheet Should Be This Simple (XL2000-XL2007)

In Excel 2007, when you want to insert a new worksheet into your workbook, there is an Insert Worksheet button at the end of the sheet tabs. At first glance, this seems like a good idea, however, I don't think Microsoft put a lot of thought into it. 

When you click the Insert Worksheet button, it adds a new sheet tab at the end of the other sheet tabs. What if you want to insert the sheet between two existing sheets? You'll just have to move it to where you want it. 

What if your workbook has fifteen sheet tabs and they don't fit into the view? Neither does the Insert Worksheet button. Now if you want to insert a sheet, you have to scroll to the far right of the sheet tabs to find the button so you can click and insert a new sheet at the end, which you may then need to move back to the left.

Excel 2007 Insert Worksheet button out of view

But there are other options. 

  • You can use a keyboard shortcut (Shift+F11) to insert a sheet to the left of the currently selected sheet tab. Now, that's much more convenient. 
  • You can click Insert, Worksheet in the Cells group on the Home tab. Yes, I said the Home tab, not the Insert tab. Go figure!
  • You can right-click on a sheet tab and select Insert.

So, which one do you like?

I don't know about you, but I'd prefer a more user-friendly option.

Let me suggest another option which I use and have been using a long as I can remember.

Add the Insert Worksheet tool to your toolbar (Quick Access Toolbar in Excel 2007). But before you do that, let me recommend that you move your QAT below the ribbon so that you're mouse doesn't have to travel so far. Simply right-click anywhere on the Ribbon or QAT and select 'Show Quick Access Toolbar Below the Ribbon'.

Add the Insert Worksheet command to your Excel 2007 Quick Access Toolbar (QAT)

1) Right-click your Quick Access Toolbar (QAT);

2) Choose 'Customize Quick Access Toolbar';

3) In the 'Choose commands from' dropdown, select 'All Commands';

4) Drag the scrollbar down and find the Insert Worksheet button;

5) Click the Add>> button;

6) Use the Up/Down arrows to position the button in your QAT. I prefer to place my Insert Worksheet button on the left side of my QAT.

7) Click OK.

Now you have an easy, one-click option to insert a new sheet into your workbook and a keyboard shortcut (Shift+F11).

If this is your first time adding a command to your QAT, now that you see how easy it is, you can use these same steps to add other commands that you'd like to have quick access to.

For Excel 2000-2003 users, you have basically the same options and limitations for inserting a new sheet - except for the Insert Worksheet button at the end of your sheet tabs.

You can also add the Insert Worksheet button to your toolbar, making inserting new sheets just a simple one-click trick.

1) From the Tools menu select Customize;

2) In the Customize dialog, select Insert from the Categories list;

3) In the Commands list (right side of dialog), select the Worksheet command ;

4) Drag the Worksheet command and drop it onto your toolbars;

5) Click Close.

Keyboard or Mouse Option?

Use the keyboard shortcut (Shift+F11) when your hands are on the keyboard so you don't have to reach for the mouse. And click the Insert Worksheet toolbar button when your hands are on the mouse so you don't have to reach for your keyboard.

Isn't Excel great?

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

Getting Better Google Results
Tips for better Google search resultsIt seems that no matter what you search for in Google, thousands, hundreds of thousands, or sometimes millions of matching results are found.

Google does a pretty good job at returning relevant results, especially on the first few pages, but often the term you are searching for has multiple meanings. This means that your search results, even on the first page, may be littered with irrelevant results.

You can help Google find better results by being more specific in your search terms, typing the most important words first and putting quotes around a specific phrase you are searching for.

In cases where a word or search phrase has more than one meaning, you can tell Google to exclude results for the irrelevant meaning by typing a minus sign before the word you want to exclude.

For example, if you are searching for information on BASS fish, you may also get results for music, shoes, and skiing. To eliminate those results from your search, after your search term, type a hyphen each terms you want to exclude.

How to get better Google search results

This will filter out pages containing those words and give you more of what you are really searching for.

Until Google figures out how to read your mind (it may not be that far off), giving it a little help can make your searches much more productive and less frustrating

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