Mr. Excel, World's Foremost Spreadsheet Wizard
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
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.
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
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
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
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
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
8) For readers who have Excel 2003, the
chapter on XML will teach them how to retrieve Amazon data directly into their
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
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
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
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
Q: What future books can we expect from
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ï¿½.?
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
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
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?
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
Q: Bill, thank you again for your time, and
consideration in doing this interview.
A: ï¿½Thanks again.