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, =,
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.
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.
