Excel Stories

January 06, 2021 - by Bill Jelen

Live – On Stage – In Person: The Power Excel Seminars

That poster from Hatch Show Print on my website sort of started out as a joke. I was flying through Nashville and saw a display of old country music letterpress posters on the wall. The little sign by the posters said they were produced on a 100-year-old hand-cranked press and that the company was still operating in Nashville. I called them and hired them to produce a “concert” poster for my live Excel seminars. They had to think I was a little nuts. The print shop manager, Jim Sherraden, often did an informative presentation about the history of Hatch, and he cited my poster as the first to list a website URL.

I do my half-day or 1-day seminar for accounting groups across the country. Every large city has a local chapter of the Institute of Managerial Accountants or the Institute of Internal Auditors. The people who belong to these groups usually need 20-30 hours of continuing education (CPE) each year. If you join the organization and go to 12 lunch meetings, half of your CPE is done. The organizations will often sponsor a 1- or 2-day event with speakers so their members can earn the remaining CPE at one time. I often am invited to speak at these events. The poster on the door lets people know that they are not in for the usual tax update. (I don’t mean to offend the wonderful people going through the new tax laws…someone’s got to do that.)

Excel is used on 750 million devices. I’ve met a lady in Appalachia who used Excel to design original quilt patterns. I’ve met people who have designed workbooks in Excel that train people how to fly 737 jets. From quilts to jets, Excel is used everywhere.

When you hand the world’s most flexible software to 750 million people, everyone finds interesting things to do or interesting ways to use Excel. I love traveling to do the in-person Excel seminars because I get to meet these people. At every seminar, someone has an interesting story of how they are using Excel. At every seminar, I always learn a few new Excel tricks from the people in the seminar. I joke that my job is collecting the cool and obscure tricks in Excel from people along the road and passing them on to people further on up the road. Many of the amazing tricks in this book came from people in my live Excel seminars.

This is truly a win-win. I have fun doing the seminars. The audience get to take home a cool Excel book. Among the 75 tricks that I show in a day, hopefully everyone will find a few that they can take back to work to start becoming more efficient the next day. Plus, people earn the CPE hours without hearing about any tax updates. Even at the Association for Computers and Taxation, where their annual meeting is all about tax updates, they bring me in to do a 2-hour session to offer a little levity between the tax updates!

The Excel Guru Mission Patch

Do you own a the highly-coveted Excel Guru mission patch?

When I do my live Power Excel seminars, I always encourage the audience to “show me up.” Someone in that room is going to have a better way to do something in Excel. I used to offer a small prize to the first person to teach me something during the seminar. I would also predict that the first person to show me up that day walked into the room and sat in row 2.

I have no idea why they often sit in row 2. But I noticed early on that the best Exceller often chooses a seat in row 2. They want to be close. But not front-row close. Almost every time, the first cool idea from the audience comes to row 2. (On the times when the first tip comes from another row, I quip, “Did you arrive late, and row 2 was already full?”)

Over the years, the prize varied from a laminated tip card to an Excel Master enamel pin. The current prize is an embroidered Excel Guru patch designed by the same guy who designs mission patches for Cape Canaveral rocket launches.

As Seen in the Wall Street Journal: the Excel Function Clock

It was a Friday afternoon at work and time was dragging. I would look at the clock, do some VLOOKUPs, then look back at the clock and barely a minute had passed.

it struck me that it would be funny if someone made a clock where the 3 was replaced with the Excel function =PI(). This simple function with no arguments returns 3.14. So, =PI() wouldn't be exactly at 3. It would have to be just a bit beyond the 3. The solution would be to use the INT function to return just the integer portion of PI(). =INT(PI()) returns just 3. Perfect! I am sure I pitched both of these ideas to Kevin Adkins, who probably didn't think it was a funny as I did. That never deters me, though.

After writing all 12 functions, I actually found the graphic designer who designed the clocks for the old Signals mail order catalog. The Excel function clock was on Call For Help a few times. Once, Leo Laporte attached it to a lanyard and wore the clock as if he were rapper Flavor Flav. It was funny stuff.

The clock became famous, though, in 2017 when it was featured in a Wall Street Journal article about the CFO of PF Changs saying he was going to dump Excel. People were furious and the WSJ ran a follow-up article with a quote by me and also a picture of my clock. I sold a lot of clocks that week!

Here are the 12 formulas:

