How to crush it with Excel – practical guide for Management Consultants

In Consulting you will have one friend that always will be there for you, who will be helping you in ways you can’t imagine. You will spend more time with him than with your family and friends. If you treat him nicely and spend time with him he will help you with your career, private life, and your future. His name is….. Excel.

You may think that this is an overstatement but trust me a badass consultant always trusts his Excel and spends countless hours perfecting this tool.

As a Business Analyst or Management Consultant, you will be spending 3-5 hours a day crunching data in Excel and doing analysis. That is why you should become a Master of Excel and be able to do fast analyses. In this way, you will have a chance to have a life outside your time-consuming work. People trained by me are able to do business analysis in Excel 10x faster than most of the business analysts and management consultants working in top consulting firms. 50% of the effect comes from knowing the right formulas and applying certain rules in analysis. 30% is coming from applying Excel shortcuts that drastically change the speed at which you are able to produce analysis in Excel. The rest is achieved by using templates and constructing your analyses in such a way that you can easily transfer them to Excel.

Let’s go one by one and see what you have to master to become a badass consultant

Useful Excel functions.

Let’s start with the functions that will help you do things much faster:

1. VLOOKUP. The most versatile function you can imagine. You can assign categories using it, replace complicated IFs, and merge data. VLOOKUP can be also combined with other functions to do more complicated things. Below is an example of combining VLOOKUP with the MATCH function that we use to assign categories based on 2 criteria:
   
2. PIVOT table. You can fast analyze data using them and go deeper into data if there is a need for it. They also help you create dashboards, go fast from general to specific, and filter data. A badass consultant quite often uses them to have the first glimpse at the data. There are also great for creating good-looking end-products for customers. Let’s look at a dashboard created by using Pivot Tables and Pivot Charts:
   
   They become especially powerful if you calculate inside the Pivot Tables additional items not listed in the original list. Let’s have a look at an example:
   
3. SUMIFS, COUNTIFS – those functions are the perfect substitute for Pivot Table. Not that elegant but very versatile. In many cases, you will not be able to use the Pivot Tables. In those instances, those 2 functions come in handy. They can also be used instead of VLOOKUP if you have more than 2 criteria. Below is an example of such an application of SUMIFS:
   
4. Functions that enable you to work on the text variables: CONCATENATE – helps you combine text from 2 different fields, LEN, RIGHT – enables you to take a few letters from the text starting from the right / end (i.e. the first 3-4), LEFT – similar to the right you just start from the left / beginning of the world. You can also find specific characters i.e. the “-“ with the SEARCH function and use it to divide a word into 2 words. All those functions are extremely powerful once you want to clean and unify records (this is 70% of your work during analyzing data)
5. INDIRECT – this function enables you to create a family of functions with 2-3 clicks. Using this function you can create a function that works on different sheets without needing to type the name of every sheet separately. Have a look at this example to see how powerful this function is
   
6. Other logical functions. On some rare occasions to create some logical rules, VLOOKUP will not suffice. In those cases, the traditional IF combined with the AND, OR function is a good choice. In many cases, a better function is IFERROR which creates IF conditions if an error occurs (i.e. you can tell him what to put if you divide by zero – which still is illegal 😉 ).
7. Relative addresses. You usually use in Excel absolute addresses (the number of rows and the column on which you work i.e. A4). You can replace it with the relative addresses where you tell him for example to use the column which is to the left of your current place and 1 row above. It is a very powerful feature and you may find it useful in more complicated analysis
8. Data Validation – it is not a function yet a built-in feature. Still, you can use it to limit the choices for the data input i.e. limit the things he can put in a specific field to 5 pre-defined choices. If we were talking about the status of the project you can have i.e. the following choices: on-track, done, delayed, or on hold. In this way, the data input is much faster and you don’t need to waste time later on modifying the data. You are able to save a lot of time thanks to this function. To create a dynamic drop-down list you would have to combine Data Validation with Excel Tables.
9. Other useful functions: OFFSET, MOD, SUMPRODUCT, OFFSET, YEAR, MONTH, DAY, ROUNDDOWN, MATCH, HLOOKUP, RAND, MAX, MIN, CORREL, How and when to use them you can find them in my presentation Essential Excel for Business Analyst.
10. Other built-in features. Excel has also a lot of useful built-in elements: Slicer, Pivot Charts, Data Analysis Tool Pack (especially Solver – have a look at an example of how to use solver) Slicers, and Formats. They come in quite handy on many occasions. Let’s look at an example of an analysis done with the help of a solver:
    

To use the solver it’s a good idea to use the so-called linear programming that helps you define the problem in a specific way. Let’s look at an example of a firm producing burgers. We will have them find the optimal production mix of 2 categories of burgers:

Shortcuts and other helpful tips

Apart from the functions, there are some hacks, and tips that will help 10x your efficiency in Excel. Below is my favorite pick

