If you're new to Excel, you may be a little lost when faced with the power of this tool. Whether for professional purposes, for your studies or for personal projects, Microsoft's spreadsheet is essential. So, if you want to get started or improve, here are 16 Essential Excel formulas (13 for beginners and 3 to go a little further) to discover and master urgently!
13 essential Excel formulas to get started
We Are
Although very basic, this formula is one of the most used. As its name suggests, it allows you to add the contents of several cells.
The basic formula is =SOMME(Cellule1;Cellule2;Cellule3) to add specific cells, but the most efficient is to add a range of cells that follow each other: =SOMME(CelluleDebut:CelluleFin). If you have a table, you can do the exact same thing by simply choosing the start cell and the end cell.
Mean (arithmetic)
Another basic operation very useful in statistical calculations and reports of all kinds is the average. The basic formula is =MOYENNE(Cellule1;Cellule2;Cellule3 in which all cells to be considered are separated by semicolons.
As in the previous case, it is not uncommon to have your data in a table in a row or in a column, so we can simplify by using the colon (:) to indicate the start and end cell, i.e. =MOYENNE(CelluleDebut:CelluleFin). It can also be applied to a complete table.
Maximum and Minimum
Here it is not one, but two formulas that we are going to see together. These formulas allow you to obtain the highest value and the lowest value of a group of cells. As for the other formulas, you can list each cell individually by separating them with semicolons, but here again, it is more interesting to select rows, columns or the whole set. The formula takes the following form for the maximum =MAX(CelluleDebut:CelluleFin) and for the minimum=MIN(CelluleDebut:CelluleFin).
Count with NB or NBVAL
If you have a table with a long list of entries and you want to know how many there are, whether it's numbers or other information. You will be able to use =NBVAL(CelluleDebut:CelluleFin) where what is in parentheses are the cells you want to count.
If you only want to count cells containing numbers, the formula to use is =NB(CelluleDebut:CelluleFin). Please note that these two formulas do not count empty cells!
stallion
This function eliminates the decimal part of a number. It can be applied directly with =ENT(124,9), which will return 129, or =ENT(Cellule) to perform the same operation on a specific cell.
transposition
If you often work with data matrices, this formula will be very useful because it allows you to swap rows and columns. The formula to use is =TRANSPOSE(CelluleDebut:CelluleFin), the first cell being the one that occupies the position row 1, column 1 and the last one being the one that occupies the last row and the last column. The formula works with a table but also with a column or a row. The column will be transposed into a row and the row into a column
Conditional formulas
The IF formula is really very useful because it allows you to integrate a conditional dimension. The idea is this: depending on whether something is true or false, then the formula will perform different actions. The classic example: if the grade is less than ten, then "failure". Otherwise, it will be "success". The formula is written as follows: =SI(Cellule<10;"Échec";"Réussite"). Note that each part is separated by semicolons and that the texts are in quotation marks. You can put whatever you want in place of failure or success, including formulas.
Number of days between two dates
If you are working on projects with dates, the DAYS formula will be very useful to you since it allows you to calculate the number of days between two dates. Be careful, in the formula =JOURS(DateFin;DateDébut), you must first put the final date then the initial date.
To display the current date in Excel, use =MAINTENANT().
Replacements
If you have detected an error in your Excel project or change your mind about naming a concept, instead of using the manual search and substitute option, you can opt for the =SUBSTITUE(Cellule;"texte à remplacer";"nouveau_texte"). Useful for making quick corrections to a large number of cells!
concatenation
Among the essential Excel formulas, this very useful formula allows you to combine information from different cells into another cell. Practical if you need to associate several cells together. The formula to use is =CONCATENER(Cellule1;Cellule2;Cellule3...), but be careful because it adds the contents of the cells one after the other, that is, without spaces between the strings. You can use =CONCATENER(Cellule1;" ";Cellule2) to add a space between the two. Similarly you can add text directly into the formula by putting it in quotes.
Count characters
It can sometimes be useful to know the number of characters in a cell, which you can easily do with =NBCAR(Cellule).
5 essential Excel formulas to go a little further
If you are comfortable with the formulas listed above, here are 3 essential Excel formulas that can be particularly useful although they are a little more complex.
SearchV and SearchH
The VLOOKUP (Vertical Lookup) and HLOOKUP (Horizontal Lookup) functions are extremely useful for looking up a value in a table and returning a corresponding value in another column or row. These formulas are written as follows: =RECHERCHEV(valeur_à_rechercher, table_de_recherche, numéro_de_colonne, valeur_proche).
The value to search for corresponds to a reference. The lookup table corresponds to the matrix or table in which you want to do your search. The value to search for must be in the first column of this table for a vertical search or first row for a horizontal search. The column number corresponds to the column in which the information you want to retrieve is found. For "Close value" you should generally put "False". In this case, the search will be done on the exact term. If you put "true" the formula will look for a close value.
In this example, the value to search for is "Pierre" which is in the first column of our table. We want the formula to return the grade corresponding to Pierre in the 2nd column of the table.
Index and Equiv
More complex but even more powerful than VLOOKUP and HLOOKUP, Index and Match are two powerful functions for retrieving data from a table. INDEX returns the value of a cell in a table based on its coordinates, while MATCH returns the relative position of a value in a range. Combined, they are very effective: =INDEX(table;EQUIV(valeur;plage ligne;0);EQUIV(valeur;plage colonne;0)).
The INDEX function first asks for the table coordinates, then the row number and the column number. From these two pieces of information, the formula will return the value of the cell at these coordinates.
The MATCH function will return the position of a searched value in a row or column.
So by combining the two we have a very powerful search tool.
In the example above we are looking for Peter's grade, the data range is in B1:D6. We will then look for the coordinate of the row, that of Peter (the first MATCH will return 2 because Peter is in the 2nd row of the table) then the coordinate of the grade (the 2nd MATCH will also return 2 because the word "Grade" is in the 2nd column of the table).
By simplifying the EQUIV in the formula we arrive at this =INDEX(B1:D6;2;2) which gives 8 as a result.
Sum.If and Sum.If.Set
To make conditional sums, the SUMIF function allows you to sum cells based on one criterion, and SUMIFS for multiple criteria. Which gives =SOMME.SI(plage_de_condition;critère;plage_à_sommer).
The SUMIF function works the same way except that you must first enter the range of values to be added, then the first range of criteria, the first criterion, the second range of criteria, the second criterion, etc. =SOMME.SI(plage_à_sommer, plage_de_condition1;critère1;plage_de_condition2;critère2)
The list of functions available in Microsoft Excel is vast and it is always good to consult the help to discover some potentially interesting functions and understand how they work. Some are not very intuitive and too long to memorize, unless you use them frequently. However, the formulas mentioned in this article are useful and commonly used, which makes them easy to learn to get the most out of Microsoft's spreadsheet.
