How Consultants Use Excel

This article is for anyone who wants to understand how consultants use Excel. Whether you are an aspiring consultant or already on the job, a better understanding of how consultants use Excel is one of the keys to drastically increasing your productivity. Consulting Excel skills is honestly something that you can’t get too much of!

One common tradeoff that all consulting analysts face early in their careers is trading time now for time later. You will likely have a variety of analytical tasks to perform early on where you can somewhat quickly get the job done manually. Or, you can invest the time to learn the appropriate Excel formula, which will take time for you to learn now. But once that learning occurs, that task can take you seconds instead of minutes in the future. Early in your career, we recommend investing heavily in building your Excel skills.

Let’s explore some common consulting Excel skills.

Needed Excel Skills for Consulting

Below you’ll find a list of Excel formulas and concepts along with descriptions and examples of why they matter. Remember, excel for consulting is a little different animal that what you may be used to.

Pivot Tables

Pivot tables allow you to quickly and easily analyze large data sets. Say you have a large data set with lists of customer purchases. A pivot table allows the analyst to “pivot” on certain dimensions, like customer or product, to display summary metrics like the total count, sum, or average.

Vlookups, Hlookups, Index/Match

Imagine that you have one data set with profitability information about customers. But you also have another data set with descriptive information, like size and industry for each customer. A vlookup, hlookup, or index/match function would allow the Excel analyst to link the two data sets, and pull in information from one data set into the other. For example, you would vlookup on the customer name, and bring in the type of “industry” into the profitability data set. To learn more about vlookup functions, click here.

Arrays

An array formula is one of the more complicated Excel formula options. An array formula allows you to perform multiple calculations on items in an array. A good definition of an array is a row of values, a column of values, or a combination of both. Basically, array formulas allow you to perform calculations on only elements of the array that meet certain conditions so you can create things like subtotals or averages for certain sub-categories of the data. You can learn more about arrays here.

Macros

Macros are another advanced Excel technique. They basically allow you to “record” a certain series of steps and then repeat them on new inputs. They often are used when the “formula” required to perform an analytical task would be overly complicated to write. Many highly successful consultants never really learn to use macros, and that’s OK. This is one type of consulting Excel skill that is a bit of a bonus. They are nice to know, but not really necessary.

Shortcuts For formatting

Management consultants often want to format their data to make it easier to digest the analysis and identify the insights. Having too many decimal points or no bolding can just make it difficult to interpret information. For example, holding “control + B” applies bold font to whatever you are highlighting. This is a simple but often used formatting shortcut. Adding borders or decimal points or coloring to cells are other good examples of Excel short cuts for formatting.

Text To Columns

Sometimes, you’ll have a long list of text-based data that you need to break up somehow. For example, imagine you have a column and each cell in that column is a customer’s full name. But you want to identify just their first name. Highlighting the column of data and applying “text to column” would allow the consultant to separate this data into two columns, one for first name and the other for last name. The Excel technique accomplishes this by allowing you to separate the data in the cell based on the existence of a space in the data. So “John Doe” gets split into one column for “John” and the other for “Doe” based on Excel seeing a space between John and Doe.

Conditional Formatting

Conditional formatting can be used to draw out insights in the data without writing additional formulas or adding columns or rows of data. When looking at a matrix of data, one can make every cell “green” that is above a certain level by highlighting a column and using the conditional formatting functionality in Excel.

If Statements

If statements are a powerful and popular Excel formula. The Excel analyst can apply a different formula (or no formula at all) to a data point only if it meets certain criteria. Imagine you are analyzing survey data where customers have told you whether they intend to spend less, about the same, more, or a lot more in the next year. You could write an If formula that projects their future spend using a different percentage of growth, based on how the customer answers the question about their future spend intentions. Learn more about If statements here.

Min Max Functions

Min max functions are somewhat simple Excel functions. They, as their name implies, allow you to get the minimum or the maximum value in a range of data. By typing =”min (INSERT COLUMN OR ROW OF DATA)” and pressing enter, you’ll get the smallest value in the data range as the result.

Sum Product / Sum If

“Sum product” returns the sum of products of corresponding ranges of data. Imagine you have a column of data for “price” and another column for “minimum quantity” for various products. A “Sum product” formula could be used to estimate the minimum amount of revenue by automatically estimating the revenue for each product (price x minimum quantity) and then adding it all up.

Sum if functions allow a consultant to calculate a sum of a data range, but only if certain criteria are met. You need three columns of data, and you type in =sumif(range, criteria, sum_range). The range is the list of data against which you want to test the criteria. Let’s say you are summing up the population of states in the Midwest. The range would be the column that indicates if a state is in the Midwest, the criteria would be “Midwest” and the sum_range would be the range of data in the column for “population.”

