Excel for Consultants – Key Skills for Your Life as a Consultant

If there ever was a software guilty of feature-overload, Microsoft Excel would probably be at the top of that list. It is an industry standard for casual consumers, amateurs, professionals, and even businesses. Seldom does a software reach this pinnacle of having the flexibility, ease, and yet the power to serve & dominate all segments of users. I am sure Microsoft does not know the full extent of ways in which perhaps the only other software to have ever even been in the vicinity of that pinnacle, are Adobe Photoshop and Microsoft PowerPoint.

The Most-Common Use Cases for Excel

In consulting, these are a few most-common use cases for Excel:

1. Data Setup:
   Cleaning-up and setting-up client’s financial and operational data for further analysis
2. Data Analysis:
   Analyzing the client’s financial and operational data to draw insights and prove hypotheses that will help in moving the case forward
3. Showing Insights:
   Create charts from the outputs generated during data analysis to help the client in easily visualizing insights. These charts will be typically be embedded in slides.
4. Financial & Business Planning:
   Creating financial models for the client’s existing and/or new businesses that will allow the client to take investment decisions
5. Build & Transfer:
   Providing all your analysis and models to the client upon project conclusion
6. Project Management:
   Creating planners and gantt charts for managing a client project or helping the client in managing one of their own projects
7. Personal Management:
   Creating your personal activity tracker
8. And many others…

Essential Excel Skills

Key skills required across these use cases are as follows. The list below is only an introduction to the most essential skills in excel. There is no end to Excel mastery. As you work with it and learn it on your own – always ask yourself if you can do things more efficiently. Chance are you can – and chances are that excel has a feature for that already.

Formatting

A significant majority of people do not focus much on the formatting side of Excel – they feel its just numbers and calculations. On the contrary, it is quite important. It helps in making the excel files more presentable, easier to understand, easier to operate, and it makes Excel a little less boring. Here are some of the things you need to consider when thinking about formatting in Excel.

• Number formatting:
  This is a very important skill that many people do not learn. When you are working on a financial model, you will encounter millions, billions, normal numbers, negative numbers, decimals and a whole lot more. Most people simply divide the millions with 1 million to make it appear as a normal number. However, this makes your sheet prone to errors. You might forget to convert the millions when needed. You might even forget it is in millions. With number formatting, you can instruct excel to show numbers in the specific format you would like while retaining the full and actual numbers in the cell. Number formatting has a lot to learn and can elevate your excel files beyond recognition if used properly.
• Conditional formatting:
  You can automatically make certain cells look different color and style based on certain rules. This makes excel files appear dynamic and also increases their functionality. Excel has several dozen ways to implement conditional formatting – cell shading, graded shading, color bars, mini graphs called sparklines, and a lot more. If used properly, conditional formatting can provide an additional layer of information over and above the numbers.
• Cell shading:
  By default, all cells in Excel look white (although they are supposed to be ‘transparent’ technically). By shading or filling cells with appropriate colors, one can differentiate them visually and also indicate the different purposes of the cells. Header rows could be one color, and total rows could be a different color. Subtotals could have a lighter shade from that of the total row. Certain cells that need attention could have a brighter shade, and so on. Too much or too little formatting might just look bad. One needs to always sense-check if the shading is adequate and aesthetically pleasing. The color shades could be borrowed from the client’s representative color palette.
• Cell borders:
  By default, the border lines that we see in excel are actually only gridlines, not borders. Excel allows you to add cell borders that can help differentiate cells better. You can select the color, thickness and dash-style of these borders. There are some pre-determined options available in excel but you can always customize them to your hearts content.
• Gridlines:
  Once an excel file is fully developed (including cell shading and borders), you can choose to hide the gridlines (go to View menu). This makes the sheet look a lot cleaner.
• Text formatting:
  This includes font, font size, font color, font style, highlights, etc. Make sure to format the text properly to appear aesthetically pleasing and consistent with the rest of the sheet.

Formulas

