Teach me Excel

“Teach me Excel” is a tough request to satisfy because it’s so vague, and the person making the request doesn’t realize the bottomless possibilities within Excel.

When the open-ended requests “teach me Excel” come to me, there’s a situational urgency:

  • They want to change careers and need to know what the heck is this excel thing in every job description and interview.
  • A recent college graduate discovers that Excel isn’t primary to the jobs they’re interviewing for, but Excel is critical to the functioning of the businesses.
  • They’re between jobs, want to increase their employability, and finally have to embrace Excel.

The urgency feels to me like they’re in a tiny room with an unexpected overwhelming need to manage data, and Excel is a way to get control over the situation.

trapped_in_a_box_by_lackofcolorx

But here’s a problem that’s presented:

  • Without an immediate need, it’s hard to effectively teach someone Excel. It’s used in so many ways, inside so many different processes that it’s hard to design an effective program that will stick and be available when they do need the skill.

Why is “teach me Excel” so complicated?

Here’s a real example:

Your Task You are supposed to create summaries of data from 5 sources that are all spreadsheets.

  • One source has sloppy formatting. Some of their entries are allcaps, others are all lowercase.
  • One source sends a spreadsheet that includes units of measure that need to be converted to match the other 4 sources.
  • Another source provides data with summaries that never add up right.

The upshot:

Your REAL Task After merging and fixing the data, and reviewing the data quality, then and only then, create summaries of data from 5 sources that are all spreadsheets.

You not only need to learn to build summaries. Now, you need data-parsing skill.

This is blog-worthy because the issue regularly comes up in workshops, in conversations, and interviews with prospective clients. The Task is simple but The REAL Task is bizarro stuff that consistently keeps people from The Task.

Therefore, it’s best when a potential student–no matter their level of experience–can make a specific request. We discussed this on ExcelTV Episode 6 with Keidra Chaney.

The open-ended request, “Teach me ____.” Sets the student up for disappointment and the teacher for failure.

But ok, there’s still the question: for a person who’s got limited Excel exposure and no real-world immediate need, where can they start?

5 Suggestions For: “If I want to learn Excel, where should I start?”

Based on the many spreadsheets, businesses and projects that I’ve come across, here are 5 good fundamentals. Notice that I’m not listing top functions or details of the Excel tool.

1.  Be Willing To Play

Loosen up and just play. Make stuff up! For a beginner musician, one way to get quicker familiarity with their instrument is to write simple songs. They’ll likely be terrible songs, but the exercise gets the focus away from the tool, and puts the tool in service to something. Whether you’re using a piano, a mop or Excel, focus has to be on the purpose, not the tool.

Let’s play : Where Did That Fish Go?
You and I have a restaurant and want to understand our costs and inventory better. So, we’re going to start by tracking every ounce of this fish.

If you download the spreadsheet or open it in Excel Online, you will see the formulas and they’re basic math. But the formulas aren’t as important as the development of something that brings features together into something interesting. Again, it’s like taking a few musical notes and writing a simple melody.

2.  Integration of Formulas

This is a regular blunder in spreadsheets of even the most experienced spreadsheet users: poor integration of features and formulas.

Get into the habit of having everything in your spreadsheet tied together so that one change is immediately reflected throughout your document. In our game, Where Did That Fish Go?, if we discover that we actually bought 5.5 pounds of fish, that update in cell B2 will immediately update cells A6, C6, G12, H11 and H12.

change fish weight

fisharrows2

3.  Understand Absolute & Relative Cell References

This is another good habit to develop early because it helps both accuracy and efficiency. Knowing how to use absolute and relative cell references in formulas can save HOURS of tedious work, and keeps formulas integrated. Here is a short blogpost and video that I did on Absolute and Relative Cell References.

We would use this concept to calculate the cost of each use of the fish.

  • First, we calculate the cost per ounce: 56 cents
  • Next, in cell H2 we multiply the weight by the cost per ounce in cell C8
  • The Absolute Reference is designated by the dollar-signs: $C$8. But there are no dollar-signs around H2. Therefore, when we drag the formula down to other cells, the reference to H2 will change to H3, H4 … H9, but C8 will not change when it’s in the form $C$8.

What do we learn?

  • There’s still $8.09 worth of fish remaining
  • Dish A uses $2.79 worth of fish each time
  • You could extend this and get the cost of the missing portion

Now, imagine the poor souls who type that formula 8 times.

fish costs

4.  Watch Videos And Don’t Get Intimidated By The Details

Dueling Excel videos can be good for beginners if you pay attention to things like

  • Layout
  • Integration of formulas
  • How Bill Jelen and Mike Girvin think through the challenges
  • Excel’s capabilities

Kevin Lehrbass, MySpreadsheetLab, also has good videos that show development and integration of features.

Extending the music analogy, this suggestion is like inviting you to just listen to the music. Don’t leap into trying to figure out the meaning of the lyrics or song structure.

I created this high speed video showing how my entire Naughty-Nice-Ometer was build from a blank spreadsheet. The idea is to show the various layers of development. Details and download of the spreadsheet can be found here: Excel, Christmas, Krampus & Kids.

5.  Ask Questions Ask Questions Ask Questions

The Excel community is very helpful. Ask for help. Ask “is there a better way?” Ask “is this possible?”

Here is my page of Badass Excel Bloggers. Massive knowledge, and good folks.

CONCLUSION

For the person who needs to know Excel but doesn’t have real-world need, these 5 suggestions are based on what I’ve seen in lots of spreadsheets over the years. Being able to make a bar graph isn’t necessary for everyone, but absolute/relative cell references show up whether you’re building budgets, tracking time and pay, cleaning data, managing inventory or building complex tools.

Having a handle on these will help a beginner when they’re ready to write complex formulas, build pivot tables, and write VBA code.

Trapped in a Box image courtesy of lackorcolorx