Thoughts and Adventures

The Most Powerful Tool that Every Business Owns, but Most Don’t Know How to Use

I looked up from my computer laughing and pumped a fist into the air…

Perhaps I will never forget this moment. It was the turning point where I realized just how powerful Microsoft Excel could be. I was working on a pricing project for a healthcare client to determine how much they should be charging their clients for each service. My most significant task was building a model that could analyze the price adjustments to figure out the additional revenue that it would bring in for each hospital over the next year. This may seem simple at first, but once you realize how many services hospitals offer, the many different ways that insurance companies pay for these services and how to factor in the variance of insurance for each customer, you begin to realize how complex it can get. To add to it, I wasn’t supposed to build this just for my client. I needed to build it to receive input from any number of hospitals, making it generic enough to be used for future clients. Furthermore, I needed to include a way to track the actual revenue over the next year in case we were paid based on the added value rather than a fixed rate.

So, the exciting, celebratory moment was when I placed input excel files into specific folders, pressed a button and watched the magic happen. Perhaps this is partially why I was drawn back into being on programming projects and why I desired to improve my own programming skills.

While this was my inspiration and depicts me as being kind of nerdy, you don’t need to do anything near what I was doing or be a nerd to realize the power of Excel. All it takes is understanding some of the more important functions and tools of Excel and applying them to your daily tasks to make them more efficient, making your life easier. I would like to help you with this.

I don’t claim to be an expert, but I believe that I know a lot more than the average user and have the desire to figure out anything I don’t know and continue to learn more. That being said, I have taught two classes on Excel. One was to about thirty people on my project in Wisconsin and another to non-profits that Investours partners with near Puerto Vallarta, PEACE and Sé Más.

When first teaching someone about Excel, I never know if I am going to start with absolute references or pivot charts. People’s abilities vary so greatly. When teaching a class, it’s a little easier because I need to start at the beginning and cover everything I can at a reasonable pace in the amount of time we have. Even some of the seemingly simpler topics are not understood to their full potential by more advanced users. So, it helps to review those as well.

Before the first class I gave, I sent an email out to some friends to ask them what they thought were the most important parts of Excel and what I should highlight. I got so many different responses. I realized that Excel has so much to it that people don’t want to know everything, they just want to know the things they need to make their work easier. So, instead of making the class based on what I thought were the most interesting parts, I tried to take some examples that were relevant to the audience I was working with. I have uploaded a cleaned up version of the excel file that I used for my Excel class that was targeted for people working on a web development project. (Directions may differ based on your version of Excel. It should be correct for Excel 2003, which was commonly used by those attending the class.)  After the class, the most common request I got was: “Can you show me how to check for duplicates again?” I wasn’t expecting that a simple countif statement would be the biggest take away for the class, but I was happy that so many people were using what they learned to expedite their tasks. I think walking through real world examples instead of a PowerPoint was one of the main reasons for this. It helped people realize the ways Excel could help them.

For those of you that don’t want to open the file to figure out the highlighted topics, here is a list of them.

  • Cell Referencing
  • Operators
    • Addition
    • Subtraction
  • Functions
    • Sum
    • SumIf
    • Count
    • CountIf
    • Concatenate
    • VLookup
    • HLookup
    • Average
    • SubTotal
    • SumProduct
  • Filters
  • Freeze Panes
  • Named Ranges
  • Data Validations
  • Pivot Charts
  • Goal Seek
  • Creating Your Own Function

Please keep in mind that the worksheet I provided is not supposed to be used to learn as much as it should be a reference for reminders after a class.

The class I taught near Puerto Vallarta had a much wider range of students, who had very different roles. As I started to go through an overview of each topic above, it became apparent that it would be more useful to them if I just sat with each of them individually. I feel like I was able to help each of them in a different way and hope that they are continuing to use some of the things they learned and apply it to other projects they work on. The most difficult of the students for me to assist was extremely new to Excel and computers in general. On top of that, she did not know English. This really tested my Spanish vocabulary and helped me to learn some new words by knowing where certain functions were positioned when they had english words next to them. It was an interesting and exciting experience.

As always, please let me know your thoughts, questions and suggestions for improvement. If you are interested, I would be more than happy to teach another class to a group of your colleagues if you think they would benefit a lot from it. If you have any individual questions, feel free to ask them to me as well.

I sometimes joke that if I were a superhero, I would love to be referred to as ‘The Great Optimizer’. My dream job would be helping people make their lives easier through optimizing the tasks in their life. I feel like I can do that through Excel and can continue to do so.

Special Thanks to my friend and mentor Kris Johnson. I had a memorable summer internship working for him at JetBlue. I first learned from Kris about the power of Excel. He is a great guy and a real power user of Excel for analyzing business problems.

I would like to also thank Seth Mandel for giving me the opportunity to teach a class for the first time. I found out afterwards that he didn’t realize that it was my first time teaching a class, but luckily I think everyone was happy with the result and he learned a few things as well.

FYI : You may be interested in learning about the success of the model I created and referenced at the beginning of this entry. Unfortunately, I came across an ethical dilemma with one of the project partners regarding how I should be reporting my hours. In an effort to avoid having another ethical disagreement, I chose to distance myself from working with this particular partner again. I would love to know that the work I did was helpful to other projects. Please let me know if you are aware of it.

15 Responses to “The Most Powerful Tool that Every Business Owns, but Most Don’t Know How to Use”

  1. Som says:

    Kurt,

    I could not agree more on the uses of Excel. I believe I have used everything in the past other than Data validation, Goal seek, creating my own functions.
    I am eager to encounter a situation where I can put these tools to use.

    Thanks for Sharing this experience.

    Regards,
    Som

  2. Congrats on your epiphany Kurt. Your technical abilities and enthusiasm are what make people want to work with you.

  3. Choosing the right hospital is important, even something as simple as the air filtration can have a huge impact on what bacteria you are exposed to. I agree that many factors should be publicly accessible. I wonder though how much such a database would incite unneeded panic.

  4. Tod Polo says:

    Rattling excellent info can be found on web blog . “I can think of nothing less pleasurable than a life devoted to pleasure.” by John D. Rockefeller.

  5. It’s a legitimate awesome together with beneficial part of facts. I’m delighted that you just contributed this excellent practical information and facts around. Be sure to hold on to all of us advised like this. Thanks to get sharing.

  6. We have recently started an internet site, the data anyone offer on this internet site has helped my family tremendously. We appreciate you your entire time frame & function.

  7. Blogging ain’t easy but you make it look that way!

  8. ねえ – 偉大なブログは、単にいくつかのブログについて見て、あなたが利用される可能性がありかなりいいプラットフォームです。イムは、現在、私のウェブサイトのいくつかのためのWordpressを利用するが、試運転であるとしてあなたに似たプラットフォームを介してそれらの1を変更するために探しています。特にヤウドで何かがそれについてお勧め?

  9. ちょうどたいの発言もの、ウェブサイトスタイルは完璧、単に 、あなたがあなたに知らせる {あなたを伝えるべきである偉大な| |優れた|私は単に非常に良い

  10. ほとんどの人々は、そのは、いくつかの例が欠落していてもあなたのポストに同意します。

  11. Yの心多くのおかげで、私はいくつかの時間のためにこのテーマについて考えています。

  12. これは、ドアに足を取得する方法です来て、それを維持します。こんにちは

Leave a Reply