You may be interested to know that I ... - The Excel …

1 You may be interested to know that i designed the entire front cover of this book with just Excel by using Print Screen, cropping, resizing, gradient background fills, transparent fills, Word Art, text boxes, and a lot of other cool tricks that I've discovered over the years. After all, I am an Excel Addict'. You don't think I would pay a designer to do it, do you? Francis J Hayes The Excel Addict Francis J Hayes Please note that the tips contained in this eBook are primarily for use with Excel 2003. Most will also work with Excel 2002 and Excel 2000. However, these tips have not been tested in Excel 2007 or Excel 97. Please read the following very carefully. DISCLAIMER AND TERMS OF USE AGREEMENT. You have permission to share this preview copy' of 101 Secrets from a Microsoft Excel Addict' as long as it remains unedited. No additional part may be copied, or changed in any format, sold, stored on any website or used in any way other than what is outlined under any circumstances.

2 This e-book is copyrighted by Francis J Hayes and contains material protected under International and Federal Copyright Laws and Treaties. Any unauthorized reprint or use of this material is prohibited. The author and publisher of this e-book have used their best efforts in preparing this material. The author and publisher make no representation or warranties with respect to the accuracy, applicability, fitness, or completeness of the contents of this e-book. The author and publisher disclaim any warranties (express or implied), merchantability, or fitness for any particular purpose. The author and publisher shall in no event be held liable to any party for any direct, indirect, punitive, special, incidental or other consequential damages arising directly or indirectly from any use of this material, which is provided "as is", and without warranties. Francis J Hayes 101 Secrets of a Microsoft Excel Addict All Rights Reserved 3. Introduction By June of 1992 I had been working with SuperCalc3.

3 Spreadsheets on a MS-DOS computer for 5 years. I had always been a person who constantly looked for faster and easier ways to do things, so using SuperCalc 3 was a frustrating experience for me because I knew there had to be a better way. When the company I worked for gave us new computers with Microsoft Windows and Excel , I thought I had died and gone to heaven. It seemed to me, at that time, everything I had ever wanted a spreadsheet to do was designed right into Excel . I was so excited that eight hours working with Excel at my day job wasn't enough. I spent hours more each night at home learning all of the amazing new capabilities Excel was offering me. I read every magazine and book I could get my hands on to discover Excel 's secrets. Within a few months I was developing workbooks that , to my co-workers, worked like magic. I had discovered, and was using, hundreds of shortcuts and tricks that helped me get hours and hours of work done in mere minutes.

4 I wanted to share my new discoveries with everyone, but surprisingly to me, not everyone around me was as excited about Excel . Most preferred to do things their own way. They were too busy to learn how to do things more efficiently. In 2003, I decided to share my knowledge and excitement with other Excel users by creating my own website and newsletter. Since then I have helped thousands of Excel addicts' in more than 100 countries around the world. Six versions later, (I skipped Excel 2002), I get just as excited every time I sit down to work with Excel . I still meet a lot of Excel users who prefer to figure it out on their own, but I know there are millions more Excel users out there who would love to know these shortcuts. I get a lot of enjoyment out of helping people who are just discovering how exciting this amazing software program can be once you learn its secrets'. If you are new to Excel , or even if you have been using it for years, I'm sure that in my book you will discover many new and exciting ways of doing things that you either thought couldn't be done or hadn't even thought of doing.

5 I hope this book will get YOU as excited about Excel as I am, even after 15 years. Francis J Hayes The Excel Addict Francis J Hayes TABLE OF CONTENTS. Preview Tips 1. Let Excel Read To 9. 2. The Quick And Easy Way To Move Rows and 10. 3. Put Pop-Up Pictures In Your 11. 4. Put Checkmarks In Your Cells .. 12. 5. Create Cool 3D Effects In Your 13. The following tips are available in the full version of this ebook. 6. Fonts In Any Size You Want 7. Quickly Enter Duplicate Formulas 8. Combine Text And Numbers From Different Cells 9. Instantly Move To The End Of A Long Column 10. Make Your Spreadsheet Fit Your Screen 11. Hide Duplicate Values In A List 12. Quickly Select All Blank Cells In A Range 13. Turning The Tables 14. Recover A Deleted Sheet 15. Print Your Spreadsheet On A Specific Number of Pages 16. Dialog Box Tip 17. Quicker Data Input Using 'Pick From List'. 18. Entering Data Into Cells In A Specific Order 19. A Double-Click Fill Handle Trick 20.