1. I don’t use the mouse. You can do most of the things with shortcuts that save you tons of time. Below is my favorite pick for PC (Mac is not the best choice for Excel freaks)
   • Ctrl + C to copy and Ctrl +V to paste it
   • Shift + Space to mark the whole row. After you mark it you can remove it by pressing Ctrl + “-“ or add a new row by pressing Ctrl+ Shift + ”+”
   • Ctrl + Space to mark the whole column. After you mark it you can remove it by pressing Ctrl + “-“ or add a new row by pressing Ctrl+ Shift + ”+”
   • Ctrl + 1 to get the menu to format the cells.
   • Alt + E + S to get the menu to paste special to the staff. You can move even further i.e. press “V” to paste values and i.e. “E” to transpose the whole columns
   • Shift + Space and then arrow up or down to mark the area. In the second step press Shift + Alt + Right arrow to group the rows you have marked. By pressing Shift + Alt + Left arrow you can ungroup them
   • Ctrl+ Space and then Shift + arrow left or right to mark the columns. In the second step press Shift + Alt + Right arrow to group the rows you have marked. By pressing Shift + Alt + Left arrow you can ungroup them
   • Let’s  see how to use 7 shortcuts in practice:
     Shortcuts enable you also to move faster in Excel which in turn helps you save a lot of time. Below are a few tips on that:
     
2. Use colors to put everything into order. I use colors to make Excel more clear and easy to read. I use for formulas white and for things fed in manually the blue color. In this way, I know what are the drivers of a specific analysis (the staff in blue). Sometimes I also use orange to mark things from different sheets. Have a look at the analysis that is using such coding
3. Master sheet. For a bigger analysis, it is worth putting a master sheet that has a table of content with links to all sheets in the file. In this way, the file is easier to navigate. In every sheet, I also put the “back” world with a hyperlink to the Master sheet. In this way, I am 2 clicks away from any sheet no matter how big the file is. Have a look at the analysis that is using such a Master sheet
4. Use Excel Tables to make certain parts more dynamic. Excel tables help you browse through big tables with the help of slicers. They can also automate the Pivot Tables and Pivot Charts. On top of that, you can use them to create dynamic drop-down lists. Let’s see how this can be done in practice:
   

Excel Tables

Usually, when using Excel you will be using the so-called range which is a collection of cells. This means that every cell is treated as a separate entity. To address a bigger number of cells (for example aggregate the value from those cells) you will be using the addresses of those cells (e.g., C5:F10 would mean cells in the rectangle starting from C5 and ending with F10). However, you can also use Excel Tables which are treated by Excel as a separate entity. An Excel Table consists of columns. In every column, you should have a different variable. Excel Tables help you browse data fast and make the calculations more dynamic (for example when you combine them with Pivot Tables). You can also use slicers (similar to Pivot Tables) in Excel Tables to find the right element. Let’s look at some basic operations you can do in Excel Tables:

1. Creating Excel Table. You can create the table in 2 main ways. Let’s see how this can be done in Excel and what are the implication of using Excel Tables when it comes to functions used
   
2. Renaming Excel Tables. If you work with more than one Excel Table, you will most likely want to change the names of the Excel Tables. It will make it easier to use them. You can also rename a specific column of the Excel Table. Let’s see how this can be done in practice
   
3. Formatting Excel Tables. You can also change the look of the Excel Tables. Let’s see how this can be done in practice
   

Power Query

If you spend a lot of time on improving the quality of data or you connect data from different sources, you should definitely also check the add-on for Excel called Power Query. It enables you to automate a lot of operations and it can work directly on the source without the need to download the data into Excel. Let’s look at some examples of how you can use them in practice:

1. Clean data and improve their quality. In many situations, you will be given data that due to manual errors are of low quality. Before you start analyzing, you have to standardize the data and improve its quality. Power Query can let you do it much faster directly in Excel. Let’s look at an example:
   
2. Combine data from different sheets. Sometimes you need to combine data from different sources like files, sheets, or databases. Power Query is a perfect solution for that. Let’s see how you can combine data from different sheets of the same file:
   
3. Standardize data using AI. Power Query has also AI that helps you create the formula automatically that will translate original data into an improved data set. Let’s look at an example:
   
4. Unpivot Data. Sometimes you will be given data in a form of a pivot table or something which looks similar. Since you will use the data for multiple analyses, you will want this data to be transformed into a regular table. This operation is called unpivoting and Power Query has a built-in capacity to do that:
   
5. Built-in transformations. Power Query has also plenty of built-in transformations of data. For example, you can extract the year, month, and weekdays without typing any formula or code. You can also extract specific text much easier and faster than in Excel. Again you just select the right option and you don’t have to type in any formulas.
6. Built-in Capacity to create new data. In a similar way, Power Query also enables you to create new data (columns) based on existing data. Let’s look at how you can create a new column using something that resembles the IF functions:
   

That’s in short what you need. If you need more practice check my courses related to Excel. They will help you 10x your productivity and do work much faster. The courses are used by more than 175 000 students including people from EY, PwC, McKinsey, Bain, BCG, Walmart, and IBM:

1. Essential Excel for Business Analysts and Consultants
2. Excel Pivot Tables for Management Consultants & Business Analysts
3. Financial Modeling in Excel
4. Online Business Models in Excel – Practical Guide
5. Offline Business Models in Excel – Practical Guide