Microsoft Excel It's much more than a simple spreadsheet. It's a powerful tool that, when used properly, can become your best ally for analyzing data, managing budgets, developing automated processes, and much more. In this article, we reveal the Essential Excel formulas and functions, in order to get the most out of this tool.
Knowing how to use Excel formulas and functions correctly is not only an important saving time, it also allows us work with greater precision, clarity and efficiency. We tell you everything below:
What is a formula and what is a function in Excel?
Formulas, functions… In Excel, both concepts are closely related, but it is important to understand that they are not the same. formula It's any equation you type into a cell to perform a calculation. For example: =A1+A2 add the values in cells A1 and A2.
On the other hand, function It's a predefined formula in Excel that automatically performs specific operations. A classic example is: =SUMA(A1:A10), which sums all the cells between A1 and A10. Functions allow simplify the work a lot, since they avoid having to write long and complex formulas.
All functions begin with the equal sign (=) and they can have one or more arguments, which indicate where they obtain the information to calculate the result.
Parts that make up an Excel formula
To master Excel, you need to understand the elements that combine within a formula:
- Constants: fixed values such as numbers (5) or text ("Invoice").
- References: point to cells or ranges. Example: A1 or B2:B10.
- Operators: symbols that indicate mathematical action: +, -, *, /, ^.
- Features: built-in formulas such as SUM, AVERAGE, MAX, among others.
Let's see a complete example: =SUMA(A1:A3)*2+5. Here, SUMA(A1:A3) is a function, * y + They are operators, 2 y 5 they are constant.
How to create formulas in Excel step by step
Creating a formula is simple:
- Select the cell where you want to see the result.
- Type the equal sign (=) to indicate that you are entering a formula.
- Write the formula using references, operators, functions or constants.
- Hit Enter.
Practical example: if in A1 you have the number 10 and in A2 the number 5, and you write in A3 =A1-A2, the result will be 5.
Basic Mathematical Formulas in Excel
These are the simplest operations and the ones you will probably use daily:
- Sum:
=A1+A2or using function:=SUMA(A1:A10) - Subtraction:
=A1-A2 - Multiplication:
=A1*A2 - Division:
=A1/A2 - Parenthesis: to control the order of operations:
=(A1+A2)*A3
Excel respects the classic hierarchy of mathematical operations: First, multiplications and divisions are performed, then additions and subtractions, unless parentheses are used.
Key statistical and logical functions
Excel is brilliant when it comes to analyzing large amounts of data, And for this, statistical and logical functions are essential. Here are some you should know by heart:
- AVERAGE: calculates the arithmetic mean of a range. E.g.:
=PROMEDIO(B2:B10) - MAX: returns the highest number in a set. E.g.:
=MAX(B2:B10) - MIN: returns the lowest value. E.g.:
=MIN(B2:B10) - WHETHER: evaluates a condition and returns a result depending on whether it is met. E.g.:
=SI(A1>5,"Aprobado","Suspenso") - YES.ERROR: avoids displaying errors like #DIV/0! and allows you to define an alternative value. E.g.:
=SI.ERROR(A1/B1,"Error")
Counting Functions in Excel
Counting cells in Excel is a common practice. You can do it in several ways:
- COUNT: count cells with numbers.
=CONTAR(A1:A10) - WILL COUNT: counts cells that are not empty.
=CONTARA(A1:A10) - COUNTBLANK: counts empty cells.
=COUNTBLANK(A1:A10) - COUNTIF: counts cells based on a condition.
=CONTAR.SI(A1:A10, ">=10") - COUNTIFS: has multiple conditions.
=CONTAR.SI.CONJUNTO(A1:A10, ">=10", B1:B10, "<5")
Date and time functions
Working with dates can be a nightmare if you don't know these functions:
- TODAY: returns the current date.
=HOY() - NOW: returns the current date and time.
=AHORA() - DAY: extracts the day from a date.
=DIA(A1) - MONTH: extracts the month from a date.
=MES(A1) - YEAR: extracts the year.
=AÑO(A1) - DAYS: calculates the number of days between two dates.
=DIAS(B2,A2) - WEEKDAY: returns the day of the week as a number. Monday = 1, etc.
=DIASEM(A1,2)
Text functions: manipulate strings like an expert
When working with databases or lists of names, products, or addresses, knowing how to manipulate text is essential. Excel offers very powerful functions for cleaning, modifying and combining text:
- CONCATENATE: joins several texts.
=CONCATENAR(A1," ",B1) - SPACES: eliminate unnecessary spaces.
=ESPACIOS(A1) - FIND: find the position of a word.
=ENCONTRAR("@",A1) - EXTRACTS: extracts text from a cell.
=EXTRAE(A1,2,5) - LEFT: returns the first characters.
=IZQUIERDA(A1,3) - RIGHT: returns the last characters.
=DERECHA(A1,3)
A very useful one for combined emails and names is:
=IZQUIERDA(A1,ENCONTRAR(" ",A1)-1), which would return the first name of a value like "Pedro López".
Searching and retrieving data: essential functions
When dealing with lists of products, employees, or customers, these features are a lifesaver:
- VLOOKUP: searches the first column of a range and returns a related value in another column. E.g.:
=BUSCARV("Pepe",A2:C10,2,FALSO) - LOOKUP: modern and more flexible version of VLOOKUP.
- INDEX: returns the value of a cell based on its relative position.
=INDICE(A2:C5,2,3) - COINCIDE: returns the position of a value.
=COINCIDIR("Mesa",A2:A10,0) - HYPERLINK: create links.
=HIPERVINCULO("http://google.com","Ir a Google")
Random functions and rounding
- RANDOM.BETWEEN: generates a random number between two values.
=ALEATORIO.ENTRE(1,100) - ROUND OUT: round with decimals.
=REDONDEAR(PI(),2) - ROUND.MINUS: round down.
- ROUND.MORE: round up.
- TRUNCATE: remove all decimals.
These functions are ideal for generating tests, simulations, raffles, or financial calculations with adjusted decimals.
Ultimately, mastering Excel isn't about memorizing hundreds of functions, but rather understanding which ones to use, when, and how to combine them effectively. This guide is your starting point to stop fearing Excel and start using it as the professional tool it truly is.