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 Analysts 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
Let’s start with the functions that will help you do things much faster:
- 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:
- 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 the 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 example:
- 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:
- 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 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)
- 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
- 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 😉 ).
- 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
- 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
- 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.
- 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 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 useful tips
Apart from the functions, there are some hacks, and tips that will help 10x your efficiency in Excel. Below is my favorite pick
- 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 a few tips on that:
- 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
- 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 a backword with a hyperlink to the Master sheet. In this way, I am 2 clicks away from any sheet no matter how big the file. Have a look at the analysis that is using such a Master sheet
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 125 thousand students including people from EY, PwC, McKinsey, Bain, BCG, Walmart, IBM:
- Essential Excel for Business Analysts and Consultants
- Excel Pivot Tables for Management Consultants & Business Analysts
- Financial Modeling in Excel
- Online Business Models in Excel – Practical Guide
- Offline Business Models in Excel – Practical Guide