For many readers, your are excel professionals. You get happy – almost excited – to create tricked-out excel models to impress your boss and clients. The fact that there are 500+ formulas,100+ functions, and 200+ shortcuts, is kinda cool. Yes, you are a nested-formula nerd. Please skip this post.
For others, excel can be a bit intimidating. You know the basics = sum(a1:c3). Frankly though, you’re no expert. You export results from SAP, Oracle, McKesson, EPIC, Cerner, or Salesforce. With reports, you’re afraid to mess with it too much, in case you “break” it. For those new-ish to excel:
Start with these five: Pivot, VLookUp, Filter, Sort, $
In my mind, there are 5+ core things you need to know in excel. This gets you conversant in the language of excel, not fluent, but enough to get around. Here are the big 5: Sort, Pivot, Filter, VLookup, $. If those are not familiar, then this is item #1 for you. ExcelJet is a great resource, click on the links below.
If someone asks you to self-evaluate your excel proficiency in an interview (yes, that is what I do), then you better not say anything over a 3/10 without knowing these five. However, the upside, the majority of excel work is pretty straight forward. . . organizing of rows of data (filter, sort, text to number), and doing simple math. For example, over the last month, these five helped cover 80% of what I did.
For the purposes of demonstration, using this John Hopkins data set of confirmed Covid-19 cases (as of 03/07/20). Feel free to download here and play along here. Green button on the right – download.
1. Pivot Tables
This is the easiest way to look fancy. This is a tool to group your data, and turn rows into columns and vice versa. I know that sounds like mumbo-jumbo, so take a look.
How many cases of Covid-19 are there, as of 03/07/20? 105K. How do I know that? a few ways:
1) Use the SUM formula for all the numbers in column AX.
2) Another way – more pro – is to put your cursor on the top data point (Anhui in AX2, and hold down <CTRL> button + <SHIFT> button + then the down arrow button. You get this. . .and the count, sum, average is show at the bottom.
3) Okay, getting to PIVOT tables, I promise. What if I wanted to know cases from Australia by source. . . this is not as simple as a SUM. This is the best part of Pivot tables, it allows you to quickly bucket the data.
2. VLookup
This is also a form of magic. Basically, you have 2 different sheets of data, and you “Lookup” one value and bring it over to the other sheet. “Rover, Red Rover . . .” I mocked this up below:
If you wanted to bring the population of each city from Sheet #1 to Sheet #2. . .it is essentially telling the compute to look up “City A” in the Sheet #1 (blue box) and bring over the value that is 2 spaces to the left.
If you only wanted to find 1 city. . .no need to do this, just look on sheet #1 and <CTRL> + F to find it. . but what is you have 35,000+ records? The VLookup will save you. Great video explanation here.
This is exactly what it sounds like. You can have a column of 67,000 items. . and you can filter them for numbers > or < or = to a value. You can just type in the letters you are looking for. . . If I wanted to find all the cities in Florida, I might type in “FL” which narrows down the list considerably. More on filtering here.
4. Sort
Sort is the same idea, except you are including all sorting High to Low, or A to Z etc. . . Please be careful, if there is a break in your data, it will only SORT the section of data before the break. We have all been RUINED because of this. . . make sure you data is continuous (fancy way of saying there are no gaps)
5. $ (Absolute reference)
This also a good way to discern if the person you are talking to is an excel-head or not. If they don’t know what the $ does in a formula, they have never really put together an excel model by themselves.
One of the beautiful things about excel is that you can “copy down” and repeat the formula for thousands of rows in 1 second. Great. The bad thing is that it will “copy” the exact formula, but the reference cells go down at the same time. Yes, that sounds like mumbo-jumbo. . .
In the example below, we are trying to figure out what % of total Covid-19 cases are from China, and Italy. For China, take their number and divide it by the total (blue /red). You can “copy” the exact formula down the column EXCEPT, you want to make sure you are dividing Italy by the same 105,840 total. So you put a $ in front of the column H and in front of the row 82. This anchors the formula, so that although you copy the formula down, it always divides by the 105,830 (red). Learning more on the absolute reference here.
Using F4. . . it adds $ automatically for you. Yes, saves you time. 54 more excel shortcuts here.
Note: Thanks RR, for feedback on typo . .absolute reference, not value ABS().