19 Essential Excel Formulas to Master the Application

We are going to tell you the 19 Essential Excel Formulaswith a compilation for you to squeeze out the spreadsheets of Microsoft Office. Excel. This tool is together with Google Sheets one of the best alternatives to create spreadsheets, and these resources are initial ideas so you can see what you can do with it. And if you don’t know enough, remember that we have also shown you the best excel tricks.

Excel formulas are the heart of the program, since they are what will allow you to do all types of calculations, and they can adapt the program to your needs. Today we will review the most useful formulas for the general public, with examples of how to use each. Excel files can be opened in other appsremember it, but if you use another one, the way you proceed with the formulas could be different.

And these formulas are our basic proposals, but don’t be afraid to modify or combine them. And if you think there is any other that should be on the list, we invite you to tell us in the comments section so that other readers can benefit from the knowledge of our xatakeros.

Simple math operations

Before getting into more complicated formulas, let’s see how to do the simpler math operations: addition, subtraction, multiplication and division. Technically only the sum is a formula, since in the rest of the cases special operators are used.

• ADDITION– This formula adds the values ​​of the cells inside it. Supports both separate cells and intervals. Example: =SUM(A1:A50)
• Subtractions: To subtract the values ​​of two cells you must use the subtraction symbol “-” between them. Example: = A2 – A3
• Multiplications: to multiply the values ​​of two cells you must insert an asterisk * between them. Example: = A1 * A3 * A5 * A8
• Divisions: to divide the values ​​of two cells you must include the line / between them. Example: = A2 / C2

Excel respects the logical order of mathematical operations (multiplications and divisions first, then addition and subtraction) and supports the use of parenthesis to give priority to some operations over others. This way, you can create formulas like = (A1 + C2) * C7 / 10 + (D2 – D1).

AVERAGE

The average formula returns the value of arithmetic average of the cells you pass or range of cells you pass as a parameter. This result is also known as the mean or arithmetic mean.

• Use:=AVERAGE (cells with numbers)
• Example:=AVERAGE (A2:B2)

MAX and MIN

If instead of wanting to know the arithmetic mean you want to know what the highest value or lowest value of a set, you have at your disposal two formulas with predictable names: MAX and MIN. You can use them with separate cells or ranges of cells.

• Use: =MAX(cells) / =MIN(cells)
• Example: =MAX(A2:C8) / =MIN(A2,B4,C3,29)

YES.ERROR

SI.ERROR is a formula that will get you out of more than one trouble. With it you can avoid mistakes #DIV/0! and the like. This formula allows you return a value in case another operation fails. This is quite common with divisions, since any division by zero will give an error, which can cause a chain reaction of errors. The operation in question can be an operation or any other formula.

• Use: =IF.ERROR( operation, value if there is an error)
• Example: =IF.ERROR (MAX(A2:A3) / MIN(C3:F9),”There has been an error”)

YEAH

SI is one of the most powerful formulas in EXCEL and with it you can return a different result depending on if the condition is met. This way, you could use it to have one cell say “APPROVED” if another is a number greater than 5, or “SUSPENDED” if it is less.

• Use: =IF(condition, value if the condition is met, value if it is not met)
• Example: =YES(B2=”Madrid”,”Spain”,”Other country”)

WILL COUNT

COUNTA is one of the formulas for counting values. Unlike simple COUNT, WILL COUNT also count values ​​that are not numbers. The only thing it ignores are empty cells, so it can be useful for you to know how many entries a table has, regardless of whether the data is numeric or not.

• Use: =COUNT(range of cells)
• Example: =COUNT(A:A)

COUNTIF

The COUNTIF formula is a kind of mixture of the previous two. It will count the specified range of cells as long as when they meet certain requirements. These may have a certain value or meet certain conditions.

• Use: =COUNTIF(cell range, criterion)
• Example: =COUNTIF(C2:C, “Pepe”)

SUM IF

This is the equivalent for sums of the previous one. SUMIF is just as essential and very intuitive for users who already understand COUNTIF: it adds only the values ​​that meet a given criterion, without the need to manually filter.

• Use: =SUMIF(criterion_range; criterion, sum_range)
• Example: =SUMIF(B2:B50; “Madrid”; C2:C50) → adds all the values ​​of C only when the corresponding cell in B says “Madrid”.

RANDOM.BETWEEN

This formula generates a random number between two other given numbers, and is therefore ideal when you need to choose something at random. The generated number changes each time the sheet is regenerated (for example, when you type a new value).

• Use: =RANDOM.BETWEEN(smallest number; largest number)
• Example: =RANDOM.BETWEEN (1;10)

