Excel Emergency Kit
What would you like to do?
Excel Emergency Kit Working with SheetsSave time "Dragging" content with the "fill handle" (the plus)Using dollar signs! Use a functionKey functionsWorking with ChartsChange axesChanging dates
Working with Sheets
Save time
"Dragging" content with the "fill handle" (the plus)
Excel calls this bold plus sign the "fill handle." It will let you copy data or fill cells with a formula across rows or across columns.
If you have a function with a cell reference, it will increment that reference using its best guess unless you have a $ to hold it
Using dollar signs!
You can insert dollar signs in any cell reference to "hold it" when you're dragging it or copying and pasting it. This is very useful! Microsoft offers a more detailed explanation.
The terminology around this is "absolute," "mixed," and "relative" cell references:
| absolute | $B$4 | Will hold always |
| relative | B4 | Will increment columns horizontally, rows vertically |
| mixed | $B4 | Will hold column always, increment row horizontally |
| mixed | B$4 | Will increment columns horizontally, hold row always |
Even better, you can toggle through them with your keyboard like a God. 🤯🤯🤯
Windows: F4
Mac: ⌘-T
Use a function
To tell Excel we want a funtion, we start by entering = in our cell. Once we add a letter, it starts a search, and Excel will provide a list of recently used functions as well as functions with names close to what you're typing. This isn't the worst way to explore things. If I know I want to find something about normal distributions, I can type =no, and Excel will give me a list of what it thinks I might want:
Not too bad!
Once you select a function, Excel will give you hints on how to enter it. You can always check out the help files for more detailed explanations
An example
For this averageif function, Excel wants me to first enter a range over which the if statement will check. Then, I should enter a comma, then the criteria (or conditions) that must be true for the average to work.
The part in brackets, [average_range] is the range to actually average over. This is in brackets becuase it is optional. If we dont' specify anything, it will use range as average_range. That is, if we are doign something like averaging over all cells between A4:B52, then we don't need to write it twice. TLDR; brackets indicate that something is optional.
Key functions
| Function | What it does | Sample syntax | Explanation |
|---|---|---|---|
| COUNT | Count the numbers in the range | =COUNT(A2:A7) | Counts the numbers in the 5 cells from A2 to A7 |
| COUNTIF | Count cells in which a critera is met | =COUNTIF(A2:A7,"<>0") | Counts cells that are not equal to the number zero in the 5 cells from A2 to A7. Note that this includes blanks! |
| =COUNTIF(A2:A7,">0") | Counts cells that are greater to zero. Note that this excludes blanks | ||
| AVERAGE | Average over the numbers in the range | =AVERAGE(A2:A7) | Averages the numbers in the 5 cells from A2 to A7 |
| AVERAGEIF | Average over range if criteria is met | =AVERAGEIF(A2:A7,">0") | Averages the numbers in A2:A7 that are greater than 0 |
| =AVERAGEIF(A2:A7,">0"B2:B7) | Averages numbers in A2:A7, but only if the value in the same row of column B is greater than 0 | ||
| AVERAGEIFS | Average over range, multiple criteria | =AVERAGEIFS(B2:B7,">0",A2:A7) | Averages numbers in A2:A7, but only if the value in the same row of column B is greater than 0 (same as above - note that the order of the syntax is flipped!) |
| =AVERAGEIFS(B2:B7,">0",A2:A7,) | * | ||
Working with Charts
Change axes
So first, you need to know how to change anything about an axis. Excel will help you with this video tutorial.
Changing dates
Dates are tricky. Excel will pick a starting point and ignore all reason. You can (and should) fix this. Again, Microsoft has a useful video tutorial/walkthrough.
Here's the default from an unemployment graph. Why would anyone want that x-axis?
Head to the format axis pane by clicking on the offending axis then right clicking "Format axis..." or clicking on the "Format pane."
17 months? No one wants that. Fix it. Make it something human.
That said, you might still get something like "Jan-48, Jan-49, etc." You'll want to customize the label. You can do this by clicking on the "Number" dropdown. Either find the label you like. Or, find one that's close enough and edit it in the "Format Code" box and click "add"
Pro tip: If you want to go deep, you can find the proper codes for formatting just about any number any way you like! For example yyyy will give you just (1948, 1949...). This is helpful for non-charting applications as well.
