A Guide to Using Common Microsoft Excel Formulas & Functions

Microsoft Excel’s features make it the go-to tool for businesses working with data. It’s built to generate reports and business insights, and mastering Excel’s basic formulas and functions is an essential first step for those interested in the fields of financial analysis, data analysis, accounting, and more.

What Are Excel Formulas?

Formulas are expressions that operate on values in a range of cells. They can be used to perform calculations and data analysis on the contents of the cells.

Formulas can be as simple as adding a column of numbers together or as complex as returning statistics on a data set. You can manipulate string, number, and date data fields, or use if-else statements, find and replace, mathematics and trigonometry, finance, logical, and engineering formulas. Formulas are highly useful for turning spreadsheet data into meaningful information to influence business decisions.

To use formulas, you just write the formula name and the arguments. You don’t have to use programming languages, and you can add formulas with the Excel-assisted user interface.

How to Use Excel Formulas

Adding an Excel formula is relatively easy. The fastest and most effective way to use formulas is by adding them manually.

Here are general guidelines for manually inputting formulas:

1. Choose the cell for the resulting output.
2. Type “=” in the cell. The equal sign will appear in the cell and formula bar.
3. Type the address(es) of the cell(s) you want to use for your calculation.
4. Add the calculation you want to perform.
5. Press Enter.

Here’s an example: =(A1+A2+A3). This formula will calculate the sum of the values in cells A1, A2, and A3.

What Is a Function in Excel?

Functions are built-in formulas in Excel. They’re like shortcuts for performing a calculation or other operation on cell data. There are almost 500 Excel functions, and the number continues to grow. But most actions a typical business user would want to perform can be done with just a few functions.

How to Insert Formulas in Excel for an Entire Column

Once you’ve added a formula to a single row, you don’t have to reenter it again for the following rows. You can apply the same formula to an entire column in a few different ways:

• Drag down the fill handle: When you select a cell, you will see a small green box at the bottom right called a fill handle. Click and hold the fill handle, and drag it down through all the rows you want the formula to be applied to. The formula will be applied to all the rows you selected.
• Double-click the fill handle: Select the cell with the formula in it and double-click the fill handle. The formula will be applied to the entire column.
  Shortcut: Select the cell with the formula and the empty cells below it. Press CTRL + D on Windows or CMD + D on Mac to apply the formula to the selected cells. Make sure you’re not selecting anything above the formula cell.
• Copy-pasting: Copy the cell with the formula by pressing CTRL + C on Windows or CMD + C on Mac. Select the empty rows in the column and paste the formula by pressing CTRL + V on Windows or CMD + V on Mac. Make sure you’re not using the fill handle to select the rows.

Common Excel Formulas & Functions

Here is a list of some basic Excel formulas and functions.

SUM

The SUM() formula performs addition on selected cells. It works on cells containing numerical values and requires two or more cells.

To add the values of two cells together, your formula can look like this: =SUM(A1+B1)

If you want to add the values of more than two cells, you can use a colon (:) to indicate a range of cells. Your formula can look like this: =SUM(A1:A3)

Average

The AVERAGE() formula calculates the average of selected cells. You can provide a range of cells or select individual cells.

Your AVERAGE formula can look like this: =AVERAGE(A1:A5)

Count

The COUNT() formula counts the total number of selected cells. It doesn’t count blank cells or cells with data formats other than numeric.

Your COUNT formula can look like this: =COUNT(A1:A10)

If you want to count cells with non-numeric values, like date-time and string data, you need to use the COUNTA() formula.

Like the COUNT formula, the COUNTA formula does not count missing values. To count blank cells, use the COUNTBLANK() formula.

Power

The POWER() formula can be used to square, cube, or raise a value to any power. For example, you can use it to convert height data in centimeters to meters squared (m2).

If the height data in centimeters was in cell A2, your POWER formula could look like this: =POWER(A2/100,2)

This formula will divide the value in A2 by 100 and square it (raise it to a power of 2) to give you the height in meters squared (m2).

MIN & MAX

The MIN() formula requires a range of cells, and it returns the minimum value in that range.

Your MIN formula can look like this: =MIN(A1:A3)

The MAX() formula is the opposite of the MIN() formula. It will return the maximum value in a range of cells.

Your MAX formula can look like this: =MAX(A1:A3)

CONCAT

The CONCAT() formula merges the values in multiple cells. It can merge strings, which are groups of text, and it can also join multiple cells with strings.
If your spreadsheet contains age and sex values, for example, you can merge these into one value with the CONCAT formula.

If the age and sex data are in cells A2 and B2, your formula can look like this: =CONCAT(A2, B2)

The result will be a cell with both values combined.

If Function

For IF functions, you provide the logic of the formula. If the logic is correct, it will return a certain value, and if the logic is false, it will return a different value.

For example, if you need to know whether the value in A1 is greater than 5, your formula could look like this: =IF(A1>5, “Yes”, “No”).

If the value in A1 was 4, this formula would return “No”.

Replace & Substitute

The REPLACE formula is used to replace part of a string (text) with a new string. The formula is REPLACE(old_text, start_num, num_chars, new_text).

• Old_text is the original text or cell containing the text
• Start_num is the position in the cell where you want to start replacing the text
• Num_chars is the number of characters you want to replace
• New_text is the text you want to replace the old text with

If you wanted to change the first character in cell A1 to the letter B, this is what the formula would look like: =REPLACE(A1, 1, 1, “B”)

The SUBSTITUTE formula is similar to the REPLACE formula, but instead of providing the location of a character or the number of characters, we will only provide old text and new text. The SUBSTITUTE formula is SUBSTITUTE(text, old_text, new_text, [instance_num].

If you want to substitute a word in cell A1, your formula can look like this: =SUBSTITUTE(A1, “old word”, “new word”).

If the word you want to substitute occurs twice in a cell and you only want to change the second instance of the word, your formula would look like this: =SUBSTITUTE(A1, “old word”, “new word”, 2).

Trim

TRIM is used to remove extra spaces from the beginning, middle, and end of cells. This can be useful when trying to find duplicate values because extra spaces will prevent the same values from coming up as duplicates.

To use the TRIM formula, you just have to input the cell you want to fix. Your formula can look like this: =TRIM(A1).

VLOOKUP & HLOOKUP

The VLOOKUP formula searches for the value in the leftmost column of the table array and returns the value from the same row from the specified columns. The formula is VLOOKUP(lookup_value, table_array, col_index, range_lookup).

• Lookup_value is the value you are looking for that is present in the first column.
• Table_array is the range of the table, worksheet, or selected cell with multiple columns.
• Col_index is the position of the column that contains the value you’re looking for.
• Range_lookup is an optional part of the function. If it’s TRUE, it will return an approximate match, and if it’s FALSE, it will return an exact match.

Let’s say you want to look for some information about the value in cell A2 that is contained in column 6, and the range of your table is B2:H20. Your formula could look like this: =VLOOKUP(A1, B2:H20, 6, FALSE).

HLOOKUP searches for a value in the first row instead of the first column. It returns the value from the same column and the row that you specified. The formula is HLOOKUP(lookup_value, table_array, row_index, range_lookup).

Become an MS Excel Expert at Erie Institute of Technology (EIT)

If you’re interested in mastering MS Office programs like Excel to help today’s businesses stay organized and data-driven, you should consider enrolling in our Business Programs.

In this 9-month program, you’ll gain the network, database, and computer skills you need to work in a modern business organization.

Apply online today or learn more about our other computer and design programs in Erie.

About the Author: