Basic Excel formulas and functions

Microsoft Excel is the most common application used for calculations and visual presentation of data. Excel makes it possible to automate even the most complex calculations. Knowing the basic formulas and functions of this program allows you to perform all these tasks much faster. It is very useful to use formulas when a large number of the same type of actions with many operations is performed. A novice user of MS Excel does not need to know all the built-in functions of this application, there are a lot of them. It is enough to have an idea about the main functions and use them in practice. This will greatly increase the efficiency of working with Excel.
What is the difference between a function and a formula in Excel?
Ways to Insert Formulas
Sum formula
Average formula
Formula to count the number of cells
Boolean IF
Function to remove extra spaces from text
A function to find the maximum and minimum value

What is the difference between a function and a formula in Excel?

There are two ways to perform calculations in Excel – formula and function.
A formula is an expression that calculates the values of a range of cells or a single cell. For example, =C1+C2+C3+C4 is a formula that calculates the sum of the range of cells from C1 to C4. Formula in Excel
Cell C5 in the figure contains a formula that adds up the values of the cells in the range.

A function is a ready-made formula that is built into Excel. It saves the user from the time-consuming manual entry of the formula. Each function has its own name, which is the same as what it does. For example, the function =SUM(C1:C4) calculates the sum of the range of cells from C1 to C4.

Function Excel
Each function consists of a name and an argument. The name of the function is, in fact, the name of the formula that will perform certain calculations. After the name of the function, its arguments, that is, the parameters of the function, are written in brackets. Arguments can be of different types, for example, logical, text, numeric.

A formula can consist of one or more functions. To write a formula, you need to double-click on the cell that will contain the calculated value of the formula. Then write an equal sign and an expression that will calculate the value of the cell.

Ways to Insert Formulas

There are several ways to insert formulas in Excel. Each of them has its own advantages.

  1. The usual insertion of a formula inside a cell. To use this method, place the cursor in the cell where the result of the formula will be located. Type an equal sign and enter the formula. When using functions, start typing the name of the function. Excel will then offer a list of functions beginning with those letters. Select the desired function and press the Tab key. Typing  name of function
  2. Inserting a function in the Formulas tab. From the Excel main menu, click the Formulas tab. Click the “Insert Function” button. A window will appear with a list of functions, in which you have to select the desired function. Click OK. Inserting a function
  3. Selecting the desired function from the group of functions on the “Formulas” tab. This quick way to select the desired function is useful if you know what type of function you need: financial, logical, mathematical, and so on. Go to the “Formulas” tab, click on the group to which the function belongs, and select it from the proposed list. Formulas  tab
  4. Using the AutoSum button. This button is found on the Formulas tab and on the Home tab. After clicking on it, a list of the most frequently used functions will appear, for example, sum, average, maximum, minimum. AutoSum button
  5. Paste from the list of recently used functions. If you have used the same function frequently recently, you can select it from the list of recently used functions. To do this, open the “Formulas” tab and click the “Recently Used” button. Then select the desired function from the proposed list. Recently Used

These are the most common ways to insert functions. Use any of them depending on the circumstances of using the function, and then you will be able to enter functions in your calculations as efficiently as possible.

Sum formula

This formula is used very often in Excel. In the simplest case, the plus sign + can be used to calculate the sum of the contents of the table cells. For example, let’s find the sum of cells B2, B3 and B4. To do this, place the cursor in the cell where the total amount will be located and type the equal sign = in it. Then left click on cell B2, click the + sign, click on cell B3, click on the + sign, click on cell B4, and press the Enter key. Find the sum
After you press Enter, the sum value will appear in the final cell. Everything is very simple, but, as a rule, no one uses this method in such calculations, because there is a faster and easier method.
To automate the calculation of the sum of data in Excel, there is a special function that does this automatically. This function is called SUM. It is written like this: =SUM(number1;number2;…), where the number 1, the number 2 and so on are the addresses of the cells to be summed. If a range of cells is used, a colon is placed between the first and last cell of the range. For example, you can use the following formula: SUM(B2:B6;B7:B9;B12).

SUM formula
In order to use this formula, place the cursor in the final cell, type in it the combination SUM(B2:B6;B7:B9;B12) and press the Enter key.

To sum consecutive cells, you can use the autosum function, which is located in the Excel menu on the Home and Functions tabs. Autosum function
Simply select the range of cells in the column that you want to sum with the mouse and click the autosum icon. Below the selected range, the value of the sum of all cells will appear.

