Data Analysis with Excel

There are a ton of data analysis programs used today, with the demand for Excel skills dwindling over time. But personally, I believe we all need to go back to the basics still, just imagine a world where nobody knows how to use excel anymore — a growing realisation I have that my generation knows nothing about Excel and its capabilities. And having used Pandas for data visualisation before, learning Excel was a breeze and definitely a lot easier in terms of customising charts and graphs.

I thought that I would share some useful functions I have learnt from the course I took on Coursera offered by Rice University in the form of a cheat sheet — and perhaps to serve as a reminder for myself if I ever have to use Excel in the future.

Functions

Shortcuts

1. Ctrl + Up/Down - jump to the first or last row

2. Ctrl + Shift + Down - select entire column after selecting the first cell in the column

Important formulas

1. =SUM(range of cells to calculate sum)

2. =SUMIF(column to check for condition, condition, cells to calculate sum) — ie =SUM(col1, “Word”, col2) checks for “Word” in col1 and sums the respective values in col2

3. =MAX(column), =MIN(column), =AVERAGE(column)

4. =COUNT(column) — checks for number of instances

5. =COUNTIF(column, criteria) — ie =COUNTIF(col, >300) counts how many instances in col is >300

6. =IF(condition, value_true, value_false) — ie =IF(cell = 50, ‘High’, ‘Low), if the cell is 46, it will return ‘Low’

7. =VLOOKUP(cell, table range, which column in table to access, TRUE/FALSE) - FALSE is to find exact match, TRUE rounds off and finds the lower value in the table; table you are referencing is vertical; fix table range

8. =HLOOKUP() — same parameters as VLOOKUP; table you are referencing is horizontal; fix table range

9. Fix range of cells $A$1 — place a ‘$’ in front of the column/row which you want to fix

Others

1. Data filtering — select header row > Data > Filter

2. Pivot table — select table data > Table > Pivot Table

3. Plotting histogram — create a bins table > Data > Data Analysis > Histogram > input data range, bin range, check chart output and select output range as an empty cell

I have to apologise I’m not sure how to explain making a pivot table/pivot chart in words, but I highly recommend this particular course on Coursera. It is very comprehensive and concise.