The Cell Master.
Please introduce your self. You may be as thorough as you wish. Feel free to include or omit any detail about yourself.
MrExcel.com is my full-time gig. We have a great staff of developers who write custom applications for Microsoft Office for clients around the English-speaking world. In addition, I publish a series of books and CDs for users of Microsoft Office under the Holy Macro! Books imprint.
I am a 1987 graduate of the University of Notre Dame with a degree in Management and a concentration in Management Information Systems.
After college, I worked as a programmer at a computer company. After 2 years in the information systems department, I transferred to the Finance department in order to assist with a brand new 4th GL reporting tool that the company had just bought. The main problem – the tool didn’t work. The VP of Finance assured me that he had just paid $100,000 and certainly the tool must work. Luckily for me, the tool could produce Lotus 1-2-3 files from mainframe data. I quickly became very proficient at downloading data to Lotus 1-2-3 and using @DSUMs and /Data Table 1 in order to produce the summary reports that they had previously hoped to come out of the 4th GL tool. I ended up working at that company, using Lotus and later Excel for 12 years.
In 1998, I was the informal “go-to” guy for any Excel questions in the company. Everyone told me that I should write a book on Excel. In 1998 our competitor launched a hostile takeover attempt of my employer. Figuring I would soon be out of a job, I launched MrExcel.com. The concept was simple – e-mail me your Excel question, I would send an answer, and the best questions might someday end up in a book. In the back of my head, I figured that if I answered 50 questions before I lost my job, then I would have 50 people with whom I could network when looking for a new job.
The hostile takeover attempt failed. I kept doing MrExcel.com part-time while I continued to work. In 1999, I launched the Message Board. Folks like Ivan, Dave and Cecilia nicely staffed the board, providing help to anyone who asked. I remember a day in 1999 when I was fairly amazed that we had managed to hit 250 page-views on one day. Today, we enjoy over 1 million page views a month.
Today, there are 32,000 registered users of the MrExcel board. Over 100,000 questions are archived and available for searching. Staffed by excellent volunteers, the board is a great resource. Obviously, with 200 million users of Office, just about all of them one day are going to run into the basic “How do I make my column headings print at the top of each page”-type question. We get some of those, but there are also some hard-core VBA and formula people who can answer amazingly hard questions. Aladin Ayurek has posted over 17,000 responses as of this date. He can do things with Array Formulas that make my head spin.
I did eventually write the book that I mentioned in 1998 on the website. It turned out to be a book about all of the powerful features on the Excel data menu. It is called “Guerilla Data Analysis Using Microsoft Excel”. I then co-authored “VBA & Macros for Microsoft Excel”. In both of these books, I remembered the pain of coming up to speed with spreadsheets or VBA. I try to place myself in the reader’s shoes, trying to remember what it was like trying to figure out this strange beast. I am the co-author of an upcoming book on Microsoft OneNote. I am also working on a very cool project that will be called “The Spreadsheet at 25 – the Evolution of the Invention that Changed the World.”
I live outside of Akron Ohio USA with my wife Mary Ellen Jelen (yes, it rhymes..) and my boys Josh and Zeke. I am fairly involved in the community, serving on the school board, the chamber of commerce, the Rotary club and other civic projects such as the Relay for Life fundraiser for the American Cancer Society.
My boys and I are fans of Drive-In Movie Theatres. We spend many summer nights camped out in lawn chairs behind the tailgate of my van, sitting under the stars and watching movies on the giant outdoor screen.
When do you remember using Excel for the very first time? Can you remember any specific details from that first time?
I first touched a spreadsheet in 1984 when I began using Microsoft Multiplan on a TI-994a. I worked at a mail-order firm that sold software for the Texas Instruments computer. I understood what a word processor was for. I remember asking about this product called Multiplan. The president of the company had a large piece of green ledger paper on his desk with numbers all over it. He told me that Multiplan would eliminate the need for green ledger paper. For the most part, he was right.
My first real experience with spreadsheets was with Lotus 1-2-3 in business school. I worked nights in the computer lab and would experiment with Lotus when we weren’t busy. One of the MBA students who worked in the lab gave me a simple macro that would let you do word processing in Lotus – it was nothing more than a bunch of Edit – Fill – Justify commands in a loop. That would definitely have been the first macro that I ever typed.
Two of the professors at Notre Dame were trying to figure out how people would learn. Their experiment was to take MBA students and have them sit through either 8 hours of Lotus videos or 8 hours of interactive laser disk. They then gave all of the students an impossibly hard exam. My job was to grade the exam. After months of blindly grading the exam and never seeing anyone get higher than 60%, I figured that if I would learn the reasoning behind every question, I would know a lot about spreadsheets.
So – I have rambled on here and still not addressed your question. The first time that I used Excel was in 1994. My employer made the decision to switch everyone over from Lotus and Word Perfect to Microsoft Office. It was Excel version 5. I was not happy. My job was to get things done quickly. I wanted the conversion from Lotus to Excel to be flawless. While most of the things transferred over pretty well, the @VLOOKUP formula from Lotus would not work in Excel. I remember running around, working on a deadline, trying to find someone who knew something about Excel. It was Adam Meyerson who said – “Oh – you have to add ‘,False’ to the function.” It was the first time that I cursed Microsoft. At the time, I thought the 4th “,False” argument was really a plot by Microsoft to punish the hard-core Lotus users. I now realize that there are no evil plots within Microsoft. They are too big to effectively pull off any evil plots.
I also remember hating the Excel charting compared to the Lotus charting. At the time, it seemed to me that Lotus offered more control over their charting.
If course, in subsequent years, Microsoft added pivot tables, Automatic Subtotals, AutoFiltering – all very powerful stuff that caused it to run circles around Lotus. I am now happy to say that I don’t remember how to do a /Data Table 1 anymore – because Pivot Tables have made it so much easier.
When do you remember writing your first formula or VBA code for Excel?
The first formula in Lotus was =B2+B3+B4. I remember being hacked off that someone told me this was wrong. They insisted that @SUM(B2:B4) was preferred. This was my first lesson that there are many ways to do anything in a spreadsheet and one way was usually better than the others.
I painfully remember the first time I tried to use VBA. A lady named Pam Gensel came over from MIS. We recorded a macro. The code looked so non-intuitive. The “B” in VBA stood for “BASIC”, right? Where were the loops? Where were the variables? Pam taught me the oh-so-important trick to click in anything and hit the F1 key. I was slightly upset that Excel couldn’t record the simple action of hitting , a trick that I had used religiously ever since I read about it in a 1988 article in Lotus Magazine. Eventually, though, I came up the learning curve and wrote useful applications to automate the various reports that we had to do.
On average, how many hours per day do you spend working with Excel formulas and/or VBA code?
Only 16 hours a day now. I’m trying to cut back.
Which do you find most rewarding to work with: Formulas or VBA in Excel? Please tell us why?
I use VBA all of the time. I remember when I used to be really excited about being able to write on Excel formula that would copy across multiple rows and columns. But, anymore, it is faster for me to just use VBA. I admire what Aladin can do with an array formula.
It makes me feel inferior that I simply rely on VBA to do what you could actually solve with straight Excel. But – in the big scheme of things, I am more comfortable solving the problem in VBA and it is faster.
If you were going to give a novice, just starting out with Excel, some advice, what would it be?
Watch someone else work. Find the smartest Excel person in your office and watch over their shoulder for an hour. Everyone who is self-taught has learned various tricks. Spend time learning what other people have learned.
The miracle of spreadsheets is that you can write a formula, copy the formula, and the cell references move automatically. These are called “relative” references. When you enter a formula using the mouse, you are getting relative references. The most important concept is that there are also absolute references and mixed references. These are the formulas where you see dollar signs in the reference – like =$G$1. If you are new to Excel, open Excel help, search for “relative reference”, and then read the topic “About cell and range references”.
The other tip for new Excel users – if you want to add two numbers together, use the + sign. If you want to join two text cells together, use the & sign. This is called concatenation. =A1&” “&B1.
Please provide a sample of your first work (either as a formula or vba code) in Excel and tell us about it.
I definitely don’t have any Multiplan examples left anymore. The first practical application was a tool in Lotus 1-2-3. It would import yesterday’s invoices, add a total row, then copy the total row to a summary range elsewhere on the spreadsheet. I don’t think that I have this anymore, and even if I did, it would be on a 5.25” floppy.
What is your mental attitude when you are preparing to write formulae or VBA code? And what is your working environment?
I just dive right in. I plan as I go. My Excel friends yell at me for not declaring my variables. Frankly, I don’t know what variables I am going to use when I start.
I break the task into a rough outline of the steps that need to be done. Perhaps I will write the comments to pseudocode the application - and then I write the code to implement the task described in the comment. By writing the comments first, I always end up with commented code!
I have a 12’ x 15’ office in my home. Two windows with a view of our tree-lined neighborhood. On the desk are three computers. I am writing this on my production machine loaded with Office XP. The computer in the center is the beta machine with Office 2003. The machine on the far left has Excel 2000. Downstairs is an old machine with Excel 97 for the times that we need to make an app run in that environment.
If there special preparations that must be in place before you can begin, what are they?
I prefer to write code in silence, but listen to music when not writing code. So – I turn of the music, get a big glass of water, and dive in.
Finally, please give us something to think about - a reminder of your words here; a phrase that has helped you; a link to your own website. Anything that you think is important for the readers to remember.
If you are an Excel fan, it is time to offer a tip of the cap to Dan Bricklin and Bob Frankston. It was 25 years ago – October 1979 that these two guys introduced a Visible Calculator for the Apple IIe and called it VisiCalc. This invention changed the world. You have to realize that in 1978, the only reason to get a personal computer was to play MicroChess. No company was spending over $2000 to get a toy to play MicroChess. When Bricklin and Frankston invented VisiCalc, businesses everywhere began buying a personal computer in order to run VisiCalc. The invention of the spreadsheet literally fueled the personal computer market. Without these two guys, we would have nothing.
For a trip down memory lane, download the working copy of VisiCalc available at www.DanBricklin.com
Thank you very much for answering the questions.
This Black belt is yours...