Excel 2000, 2002, 2003, 2007 Tips

The Excel Addict - Help with Excel 2007

Publication Date: May 12, 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)After almost a month of nightly hockey, things are starting to wind down in the National Hockey League Playoffs. So tonight I get a night away from the TV - and some badly needed sleep.

Chicago Black Hawks and San Jose Sharks made it to the semi-finals in the West and Montreal Canadiens in the East are waiting to see who they will play, Boston Bruins or Philadelphia Flyers. Then it's almost another month of hockey before the winner of the Stanley Cup is decided. Gotta love ice hockey in June - especially in San Jose, California!

Just a heads up for you. My tips this week are longer than usual, so if you don't have the time right now, you may want to print them for later. Printing these longer tips will make it easier for you to follow along so you don't miss any of the steps.

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) Automatically Flag Duplicate Values On Input (XL2000-XL2007)
2) Bring Back The 'Center Across Selection' Tool (XL2000-XL2007)
3) What Time Is It There?

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

You can still find last week's newsletter here.

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


"Success is not the key to happiness. Happiness is the key to success. If you love what you are doing, you will be successful." -- Albert Schweitzer

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

Automatically Flag Duplicate Values On Input (XL2000-XL2007)

Preventing duplicate values from being entered in a range of cells may be a requirement in some of your worksheets. Although not obvious, enabling this capability in Excel is very easy.

Here's how...

Restrict values intered in a range in Excel with Data Validation1) Select the range of cells where you want to prevent duplicate values (e.g. B3:B20);
2) In Excel 2007, on the Data tab click Data Validation in the Data Tools group. In Excel 2000-2003, from the Data menu select Validation;
3) In the Data Validation dialog, in the Settings tab click the Allow dropdown and select Custom from the list. A Formula field will appear;
4) In the Formula field type =COUNTIF($B$3:$B$20,B3)<2. When data is entered into a cell, this formula is evaluated. If the formula evaluates to TRUE, the value entered is accepted. The formula in this example says, count how many times the Active Cell's value appears in the range B3:B20. If it is less than 2, allow it, otherwise, display the Error Alert message. In this example, B3 is the Active Cell. You will need to adjust your formula as required. It is critical that you use Absolute referencing for your validation range (e.g. $B$3:$B$20) and relative referencing for your Active Cell (e.g. B3);
5) In the Data Validation dialog, click the Input Message tab and, if desired, type a message that will be displayed when the user selects a cell within the range of the Data Validation rule. Otherwise, if you don't want a message to appear every time someone selects a cell in the range, clear the 'Show input message...' option.
6) Click the Error Alert tab and type the message that will be displayed if the user enters a duplicate value in the range;

Data Validation Error Alert for invalid data entered in a cell

 7) Click OK.
Now test your Data Validation rule by entering values, including some duplicates, within the range.
To edit or delete existing validation rules, you start by selecting a cell within the validation range. Note that the formula in the Data Validation dialog will now be relative to the currently active cell. To ensure that the rule is applied to all of the cells in the validation range, select the 'Apply these changes to all other cells with the same settings' option before clicking OK.

After you have applied Data Validation rules to your worksheet, you may not remember which cells the rules have been applied to. Thankfully, there is an simple method to find the Data Validation rules in your worksheet.
Finding ranges containing Data Validation:

Easily find Data Validation ranges with Goto1) Press the F5 key on your keyboard;

2) Click the Special button on the Goto dialog;

3) In the bottom right corner of the dialog you will see a Data Validation option. Click All to select all Data Validation ranges in the worksheet or Same to highlight just the cells that have the same Data Validation rules as the active cell.
There is one major drawback when using Data Validation. It does not stop a user from pasting invalid data into the range. When pasting into a cell containing Data Validation, the validation is removed because the cell's formatting is overwritten. For the most part, however, Data Validation is very useful for preventing invalid entries.

And as you can see in the Settings tab of the Data Validation dialog, there are many other ways to apply validation to a range.

Microsoft Excel Tip #2

Bring Back The 'Center Across Selection' Tool (XL2000-XL2007)

Pour yourself a coffee. This one is going to take some effort but it will be worth it.

After last week's tip on 'Center Across Selection', I received several emails asking about adding a 'Center Across Selection' tool to the Ribbon or toolbar. So here it is...

If you started using Excel after Excel 95, you probably are not aware that there once was a toolbar button called Center Across Selection that allowed you to center text from one column across a range of columns without requiring the cells to be merged. This was a very useful tool, especially for centering report headings. But for some reason, beginning with Excel 97, Microsoft saw fit to replace this tool with a Merge and Center button. 

On the surface they both seem to perform the same function but, if you have ever used Merge and Center, you have probably discovered that merged cells can often be troublesome. Merged cells can often interfere with inserting or deleting cells, pasting, selecting, etc... 

