The best use you can make of Microsoft Excel (or its open source counterpart LibreOffice Calc) is to perform automatic calculations. Trying to be super synthetic in this guide article, let's talk about its Functions, i.e. the commands that you can execute on Excel cells to make sure that they are automatically filled based on the data written in the other cells. In other words, after creating a table of numbers or other values, it is possible to select a cell whose value inside it, after configuring a function, fills itself. This formula can be, for example, the sum of the values of the other cells, making sure that this sum changes automatically only by changing or adding numbers in the table, making each type of calculation very fast, even with thousands of data.
In this guide we see how to use the most important Excel Formulas and Functions to know, both the best known and the most particular ones, which are still used every day by those who work with Excel.
READ ALSO: How to make calculations on Excel
The formulas are always written with a = in front (for example = SUM) and then below indicating, in parentheses, the cells to be added, separated with a semicolon (for example = SUM (A3; C2) is the sum of the values of cells A3 and C2) or separated by its points if you want to add all the cells in the middle of two (for example = SUM (A3; A8) adds the values from cell A3 to cell A8.
A quick way to create functions in Excel is to write the formula in a cell (for example = SUM), make a space and then select the cells to be included in the function, one by one, or by pressing on the header of a column or even dragging the mouse over a column or the range of cells whose sum is to be calculated or any other calculation is expressed. But I recommend not to forget to insert the brackets. Press Enter to accept the formula, while to exit the function mode you can press ESC .
READ ALSO: Guide to Excel Formulas to count numbers and cells (COUNT and SUM)
Note: an alternative method to select multiple elements to perform a mathematical function is to select individual elements by typing the beginning of the function, then holding down the CTRL key, you can select the cells you want to add or multiply one at a time. After typing the closing parenthesis ) to close the function, press ENTER to have the result displayed in that cell.
To use a function like MEDIA.SE you have to write in the following way: = SUM.ME (C1: C11; "> = 10") which means average all the cells that have a value greater than or equal to 10. In short, the range of cells is inserted in the parenthesis, then the; and then, in quotation marks, the condition. To facilitate the creation of conditions, you can use the function creator in the Formulas menu at the top.
In addition to SOMMA.SE (SUMIF) (guide) and MEDIA.SE (AVERAGEIF) (guide) other formulas with very common and easy to use conditions are:
To use the SE function, press in the cell where you want to display the result, then type = SE ( .
Then click on the cells you want to compare, separated by a comparison operator. For example A2 . Put a semicolon followed by the value you want to display if the condition is true. For example, type "YES" to see the word YES written in case the number of cells A2 is less than that of cell B2.
Then type another semicolon, followed by the word "NO" that you want to display if the comparison between values is not true and, therefore, if the value of B2 is lower than A2, we will have written NO and close the parenthesis .
Ultimately the formula of this example will be: = SE (C7> C8; "YES"; "NO")
You can then fill the entire column down to the last row to perform the same comparison on all the rows.
An advanced approach to using the SE function is to nest another calculation in the True or False fields. For example, if A2 , you can put that if it is True, you put out the result of A3-A2 while if it is false you make the difference A2-A3 (Guide to SE).
Other logical functions in Excel include operators such as:
E (AND): If all the cells of a list comply with the condition, the result is True, otherwise False appears. (see guide)
NON (NOT): this function can be used to invert the result in another cell; From true to false or from false to true. (see guide)
O (OR): If any of the cells meet the condition, the result is True, otherwise False. (see guide)
SWITCH : extracts a certain specified value if this is seen inside the selected cells (see guide)
XOR : if only one of the listed cells is True, the result will be True, if all are True or all are False, the result will be False. (see guide)
Just like with the SE instruction, it is possible to replace the true and false outputs with other formulas, making logic functions a truly powerful tool of EXcel, albeit complex to use at very advanced levels.
The CONCAT function allows you to concatenate multiple text strings from different cells of the worksheet into a single string. The use of this function is simple to write and just do: = CONCAT ("string1": "string2"; "string3") (see guide). With such a function it becomes possible to combine several words together in different cells.
Other text and word functions that you can use in Excel are:
There are Excel functions for almost any calculation or data manipulation purpose that you could imagine and even more. The more you experience with formulas, the better you become with Excel and you can also manage large and full spreadsheets of data of all types. The easiest way to try and learn the functions is to use the Formulas menu, press Insert function and, in the pop-up window, change the Category to see all the available and usable functions. The formula creation tool explains step by step how to select the various criteria, conditions and cells, making even a job that until recently was only for experts simple.
READ ALSO: Excel tricks to become experts in calculations and data presentation
In this guide we see how to use the most important Excel Formulas and Functions to know, both the best known and the most particular ones, which are still used every day by those who work with Excel.
READ ALSO: How to make calculations on Excel
How to use Excel functions
To use the functions in Excel, simply write the name of the function and then the cells that concern it. This can be done by hand by selecting a cell and then writing the function at the top, above the ABCDEF .. line. Alternatively, you can also go to the Formulas menu at the top and press the Insert Function key so that a guided formula creation box appears, with some basic examples.The formulas are always written with a = in front (for example = SUM) and then below indicating, in parentheses, the cells to be added, separated with a semicolon (for example = SUM (A3; C2) is the sum of the values of cells A3 and C2) or separated by its points if you want to add all the cells in the middle of two (for example = SUM (A3; A8) adds the values from cell A3 to cell A8.
A quick way to create functions in Excel is to write the formula in a cell (for example = SUM), make a space and then select the cells to be included in the function, one by one, or by pressing on the header of a column or even dragging the mouse over a column or the range of cells whose sum is to be calculated or any other calculation is expressed. But I recommend not to forget to insert the brackets. Press Enter to accept the formula, while to exit the function mode you can press ESC .
Excel Functions and Formulas to know
Below, let's see what are the most used functions by those who use Excel. For each formula we also see how it is written in Excel in English (the figures are also in English).Calculation functions
- SUM : it is the most used function in Excel. Its use is very simple, just write the word = SUM in a cell and then the range of cells with the values to be added.
- COUNT : to count the number of cells, use the formula = COUNT. NUMBERS
READ ALSO: Guide to Excel Formulas to count numbers and cells (COUNT and SUM)
- AVERAGE: The AVERAGE function works in a similar way, it shows the average value of a series of numbers and is used by writing in a cell = AVERAGE . Then drag the mouse along the column of cells to average the values. (guide)
- MAX : The MAX function is useful when there is a long list of numbers in a column and you need to find the largest value. Type = MAX (in the cell), then drag the mouse along the column of cells where you want to find the maximum value. (guide)
- MIN : The MIN function is like MAX, only it is used to find the minimum value in the list. (guide)
- PRODUCT (PRODUCT): This function allows you to multiply the numbers of the selected cells, also good for calculating a price amount by a percentage (for example VAT or discount made). Then write = PRODUCT, make a space and then insert the cells to be multiplied or the range of cells in between. (guide)
- MATR.SOMMA.PRODOTTO (SUMPRODUCT): This function, which in English is called SUMPRODUCT, allows you to select multiple lists of numbers in the worksheet (therefore multiple columns of data), multiplying the sums of the selected columns with each other. (guide)
Note: an alternative method to select multiple elements to perform a mathematical function is to select individual elements by typing the beginning of the function, then holding down the CTRL key, you can select the cells you want to add or multiply one at a time. After typing the closing parenthesis ) to close the function, press ENTER to have the result displayed in that cell.
Logical SE functions
More complex formulas can be created by adding conditions . Functions with conditions are created by adding the .SE to the function (for example SUM.SE). These formulas allow you to perform calculations on cells only if certain conditions are met . For example, if you have a column of numbers, you can make an average of those that exceed a certain limit with the AVERAGE function.To use a function like MEDIA.SE you have to write in the following way: = SUM.ME (C1: C11; "> = 10") which means average all the cells that have a value greater than or equal to 10. In short, the range of cells is inserted in the parenthesis, then the; and then, in quotation marks, the condition. To facilitate the creation of conditions, you can use the function creator in the Formulas menu at the top.
In addition to SOMMA.SE (SUMIF) (guide) and MEDIA.SE (AVERAGEIF) (guide) other formulas with very common and easy to use conditions are:
- COUNTIF (COUNTIF): only counts cells that satisfy a condition. (guide)
- COUNTIF ( COUNTIFS ): like COUNTIF, but can be used to count string data. (guide)
- MIN.PIÙ.SE (MINIFS): will return the minimum value in a range of elements that satisfy the condition. (guide)
- MAX.PIÙ.SE (MAXIFS): as MIN.PIÙ.SE, but returns the maximum value in a range. (guide)
Logic functions
Some of the most useful Excel functions are the logical functions that are used to compare different cells to return results based on the comparison results. For example, the SE function will examine whether the cells match the comparison conditions set. If the comparison is true, the result field will return whatever value is specified if it is TRUE, otherwise, it will return the value specified for FALSE.To use the SE function, press in the cell where you want to display the result, then type = SE ( .
Then click on the cells you want to compare, separated by a comparison operator. For example A2
Then type another semicolon, followed by the word "NO" that you want to display if the comparison between values is not true and, therefore, if the value of B2 is lower than A2, we will have written NO and close the parenthesis .
Ultimately the formula of this example will be: = SE (C7> C8; "YES"; "NO")
You can then fill the entire column down to the last row to perform the same comparison on all the rows.
An advanced approach to using the SE function is to nest another calculation in the True or False fields. For example, if A2
Other logical functions in Excel include operators such as:
E (AND): If all the cells of a list comply with the condition, the result is True, otherwise False appears. (see guide)
NON (NOT): this function can be used to invert the result in another cell; From true to false or from false to true. (see guide)
O (OR): If any of the cells meet the condition, the result is True, otherwise False. (see guide)
SWITCH : extracts a certain specified value if this is seen inside the selected cells (see guide)
XOR : if only one of the listed cells is True, the result will be True, if all are True or all are False, the result will be False. (see guide)
Just like with the SE instruction, it is possible to replace the true and false outputs with other formulas, making logic functions a truly powerful tool of EXcel, albeit complex to use at very advanced levels.
Text functions
To end this guide to Excel functions, let's talk about text functions, which are not mathematical formulas or calculations, but can be useful for concatenating words with each other or adding product lists and so on. Excel offers a certain variety of string formulas that can be used in these situations.The CONCAT function allows you to concatenate multiple text strings from different cells of the worksheet into a single string. The use of this function is simple to write and just do: = CONCAT ("string1": "string2"; "string3") (see guide). With such a function it becomes possible to combine several words together in different cells.
Other text and word functions that you can use in Excel are:
- LEFT (LEFT): returns the number of characters specified from the left side of a word (see guide)
- RIGHT (RIGHT): returns the number of characters from the right side of a string. (see guide)
- STRINGA.ESTRAI, MEDIA.B (MID): Extracts characters in the center of a word. (see guide)
- CANCEL SPACES (TRIM): removes spaces at the beginning or end of a string. (see guide)
- TEXT (TEXT): converts a value from any cell into a word version of that value. (see guide)
- LENGTH (LEN): to know the number of characters in a word or text. (see guide)
There are Excel functions for almost any calculation or data manipulation purpose that you could imagine and even more. The more you experience with formulas, the better you become with Excel and you can also manage large and full spreadsheets of data of all types. The easiest way to try and learn the functions is to use the Formulas menu, press Insert function and, in the pop-up window, change the Category to see all the available and usable functions. The formula creation tool explains step by step how to select the various criteria, conditions and cells, making even a job that until recently was only for experts simple.
READ ALSO: Excel tricks to become experts in calculations and data presentation