This is the ultimate bread & butter of anyone who is going to use excel. Formulas allow you to work the magic of excel and create meaningful output. Formulas can be used to manipulate numbers, text, logic, dates, and much more. Always keep in mind – “there is probably a formula for that”. Over the years I have found that many people continue using basic formulas to do compound tasks that could have been done in a single stroke with the right formula. This article is not the right place to explain the workings of each and every formula in excel. However, below you will find a small list of the formulas that you will most likely use as a consultant:

• Arithmetic & Statistics:
  AVERAGE, COUNT, COUNTA, COUNTIFS, FREQUENCY, MAX, MIN, PERCENTILE, PERCENTRANK, STDEV, SUBTOTAL, SUM, SUMIFS, SUMPRODUCT…
• Arithmetic Manipulation:
  ABS, CEILING.MATH, FLOOR.MATH, MROUND, POWER, RAND, RANDBETWEEN, ROUND, ROUNDDOWN, ROUNDUP, TRANSPOSE…
• Financial:
  NPV, IRR, PMT, PV, FV…
• Logical:
  AND, OR, IF, IRERROR, NOT…
• Text:
  CHAR, CONCAT, FIND, LEFT, MID, RIGHT, TRIM…
• Date & time:
  DATE, DAY, DAYS360, NETWORKDAYS, TODAY, MONTH, YEAR…
• Modeling:
  INDEX-MATCH, INDIRECT, OFFSET…
• Information:
  ISBLANK, ISERROR, ISNA…
• Formula Referencing:
  Fixed, Relative, Partial, Ranges, Arrays…

You may notice that one of the most talked about formula is not mentioned in the above list – VLOOKUP. While it is a highly popular formula, it also a bad habit for a productive consultant. A better formula to use is the INDEX-MATCH combo. INDEX-MATCH is significantly faster than VLOOKUP, less prone to errors, does not break with changes in the sheet, can work in 2-dimensions simultaneously, and is vastly more flexible. Do the right thing today itself – chuck VLOOKUP out of the window and adopt INDEX-MATCH.

As you learn formulas, you should also learn how to trace them. This is beneficial for resolving error. It is an excel feature that allows you to do things like check dependent and precedent cells, perform step-wise calculations, identify circular references and many more.

Charting and Graphing

Excel work is never going to remain isolated to Excel. Almost all the time, you will have to transfer your analysis from the sheets into slides. And one of the most popular ways to do that is by creating charts and graphs. There are two ways to do that:

• Excel’s built-in capabilities:
  Excel has a reasonably good set of charting options available – bar charts, line graphs, scatter, bubble, and so on. However, there are a few shortcomings that prevent the graphs from being professional enough or convenient enough. If you want to customize the layout, adjust the labels dynamically, add dynamic trends, and so on – you will feel a bit wanting and struggling. Not to mention, the overall look and feel of the built-in charts can take a fair amount of tweaking to make them look professional enough. These shortcomings are solved by charting add-ons such as Think-cell.
• Think-cell (or equivalent):
  Think-cell is the consulting industry standard when it comes to charting. Chances are, your laptop would have it pre-configured and integrated with MS Office. It contains all of the Excel’s built-in chart types and many more such as Waterfall, Mekko, Gantt, Process, Agenda, Smart elements, and many more. It is also more flexible, easier, and faster than built-in excel charts. Learning Think-cell is also easier than learning Excel’s built-in charting tools.

Shortcuts

They say that the ideal consultant should not need to use their mouse when using Excel. Almost every action in Excel can be executed using a keyboard shortcut. Navigating the sheet, filtering, sorting, and many other everyday tasks can be performed in a quicker and smarter way using shortcuts. Up until Office 2003, we used to have standard menus similar to most applications on Windows. However, starting with Office 2007 and onwards – the standard menus were replaced by the now ubiquitous Ribbon. Hence nowadays there is a composite set of shortcuts borrowed from both the eras. Always keep in mind – “there is probably a shortcut for that”. The latest list of shortcuts are available on several websites including Microsoft, and of course the ideal way is to learn as you work, instead of cramming all shortcuts like a syllabus. They are no good if you don’t have them in your muscle memory – and that can only happen over-time with enough practice.

Data Analysis

