30 Excel Formulas and Their Functions (from Basic to Advanced and with Examples)


In these times, where data is the new oil, handling it under any method that is practical, simple to use and effective will be favored by the people who have to make use of it.


That's how Excel, the computer program developed by Microsoft, which came to Mac in 1985 and to Windows in 1987, is “the calculation processor” most used in the history of computing.


With its columns, rows, chart and table options, and the possibility of programming with the Visual Basic language, it becomes a great resource with countless options that can be accessed through formulas, which is why today we'll dedicate an article to it that will be a real feast of numbers.


Shall we begin? Let's go for it!


What is an Excel formula?


An Excel formula is a calculation function that is carried out through the programming language developed by Microsoft, which is Visual Basic.


They are, without a doubt, the reason for the computer program's existence. This is so because that grid made up of columns and rows would be nothing if it didn't allow play through formulas.


And when we say play we don't mean the playful side of calculations, but the endless solutions these formulas represent and why it's so used for financial organization, accounting, presentation of financial statements, quick calculations, etc.


In all cases, formulas let you perform a certain calculation nimbly and precisely, being thus safer and more practical than using a calculator.





What are Excel formulas for?


From the most basic formulas to the most complex, Excel contains a sum of them presented as functions that simplify calculations, enabling data processing.


Although some appear to be very simple at first glance, it's worth getting to know and seeing in examples what each formula is for so you can create practically smart tables where you only have to add some data and the calculations resolve automatically.


This way, you'll be able to solve not only simple calculations, but also the most complex ones, with a combination of functions that you can apply to:


Process data.


Classify information.


Process data.


Assign values or patterns.


Visualize results through charts based on exact calculations.


Compare information and visualize the results of said comparison to detect and understand patterns.


If you're wondering with which formulas you'll be able to do all this, keep reading.


What types of Excel formulas exist?


The types of Excel formulas that exist can be classified according to their functions into six groups:


Mathematical functions


These formulas are the best known and most used by Excel users; however, this doesn't mean they're the simplest to use. While they serve to perform sums and multiplications, there are several types of sums to do here.


In addition, that's not the only thing. You can also do multiplications, count elements simply or conditionally, obtain whole numbers or get averages.


Later we'll see examples of each case. Logical functions


For their part, the formulas that serve logical functions are related to the use of certain logic, like obtaining a result only under a certain condition. These formulas are more complex when you use one or more at the same time under the nesting function. 


Don't worry if it's not clear yet, we'll also see it later.


Text functions


Not all calculations are carried out based on numbers. In Excel it's possible to organize data (to the right or to the left), concatenate cells, remove spaces or organize the length of a text string through formulas.


Lookup and reference functions


Do both functions require formulas? That's right, as long as you want to do something as a consequence of these actions. Later we'll show you examples that will leave you open-mouthed and wondering why you didn't apply it before!


Date functions


When your calculations depend on dates, working days, holidays, vacations, etc. then you need the right formulas to automate it.


Operational functions


This represents all the formulas, simple and complex, that will make everything else boost its effectiveness. From flagging errors, importing dynamic data and even ranking it in a list, this group embraces very good formulas for good.


If just by naming the function groups we sparked your curiosity, wait until you get to know the formulas, with examples and all. Let's start with the most basic ones!





List of 10 basic Excel formulas


The formulas based on mathematical functions are, as we said earlier, the most used. We're sure that at some point you turned to them and that, almost unconsciously, you knew how to obtain the result of a sum by prefixing the equals sign (=).


Don't do it intuitively; here we tell you what the 10 most used basic Excel formulas are:


Before getting into more complicated formulas, let's see how to do the simplest mathematical operations: additions, subtractions, multiplications and divisions. Technically only the sum is a formula, since in the rest of the cases special operators are used.


SUM 


From a technical point of view, SUM is a formula and the rest (multiplications, divisions and subtractions) are operations. It's like the base formula.


For example: =SUM(A1:A50)


SUBTRACTION


Subtraction is a very simple operation involving the values of each cell and row, the initial equals sign and the intermediate hyphen for subtraction between the parameters chosen


For example: = A2 - A3


MULTIPLICATION


Instead of inserting a hyphen sign (-), in this case the asterisk sign (*) is used between the parameters marked by each cell and row.


For example: = A1 * A3 * A5 * A8


DIVISION


Once again, this calculation requires the use of another sign. In this case it's the forward slash.For example: = A2 / C2


COMBINED OPERATIONS


You can combine mathematical operations and even prioritize some over others, for which you'll use parentheses, in addition to the signs previously explained.


For example:  = (A1 + C2) * C7 / 10 + (D2 - D1).


AVERAGEIf you want to know the average between two or more parameters, you'll use this function and the parameters within parentheses.


