The most important Excel formulas to start from scratch like a pro

Mastering Excel from scratch is easier than it seems if you know where to start. Many users, even those who use it daily, tend to limit themselves to the most basic formulas, thus missing out on much of this program's powerful potential. Learning essential Excel formulas will not only save you time and errors, but It will allow you to analyze, organize and visualize data like a true professional..

In this guide, you'll discover the most important formulas, from the simplest to the most advanced operations that will prepare you for any job challenge. The goal is for you, whether you're a beginner or already experienced, to be able to handle any spreadsheet with ease, judgment, and efficiency. In addition, Helpful tips, practical tricks, and resources to make your learning as smooth as possible.

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

A formula in Excel is a sequence that performs calculations, processes data, or returns values ​​automatically within a spreadsheet. All formulas begin with the equal sign (=), which tells Excel to calculate the result based on the included values, references, and operators.

Formulas allow you to perform everything from simple mathematical operations to searching, data analysis, financial calculations, date manipulation, and much more. Their versatility makes them essential in any environment, whether academic, business or for personal use.

For example, if you write =A1+B1 in a cell, Excel will add the values ​​contained in cells A1 and B1. But the possibilities go much further, allowing you to build complex operations with multiple conditions, dynamic references, nested functions, and automatic calculations of large volumes of data.

Differences between formulas and functions in Excel

In Excel, the terms formula and function are often used interchangeably, but they are not exactly the same. Understanding the difference will help you create and combine more sophisticated operations:

• Formula: It consists of a manually created expression using values, cell references and mathematical operators (+, -, *, /, ^). Example: =A1+B2-3
• Function: It's a predefined operation in Excel that simplifies complex calculations. Each function has a specific syntax and arguments, which are enclosed in parentheses. Example: =SUM(A1:A10)

In practice, formulas can include functions, and these can be combined to create increasingly advanced operations..

Elements of a formula in Excel

Excel formulas are made up of several key elements that you should know:

• Constants: They are fixed values, like numbers (50) or texts (“January”).
• References: Indicates the cell or range of cells where Excel will search for the values ​​to use. Example: A1, B2:B8.
• Operators: Mathematical signs to add (+), subtract (-), multiply (*), divide (/), enhance (^), concatenate (&) and compare (=, >, <, <>, etc.).
• Functions: Predefined keywords that perform specific calculations (SUM, AVERAGE, VLOOKUP, IF, etc.).

Learning to combine these elements is the key to building formulas that really make a difference in your daily work..

Types of formulas in Excel: from basic to advanced

There are multiple types of formulas in Excel, from the simplest to advanced tools for professional analysis:

• Simple formulas: Direct addition, subtraction, multiplication and division.
• Formulas with references: Operations that use data located in different cells.
• Formulas with functions: Automatic sums, averages, searches, counts, rounding, etc.
• Conditional formulas: They perform calculations depending on whether a condition is met (IF, AND, OR, NOT functions).
• Formulas with multiple criteria: Operations with multiple requirements using SUMIFS, COUNTIFS.
• Financial and statistical formulas: For loan calculations, interest, trends, etc.
• Text manipulation formulas: To join, extract or clean text strings.
• Date and time formulas: To calculate time periods, date differences, or extract elements such as the month or year.
• Matrix formulas: Calculations on massive data sets.

Basic mathematical operations in Excel

Every Excel user should master the most basic arithmetic operations, as they are the foundation upon which more complex formulas are built. You can enter them directly into a cell or combine references and functions.

• Add: =A1+B1 add the values ​​in cells A1 and B1.
• Subtract: =A2-B2 subtract the value of B2 from that of A2.
• Multiply: =A3*B3 multiply the values ​​of A3 and B3.
• Split: =A4/B4 divide the value of A4 by that of B4.
• Enhance: =A5^2 raise the value of A5 to the square.

Excel respects the order of mathematical operations: first powers, then multiplications and divisions, and finally addition and subtraction. If you need to control the order, use parentheses: = (A1+B1)*C1.

Essential Excel formulas and their applications

Below are the most important formulas you should know to start working like a true Excel professional:

SUMA: The formula par excellence

SUM is the most commonly used function and allows you to quickly and easily add the values ​​in a range or multiple non-contiguous cells.

• Example: =SUMA(A1:A10) will sum all values ​​in the range A1 to A10.
• Additionally, you can add specific cells: =SUM(A1,B2,B3)
• The AutoSum feature, located on the Home tab, makes this task easy.

AVERAGE: Calculate the average of your data

The function AVERAGE Returns the average value of a group of numbers, ideal for analyzing trends, detecting sales averages, grades, prices, etc.

