CIPS Connections

Mr. Excel, World's Foremost Spreadsheet Wizard
Interview by Stephen Ibaraki, I.S.P.

This week, Stephen Ibaraki, I.S.P., has an exclusive interview with Bill Jelen.

Bill Jelen is the acknowledged world's foremost spreadsheet wizard, widely known as "Mr. Excel”. His web site is the premier source for Excel tips and solutions (www.MrExcel.com) with more than 10 million page views per year.

Bill saves companies millions of dollars with his Excel programming solutions and self-help resources (books, website, CD's, message boards). As an example, he co-developed  "F&I Menu Wizard", a proprietary program for car dealerships that simplifies and speeds the finance and insurance process and increases dealerships' profit per vehicle by an average of $225.

As a best-selling author, his book and CD credits include "Mr. Excel On Excel," "Guerilla Data Analysis Using Microsoft Excel," "1900 Excel VBA Examples," and "Excel Knowledge Base." His recent release, “VBA and Macros for Microsoft Excel” (Que) is gaining widespread praise.

Discussion:

Q: Bill, we are very fortunate to have you with us. Thank you!

A: Stephen, thanks for having me here.

Q: How did you get your start into computers, programming, and finally Excel? Detail the many lessons you have learned, the influence of your business education at Notre Dame and the mentoring of Dr. Khalil Matta, a professor of Management Information System.

A: My first experience with spreadsheets was while I was a teaching assistant for Dr. Matta. He was in the midst of a research project to see how MBA students best learned about spreadsheets. Students would take one of two options for studying about Lotus 1-2-3 and then were subjected to the world’s hardest Lotus test. After a semester of grading the tests, I realized that none of the MBA candidates were scoring above 60%. I realized that if I learned the meaning behind every answer, I would be the smartest spreadsheet guy alive – or, at least the smartest guy in the computer lab.

From Notre Dame, I went on to a job as a COBOL programmer. Dr. Matta had taught us that the ND education was only to be used to get our foot in the door. Within two years, we should aspire to jump to better jobs. Sure enough, after two years of COBOL, I found myself in the finance department.

I never lost the ability to program. As our lives in Finance revolved around spreadsheets, I began writing macros to improve our spreadsheet applications.

Q: Please share two surprising experiences.

A: I remember the day that I walked in to my manager’s office, only to find him entering formulas over and over in each cell of a spreadsheet. With a little additional knowledge, he could have used the spreadsheet’s absolute reference feature to enter one formula and copy it down to 500 rows. Here was a senior manager, a CPA, wasting all of this time because he had never learned this one basic trick about spreadsheets. Since this guy was making twice my salary, I didn’t have the heart to tell him the trick that day. Later, a high-priced consultant from McKinsey and company saw my manager doing the same thing and corrected his ways.

I was also amazed to learn how quickly Excel VBA could be used to build a prototype system. While I was still at my Telxon job, a group of us sat around a computer one morning and in two hours built a prototype system in Excel using VBA. We started using the system the next day and over the course of some upgrades, it eventually grew to 2500 lines of code and completely replaced 40 hours of manual work each week.

Q: Can you share with us two humorous stories?

A: A lady in the marketing department at work came back from a trade show. She had a list of 5000 prospects supplied by the trade show. The list was in Excel, with the first name in column A and the last name in column B. She needed these columns joined into a single column. Not knowing any better, she began typing “John Smith” in column C.  After about an hour someone walked by. She had done about 300 of these by hand. The spectator tells her “there must be a better way – call Bill Jelen from upstairs”. I walked in, showed her one formula, and copied it down the list. Instantly, all 5000 names were done. I am not sure if she was more upset that she had wasted two hours or relieved that she wouldn’t be typing for the rest of the day.

When I started MrExcel.com, the original model was that people would send me questions and I would send the answers. After I started getting more questions each day than I could reasonably handle in my spare time, I started the message board at MrExcel. The concept was simple – if you had a question, post it to the board. If you were at the board and knew an answer, post the answer.  A few months later, I had backed myself into a corner with a client. I was at a loss for a solution.  So – I went to my own message board and posted the question – anonymously.  Sure enough, 4 hours later a fellow named Ivan from New Zealand came along and posted the answer. I was so amazed at how this resource had saved me. Today, it is much faster. Volunteers answer over 30,000 questions a year, often in minutes from when they are posted.

Q: You received two patents while at Telxon. Describe what inspired your innovations and the patent process.

A:  It was an intense hatred of the competition! In the accounting department, I was responsible for calculating a monthly royalty that was paid to our competitor. I could see this patent royalty was giving our competitor a significant cost advantage. I dreamt up the first patent, hoping it would catch on and that our competitor would have to start paying us some royalties. The second patent was really ahead of it’s time. At the risk of sounding like Al Gore, I really have the patent for ordering groceries over the Internet. Of course, it is now in the patent portfolio of the dreaded competitor, as they ended up buying Telxon in the end.

Q: Can you give us four real-world case studies, where Excel has provided effective solutions?

A: 1) There was a government agency in Australia. Each month, they were importing Oracle data to Excel and producing a series of 46 reports. It was taking a week of labor to produce these reports. Each report was a lot of work, but all 46 reports were similar (one per department). I wrote a macro to automate the creation of one report, buried that macro in a loop and presto – the 40 hours process took less than a minute.

