you may be interested to know that i designed the entire ... · pdf fileyou may be interested...

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 www.TheExcelAddict.com

3

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.

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

Introduction

By June of 1992 I had been working with SuperCalc3 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 4.0, 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. 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.

I hope this book will get YOU as excited about Excel as I am, even after 15 years.

Francis J Hayes The Excel Addict


5

TABLE OF CONTENTS

Preview Tips 1. Let Excel Read To You................................................................................................... 9

2. The Quick And Easy Way To Move Rows and Columns........................................... 10

3. Put Pop-Up Pictures In Your Cells.............................................................................. 11

4. Put Checkmarks In Your Cells .................................................................................... 12

5. Create Cool 3D Effects In Your Cells.......................................................................... 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. 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


6

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. 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


7

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. 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?


8

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


9

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.

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 works...

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.

There is also an option to have cells read back to you as you type them. It's called Speak on Enter.

To use Speak on Enter:

1. Click the Speak on Enter tool on the 'Text to Speech' toolbar.


10

2. Enter data in your worksheet. Each time you press Enter, the computer will read back the data from that cell.

You'll see two more tools on the toolbar to select whether cells are read across or down.

Now that's what I call a cool Excel tip. Don’t just read it. Go ahead and try it!

2. The Quick And Easy Way To Move Rows and Columns

This is one of those really great tricks I discovered after years of doing it the hard way. Not only has this shortcut allowed me to eliminate the frustrating process I used before, but it has also saved me a lot of time over the years.

For many Excel users, moving rows or columns involves the tedious process of inserting, cutting, pasting, and deleting. I want to share with you a quick and easy process for moving rows and columns that works like magic.

1. First, select the rows or columns you want to move (must be a contiguous selection).

2. Move your mouse pointer to the edge of the selection until it changes from the white cross to an arrow pointer.

3. Hold down the SHIFT key, then hold down your left mouse button and drag the rows or columns to the new position. You'll see an insertion bar between the two rows or columns you are pointing to.

4. Release the mouse button before you release the SHIFT key and the rows or columns are moved from their original location without overwriting any data.


11

WARNING: If you let up on the SHIFT key before you release the mouse button Excel will try to copy the selection instead of move it and will ask you if you want to "replace the contents of the destination cells." Just click Cancel and try again.

Go ahead and try it. This one really is a big time saver and so easy once you get the hang of it.

3. Put Pop-Up Pictures In Your Cells

Here's a cool trick. Cell comments are not just for text. You can also display pictures in your cell comments. Place your mouse pointer over a cell and a picture will pop up. I'm sure you’ll think of many new uses for this trick? Your lists of flowers, cars, people, houses, etc... will come alive with 'pop-up pictures'.

Here's how you do it...

1. Right-click a cell and select Insert Comment to add a new comment or Edit Comment to change an existing comment.

2. Clear any text from the comment.

3. Click on the border of the comment (or press ESC), to select it.

4. From the Format menu select Comment

5. On the Colors and Lines tab, click the first drop-down arrow for Color.

6. Click Fill Effects

7. On the Picture tab, click Select Picture... **


12

8. Browse to and select a picture file from your computer.

9. Click Insert, click OK, click OK.

** Caution: The size of your workbook will increase as you add more pictures to your comments. If you plan to use a lot of pictures, I recommend that you first resize your picture files using an image editing program such as Photoshop or IrfanView (www.irfanview.com).

4. Put Checkmarks In Your Cells

I am often asked if there is a way to put a checkmark in a cell. The answer is Yes. There are many ways to create a checkmark in an Excel worksheet. Here are a few of them.

Depending on your version of Excel and Windows, you may not have some of these fonts.

1. = Type a lower-case a and change the font to Marlette or Webdings.

2. = Type an upper-case P and change to font to Wingdings 2.

3. = Hold down the ALT key and type 251.

4. = From the Insert menu, select Insert Symbol, scroll down and click on the checkmark, click Insert, Close, and press Enter.

5. = Hold down the ALT key and type 0252. Change the font to Wingdings.

6. = You can also place a CheckBox in your worksheet. Select View, Toolbars, Forms to display the Forms toolbar. Select the CheckBox icon and click where you want to place the CheckBox in your worksheet. You can change or delete the CheckBox label and resize/move it if you want. To make a copy of the CheckBox, hold down the CTRL key while dragging the border of the CheckBox.


13

You may want to bold and increase the font size of the first 5 options. My personal favourite is #5.

5. Create Cool 3D Effects In Your Cells

Usually my tips are meant to help you become more productive; however this one may have the opposite effect. But it's a pretty neat trick I think you may like and, as long as you don't get too carried away with it and waste too much time, a little fun won't hurt.

Today, I'm going to show you how to spice up your worksheet by creating cool 3D effects with your cell borders. Try this on a new worksheet.

1. Select a range of cells (A1:E10) and select a background color (Format, Cells, Pattern or use the 'color bucket' toolbar button). Pick a color (i.e. Light Blue) that also has a lighter and darker color available on the color palette;

2. Now select a smaller range (B3:D8) within the first range;

3. Select Format, Cells, and click the Borders tab;

4. Click the Color dropdown and select the lighter shade (i.e. Pale Blue) of the color you used in Step 1;

5. Make sure the Line Style on the bottom left of the Line Style box is selected (i.e. surrounded by a dotted border). If not, click to select it;

6. Click on the top border and the left border buttons;


14

7. Now, click on the Color dropdown again and select the darker shade (i.e. Dark Blue) of the color you used in Step 1;

8. Select one of the thicker border styles on the right side of the Line Style section;

9. Click on the bottom border and the right border;

10. Now, drum roll please ... Click OK.

Now, isn't that cool?

Have fun and experiment with this yourself. There are many variations you can use. Try a light gray background with white and dark gray borders. Apply thick borders on the left and top of the selected cells and the thin borders on the bottom and right to give a sunken effect.

You can download a sample workbook at http://www.theexceladdict.com/samples/3DEffects.xls

For immediate download of the full version of this eBook

“101 Secrets from a Microsoft Excel Addict” Please visit

http://www.TheExcelAddict.com/101secrets?p1

Feel free to email this ‘preview copy’ of ‘101 Secrets’

to all of your Excel friends and colleagues.

you may be interested to know that i designed the entire ... · pdf fileyou may be interested...

Documents