If I told you that learning Excel in a way that makes you competitive in the job market means 20 to 30 more hours of time investment, instead of playing Angry Birds on your lunch hour, would you get on it?

Lance Armstrong did not have Excel skills.

By contrast, if you were trying to “pull off a Lance Armstrong” with your Excel skills–air quotes intended–that’s your prerogative. But believing you can fake it when it comes to software skills is a lot like a fallen athlete thinking that he could hide his wiley ways. In the end, he was stripped of his seven Tour de France medals.

For those of you willing to work, pull up a chair, a screen, and a keyboard. I am going to send you a lifeline from Will Bachman, a management consultant who has turned new client acquisition into an art. The prior post offered up our expert’s top 4 tips for polishing your Excel skills, and we continue with tips number 5 through 8 below.

5. Get face to face with the VLOOKUP function and ride that baby to analysis heaven

The Vlookup function is useful whenever your data are arranged in columns, and one unique identifier serves as a pointer to other information you seek. For example, a database  organized by employee ID number, social security number, or a unique student identifier meets this criteria.

Typically, you face two tables with one common element and you want to look up one thing and return something else, perhaps a product ID number or expected year of graduation. Or, you have the number of products sold in a given month in one table but price is not in the same table. In either case, whether you have 200 products or 200 students, it is not so difficult. But if you have 4,000 products or 40,000 students, Vlookup becomes the simple fix. In less than a minute, you can add a column containing the Vlookup formula and Robin can rescue Batman in a flash.

The formula for Vlookup is =vlookup(unique identifier, entire range of data, number of columns offset, true/false).

To explain: Unique identifier might be the relative location of a social security number, i.e., the A4 cell may contain the code 214-53-8176. The entire range of data is the complete table from upper left corner of one table to the lower right corner of the second table, including the Column Headers. (A good shortcut: use the F3 key to name the entire table and then paste that name into the formula.) The number of columns offset is exactly how many columns to the right to find the answer. So, for example, if the unique identifier is in column A and the data you seek are in column H, the offset would be 7.

True/false will let you decide if you want an approximate match (true) or an exact match (false). An approximate match is useful if you need the closest alphabetical match, say, if you are matching people’s names. If you request an exact match and none exists, it will return “NA.”

Does this sound too confusing? Okay, here’s the deal. Let’s say there is a price list for two kinds of shoes:

• “shoes, blue” and
• “shoes, red.”

In the last month, Maya’s Couture added “shoes, green” and sold ten pairs. If you put the last variable as “true,” it will pull up the price for red shoes on those ten lines of sales because that is the nearest match on the price list when it scanned the table; and finding no exact match, it took the nearest match. A big mistake. By contrast, if you put the last variable as “false,” it would return NA and that would alert you to look up the price for green shoes.

6. Learn how to change between fixed references and variable locations within formulas

This is such a basic shortcut that even a summer intern would have mastered it years before.

If you are referencing a cell in a formula and you plan to copy it into other cells, you need to know whether you want the data in a specific (“fixed”) cell, or whether you want it to vary as you move around. If you copy a fixed reference and then move one column to the right, there is no problem pointing to the correct, fixed reference. But if you copy a variable reference and willy nilly take a look at your new data set, it will now incorrectly get data.

Don’t ever do this manually. Toggle between a fixed and relative reference by highlighting the cell (in the formula bar) and then press the F4 key. You can now fix the row reference, fix the column reference, or fix both by continuing to press F4 while it cycles through all options, i.e. G4, $G$4, $G4 and G$4. (You can even fix neither, but then why do it?) Commit F4 to memory.

7. Use data filters to clean up

There is a right way and wrong way to use filters in Excel. Our Excel whiz strongly recommends against using a filter to get subtotals and subgroups. For that, use either Pivot tables, which is clearly explained in Will Bachman’s previous post, or use the Group/Subtotal function on the Outline section of the Data tab. By contrast, use the data filters to quickly validate and error check data that you have received.

To illustrate, let’s say you receive 10,000 rows of information, which is much more than you can eyeball. Start off by doing a dummy check to ensure there is nothing “broken” that will skew your results. The dummy check has two parts, as follows.

1. You need to verify that you have continuous data, i.e., missing cell information is okay, but breaks in columns or rows will cause whole sections of data to be ignored.
2. You also need to verify that the Column Headings are proper. To do this, press the right arrow while on any cell in the column headings, and see if you land on the right-hand edge of the data.

8. Use PivotCharts when you work with archaic systems whose reporting is limited

I have personally been in face-to-face meetings where a potential employer showed me the reports that they offer clients, and the dashboards were entirely built in Excel.

Do you know what those dashboards consisted of? You got that right … pivot charts. Even though pivot tables do a great job of slicing and dicing and baking the data, sometimes you are simply better off with pivot charts.

So do not neglect learning the intricacies of how to make and adjust them.

In fact, the trend nowadays is to get more and more visual in presenting analyses. That is why infographics became the “tool du jour” earlier in 2012, and then suddenly everyone was making an infographic.

I was inundated with them, and I would guess you were, too!

Now entire conferences are devoted to the subject of how companies communicate their brand messages visually.

Creating a very wowza pivot chart, using the online training site lynda dot com.

Pivot charts are a great place to start a visual communication or create a dashboard that points up (actionable) insights. A former senior executive of a major publishing company told me in confidence that even at their good offices, pivot charts were king.

“We didn’t want to be limited by the canned reports that we had in our archaic systems. Instead, we would perform a monthly dump of sales data into Excel with pre-defined pivot tables showing the metrics we wanted to look at. Then, we would analyze it all with the pivot charts, which offered a super easy way to handle ad hoc queries.”

Photo of Lance Armstrong courtesy of Wikimedia Commons.

Just as kids are back to school this week, are you, too, ready to upgrade your software skills? Share in the comments.