How to Master Excel's Basic Formulas: A Complete and Practical Guide

Excel has become an essential tool in both the work, academic and domestic environments., allowing any user to perform everything from simple mathematical operations to complex calculations and task automation. Mastering basic Excel formulas can make the difference between working efficiently or wasting hours manually managing data. Entering this world can seem intimidating at first, but with the right information and practical examples, the process is much simpler and more accessible than often thought.

In this article you will discover everything you need to know to understand and apply basic Excel formulas and functions.. Here you will find Step-by-step explanations, visual examples, helpful tips and tricks So you can become an independent expert in solving any calculation, from basic mathematical operations to advanced searches or managing dates and texts. Whether you're starting from scratch or already have experience, the content has been designed to be clear, organized, practical, and as comprehensive as possible, gathering valuable information from all the most relevant sources on the topic.

What is a formula in Excel and what is it used for?

The first thing to be clear about is the formula concept in Excel. A formula in Excel is a mathematical equation that allows you to calculate new values ​​from existing data in the spreadsheet. Like a digital calculator, you enter operations and obtain results immediately, displaying the value in the cell where the formula is entered.

Its usefulness is enormous: They automate operations, avoid manual errors, streamline processes, and allow you to process large volumes of data in a matter of seconds. From small household accounts to corporate financial reports, basic Excel formulas save time and effort.

Elements and structure of basic formulas in Excel

Each formula in Excel must always begin with the equal sign (=)From there, you can combine different elements:

• Cell References: indicate the position of the data to be used, for example, A1, B3, C7.
• Constants: Fixed numbers or directly written texts (e.g. 5, “Hello”).
• Operators: symbols that indicate the operation to be performed, such as + to add, – to subtract, * to multiply, / to divide, ^ for powers.
• Showtimes:: More complex operations predefined by Excel, such as SUM, AVERAGE, IF, VLOOKUP, etc.

An example of a formula could be =A2+B2 (adds the value of cells A2 and B2), while a function would be =SUMA(A2:B2) (add the range of cells from A2 to B2).

Differences between formula and function in Excel

A common mistake is to confuse formulas and functions. The main difference is that a formula is the equation that you create by combining operators, references and constants.as the =B2-B3 o =5+7*2. On the other hand, A function is a preconfigured operation, such as SUM, AVERAGE, MAX, and is used within basic formulas in Excel to simplify longer calculations. For example, =SUMA(A1:A10) Quickly add the values ​​in multiple cells without having to type them one by one.

Functions can be part of a formula more complex: =SUMA(A1:A10)*2 will multiply the result of the sum of the range by two.

How to Create a Basic Formula in Excel: Step by Step

1. Select the cell where you want the result to be displayed.
2. Write the equal sign (=) to indicate that you are going to enter a formula.
3. Enter cell references or the constant numbers and operators depending on the operation you want to perform, for example, =A1+B1.
4. Hit enter and you will immediately see the calculated result.

For example, if you want to add the contents of cells A2 and B2, in the cell where you want the result, type =A2+B2If A2 contains 3 and B2 contains 5, the result will be 8.

Basic operators in basic Excel formulas

• Sum (+): adds the values ​​(for example, =A1+A2+A3).
• Subtraction (-): subtracts the value on the right from the value on the left (=B2-B3).
• Multiplication (*): multiply the values ​​(=C2*D2).
• Division (/): divides the value on the left by the value on the right (=E2/F2).
• Power (^): raises a number to the power of another (=A1^2).

Remember that Excel follows the logical order of mathematical operations: powers first, multiplication and division next, and finally addition and subtraction. You can use parentheses to change the order, e.g. =(A1+B1)*C1.

Most important and used functions in Excel

Excel has a vast repertoire of functions ranging from simple mathematical calculations to advanced searches, text manipulation, and date management.

Addition and AutoSum

SUM It is one of the most classic and widely used functions. It allows you to add from two cells to entire ranges of data. Example: =SUMA(A1:A10) Add all the values ​​in the cells between A1 and A10. You can also add individual cells separated by commas: =SUMA(A2,B4,C7).