2) Companies are swimming in data, but it is often too much data. One of my clients has a chain of retail stores. Every day, the third party cash register vendors provides statistics about what is selling in 50 stores x 3000 items. This is 150,000 rows of data – too much to be useful. I wrote an application in Excel that imports the records, finds the top 10 items in each category, then displays pictures of them for the buyers. The buyers can focus on the important items and place orders. All of this is done in Excel.

3) Think about the last time you bought a car. After you sign the deal with the salesman, you are pretty excited to take delivery of the car. But, the last stop is in the finance office. This guy has to run all sorts of numbers for the bank and insurance companies. It is often a 50 minute process and a real pain in the neck when you are trying to get out the door with your car. We wrote an application in Excel. The finance manager enters a few numbers from the deal – it takes perhaps 60 seconds, hits a button and instantly prints a sheet with all the options. We took a 50 minute process and turned it into a 10 minute process. The customer satisfaction improved and the dealership saved money.

4) A sales force uses Excel to track orders while calling on their accounts. People don’t realize that Excel can be used to display pictures. The sales rep can pull up a picture of the item and place the quantity ordered. Excel keeps track of the total dollars ordered so that the store owner can stay within their budget.

Q: Tell us more about your web site, www.MrExcel.com. What are the most frequently asked questions and their answers?

A:  With over 80,000 answers archived, they run the gamut. Everything from the basic items such as “How do I get the column headings to print at the top of every page” (Answer: File > Page Setup> Sheet > then for example, 1:2 in Rows to Repeat at Top.) We get a lot of questions where people need to do something that they think is easy but is actually incredibly complex. It will usually require a very complex array formula as a solution.

Q: Share your top ten tips from your book, VBA and Macros for Microsoft Excel (Que).

A: 1) The macro recorder doesn’t work. There are 4 very specific shortcomings to the macro recorder. Using the macro recorder and a little bit of knowledge, people can quickly come up with usable code.

2) The Advanced Filter command is an excellent tool in VBA. No one uses this in the Excel interface because it is so hard to use. In VBA, though, it is implemented in an excellent fashion. The chapter on Advanced Filter will have you routing customized department reports to each department in your company in no time.

3) Even though “Visual Basic” and “BASIC” sound alike, they are very different. I spend all of Chapter 2 teaching those who had a BASIC class in high school how to understand this very different language.

4) The cell that most people know as cell B3 actually has two names. “B3” is the Lotus 1-2-3 name for the cell. Natively, Microsoft actually calls this cell “R3C2”.  Because so many people originally used Lotus 1-2-3, Microsoft threw in the towel and made the “B3” style of cell references appear as the default. However, in VBA, the arcane R1C1 style of references is actually far superior when you are trying to enter a 1000 formulas in a worksheet. I break the code on how to understand this style and make it your friend.

5) The book has custom user functions to remove duplicates from a range.

6) The book teaches you how to tie VBA code to certain events. You can have a little macro run every time that someone enters a cell or tries to print or save.

7) The chapter on Charts will give you ultimate control over many small settings that would be very hard to set in the user interface.

8) For readers who have Excel 2003, the chapter on XML will teach them how to retrieve Amazon data directly into their spreadsheet.

9) My friend Ivan Moala contributed a great general purpose utility that will color all of the cells in the active row and column, so you can easily find the cellpointer.

10) Finally, the most important tip for people who need several employees to access the same data. If you try to use the native “Share Workbooks” feature, most of Excel’s features are turned off.  I show you how to have an Excel workbook as a front-end that stores data in an Access back-end. This allows many people to access the data simultaneously, without losing any Excel functionality.

Q: Share your top study tips for learning Excel?

A: 1) Get a book. There are 5 ways to do anything, and the way that you discovered is likely not the fastest way.

2) Read the questions and answers at the MrExcel message board. By reading problems that are encountered by others, you will learn things that you didn’t even know that you didn’t know. Someone probably asks about resolving ties with the RANK function almost once a week. For a new user, they probably didn’t even know that there was a RANK function.

