What are functions in Excel? Excel functions are a resource that allows us to do some calculations and operations in cells to obtain results in an easier and personalized way ; Depending on our purpose, and the function we use, we can get very useful results when we are using Excel, we can perform some simple operations, some complex calculation math operations, and some other non-calculation operations that have great utility..
Difference Between Formula and Function in Excel
The functions in Excel are in themselves formulas that use a resource called function in order to obtain specific results; And functions are also a resource that we can use within a formula in order to achieve the result we expect.
When we insert a function in a formula, we are creating a formula that performs an operation or calculation that brings us benefits in our work, such as speed and consistency.
Types of functions in Excel
When talking about functions in Excel, one tends to think that functions are a concrete way to perform calculations. However, although it is true that it is frequently used to perform mathematical operations, Excel functions have a wide range of possibilities and range of action to be able to perform other types of operations. Operations, for example, to search for data in other sheets, to be able to join or concatenate the content of several cells, or to check if the content of two cells is the same. These are just some examples of operations that can be carried out by using the functions in Excel. The range of possibilities of functions is very wide, and we can perform several different types of functions such as:
Within the capabilities of the functions in Excel,
we can perform simple calculations, but that make our work much easier. An example of a simple calculation that we can approach with the use of a function would be the autosum function, a function that is as basic as it is necessary and useful when making summations of columns or rows. It is a simple calculation, but it allows us to be able to make a summation of many cells quickly that avoids us having to make the sum by choosing cell by cell.
Look at the example image, as the same addition operation can be approached in Excel in two different ways, with and without the use of a function.
But with the functions we can also carry out complex and advanced calculations that we can carry out in order to obtain valuable information in order to better analyze the data..
A very clear example of a more complex calculation that is used frequently are conditional functions or logic functions. These functions allow us to obtain a personalized result, filtering the information according to the conditions that we want to apply to the formula.
The functions in Excel are definitely a very useful Excel tool that allows us to carry out simple calculations, but also a powerful tool with which we will be able to carry out complex calculations, and carry out other operations of interest such as searching for data or joining the content of cells . ..
How are the Functions: Syntax of a function
Functions in Excel are represented as a formula, in which we will find the “=” sign at the beginning and the name of the specific function that we want to use, followed by a parenthesis where the arguments of the formula will be included. function.
There are many functions that have a specific task, to obtain a specific value or result. And although many functions share this “backbone”, there are functions that, although they share the same structure (=function name (data range) ), are characterized by having a different syntax. For example, the aforementioned conditional functions or search functions. These are very useful special functions that we will explain in detail with examples.
Since there are many functions in Excel, it is difficult to learn all the functions that exist. But if we can learn at least the most relevant functions, the ones that we are going to use most frequently, and learn, as we are going to teach you, a way to be able to use any Excel function even without knowing of its existence.
1 Why are functions in Excel useful?
Excel functions are useful in response to several key factors in being able to work quickly and reliably:
• Speed and immediacy in calculations and operations: As we mentioned at the beginning, with the use of the functions we save a lot of time, and we avoid having to carry out operations cell by cell.
• Simplifies calculations: Excel functions save us a lot of time, but they also simplify formulas. When, for example, we have to carry out a calculation that involves many cells, by using the functions we avoid having to reflect all those cells in the formula. We went from talking about cells to range of cells.
• We avoid many errors: Excel functions automate the work, minimizing the possibility of making typical errors such as forgetting to include a cell in a calculation or formula. Or include a cell in the operation in the wrong way. These are two typical mistakes that we can avoid by using functions.
• Perform advanced operations and calculation tasks: Many of the results we get when we use functions would be difficult or impossible to achieve without functions. Functions open up a range of possibilities for us when carrying out operations, and being able to obtain interesting calculations when analyzing data and making decisions.
Copy a function in Excel
As we advance in the knowledge of Excel, its possibilities, resources and functions, it makes us more productive people and with better skills to face professional challenges.
When we discover new features and functions in Excel, we can apply the combination of all these resources so that the graph of our productivity is exponential.
In learning Excel functions, we must not only think about knowing the best functions, but we must also think about other resources that we have learned to be able to squeeze even more Excel's capabilities and potential.
When we are, for example, applying functions in an Excel formula, we must keep in mind other aspects of Excel that we already know how to use, such as being able to copy and paste formulas or drag them to complete a table. We have to know that we can fix a cell in an Excel formula, and we have to know that the use of functions in formulas gives us added value to be able to work much faster.
When we have to perform the same operation (for example, a sum), but with a different data range, we can also copy the cell where we have the formula with the function, and paste it in the other cells where we want to replicate that sum function.
As we will explain to you now, the Excel autocomplete function will help you to create the function in Excel, in a simpler way, and as you have just seen, later you will be able to use other learned resources, such as copying and pasting the function in other cells.
2 What are the most used functions in Excel
To expand the knowledge of the functions in Excel, we are going to see what are the most important functions, the most useful and practical functions.
Math Functions
- SUM function: the sum function is used to obtain, as we have seen, the sum of a range of data. It is one of the most used functions. When we work in data tables, it is very common to perform summations of columns in order to obtain a total value.
- AVERAGE function: The Average function, as its name indicates, helps us to obtain the average value of a data range.
- MAX Function and MIN Function: The Max (Maximum) and Min (Minimum) functions help us to obtain the maximum and minimum value of a data range.
- COUNT function: The count function is capable of summing the number of cells that contain a range of data.
text functions
- UPPER and LOWER function: The Uppercase function converts the contents of a text cell to uppercase; The LOWER function does the reverse, that is, it converts the contents of a cell from uppercase to lowercase.
- CONCATENATE function: If we want to join the contents of several cells (one or more cells) we can use the concatenate function that is capable of joining the contents of other cells in one cell.
Search and data extraction functions
- VLOOKUP function: The Vlookup function allows us to search for a text or number in another sheet and allows us to extract it to apply it to the sheet where we are working.
- INDEX function: The index function helps us retrieve the value of a cell according to its position in a data range.
- MATCH function: The match function looks for the exact match of in a data range and gives us its exact position.
Date Functions:
- TODAY function: This function gives us the exact day we are on.
- NOW function: The function now gives us the date of the day in which we are and the time.
- MONTH function: The Month function gives us the month.
Conditional functions:
- IF Function: With the IF Function we can establish some conditions in the formula to obtain one value or another depending on whether the established rule is met.
- AND function: The AND function gives us a TRUE or FALSE depending on whether the arguments we have established are true or not true.
- OR function: The OR function also returns a TRUE or FALSE depending on whether the arguments we have established are true or not true.
3 Create functions in Excel
When creating new calculations, or doing some operations in Excel, as we have seen, Excel offers a wide variety of functions that have different tasks: perform mathematical calculations, useful text functions, search functions, etc. We've described some of these features, but there are many more features that we can discover as we learn more about Excel, and we want to explore other useful features.
The most important thing is to first know the functions, know how we can create them, and try to use them whenever we can. As we have already seen, it is not only about saving time that we can gain by using the functions when doing some operations, but it is also about establishing a method and mechanisms that allow us to minimize errors. Just as it is recommended not to use fixed values in formulas, the use of references is recommended, when creating formulas, whenever we can, it is advisable to use functions. And how can we create functions?
As we have already seen, Excel is characterized by being an intuitive tool, where all the tools and functionalities are easily located from the ribbon. But, in addition, we have a search engine in the quick access bar that can help us enormously to find not only the functions, but also those options that we need to find.
There are many ways to access the function creation tool called “Insert function”.
“Insert function”, as we are going to see below, and as we have already explained on other occasions, is a tool that works as an assistant to locate the function that we need to find.
"Insert Function" is a tool that also enjoys a privileged location on the Excel toolbar; As we can see in the image, it is an always visible option that is to the left of the formula bar:
Remember that in order to access the "Insert Function" tool, we can also go to "Formulas" on the Excel ribbon.
The "Insert function" tool, as we see in the image if you remember, is a window from which we can write the name of the function or make a brief statement about the operation to be performed.
"autocomplete", an Excel tool that works in a similar way to the suggestions that a search engine gives us. Autocomplete is a function that we can call directly from the cell where we are going to create the formula. It's as easy as starting to type the name of a function in a cell, and after typing the ”=” sign. As we type, as a suggestion, we will see that Excel shows us a list of functions that match the text that we have written in the cell, as you can see in the image.
For example, if we want to use a function to find the maximum value of a data range, we insert the “=” sign, and then we write “MAX” in the cell where we want to create the function.
As we have seen in the image, Excel highlights in blue the function that matches exactly 100% with what we have written in the cell. Now by double-clicking on the suggestion, that is, by double-clicking on the function that in this case matches and Excel has highlighted it in Blue, we will see how Excel includes the function in the cell:
and now we will only have to select the range of data that we want to use. In this case it would be the column in which we are working, and very important, close the parenthesis after selecting the data range.
We press the "Enter" key on our keyboard, and we will have the result of the function. In this example, with the MAX function, we have asked you to give us the maximum value of the selected data range.
As we have seen, there are a multitude of functions, all of them useful, that we can use to be able to move faster, in a safer way, and to be able to do calculations and operations that would be impossible to carry out without the existence of the functions.
Now that we know a little more about the functions, we can delve into some important function to perform conditional calculations and extract content from other Excel sheets.
Remember that we should use functions whenever we can. The sum function is a clear example of a function that we will be able to apply to all of our tables, and that we should use whenever we can, instead of doing cell summations. In the same way, it will be very useful in many of the tables, to use the copy and paste functions, which, as we already explained, are useful to be able to copy formulas, also to copy formulas where functions are used.