Learning how to use Excel can be difficult, especially if you’re not familiar with using formulas in spreadsheets. In addition to being able to use calculations, Excel has built-in formulas that you can apply to your data. Here are eight formulas that every Excel user should know about, especially those just getting started with using the program. 8 Excel Formulas You Should Know
1) SUMIF
The SUMIF function finds a specific value within a range. If multiple values are found, it only adds up cells that contain those specific values. This is useful for adding up values in one column based on specific criteria you’ve set forth in another column—like tracking sales numbers by region, or even keeping track of your total monthly spending by day.
2) MAXIFS
This formula is used to determine which of a range of values is highest. The MAXIFS function has two arguments: a cell reference, and a list of conditions. The MAXIFS function returns an item from its list of conditions that satisfies its corresponding condition. If no condition is satisfied, it returns #N/A.
3) AVERAGEIF
This formula provides a way to average values based on matching values in a range. For example, if you want to find an average of all sales from 2011 in a range named sales, you can use AVERAGEIF(sales, 2011-01-01:2013-12-31). The function returns 12/31/2012 because that is when your date range ends.
4) TRIMMEAN
This formula, TRIMMEAN(), uses Microsoft Excel’s Data Analysis ToolPak to calculate a sample median. The keyword trim means you want to remove outliers from your set of data. In statistics, an outlier is a data point that appears to be inconsistent with the rest of your set of data and possibly misleading because it skews all your results.
5) IFERROR
The IFERROR function allows you to define a default value in case a formula returns an error. This is extremely helpful when dealing with complex formulas that contain various If statements, or when calculating statistical data. For example, =IFERROR(A1/C1,) will provide a result of 0 if cell A1 contains no number and cell C1 contains a number. It will return blank if one of these cells is blank or contains any text or other non-numeric values.
6) VLOOKUP
Do you have more than one column of data and want to know what’s in each row of one column? Enter VLOOKUP into your spreadsheet. If, for example, you have a list of first names in column A, and a list of last names in column B, VLOOKUP will give you exactly what you need (the full name) if entered correctly. In order to use it correctly, though, your table must be sorted by both columns A and B. Here is an example of how to find someone using VLOOKUP:
For more information about using VLOOKUP see here One of my favorite uses for VLOOKUP is to match students with an alumnus of a certain college, based on name. If you have a list of colleges in column A and a list of last names in column B, enter =VLOOKUP(A2,B2:D7, in cell C2 and it will pull up column C (containing last names) with any matches to your college’s alumni.
7) LOOKUP
The lookup formula is incredibly useful. It allows you to see data in a table (even if it’s not directly beside your data) and copy that information into a new cell for analysis.
8) CHOOSE
Excel has built-in functions that can be incredibly useful, but many users don’t know how to access them or use them correctly. It can be easy to forget about some of these gems—but it’s worth reviewing your favorites and making sure you know how to use them in multiple scenarios. Here are eight of our favorite formulas.