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.
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.
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.
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.
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.
Print Pages In Any Order You Want - Part 2 (XL2000-XL2007)
In 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.
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.
But there are other options.
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'.
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?
If you're not 100% satisfied, simply request a full refund from ClickBank.com
Getting Better Google Results
It 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.
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
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