## 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:

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

## 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.

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

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:

- 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 - Type =SUM(
- Select the
*cells that you want to add up*: C4:C15 - Type )
- 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.

## How to use arithmetic operators in Excel?

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:

- Select the
*target cell*(H9) - Type =
- Select the cell with total sales YTD (H4)
- Type a
*division sign*(/) - Select the cell with the number of months YTD (H5)
- Type a
*multiplication sign*(*) - 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:

- Select the
*target cell*(H11) - Type =SUMIFS(
- Select the
*cells to be summed up*– in our case, the monthly sales budget (D4:D15) - Type a
*comma* - Select the
*cells to be tested for a condition*– actual monthly sales in our case (C4:C15) - Type a
*comma* - 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 - Press
*Enter*(see Figure 5 for how your screen should look like before this step)

=SUMIFS(D4:D15,C4:C15,”>0″)

– the final formula

## How to use 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**:

- Select the
*target cell*(H13) - Type =IF(
- 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 - Type a
*comma* - Type the value to be returned
*if the condition is satisfied*: “Yes” - Type a
*comma* - Type the value to be returned
*if the condition is not satisfied*: “No” - 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:

- Select the
*target cell*(H15) - Type =VLOOKUP(
- Select a cell with the
*value to find*– the name of the month in our case (G15) - Type a
*comma* - 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) - Type a
*comma* - Type
*number of the column*in the selected rectangular area which includes the values we are looking up: 2 - Type a
*comma* - Type 0 to find the
*exact match*of the month name - 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 Quiz

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