OK, so you’re really good with Microsoft Excel.  You can write bi-conditional nested if statements and vlookups that make peoples head spin.  You’re the only person in your tri-county area that can make a dynamic pivot chart with multiple vertical axes that retains its formatting.  Maybe you’ve even created user-defined functions that have replaced your entire secretarial staff, or written VBA subroutines that made your co-workers shed tears of joy.  Well, this post is for you; the lonesome few that have mastered the tools and are solving complex problems at work, but are looking for a way to tie it all together in a way that takes your career to the next level.

I see two distinct paths you can take as an Excel Guru:  The first is you quit your job, become an independent contractor spending 8 hours a day solving complex issues with various implementations of Excel and Business Automation.  Excel consultants such as these are in demand and making enormous hourly rates. The other path is for those who love the challenge of climbing the corporate ladder, taking on more and more responsibility, and eventually leading other Excel gurus .  Both of these routes have one major thing in common: you have to be able to connect your technological savvy with the business needs of your client or boss.

So how exactly do you translate those skills into value-added business skills?  Here’s an example to get you started:

“The Holy Trinity” -  Raw Data Tab / Static Formula Columns / Pivot

Here’s a framework in three steps that works when you are given a weekly raw data feed and asked to make a report.

1) The Raw Data

First make a “Raw Data” tab on your Workbook and paste that data in exactly as it is in the weekly report.  Don’t delete unneeded columns, or filter out erroneous records, we’ll let Excel do that for us later on.  Any steps you take to pre-process the data now are steps that you’ll have to take each week, which means more work and more potential for making “human” mistakes.  “Paste in the new data, hit refresh, and you’re done” should be your new mantra as you design the report.

2) Static Formula Columns

Next you’ll want to make static formula columns on the raw data tab.

You have data like this:

The first thing you want to do is add calculated fields for values that you’ll need to pivot on later.  The trick here is that the Raw Data coming in each week must always have the same number of columns, say A through H, so that you can add your formulas to I, J, K, etc and know that when you paste your new data in next week they won’t be written over.  The most common formulas here are breaking the “Date” field into “Month”, “Data”, and “Year” (as outlined below) but the possibilities are endless.

Once you’ve added the new columns, enter the formulas

Once you’ve entered all of your formulas, “fill” them down to the bottom of the data.  To more easily identify these calculated fields columns later on, I always make them a different color.

Some other examples of useful columns here are:

• City or State using a vlookup with some unique identifier on another sheet
• Actually constructing your unique identifier key by concatenating several other values together, e.g. =A1&A2&A3 (this one can be really useful in certain situations)

Remember that if you’re dealing with a dataset that has a varying number of rows each week, you’ll have to make sure to drag the formulas down to the end of the new data once you paste it in over the previous weeks data.  That’s a heck of a lot less painful that re-creating the report from scratch each week.  If you wanted to really get fancy you could even do a:

=if(iserror(your_formula_here)=true,”",your_formula_here)

Which would make the formulas blank on rows that didn’t contain data.  Then you could pull the formulas down “to infinity” and never have to worry about it again.  Just watch out for bloated file sizes.

3. Putting it All Together
Here’s is the fun part: Now we’re going to take the data, along with its static formula fields, and translate it into something readable and useful by businesspeople. Data-wise, focus on using pivot tables or pivot charts when possible to automate the report.  Also, ask the client if they are interested in a dynamic report (where the user can interact with the data via pulldown menus, etc.) and design accordingly. 

• W.I.F.M.
  • The one question on a person’s mind when they look at your report is going to be “What’s in it for me?” Think about the needs of the person looking at your report, and cater your deign to those needs.  Try to keep out extranous or non-essential information and get right to the point.
• K.I.S.S.
  • “Keep it simple, stupid!” You’ll want to keep the design of your report elegant and simple, yet rich with useful information.  Here are some experts’ take on report design to help you discover the process yourself:
    • Check out Edward Tufte, who is the quintessential master on the topic of data visualization.
    • Check Out John Peltier’s Site for great tutorials on how to build elegant charts and other visualizations in Excel
    • Get some great design ideas from FlowingData.com
    • The Infosthetics Blog
    • Some great ideas for the advanced user can be found on this blog post, over at Smashing Magazine

Here are some other Excel topics we’re considering writing on next… cast your vote in the comments below for which one you like best, or suggest your own idea:

• Upgrading to Excel 2007: Navigating the Minefield
• Statistical Analysis for Excel: The Missing Guide