Oh sure, you can still access the old Center Across Selection function by pressing CTRL+1, selecting the Alignment tab, selecting 'Center Across Selection' from the Horizontal alignment dropdown. But that is much harder than simply clicking a button on your Ribbon or toolbar.

This week I am going to show you how you can use a simple macro to bring back that long-lost, one-click Center Across Selection tool to Excel.

Step 1: First you need to determine if you already have a Personal Macro Workbook

If you're not sure if you have a Personal Macro Workbook, choose Window, Unhide in Excel 2000-2003 or View, Unhide in Excel 2007. If PERSONAL.XLS (Excel 2000-2003) or PERSONAL.XLSB (Excel 2007) doesn't appear in the Unhide dialog, you will need to create one. Click Cancel to close the Unhide dialog.

Step 2: Create a Personal Macro Workbook (if required)

Record macro in Excel 20071) In Excel 2007, if you don't see the Record Macro button on the left end of the Status Bar (next to Ready), click the Office Button, click Excel Options (bottom of dialog), select the third option 'Show Developer tab in the Ribbon' and click OK. Click the Record Macro button. In Excel 2000-2003, from the Tools menu select MacroRecord New Macro;

2) In the Record Macro dialog, type 'dummy' for the macro name, select Personal Macro Workbook from the 'Store Macro In' dropdown and click OK. In Excel 2007, the Record Macro button will change to a Stop button. In Excel 200-2003, a small floating Macro toolbar will appear;

3) Click the Stop Recording button. Excel has just created a Personal Macro Workbook and recorded a 'dummy' macro.

Step 3: Enter the 'CenterAcrossSelection' macro code in the Personal Macro Workbook

1) If you just created a Personal Macro Workbook, press ALT+F8 to open the Macro dialog. You should see listed the 'dummy' macro you previously recorded. Select the 'dummy' macro from the list (i.e. either PERSONAL.XLSB!dummy in Excel 2007 or PERSONAL.XLS!dummy in Excel 2000-2003)

If you already had an existing Personal Macro Workbook, press ALT+F8 to open the Macro dialog and select any macro from the list of PERSONAL macros;

2) Click the Step Into button. The Visual Basic code window will open with the first line of the macro highlighted in yellow;

3) From the Run menu click Reset;

4) Select and copy this macro code...

Sub CenterAcrossSelection()
    With Selection
        .HorizontalAlignment = xlCenterAcrossSelection
    End With
End Sub

Excel Visual Basic code window... and, if you recorded a 'dummy' macro, paste it over (i.e. replace) the 'dummy' macro code or if you didn't record a 'dummy' macro, paste it below one of the End Sub lines in the code window.

5) Now, in the Visual Basic window click File, Save and close the window.

Step 3 Add the Center Across Selection macro button to your toolbar

For Excel 2007:

a) Right-click on the Ribbon and choose 'Customize the Quick Access Toolbar';

b) From the 'Choose commands from' dropdown, select Macros;

c) From the list of macros select 'PERSONAL.XLSB!CenterAcrossSelection' and click the Add>> button;

d) Select CenterAcrossSelection on the right side of the dialog and click Modify;

e) Choose a image to use on the QAT and click OK, then OK.

Center Across Selection button on the Quick Access Toolbar

For Excel 2000-2003:

a) Right-click on the toolbars and select Customize;

b) On the Customize dialog, click the Settings tab;

c) Scroll down the Categories list and select Macros;

d) From the Commands list, drag the 'Custom Menu Item' using your left mouse button and 
drop it on one of your toolbars;

d) Right-click the item you dropped on your toolbar and a shortcut menu will pop up. Change the Name to Center Across Selection (or CAS for short). Also on the shortcut menu, click Assign Macro;

e) In the Assign Macro dialog, select your CenterAcrossSelection macro and click OK. (If you don't see the macro listed, click the dropdown and choose Personal Macro Workbook).
Step 4: Test Your 'Center Across Selection' macro

1) Type something in a cell;

2) Select that cell and highlight several cells to the right;

3) Click your CenterAcrossSelection toolbar button;

The value in the left cell should now be centered across the range you have selected.

Center text across columns in Excel 2007 

OK, so that took quite some work to set up but I think you'll find that it is well worth the effort.

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

What Time Is It There?

In the past, when I needed to know what time it was in some other part of the world, I would go to TimeAndDate.com and search for the city or country.

Then I discovered that I could simply enter the word 'time' and the place name (e.g.. time montreal) into the Address bar or Search bar in Internet Explorer or Firefox and I will immediately be shown the current time for that location.

In Internet Explorer, I have Google set as my default search provider, so my time query in the Search bar or the Address bar takes me to Google. In Firefox, a time query in the Search bar brings me to Google however, searching from the Address bar finds the time at TimeAndDate.com. If you are using a different browser or have different search settings you may get different results.

Regardless, this is a great way to quickly find out the current time anywhere in the world.

Instantly find out what time it is anywhere in the world

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