For example: = AVERAGE (A2:B2)


IFERRORGenerally, when we get a formula wrong this code appears: #DIV/0! which is really the expression that we asked for a function with zero value, which is very common when we do divisions. This operation lets you return the value if you have an error or a string of errors.For example:  =IFERROR (MAX(A2:A3) / MIN(C3:F9),"There has been an error")


IF


With this formula you can return a different result if a certain condition is met. For example, if you want a cell to automatically say OK if another has a number greater than 10, or NO if it has less than 10, you have to do it like this:Example: =IF(B1>B2="OK","FAILED")


COUNTIF


With this formula you'll be able to count certain ranges that meet the requirements you indicate. 


For example: =COUNTIF(C2:C, "OK")


You can also implement the same function but with a set of conditions. In that case it's =COUNTIFS (C2:C, "OK","FAILED") 


RANDOM


It's the ideal formula for selecting values at random, being able to use it for drawings. For example:  =RANDBETWEEN (10,20)


Are there more simple Excel formulas to explore? Of course there are! But let's put some order, friends, otherwise this article would have no end. 


If you want to know how to do slightly more complex calculations, line up to shed the rookie label, because with these formulas you'll become true experts!


20 advanced Excel formulas you have to know


Yes, the last formulas we shared, which are in the section of the simplest ones, have a certain degree of complexity. Don't be scared! Once you master them you'll know you have the power of calculations in your hands.


Shall we go a little deeper? Of course!


ROUND


Nobody wants to do calculations with decimals and even though the formula simplifies them it's good that the figure can be visually rounded. 


For example:  =ROUND(cell, number_of_decimals)


AVERAGE


If you have to get the average between two values, it will be enough to select each one by placing this formula in another cell.


For example:  =AVERAGE(A1:A10) 


CALCULATE VAT IN EXCEL


Here you have to make use of two formulas: multiplication and addition (=A1*B1 and =A1+B1). Now, we'll use A2*B2 to multiply the net price with VAT at 21% (which is the percentage equivalent to this tax in Argentina) and then we reproduce the formula in all the cells we want. In the same way, we copy it in the rows we want. Here we integrate the example into the explanation to make it easier when you practice it. 


CONCATENATE


As the term says, you can join a set of texts in a single cell. If in one you have first names and in another you have last names, you can join the correct first and last names in a following column.


For example:  =CONCATENATE(“A2”, “space“, “B2”)


CHOOSE


In this formula an argument will participate; this is a code that indicates a certain index and it's recognized as index_num. This argument returns a certain value within a list of values. You can use this formula for financial analysis.For example: =CHOOSE(index_num; value1; [value2]; ...)


INDEX MATCH


With this function you can indicate that a value (INDEX) returns a value from a cell based on a certain column and rows. In this case, we use the word MATCH to return the position.For example: =INDEX(C3: E9, MATCH (B13, C3: C9,0), MATCH (B14, C3: E3,0))


MID


Here the aim is to return a certain number  of characters belonging to a text string based on specified requirements like the position and the number of characters. You have to know that this formula considers each character as 1.For example: =MID("text", starting_position, num_of_characters)


MATCH


Just like when you do lookups and references, with this formula you'll be able to find a certain element in a  range of cells and return it to a position.


For example: =MATCH (lookup_value,lookup_array, [match_type]) 


AND


You can join several logical tests with this formula. When the result is TRUE,  the AND function will return TRUE, otherwise it will return FALSE. 


For example: =AND(logical_value1, [logical_value2], ...)


OR


With this function, on the other hand, you can check whether a criterion is TRUE and extract the value. However, when the criteria are different, the value is FALSE. 


For example: =OR (logical_value1, [logical_value2], ...) Don't get frustrated if, when reading it, it's still incomprehensible. The reality is that Excel formulas are explained in practice and only when they respond to a concrete need do you start to understand the logic behind this programming language.


Once you're practicing and playing with the formulas, you'll notice how simple it is to combine functions and solve matters that previously seemed impossible. At Coderhouse we've designed the ideal course for you to acquire this information, from commonly used formulas to the most advanced ones.


Remember that Excel has more than 400 different options, so don't expect to learn them all overnight. Join our Coder community of knowledge and start using Excel like data professionals. 

If you'd like to keep exploring this topic, you can also read how to build an AI project portfolio to land a job.

Recommended Coderhouse courses

If you want to go deeper into data analysis and applied artificial intelligence, Coderhouse has programs for all levels:

• Data Analytics Course: to learn to analyze data, build dashboards and make decisions based on real information.

• AI Engineering Course: to incorporate AI and machine learning models into your data projects.

• Introduction to Artificial Intelligence Course: to understand the AI ecosystem and complement your data profile with knowledge about language models.