Introduction

What is an Excel formula?

Excel formulas are expressions that define how the value of an Excel cell is calculated. The value of the cell is automatically updated every time when the cells that are referenced in its formula change their values.

Excel formulas always start with equality sign (=) and usually include a combination of cell references (e.g., A1, E13), calculation operators (e.g., +, , *), and Excel functions (e.g., SUM, IF).

What is an Excel function?

Excel function is a part of Excel formula that performs a predefined set of calculations based on user-provided inputs – or parameters – and returns the result as a function value.

Excel functions always start with the function name (e.g., SUM, IF) and a left parenthesis (“(“), which is usually followed by a number of parameters separated by commas – or semicolons, depending on your regional settings – and, finally, a right parenthesis (“)“). Some functions don’t take any parameters (e.g., NOW(), RAND()).

Most useful Excel formulas and functions

The list of most frequently used Excel formulas and functions varies by user and highly depends on your goals. However, the following are the ones that we find the most helpful and widely used:

  1. SUM function – to add up cells
  2. COUNT function – to count the cells that contain numeric values
  3. Basic arithmetic operators – to subtract, multiply, and divide cells
  4. SUMIFS function – to add up cells that meet specific criteria
  5. IF function – to check whether a specific condition is true
  6. MIN and MAX functions – to find minimum and maximum values
  7. VLOOKUP function – to pull a value from a table based on its row title and its column number
Quick Examples

Sample task

Let’s say, we have a report that includes monthly actual and budgeted sales for the current year. The report is updated every month as we receive new numbers from our sales reps. We need to create a form that would calculate a number of analytical parameters and would update automatically every month when we add new data. Figure 1 shows an example of how such a worksheet might look like, with the actual sales report on the left-hand side, and the analysis form with our most useful excel formulas and functions – on the right.

useful excel formulas

Figure 1. An example of applying some useful Excel formulas

How to use SUM, COUNT, MIN, and MAX functions?

useful excel formulas sum

Figure 2. SUM function

First, let’s say we want to calculate total actual sales year-to-date. SUM function in Excel is the easiest way to do it. Here are some step-by-step instructions:

  1. Select the target cell (H4 in our example above). If you need help with this step, consider reviewing our post about how to select cells in Excel
  2. Type =SUM(
  3. Select the cells that you want to add up: C4:C15
  4. Type )
  5. Press Enter

=SUM(C4:C15)    – the final formula

Figure 2 shows how your formula should look like before you press Enter in step 5 above.

Same way, we can count the total number of months where we have actual sales data (COUNT function), as well as lowest and highest monthly sales to date (MIN and MAX functions). COUNT, MIN, and MAX formulas will include exactly the same steps as described above, except you’ll need to select different target cells in step 1 (H5 for COUNT, H6 for MIN, and H7 for MAX) and type an appropriate function name in step 2 (=COUNT(, =MIN(, and =MAX().

Figure 3 shows the formulas you should have for all four functions discussed above.

useful excel formulas count min max

Figure 3. SUM, COUNT, MIN, and MAX functions

How to use arithmetic operators in Excel?

useful excel formulas arithmetic operators

Figure 4. Formula with arithmetic operators

Let’s say we want to look at estimated full-year sales, calculated as YTD sales divided by a number of months and multiplied by 12 months. The easiest way to do it is by using basic arithmetic operators in Excel formula:

  1. Select the target cell (H9)
  2. Type =
  3. Select the cell with total sales YTD (H4)
  4. Type a division sign (/)
  5. Select the cell with the number of months YTD (H5)
  6. Type a multiplication sign (*)
  7. Press Enter (see Figure 4 for how your screen should look like before this step)

=H4/H5*12    – the final formula 

Other helpful arithmetic operators in Excel include addition sign (+), subtraction sign (), and calculation precedence signs, or parentheses (“(” and “)“).

How to use SUMIFS function?

Next, let’s calculate budgeted sales year-to-date – the sum of all cells in column D which have corresponding actual sales in column C. SUMIFS is the best function to solve this problem:

  1. Select the target cell (H11)
  2. Type =SUMIFS(
  3. Select the cells to be summed up – in our case, the monthly sales budget (D4:D15)
  4. Type a comma
  5. Select the cells to be tested for a condition – actual monthly sales in our case (C4:C15)
  6. Type a comma
  7. Type a condition to be tested – whether the actual monthly sales exist (“>0”). This is the condition that we want to test for every cell in C4:C15. If the condition is satisfied, the appropriate monthly budget from D4:D15 will be added to the total
  8. Press Enter (see Figure 5 for how your screen should look like before this step)

=SUMIFS(D4:D15,C4:C15,”>0″)    – the final formula

useful excel formulas sumifs

Figure 5. SUMIFS function

How to use IF function?

useful excel formulas if

Figure 6. IF function

Now that we have actual and budgeted year-to-date sales, we can compare them and tell whether we are on track vs. our targets. Let’s use IF function to add an automatic check whether current sales are higher than our budget:

  1. Select the target cell (H13)
  2. Type =IF(
  3. Add a condition to test. In our case, we’re testing whether actual sales YTD (H4) are at least higher (>=) than budgeted sales YTD (H11). As usually, you can either click on cells H4 and H11 or just type them in, depending on your preferences
  4. Type a comma
  5. Type the value to be returned if the condition is satisfied: “Yes”
  6. Type a comma
  7. Type the value to be returned if the condition is not satisfied: “No”
  8. Press Enter (see Figure 6 for how your screen should look like before this step)

=IF(H4>=H11,”Yes”,”No”)    – the final formula

How to use VLOOKUP function?

Finally, let’s imagine we need an automatic pull for sales in a given month. In other words, we want to specify the name of the month in one cell and get the actual sales for that month in another cell. VLOOKUP is the function to use in that case:

  1. Select the target cell (H15)
  2. Type =VLOOKUP(
  3. Select a cell with the value to find – the name of the month in our case (G15)
  4. Type a comma
  5. Select a rectangular area on the worksheet that would include the month names as the first column and the actual sales as the last column (B4:C15)
  6. Type a comma
  7. Type number of the column in the selected rectangular area which includes the values we are looking up: 2
  8. Type a comma
  9. Type 0 to find the exact match of the month name
  10. Press Enter (see Figure 7 for how your screen should look like before this step)

=VLOOKUP(G15,B4:C15,2,0)    – the final formula

useful excel formulas vlookup

Figure 7. VLOOKUP function

Test yourself...

Useful Excel Formulas Quiz

Take a quick quiz to understand how well you know some useful Excel formulas and functions:

Share This