How to use Excel functions and formulas

Why do I need formulas for working in Excel? Microsoft Excel is not just a program for creating spreadsheets. In fact, this is a powerful tool for working with tabular data. The most important advantage of MS Excel is the built-in functions, as well as the ability to create formulas for calculation. These formulas can be either very simple or very complex.
How to create a formula in Excel
Using Functions in Excel
Financial functions in Excel
Excel date functions
Excel math functions
Statistical functions in Excel
Working with arrays in Excel
Excel database functions
Excel text function
Logical functions in Excel

How to create a formula in Excel

Beginning PC users often believe that it is very difficult to create formulas in Excel, and therefore do not use this functionality of the application. In fact, in order to master the simplest calculations in Excel, it’s enough to spend quite a bit of time.

MS Excel allows you to perform various operations on data tables in two ways:

Creating formulas manually.
Using the built-in Excel functions.

How to create a formula in Excel? Any formula, whether written by hand or created using built-in functions, starts with a “=” sign. Next go directly to the calculation. In order to understand the mechanism for creating formulas, we illustrate this with the simplest example.

There is a table with the code of the goods, their quantity and price. Opposite each title, you need to put the total amount of the cost of the goods. Thus, in each cell of the final column there must be a product of the price for the quantity of this commodity.

Let’s create a formula that will automatically multiply the contents of these cells.

Place the cursor in cell D2.
In the formula line, type =B2*C2 and press Enter.

In cell D2, the result of multiplying the contents of cells B2 and C2 will appear. If you copy cell D2, not the content of the cell (number 48) is copied, but the formula.

And this formula will automatically change depending on the number of cells. For example, if the D2 cell is copied to D3, then the cell numbers in the formula increase by 1, that is, the formula in cell D3 takes the form =B3*C3.

In the above example, it is enough to write the formula only once, and then just copy it to the bottom cells by pulling the bottom right corner.

If it is necessary to copy not the formula, but the contents of the cell, you need to change the formula a little by putting the $ sign to its elements. For example, for cell D2, this formula takes the form =$B$2*$C$2.

The figure shows that the contents of the cell were copied, not the formula.

You can also make only a part of the elements of the formula unchanged. For example, we multiply the quantity of goods in each cell by the price of the goods with the code 111111. For cell D2, the formula is an example of the form =B2*$C$2. If this formula is copied and then pasted into another cell, the second factor will remain unchanged. That is, in this example the quantity of any product will be multiplied by 12.

In these examples we have considered only the simplest formulas. In fact, they can be with a lot of elements, mathematical signs, brackets. It is important to remember the priority of performing calculations within the formula. At first, the action is always performed in parentheses. Then exponentiation, multiplication and division are calculated. And only after this addition and subtraction.

If you doubt that you correctly defined the sequence of calculations in the Excel formula, enclose the necessary elements in parentheses. Even if they can be omitted by the rules of mathematics.
Sometimes users forget that the obligatory condition for multiplying the elements of the formula is the multiplication of the sign *, because the rules of mathematics allow to omit the multiplication sign. For example, the formula “(a + b)c” according to the rules of mathematics will be considered correctly written, while Excel will give an error. The formula in this case must have the form =(A2+B2)*C2.

Using Functions in Excel

Functions in Excel are another important element of this application. In its essence, the function is a short record of the frequently used formulas. For example, instead of writing the formula =B2+B3+B4+B5+B6, you can use the function =SUM(B2:B6). There are a lot of functions, they are divided into categories. With the help of them, you can perform not only mathematical calculations, but also perform statistical, financial, engineering calculations and much more.

For beginners, it is recommended that you use the function wizard when exploring Excel functions. You can find it by clicking on the function icon located near the formula bar or by clicking on the triangle in the top Excel menu near the sum icon.

Let’s consider a simple example. You need to calculate the total amount for the specified column. We use for this purpose a function that calculates the amount in the specified range of cells.

Click on the cell where the total amount will be indicated.
Click the function call icon.
Select the desired function from the category list and click OK. In this case it will be a function of SUM. Each function has a brief description of what it does.
Select the arguments to the function. In this case, this will be the range of cells from D2 to D8.
After clicking the “OK” button in the final cell, the result of calculating the total amount appears.

