9 Excel Formulas Every Beginner Should Know

One of the most important features of Excel is the ability to use formulas. Formulas allow you to perform calculations, manipulate data, and automate repetitive tasks, making Excel an indispensable tool for anyone who works with numbers. If you're looking for a basic overview of Excel, check out my Introduction to Excel. In this blog post, we'll cover 9 Excel formulas every beginner should know.

Hi, I'm Lindsay and I'm an Excel enthusiast and I have worked with Excel nearly every day for almost 20 years. I've even taken a few university classes on the topic! The formulas I'm about to discuss below are all ones that I have used extensively and you will too if you start using Excel often.

Understanding Excel Formulas

Before we dive into the formulas, let's take a moment to understand what Excel formulas are and how they work. A formula in Excel is a mathematical equation that performs calculations on values in your spreadsheet. It's like telling Excel what you want it to do with the numbers, and then Excel gives you the answer! Formulas always begin with an equals sign (=) and can include a combination of numbers, cell references, and functions.

For example, the formula “=A1+B1” adds the values in cells A1 and B1 together. As you change the values in those cells, the formula automatically updates to reflect the new result.

Now that we understand the basics of Excel formulas, let's explore 9 formulas that every beginner should know:

Excel Formulas Every Beginner Should Know

SUM Formula

The SUM formula is one of the most frequently used formulas in Excel. It allows you to quickly add up a range of values in a column, row, or range of cells. Here's an example of how to use the SUM formula:

Let's say you have a spreadsheet with a list of household expenses (dollars) from the week in one column. The expenses are stored in cells B2 through B5, and you want to find the total expenses for the week. To do this, you can use the SUM formula as follows:

