Excel 2000, 2002, 2003, 2007 Tips

The Excel Addict - Help with Excel 2007

Publication Date: May 5, 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,

Francis Hayes (The Excel Addict)Thanks for joining me again. Usually I don't watch a lot of TV but lately I have been watching WAY TOO MUCH TV and losing WAY TOO MUCH sleep. The NHL playoffs are in full swing now and there's hockey on TV almost every night.

With three fellow Newfoundlanders playing in the playoffs, I'm struggling with which team to root for. Dan Cleary's (Harbour Grace, NL) Detroit Red Wings are currently playing against Ryan Clowe's (Fermuse, NL) San Jose Sharks. And if the Boston Bruins makes it through the next two rounds and wins the Eastern Conference final, it's likely that Michael Ryder (Bonavista, NL) will be playing for the Stanley Cup against Cleary or Clowe.

So does this mean that I'm also a hockey 'addict'? Well, sort of. But I usually 'get into' hockey for the playoffs (2 1/2 months) but with Excel, it's a year-round addiction.

I hope you enjoy the tips this week.

To your SUCCESS !
Francis Hayes (The Excel Addict)

If you 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) Don't Merge Cells - Center Across Selected Cells (XL2003/XL2007)
2) Selecting Special Types of Cells (XL2003/XL2007)
3) Arrange Your Files In Groups

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) Easy Keyboard Shortcut To Compare Lists (XL2000-2007)
2) Creating Cool Graphics Using Just Excel (XL2000-XL2007)
3) Seeing The Big Picture

You can still find last week's newsletter here.

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


"What we do for ourselves dies with us. What we do for others and the world remains and is immortal." -- Albert Pine

CAUTION: 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

Don't Merge Cells - Center Across Selected Cells (XL2003/XL2007)

Often you may want to have your headings centered across the top of your reports. Since Excel 2000, it appeared that the only way to do this was by using the Merge and Center option. Although Merge and Center does exactly what it says, it is also an invitation for other problems later on.

Problem is, when you merge cells in Excel, you will often find that some procedures such as selecting ranges, copying and pasting ranges are affected.

Avoid errors encountered with Merged cells in Excel 2003

To avoid these problems, if you simply want the headings centered across a range of cells and not necessarily have the cells merged, a better option would be to use Excel's Center Across Selection feature.

In Excel 2007, from the Home tab, click the small dialog launcher arrow on the bottom right corner of the Alignment group. Click the Alignment tab on the Format Cells dialog and select Center Across Selection from the Horizontal dropdown arrow and click OK.  There is no Center Across Selection command available to add to your Quick Access Toolbar.

In Excel 2003, from the Format menu select Cells. Click the Alignment tab on the Format Cells dialog and select Center Across Selection from the Horizontal dropdown arrow and click OK.

When you use Center Across Selection, the data you want to center must be in the leftmost cell and all other cells in the same row must be blank. You can apply this formatting to multiple rows. For example, selecting A1:E2 will center the value from cell A1 across A1:E1 and the value from cell A2 across A2:E2.

Center headings across columns in Excel 2003

Microsoft Excel Tip #2

Selecting Special Types of Cells (XL2003/XL2007)

At some time you've probably needed to select all of the cells in your worksheet containing formulas or all of the cells containing fixed values. Often you may need to select just the blank cells in a range (a great way to delete all blank rows in a range). If, up to this time, you have resorted to manually selecting these cells, you're going to love this tip and will probably wish that you had known about this years ago.

How to select blank cells, formula cells, comments, conditional formats, etc...in Excel 20073With Excel, there is an easy way to select various types of 'special cells'. Unfortunately, like many other things in Excel, most people are not aware of this because it so well hidden.

In Excel 2007, on the Home tab, click the Find & Select option in the Editing group (far right) and choose the Goto Special option. In Excel 2003, from the Edit menu choose the Go To option, then click the Special button at the bottom of the Go To dialog. 

HOT TIP: A quicker way to display the Go To Special dialog in both Excel 2007 and 2003 is by pressing the F5 key and then clicking the Special button.

In the Go To Special dialog, choose an option for the specific type of cells you want to select and click OK. Excel will select the cells matching your choice from the current selection.
If no cells match your choice, Excel will give you an error message. 

To limit the selection to a specific range, first select that range. Otherwise, the selection will be applied to the entire sheet.

Once you have selected the cells, if you need to move the active cell around the selected cells, press ENTER (down), SHIFT+ENTER (up), TAB (right) or SHIFT+TAB (left).

For an explanation of the other available options on the Go To Special dialog, take a look at this page on the Microsoft website.

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

Arrange Your Files In Groups

When you view files in Windows Explorer or My Computer, they are usually arranged in alphabetical order. You probably also know that in Detail view (View, Detail) you can click the column headings (i.e. Type, Size, Date, etc...) to sort your files by that column. Clicking the column heading a second time will sort the files in reverse order.

What you many not know is that you can also arrange the files into groups. How they are grouped depends on what sort order you last used. The default order is filenames grouped by letter. All filenames starting with A are grouped, filenames starting with B are grouped, etc..

Arrange My Computer files in groups

To arrange files into groups, right click any empty space on the window's contents pane, click Arrange Icons By and click Show in Groups. To change the way the files are grouped, either click a column heading or right-click again in any empty space on the window's contents pane, click Arrange Icons By and choose Name, Size, Type, or Modified or other available option.

Note that grouping affects only that specific folder. This is a good thing because there may be some folders where you want files grouped by age, others grouped by filesize, etc... To remove grouping from a folder, right-click 
an empty space on the window's contents pane and remove the checkmark form the Arrange Icons By option.

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