Excel is the saviour for most consultants looking for quick ways to calculate and manage data and make financial models. But the knowledge needed to perform these tasks efficiently is vast and complicated. So to simplify it for you, here is the ultimate Excel list of things to know about this programme for consultants. Short bursts over a long period of time will ensure that these become innate skills. So click on one of these links each day, watch the videos and read the articles and over the summer before you start your graduate placement, you'll be ahead of the game!
Basic Introductions
Day 1 Learn the Excel interface - Probably the most basic introduction to the Excel interface and what different things in Excel look like and do. Simply an explanation of how the programme works and how to perform basic tasks. More
Day 2 Sort a list - Needless to say, sorting lists can organise your data in more practical ways and it is very simple as Excel has the capacity built in to do this. Here are three ways to use it.
Day 3 Filter unique items on a list - Sometimes you may want to filter lists according to specific criteria. Read this basic intro to filtering out data and removing duplicates in the sort. More
Now watch this short video on sorting and filtering to recap
Day 4 Create a Custom List - When looking to sort according to criteria that Excel can't do automatically such as alphabetically or numerically then you will need to create a custom list. This article shows you how here
Day 5 Basic SUM, AVERAGE and MAX/MIN functions - This is a quick video intro to entering data and using these simple functions using a very basic table of data.
Day 6 Customise and format data - Excelteacher offers a video tutorial here on how to generally customise Excel worksheets with colours and formats and how to protect them from interference.
Day 7 Pull data from a website - This is obviously an important skill to learn if using client's data that is available online.
Day 8 Cell references - Understand the difference between relative, absolute and mixed cell references in this short article. More
Day 9 How to Print a spreadsheet in one page - Excel has a tendency to be awkward to print due to the tabular form of the interface but there are ways to ensure that it all fits onto one page and is formatted correctly.
Day 10 How do functions and formulas work? - Excel seems like a very complicated programme when you start using it as there are lots of seemingly random symbols used within formulas to calculate things. Read through Microsoft's overview of formula construction here and understand what the symbols you will be using actually mean here. Essentially, a formula is an expression which calculates the value of a cell whereas functions are predefined formulas and are already available in Excel. So you insert functions such as SUM, AVERAGE, COUNT etc to create a formula that performs calculations on certain cells (which would look for example like =SUM(A1:A2). This could be written out like =A1+A2 but the SUM function is a quicker and easier way to perform such a task in Excel).
Managing Data
Day 11 Using the 'Fill' command - There are several ways to fill data or formulas into multiple cells. This article shows you four types, from dragging the Fill handle to using a Custom Fill. More
Day 12 ROUND - It is often useful to ROUNDUP or ROUNDDOWN when making reports in order to make the numbers easier to handle. This video shows you how.
Day 13 CEILING - This is also relevant to rounding as it caps the number of digits displayed in the cell. More
Day 14 Nesting functions - This is when two or more functions are used together in one cell to make a calculation. You can use up to 64 levels of nesting in one formula so they can get quite complicated but do follow logical pathways. Read the short explanation here with the SUM and ROUND example.
Day 15 RANK - This function is used to compare numbers against each other.
Watch the short Contextures tutorial here on how to use it
And read techrepublic's more technical post on the language input here.
Day 16 VLOOKUP - This function allows you to search for information across vertical columns (that's what the 'v' stands for!) Read how to use them here. The basic construction is =VLOOKUP(what,where,inwhichrow,true/false)
Day 17 - HLOOKUP - Just like VLOOKUP, but the 'H' means 'horizontal' so is only used for rows. It is not used as much as VLOOKUP as usually tables are based on vertical columns, but it still a useful counterfoil to understand. More
Day 18 - MATCH and INDEX - As VLOOKUP and HLOOKUP are restricted by either the vertical or horizontal axes they are searching, many argue that MATCH and INDEX are better to use. This businessinsider article explains why here and Mr Excel explains how to use them too here.
Here is a quick summary of all the lookup functions.
Day 19 - Using VLOOKUP info from different workbooks
Day 20 - Advanced filtering techniques There are plenty of ways that data can be filtered so read through this more extensive list of different filters you can create (includes worked through video examples). More
Logical Formulas
The following inputs create a formula which will carry out a different calculation based on criteria that you specify.
Day 21 - IF Statements This function is used to work out statements that will come out TRUE or FALSE
Watch the ExcelIsFun video below with 7 worked through examples
Day 22 IF THEN ELSE You can add more and more conditions into IF statements in order to affect the TRUE or FALSE outcome. See how to use the words 'THEN' and 'ELSE' here
See this short video here too
Day 23 IF AND OR
A short explanation of how IF statements work with AND/OR here
Day 24 Logical formulas in practice Using a simple chart to get to grips with these functions, Excelteacher goes through these functions.
Array Formulas
An array is a group of cells that are related e.g. in adjacent columns or rows. An array formula is therefore a formula that works with a series of data values rather than just one. It returns either a single value or an array of values into a selected cell or array of cells. Read the basics in this blog post here.
Day 25 SUMPRODUCT - This function calculates the SUM across an array of cells. Follow the input steps here
Day 26 SUMIF - This is used to add up the SUM across a certain number of cells that meet specified criteria. Here's a five minute lesson on how to use this function.
A large range of examples and more complex SUM calculations including videos can be found here at Contextures.
Day 27 COUNTIF - This function calculates the number of times that a value occurs within a set range of criterion. This article explains how to use it.
Text inputs
Day 28 LEFT, RIGHT and MID - These functions cut text from the left, right or middle to simplify the data.
Techrepublic goes through an example to explain how this is useful here
And here's a Danny Rocks tutorial on how to use these three functions
Day 29 CONCATENATE - This word means 'to combine' and is therefore used in order to combine information from different cells that involve text, cell references or blank spaces (otherwise it would be a SUM or other numerical function). It can be used for up to 255 text entries.
Watch the ExcelIsHell tutorial here
And here is a printable example sheet too.
Day 30 LEN - This function tells you the character count of a piece of text inputted into a cell. It is very simple to use as LEN is just added with the cell number you want to know. More
Day 31 FIND - Similar to VLOOKUP but used to search for the position of specific text within the worksheet. Watch out, it's case sensitive! more
Day 32 Date and time arithmetic - The symbols / and - need to be used when inputting dates and Excel has the capability to add dates and times together too. Follow the Excel Easy article here on how Chandoo also has some Top 10 tips too here
Day 33 Formatting Textual Information
A good video here on formatting textual information so that Excel treats the data as numbers but places the correct information in front (e.g. dollar signs, AM/PM, percentages)
VBA and Macros
VBA is the 'language' used to create functions and formatting in Excel. So all the functions above are in this VBA 'language' but are inputted into each cell rather than into the code. If you use VBA then you have created a macro. This is a very large and complicated topic and goes all the way to configuring the programme. But work through the following links to get the basics on how to do more complicated sums and calculations.
Day 34 Create a Macro - basic intro Read this HowtoGeek intro into what macros are with plenty of screenshots to help you navigate. More
Day 35 Macros from Easy to Intermediate Go through the short videos and accompanying text here on everything to do with Macros from easy to intermediate levels.
Day 36 How the VBA InputBox works - Follow this article that uses an example VBA code and what it shows.
Day 36 Practice Macros and VBA Use Microsoft's 30-40 minute tutorial here to check everything is understood.
Day 37 Apply conditional formatting This function allows you to apply certain formats depending on the criteria that you set e.g. any number over 5 turns the cell green. This is a useful tool for you to visually analyse large sets of data quickly
Watch this video to see it in practice
Day 38 VBA Cheat Sheet Read this Dummies cheat sheet that goes through most of the VBA language that you will come into contact with. A good reference sheet for the future too.
Statistics
Excel is not built for high level statistics but can easily deal with simple statistical problems that a management consultant is likely to come across.
Day 39 Quick Descriptive Statistics This Excel function allows you to see the main statistical outcomes of a set of data at the click of a button. It automatically calculates mean, average, sum, skewness, standard deviation etc).
Day 40 Correlation and Regression Read this help sheet from page 2.
Day 41 TREND - This function calculates the trend line through a given set of y and x values. Of course this is easier to see on a graph but this function enables you to complete the maths behind the graph line.
Here is a short video on using TREND with a simple linear regression
On Trend Analysis, Wikihow has quite a detailed description of various methods you may come across here.
Day 42 STDEV - Short for 'Standard Deviation' which can be calculated from lists of data. Enter STDEV and the range of cells you want to calculate it for. Watch the simple video tutorial example here:
Day 43 VAR - Short for 'Variance' of a set of data. Simply explained by Microsoft including the statistical equation this function is solving. More
Day 44 FREQUENCY - As the name suggests, this function shows how many of a certain type of something are mentioned within a data set. Some examples given here on how it works.
Day 45 - Statistics overview Follow the Microsoft training website for their 50 minute tutorial on everything about Excel statistics. Includes some practice examples too that will ensure you understand all. More
Financial Modeling
Day 46 How to create financial scenarios in Excel - Financial Modeling is an important feature of Excel for consultants as it allows for the manipulation of the inputted and managed data to come to decisions regarding investments or profitability of a company.
Watch this 15 minute edutechional video on various types of scenarios that can be modeled within Excel
Chandoo offers a 6 part intro into how to start building financial models and project evaluations here
Day 47 DCF (Discounted Cash Flow)
This is a type of analysis used by consultants to value a project, company or asset over time. All the future incoming and outgoing cash flows are estimated and discounted to give Present Values (PVs). Follow this step by step blog post here on what to do to work out the DCF of a set of data here
And the well known Wall Street Prep offers a video tutorial here
Plus there is a free template download available here to start building your own DCF model.
Day 48 NPV (Net Present Value)
This is simply the sum of all future cash flows worked out during the DCF calculations to estimate the overall present value of a company. Your clients are sure to be wanting this to be a high number and your job may be to find solutions to get this number up!
Watch the ExcelIsHell video to see how!
Day 49 IRR (Internal Rate Return)
This is a term used in capital budgeting to work out the profitability of investments. Again, another term that is going to be very useful for consulting work. Follow this friendly voice through the IRR tutorial
Day 50 WHAT-IF analysis Allows you to try out different scenarios for formulas depending on the criteria you set. This function is therefore very important for management consultants when working out what types of mathematical solutions they can offer to the client based on the data they have researched/provided. Read the Excel Easy article on how to use it here
Day 51 Solver and Goal Seek Indeed Excel now offers tools that find optimal solutions to all kinds of decision problems! Solver can take into account constraints and variables and Goal Seek is used to find a result when you are not sure of the starting value. Find out how Solver works here and for Goal Seek check out this
Visualising Data - Reports and Charts
Day 52 How to create a graph or chart from worksheet data
As creating charts is a very visual skill, GCF Learn Free has a great slideshow like page here that goes through the different types and how to create them.
Day 53 Creating a Gantt chart This type of chart is used a lot by management consultants as it is used for project management. Tasks are shown on the vertical axis, and the project time span is represented on the horizontal axis. Each task has a corresponding bar that shows the time span required for that task. The bar can be filled in to show the percentage of the task that has been completed.
Watch this video tutorial that goes through how it works
And you can use a free Gantt template to work from here
Day 54 - Sparklines A new feature in Excel 2010 that offers graphical lines in a cell to chart the rise and fall of the values entered in the column/row
Day 55 Create a Pivot Table
They are considered an advanced Excel skill but are not very hard to create! Watch this short video with transcript below.
Now watch this excellent longer worked through example
Day 56 Power Pivot a Pivot Table This is a new function of Excel that allows larger sets of data known as 'Power Pivot' to be entered into Pivot Table analysis.
Watch Microsoft's quick explanation of how to create a Pivot Table using Power Pivot data here
Day 57 Pivot Charts These enable the user to visualise Pivot Table data. Excel Easy shows how to quickly do it here But for a more fancy interactive format, watch the following video
Now Check out this 6 page guide and download the practice workbook to try out your newly learnt Pivot Table skills!
Day 58 - Create a Summary Report Reports will be vital for your consulting career as ways of visualising and summarising the data that you have collected and researched. Here is the Dummies rundown of how to create one.
For a quick overview of Summary reports, watch this
Day 59 - Create a Key Performance Indicator (KPI) Dashboard Report Another vital reporting technique that all consultants will be using at some point. The dashboard is designed to consolidate KPIs from numerous areas of the business research to increase the visibility of business-critical information to the end-user (the client). They are also particularly useful as they can be interactive with graphs changing according to other criteria that are clicked on.
See this 9 minute video to build a simple KPI dashboard
For other types of Dashboard report, see Chandoo's templates and tutorials here.
Conclusion
Day 60 - Refresh and Consolidate! And there you are! You did it! Now you should have a pretty good understanding of most of the things you will need to know in Excel but it is important to refresh and consolidate this knowledge. Spend this final day looking back over the skills you have learnt over the last 59 days and ensure you remember them all. There will always be more to learn and your skills will improve on the job, but as a good starting point for your management consulting career this course should equip you with the necessary knowledge. For now, we dub thee an Excel Power User!
Other useful resources
Here are some more useful resources which cover more topics than were appropriate for the daily quick fire tutorials.
http://www.contextures.com/xlfaqApp.html#App04 A good FAQ list with simply explained answers including codes to enter and screen shots
http://www.washburn.edu/faculty/boncella/BU956/Excel%20Modeling/Ch3.pdf A really good pdf here of all basic Excel knowledge from navigating the windows to inputting data and creating charts
https://www.lmu.edu/AssetFactory.aspx?did=53607 The checklist for the lynda.com Excel course - useful to compare your skills to what is listed here
http://laude.cm.utexas.edu/courses/OAS/OASskillmodules/sme16f08f08stasin%20excel.pdf A printable statistics revision sheet from the University of Texas