- You will be better at your job when you get to grips with this
- Excel can be a highly rewarding pastime!
- There is a Google Sheet with all of the examples so that you can copy it and practice in your own time
Excel is arranged in columns and rows. Columns consists of vertical rows of cells; they are labelled with letters. Rows consist of horizontal columns of cells; they are labelled with numbers. A range in Excel is a selection of a number of cells that are adjacent to each other.
A cell has a unique cell reference. The top left cell is named A1. The cell to the right of it is named B1. The cell below A1 is A2. You get the idea.
You can select a random group of adjacent cells by typing the top left cell reference, adding a colon, then typing the bottom right cell reference.
A1:B2 would therefore select 4 cells in the top left corner of the spreadsheet.
You can select whole columns by typing:
One column: =(A:A)
Two columns: =(A:B)
26 columns: =(A:Z)
And whole rows:
One row: =(1:1)
Two rows: =(1:2)
150 rows: =(1:150)
It’s good to get a handle on typing these out as when you start working with formula, you want to be able to understand where the formula is being applied to.
There are some very simple formula here, which will enable you to ditch your calculator. For ranges I’ll use A1:A4, but you can change this range to anything.
Operators within formulas:
To the power of ‘n’ =A1^n
Square root =sqrt(A1)
Sum of a range =sum(A1:A4)
Mean of a range =average(A1:A4)
Mode of a range =mode(A1:A4)
Median of a range =median(A1:A4)
Finding data within data, using data
Here is where the king of Excel formulas lie. The mighty VLOOKUP. Master this and you’re halfway up the road to Excel glory.
The VLOOKUP lets you find a value in a long list of jumbled values and is very helpful when dealing with big data sets. Imagine you had a huge list of customer ID numbers in column A and also email addresses in column B. You’re then looking to find 100 email addresses from a separate list of customer IDs. VLOOKUP is now your best friend for this task.
To do this we need to examine what VLOOKUP actually does. It looks for a value you specify within a range (across multiple columns) you also specify. It always looks for the value in the first column of the range. As it looks down the first column, it will match the first value it finds. Once this happens it then looks at the index value you have specified. This tells the formula how many columns across to the right it should look at. It then selects the value in that column. Each part of the formula is split by commas.
Here is how its syntax is laid out:
=VLOOKUP([value], [range], [index],)
Using some example data it would look like this:
Match type can be confusing but usually you will always set it to 0, which indicates an exact value match. Setting it to 1 can give you some very unexpected results.
HLOOKUP works in the same way as VLOOKUP but instead of searching for a value down a column, it does it across a row. You then specify how many rows down you want it to go, using the index syntax, to pluck the resulting value out.
In summary, VLOOKUP goes down then across to the right. HLOOKUP goes across to the right then down.
This section covers COUNTIFS, SUMIFS, AVERAGEIFS.
So what if you have a load of data in a table, but you only want to do maths to the parts that correspond to certain attributes of the rows? You use one of the above. This example is way easier to see in Google Sheets, so take a look. When this works, you’ll be grinning with delight.
The syntax is below. COUNTIFS is slightly more simple than the others as it contains no range to do maths on (as it just counts your conditions). All of these formula talk about criteria ranges and criteria. In these formulas criteria ranges act as the ranges where your conditions lie, and your criteria is normally a cell that acts as your condition. In these formulae you can have one condition or indeed multiple conditions. If just one, then you shouldn’t fill in any of the extra conditions in. They’re really useful as an alternative to using a Pivot Table (although, fun fact, Pivot Tables are much less RAM & CPU intensive so if working with massive data sets use Pivot Tables).
1 condition SUMIFS example:
2 condition SUMIFS example:
Quite often we get really shoddy data sets to work with. This might be because people have typed into forms in annoying ways (i.e. not capitalising their name), or you’ve scraped a load of data from a website and some anomalies exist. Well, often you’re going to want to remove these and make your data nice and uniform. Here are some tricks…
Capitalise everything =UPPER(A1)
Lowercase everything =LOWER(A1)
Title case everything =PROPER(A1)
Replace ‘+44’ with ‘0’ =SUBSTITUTE(A1,+44,0)
Select the first 5 characters =LEFT(A1,5)
Select the last 3 characters =RIGHT(A1,3)
Find the position of @ in a string, starting at character 2 =FIND(A1,”@”,2)
Select the middle 5 characters starting at pos 2 in a string =MID(A1,2,5)
PRO TIP: You can combine the two formulae above to strip out domains of email addresses, for example:
A1 = firstname.lastname@example.org
B1 = forwardpartners.com
Intro to logical expressions
You can use a myriad of logical expressions in Excel, but I find the IF function the most useful. Basically it can be used to perform a function if a logical argument is satisfied (or not). Again this is one that’s probably going to be more useful to see in the Google Sheet, but here goes:
The [logical_expression] is usually a mini equation, but can take many forms. Your imagination is the only limiting factor here:
A1>B1 (A1 is greater than B1)
A1<=B1 (A1 is less than or equal to B1)
A1=”Thomas” (A1 equals the text ‘Thomas’)
[value_if_true] is the answer you want to get when the logical expression is indeed true, this can be a formula too.
[value_if_false] is the answer you want to get when the logical expression is not true, this can be a formula too.
When you get good at these you can start to nest IF equations within IF equations to give you a multi-answer decision-tree style formula.
Did you know that you can concatenate cells by just using an ampersand?
A1 = Tom
A2 = MacTom
Also that when using numbers, especially UK phone numbers, in cells the leading 0 is removed as Excel thinks it’s an irrelevant number. You can get around this by adding an apostrophe before the number. Same deal for numbers beginning with an international dialling code, such as +44. In this scenario Excel things you are trying to do maths (addition). Again, get it to ignore this by adding a leading apostrophe.
If you are dragging or copying a formula across or down a table, then the cell references will automatically shift relative to your dragging/copying. This is super handy most of the time, however if you need to lock a cell (i.e. it’s a fixed value and doesn’t change) you can use a dollar sign:
A$1 would lock just the row reference, i.e. it could still move horizontally across a row.
$A1 would lock just the column reference, i.e. it could still move vertically across a column.
$A$1 would lock the cell in both axes, this guy isn’t moving.
There you have it, a little primer on highly useful excel formula. Oh boy, there is so much more to learn but these basics will get you a very long way. They’ll also enable your brain to sync with the way excel works so that any new formulae you come across will make immediate sense.
Acknowledgements to The Path Forward for the primer