Yes, You Can

One of my favourite blogs is Excel Theatre. It's over there on the sidebar, but if you haven't had time to investigate, the blog is a daily collection of humorous tweets about Excel and spreadsheets. One of my favourite quotes was this one:

Will be reciting Excel equations in my sleep tonight. My colleague taught me so much today! My bff said, “Excel could raise a child.”

Indeed, it probably could raise a child. The frustrating thing for me, however, is figuring out how to cajole a variety of functions out of Excel (although child-rearing has not been one). I'll have a goal in mind...will be pretty sure Excel can take me there...but no idea what to do in between. So, I thought I'd share my process of going from zero to hero.

Step One
Start inside the Excel program. I have two places I search first. One is to go to the "Formulas" tab and click the button on the left ("Insert Function"). This brings up the dialogue box shown below:

See that? You can describe what you want to do, and Excel can tell you what it's called. This can be handy for very simple formulas. The naming conventions in Excel do not always make sense (at least to me). Would you guess that "PROPER" is what you use to convert a text string so that the first letter of each word is uppercase and the rest lowercase? (Very handy when you have a set of names that aren't formatted like you want.) If you're not getting any reasonable suggestions from the dialogue box, try clicking the "?" (also known as "Help") in the upper righthand corner of the program window. Since it can also search online resources, you might have some luck there. However, even if one of these methods starts to pay off, you might not be able to interpret what Excel wants. For example, what the hell is this supposed to mean?

This is one of the main functions I ended up using in my gradebook template, but if I'd seen it like this, I would never have known it was what I wanted. The description here makes no sense to me. Which leads us to Step Two.

Step Two
Offer up your need to Teh Googles. If I think Excel can do something and I can't figure out what it's called, I just type a phrase or two into Google. For example, "How to autoupdate a cell in Excel based on the contents of another cell." This usually provides me with a host of links, most of which are either (a) not really what I wanted, after I take a moment to scan the info or (b) look like what I want, but are written in a way that's beyond my level of comprehension. However, what I usually glean from this are the right terms for the functions I'm going to need. This greatly helps me refine my search. I will still not be able to find specific examples that I want. Most of the Excel world is concerned with big business---not education. But by this point, I will have at least one or two webpages with potential. Then, I move on to Step Three.

Step Three
Thank goodness for YouTube. I use the function names gleaned from the web search as inputs into my YouTube search. Watching screencasts on YouTube can be a painful process. There is a lot of ugly stuff out there. However, the benefit of YouTube is that you can actually see how to input formulas and get results. Even the very best explanations on a web page do not bring about the same level of understanding as watching someone else work the spreadsheet. Sometimes, you just gotta see it to believe it.

Step Four
Practice using some fake data. If you're applying new-to-you formulas, don't dick around with your prize data...or at least make a copy you can play with first. I like to use something very simple---the smallest data set necessary. Most of the time, it's just a four column by four row set of junk numbers or text. Small is better. That way, if you need to troubleshoot, there's not much to wade through. Once you've perfected the formula, it's ready to apply to the real deal.

Step Five
Bask in your Excel prowess. Lather. Rinse. Repeat.

Someday, Excel may be written in plain English (or language of your choice). Until then, when you find yourself in Excelhell, take advantage of the paths available to you. Chances are, you're not the first person to want to bend Excel to your particular will. There is a wealth of resources out there to make sure you can.