Excel Bootcamp: Fab Five Functions
As an accountant, I am basically married to Excel. But my love of Excel doesn’t stop at my career. I am an avid planner and Excel/Google Sheets has been a lifesaver when it comes to vacations, party-planning, and moves. I know a lot of people tend to struggle with spreadsheets, either because the process of learning how to use it is boring so they check out (ME) or because they haven’t had any practical experience with it (also me before I started working). Hopefully, I can explain these in a brief and slightly entertaining way. Without further ado, here are a few of my favorite basic functions!
TRIM
One of my pet peeves with spreadsheets is when there are extra spaces within cells. This almost always happens when you let a bunch of different people input data into a form or a spreadsheet. That’s where the trim function comes in. You can use it to quickly remove all of those stupid excess spaces quickly.
The function for this is =TRIM(reference cell)
That’s the whole function. So easy right?
IF
This next function is incredibly basic, but honestly has really helped me evaluate data in an easier way once I started using it. The general idea of this function is to return a specific response depending on if the value meets certain criteria.
The function for this would be =IF(reference, value if true, value if false)
So, for example, if I want any class that I withdrew from to return the phrase “Re-take” and all other grades to return the word “good”. I would enter the following formula: =IF(B1=”Withdraw”, “Re-take”, “Good”)
SUM
If you want to add up a series of numbers, there are a few ways you can do it.
The first, is that you can highlight all of the cells you want to add up, and then reference the number in the bottom right hand corner of Excel.
The second way can be done by typing an equal sign, selecting or keying in the cell code, and then hitting the plus sign and selecting the next cell that you want.
Ex: =B1+B2+B3+B4…
The third, and easiest way is to use the SUM function. You create the functions by typing equal sign, SUM, and open parenthesis. Then you will select the row or column that you would like to add up, then close the parenthesis. If your values are not condensed into one column or row, you will hold down the CTRL button and select the cells you want to sum.
EX: If I want to know how many credit hours I’ve taken so far, I would do =SUM(B4:B23)
Freeze Panes
Okay so I’m not 100% positive that this qualifies as a function, but this is vital if you’re working with a large amount of information that requires you to scroll at all. I have really terrible eyesight, so I end up using this on every spreadsheet that I work out of.
What you’ll do is select either the row below the row you want to freeze or the column to the right of the column that you’d like to freeze. You’ll then go to the “View” tab and select “Freeze Panes” and then select the new “Freeze Panes” button. If you’re only wanting to freeze the top row or the first column, you can just select those options within the first “Freeze Panes” button. Once you’ve done that, you can scroll all over and your headers should follow along with you.
LEFT/RIGHT/MID Functions
I’m grouping these 3 functions together because they’re basically the same thing. I think the clearest example I’ve seen to illustrate the usage of these functions is class numbers (ex: ACC340). You’d use these functions if you want to pull a certain number of characters from the words in a cell.
- LEFT
- Using the LEFT function, you would return a string of characters starting with the left-most one.
- =LEFT(reference cell, number of characters)
- EX: If you want to pull the first 3 characters of each class, you would use the function =LEFT(A1,3)
- RIGHT
- Using the RIGHT function, you would return a string of characters starting with the right-most one.
- =RIGHT(reference cell, number of characters)
- EX: If you wanted to pull the class level from each class code, you would use the function =RIGHT(A1,3)
- MID
- Using the MID function, you would return a string of characters starting with a specific selected character.
- =MID(reference cell, number of the character you want to start with, number of characters you want to pull)
- Not sure why you would want to use this for the example I’ve selected, but I backed myself into this corner here. So if you wanted to pull 3 digits from the last letter of the class codes, you would use the function =MID(A1, 3, 3)
No Comments