Whether you’re studying statistics or English, there’s a good chance that you’ll need to use a spreadsheet to analyze data at some point. And while Excel is extremely powerful, it’s not always intuitive.
These five functions will help you in a range of situations. Memorize them so you’re ready to use them on any data set you come across, and you’ll save yourself tons of time when you need them!
CONCATENATE
If you have a spreadsheet that contains a lot of text, there’s a good chance that you’ll need to combine different text fields. It could be first and last names, or names and phone numbers, or any other type of text data. Having that data split up into different columns can be inconvenient, but merging the cells gets rid of the existing organization.
What can you do?
Excel’s CONCATENATE function is perfect for this situation. It takes the text in two or more cells and puts it together in a new cell.
For example, you might have “John” in cell A2 and “Smith” in B2. Instead of merging these cells, you can concatenate them.
In this case, you’d need to click into an open cell, then type the following:
=CONCATENATE(A2, “ “, B2)
In our example, we’re concatenating three things: the contents of cell A2, a space (between the quotes), and the contents of cell B2. In the end, you’ll get a cell that reads “John Smith.”
VLOOKUP
Excel has several different lookup functions, but VLOOKUP is likely the most useful. It lets you search for one value in a table, and get back the corresponding value from a different column in the same row.
For example, let’s say you have students, scores, and grades. If you know the student name, you can enter it into the formula and have the equation return the grade. It’s important to note that the data needs to be organized in columns; so students should be in one column, scores in the next, and grades in the next.
Here’s the syntax you need to know:
=VLOOKUP(lookup_value, table_array, col_index_num)
The lookup value is the known value; in our example, the name of the student. The table array is the area occupied by the table you want to search. If the table in our example were aligned with the top-left corner of our spreadsheet (with headings), it might be something like A2:C50. Finally, col_index_num is the column that contains the value you want returned (learn VLOOKUP more in-depth here)
To look up Jason Jones’ grade, you’d use this formula:
=VLOOKUP(“Jason Jones”, A2:C50, 3)
Excel will look for the text “Jason Jones,” then return the corresponding value from the third column in the table. In this case, it would be his grade.
IF
Logical functions let you tailor the returned values from your formulas. The IF function checks whether a logical statement is true; if it is, Excel can either tell you that it’s true or execute another command.
For example, you could tell Excel to display a value only if another value is above a certain threshold. Let’s look at the syntax before we set up this example:
=IF(logical_test, [value_if_true], [value_if_false])
Here’s an example you might use in grading. Let’s say column 2 of your spreadsheet contains test scores, and you want Excel to tell you if students passed or failed. Here’s how you might do that:
=IF(A2>65, “Pass”, “Fail)
This formula checks cell A2 for a score higher than 65. If the score is indeed higher than 65, Excel will display “Pass.” If it’s not, it will display “Fail.”
You can even chain multiple IFs together, like so:
=IF(A2<65, “Fail”, IF(A2<70, “D”, IF(A2<80, “C”, IF(A2<90, “B”, “A”))))
In this case, Excel checks for a score under 65. If it finds one, it displays “Fail.” If it doesn’t, it checks to see if it finds a score under 70. If it does, it displays “D.” If it doesn’t, it checks to see if the score is under 80. And so on.
COUNT
With numbered rows, you might think that you’ll never need to use a function that counts values. But COUNT can be great if you have a spreadsheet where some cells are blank.
For example, if you use =COUNT(A1:A100), you’ll get the number of cells that have numbers in them. That could be all 100 cells, or it could only be a subset. If you need to know how many data points are missing, or how many people answered a certain question on a survey, COUNT will do it for you.
COUNTA is a related function, but it doesn’t just count cells with numbers in them; it counts cells that aren’t empty. So running it on the same 100 cells as we did before would give us a different answer if some cells have text or TRUE/FALSE values in them.
It might not seem that useful right now, but it’s a good function to be able to call on when you need it.
MAX / MIN
The MAX and MIN functions are very similar; the first returns the largest number in the set you’ve selected, and the second returns the smallest.
Just type =MAX([your data]) and you’ll get the largest number in the set. =MIN([your data]) does the opposite.
It’s a great way to find these two numbers more quickly than using Excel’s sort and filter options. You can also leave them in place to see how the maximum and minimum values change after you’ve made edits to your data set.