For the uninitiated, learning some of the “advanced basic” features of Microsoft Excel can have a dramatic impact on their life. It can help save countless hours of manual work, and can even automate certain aspects of it. Depending on the industry and office makeup, it can even help establish you as the office’s computer guru!

Excel is very powerful, and there are very few things that it cannot do. Working in investment banking, I have to use Excel daily, and I am surprised by how little of Excel new graduates know. I am not talking about VBA either; I’m talking about good old in-built formulas. Worry not, however, as it is straightforward to catch up quickly.

In this blog post, we are going to cover the top 10 things I wish everybody would know about Excel!

How to Use Excel Formulas

The very first thing you need to know is that Excel expects all formulas to start with the equal sign. Click in any empty cell and type ”=” and you will notice some of the inbuilt functions ready to be used.

By the time you are done reading this blog, you will feel comfortable trying them all out!

Concatenating cells together with ”&”

The first thing we are going to learn about today is the ”&” sign. It is used to concatenate cells together. Why would we ever want to do that you ask? The most common usage I have found is to create unique data keys. If a data set has more than one field in its primary key, you might want to have a single attribute that represents it, to make joins and lookups easier between data sets.

Let’s see it in practice:

Looking up Data with VLOOKUP

Sooner or later, the need will certainly arise where you need to retrieve information from one set of data, based on a value from another. Take, for example, the situation where you want to be able to return a person’s height based on their name, assuming you hold all relevant information.

The simplest way to achieve that is to use VLOOKUP. We first need to choose the value we would like to use in the lookup, then the range we’re interested in (keeping in mind the look will happen in the first column), then the column we would like to return data from, and then whether we want an exact or approximate match.

Here is an example where you can clearly see what is happening:

You may have noticed that VLOOKUP only supports vertical lookups. If you are ever after a horizontal lookup, you can use HLOOKUP.

Conditional Summation: SUMIF

Another inbuilt function that may come in handy more often than you may think is one that allows you to conditionally sum values. That is, based on certain criteria, we can specify whether we would like to include the values into the total.

This is easiest understood through an example:

Arithmetic Sequences with SUBTOTAL

SUBTOTAL is a nifty little function that enables you to perform arithmetic operations to a number of fields. Average, count, max, min, sum, stdev and more are functions that SUBTOTAL can support.

The other secret power of this function is that it only considers visible values. In other words, when you filter your result set (ctrl + shift + L, and filter), SUBTOTALS will only consider the values that are visible within the defined range.

#excel #data-analysis #data-science #developer