To add quickly without writing the function manually, use the AutoSum tool on the Home tab. Select a cell next to the column or row of numbers, click AutoSum, and press Enter. You'll see the result automatically.

Resta

Although there is no SUBTRACT function, it is sufficient to use the - operator. Example: =A2-B3You can also subtract multiple values: =A1-B1-C1.

Multiplication

It is done with the asterisk *. If you have the values ​​in cells B2 and B3, the formula will be =B2*B3There is also the function PRODUCT to multiply ranges: =PRODUCTO(A1:A4).

Division

The slash / is used. For example, to divide the value in cell C2 by the value in C3, type =C2/C3. There is no specific function for splitting.

Powers

To raise a number to a power, use the circumflex accent ^. For example, =A1^3 will raise the contents of A1 to the power of 3.

Average

The function AVERAGE calculates the arithmetic mean of a set of cells: =PROMEDIO(B2:B10). Very useful for, for example, calculating the average grade of students. If you want to calculate the average only of cells that meet a criterion, there are functions such as AVERAGE.IF y AVERAGE IF JOINT.

Counting and counting specific values

• TELL: Counts only cells with numbers. Example: =CONTAR(A1:A10).
• WILL COUNT: Counts all non-empty cells, regardless of whether they contain text or numbers. Example: =CONTARA(A1:C10).
• COUNT YES: Counts the cells in a range that meet a condition. Example: =CONTAR.SI(B2:B10,"Aprobado") Count how many times the word “Passed” appears in that range.

Date and time functions

• NOW(): Returns the current date and time, and updates automatically each time you open the file.
• TODAY(): returns the current date without the time.
• DAY, MES y YEAR: extract the day, month, and year from a given date. Example: =DIA(A1) if cell A1 contains a full date.
• DAYS: calculates the days difference between two dates. =DIAS("2/2/2018", B2).
• IF DATED: Calculates the number of days, months, or years between two dates. Example: =SIFECHA(A1, B1, "D") to calculate days.
• DAYSEM: Indicates the day of the week for a date, and you can choose the start day (Monday, Tuesday, etc.). Example: =DIASEM(A1,2).

These functions allow you to monitor and analyze temporal information in a very simple and effective way.

Statistical functions: MAX and MIN

• MAX: returns the maximum value from a set of cells: =MAX(A1:A100).
• MIN: returns the minimum value: =MIN(A1:A100).

Logical and conditional functions

• SI: allows you to return a result based on whether a logical condition is met. Example: =SI(B2>=5,"APROBADO","SUSPENDIDO") displays “PASS” if B2 is greater than or equal to 5.
• Y: returns TRUE only if all arguments are TRUE. Example: =Y(A1>0, A1<100).
• O: returns TRUE if at least one argument is TRUE. Example: =O(A1>10, B1<5).
• DO NOT: inverts the logical result. Example: =NO(A1=5).
• YES.ERROR: allows you to handle errors by returning an alternative value. Example: =SI.ERROR(A2/B2,"División por cero").

Search and query data

• VLOOKUP: Searches for a value in the first column of a table and returns the value from another column in the same row. Example: =BUSCARV("Nombre", A2:D10, 3, FALSO).
• HLOOKUP: like VLOOKUP but searches horizontally.

Text functions

• CONCATENATE: joins two or more text strings: =CONCATENAR(A1,B1). In recent versions it is used CONCAT o UNICHAINS.
• SPACES: removes extra spaces (leaves only one between words).
• UPPERCASE: converts the text to uppercase.
• MINUSC: converts text to lowercase.
• PROPER: capitalizes the first letter of each word, useful for proper nouns.
• LONG: returns the number of characters in a text.
• REPLACE: replaces part of the text with another text.
• DISCOVER MORE: finds the position of one text within another.

Advanced and useful functions

• TRANSPOSE: changes rows to columns and vice versa, useful if you need to reorganize a table.
• Hyperlink: Converts a web address into a link or creates a link with custom text. Example: =HIPERVINCULO("http://www.google.com", "Visita Google").
• RANDOM.BETWEEN: generates a random number between two values. Example: =ALEATORIO.ENTRE(1, 100).
• REPLACE: inserts or replaces part of a text string with another, being able to define the position and the number of characters to delete.

