DashCoreHubExcel formulas Cheat Sheet
60 essentials with syntax and working examples. Search or filter by group.
XLOOKUP (ES: BUSCARX)
Finds a value in one column and returns the matching value from another. Exact match by default, works left or right, and lets you set a not-found message.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])=XLOOKUP(A2, Products[product_id], Products[unit_price], "Not found")XMATCH (ES: COINCIDIRX)
Returns the position of a value inside a list. Exact match by default; pair it with INDEX or CHOOSECOLS.
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])=XMATCH("Dairy", {"Beverages","Snacks","Dairy","Frozen"})VLOOKUP (ES: BUSCARV)
Classic lookup: searches the first column of a range and returns a value from a column to the right. Always use FALSE for an exact match.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])=VLOOKUP(A2, Products, 6, FALSE)INDEX (ES: INDICE)
Returns the value at a given row (and column) position of a range.
=INDEX(array, row_num, [column_num])=INDEX(Products[unit_price], 5)MATCH (ES: COINCIDIR)
Returns the position of a value in a range. Use 0 for exact match; combined with INDEX it replaces VLOOKUP in older Excel versions.
=MATCH(lookup_value, lookup_array, [match_type])=INDEX(Products[unit_price], MATCH(A2, Products[product_id], 0))CHOOSECOLS (ES: ELEGIRCOLS)
Returns only the columns you list, by position. Negative numbers count from the right.
=CHOOSECOLS(array, col_num1, [col_num2], ...)=CHOOSECOLS(Sales, 3, 12)SUM (ES: SUMA)
Adds numbers, cells or whole columns.
=SUM(number1, [number2], ...)=SUM(Sales[net_sales])SUMIFS (ES: SUMAR.SI.CONJUNTO)
Adds values that meet one or more conditions. The range to sum comes first.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)=SUMIFS(Sales[net_sales], Sales[region], "South", Sales[channel], "Online")COUNTIFS (ES: CONTAR.SI.CONJUNTO)
Counts rows that meet one or more conditions.
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)=COUNTIFS(Sales[category], "Dairy", Sales[discount], ">0")AVERAGEIFS (ES: PROMEDIO.SI.CONJUNTO)
Averages values that meet one or more conditions.
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)=AVERAGEIFS(Sales[net_sales], Sales[segment], "Family")MAXIFS (ES: MAX.SI.CONJUNTO)
Returns the largest value among rows that meet the conditions. MINIFS works the same way for the smallest.
=MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)=MAXIFS(Sales[net_sales], Sales[store_name], "Sunshine Tampa")SUMPRODUCT (ES: SUMAPRODUCTO)
Multiplies matching items of arrays and adds the results. Handy for weighted totals without a helper column.
=SUMPRODUCT(array1, [array2], ...)=SUMPRODUCT(Sales[quantity], Sales[unit_price], 1 - Sales[discount])ROUND (ES: REDONDEAR)
Rounds a number to the number of decimals you choose. Use ROUNDUP/ROUNDDOWN to force a direction.
=ROUND(number, num_digits)=ROUND([@quantity] * [@unit_price] * (1 - [@discount]), 2)SUBTOTAL (ES: SUBTOTALES)
Calculates a total that respects filters. 109 = SUM ignoring hidden rows, 101 = AVERAGE, 103 = COUNTA.
=SUBTOTAL(function_num, ref1, [ref2], ...)=SUBTOTAL(109, Sales[net_sales])COUNTA (ES: CONTARA)
Counts non-empty cells (text or numbers). COUNT counts numbers only.
=COUNTA(value1, [value2], ...)=COUNTA(UNIQUE(Sales[order_id]))MOD (ES: RESIDUO)
Returns the remainder after a division. Common for flags like even/odd rows.
=MOD(number, divisor)=IF(MOD([@order_id], 2) = 0, "Even", "Odd")IF (ES: SI)
Returns one value if a condition is true and another if it is false.
=IF(logical_test, value_if_true, [value_if_false])=IF([@discount] > 0, "Discounted", "Full price")IFS (ES: SI.CONJUNTO)
Checks several conditions in order and returns the value for the first true one. End with TRUE as a catch-all.
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)=IFS([@net_sales] >= 50, "High", [@net_sales] >= 20, "Medium", TRUE, "Low")SWITCH (ES: CAMBIAR)
Compares one value against a list of options and returns the matching result, with an optional default.
=SWITCH(expression, value1, result1, [value2, result2], ..., [default])=SWITCH([@region], "North", "N", "South", "S", "West", "W", "Central", "C", "Other")IFERROR (ES: SI.ERROR)
Returns a fallback value when a formula produces any error (#N/A, #DIV/0!, etc.).
=IFERROR(value, value_if_error)=IFERROR([@net_sales] / [@quantity], 0)IFNA (ES: SI.ND)
Like IFERROR but only catches #N/A, so other errors stay visible.
=IFNA(value, value_if_na)=IFNA(VLOOKUP(A2, Products, 6, FALSE), "Missing")AND (ES: Y)
Returns TRUE only if every condition is true. Usually used inside IF.
=AND(logical1, [logical2], ...)=IF(AND([@channel] = "Online", [@net_sales] > 30), "Big online order", "")OR (ES: O)
Returns TRUE if at least one condition is true.
=OR(logical1, [logical2], ...)=IF(OR([@category] = "Dairy", [@category] = "Frozen"), "Cold chain", "Shelf")TRIM (ES: ESPACIOS / SUPRESP)
Removes extra spaces, leaving single spaces between words.
=TRIM(text)=TRIM([@store_name])PROPER (ES: NOMPROPIO)
Capitalizes the first letter of each word. Great for fixing UPPERCASE names.
=PROPER(text)=PROPER(TRIM([@store_name]))UPPER (ES: MAYUSC)
Converts text to uppercase. LOWER converts to lowercase.
=UPPER(text)=UPPER([@region])SUBSTITUTE (ES: SUSTITUIR)
Replaces specific text with other text, e.g. removing a $ sign.
=SUBSTITUTE(text, old_text, new_text, [instance_num])=SUBSTITUTE([@net_sales], "$", "")VALUE (ES: VALOR)
Converts text that looks like a number into a real number.
=VALUE(text)=VALUE(SUBSTITUTE([@net_sales], "$", ""))LEN (ES: LARGO)
Returns the number of characters in a text, spaces included.
=LEN(text)=LEN([@product_name])LEFT (ES: IZQUIERDA)
Returns the first characters of a text. RIGHT and MID take characters from the end or the middle.
=LEFT(text, [num_chars])=LEFT([@category], 3)TEXT (ES: TEXTO)
Formats a number or date as text with a format code. Format codes depend on your Excel language (Spanish uses "aaaa" for year).
=TEXT(value, format_text)=TEXT([@order_date], "mmm yyyy")TEXTJOIN (ES: UNIRCADENAS)
Joins text from many cells or a whole range with a delimiter, optionally skipping blanks.
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)=TEXTJOIN(", ", TRUE, UNIQUE(Sales[category]))TEXTBEFORE (ES: TEXTOANTES)
Returns the text before a delimiter. TEXTAFTER returns the text after it. A negative instance counts from the end.
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])=TEXTBEFORE("3 units", " ")TEXTSPLIT (ES: DIVIDIRTEXTO)
Splits text into several cells using a delimiter, spilling across columns (or rows).
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])=TEXTSPLIT("Dairy,Bakery,Produce", ",")TODAY (ES: HOY)
Returns today's date. It updates every time the workbook recalculates.
=TODAY()=TODAY() - [@order_date]DATE (ES: FECHA)
Builds a real date from year, month and day numbers. Safer than typing dates as text.
=DATE(year, month, day)=SUMIFS(Sales[net_sales], Sales[order_date], ">=" & DATE(2025, 1, 1))YEAR (ES: AÑO)
Returns the year of a date. MONTH and DAY return the month and day.
=YEAR(serial_number)=YEAR([@order_date])EOMONTH (ES: FIN.MES)
Returns the last day of the month, a number of months before or after a date. Use 0 for the same month.
=EOMONTH(start_date, months)=EOMONTH([@order_date], 0)EDATE (ES: FECHA.MES)
Returns the same day a number of months before or after a date.
=EDATE(start_date, months)=EDATE(DATE(2025, 1, 15), 3)NETWORKDAYS (ES: DIAS.LAB)
Counts working days (Mon-Fri) between two dates, both included, minus optional holidays.
=NETWORKDAYS(start_date, end_date, [holidays])=NETWORKDAYS(DATE(2025, 1, 1), DATE(2025, 1, 31))WEEKDAY (ES: DIASEM)
Returns the day of the week as a number. Return type 2 makes Monday = 1 and Sunday = 7.
=WEEKDAY(serial_number, [return_type])=IF(WEEKDAY([@order_date], 2) >= 6, "Weekend", "Weekday")DATEDIF (ES: SIFECHA)
Returns the difference between two dates in whole years ("y"), months ("m") or days ("d"). Not listed in autocomplete, but it works.
=DATEDIF(start_date, end_date, unit)=DATEDIF([@signup_date], TODAY(), "m")FILTER (ES: FILTRAR)
Returns only the rows that meet a condition, spilling the result. Multiply conditions for AND, add them for OR.
=FILTER(array, include, [if_empty])=FILTER(Sales, (Sales[region] = "South") * (Sales[channel] = "Online"), "No rows")SORT (ES: ORDENAR)
Sorts a range by one of its columns. Sort order 1 = ascending, -1 = descending.
=SORT(array, [sort_index], [sort_order], [by_col])=SORT(UNIQUE(Sales[store_name]))SORTBY (ES: ORDENARPOR)
Sorts a range by another range, which does not have to be part of the result.
=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)=SORTBY(Sales[product_name], Sales[net_sales], -1)UNIQUE (ES: UNICOS)
Returns the distinct values (or rows) of a range.
=UNIQUE(array, [by_col], [exactly_once])=UNIQUE(Sales[category])SEQUENCE (ES: SECUENCIA)
Generates a list of sequential numbers. Combine with dates to build calendars.
=SEQUENCE(rows, [columns], [start], [step])=DATE(2025, SEQUENCE(12), 1)LET
Names intermediate results inside a formula so it is easier to read and each part is calculated once.
=LET(name1, value1, [name2, value2], ..., calculation)=LET(rev, SUM(Sales[net_sales]), cst, SUM(Sales[cost]), (rev - cst) / rev)LAMBDA
Creates a reusable custom function. Save it in Name Manager to call it by name.
=LAMBDA([parameter1, parameter2, ...], calculation)=LAMBDA(sales, cost, (sales - cost) / sales)([@net_sales], [@cost])VSTACK (ES: APILARV)
Stacks ranges on top of each other into one array. HSTACK places them side by side.
=VSTACK(array1, [array2], ...)=VSTACK(Sales2024, Sales2025)TAKE (ES: TOMAR)
Keeps the first rows (or the last, with a negative number). Perfect for top-N lists.
=TAKE(array, rows, [columns])=TAKE(SORTBY(Sales[product_name], Sales[net_sales], -1), 5)DROP (ES: EXCLUIR)
Removes the first rows (or the last, with a negative number) from an array.
=DROP(array, rows, [columns])=DROP(A1:N100, 1)GROUPBY (ES: AGRUPARPOR)
Builds a grouped summary with a single formula, like a mini pivot table. Microsoft 365 only.
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])=GROUPBY(Sales[region], Sales[net_sales], SUM)PIVOTBY (ES: PIVOTARPOR)
Builds a cross-tab (rows by columns) summary with a formula that updates automatically. Microsoft 365 only.
=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to])=PIVOTBY(Sales[region], Sales[channel], Sales[net_sales], SUM)AVERAGE (ES: PROMEDIO)
Returns the arithmetic mean. Empty cells and text are ignored.
=AVERAGE(number1, [number2], ...)=AVERAGE(Sales[net_sales])MEDIAN (ES: MEDIANA)
Returns the middle value. Less affected by a few very large orders than AVERAGE.
=MEDIAN(number1, [number2], ...)=MEDIAN(Sales[net_sales])STDEV.S (ES: DESVEST.M)
Estimates the standard deviation of a sample: how spread out the values are.
=STDEV.S(number1, [number2], ...)=STDEV.S(Sales[net_sales])PERCENTILE.INC (ES: PERCENTIL.INC)
Returns the value below which k (0 to 1) of the data falls. 0.9 = 90th percentile.
=PERCENTILE.INC(array, k)=PERCENTILE.INC(Sales[net_sales], 0.9)RANK.EQ (ES: JERARQUIA.EQV)
Returns the rank of a number in a list. Order 0 (default) ranks the largest as 1.
=RANK.EQ(number, ref, [order])=RANK.EQ(B2, $B$2:$B$13, 0)LARGE (ES: K.ESIMO.MAYOR)
Returns the k-th largest value. SMALL returns the k-th smallest.
=LARGE(array, k)=LARGE(Sales[net_sales], 3)