Bad-ass Excel formulas that will help you save the world

Harry Potter potions room

I once tried to chat up a guy in a bar using my favourite Excel formula. He shockingly didn’t take the bait. I wasn’t too cut up about it, I figured if he were the guy for me his eyes would have lit up the moment I said VLOOKUP. He would be in my bed right now asking me to show him my SUMIF.

Sexy talk aside, if you can master the below formulas people will think you are a wizard. I am 99% sure that Voldemort could have been taken down with a good PIVOT table and if you learn the formulas below you too might be able to take down a fascist overlord one day. And if it turns out that magic doesn’t exist then they might actually help you with your job. Boring. Bring on the Harry Potter references.

With all this Excel knowledge I genuinely have no idea why I’m still single.


Ok so imagine that you have a list of Death Eaters and how many muggles each of them killed. You are in the middle of a court case at the Ministry of Magic where 4 of the Death Eaters are being tried and you need to quickly know the muggle count for each. What do you do?

Death Eaters

Use the magic of the VLOOKUP of course! Here is our list of Death Eaters and kills on the left (imagine that there are thousands on the list because I can’t be arsed googling more than ten). Bellatrix is first up in the court and what I am going to do is write the following formula into cell F2 of my court list.


1: Click the cell that has the thing you want to look for written in it, in this case I have clicked E2 because that is where Bellatrix’s name is.

2: VLOOKUP’s think in terms of look for the information in a table, so we need to tell excel where this table is. For this example my table is columns A and B, because that is my list of Death Eaters. By writing A:B I have selected the whole damn thing. If your list also included where they lived, their favourite Unforgivable spell and their wand type you would just select more columns.

3: This is where you tell the formula what information you want it to bring back, if it finds Bellatrix I want it to bring back how many kills she had. In the table we selected above it has two columns (A is 1, B is 2), I am going to put 2 because I want the muggle number to come back.

4: Always write FALSE here. Don’t try to understand why, if you don’t the formula will fuck up and Bellatrix will literally get away with murder.

Top VLOOKUP tip: Always make sure the thing you are looking for is in the left hand column of the table.

&” “&

Snape has sent you a spreadsheet of the Gryffindor Quidditch team. But because he is a lovesick resentful dickhead he has given it to you in a really unhelpful format. The names are in column A, and the positions are in column B; what you want is the name and position in the same cell (god knows why, just humour me).

Quiddich team

If I used the formula =A2&B2 it would give me the name and position like this Oliver WoodKeeper with no space between the words; that is why I added the “ “ between them. That tells excel to put the name, then a space, then the position. Fuck you Snape, your tricks don’t work around here.


You copied your Transfiguration homework from Hermione and it wasn’t until just before class that you realised she passive aggressively wrote an extra bit at the start of each spell so that you would be marked wrong. Never fear, Excel is here. The MID formula lets you take the middle part out of a string of text.



1: the click the cell that the text is currently in

2: Write the number of the letter you want it to start with, in this example I want all the spells to start on the 2nd letter, so I am going to write the number 2

3: What letter do you want to end with? Because all my spells are different lengths I have just put 100 so that it captures all of the rest of the spell. BOOM cheating achieved.


Well it is the end of the school year and the entirely random allocation of house points has been done (Longbottom getting ten for standing up to his friends just reeks of a rigged system created by a crazed despot). But because wizards clearly have no understanding of structure it is time to add up the meaningless points.

house points


1: This is the column where Excel is going to look for your citeria, in this case it is column A because that is where all the houses were listed

2: This is where I tell excel what I want it to look for, in this case I have clicked on the cell that has the House name in it in my final points table, I could also have typed “Ravenclaw”.

3: Select the column that has all the fucking pointless points in it (aka, column B)

NB: To select an entire column you can click on the damn letter on the top of the column you want, like click on the actual fucking letter at the top.


There you have it. I’m drunk and I think I may have just insulted both Harry Potter fans and Excel experts. But fuck it, if I see Voldemort I am going just stand there screaming I WANT TO PIVOT YOU until he gets concerned and offers me a cup of tea.

Is there a support group I can join?

Yes, I am a Ravenclaw how did you guess?

Author: runawaykiwi

7 thoughts on “Bad-ass Excel formulas that will help you save the world

  1. You do know about INDEX(MATCH) instead of VLOOKUP, don’t you?
    Much more efficient, since it’s only calculated once
    I’ve had spreadsheets crash under the weight of too big a VLOOKUP

    1. Oh god I hate IndexMatch! In the excel world you are either on team indexmatch or team vlookup…nothing in between.

    2. Hang on there’s another way that doesn’t involve Vlookups – mind blown!

      Also RunAwayKiwi – BEST POST EVER!!

  2. I’m a huge vlookup groupie! G has woken me up various times over the years telling me I was getting very excited about a vlookup in my sleep while tapping away at his body assuming he was my keyboard – I’m often pissed as it means he’s woken me up from an extra kinky excel dream (I kind you not…ask G and he will tell you that I talk about excel formulas in my sleep!).
    Learnt how to slice today…very exciting day in the office.
    I do love running a macro now and then I must say as well…all that power with just pushing one little button!
    Fabulous post!

Comments are closed.