Types of basic formulas in Excel according to their usefulness

Excel formulas can be classified according to their primary use:

• Simple: basic mathematical operations such as addition, subtraction, multiplication and division (=A1+B1).
• Referential: those that operate with data from other cells or even different sheets (=Hoja2!A1+Hoja3!B2).
• With functions: they use predefined functions or combine several functions (=SUMA(A2:A6)-MIN(B2:B6)).
• Conditionals: incorporate conditional logic to return results based on criteria (=SI(A1>100,"Alto","Bajo")).

How to edit, copy, and drag basic formulas in Excel

Editing a formula is as simple as double-click on the cell that contains it and modify the content directly, or select the cell and edit it in the formula bar.

Drag a formula speeds up work when you need to apply the same operation to multiple rows or columns:

• Click on the cell with the desired formula.
• Place the cursor in the lower right corner of the cell (a + sign will appear).
• Drag the cursor down or sideways to the cells where you want to copy the formula.

Excel will automatically adjust cell references for each row or column. If you don't want them to change, use absolute references with the $ symbol (for example, =$A$1+B2).

Show, hide, and lock formulas in Excel

Show formulasTo see all the formulas in a sheet, go to the Formulas tab and select Show Formulas. This way, you can check for errors in any formula.

Hide formulas: Useful if you don't want others to see how you arrived at a result. Select the cell, right-click, choose "Format Cell," go to the "Protect" tab, and select "Hidden." Remember, you'll need to protect the sheet so the formulas aren't actually displayed.

Lock formulasTo prevent accidental deletion or modification, select the cells, go to Format Cell, turn on Lock, and then password-protect the sheet from the Review tab.

Delete formulas without losing the result

Sometimes you prefer to leave the values ​​alone and delete the formula to avoid future changes. To do this:

• Select the cell with the formula.
• Copy the cell (Ctrl + C).
• Paste only the value (Home > Paste > Paste Values).

In this way, the result remains fixed and the formula disappears.

Managing common errors in Excel formulas

It's easy to make mistakes when creating formulas, but fortunately Excel offers features to manage them:

• YES.ERROR: Allows a custom message or alternative value to be displayed in the event of an error (e.g., division by zero).
• The most common errors you may encounter are: #DIV/0! (division by zero), #WORTH! (incorrect data type), #REF! (reference removed).

Tricks and shortcuts for working with formulas in Excel

Excel is full of little tricks and keyboard shortcuts that can save you time:

• Quick AutoSum: Select a cell under a column of numbers and press Alt + = to automatically insert the sum.
• CTRL + C / CTRL + V: Quickly copy and paste cells or formulas.
• CTRL + spacebar: selects the current column.
• CTRL + 1: Opens the cell formatting menu to lock or protect formulas.
• CTRL+ALT+V: Access 'Paste Special', where you can choose to paste only values, only formulas, only formatting, etc.
• Shift + F3: Quickly insert a function.
• CTRL + L: : quickly find and replace data.
• CTRL + ;: inserts the current date.
• CTRL + :: inserts the current time.

Practical examples of basic formulas in Excel

To make it clear how to apply what you've learned, here are some common examples:

Operations	Formula in Excel	Description
Amount	=A1+A2	Add the values ​​of A1 and A2
Resta	=B2-B3	Subtract B3 from B2
Multiplication	=C2*C3	Multiply C2 by C3
Division	=D2/D3	Divide D2 by D3
Power	=A1^2	Square A1
Average	=AVERAGE(E2:E10)	Calculate the average of E2 to E10
Maximum event value	=MAX(A2:A10)	Returns the highest value between A2 and A10
Minimum event value	= MIN (B2: B10)	Returns the lowest value between B2 and B10
Count non-empty cells	=COUNTIF(A2:A20)	Counts cells with any content
Simple condition	=IF(C2>=5,»OK»,»Review»)	Indicate if C2 is greater than or equal to 5
Search for value	=VLOOKUP(G2,$A$1:$C$100,3,FALSE)	Find the value of G2 in the table and display the value in column 3
Concatenate texts	=CONCATENATE(A1,» «,B1)	Join the values ​​of A1 and B1 with a space between them
Remove extra spaces	=SPACES(C1)	Remove extra spaces in the C1 text
Get day of the week	=WEEKDAY("20/01/2024";2)	Calculate the day of the week starting with Monday
Random value	=RANDOM.BETWEEN(1,100)	Returns a random number between 1 and 100

