Tips for an Easier Life in Excel, Part #2: Formulas

As a continuation from last week’s post, Essential Tips to Make your Life Easier in Excel, we are excited to share tips for basic Excel formulas!

To reiterate our proposal, we will help you with a series of great tips on Excel in the next several weeks. Since our community has answered more than 1,000,000 questions in the Smarterer Excel test, we want to help you by cutting through the clutter and drill down on the most important techniques in Excel.

For part 3 of this series, here are of the most frequently used formulas that anyone should learn:

Basic Excel Formulas:

Excel is a prerequisite for most professions today: accounting, finance, sales etc. For almost any job, you will have a bunch of data points to analyze. Here are basic formulas you won’t escape from:

  • Sum(range): Adds the numerical value of all cells within a range. Text is ignored.
  • Average(range): Obtains the average of all cells within a range.
  • Median(range): Obtains the middle value all cells within a range.
  • Round(range): Round the value to the specified decimal place.
  • Min(range): Obtains the lowest value of all cells within a range.
  • Max(range): Obtains the highest value of all cells within a range.
  • Count(range, value): Counts the number of cells that contain numbers that are not in a contiguous row or column.
  • Countif(range, criteria): Counts the occurrence of a value within a range.

Fancy & Powerful Excel Formulas:

When you have a large dataset with thousands of categories and data points, how do you go about your project? Thanks to Excel, many analyses today much easier to execute comparing to the old days, saving thousands of hours of mundane, manual labor. Here, we recommend 3 formulas that you must master to get to the next level:

Sumif(range, criteria, sum-range):
Suppose you are a real estate agent and wants to know your commission based on different value of properties that you sell:

For the total sum of the commissions for property values over 160,000, use this formula =SUMIF(A2:A5,”>160000″,B2:B5). Explanation: The A2:A5 defines the range. “>160000” is the criteria that Excel should search for in column A, and B2:B5 is the range/column where Excel looks for the values to sum up.

 

Vlookup(value to lookup, range, number of column, exact match):
Suppose you are a scientist and you have the following data:


In this example, your goal is to find the Temperature when the Density is 1.09.

Explanation: Using VLOOKUP(1.09,A2:C10,3,FALSE), Excel first searchs the Density column (within range A2:C10) and finds the row that matches 1.09. It then finds the corresponding value in the Temperature within the same row, by counting the number of columns from left to right (e.g. Viscosity column would be 2, and Temperature column would be 3). The “FALSE” property at the end specifies that you would like an exact match for 1.09 in column A.

If(criteria, do this, else do this):
For many beginners, if statements look intimidating at first. But once you get used to the syntax, it is actually very straightforward. For example, you’re a financial analyst with a budget of $100. For this year’s budget, you have $50 in cell A1. To generate a simple report for management, you want cell B1 to display the words “Within budget”:

To do this, you simply input into cell B1 this formula: IF(A1<100,”Within budget”,”Over budget”). Explanation: the formula first looks at cell A1 and see if the value is less than 100. If it is, then it will display the words “Within budget” (note that in order to display text, you must wrap the value in quotation marks). Otherwise, if cell A1 is greater than $100, cell B1 should display “Over budget”.

As you might expect, you can combine various formulas to perform very sophisticated analysis in Excel. But before jumping the gun, it’s important to achieve mastery of individual formulas first. In Part #3 next week, we will show you how to combine these techniques to propel your productivity.

If you want to learn more, Bill Jelen—the renowned “Mr. Excel” and Microsoft MVP—offers a video showing many tricks on basic formulas.

Follow us on Twitter @smarterer to stay tuned for Part #3: Complex Techniques with Formulas.

In the meantime, why not test your current knowledge of Excel with our test?

Excel