5 Handy Excel Formulas and How to Use Them
1. VLOOKUP
VLOOKUP stands for Vertical Lookup and can be a really handy tool if you’re looking for specific information in a list or table and want to save time. Instead of reading through the whole list or table, simply find a cell where you can enter a VLOOKUP formula.
Start with =VLOOKUP(
Then add the name of the item you’re searching for information about. For example, if you have a list of gift items and want the price of a flower vase you would type:
=VLOOKUP(“flower vase”,
The argument is in quotes because it’s text. And the comma is important because it separates each argument.
Next in our formula, we’ll specify the cell range that contains the data. One thing to note is that VLOOKUP will always search the first column in the range, so for our example, we need to make sure that the first cell number has the letter of the column that contains our list of gift items.
If the table you’re searching goes from A2 in the upper left to B20 in the lower right, you would type:
=VLOOKUP(“flower vase”, A2:B20,
Now, we’ll enter the column index number. This number references the column where our answer will be found. For example, if we want the price of the flower vase, the column index number tells us what column the price is in. So, if it’s in the second column, our formula would be:
=VLOOKUP(“picture frame”, A2:B20, 2,
The fourth and final argument tells VLOOKUP whether it should search for approximate matches. To do so, you’ll enter TRUE or FALSE. TRUE means the function should look for approximate matches. But since we’re looking for an exact match – the price of the flower vase – we’ll type in FALSE. Our finished formula looks like this:
=VLOOKUP(“picture frame”, A2:B20, 2, FALSE)
When you press ENTER, the cell will display the price of the flower vase!
2. IF
The IF formula can be incredibly helpful if you’re dealing with contingent outcomes. For example, it can be of help if you want to give a free sample to all customers who’ve purchased over $60 worth of product. Imagine that in your table, the customer’s name is in Column A, the dollar amount of product they’ve purchased are in Column B, and you want the information about whether or not to send a free sample to show up in Column C. In that case, your formula would be as follows:
=IF(B2>=60, “Free Sample”, “”)
B2 represents the cell that has the dollar amount of product purchased, “>=60” indicates that we want the formula to judge whether the dollar amount is “greater than or equal to 60.” And then next part of the formula indicates that if the first part is true – the dollar amount is greater or equal to 60 – we want “Free Sample” to show up in Column C, and if the first part of the formula is false – the dollar amount is less than 60 – we want Column C to remain blank.
3. IFERROR
IFERROR is a variant of the IF Formula. It lets you return a certain value (or a blank value) if the formula you’re trying to use returns an error. For example, if your formula is dividing Column A by Column B and in one case, Column B is zero, Excel would display the #DIV/0! error. To keep your table looking nice, instead of entering the formula like this:
=A1/B1
you can enter it like this
=IFERROR(A1/B1, “”)
The empty quotes mean that instead of displaying the error message, you want it to leave the cell blank. But if you wanted the cell to say the word “no” instead of the error message, you could enter the word “no” between the quotes, and so on.
4. CONCATENATE
The concatenate function allows to combine the value of several cells into one cell. For example if Column A has first names (Mary), Column B has middle names (Jane) and Column C has last names (Smith) and you want Column D to list full names, you could type in:
=CONCATENATE(A1&B1&C1)
This will give you MaryJaneSmith.
If you’d like spaces between the three names, simply tell Excel that in the formula, using quotes around a space, as follows:
=CONCATENATE(A1,” “, B1, ” “, C1)
This will give you Mary Jane Smith.
5. 3DSUM
You’re probably familiar with the SUM function in Excel, which allows you to add up the numbers in a series of cells, for example:
=SUM(A2, A3, A6)
3DSUM is based on the same principal, but allows you to add up numbers from different sheets. So if you want to add numbers from three different spreadsheets that are on three different tabs, this formula can save you a lot of clicking back and forth, and a lot of time. If the tabs are named, from left to right: First, Second, Third, Fourth. Then the following formula will add up the numbers in cell B2 on each of the four spreadsheets:
=SUM(First:Fourth!B2)
Written by DSD Business Systems
