A month ago, I challenged myself to learn data analysis, so I went shopping across the internet for a good place to start and the consensus was unanimous; check out good old Excel.
So what exactly is Excel?
Far from just being a digital ledger, excel is a powerful data engine. It is used in turning observations into trends in Field Research, helps businesses track sales and predict growth while in finance it is used to ensure every cent is accounted for.
Learning Excel felt very intimidating at first, the aggregate and lookup functions were all too new to me! Once I stopped being anxious however, I have been enjoying the journey of discovering this ability excel has! Here are a few things I have learned and use often;
COUNTIF () FUNCTION
This simply counts how many times something appears in your list. In its simplest form, it asks:
=COUNTIF(Where do you want to look?, What do you want to look for?)
IF() FUNCTION
This operator allows excel to ‘think’. It checks if a condition is true, and gives you one result if it is, and another if it is not.
Syntax =IF(logical_test, value_if_true, value_if_false)
You can have as many conditions as you need, all you have to do is properly nest them!
=IF(X3<2, "Poor Score",IF(X3<3,"Average Score",IF(X3<4,"Good Score","Excellent Score")))
Lookup Functions
In the real world, all the data will not necessarily be in the same place always. The sales and customer names may be in different sheets, and the lookup functions are the connectors!
VLOOKUP
It searches down the first column, to find a value and pulls information from a different column on the right.
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num,[range_lookup])
Translation:=VLOOKUP(What are you looking for?, Where is the whole table?, Which column number has the answer?, FALSE)
The FALSE at the end ‘tells’ excel that you want a perfect match
=VLOOKUP(10010, A2:F13, 5, FALSE)
(This simlpy says; Find ID 10010 in this table, and give me the value from the 5th column. The result will be 82107)
Because it relies on a fixed column number, adding a new column to your table can break its logic. If you insert a new column before Column 5, it becomes Column 6, but VLOOKUP will still look at Column 5! Since the 5th column is no longer where the salary ‘lives’, it might return the wrong data, without giving you any error message.
XLOOKUP
A relatively newer version that beats these limitations is the XLOOKUP, and it does not require one to count the columns! XLOOKUP works, regardless whether other columns are added or deleted.
Syntax: =XLOOKUP(Lookup_Value, Lookup_Array, Return_Array)
=XLOOKUP("P-102", A:A, C:C)
(This simply says "Find P-102 in Column A, and give me the corresponding value from Column C."
XLOOKUP only works on Office 365, Excel 2021, or later.
(INDEX + MATCH)
What if you’re working on an older model and still need to bypass VLOOKUP's limitations? You use INDEX and MATCH.
MATCH finds the position of a value, and INDEX grabs the value from that position.
Syntax:=INDEX(Return_Column, MATCH(Lookup_Value, Lookup_Column, 0))
(Note: We use 0 for an exact match every time!)
=INDEX(G2:G13, MATCH(10010, A1:A13, 0))
Translation: Find ID 10010 in Column A, then give me the value from the matching row in Column G.
This combo is powerful because unlike VLOOKUP, it can look to the left or right without breaking!
PIVOT TABLES
Before embarking on excel, these used to look like Martian tech to me! Now, I realize they are effective summary machines! In a few clicks, you have a clean summary of your data. They take data and tell me the averages, totals and counts in less than a minute!
- You highlight all your data.
- Click Insert
- Select Pivot Table.
- Drag a field to Rows.
- Drag a different field to Values.
And just like that, you have every sum, average, count that you need.
REFLECTION
It’s only been two weeks, but my relationship with data has shifted. There is a massive sense of empowerment in looking at a messy sheet and having a good idea how to fix it, realizing i have the tools to make my data talk to me!
Since I’m still very much in the learning phase and have only just scratched the surface, I’m curious:
What was that one surprisingly simple Excel trick that completely broke your brain when you first saw it?
If you're a seasoned pro, what is a golden tip you wish you knew when you were just starting out in excel?
Please drop your tips, shortcuts, or aha! moments, I’m all ears and ready to learn!
SOCIAL SHARE CARD GENERATOR