Learn essential Excel formulas to get off to a good start

  • Excel formulas allow you to automate complex calculations and data analysis.
  • There are basic, statistical, logical, date, text, and search functions, each with key applications.
  • The combined use of functions such as VLOOKUP, IF, AVERAGEIF, or SUMIFS increases efficiency.
  • Mastering these tools improves productivity in work, academic, and personal environments.
Daniel Terrasa

6 minutes

basic Excel formulas

Microsoft Excel It's much more than a simple spreadsheet: it's a powerful data analysis, task automation, and decision-making tool for businesses, students, and professionals in any sector. Know the essential Excel formulas It is essential to get the most out of this tool.

Esta short guide It brings together, in a detailed and understandable way, the most important formulas that anyone who uses Excel should know. From basic operations to advanced analytical, text, date, and logic functions, we'll explore each one with real-life examples that will make them easy to understand and apply immediately.

Basic Mathematical Functions in Excel

We start with the simplest operations that are often performed manually, but that Excel allows you to execute efficiently:

  • SUM(A1:A10): sums the values ​​in the range from A1 to A10.
  • =A2 – A3: Subtracts the values ​​from A3 to A2.
  • =A1 * A3: Multiply the contents of A1 by A3.
  • =A2 / C2: divide what is in A2 by the value of C2.

These operations respect the standard mathematical rules and allow parentheses to be used to establish the order of calculation, as in: = (A1 + C2) * C7 / 10.

Essential statistical functions

Excel offers a good number of functions for performing basic statistical calculations:

  • AVERAGE(range): returns the arithmetic mean. Example: =PROMEDIO(B1:B12).
  • MAX(range) y MIN(range): return the highest or lowest value, respectively. Example: =MAX(A2:B5).
  • COUNT(range): Counts how many cells contain numbers.
  • COUNTA(range): Counts all non-empty cells, even if they contain text or dates.

These functions are essential for analyzing metrics such as sales, grades, survey results, etc.

excel

Conditional Functions: Make Decisions with Excel

The conditional functions They allow Excel to perform different actions depending on whether a certain condition is met or not:

  • IF(condition, value_if_true, value_if_false): is the logical function par excellence. Example: =SI(B2>5, "APROBADO", "SUSPENSO").
  • IFERROR(formula, value_if_error): Avoid errors like '#DIV/0!' by returning an alternative value. Example: =SI.ERROR(A2/B2, "No válido").
  • YES SET: evaluates multiple conditions successively. Example: =SI.CONJUNTO(A1>90,"Excelente", A1>70,"Bueno", A1>50,"Suficiente").
  • I DON'T: evaluate multiple logical conditions. =Y(A1>0, A2<5) returns TRUE only if both are true.

These functions are key to Create dynamic reports, segment data, or create automatic evaluation flows..

Functions of searching and referencing

One of Excel's strengths is its ability to search and retrieve data from huge databases. This is where the following come into play:

  • VLOOKUP(lookup_value, table, column, ): Searches for a value in the first column and returns the associated data in another column. Example: =BUSCARV(102, A2:C10, 3, FALSO).
  • INDEX(array, row, column): Retrieves a value based on its position in a table. More flexible than VLOOKUP.
  • MATCH(value, range, type): Returns the position of the value within the range. Excel becomes more powerful when used with INDEX.
  • XLOOKUP(value, search_array, result_array): modern alternative to VLOOKUP with more capabilities.

These functions allow you to Connect tables, relate information, and automate tasks such as reports or dashboards..

Excel

Formulas with multiple conditions (advanced criteria)

When more specific conditions need to be applied, a new family of functions appears:

  • ADD IF: adds a rank if a single criterion is met.
  • SUM.IF.SET: sum only when multiple conditions are met. Example: =SUMAR.SI.CONJUNTO(D2:D12, B2:B12, "Jane", C2:C12, ">200").
  • COUNT YES: Counts how many cells meet the condition. Example: =CONTAR.SI(A1:A10, ">50").
  • COUNT IF SET: like the previous one, but with several conditions at the same time.
  • AVERAGEIFS: calculates the average of values ​​that meet all specified criteria.

These functions are essential to work with databases, sales, customers, products, or any set of records.

Date and time functions

Excel also allows you to perform date-based calculations and analysis:

  • NOW(): Returns the current date and time in real time.
  • TODAY(): Returns only the current date.
  • DAYS(end_date, start_date): calculates the number of days between two dates.
  • WORKDAYS(date1, date2): weekdays between two dates (excluding weekends).
  • WEEKDAY(date, type): returns the day of the week in number (Monday, Tuesday…).

These formulas allow you calculate deadlines, automate due dates, generate temporary reports or control calendars.

Text Functions: Easily Manipulate Strings

Excel also allows you to work with texts in a flexible way:

  • CONCATENATE o CONCAT: join contents of several cells. =CONCAT(A1, " ", B1).
  • LEFT / RIGHT: extract characters from the left or right of a text. Example: =IZQUIERDA(A1,5).
  • EXTRACT(text, start_position, num_characters): extracts fragments of the text.
  • SPACES(text): removes unnecessary spaces.
  • REPLACE: changes part of a text to a new one.
  • REPLACE: replaces all occurrences of one fragment with another.

These functions are essential when working with data that contains errors, word combinations, codes, or non-standard formats.

What improvement does Microsoft now offer us in the copy and paste function in Excel?

Functions to detect errors and validate cells

Excel allows you to analyze the content to see if there are errors, empty cells, or specific values:

  • YES.ERROR: already explained, returns alternative value if there is an error.
  • ISERROR(value): returns TRUE if the cell contains an error.
  • ISBLOND(value): TRUE if the cell is empty.
  • ISTEXT / ISNUMBER: useful to know if there is text or a number.

They are key to validating forms, preventing errors and offering understandable messages to the user..

Advanced analysis and statistics functions

  • FREQUENCY: Calculates how many values ​​there are in defined intervals.
  • CORREL: correlation between two sets of data.
  • MEAN, MEDIAN, MODE, VARIANCE, STDEV…: classical statistics for descriptive analysis.

This group is perfect for those working with big data, studies, surveys or financial analysis..

Specific business and finance functions

  • VNAO: Net Present Value for investments.
  • TIR: Internal Rate of Return.
  • PAYMENT: calculates fixed loan installments.
  • PMT: equivalent function for periodic payments.
  • NPV: net present value, key to evaluating projects.

These financial formulas are vital in accounting, risk analysis, investments or profitability evaluation.

Formulas for order, classification and organization

  • RANK: position occupied by a value within a set.
  • SORT(range): Automatically arranges cells from smallest to largest or vice versa.
  • UNIQUE: Extracts unique values ​​without duplicates from a list.

This allows Create clean lists, sort results, and prepare pivot tables easily.

Automation and Visualization: Pivot Tables and Charts

Although they are not formulas per se, Pivot tables and charts are an essential part of Excel analysis.With them you can:

  • Summarize large amounts of data.
  • Apply advanced filters without affecting the original base.
  • Visualize results with dynamic, customizable charts.

Excel lets you combine filters, sort values, apply conditional formatting, and more, all from a visual, analysis-oriented interface.

Mastering essential Excel formulas not only saves you time, but also increases your analytical skills, transparency in your reports, and confidence in your results. Regardless of your industry or experience level, these functions will allow you to: work with data efficiently, flexibly and professionally. The next step is no longer learning more formulas, but understanding when and how to apply them in combination to solve the real-world challenges you encounter every day in your spreadsheets.