Excel 2000, 2002, 2003, 2007 Tips

The Excel Addict - Help with Excel 2007

Publication Date: April 14, 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,

The Excel AddictI've been looking forward to the spring weather warming up so I can get back out in the garden after a long winter. The plants and flowers are coming along nicely and the buds on the trees are looking very healthy. Large numbers of robins have been dining out at my bird feeders and for weeks, the sun's rays have been feeling warmer and warmer. And then BAM!! Tonight, out of the blue, we are getting blasted by a late spring snowstorm. And IT's F-R-E-E-Z-I-N-G outside !

Living in Canada, you'd think I would be used to this unpredictable weather. But dreaming of nice warm weather is a Spring tradition in Canada...even if it is interrupted by the occasional snowfall.

Wishing you a comfortable week+end.

To your SUCCESS !
Francis Hayes (The Excel Addict)
 

If your 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) AutoFill Trick For Data Containing Blanks (XL2000-2007)
2) Remove Hyperlinks In Bulk (XL2003/XL2007)
3) Scroll Wheel Precision

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) Align Decimals In Numbers With Variable Decimal Places (XL2000-2007)
2) Justify Your Text (XL2003/XL2007)
3) Add Folders To Your Bookmarks Toolbar (IE / Firefox)


You can still find last week's newsletter here.

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

QUOTE OF THE WEEK:

"There is no chance, no destiny, no fate that can circumvent, hinder or control the firm resolve of a determined soul." -- Ella Wheeler Wilcox



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

AutoFill Trick For Data Containing Blanks (XL2000-2007)

Double-clicking the fill handle is the fastest way to fill formulas down a long column if there are no blank cells in the adjacent column. When AutoFill encounters a blank cell in the adjacent column, it will stop. 

An alternative, if this happens, is to select the first cell containing the formula to be filled down, drag the vertical scrollbar to move quickly down to the last row, hold down the SHIFT key and click the last cell you want to fill. This will select all cells from the first to the last cell. Then press CTRL+D to copy the formula to all the selected cells.

But there may be an even easier way. 

If there are blank cells in an adjacent column preventing the double-click AutoFill from working, a quick workaround is to hide the column(s) containing blank cells until you have an adjacent column that is completely filled and then use the double-click fill method. 

Don't forget to unhide your columns.

Ignore blank cells with AutoFill




Pull answers from your lists with these 45 LOOKUP formulas

Microsoft Excel Tip #2

Remove Hyperlinks In Bulk (XL2003/XL2007)

In an email I received earlier this week, Tom asked, "Is there a way to remove hyperlinks from a worksheet in bulk, so that one does not have to click on each cell to remove?"
 
Yes, there are several ways to delete hyperlinks for your worksheets. Here are a few of them.

Option 1: If you don't need to retain the cell formatting and if there are no formulas interspersed throughout the range containing your hyperlinks, you can...

1) Right-click and Copy the range containing the hyperlinks;

2) Right-click and Paste Special, Values to paste the data to another range;

Option 2: If you want to retain the current cell formatting and there are no formulas within the range containing your hyperlinks, you can...

1) Right-click and Copy the range containing the hyperlinks;
2) Right-click and Paste Special, Values to temporarily paste them elsewhere;
3) Select the original range that contains the hyperlinks and press Delete;
4) Copy the range you pasted in Step 2 and Paste Values back to the original range;
5) Then go back and Delete the temporary data from Step 2.

Option 3: If you are finding that you regularly need to remove hyperlinks from data, the best option is to add this macro to your Personal macro workbook.

If you already have a Personal macro workbook, you can copy this code into it.

Sub DeleteHyperlinksInSelection()
    On Error Resume Next
    Selection.Hyperlinks.Delete
    On Error GoTo 0
End Sub


If you're not sure if you have a Personal macro workbook... In Excel 2007, from the View tab, click Unhide in the Window group. In Excel 2000-2003, from the Window menu select Unhide. If the Unhide option is grayed out or Personal.xlsb (Excel 2007) or Personal.xls (Excel 2000-2003) is not listed in the Unhide dialog, you don't have a Personal Macro Workbook. However, you can easily create one.

Create a Personal Macro Workbook

The simplest way to create a new Personal Macro Workbook in the correct folder is to record a dummy macro.

1) In Excel 2007, on the Developer's tab, click Record Macro in the Code group. 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 and from the 'Store macro in' field, select Personal Macro Workbook and click OK;

3) Click any cell in the worksheet;

4) In Excel 2007, on the Developers tab, click Stop Recording in the Code group. In Excel 2000-2003, click the Stop Recording button on the toolbar or from the Tools menu select Macros, Stop RecordingIf you didn't previously have a Personal Macro Workbook, one has just been created;

Copy the Macro Code to the New Personal Macro Workbook

1) Select and copy the macro code above;

2) In Excel, press ALT+F8 to open the macro dialog;

3) Select Dummy from the macro list and click the Step Into button. The Microsoft Visual Basic window opens;

4) Select the code from Sub Dummy() to End Sub and press the Delete key. Click OK to the message 'This action will reset your project, proceed anyway?';

5) Press CTRL+V to paste the code into the Visual Basic Editor;

6) In the Visual Basic window, press CTRL+S or click File, Save to save the changes to the Personal Macro Workbook.

Run the DeleteHyperlinksInSelection macro

1) Select the cells containing the hyperlinks you want to delete. This range can include other non-hyperlink cells. The macro will affect only the hyperlinks;

2) Press ALT+F8 to open the macro dialog;

3) Select the DeleteHyperlinksInSelection macro and click Run.


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

Scroll Wheel Precision


Adjust scrolling with your scroll wheel mouseIf your mouse has a scroll wheel, you realize what an important tool that wheel is. To be honest, the first time I ever used a mouse with a scroll wheel was less than two years ago. It was only then that I realized what I had been missing. Once I started using my scroll mouse, I could never go back to a regular mouse. In fact, it bugs me now when I am helping someone who has a 'wheel-less' mouse.

Scrolling up and down pages is a breeze with your scroll mouse but don't you find that when you need to scroll slowly, such as when you are reading a blog or other webpage, your mouse jumps three lines with each notch of the wheel, making it more difficult to follow the text? If you'd like to fine tune your scrolling, there are a couple options available.

Option1:

There is a setting in the Control Panel for the number of lines you scroll for each notch you roll the wheel (Start > Control Panel > Mouse > Wheel). By default, this setting is 3 lines. T
o allow smoother, more precise scrolling, you can easily change this setting to 1 line. However, whenever you do need to scroll quickly, you will discover that changing this setting was not such a good idea.

What if you could have the best of both worlds? What if you could scroll quickly or slowly whenever needed? You can! That bring us to option 2.

Option 2:

Adjust scrolling with your scroll wheel mouseThis one is soooo easy. Whenever you need to scroll more precisely with your scroll wheel, simply hold down the ALT key and roll the wheel. The screen will scroll one line at a time. Without the ALT key, rolling the wheel will result in the screen scrolling 3 lines (or whatever the mouse setting is in the Control Panel) at a time.

Caveat: This does not work for all programs but it works great with FireFox.







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