DAYS

The date calculations They are always a tricky subject if you have to do them by hand, but everything is much easier when a formula does the hard work for you. DAYS he tells you number of days difference between two dates.

• Use: =DAYS(first date, second date)
• Example: =DAYS (“2/2/2018”, B2)

NOW

Another Excel essential is the NOW formula. This generates the date for the current moment and it is data that will be automatically updated every time you open the sheet or its values ​​are recalculated (because you change a cell, for example). This formula does not require any parameters.

• Use: =NOW()
• Example: =NOW()

DAYSEM

SEMDAY is another useful formula related to dates, which returns numerically the day of the week of a date. Monday is 1, Tuesday is 2, and so on, although there are several ways to start counting, as you can indicate in the second parameter.

• Use: =SEMDAY (date; account type)

For account typeyou must use one of these parameters:

• 1: numbers from 1 (Sunday) to 7 (Saturday)
• 2: numbers from 1 (Monday) to 7 (Sunday)
• 3: numbers from 0 (Monday) to 6 (Sunday)
• 11: numbers from 1 (Monday) to 7 (Sunday)
• 12: numbers from 1 (Tuesday) to 7 (Monday)
• 13: numbers from 1 (Wednesday) to 7 (Tuesday)
• 14: numbers from 1 (Thursday) to 7 (Wednesday)
• 15: numbers from 1 (Friday) to 7 (Thursday)
• 16: numbers from 1 (Saturday) to 7 (Friday)
• 17: numbers from 1 (Sunday) to 7 (Saturday)
• Example: =WEEKDAY(NOW(); 2)

HYPERLINK

Excel automatically converts web addresses into links, but if you want to create a link with different text, you need to use a formula. That formula is HYPERLINK, with which you can add links to any cell.

• Use: =HYPERLINK (link address, link text)
• Example: =HYPERLINK(“http://www.google.com”, “Visit Google”)

TRANSPOSE

TRANSPOSE change rows to columns and it is a somewhat special formula. You must apply it to a selection of rows that inversely matches the table you want to transpose. For example, if the original table has 2 rows and 4 columns, you need to apply it to 4 rows and 2 columns. Also, it’s an array formula, so you need to press Control + Shift + Enter to apply it.

• Use: {=TRANSPOSE { cell range )}
• Example: {=TRANSPOSE ( A1:C20)}

REPLACE

REPLACE is a useful formula with which you can insert or replace part of a text. It does not consist of replacing one text with another (that is the SUBSTITUTE formula), but rather inserting a text in a certain position and, optionally, replacing part of the original text. With its two parameters you choose in which position the text is inserted, as well as how many characters will be removed from the original text, after that position

• Use: =REPLACE (original text; location where it is inserted; characters from the original text that are deleted; text to insert)
• Example: =REPLACE(“Merry Christmas”; 6; 8; “Hanukkah”)

CONCATENATE

CONCATENATE is a formula that can get you out of several troubles. Its usefulness is as simple as join multiple text elements into a single text. As a parameter you cannot specify a range of cells, but rather individual cells separated by commas.

• Use: =CONCATENATE( cell1; cell2; cell3 … )
• Example: =CONCATENATE ( A1; A2; A5; B9)

SPACES

Extra spaces in a text can be problematic because you will not always be able to detect them with the naked eye. One way to kill them easily is with SPACES, which removes all spaces from more than one textregardless of where they are.

• Use: =SPACES (cell or text with extra spaces)
• Example: =SPACES (F3)

FIND

FIND is a formula with which you can know if the text in a cell contains other text. If yes, the formula returns the position in the text where the first concurrence was found. If not, it returns an error (remember to use IFERROR to handle these cases).

• Use: =FIND(text you are looking for; original text)
• Example: =FIND ( “needle”; “haystack” )

VLOOKUP / XLOOKUP

It is probably the most searched formula on Google by mid-level users, and its absence in the article is the most striking gap. VLOOKUP allows you to search for a value in a column and return data from another column in the same row (for example, enter a product code and automatically obtain its price). XLOOKUP is its modern version (available in Microsoft 365), more flexible and without the address limitations of VLOOKUP.

• Use VLOOKUP: =VLOOKUP(lookup_value; table_range; column_num; (sorted))
• Example: =VLOOKUP(A2; B2:D100; 3; FALSE) → looks for the value of A2 in the first column of the range B2:D100 and returns the data in the third column.

In Xataka Basics | Microsoft Excel: 24 functions, tricks and tips to get the most out of the spreadsheet app