Average formula

Every student knows that in order to find the arithmetic mean of several numbers, it is necessary to sum them up and divide by the number of terms. For example, let’s find the arithmetic mean of cells in the range from B2 to B4. We put an equal sign in the final cell and write the formula: = (B2 + B3 + B4) / 3 and press the Enter key.Find the arithmetic mean
The arithmetic mean value will appear in the final cell. This method is quite cumbersome and inefficient. Fortunately, Excel has a special function for calculating the arithmetic mean. In general, it looks like this: =AVERAGE(first cell of the range:last cell of the range). That is, in this example, the function will look like this: =AVERAGE(B2:B4).

Average formula
Similarly, you can calculate the arithmetic mean in a table row.

Using this formula, you can find the arithmetic mean not only for consecutive cells. You can also calculate the average of individual ranges. For example, =AVERAGE(A1:C1,E1:H1).

Formula to count the number of cells

Often, when working with tables, you need to count the number of cells. For this purpose, MS Excel has a special COUNT function. This function counts the number of cells in the specified range. And only cells with a numerical value. For example, to count the numeric cells of a column in the range B2 to B7, use the following formula: =COUNT(B2:B7) COUNT function
Note that the total number of cells in the example will be 5 because the range contains one non-numeric cell. You can use multiple ranges in this formula by separating them with a semicolon. For example, =COUNT(B2:B7;D4:D6).

The COUNTA formula is used to count the number of non-empty cells of any type. For example, the formula =COUNTA(B2:B7) in the above example would return a value of 6.

Boolean IF

This function is used to test some conditions and return results depending on whether the condition being tested is true or not. In such formulas, not only numbers can be used, but also text, other functions, and so on. In general, the IF function looks like this: =IF(logical expression, value if true, value if false).
For example, let’s check the contents of cell B2. If its value is greater than 40, we write “greater than” in the final cell. If less than 40, put the word “less” in the final cell. The formula will look like this: =IF(B2>40;”greater than”;”less”). Boolean IF
Please note that the text arguments in the formula are written in quotation marks so that the program correctly recognizes the text.

Excel allows you to use nested IF functions. In general, such a formula might look like this: =IF(logical expression, value if true, IF(logical expression, value if true, value if false)). Here two conditions are checked. If the first condition is true, then the result of the first argument is written; if it is false, the formula will check the next condition.

Function to remove extra spaces from text

Extra spaces are not uncommon when using cell text values. Especially if you copy information into a table from other sources. Extra spaces in text not only look untidy, but can also lead to errors when such cells are used as arguments in formulas. To remove extra spaces in Excel there is a special function TRIM.

Consider an example. The given table has a column with a first and last name. In the first cell, the value is written without extra spaces, in the second cell, the space is at the beginning of the text, in the second cell, the extra space is between the words, in the third cell, the space is at the end of the phrase. Let’s remove these spaces so that the value of all cells is the same as the value of the first cell in the column. In the next column, put the number of characters in the cell. Remove extra spaces
Let’s put the results of the formula in column A. To do this, in cell A2, write the formula =TRIM(B2). Drag down the lower right corner of the formula cell to copy it to subsequent cells in the column. Please note that extra spaces will be removed.

Function TRIM
Copy the results to the original column, or delete the original column. Note that this function does not remove non-breaking spaces.

A function to find the maximum and minimum value

As the names of the functions indicate, they find the minimum and maximum value in the argument list or within a given range. In general, these functions look like this:
=MAX( number1, number2, …)
=MIN( number1, number2, …)

As a rule, the formula specifies the ranges of cells among which you want to search for the maximum or minimum number.

These functions take into account only those cells that contain numbers, and, for example, text or empty lines are ignored. If the specified range of cells does not contain numbers, these functions return a value of zero.

Consider an example. Find the maximum value in the specified column. Function MAX

  1. Place the cursor in the cell where the result of the function will be located.
  2. Write the formula =MAX(C2:C9)
  3. After pressing the Enter key in a given cell, the result of the formula will appear. Maximum value

The MIN function works in a similar way.

We have considered a far from complete list of all Excel functions. It’s such a powerful tool that it’s impossible to cover all the features even superficially in one article. However, the ability to use even this small number of basic functions allows you to work with tables much more efficiently.

And, of course, don’t forget to encrypt confidential Excel documents. Especially if you are not the only one with access to your files. Even if you ever lose the cipher, special programs will help you easily Excel password recovery.