For this post, I’m using sample the file Excel4EngMajors_HarryPotter.xlsx on “The House Cup” tab. If you want to follow along, you can download the file here or on the Resources page.
I love Excel formulas. There, I said it.
In my opinion, the biggest benefit of using Excel is the ability to use formulas to do incredible things with your data. You don’t need to use any fancy functions or keyboard shortcuts to be really great at Excel (though you can learn a couple good ones that will make your life easier) – as long as you have a well-formatted spreadsheet that utilizes some key formulas, you will look like an Excel wizard.
This week, I’m going to show you 20 of my favorite Excel shortcuts that will be sure to impress your boss, your coworkers, your mom, and anyone who happens to wander by your computer while you are doing Excel wizardry.
Formulas that make math a snap.
| Description | Formula |
| Adds up all selected cells | =SUM( [range] ) |
| Averages all selected cells | =AVERAGE( [range] ) |
Formulas that count it all up.
| Description | Formula |
| Counts how many cells contain numbers | =COUNT( [range] ) |
| Counts how many cells are not blank (including cells containing numbers and cells containing letters) | =COUNTA( [range] ) |
Formulas that only work if you want them to.
| Description | Formula |
| Checks to see if a cell meets criteria; if yes, does one thing; if no, does a different thing. | =IF( [logical test], [value if true], [value if false] ) |
| Sums only cells that meet a certain criteria. | =SUMIF( [range], [criteria], [range to sum] ) |
| Averages only cells that meet a certain criteria. | =AVERAGEIF( [range], [criteria], [range to average] ) |
| Counts only cells that meet a certain criteria. | =COUNTIF( [range], [criteria] ) |
Formulas that keep your characters in line.
| Description | Formula |
| Takes only the characters the designated amount when moving from the left | =LEFT( [cell], [number of characters] ) |
| Takes only the characters the designated amount when moving from the right | =RIGHT( [cell], [number of characters] ) |
| Takes only the characters from the designated character moving from the left | =MID( [cell], [character start number], [number of characters] ) |
| Removes any extra spaces before or after a set of text | =TRIM( [cell] ) |
| Counts the number of characters in a cell | =LEN( [cell] ) |
Formulas that help with text.
| Description | Formula |
| Puts all text characters in uppercase | =UPPER( [cell] ) |
| Puts the first text character of each word in uppercase and all subsequent text characters in lowercase | =PROPER( [cell] ) |
| Puts all text characters in lowercase | =LOWER( [cell] ) |
Formulas that are just cool.
| Description | Formula |
| Combines the text in designated cells and/or any specified cells in a single cell | =CONCATENATE( [cell], “text”, etc. ) |
| Compares two cells together to see if they match | =EXACT( [cell 1], [cell 2] ) |
| Checks a cell to see if it’s blank | =ISBLANK( [cell] ) |
| Inserts the current date | =TODAY() |
Which formula was your favorite? Did we miss any good ones? Let us know in the comments below!
Excel for English Majors 
Great summary! – I personally like INDEX MATCH combination because they make simple worksheets become dynamic!
LikeLiked by 1 person
Thank you so much for the feedback! I’ve actually never used INDEX MATCH, though I know it is a powerful formula – I will have to investigate further!! Maybe for a future post. 🙂
LikeLiked by 1 person