For each of the Excel functions in the function wizard, you can see a brief hint or call help on this function. The selected function can be edited in the formula bar. It is also possible to enter the function manually by simply typing it in the formula bar without using the function wizard.

Financial functions in Excel

It is impossible to imagine the work of an accountant, economist or financier in this application without using Excel functions. In addition, financial functions can be used in other professions related to economic calculations. They can be useful even for ordinary users of home computers.

In order to use any of these functions, you need to select the appropriate section in the function wizard and click on the “OK” button.

In the appeared list of financial functions, select the desired function and click on the “OK” button. Next, in a new window, specify the arguments to the function and click “OK”.

Let us consider an example of using a financial function. One of the most sought-after elements of this section is the YIELD function. It calculates the profitability of securities for which interest is paid at some periodicity. How to use the YIELD function?

Select the cell in which the value of this function will reside. Find the YIELD in the list of financial functions and select it.
In the arguments window, click in the field of the first argument, then click on the cell with its value. The value of this cell will appear in the argument field. Similarly, fill in the remaining fields.
After clicking the “OK” button in the “YIELD” cell, the calculated value of this function appears.

The most commonly used financial functions are:

FV. Finds the future value of the investment.
IRR. Internal rate of return.
MIRR. Modified internal rate of return.
IMPT. Interest payments for the specified period.
PV. Presented value of investments.
RATE. Calculates the interest rate on an annuity.
EFFECT. Actual interest rate.

Excel date functions

Date and time functions are very convenient to use when certain manipulations with time slots are required. For example, when you need to calculate the number of days from one date to another, find out which day of the week the specified date corresponds to, add a few days or months to the specified date, and much more. The functions of date and time are several dozen. Consider in the simplest example how such functions are used.

There is a table with the columns “Year”, “Month”, “Day”. It is required to add a column with a date in the format dd.mm.yyyy.

Click on the Date cell and call the function wizard. Select the “Date & Time” category, and in the list of functions, select “DATE”.
In the arguments window enter the addresses of the cells “Year”, “Month”, “Day”. And click OK.
In the cell “Date” the date will appear in the required format. In order for the date to appear in the remaining cells of the column, it is enough to copy this cell by pulling the mouse over the lower right corner.

Here are a few more frequently used functions from this section.

DAY. Returns the day from the specified date.
EDATE. Counts the date in the specified number of months. If you specify a negative value for the Month argument, the function will calculate the date that precedes the specified one.
EOMONTH. Displays the date of the last day of the specified month.
HOUR. Converts the specified number to a clock or selects only hours from the selected date.
NETWORKDAYS. Counts the number of working days between two specified dates.

Excel math functions

The section of mathematical functions covers not only mathematical calculations, but also trigonometric ones. This is probably the most commonly used function. One of the most famous mathematical functions is the SUM function. Let’s consider an example of using this function, when you need to calculate the amount of one of the columns of the table.

Select the cell where the required amount will be located, go to the function wizard and select the category “Math & Trig”.

Select the SUM function in this category and click OK.

Type the appropriate arguments and click OK. In the selected cell, the value of the sum of the contents of the cells in the specified range appears.

The commonly used mathematical functions are as follows:

SUMIF. Counts the amount in the indicated cells, but subject to the specified conditions.
ROUND. Displays the rounded number value to the specified number of characters. Applies only to individual cells, not to a range of cells.
PRODUCT. Counts the product of individual numbers or the contents of the specified cells.
RANDBETWEEN. Displays a random number in the range between the specified values.
ROMAN. Converts the Arabic numerals in the indicated cells to Roman numerals.

Statistical functions in Excel

As the name implies, the statistical functions are intended for statistical analysis of data in Excel tables. A large part of such functions is used for the analysis of probabilities. Perhaps, these functions will seem very complicated, but among them there are some fairly simple functions that even ordinary users can use in their work.

