This two-part post features the sage advice of Will Bachman, a management consultant who shared his passion for uber-sharp Excel and Pivot table skills in a conversation with the author.

No doubt that she is going to get ahead.

No one in their right mind would buy a top-of-the-line Mercedes and then only drive it in 1st gear. But that is what many professionals do with Excel.

Investing 20-30 hours in upping your Excel skills will produce a huge return on investment

A one-time commitment of 20-30 hours of focused learning can allow you to do things in 15 minutes that you could never have done manually. And, by not diving deep into the program, you might not even know what you don’t know.

Will Bachman explained, “If you can become a master at Excel, that trumps getting an MBA. I have served over 50 companies in the last decade and I’ve interacted with hundreds of business professionals, and many of them have weak Excel skills. I feel passionately that upping your Excel skills is the best ROI in terms of the ability to differentiate yourself, given a minimum of investment.”

“If I have a potential hire with an MBA but weak Excel skills, and another with no MBA but good Excel skills, I would go for the person with the strongest Excel skills every time.”

Is your interest piqued? Let’s get on the mat with Part 1 this week and Part 2 next week.

8 things you need to know to save yourself countless hours and weeks of unnecessary work

Follow this list by working with actual data. Will recommends using the book Pivot Table Data Crunching: Microsoft Excel 2010 by Bill Jelen, a.k.a. Mr. Excel, but even if you don’t buy the book, you can grab some downloadable exercises http://www.mrexcel.com/learnfiles.html to get started.

1. Learn simple Excel keyboard shortcuts
Any investment banker or management consultant worth hiring has learned to minimize use of the mouse and maximize use of keyboard shortcuts. This makes you much faster in the repetitive stuff.

• Instead of formatting a cell with the pull-down menu, use Control+1 to call up the dialog box to format the cell
• To insert a row or column, use Shift-Control+ and then move up or down the dialog box to select add a row or column
• If you have a function that you want to copy down a row or across a column, there are two shortcuts: Either select the cell with the formula and all the cells below it and press Control-D ((indicates Down) to fill it in, or highlight the top cell with the function, and when you see the little black plus sign in the corner of the cell, pull it down to other cells to fill the formula in.

There are lots of other Excel shortcuts. Take a look at this complete list http://www.asap-utilities.com/excel-tips-shortcuts.php  and see what would be helpful for you. If it is overwhelming at first glance, learn two functions at a time and every so often, review the list of shortcuts and keep adding to your portfolio. A dozen keyboard shortcuts learned from this free tutorial (for Excel 2010 on a PC) will boost your productivity like a Cuisinart speeds chopping!

2. Pivot tables are the most powerful thing people don’t know about, so remedy that

If you are starting with a table of contiguous data, you can cut that data any number of ways with Pivot tables.

Here is a simple example any reader can relate to. Let’s say you’re a sales manager for a national company and you’re trying to divide up the country into 4 regions with somewhat equal revenue (for 4 sales reps). One way to do that is to manually pick a bunch of states and assign them to a region, sort or filter them, and do a sum of the states’ individual revenue to get the total for that region. That would be okay, but if you shift 3 states into a new region, you would have to redo the entire exercise … a true time-waste!

With a pivot table, if you have a column that has regions spelled out, you can pivot it with a single keystroke, and see different combinations.

Another example: You are a sales manager with a data table in front of you containing over 1,000 rows of data. There are columns for every sale your team has made, by state, by customer name, by product sku (stock-keeping unit), by name of sales rep, by revenue, and by profitability.

Sample of data set useful to learning advanced Excel skills.

Your boss asks you to give a report on revenue by product. You spend hours sorting the table, filtering it, doing sums, separating it, and so forth, for the better part of a week.

What if she then says, “Could you do it over with revenue by sales rep?”

Now, it is a week later.

She asks something else … and it may take another week, versus 15 seconds had you started with pivot tables!

Commit to learning pivot tables so you can cut data any which way, and then spend your freed-up time on something you are really keen to do, rather than grunt work.

3. Learn the advanced Excel skill of using an IF statement

People are scared of functions but you must learn the IF statement because it comes in supremely handy, so often.

There are 3 parts to an IF statement: IF(something true or false, what gets entered into a cell if the first value is true, what gets entered into a cell if the first is false).

Let’s say we want to have a summation of how many registrants passed or failed a course, and 90 or above is a “Pass.” One column is a person’s final score (the Grade column), another column lets us know their status. Use the formula IF(Grade > 70, “Pass”, “Fail”).

Then you could do a pivot table on this and sum up automatically the number of passes and fails.

4. Familiarize yourself with all of the functions that Excel offers, including the date function

You don’t even need a book to do this. Go to the function symbol on the ribbon, click ‘Insert Function,’ select a category of function. If you want to click on it, it tells you what that function does.

Pressing the fx symbol calls up a dialog box, this is one for the Mac.

Familiarize yourself with ALL of the functions.

Yes, step number one is simply to know the functions exist.

You can then ignore some that represent fancy statistical analysis or complicated math, if they are not useful to you. But you should definitely know the Date functions.

Step number two is to consider which ones might be useful. Some day, practice using them so you can toss off calculations like pulling the top off a soda can.

To illustrate: Assume you have a data table of 1,000 transactions and a date stamp (the date they occurred), and you want to know how many sales occurred over a weekend. If you didn’t know it is possible to use a function, i.e., you didn’t know the function =WEEKDAY, you might scratch your head.

Using =Weekday(serial_number, return_type) will tell you which day of the week is equivalent to the date stamp (referenced with serial_number), by returning numbers 1 through 7 (1 is Monday, 7 is Sunday, etc.). Do a pivot table based on that and add up the Saturdays (6) and Sundays (7) and in two minutes, you have your answer.

Another example: If you have two dates and you want to know the number of days between them, that would not be fun without formulas to reference. Think of cell A1 – cell B1, where cell A1 contains today’s date and cell B1 contains January 1, 2012. Then, knowing C1 will return the number of days since the beginning of this year, when you put A1 – B1 into C1, you will save lots of time.

This and many other formulas will come in handy at some point, if you really commit to learning them.

Mojo Moves

Just like that famous phrase from The Wizard of Oz (“Lions and tigers and bears, oh my!”), you are going to repeat this mantra, “Excel and shortcuts and pivot tables, oh my!” until you are 3 times faster than you are now, using the software. Seriously. Set yourself a goal, and engage in goal-seeking behavior to enable you to stop twisting in the wind of underachievement. Differentiate yourself.

• Get 30 minutes of instruction from your best buddy on how to manipulate pivot tables, and then spend a few hours honing that skill
• If you aren’t the type to yank the chain of your buddy, sign up for lynda.com and commit to using the uber-useful video tutorials to get up to speed on Excel
• Alternatively, buy the book, work with the downloadable data, and get crackin’.

Photo courtesy of Creative Commons 2.0 nocklebeast.

Let me know in the comments, will you get over your reluctance and learn advanced Excel skills?