Regardless of what type of organisation you work for and what type of role you have, the ability to use Microsoft Excel well makes a huge difference to both the ease and proficiency with which you are able to perform. I was recently amused to see that even the security guards were using Excel in the ID card office – pretty sure that it was not part of their job description when they signed up!
It is easy to spot someone who is an Excel master. For one, he or she never uses the mouse – it is all about keystrokes – navigating within a worksheet, tabbing between work sheets and work-books all through key strokes that can be keyed in very fast. Another clear differentiator is the logic that masters use. From stating overall objectives of the analysis, to laying out contents, to structuring key inputs and noting key assumptions to an efficient calculation engine and finally an easy to understand set of outputs. This kind of mastery comes with months if not years of daily interaction that, admittedly, may not be the norm in many jobs.
However, the 80-20 rule applies in spreadsheet mastery as in all other things too. In addition to the basic functionality of spreadsheets that allows you to build tables of data based on simple arithmetic functions such as add, subtract, multiply, divide – there are three functions in Excel that I would argue are the most widely used and the most useful. They are the IF function, the VLOOKUP function and PIVOT TABLES. Here is how each of them works:
IF – Allows you to define one of two results depending on the conditions of the cells to which the formula is linked. Suppose you are looking at a set of data for a superstore that offers customers a 10 percent discount on order sizes over $100 of goods. Also suppose that you have a data set of customer data and their order sizes organised in columns and you need to calculate how much to charge each customer. You could use an IF function which might look like the following (where ‘ORDER SIZE’ represents the relative cell reference that contains the data for the relevant customer): IF(‘ORDER SIZE'<100,’ORDER SIZE’,’ORDER SIZE’*0.9). This formula automatically detects those orders worth $100 or greater and produce the chargeable value after applying a 10% discount to the order size. For order sizes below $100 it would produce the same value as the order size. Pretty simple, but allows you to put in conditions on calculations very easily. If you want more than a binary condition, you can “nest” IF functions so that you create a tree structure of conditions.
VLOOKUP – Searches for a cell entry in an array of data by relating the cell entry positionally to a ‘field marker’. This can be very useful if you are consolidating data from multiple sources. Suppose that you are a company that sells multiple products to its customers and you want to understand the product mix of your customer base. You might find that each product has its own set of data in different spreadsheets and you need to consolidate it into one spreadsheet in order to analyse product mix. The way you might approach this problem is to create a new spreadsheet with a full list of customers (the field markers) in one column and label additional columns to capture volume data (how much customers have spent on a particular product) by product. You could then set up a VLOOKUP formula for each product that searches through source data for each particular customer and returns the value of the adjacent entry for product volumes. Extrapolating these VLOOKUP formulae across all the customers (using ‘drag and drop’ for example) will provide you with the dataset you need to perform analysis on product mix. One note, syntax is important in VLOOKUP – in this example it is likely that not every source data set will spell their customer names identically, you will likely have to ‘clean the data’ and ensure consistent syntax in all source data first before being able to use VLOOKUP properly.
PIVOT TABLES – Imagine that you have a massive array of customer data for a supermarket store including the personal details of tens of thousands of customers (nationality, address, age, occupation) and that for each customer you have 10 different product categories ranging from fruit and vegetables to domestic cleaning products and details of each customer’s total spend by product category over the last year. If you had to produce analysis that shows the split of total spend by category by customer occupation, how would you do it? There are actually a few ways to approach the problem, but by far the simplest way is to produce a pivot table. It will take you 30 seconds to set up the pivot table and the result is almost instantaneous. What is even cooler about a pivot table is that once you have set up one pivot table on an array of data you can cut the data any way you want within the same table. You can also breakdown each axis to various levels of granularity and in various orders. For example, you might have on one axis all customers grouped by nationality and then within each nationality, their occupation and within their occupation, their age alternatively you could have age first then nationality and then occupation. You can also use a different operator for the table output such as count, sum, average etc.
Excel is a very powerful tool and these functions are just three of hundreds of functions available but if you spend any time using spreadsheets you will find yourself using these three over and over again and more than any other. Understand when to use them, know how to use them and produce detailed analytical results with relatively little effort.
Hugh Karseras is author of From New Recruit to High Flyer, published by Kogan Page. He is a graduate of Princeton University and Harvard Business School, a former McKinsey consultant and is currently a director in a leading Investment Bank.
Click to Add the First »