4 ways to be more useful with Excel

Excel is everywhere, at every company. Microsoft is on-track to become a trillion-dollar company, and out of the top five skills that new college grads are learning, three of them - data visualization, data modeling and databases - are at least tangentially related to spreadsheets. Here are four ways to better utilize them:

1. Get really good at the simple stuff

Spreadsheet software is just another branch of mathematics - and like most branches of mathematics, it's important to have a solid understanding of the basics.

There are lots of lists of most useful Excel functions, but most business spreadsheets run on a few basic functions - SUM() and AVERAGE() to sum and average lists of numbers, VLOOKUP() to pull data from tables where it'd be inconvenient to go look for it manually, and IF() to select one of two possible values, based on the output of another value.

Most spreadsheets are designed for general use by corporate employees. Most corporate employees are not analytics experts. Knowing how to do matrix multiplication and sigmoid functions in Excel is nice, but it's pretty uncommon to need to build a neural network in a spreadsheet; there are generally better alternatives - Python, for example - for more complex analytical methods.

Plus, since most people only use Excel for the (relatively) simple(r) stuff, being able to do the simple stuff really well sets you apart - there's plenty of people who can write an INDEX(MATCH()) function to pull data from one tab into another, but there's significantly less people who can write that same INDEX(MATCH()) function in two minutes. Lots of spreadsheet wizardry takes place on small, ad-hoc projects: conditionally-formatting a report to make it look nicer a few minutes before the client sees it, querying 30 vendor names in a list instead of manually scrolling through 2,000 rows of data to find them, fixing the capitalization in a list of manually-entered first and last names from manually-inputted customer-service records.

2. Focus on learning concepts instead of rote memorization

I ran a tutoring business on Wyzant for about a year, tutoring introductory finance classes - among other things. Half of the tutoring involved going over concepts like bull call spreads and Asian options - which I didn't know enough about to avoid having to look them up on Investopedia every time I had a tutoring session. The other half of the tutoring involved teaching financial functions like IRR(), FV() and PMT() - which I also didn't know enough about to avoid having to look up their syntax every time I had a tutoring session. I still had enough college students paying me $39/hr to keep myself busy for a year, until I quit tutoring to focus on grad school.

Excel can do pretty much anything - and everyone's going to want to do something different with it, so trying to memorize a particular list of functions (especially a list of functions you may not ever end up using) is often wasteful. Instead, focus on learning how to quickly figure out which method you need to use for the problem at hand. During my online tutoring sessions, I didn't know what the syntax was for the PV() function, but I did know that there was a function out there that could tell me what the present value of a loan was, given a certain interest rate, monthly payment and loan term. If you forget the syntax, you can always Google it.

The thing about Excel functions is: once you get past the basics, everyone has a different idea of which functions are important, and trying to memorize everybody's idea of a good function can make one's head spin. My first job at an insurance company involved using logical functions - AND(), OR(), NOT(), IF() - to implement arcane state-based insurance regulations. At a benefits consulting firm, I used VLOOKUP() and INDEX(MATCH()) to pull historical salary data and balance information for pension participants into benefit calculators. Prior to my current role, I used LEN(), MID(), RIGHT() and LEFT() to get date information from lists of bond names - and nowadays, I mostly just use keyboard shortcuts and the occasional conditional format to tidy up the output from numerically-correct-but-cosmetically-impaired SAP BusinessObjects reports. Lots of companies, lots of spreadsheets, not much consistency.

3. Learn how to type quickly

It takes five to ten seconds to move your mouse up to the top of the screen, select the 'Formulas' tab, and flip through the function library to find the function you need. It takes less than one second to type the function out on your keyboard - even if you aren't a particularly fast typer. (And if you aren't a particularly fast typer, it's worth noting that typing - like any skill - can be improved with practice. 70WPM is a good goal to strive for.)

This Reddit user recommends not using your mouse, due to the fact that a keyboard can easily be five to ten times faster than a mouse. So does this Wall Street Oasis blogger, who also provides a handy list of alternatives. Unless you're doing something that requires a lot of customization - such as tweaking the formatting in a column chart or using Solver, you're probably better off using a keyboard shortcut.

Here's a tip: if you know where to find some functionality in the worksheet menu bar at the top of the screen, you can quickly find the shortcut for that functionality by clicking the 'Alt' key, then clicking the appropriate letter/number keys until you get what you want. (For example: if you want to reformat the text in a given cell to be centered, instead of using your mouse to select the 'Home' tab in the worksheet menu bar and then clicking the 'Center' button, click 'Alt', then 'H' to select the 'Home' tab, then 'A', then 'C' to center the text. After you click the 'Alt' key, every tab at the top of the screen will have its corresponding letter-key displayed by it; once you click the tab's letter-key, the tab will be selected and you can just look at the worksheet menu bar to see what the shortcut should be. Try it!)

4. Trust that your skills will be applicable

Fun fact: back in the 1700s, the price of a pineapple in today's dollars was about $8,000. Few people could afford pineapples, and the affluent ones who could wouldn't even use them - they'd just display them as centerpieces. As time went on and transportation methods improved, the price dropped - nowadays, there's pineapple on pizza, pineapple in cookies, pineapple in skincare products. Everybody eats pineapple.

Your Excel skills are like those colonial pineapple merchants - the faster you can deliver, the more people will find uses for you, and the better off you (and your coworkers) will be. Pivot tables might not sound so great when they take two hours to get implemented - but if you can implement one in three minutes, they start to become a lot more useful.