6 Move A Little Closer 21. Create A Table of Contents For Your Workbook 22. Fill Handle Secret Revealed 23. Easily Move Sheets Between Workbooks 24. Turn Off that Annoying Paste Options Button (XL2003). 25. Prevent Automatic Creation Of Hyperlinks 5. Francis J Hayes 26. AutoFill Your Own Custom Lists - Cool! 27. Make Your Worksheet Look Less Like A Worksheet 28. View Two Parts Of Your Worksheet Simultaneously 29. Give Me The Full Menu! 30. Create Dynamic Row Numbering 31. Sum Only Data that Meets A Condition You Specify 32. Cycle Font Color Tool Trick 33. Floating Colors 34. A Solution To The Last Used Cell' Mystery 35. A Neat Trick To Copy Formulas And Maintain Their Original Cell References 36. Cutting Corners On Your Text Boxes 37. Filter Your List With One Click 38. Delete Those Pesky HTML Objects From Pasted Web Data 39. Zoom Out For A Bird's-Eye View 40. Supress #DIV/0! Errors 41. Quickly Increase Your Value 42. Force Your Worksheet To Print Only One Page Wide 43.

7 A Simple Trick To Replace A Formula With Its Value 44. Link a Text Box to Data in a Cell 45. Quickly Sum Non-Contiguous Cells 46. Display A User Input Message In A Cell 47. The Extend Selection Mystery 48. Formulas To Show The Path, Filename and Sheet Name 49. Extract Unique Items From A List 50. Align Objects With Cell Borders 51. Quickly Enter The Months Of The Year 52. F4, One Of Excel 's Best Kept Secrets 53. Format Only Part Of The Text Within A Cell 54. A Quicker Way To Move or Copy Cells 55. Hide A Sheet Tab 56. Getting Rid of Those #$@& Spaces In Imported Data 6. Francis J Hayes 57. Print Just A Small Section Of Your Worksheet 58. Quickly Convert Positive Values To Negative 59. Fonts and Cell Backgrounds In Every Color Of The Rainbow 60. Print Your Company Logo At The Top Of Each Sheet 61. AutoShape Perfection 62. No More Counting Characters 63. Quickly Access A Calculator From Excel 64. Create A Keyboard Shortcut For Windows Calculator 65.

8 Advanced Windows Calculator 66. Use Your Keypad To Operate Windows Calculator 67. Have Multiple Print Areas On The Same Worksheet 68. Custom Sort Your Data 69. How To Subtotal Your List 70. Make Your Subtotals Really Stand Out 71. Sort Your Data Sideways 72. Move The Active Cell Within A Selection 73. Return The Day Of The Week From A Date 74. Change The Font in Your Footer or Header 75. Easily Convert Formula Results To Fixed Values 76. This Tip Is Wheel Cool! 77. Make Your Data Really Stand Out With Linked AutoShapes 78. Paste Everything Except Borders 79. Quick Solutions with Goal Seek 80. Create A Linked Picture Of A Cell Range 81. What Column Number Is It? 82. Prevent Unwanted AutoFilling 83. Convert Numbers With Minus Sign On Right-Hand Side 84. Change Excel 's Default Open/Save To Location 85. Create Bullet Lists in Excel ? Yes, You Can! 86. Avoid Vertical Alignment Problems with Wrapped Text 87. Can't Find that Recently Used File?

9 7. Francis J Hayes 88. Change Excel 's Default Font 89. Create Cell Comments Of Various Shapes 90. Formula To Calculate Last Day Of The Month 91. Easily Clear Just Your Input Cells 92. The Fastest Way To Select A Block Of Cells 93. Create Your Own Custom AutoFilter Toolbar 94. Copy Just The Subtotal Rows 95. Display Your Numbers As Thousands 96. Create A Running Total' Formula 97. Create A Custom Menu For Frequently Used Workbooks 98. Use Wildcards To Speed Up Your Auto Filtering 99. Avoid Excel 's Out-Of-Control Scrolling 100. Calculate The Number Of Days, Months Or Years Between Two Dates 101. Create A Personalized Default Workbook 8. Francis J Hayes 1. Let Excel Read To You How many times have you typed information from a printed page into Excel and then needed to proofread it? After a while, looking back and forth from your monitor to a printed document can make you 'blurry-eyed'. If you were lucky, you may have had someone to read the data from the document back to you so that you could verify it on the screen.

10 There is a really cool feature in Excel (2002 and 2003) called Text to Speech that can read back information from cells in your worksheet. I know , you're going to love this! Note that 'Text to Speech' may not have been included when Excel was installed on your computer, so you may need your Excel /Office installation CD. You will need a computer with a sound card and speakers for Text to Speech' to work. Here's how it First, you need to display the 'Text to Speech' toolbar. From the Tools menu select Speech and then click Show Text to Speech Toolbar. If the Text to Speech' toolbar gets in your way, you can move it around by dragging its header. To play back cells: 1. Select a range of cells you want read back to you (numbers or text). 2. Click the Speak Cells tool (the one with a text balloon and arrow). 3. As each cell is read, it is highlighted. If you find an error in your data, you can click the Stop Speaking tool on the toolbar.