1. =MIN(1,10). Min returns the minimum number. Since 1<10, the function returns 1.
2. =MONTH(23790). That is the serial number for my birthday, February 17, 1965. That is month number 2.
3. =INT(PI()). =PI() would've been 1/7 of the way past 3, so =INT returns the integer 3.
4. =LEN("FOUR"). I used LEN daily. =MID(A2,LEN(A2)-7,2) gets the last 2 characters. How long is FOUR? 4.
5. =SQRT(25). The square root of 25 is 5.
6. =FACT(3). Factorials are used to calculate lottery probability. The Factorial of 3 is 3x2x1 or 6.
7. =GCD(77,49). Middle school match is simpler with Excel. The greatest common divisor of 77 and 49 is 7.
8. =2^3. The ^ raises 2 to the 3 power. =2*2*2 is 8.
9. =PMT(9%,9,-53.96). If you borrowed $53.96 from the bank to buy some MrExcel books and had to pay it back over 9 years with a 9% interest rate, each yearly payment would be $9.
10. =LCM(2,5). Another one for the 7th graders. The Lowest Common Multiple of 2 and 5 is 10.
11. =ROMAN(2). Hmm. My fascination with =ROMAN() began before my 40th book. The =ROMAN(2) is II, which looks sort of like 11. Just like XL sounds sort of like Excel.
12. =COLUMN(L1). tells you the column number of a cell. L is column number 12.

If you are thinking you need to get one of these for yourself, head over to mrx.cl/excelclock.

The MrExcel Message Board Community Answered 1 Million Excel Questions

When I launched MrExcel.com in November 1998, I would get up every morning and answers yesterday's e-mailed questions before heading in to work. Initially, it was a question or two each weekday. But, by May 1999, I was getting more questions than I could answer in an hour. I was answering questions from 5 AM to 6 AM and then heading in to work. I either had to start getting up at 4 AM or find another way.

I downloaded WWWBoard from Matt’s Script Archive. I asked people to post their question at the message board. And I asked that, after posting, they look at the last few questions to see if they could help someone else out. It was like the take-a-penny, leave-a-penny tray at a cash register. If you need help, post a question. If you can help someone else out, help them out. It worked. People started posting and answering questions.

In the early days, I noticed a few people would stop by almost every day and answer a question or two. Ivan F. Moala. Cecilia. The late Dave Hawley.

One day, at work. I was stumped. I went out to my own message board, and described my problem. An hour later, Ivan F. Moala from New Zealand had posted an amazing answer. I knew I had something.

The board transformed over the years, morphing into bigger platforms. I was on a $10-a-month hosting plan at Pair Networks in Pittsburgh. One day, I get a call from them wondering what I was doing. “You are on a $10-a-month plan, but you are using as much traffic as our $1,000-a-month plan!” Apparently, the MrExcel Message Board had taken on a life of its own.

