I’ve been away for over a year, holy cow. Time to dive right back in with a writing sample I wrote about – woohoo – Microsoft Excel!
Spreadsheets are a pretty amazing tool, and you probably have some experience using them or you wouldn’t be here! Although I can point you to several engineers who wouldn’t dream of booting up Excel for their tasks, basic “what-you-see-is-what-you-get” tools possess a fundamental beauty in bridging the gap between programmers and non-programmers. For example, I can demonstrate complex algebra and statistics ideas on a spreadsheet that makes them perfectly clear to people who have not had much experience with them. This is critical in an age where we are all expected to have some competence with these tools no matter what we do.
In reality, not everyone is going to do complicated math in a spreadsheet. Most of what I see on a daily basis is just simple data in rows, some multiplication and division, and infrequent VLOOKUPS and SUMIFS. And charts. Oh, those charts. People just love charts.
From my perspective, going from basic to advanced is stepping over the line from using Excel as a spreadsheet/word processor/MS paint for the sole purpose of presenting data to a pseudo-database that can actually tell you something about that data. That “Advanced” moment is a sudden realization that keeping source information clean and exclusively categorized can actually provide you with so much more useful insight.
So, are you advanced? Here are a few questions you can ask next time you’re busy building spreadsheets.
Are You Allowing the Spreadsheet to Think For You?
Many tasks involve looking at large lists of information and making decisions about how to analyze, categorize, or summarize that information in a way that gives you concise answers. At the advanced level this requires indexing, if/then formulas, custom conditional formatting, and custom filters.
Are You Letting Your Variables Be Themselves?
=A1 + B1 works great for single sheet files with basic information. What happens when you’re dealing with 17 sheets, plus 5 reporting sheets and a data validation variable (I speak from experience)? Advanced Excel demands that you name ranges and cells for clearer formulas and references, and eventually use INDIRECT when you start getting really complex.
Are You Leaving the Editing to The Professionals?
Many basic level users are perfectly happy learning how to use CONCATENATE and never looking back. Eventually, they’ll realize that using a formula with parameters to join strings together just doesn’t make for an easily scalable sheet. Advanced users know that it’s important to use formulas like FIND, ISNUMBER, and LEFT/MID/RIGHT to edit and produce the exact result they want dynamically. I once made a sheet to help me write 301 redirects for 300 webpages. Advanced text manipulation cut that down to 10 minutes.
Are You Giving Excel Your Dirty Work?
I haven’t been entirely honest with you up to this point – I actually do more at work than create spreadsheets. In fact, I use numerous different proprietary tools and sites day to day, but they do not assemble the information together in the way that I need them to, and they certainly never give me immediate insights. Advanced users know that getting to know the Data Model in Excel and leveraging the raw reporting power of Pivot Tables can free up the time you need to do whatever it is you do at the highest possible level. And yes, for crying out loud – you can still do charts!
So get learning about these new ideas!