Excel for Management Consultants: 4 Must Knows

Will B

Consultants spend a lot of time on Excel.  But what exactly are they doing?

They are arranging and analysing a company’s data.  Often this is disorderly, poorly formatted and littered with formulaic errors.  The firm probably never thought lots of this info would be important.  Consultants often find that it was no-one’s job to manage all these numbers.

The job of the consultant is therefore to make sense of this and convert it into a diagnosis of the problem and a strategy to solve it.

We have rounded up a few of our favourite tools for using Microsoft Excel.  And my goodness will you be using Excel.

Filter

Filter is pretty much what it sounds like.  You can use this to order any number of values in a column or row.  You can do this along almost any vertical you fancy – a letter, =, < etc.  It hides the data that doesn’t fit the function’s terms.  Consultants use this to piece their data together in a way that is easy to skim over.  Be careful!  People often forget that a filter function is affecting a column/row and go full steam ahead – some of their content will be missing!

Pivot Table

This makes you look awesome.  It pulls your table into ordered groups.  A pivot table is a data summarization tool that is used in the context of data processing.  Often it is used to pull a massive table into smaller ones. Pivot tables are used to summarize, sort, reorganize, group, count, total or average data stored in a database.  Consultant’s Mind provides a good example.  He used a pivot table to show how many Covid-19 there were as of 07/03/20 – 105k.

VLOOKUP

This is the Greatest of All Time.  The GOAT.   It quite literally “looks up” anything you fancy.  Within Reason.  When a client hands over a HUGE chunk of data and you can’t find the important bits, this is important.  It works by telling Excel to find X – e.g. ‘Blue’.  it can then grab the data one space to the right or left, for example.  You can use this to move data between sheets, columns, tables etc.  This obviously assumes that the data is laid out in a uniform way across the spreadsheet.  The formula for this is quite a complex one so for more details on its practical use, have a look here.

Check out Heinrich’s alternative take on VLOOKUP

Absolute Reference

his is quite a niche one.   This works by adding $ into a formula. Usually in Excel when you ‘copy down’ – when you click a cell and scroll down to extend the value set – the numbers change.  If you add $ around a a value i.e. $C4$, it will stay as C4 when you scroll down, rather than becoming C5 etc.   In Excel, you often copy down at a rate of knots, and this can be super useful.  For example, if the formula is finding a mean, the n term would be in a single box, so you would want to use $.  

Sort

Sort is a similar idea to filter, except you are usually sort High to Low, or A to Z etc.  You can also still see all the values in the range, they are just reordered.  Filter obscures the non-target values.  Sort is often less for ease of analysis than for presentational purposes.  Beware.  If you use this tool when there is a break or misalignment in your data, and then crack on with your spreadsheet, this could become an irreversible disaster.  

For those interested in a bit of practice, we have a Home Excel package on offer here.

If you enjoyed our content, subscribe here!

All content and information on this website and/or newsletter including our programs, products, and/or services is for informational and educational purposes only, does not constitute professional advice of any kind, and does not establish any kind of professional-client relationship by your use of this website and/or newsletter. A professional-client relationship with you is only formed after we have expressly entered into a written agreement with you that you have signed including our fee structure and other terms to work with you in a specific matter. Although we strive to provide accurate general information, the information presented here is not a substitute for any kind of professional advice, and you should not rely solely on this information. Always consult a professional in the area for your particular needs and circumstances prior to making any professional, legal, financial, or tax-related decisions.
© 2021 TheCambridgeConsultant.com.
Privacy Policy | Terms & Conditions | Disclaimer