How to Use Common Excel Formulas (With Helpful Tips)

By Indeed Editorial Team

Published June 10, 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Microsoft Excel is a spreadsheet software that many professionals use to organize data and information. Different formulas or functions in Excel can help you calculate and search for various important information. Understanding how to use formulas can help you use Excel more efficiently and make it easier to organize your data. In this article, we define Excel functions, review common formulas in Excel, and share tips for using these formulas and functions successfully.

What are Excel formulas?

Individuals use these formulas in Microsoft Excel, a type of spreadsheet software, to perform different functions for a cell group or a range of cells. For example, these formulas can determine sums, averages, and percentages for ranges of cells. These formulas make it easier to work with a large variety of values and find the information you need. Also called functions, they're pre-programmed into Excel to make working in this spreadsheet software easier.

Related: 5 Basic Excel Skills and How to Include Them in Your Resume

Common formulas in Excel

There are over 450 pre-loaded formulas in Excel. Individuals working in Excel tend to use some of these formulas much more frequently than others. Some of the most common formulas in Excel include:

COUNT

The "COUNT" function counts the number of cells within the given range that contain a number value. For example, you might use the "COUNT" function to look at the first 150 entries in the B column, and return the number of these cells that contain a numerical value. The formula for the "COUNT" function is "=COUNT(Range)," in which you may state the range of cells to view.

Related: A List of Intermediate Skills in Excel to Include in Your Resume

COUNTA

"COUNTA" is similar to "COUNT" in that it counts the number of certain values within cells. COUNT differs from COUNT because it returns the number of cells that aren't empty. This formula can be helpful when working with incomplete data sets so that you can determine where in a cell particular values are missing. The formula for "COUNTA" is "=COUNTA(Range)".

Related: 18 Data Analyst Skills for Success

SUM

The "SUM" function adds up the values within a cell or range of cells. This makes it easier to add or product the sum of the values in the cell. It can also help you adjust the sum when there are changes to different values in the cell. The formula for "SUM" is "=SUM(Range)".

AVERAGE

The "AVERAGE" formula provides the average value for the group of cells specified. This means that this formula determines the total sum or value of the cells, and then divides it by the number of cells, providing the average or mean of the cell range. It's the "SUM" function divided by the "COUNT" function. The formula for calculating the average is "=AVERAGE(Range)".

Related: How to Calculate the Median of a Data Set in Statistics

IF statements

"IF" statements are an extremely useful tool in Excel and you can use them in combination with other formulas to give you more specific results. "IF" statements mean the formula is only run when the data meets the conditions given. You can input these conditions into the "IF" statement formula, and specify whether you want to use the formula when the data meets these conditions, or when it doesn't.

For example, if you want to offer a bonus to customers who have spent more than $150, you can use "IF" statements to determine which customers met this condition quickly. For example, you might offer this bonus to customers who have a value higher than $150 in column A. The formula you can use for this is "=IF(A1>150, "Bonus," "No Bonus")". Then, if the customer spent more than $150, Excel returns the value of "BONUS". If the customer spent less than $150, Excel returns the value of "NO BONUS". The formula for "IF" statements in Excel is "=IF(condition, TRUE, FALSE)".

Related: Advanced Excel Skills: Definition and Examples

CONCATENATE

The "CONCATENATE" function is used to combine the values of two or more cells. For example, if you have a list of customers with the customer's title in column A, their first name in column B, and their last name in column C, you can use the "CONCATENATE" function to return their full name in column D. In this example, the formula to input in D1 is "=CONCATENATE(A1, " ", B1, " ", C1)". The formula for the "CONCATENATE" function is "=CONCATENATE(Value1, Separator, Value2)".

MAX and MIN

The "MAX" and "MIN" formulas return the largest or smallest number with a cell's dataset, respectfully. The formula for "MAX" is "=MAX(Value1, Value2)". As an example, "=MAX(C1, C20)" can return the largest or maximum value found between C1 and C20. The formula for "MIN" is "=MIN(Value1, Value2)". For example, "=MIN(C1, C20)" returns the smallest or minimum value found between C1 and C20.

AND

You can use the "AND" function in Excel to determine if something is true or false. The formula for calculating "AND" is "=AND(logical1),(logical2)", where logical refers to the conditions to be met. With this formula, you can add as many conditions as you like. For example, using the formula "=AND(A4>10, B4="Yes")" returns "TRUE" if A4 was a value greater than 10 and B4 was the text of "YES". If it doesn't meet both of these conditions, the result returns as "FALSE".

XLOOKUP

"XLOOKUP" is a newer formula in Excel that is used for finding information within an excel spreadsheet. This formula searches a given range and shows the corresponding value. For example, if there's a spreadsheet with names in column A from A1 to A20 and corresponding addresses in column B from B1 to B20, you can use the formula "=XLOOKUP(“Elizabeth”A1:A20,B1:B20)" to return Elizabeth's address. If there's no clear match found, excel returns the closest approximate match. Because "XLOOKUP" is a newer excel function, individuals with an older version of Excel may the formulas "VLOOKUP" or "HLOOKUP".

REPLACE

The "REPLACE" function is ideal for making changes in a spreadsheet, as it allows you to replace certain values with a desired alternative quickly. The formula for "REPLACE" in Excel is "=REPLACE(old_text, start_num, num_chars, new_text)", where "old text" represents the text you want to replace, "new text" is the text you wish to replace the old text with, "start num" is the spot within the cell where the text you wish to replace starts, and "num chars" is the number of characters you wish to replace.

For example, if you have a list of ID numbers and registration dates in column A and a list of corresponding names in column B, and you want to replace the list of ID numbers with the related name, inputting the formula "=REPLACE(A2, 1, 10, B2)" into column C can produce a list of registration dates with the associated name.

Tips for using formulas in Excel

The following tips are important for using and inputting formulas or functions in Excel successfully:

Inputting formulas

Here are the steps to follow to input a formula into Excel:

  1. Begin by clicking the cell where you wish the result to be generated.

  2. Enter the formula into the white bar, located next to the "Fx" button above the spreadsheet.

  3. Ensure that you begin the formula with an equal sign, as all formulas in Excel start this way.

  4. Push "ENTER" to use the formula.

Searching for functions

You can use the following steps to find a function in Excel:

  1. Begin by clicking the cell where you wish the result to be generated.

  2. Locate the "Fx" button above the spreadsheet.

  3. Browse through the functions by category, or begin typing to search for a desired function. Click on the function to have the formula appear in the formula bar next to the "Fx" button.

  4. Select or enter the data into this formula and push "ENTER".

Editing a formula

You can use the following steps to edit a formula in Excel:

  1. Click on the cell where the result of the formula is occurring. After doing this, the formula for the cell appears in the formula box.

  2. Click on the formula and edit it as desired. For example, change "=SUM(A1:A10)" to "=SUM(A1:A20)" to change from calculating the sum between A1 and A10 to calculating the sum between A1 and A20.

  3. After editing the formula, hit "ENTER" to use the formula with the changes.

Deleting a formula without losing a value

Usually, if you delete a formula from Excel, Excel also deletes the related values. If you wish to delete the formula without deleting the values, you can follow these steps:

  1. Select the cells containing the formula.

  2. Copy the cells using "CTRL + C".

  3. Right-click on the cells and select “Paste values > values” to paste these values back into the cells.

Please note that none of the companies, institutions, or organizations mentioned in this article are affiliated with Indeed.

Explore more articles