AVERAGE. The function is designed to calculate the average value in the cell ranges. Moreover, there can be several ranges. In calculations, non-contiguous ranges and cells can also be used.
AVERAGEIF. The function is similar to the previous one, but the values for calculating the mean are selected according to the specified condition.
MAX. Finds the maximum value in the specified range of cells.
LARGE. Finds the specified value from the range of cells. For example, you can find the second largest value or the tenth largest value from the list.
MODE. This function finds the most commonly occurring value in the specified data array.

Working with arrays in Excel

This group of functions allows you to process data from an array. The result of these functions can be either one value or an array of values. To go to the list of functions for working with arrays, you need to go to the “Lookup & Reference” category of the function wizard. Array functions are difficult to understand, but in some cases they can be the only way to solve complex Excel tasks without using a variety of macros and long formulas.

For example, we will discuss the use of the TRANSPOSE function. It converts the horizontal range of cells to vertical and vice versa. The first line of the array becomes the first column of the new array and so on. For example, there is a table with lines January, February, March and the number of days in each month. It is necessary to transform it so that the names of the months go horizontally.

Select the array of empty cells with three columns and two rows.
Open the function wizard, find the category “Lookup & Reference” there and select the “TRANSPOSE” function. Click OK.
Select all the elements of the initial array with the mouse. Click OK.
In order for the function to start, click the mouse in the formula bar and press CTRL + SHIFT + ENTER. This shortcut should end any function for working with arrays.
The new table will look like this.

Excel database functions

The database in Excel is, in fact, ordered data. In order to work with these functions, tables must satisfy several conditions.

The table should not contain empty rows or columns. You can not use merged cells.
Each column must have a header. The header should not contain empty or merged cells.
The data in each column must be strictly of the same type. That is, either numeric, or textual, or date and so on. Numerical cells cannot be empty. In the absence of meaning, zero is put there.

To work with database functions, you can, for example, use the following table:

Each of the functions for working with databases uses three arguments:

Database. This is a complex of interconnected cells, where the rows are records of the database table, and the columns are fields.
Field. This is the column of the table that uses the function to calculate. It can be either its name in quotation marks (for example, “Price”), or the number of this column in order (for example, 3 for the “Price” column).
Criterion. This is the interval of cells with given conditions. It must contain at least one column name and one cell with the condition under it.

An example of a database function is the DCOUNT function. Using it, you can count the rows in the database table that satisfy the specified conditions. This function is convenient to use in the presence of complex criteria based on formulas.

Excel text function

Text functions, as the name implies, are designed to work with text data of Excel tables. But these functions can be used in the work with numerical data. Consider the work of one of the text functions in a simple example.

There is a table where the contents of the cells are written in capital letters. You need to convert them to lowercase.

Go to the function wizard and select the “Text” category. Select the LOWER function and press “OK”.
In the function arguments that appear, specify the cell whose contents you want to convert, and click OK.

There are many other functions for working with text data.

CONCATENATE. Combines the contents of several specified cells in one cell.
UPPER. Makes all letters in the specified cell uppercase.
PROPER. Capitalizes only the first letter.
EXACT. Compares the contents of two text strings case sensitive. If the value is the same, the function returns “TRUE”. Otherwise – “FALSE”.
TRIM. Removes all extra spaces except for single.

Logical functions in Excel

Logical functions are one of the most common functions of Excel. Often, they are used when performing financial calculations. Logical functions check the validity of the given conditions and, if the conditions are met, the function produces the value “True”, and if not, “False”. All of them in their work use comparison signs, such as “=”, “<“, “>” and so on.

In order to use the logical functions in the work, you should go to the function wizard and select the category “Logical”.

Let us briefly consider some of them.

IF. This function returns one result if the specified condition is true, and another result if false.
TRUE. This function is used only in conjunction with other functions. It returns only the logical expression “True” and has no arguments.
FALSE. Similar to the function TRUE, only returns the logical expression “False”.
NOT. Changes the obtained logical value by the opposite meaning, replacing “True” with “False” and vice versa.

So, using MS Excel, you can create financial, mathematical, statistical and other types of reports and documents. The application allows you not only to generate these reports, but also to securely encrypt them. Experts strongly recommend putting protection against unauthorized access to critical files. Even if you subsequently lose the key to the encrypted data, you can easily find it using special Excel password recovery programs.