Excel functions in English and Spanish

Basics

• SUMSUMAAdds numbers, cells and ranges. It is Excel’s most used function.
• AVERAGEPROMEDIOCalculates the arithmetic mean: adds the numbers and divides by how many there are. Skips blank cells and text.
• MINReturns the smallest number in a list of values.
• MAXReturns the largest number in a list of values.
• COUNTCONTARCounts how many cells contain numbers (dates included).
• COUNTACONTARACounts cells that are not blank, whether they hold numbers, text or errors.

Percentages and rounding

• ROUNDREDONDEARRounds a number to the digits you choose.

Logic

• IFSIReturns one value when a condition is true and another when it is false. It is the basis of logic in Excel.
• ANDYReturns TRUE only when every condition is true. It is almost always used inside IF.
• OROReturns TRUE when at least one condition is true.
• IFERRORSI.ERRORReturns a formula’s result or, if it returns any error, the value you choose.

Conditional counts and sums

• COUNTIFCONTAR.SICounts the cells in a range that meet a condition.
• SUMIFSUMAR.SIAdds only the rows that meet a condition.
• AVERAGEIFPROMEDIO.SIAverages only the rows that meet a condition.

Multiple conditions

• COUNTIFSCONTAR.SI.CONJUNTOCounts the rows that meet every condition at once.
• SUMIFSSUMAR.SI.CONJUNTOAdds only the rows that meet every condition.
• AVERAGEIFSPROMEDIO.SI.CONJUNTOAverages only the rows that meet every condition.
• MAXIFSMAX.SI.CONJUNTOReturns the highest value among the rows that meet the conditions.
• MINIFSMIN.SI.CONJUNTOReturns the lowest value among the rows that meet the conditions.
• SUMPRODUCTSUMAPRODUCTOMultiplies ranges row by row and adds up the results: the total of quantity × price in one formula.

Lookup

• VLOOKUPBUSCARVLooks up a value in a table’s first column and returns data from another column on that row.
• INDEXINDICEReturns the value at a position in a range. Together with MATCH it makes Excel’s most flexible lookup.
• MATCHCOINCIDIRReturns the position of a value within a row or column.
• XLOOKUPBUSCARXThe modern lookup in Excel 365 and 2021: it searches in any direction and matches exactly by default.

Text

• LEFTIZQUIERDAReturns the first characters of a text.
• RIGHTDERECHAReturns the last characters of a text.
• MIDEXTRAEReturns characters from the middle of a text, starting at a position.
• PROPERNOMPROPIOCapitalizes the first letter of each word and lowercases the rest.
• LOWERMINUSCConverts text to lowercase. Its opposite is UPPER.
• LENLARGOCounts the characters in a text, spaces included.
• TRIMESPACIOSRemoves leading and trailing spaces and leaves a single space between words.

Dates

• YEARAÑOReturns the year of a date as a four-digit number.
• MONTHMESReturns the month of a date, from 1 (January) to 12 (December).
• DATEDIFSIFECHACalculates full years, months or days between two dates. It is how you calculate age or tenure.
• NETWORKDAYSDIAS.LABCounts working days (Monday to Friday) between two dates, both included, minus the holidays you list.
• EDATEFECHA.MESAdds or subtracts months from a date, handling short months.
• EOMONTHFIN.MESReturns the last day of the month, some months before or after a date.
• WEEKDAYDIASEMReturns the day of the week of a date as a number.

Dynamic arrays (365)

• UNIQUEUNICOSReturns the list of values without duplicates. The result spills down.
• SORTORDENARReturns a sorted range without changing the original data.
• FILTERFILTRARReturns only the rows that meet a condition; the result spills and updates by itself.

English ↔ Spanish equivalents table

Spanish Excel in Spain and much of Latin America separates arguments with semicolons (;); English Excel and Mexican Spanish Excel use commas (,).