3) Excel does a lot more than just SUM.  Use the formula painter. When you find a function that looks good, hit Ctrl+A to see an excellent wizard to walk you through correctly filling out the functions.

4) Learn the difference between relative and absolute cell references.

5) Learn how to use VLOOKUP. It is the most important function.

Q: Which Excel feature is the most powerful but widely unknown?

A:  Pivot Tables are by far the most powerful feature, but are used by only 20% of the Excel users. They allow you to take thousands of rows of data and quickly produce a summary for your manager.

Q: What is the most compelling issue facing business and IT professionals today and in the future? How can it be resolved with Excel?

A: Backlog. The I.T. department has a several-month long backlog of reports. By enabling the Excel users in their organization to build a few macros in Excel, the end-user can automate their own reports and not bog down the I.T. department.

Q: What is the best resource for technology and business professionals.

A: I am biased, but the newsgroups and message boards are an excellent resource. When writing a book, I can try to cover many topics, but the odds might be that your need is slightly different than the case covered in the book. A newsgroup or message board can get you the advice you need and quickly.

Q: You pick the topic: now provide us with a valuable rare “gem” that only you know.

A: October 2004 is the 25th anniversary of the most important invention of our time: The Spreadsheet.   Actually, by the summer of 1979, Dan Bricklin and Bob Frankston were showing pre-release versions of VisiCalc at a computer show, but it was October 1979 that the first commercial copies of VisiCalc shipped.

You have to understand that before this time, there was no good reason to own a computer. You could perhaps play chess, but unless you knew how to program, there were no suitable programs for the personal computers of the day.

Bob and Dan released VisiCalc and all of a sudden, businesses had a compelling reason to go out and spend $3000 on a personal computer. This one invention drove the entire personal computer revolution. 

Q: What future books can we expect from you?

A:  I am currently working as a co-author of “Life on OneNote”, a guide to the best new component of Office to come out of Microsoft in years; OneNote 2003.  I am also editing a book to celebrate the 25th anniversary of the invention of the spreadsheet.

Q: What do you consider to be the most important trends to watch, and please provide some recommendations?

A: There is a lot of talk inside Microsoft about security. I am worried that by 2007, Microsoft will remove the emphasis on VBA as an automation tool inside of Office. This will require some amount of work to port VBA code to the replacement platform.

Q: What kind of computer setup do you have?

A: I am surrounded by four systems. The desktop has a machine with Excel 2000, my production machine with Excel 2002, a beta machine with Excel 2003 and an older machine in the basement with Excel 97. A pair of wireless laptops are used for travel and road shows. None of the machines are remarkable. Other than getting a minimum of 512MB of RAM, I always buy the cheapest machine available.

Q: If you had to do it all over again….?

A:  I would have imagined the growth of the message board at MrExcel and bought faster hosting machines from the get-go.

Q: What drives you to do what you do?

A: When you can show someone a way to save a few hours a week, it is a rewarding experience.

Q: If you were doing this interview, what five questions would you ask of someone in your position and what would be your answers?

A: Q1: Is this your only job?
A1:  Yes! Being MrExcel is my only job. We employ three full time programmers, a project manager, and then several part-time people. There are 65 million people using Excel and I figure that I can help every one of them save hours per week.

Q2:  You’ve staked your entire business on one product – Excel. Is that wise?
A2:  (laughing).  Well, back in 1989 I would have sworn to you that no one would ever take over the dominance enjoyed by Lotus in the spreadsheet market. And now in 2004, I will swear to you that no one would ever take over the dominance enjoyed by Microsoft in the spreadsheet market…  I guess if I were to hedge my bets, I should register MrStarOffice.com?

Q3:  What is the strangest use that you have seen for Excel?
A3: One woman in Appalachia is using Excel to design quilts. Another man in Virginia has built a pilot training tool in Excel.

Q4: Do you have any evidence that people are not making full use of Excel?
A4: Yes – in a poll of 4000 readers at MrExcel.com, we asked how many people used the top 10 power features in Excel. 57% are not using Pivot Tables. 62% are not using the Filter command, 57% never use the automatic subtotals feature, 66% don’t use Data Validation, 71% never use array formulas, 83% never use web queries to retrieve data from the web.

Q5:  What one Excel feature should every reader try today?
A5: Try the automatic subtotal feature. It is intuitive and easy to use. Place the cellpointer in the middle of any range of data and select Data > Subtotals… from the menu. This will save more people an hour or more this week.

Q: Do you have any more comments to add?

A:  I want to thank my co-author Tracy Syrstad. Tracy is my project manager at MrExcel and a former technical writer. She authored half of our chapters and was a force in making sure that I stayed on track with my chapters.

Q: Bill, thank you again for your time, and consideration in doing this interview.

A:  Thanks again.

CIPS Connections | Archives | Search