Recommendations for mastering Excel formulas

• Practice oftenThe key is repetition. The more you use formulas, the easier they will be to remember and combine.
• Take advantage of Excel's help: When you type a formula, Excel usually suggests the necessary syntax and arguments.
• don't be afraid to experiment: Try combining several functions into a single formula, but always check the results.
• Use shortcuts and editing tools: you will save a lot of time.
• Manage errors well: Use IFERROR and check paths and references.

Advanced tips and tricks for intermediate and expert users

• Use range names: Assigns names to cell ranges for clarity in formulas (=SUMA(Ventas2024)).
• Apply conditional formatting with formulas: Automatically highlights cells that meet certain criteria.
• Use mixed references: Mix absolute and relative references depending on whether you need certain values ​​to remain fixed when copying formulas.
• Take advantage of nested functions: insert a function inside another for more advanced operations, for example, =SI(PROMEDIO(A2:A10)>5,"Bien","Mejorar").
• Test data validation: Limits the values ​​that can be entered into a cell using custom formulas.
• Explore the use of macros: if you need to automate even more complex and repetitive calculations.

Most common mistakes when using formulas and how to avoid them

• Confusing relative and absolute references: remember to use $ to fix rows or columns.
• Forgetting the equal sign (=) at the beginning of the formula.
• Misaligned parentheses: Check that each opening has its closing, especially in nested formulas.
• Misspelled cell ranges: Make sure to separate the beginnings and ends of ranges well with :.
• Syntax errors in functions: Consult Excel help to see the correct structure of each function.
• Division by zero: Use IFERROR to avoid visible error messages.

How to combine multiple functions and formulas in Excel

Many times, the power of Excel lies in combine multiple functions into a single formula. For example, you can calculate the average of only the values ​​that meet a condition with =PROMEDIO.SI(A2:A10,">5") or display a custom message when no results are found using =SI(CONTAR.SI(A2:A10,"Pepe")=0,"No encontrado",CONTAR.SI(A2:A10,"Pepe")).

You can also use logical functions inside other functions, such as =SI(Y(A2>=5, B2<10),"En rango","Fuera de rango")The key is to understand how each function fits together and experiment with different scenarios.

Advanced text manipulation with Excel formulas

In addition to calculations, Excel allows modify and analyze texts using specific formulas:

• UPPERCASE: converts to uppercase.
• MINUSC: converts to lowercase.
• PROPER: capitalizes the first letter of each word.
• LONG: Counts the number of characters in a text.
• SPACES: Removes redundant spaces.
• REPLACE: replaces parts of the text.
• DISCOVER MORE: finds the position of a character or word within another text, useful for validations.
• CONCATENATE / CONCAT / UNICHAINS: to join several text fragments or cell references.

These functions are especially useful in databases with names, addresses, or any type of textual information.

Date and Time Formulas: Common Examples

• Calculate the day of the week: =DIASEM(A1,2) if A1 contains a date.
• Add days to a date: =A1+7 add a week to the A1 date.
• Subtract two dates: =B1-A1 returns the days between the two dates.
• Get day, month or year: =DIA(A1), =MES(A1), =AÑO(A1).
• Calculate seniority: =SIFECHA(A1,HOY(),"Y") returns the years between the date A1 and today.

Conditional Formula Applications in Excel

The conditional formulas allow you to automate decisions. For example, you can automatically mark students whose average is less than 5 as “Review” with =SI(PROMEDIO(B2:H2)<5,"Revisar","Aprobado").

Another possibility is highlight cells with conditional formatting using custom formulas to flag out-of-range values, duplicates, and more. Share the information so more users know how to use these basic Excel formulas..