This is not only a use-case, but also a skill. You will often find yourself sifting through client data to prove certain hypotheses, create visualizations & charts, or simply create small tables that would go as-is into slides as per the storyline. Either way, you need certain data analysis skills at every stage of the way:

• Data Cleaning:
  In order to make the data usable, you need to do a lot of cleanup. Some of the skills required include text-replacement, trimming of leading & trailing spaces, splitting text into columns, removing duplicates, converting text to numbers, converting text to dates, and many more. Keep in mind that most of these tasks have a direct formula or some functionality built-in.
• Basic Data Analysis:
  At minimum, you would need to understand basic data cleaning and data operation concepts such as single-field filtering, multi-field and multi-value filtering, color-based filtering, single-field sorting, multi-field sorting, totals, averages, deviations, and so on. I will iterate again that most of these tasks can be done through a formula or a convenient built-in tool in Excel. Be sure to search for it before taking the longer route.
• Advanced Data Analysis:
  Moving further, some of the advanced Excel data analysis capabilities you would need would revolve around drawing complex insights, creating sensitivity analysis, finding optimal scenarios, and the lot. The 3 key excel skills you would need for this would be Pivot Tables (for drawing complex insights), Data Tables (for sensitivity analysis), and Solver (for finding optimal scenarios). Each of these is a fairly involved topic within itself – especially Pivot Tables. SO ensure you’ve done your proper homework.
• Modeling:
  We now move from tool-based skills to slightly more artistic skills. Excel modeling is an art unto itself and one cannot simply learn it overnight. You may know all the tools, the formulas, the analysis tools, and all. But perhaps the best way to become an expert in modeling is to learn from example, lead from common-sense, and finish in style. So instead, I will list out some of the features of a good excel model. Many of these features are hard-to-find in the models of many consultants – and you would do well to learn & imbibe these over the course of time:
• Inputs sheet:
  All the inputs & assumptions going into the model should ideally be kept in separate sheet(s) that contain nothing else. Mixing inputs into other sheets that may have calculations will make the model confusing, error prone, and difficult to modify & maintain. Keep your input cells properly labeled and neatly arranged. This will make it easy to explain the model and transfer knowledge. Inputs & assumptions are typically the most important considerations for Partners and Clients. They seldom look at the mechanics of the model, but they dwell a lot on the inputs. If your inputs are scattered in different sheets, it will definitely confuse the Partner and likely make things difficult for you. Depending on the complexity of the model, you might need multiple sheets for inputs – which is also fine.
• Outputs sheet:
  They next important thing that a Partner or client would look at is the outputs from the modeling exercise. The best practice is to have 1 sheet that consolidates all possible outputs from everywhere across the model, and have another 1 sheet if needed – to distill more relevant outputs for a focused discussion.
• Dashboard:
  A dashboard is a fusion of inputs and outputs, laid out in an aesthetically pleasing manner. The inputs in the dashboard are connected back to the inputs sheet. This means that when you change an input in the dashboard, you change it in the model. Hence the outputs can change dynamically. This makes it very easy to have iterations on assumptions and also develop sensitivity analyses that can be useful for certain projects.
• Hard-coded inputs:
  This is one thing that you must NOT do. Never have a static value embedded in a formula anywhere in the model. Always pull it out of the formula and represent it as a separate input in the inputs sheet. Hard-coded inputs are the most common source of errors – they are hidden, they are forgotten, and they are confusing. When you embed a hard-coded input, only you know about it. After a while, even you will forget about it – let alone anyone else who will be operating your model. Do the right thing today and chuck hard-coded inputs out of the window for good.
• Lookup tables:
  Many times you would be using certain values in a model over & over – such as currency conversions, unit conversions, abbreviations, drop-down lists, among others. It is advisable to have all of these values laid out in a separate sheet of their own. It makes the model much cleaner and easier to maintain.
• Named ranges:
  This is a key different between excel models by amateurs and pros. In Excel you can give names to specific cells or ranges of cells. So those cells can be referred anywhere in the model directly with the name – no need to used the cell address. This has several advantages. It makes your formulas easier to understand, it allows you to modify the range of cells without having to rewrite the formulas, it makes your models less error prone, and easier to maintain.

About the Author