Learning Excel is one of those basic needs that we must meet to facilitate not only our entry into the world of work, but also many processes of our daily lives. The Microsoft spreadsheet is a really powerful tool that is also involved in many areas, from work to academics and even in personal projects. In that sense, if you are thinking of starting your path with this program, we want to help you take the first steps. Therefore, we are going to review the basic formulas that you should know to learn how to use Excel.
These formulas will help you move smoothly through the tool, performing arithmetic operations, ordering elements, counting and other functions that will give you the possibility to perform various tasks.
What is a formula in Excel?
When it comes to Excel, even if you have no knowledge in the area, surely you have heard of formulas. The vast majority of operations in this program are facilitated by a formula, which is nothing more than a special code or equation that we insert to carry out an action. The actions in question can range from a simple sum, the ordering of elements on the sheet or the calculation of variables such as areas, surfaces and more.
Excel has an extensive catalog of financial, logical, mathematical and trigonometric formulas, as well as search and reference formulas. Likewise, you will find options oriented to statistics, engineering and also for information management.
In this way, if you are in the middle of a job that involves any of these areas, it will be enough to have the necessary data and enter the formula in question, to obtain the exact result. In that sense, we are going to immediately review what are the basic formulas that we must handle to learn Excel.
The formulas you need to know to start learning Excel
Basic mathematical operations
First of all, we are going to show you how to carry out the basic mathematical operations of addition, subtraction, multiplication and division. Actually, the only one with a real formula format is Sum, while the rest are simply based on the use of the signs determined for each task..
Amount
To perform an Excel sum we must enter the following formula:
=SUM(A1:A2) or =SUM(2+2)
As we can see, the formula supports two modes, one to add two different cells and another to add two numbers within the same cell.
Resta
On the other hand, to subtract the process is much simpler because you only have to use the “-” symbol. In that sense, we would have something like:
=A1–A3
In this way, we subtract the values of two cells, although it is also possible to do it with numbers within it.
Multiplication
As in subtraction, multiplication in Excel is based on using the asterisk as a symbol. Thus, to multiply two or more elements we would have something like:
=A1*A3
It is noteworthy that, as in the previous cases, we can multiply two values within the same cell.
Division
Finally, to do division operations, we will use the bar as a symbol. In this way we have:
=A1/A3
Also, you can use the same formula to divide two numbers in a cell.
AVERAGE
The AVERAGE formula is extremely useful in many fields and as its name indicates, it helps us to obtain the average value within the selected set of numbers. It should be noted that this operation is also usually known as Mean or Arithmetic Mean and is very present in the academic field and also in statistics.
To use it you must enter:
=AVERAGE(A1:B3)
SI
The IF function is part of the conditional formulas, quite useful for when we need to check if some scenario is true or not. This is especially used in areas such as academics, for example, where we can have a list of students with their grades and right next to it a note indicating whether they have passed or not. In this way, we have to set the formula so that it validates if the condition is met.
In that sense, the syntax is as follows:
=IF(Condition, Value if true, Value if not true)
Thus, if the passing grade is 50, we would have an example like this:
= YES(B2>=50, PASS, FAIL)
WILL COUNT
COUNTA is another of the basic formulas to learn how to use Excel that we must handle because of its great utility. With it, you can count the number of cells with data that exists in a given range. In this way, if you need to know the number of elements in a column and that it also be updated, then you can use it.
To do this, the syntax is as follows:
COUNTA (A1:B3)
Inside the parentheses you can enter the range of cells you need to validate and you will immediately have the result.
Hyperlink
Although Excel recognizes the links when we insert them and immediately converts them into a link accessible from the click, the HYPERLINK formula takes this possibility a little further. In that sense, we can customize the hyperlink text, making some text appear instead of the link.
The formula is as follows:
=HYPERLINK(link, text)
=HYPERLINK(“www.windowsnoticias.com”, “Get to know our website”)
VLOOKUP
VLOOKUP is one of the most popular functions in Excel and will allow you to find the value you want within a range of cells. In this way, you will be able to avoid going through each cell to find the data you need and instead, it will be enough to apply the formula in question. The syntax is as follows:
=LOOKUP(LOOKING FOR VALUE, RANGE OF CELLS, NUMBER OF COLUMN WHERE THE LOOKING DATA IS FOUND, ORDERED)
In this sense, the first parameter to enter is the value sought, which is nothing more than the reference that we will use to locate the data in question. Then, it is the turn of the range of cells where the search will be done and then add the column number where the data we want to find is located.. The columns start to number from the one you selected. Lastly, Sorted refers to whether the search will return an exact or approximate match. For exact matches, enter FALSE.