Today, the amazing people at the MrExcel Message Board have answered 1 million questions about Excel. We’ve attempted to keep every question live on the site. There are some massive competitors (I won't name them, but their anagram is "is Comfort" or "of Mr Stoic") who have competing forums, but they have no problem wiping out their history. I run into people all the time who say they use my forum. I ask what their UserID is. “Oh – I’ve never had to post – I just search. Every answer is already there.”

The MrExcel community is staffed by volunteers. Over the years, various experts have worked tirelessly as moderators and administrators. My sincere thanks to Andrew Poulsom, barry houdini, Colo, fairwinds, Ivan F Moala, Joe4, Jon von der Heyden, Juan Pablo Gonzalez, NateO, PaddyD, Peter_SSs, Richard Schollar, RoryA, Scott Huish, Smitty, Starl, SydneyGeek, VoG, Von Pookie, zenou, and Zack Barresse.

Suat Ozgur and Scott Pierson handled the tech issues with the board.

As far as the people answering questions, 45 people have answered more than 10,000 questions at the board. This is a staggering contribution to the Excel community. Thanks to Aladin Akyurek with 85,000 posts. Norie and Andrew Poulsom have over 70,000 posts each. VoG has over 63,000 posts. Others with over 10K posts: Joe4, Jonmo1, Peter SSs, Rick Rothstein, RoryA, Fluff, Smitty, Richard Schollar, mikerickson, shg, barry houdini, Scott Huish, pgc01, Domenic, erik.van.geit, Michael M, hiker95, jindon, JoeMo, T. Valko, xenou, Marcelo Branco, AlphaFrog, My Aswer Is This, MickG, PaddyD, lenze, Von Pookie, SydneyGeek, Juan Pablo Gonzalez, Mark W., Yogi Anand, MARK858, Tom Urtis, JLGWhiz, GlennUK, tusharm, oldbrewer, Zack Barresse, Jon von der Heyden, just jon, and Greg Truby, and Mole999. Many of the experts at MrExcel.com are awarded the MVP Award in Excel from Microsoft.

If you ask Google any Excel question, the odds are pretty good that one of the top answers will be from MrExcel. If you can’t find your answer on Google, it is free to post a new question. Make sure to give the post a title that describes what you are trying to do. Even in the middle of the night, someone will likely have an answer.

In late 2019, the message board was migrated to a modern Xenforo platform, complete with the ability to post screenshots of your Excel problem.

How to Post Your Question to the MrExcel Forum

Here are a few tips to getting an answer quickly at the MrExcel forum.

1. Before posting, do a Search. With a million threads, it is likely someone has had this problem before. You do not have to register to search.
2. In order to post, you have to Register. Give yourself a clever name such as XLGoddess or VLOOKUPKing. We ask for your e-mail address for password resets. I am not doing this to spam you. I won't spam you (because I can't figure out how to spam this many people without getting thrown off the Internet). We used to allow "Anonymous" to post to the board, but when five different "Anonymous" started posting in the same thread, it became impossible to follow the conversation. After registering, click the link in the confirmation e-mail that we send.
3. Start in the Excel Questions forum (there are other forums for Access, Power BI, and so on).

4. Click the Post Thread button in the top right.

   

5. Include a meaningful title that is specific to your question. Here is an example of a good title: "Excel VBA Type Mismatch Error". Your goal with the title is to get an expert to be interested in your question. Titles such as "Excel Help" or "Help" are not useful.
6. State your question clearly, including your entire need at the start.
7. Add some representative sample data - actual data is much better than an image. There are free tools to help you post your data to the board. See "How to Show Your Worksheet in a Post" below.
8. Tell us what formula/code you have currently, and why your results are not what you want.
9. Include which version of Excel and Operating System you are using.
10. If you are looking for help with an error received, what is the actual error message and where exactly does it occur?
11. Before posting, read your question back to yourself to check it will make sense to somebody unfamiliar with your worksheet.
12. If the answer you receive is inadequate for some reason, post a reply stating why the answer does not work and/or with more information (if needed) in the same thread.

If one or more posts were particularly helpful, click the Like button on that post. Getting Likes increases a person's reputation.

How to Show Your Worksheet in a Post

There are two ways to show your worksheet when posting to the MrExcel Message Board. First, you can use any screen clipping tool, copy a portion of the screen to your clipboard, then paste it into a post at the board. While pasting a picture can allow someone to see what is going on, in many cases, the volunteer who is going to answer your question will want to test some formulas in Excel, and a picture forces them to re-create your worksheet. Instead, our awesome web developer, Suat Ozgur has produced a free Excel add-in called XL2BB which allows you to post your worksheet to the board and allows the volunteer to then copy this data to Excel.

1. Download XL2BB and install following the instructions at this page.

2. You will see two new icons on the right side of the Help tab in the Ribbon.

   

3. Select the portion of the worksheet you would like to share and click Capture Range.

   

4. Select All and Generate Output.

   

5. Switch back to the MrExcel forum and Ctrl+V to paste the code into your question. Initially, it will be a scary-looking bunch of codes, like this:

   

6. Click the Preview button

   

7. And you will see a version of your worksheet:

   

Here is why going to these extra steps will get you a better answer. When you post your worksheet to the board, the volunteer Excel experts love this view of the data for several reasons:

1. They can hover over any value in a cell with a yellow triangle to see the formula in that cell.
2. If they click the value, the formula is displayed in the formula bar and automatically copied to the clipboard so the volunteer expert can paste it into their copy of Excel.

3. If they click the "Select All" icon above and to the left of cell A1, the code for the worksheet is copied to the clipboard. They can then paste this code to a blank workbook and test their formula ideas.

   

   • Hover over a formula cell and the formula appears in a tooltip.
   • Click a formula cell and the formula appears in the formula bar, ready for copying to the clipboard.
   • Click the Copy All icon (diagonally above and left of cell A1) to copy someone else's spreadsheet so you can paste it to your Excel.