Dec 12, 2018 @ 12:45 am

How to Create Simple Formulas and Functions in Microsoft Excel?

Microsoft Excel is the most powerful member of the Office suite. With that power comes a steep learning curve. But it’s worth climbing it because spreadsheet skills are a must for self-respecting digital workers today. And don’t worry like everything else you can start from the very basics and learn Excel one tiny bit at a time.

One of the fundamental concepts in Excel is the difference between formulas and functions. So, let’s learn how to create them and see how they can help us manipulate the data in the rows and columns.

What is the Difference Between a Formula and Function in Excel

A formula (also called an equation) is something that you as the user create to calculate a value with the data in the cells.

A function is a built-in formula that already exists in Excel as pre-defined piece of code.

In short: A function is a built-in calculation, while a formula is a calculation defined by the user.

Both are building blocks of any Excel operation. A formula can make use of a single function or a combination of functions to calculate a result. A formula can also refer to cells, rows, columns, defined variables, and functions to create complex statements that manipulate the data in the spreadsheet.

Here’s an example:

A1 + A2 + A3 is a simple formula that calculates the values in the cells A1, A2, A3.

A Simple Excel Formula

 

Instead of laboriously adding each cell, you can use the SUM function to add up a range of cells: =SUM(A2:A3)

Microsoft Excel - SUM Function

How to Write a Formula

To enter a formula:

  1. Select a cell.
  2. To enter a formula, type an equal sign (=) in the cell.
  3. Type the cell references for the cell values you want to calculate. For example, type the formula =A1+A2+A3. Hit Enter.
  4. Instead of typing each cell address, you can also select cell A1, A2, and cell A3.
  5. If you change the value of any cell, Excel automatically re-calculates the total. Also, when you select a cell, Excel shows the value or formula of the cell in the formula bar. You can always click in the formula bar and edit the formula.

How to Write a Function

Instead of inserting each value to be summed as in the above formula, you can use the SUM function to add up a range of cells. There are several ways to insert and use functions, but let’s see the simplest one to begin with. The process is similar…

  1. Select a cell.
  2. To enter a function, type an equal sign (=) in the cell.
  3. Type SUM (A1:A3). Hit Enter.
  4. The SUM function simplifies the calculation. The part between the brackets (arguments) gives Excel the range A1:A3 as input. As you can see, the range includes the first and last cell of the range separated by a colon.
  5. You don’t have to type a function name in all caps. Microsoft Excel automatically capitalizes it once you finish the formula and press the Enter key to complete it.

SUM is a basic function. There are 467 functions in Excel and more can be used through third-party add-ons. Don’t worry—there are easier ways to use functions without memorising them all. Let’s get introduced to the Insert Function feature in Excel.

How to Use Insert Function in Excel

The Insert Function dialog box makes it easy to search and reuse the right functions from the library Excel provides. It also gives you an easier way to understand the arguments the function will take.

  1. To view the Insert Function dialog box, click the fx button on the Formula bar.

Microsoft Excel-The Insert Function Dialog

  1. Use the search box to type a brief description of the task you want the function to do. Click Go.
  2. A list of closest matches will be displayed in the Select a function box. You can use the dropdown under select a category to broaden your search or narrow it down. For instance, selecting All will display all Excel functions in an alphabetical order.

Microsoft Excel - Insert Function Arguments Screen

  1. Click a function name to see the function syntax and a brief description immediately below the Select a function.
  2. Double-click a function name to display the function and its arguments in the Function Arguments wizard, which helps you add correct arguments.

The Insert Function dialog box helps you paste the correct function with its arguments in a cell. Simpler functions can be entered manually, but you will feel better equipped to handle the more complex functions (like COUNTIF or COUNTIFS functions) with the dialog box. Excel couldn’t have made it any easier for beginners.

There are two other ways to display the dialog box.

  1. Click the Insert Function button on the Formulas tab.

Microsoft Excel-Insert Function Formula Tab

  1. Click the small arrow to the right of the AutoSum feature on the Formulas Ribbon. Select More Functions from the dropdown.

Microsoft Excel-Insert Function-AutoSum

The AutoSum Button for Simple Formulas

The AutoSum button on the Home tab is a timesaving feature for the simplest of calculations. You can also use it from the Formulas tab. This one-click formula button can add, average, count numbers, or find the minimum or maximum for a column, row, or group of cells.

  1. Click the AutoSum button and it will enter the SUM function. Click the arrow for the button, you will see the other common functions you can use for basic calculations.

Microsoft Excel-AutoSum Button

  1. Let’s say you have a column of numbers you would like to add. Click the cell where you want the total to be displayed. Then, click the AutoSum button. Excel will automatically detect the numbers you want to add and their cell references. The cells get selected and the function will be displayed like this:

Microsoft Excel-AutoSum Example

  1. Click Enter for the total.
  2. The other functions available in the AutoSum button will work in the same way.

Do Everyday Tasks with Simple Formulas and Functions

You don’t need to be a statistical guru to use Excel. Formulas and functions are a great help to manage everyday tasks like home budget management, income taxes, and loan amortization calculations. Simple formulas and functions are the first building blocks.