• Example: =PROMEDIO(B1:B12) will calculate the average of the values ​​between B1 and B12.
• For conditional averages there are AVERAGE.IF y AVERAGE IF JOINT.

COUNT, COUNTIF, and COUNTIF: The art of counting data

With Excel, you can count both numerical and text data, empty cells or cells with content, depending on the function chosen.

• COUNT: Counts only cells that contain numbers. =CONTAR(C1:C10)
• CONTRAA: Counts all cells with data (numbers or text), ignoring only empty cells. =CONTARA(C1:C10)
• COUNTIF: Counts cells that meet a specific criterion. =CONTAR.SI(D1:D20,"Aprobado")
• COUNTIFS: Allows multiple conditions. =CONTAR.SI.CONJUNTO(A1:A10,">10",B1:B10,"Hombre")
• COUNTBLANK: Counts empty cells in a range. =COUNTBLANK(A2:D2)

MIN and MAX: Find extremes in your data

The MIN and MAX functions quickly identify the lowest or highest value in a range, useful for price analysis, notes, sales peaks, etc.

• Minimal example: =MIN(A2:A100)
• Maximum example: =MAX(A2:A100)

ROUND, ROUNDUP, and ROUNDDOWN: Precision in calculations

These functions allow you to adjust the result to a specific number of decimal places or round up or down according to your needs.

• ROUND OUT: =REDONDEAR(A1,2) adjusts to two decimal places.
• ROUND.MORE: Always round up. Example: =REDONDEAR.MAS(3.14159,2) → 3.15
• ROUND.MINUS: Always round down. Example: =REDONDEAR.MENOS(3.14159,2) → 3.14

Conditional Formulas: Making Smart Decisions

To run calculations or display different results based on conditions, such as whether someone passed or failed, you must use conditional formulas. The most commonly used logical functions are:

• YEAH: Evaluates a condition and returns one value if it is true and another if it is false.
  Example: =SI(B2>=60, "Aprobado", "Reprobado")
• Y: Returns TRUE only if all conditions are met.
  Example: =SI(Y(A1>10, B1<50), "Ok", "No")
• O: Returns TRUE if at least one condition is met.
  Example: =SI(O(A1>100, B1>100), "Revisar", "Correcto")
• NO: Inverts the logical value.
  Example: =SI(NO(A1=15), "Diferente de 15", "Es 15")
• IF ERROR: Allows you to display an alternative value if a formula fails.
  Example: =SI.ERROR(A2/B2, "No calculable")

Search and extract information: VLOOKUP, HLOOKUP, INDEX, MATCH and XLOOKUP

One of Excel's most powerful features is the ability to search for data in tables and return related information. If you want to dig deeper into these features, learn more with this guide on how to use Excel formulas.

• VLOOKUP: Finds a value in the first column of a range and returns the data from another column in the same row. Example: =BUSCARV(102,A2:C10,3,FALSO)
• BUSCARH: Similar to VLOOKUP but acts horizontally with rows.
• INDEX + MATCH: They allow you to search in any direction (not just to the right like VLOOKUP) and offer a lot of flexibility.
  Example: =ÍNDICE(B2:B20, COINCIDIR("Producto",A2:A20,0))
• XLOOKUP: The modern function that replaces VLOOKUP and HLOOKUP, allowing more dynamic and flexible searches in both columns and rows.

SUMIF, SUMIFS, and AVERAGEIFS: Sums and averages with criteria

• SUMIF: Sums the values ​​in the range that meet a condition. Example: =SUMAR.SI(A1:A10,">10")
• SUM IF SET: Allows multiple requirements. =SUMAR.SI.CONJUNTO(C2:C12,B2:B12,">500",D2:D12,"Región1")
• AVERAGEIFS: Calculates the average of values ​​that meet multiple criteria. Example: =AVERAGEIFS(C3:C15, A3:A15, "Manzana", B3:B15, "Sur")

Manipulating text in Excel: CONCAT, CONCATENATE, JOIN, LEFT, RIGHT, MINT, LEN, SPACES, SUBSTITUTE, REPLACE

• CONCAT and CONCATENATE: Combine the contents of several cells into a single cell. Example: =CONCAT(A1, " ", B1).
• JOINERS: Matches texts with a specific delimiter. Example: =UNIRCADENAS(", ", VERDADERO, A1:A3).
• LEFT and RIGHT: Extract the first or last characters from a text. Example: =IZQUIERDA(B2, 3) extracts the first three characters.
• EXTRACT: Extracts a specified number of characters from a position of your choice. Example: =EXTRAE(C3, 2, 5).
• LONG: Returns the length of a text. Example: =LARGO(D4).
• SPACES: Remove unnecessary spaces from a text. Example: =ESPACIOS(E5).
• REPLACE: Replace parts of the text with others. Example: =SUSTITUIR(B15, "Madrid", "Barcelona").
• REPLACE: Replaces characters at specific positions. Example: =REEMPLAZAR("Feliz Navidad", 6, 8, "Hanukkah").