Truncate

This is a simple formula used when you want to truncate a number instead of round it. It allows you to return “4.0” every time the data says “4.7”. It is useful when you don’t want to round data, but instead just need to know the correct integer. It’s a simple consulting excel skill, but a time saver in the long run! Learn more about truncating here.

Proper, Lower, Upper

Excel has formulas to turn data into upper case or lower case. This is what proper, lower, and upper formulas do. Learn more about these formulas here.

Goalseek

When you have an Excel model built, you’ll sometimes know the result you are looking for but aren’t sure what assumptions are going to be necessary to achieve that result. For example, imagine you have built a valuation model. You want to know what rate of profit growth is necessary to assume to get your model to say the stock is worth $100. Goal seek can be used to determine that rate of growth. Here are simple examples of this type of goalseek analysis.

Data Tables

Data tables are all about scenario analysis. Imagine you’ve built an Excel model to measure and analyze a pricing strategy. Should you raise or lower prices? This depends on how much volume you’ll gain or lose when you raise or lower the price. Instead of looking at scenarios one by one, analysis with data tables creates a matrix of data showing the “results” of various combination of price and volume. You can learn more about analysis with data tables here.

Advanced Chart Techniques

There are a lot of interesting ways to visualize data in Excel. Current versions of Excel go far beyond simple bar charts, line charts and scatter plots. You can create waterfall charts to show how you get “from here to there” – from profit last year to profit this year. Other types of advanced charts available in Excel include radar, histogram, funnel, and maps.

Circular error troubleshooting

Circular errors occur in Excel when you ask Excel to calculate a value in one cell that depends on calculations in another cell. But that cell is itself referencing the first cell in the chain. In other words, a circular reference has occurred and Excel can’t work through the formula. This blog article can teach you more about circular references and provide some tips and tricks for fixing them when they do occur.

Consulting Excel Examples

There are innumerable uses for Excel in consulting. We have been working as consultants for ~15 years, and Excel remains the primary analytical tool consultants use to analyze data. Recently, other tools for managing very large data sets have come on to the scene. That said, the current version of Excel can handle over a million lines of data (the first version we worked with could only handle 50,000), so Excel remains incredibly relevant.

Here are three extremely common uses for Excel:

Valuation models

Excel is what consultants use to place a financial value on companies or business opportunities. If you want to forecast cash flows based on assumptions around growth and margins, you do so by building a model in Excel. Goalseek and if statements feature heavily in value model development. If you need to build banker-level valuation models, check out the great courses at Breaking into Wall Street.

Profitability insights

It is so common for consultants to be tasked with analyzing data to understand where and how a client is making money. Where are profits higher or lower and why? A wide variety of formulas is used in profitability analysis. Consultants will often want to create sub-totals and understand the margins associated with sub-segments within the data set. Basic and conditional formatting can also be very helpful when developing profitability insights on large data sets.

Survey data analysis

Many consulting firms conduct surveys of a client’s commercial teams, leadership teams, or customers to develop insights about its business model. For example, one member of our team was involved in a project where they surveyed a client’s sales force to understand how they approached the process of setting prices. In analyzing this set of survey data, advanced charting featured heavily. What the team really needed to see were the survey results in a way that allowed the insights to “pop” off the page.

Excel Training For Consultants

If you are embarking on a career in consulting, particularly as an undergraduate, you want to embrace the use of Excel and become as much of an expert as possible. By developing “expert level” Excel skills, you’ll allow yourself to either a) develop better insights or b) get to basic insights faster. This will allow you to develop better written PowerPoint recommendations or play a bigger role on your team suggesting creative ways to solve problems on the project. Check out FastTrack – two of our best courses to help you accelerate your consulting career. If you can’t yet drive executive decision-making through your models and presentations, FastTrack is for you!

Conclusion

It is almost impossible to imagine a successful career in consulting without a least average Excel skills. Most consultants build these skills through trial and error. But investing a few hundred dollars to have the most important techniques taught to you could be an investment with incredibly high ROI. As we started off the article noting, it is wise to invest the time upfront to learn how to do things efficiently in Excel. This approach allows you to spend more time, in the future, on other key aspects of consulting that don’t involve Excel.

Additional Reading:

• Fast Math: Quant Skills
• Math Drills
• Financial Modeling 101: 6 features of a killer consulting financial model
• Financial Modeling 102: 4 major types of consulting models