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.
Hi fellow 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.
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.
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.
Automatically Flag Duplicate Values On Input (XL2000-XL2007)
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.
the range of cells where you want to prevent duplicate values (e.g.
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.
Press the F5
key on your keyboard;
button on the Goto dialog;
3) In the
corner of the dialog you will see a Data
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.
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.
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)
1) 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 Macro, Record 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...
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
b) From the 'Choose commands from'
dropdown, select Macros;
c) From the list of macros select 'PERSONAL.XLSB!CenterAcrossSelection'
and click the Add>>
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.
a) Right-click on the toolbars and select Customize;
b) On the Customize dialog, click the Settings
c) Scroll down the Categories list and
d) From the Commands list, drag the 'Custom Menu Item'
using your left mouse button and
d) Right-click the item you
dropped on your toolbar and a shortcut menu will pop
up. Change the Name to Center Across
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).
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.
OK, so that took quite some work to set up but I think you'll find that it is well worth the effort.
Try out the
secrets in my
ebook for two months. If
they don't save you 10
the price of the book... If you're not
request a full
refund from ClickBank.com There's
absolutely no risk
If you're not 100% satisfied...
request a full
refund from ClickBank.com
There's absolutely no risk to you.
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.
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