Jelen - Learn Excel 2007 from Mr Excel: 377 Excel mysteries solved

T his book was edited by a lot of people. Linda DeLonais provided the final technical editing. Along the way, Kat Chamberlin provided a great deal of feedback that ultimately led to this becoming a better book. During the spring of 2005, over 6,000 people took part in a free preview of the book and many readers sent in suggestions. Thanks to James Afflitto, Andres Alvear, Ron Binder, Alan Brady, Alan Brown, Phil Chamberlain, Richard Clapp, Dave Connors, Bryan Enos, Linda Foster, Margarita George, Odd Inge Halvorsen, Sue Hartman, G. Russell Hauf, Rich Herbert, Steve Hocking, Mike Howlett, David Komisar, Howard Krams, Ann Lasasso, Carl MacKinder, Al Marsella, Real Mayer, Wendy McCann, Henning Mikkelsen, Mark Miller, Mark Miller, E. Phillips, Dave Poling, Bill Robertson, Marty Ryerson, Ashokan Selliah, Don Smith, Bill Swearer, and Tim Wang for their suggestions. Suat Ozgur provided countless macros that helped me number or renumber or caption images. Freeda Roberts typed early sections of the book. Lisa Davis typed and proofed everything, including the illegible handwriting written on bumpy airplane flights. Shailander Malhotra and his team did a great job assembling the 277 topics into a meaningful book. Shannon Mattiza provided a great cover and publicity materials.

I always thank Dan Bricklin and Bob Frankston for inventing the spreadsheet in the first place. Without them, the computer industry would not be where it is today.

A good deal of this book was written on airplane flights from Cleveland to Toronto for appearances on TechTV. Thanks to the entire crew at Call For Help, including Leo LaPorte, Andy Walker, Amber MacArthur, Claudia Abate, Katya Diakow, Matt Harris, Steve Antal, Claudia Abate, Mike Lazazzera, Jenny Celly, Doug Robertson, Gregory Pilsworth, Basil Coward, Aaren Perrier, Lorraine Quirk, Hayden Mindell, Kelly Colasanti, and Malcolm Dunlop.

Tracy Syrstad managed MrExcel Consulting while I was writing this book. Wendy Kertesz handled publicity to make sure that you heard about the book. My sister Barb Jelen likely packed and shipped the book if you ordered it directly from MrExcel.com .

Thanks to Josh Jelen, Zeke Jelen, and Mary Ellen Jelen!

ABOUT THE AUTHOR

I n 1989, Bill Jelen took a job in a Finance department to maintain a very expensive reporting tool. When he discovered on day one that this new tool did not work, he began to learn how to use a $299 spreadsheet program in ways no sane person would ever think to use it. To the manager who hired him, he now wants to admit that all of the reports that allegedly came out of the $50K 4th GL reporting tool from 1989 through 1994 really were produced with Lotus 1-2-3 and, later, Excel.

Thinking he was the smartest spreadsheet guy he knew, Jelen launched MrExcel.com in 1998 and quickly learned that while he knew everything about taking 50,000 rows of mainframe data and turning them into a summary report, there were many people using Excel in many different ways. To all of the people who mailed in questions back in 1998 and 1999, Jelen thanks them for honing his spreadsheet skills. He now admits that he initially knew the answers to none of their questions, but secretly researched the answer before hitting Reply to their e-mails.

Today, MrExcel Consulting provides custom VBA solutions to hundreds of clients around the English speaking world. The MrExcel.com website continues to provide answers to 30,000 questions a year. In fact, with 135,000 answers archived, it is likely that the answer to nearly any Excel question has already been posted on the websites message board.

Jelen enjoys getting out to teach a Power Excel seminar. There are so many features in Excel, that Jelen has never taught a seminar without learning something new from someone in the audience who reveals some new technique or shortcut. Mostly, though, Jelen learns what is driving Excel users crazy. The questions in this book are the types of questions that Jelen hears over and over.

Jelen is the author of seven books on Excel and OneNote. You can see him regularly on TechTV Canada. In his spare time, you will find him promoting literacy causes as president of his local Rotary Club. He is the founder of the Fresh Writers Books program, encouraging high school students to pursue literary careers.

He lives outside of Akron, Ohio with his wife Mary Ellen, sons Josh and Zeke, and two dogs.

AFTERWORD

T here you have it 277 problems and their solutions. Hopefully, you have found many that will make your experience with Excel far more efficient. Undoubtedly, you will still run into problems that are not in this book. Just as when I am teaching the Power Excel class, every new class brings new students with new problems. I invite you to send your problems to NotInTheBook@MrExcel.comIll try to get you an answer and your question might end up in the next edition of this book!

SHOW FULL MENUS ALL THE TIME

Problem: You are trying to learn Excel. Microsoft only shows you the most common menu choices under each menu.

shows a menu from one computer on my desk.

is a screenshot from another computer on my desk.

A screenshot of the data menu of the third computer on my desk is seen here in .

This is maddening. How can you learn that there is a Sort option under the Data menu if Excel will not show you all of the options? Furthermore, Microsoft customizes the menu on the basis of items you use regularly.

First, this is insane; you cannot learn to regularly use something if you dont know it is there. Second, when you go to a new computer, one of the options that you think should be there may not show up. It is very difficult to learn Excel when the menus are not consistent across computers.

Strategy: Turn off Adaptive Menus. This will show you the complete menu every time that you access that menu. Follow these steps:

1. Go to the Tools menu, as shown in .
   
2. If the Customize option is not shown, select the double-down arrow at the bottom of the list in order to expand the menu.
3. Select Customize from the Tools menu, as shown in .
   
4. As you will see in , there are three tabs across the top of the Customize dialog. Choose the Options tab and then choose the box for Always Show Full Menus.
   

Additional Details: If you are in the process of learning Excel, it also helps to choose the Show Standard and Formatting Toolbars on Two Rows option. This will enable you to see all of the icons on the important Standard and Formatting toolbars. In Excel 2000, this setting appears as Standard and Formatting Toolbars Share One Row. In that version, you will want to uncheck the option.