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 overstatement but trust me a badass consultants always trust his Excel and spends countless hours in perfecting this tools.
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 the 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 business analysts and management consultants working in top consulting firms. 50% of the effect comes from knowing the right formulas, applying certain rules in analysis. 30% is coming from applying Excel shortcuts that drastically changes 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 it 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:
- VLOOKUP. The most versatile function you can imagine. You can assign categories using it, replace complicated IFs and merge data. Have a look at some movies showing how to use the VLOOKUP, MATCH to analyze – these are typical analyses done by Business Analysts and Management Consultants.
- 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, 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.
- SUMIFS, COUNTIFS – those functions are 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
- 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 letter 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 character i.e. the “-“ with the SEARCH function and use it do divide a word into 2 words. All those functions are extremely powerful once you want to clean and unify records (this is 70% of you 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 are a good choice. In many cases a better function is IFERROR that 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 row 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 very powerful feature and you may find is 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, on-hold. In this way the data input is much faster and you don’t need to waste time later on to modify 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 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 how to use solver , Slicers, Formats. They come quite handy on many occasions
Below the most useful function and features in the form of a presentation:
Shortcuts and other useful tips
Apart from the functions. There are some hacks, tips that will help 10x your efficiency in Excel. Below my favorite pick
- I don’t use the mouse. You can do most of the things with shortcuts that save you tones of time. Below 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 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 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 second step press Shift + Alt + Right arrow to group the rows you have marked. By pressing Shift + Alt + Left arrow you can ungroup them
- For more useful shortcuts check my Top 20 Excel shortcuts. They will make your life much easier.
- Use colors to put everything into orders. I use colors to make the 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 analysis that is using such coding
- Master sheet. For 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 back word with 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 analysis that is using such Master sheet
If you need more practice check my courses related to Excel:
- Essential Excel for Business Analysts and Consultants
- Excel Pivot Tabels for Management Consultants & Business Analysts
- Financial Modeling in Excel
- On-line Business Models in Excel – Practical Guide
- Off-line Business Models in Excel – Practical Guide