Date and Time Formulas: Organize and Calculate Times Easily

Managing dates and times is essential for any report, plan, or analysis. 

• TODAY: Returns the current date. =HOY()
• NOW: Returns current date and time. =AHORA()
• DAY, MONTH, YEAR: Extract the day, month, or year from a date. Example: =DIA(B2)
• DATE: Creates a date from the supplied year, month, and day. Example: =FECHA(2025,6,10)
• DAYS: Calculate the number of days between two dates. =DIAS(B1,A1)
• IF DATED: Determines the exact difference between two dates in years, months, or days. Example: =SIFECHA(A1,B1,"Y")
• DAY: Returns the day of the week for a given date. Example: =DIASEM(B2,2)

Data analysis tools: Pivot tables, sorting and filters, slicers, charts

To enhance the analysis and visualization of your data, in This article You can find the best AI-powered Excel tools, including pivot tables and interactive charts.

• Pivot tables: They allow you to group, summarize and analyze complex data in a dynamic and visual way.
• Functions like SORT and UNIQUE: They help to extract unique lists or to sort data automatically.
• Graphics: They facilitate the visual interpretation of the results (columns, bars, lines, areas, etc.).
• Segmentations: Visual and interactive filters for pivot tables.

Combining these tools with the formulas described will allow you to obtain relevant information in seconds and make decisions based on solid data.

Essential financial and business formulas

• NPV (Net Present Value): Evaluate investments taking into account the present value of future cash flows.
• IRR (Internal Rate of Return): Calculate the profitability of an investment.
• PAYMENT and PMT: They determine the periodic payment of a loan.
• NOMINAL VALUE/NOMINAL RATE: Conversion between different types of interest rates.
• DIAS360: Useful for financial accounts that use 360-day years.
• FORECAST, TREND: Useful for projections and trend analysis.

Control and debugging: Formulas for handling errors and controlling data

To keep your spreadsheets error-free and improve the reliability of your calculations, you'll find tips for managing errors in Excel here.

• IF.ERROR / IFERROR: They return an alternative value if a formula fails.
• ERRORS: Indicates whether a cell contains an error (for example, division by zero).
• ISWHITE: Detects if a cell is empty.
• ISNUMBER/TEXT: They indicate the type of data contained in a cell.
• LONG/LONGB: Returns the number of characters in a text, useful for reviewing imported data.
• CELL: Returns information about a specific cell, its format, data type, etc.

Best practices, tricks, and shortcuts to master Excel formulas

• Use keyboard shortcuts: Like Ctrl+C/V for copy/paste, Ctrl+1 for cell format, Ctrl+Shift+% for percentage, Alt+= for quick sum.
• Drag formulas: Use the fill handle (the small square in the corner of the cell) to copy formulas without having to type them by hand.
• Check the references: Make sure you use absolute references ($A$1) and relative (A1) correctly, especially when copying formulas.
• Protect your formulas: Lock cells with important formulas to prevent them from being accidentally deleted or changed (Format Cell > Protect > Lock).
• Hide formulas if necessary: You can hide the display of formulas to prevent others from seeing the underlying calculations.
• Clean the data: Use SPACES, SUBSTITUTE, and other functions to clean up information before applying important formulas.

Additional tips to enhance your Excel experience

• Always start your calculations with the simple: Master the basics before moving on to advanced tools.
• Learn with practical cases: Create your own spreadsheets to manage expenses, inventory, to-do lists, or note-taking.
• Explore free resources: There are a multitude of online courses, templates, forums, and tutorials that can help you every day.
• Stay up to date with the latest news: Recent versions incorporate functions such as XLOOKUP, dynamic matrices or Power Query for massive data analysis.
• Use advanced segmentations and filters: For large volumes of information, take advantage of data segmentation and filters to see only what's relevant.
• Apply conditional formatting: Automatically highlights important values, trends, or errors to visualize critical data.

This advanced knowledge and practical tips will help you better manage your daily work with Excel, automating tasks, reducing errors, and generating more accurate reports. Constant practice and exploring new features will grow your skills exponentially, positioning you as an expert in the field. We hope you now know the key Excel formulas to help you start from scratch and become a professional.