1. Click on an empty cell where you want to display the total expenses (in this case B6).
2. Type “=SUM(” in the cell, and then select the range of cells you want to add up (in this case, B2 through B5).
3. Close the parentheses and press Enter. The total expenses for the week will be displayed in the cell.

Alternatively, you can also use the AutoSum button on the Home tab of the Excel ribbon to quickly add up a range of values.

The SUM formula is versatile and can be used in a variety of ways. For example, you can use it to add up values in a single row or column, or you can use it to add up values in multiple ranges of cells. You can also use it in conjunction with other formulas to perform more complex calculations.

If you decide to sell templates as part of your Excel journey, this formula will be your number 1 go-to.

AVERAGE Formula

The AVERAGE formula is another essential formula in Excel that allows you to quickly calculate the average of a range of values in a column, row, or range of cells. Here's an example of how to use the AVERAGE formula:

Let's say you have a spreadsheet with a list of test scores for a class of students. The test scores are stored in cells B2 through B10, and you want to find the average score for the class. To do this, you can use the AVERAGE formula as follows:

1. Click on an empty cell where you want to display the average score (in this case B11).
2. Type “=AVERAGE(” in the cell, and then select the range of cells you want to average (in this case, B2 through B10).
3. Close the parentheses and press Enter. The average score for the class will be displayed in the cell.

Alternatively, you can also use the AutoAverage button on the Home tab of the Excel ribbon to quickly calculate the average of a range of values.

The AVERAGE formula can also be used in conjunction with other formulas to perform more complex calculations. For example, you can use it to calculate the weighted average of a range of values, where each value is assigned a different weight.

MAX Formula

The MAX formula is used to determine the highest value in a range of cells. For example, let's use the same list of test scores in a column, and you want to find the highest score in that column. Here's how to use the MAX formula:

1. Click on an empty cell where you want to display the highest score.
2. Type “=MAX(” in the cell, and then select the range of cells that you want to find the highest value in (in this case, the column of test scores).
3. Close the parentheses and press Enter. The highest score will be displayed in the cell.

Alternatively, you can also use the AutoMax button on the Home tab of the Excel ribbon to quickly find the highest value in a range of cells.

The MAX formula can also be used in conjunction with other formulas to perform more complex calculations. For example, you can use it to find the highest value in a range of cells that meets a certain condition, such as being greater than a certain number.

MIN Formula

The MIN formula is used to determine the minimum value in a range of cells. For example, let's use the same list of test scores in a column, and you want to find the lowest score in that column.

1. Click on an empty cell where you want to display the smallest expense.
2. Type “=MIN(” in the cell, and then select the range of cells that you want to find the minimum value of (in this case, the column of expenses).
3. Close the parentheses and press Enter. The smallest expense will be displayed in the cell.

Alternatively, you can also use the AutoMin button on the Home tab of the Excel ribbon to quickly find the minimum value in a range of cells.

The MIN formula can also be used in conjunction with other formulas to perform more complex calculations. For example, you can use it to find the smallest value in a range of cells that meets a certain condition, such as being less than a certain number.

COUNT Formula

The COUNT formula is used to count the number of cells in a range that contain a numeric value. For example, let's say you have a list of students, and you want to count how many scores there are in that column. Here's how to use the COUNT formula:

1. Click on an empty cell where you want to display the count of scores.
2. Type “=COUNT(” in the cell, and then select the range of cells that you want to count (in this case, the column of test scores).
3. Close the parentheses and press Enter. The count of scores will be displayed in the cell.

Alternatively, you can also use the AutoCount button on the Home tab of the Excel ribbon to quickly count the number of cells in a range that contain a numeric value.

The COUNT formula can also be used in conjunction with other formulas to perform more complex calculations. For example, you can use it to count the number of cells in a range that meet a certain condition, such as being greater than a certain number.

IF Formula

The IF function in Excel allows you to specify a logical test and set a value or action to be taken if the test is true or false. This can be very useful for making decisions based on certain conditions or criteria.

Here's how to use the IF function:

1. Start by selecting the cell where you want to display the result of your IF function.
2. Type “=IF(” in the cell.
3. Specify the logical test that you want to use. This can be a comparison between two values, a function that returns a true or false result, or any other expression that evaluates to true or false.
4. Specify the value or action that should be taken if the logical test is true. This can be a number, text, formula, or any other value that you want to display or use in your spreadsheet.
5. Specify the value or action that should be taken if the logical test is false. Again, this can be any value or action that you want to use.

Here's an example of how to use the IF function:

Let's say you have a spreadsheet that tracks the number of hours that employees have worked, and you want to calculate their overtime pay based on whether they worked more than 40 hours in a week. You could use the IF function to calculate this as follows:

1. Select the cell where you want to display the overtime pay.
2. Type “=IF(” in the cell.
3. Specify the logical test: “B2>40”, where B2 is the cell that contains the total number of hours worked in a week for the employee you are calculating overtime pay for.
4. Specify the value or action that should be taken if the logical test is true: “(B2-40)*1.5”, where 1.5 represents time-and-a-half pay for overtime hours.
5. Specify the value or action that should be taken if the logical test is false: “0”.
6. Close the parentheses and press Enter.

This formula will calculate the employee's overtime pay as 1.5 times the number of hours worked over 40, or 0 if the employee did not work any overtime.

CONCATENATE Formula

The CONCATENATE function in Excel allows you to combine two or more strings of text into a single cell. This can be very useful for creating longer text strings or for joining together data from different cells.

Here's how to use the CONCATENATE function:

1. Start by selecting the cell where you want to display the concatenated text.
2. Type “=CONCATENATE(” in the cell.
3. Specify the first text string that you want to combine. This can be a cell reference, a text string in quotation marks, or any other value that you want to use.
4. Add a comma to separate the first text string from the next.
5. Specify the next text string that you want to combine.
6. Repeat steps 4-5 for any additional text strings that you want to combine.
7. Close the parentheses and press Enter.

Here's an example of how to use the CONCATENATE function:

Let's say you have a spreadsheet that tracks customer orders, and you want to combine the customer's first and last name into a single cell for use in a mailing label. You could use the CONCATENATE function to do this as follows:

1. Select the cell where you want to display the customer's full name.
2. Type “=CONCATENATE(” in the cell.
3. Specify the cell that contains the customer's first name, followed by a space, and the cell that contains the customer's last name, in quotation marks: A2&” “&B2
4. Close the parentheses and press Enter.

This formula will combine the contents of cells A2 and B2, separated by a space, into a single cell.

LEFT or RIGHT Formula

The LEFT formula extracts a specified number of characters from the beginning of a text string. For example, “=LEFT(A1,3)” would extract the first three characters from the value in cell A1.

The RIGHT formula extracts a specified number of characters from the end of a text string. For example, “=RIGHT(A1,3)” would extract the last three characters from the value in cell A1.

Here's how to use the RIGHT function:

1. Start by selecting the cell where you want to display the extracted text.
2. Type “=RIGHT(” in the cell.
3. Specify the cell or text string that you want to extract the characters from.
4. Add a comma to separate the text string from the next argument.
5. Specify the number of characters that you want to extract.
6. Close the parentheses and press Enter.

Here's an example of how to use the RIGHT function:

Let's say you have a spreadsheet that tracks employee IDs, and each ID is formatted as “EMP0001”, “EMP0002”, and so on. You want to extract the employee number (the last four characters) from each ID and display it in a separate column. You could use the RIGHT function to do this as follows:

1. Select the cell where you want to display the employee number.
2. Type “=RIGHT(” in the cell.
3. Specify the cell that contains the employee ID: B2
4. Add a comma to separate the cell reference from the next argument.
5. Specify the number of characters that you want to extract: 4
6. Close the parentheses and press Enter.

This formula will extract the last four characters from cell B2 (which contains the employee ID) and display them in the cell where you entered the formula.

These formulas are also very useful when removing first or last characters of a cell.

VLOOKUP Formula

The VLOOKUP formula is used to search for a specific value in the first column of a table or range, and then return a corresponding value from a different column in the same row. This formula can be useful for tasks such as matching names to ID numbers or finding product prices based on a product code.

Here's how to use the VLOOKUP formula:

1. Identify the table or range you want to search in. The table should have at least two columns, with the first column containing the values you want to search for.
2. Select the cell where you want to display the corresponding value.
3. Type “=VLOOKUP(” in the cell.
4. Specify the value you want to search for in the first column of the table or range. This can be a cell reference, a value, or a formula.
5. Specify the range of cells that contains the table, including all columns you want to return values from. For example, if your table has three columns (A, B, and C) and you want to return values from columns B and C, your range should be A:C.
6. Specify the column number of the table that contains the value you want to return. This number starts at 1, with the first column in the table being column 1.
7. Indicate whether you want the formula to find an exact match or an approximate match. If you want an exact match, set the fourth argument to “FALSE” or “0”. If you want an approximate match, set the fourth argument to “TRUE” or “1”.
8. Close the parentheses and press Enter.

For example, let's say you have a table with two columns: “Product ID” and “Price”. You want to find the price for a product with ID number “123”. Here's how to use the VLOOKUP formula to do this:

1. Identify the table or range you want to search in (let's say the table is located in cells A2:B10).
2. Select the cell where you want to display the corresponding value (let's say the cell is D1).
3. Type “=VLOOKUP(123, A2:B10, 2, FALSE)” in the cell.
4. Press Enter. The price for product ID “123” will be displayed in cell D1.

The VLOOKUP formula is a powerful tool that can save you a lot of time and effort when working with large tables or ranges in Excel. With just a few simple steps, you can search for specific values and return corresponding values from different columns.

You're a Formula Master

That's it. There are 9 basic Excel formulas that are most commonly used. If you can master these, you'll be well on your way to graduating from ‘beginner Excel user' to ‘intermediate Excel user'. Congratulations!