Excel Formulas tab

Excel formulas and functions – All you want to know about them

If you like this tutorial, please spread the word!

Anyone who uses Microsoft applications would agree that Excel formulas and functions are one of the most important features offered by these applications.

Therefore, we will detail in this Excel tutorial everything you need to learn about Microsoft Excel formulas and functions: how to create Excel formulas, how to use them along with functions, how to edit them, how to copy them to a column or row, and how to show or trace the cells containing them.

Contents:

1- What are Excel Formulas and Functions

Formulas and functions are a key feature in the daily use of Excel, whether for experienced users or even Excel beginners.

You can think of Excel formulas as usual mathematical formulas. They have operators and operands, and they allow to perform calculations.

They can either be simple, with just operands and operators, or include predefined functions already available in Excel to offer more complexity.

But, before diving into more details about Excel formulas and functions, let’s first start with some guidelines that will allow you to better benefit from this tutorial and to make good use of Excel formulas in the future.

a- Guidelines about Excel formulas

  • Excel formulas can be entered in the cell itself or in the “Formula Bar”.
Entering an Excel formula in the formula bar
Entering an Excel formula in the “Formula Bar”
  • All Excel formulas start with an equal sign (=).
    It is automatically inserted when you create an Excel formula by inserting a function from the ribbon; otherwise, if you manually create a formula, you should enter the equel sign by yourself.
  • Cell References:
    A cell in Excel is referenced by its column and row; meaning we can’t refer to a cell in Excel without its reference. Cell references are extremely important in Excel formulas.
    For example, in the illustration below, we are selecting the cell that intersects column D and row 5. So, the reference of our cell is D5, as you can see in the “Name box“.
Cell reference in Excel
Cell reference in Excel
  • The colon mark (:) indicates a range of cells. A range of cells is referenced by its first and last cell references, separated by a colon.
    e.g. (B3:E9) means the range of cells from B3 to E9.
Range of cells in Excel
  • You can validate your formula by either pressing Enter on the keyboard or clicking the “checkmark” on the left of the “Formula Bar”.
Validate Excel formula by clicking the checkmark
Validating an Excel formula by clicking the check mark

As you may experience, it’s much better to directly press Enter after finishing your formula than to click with the mouse!

  • Simple Excel formulas can contain operators (+;-;*;/;^), numbers (12; 1689.56; etc), and cells references (A3; B68; etc);
    In addition to these elements, complex Excel formulas can contain functions, ranges of cells (SUM(A2:A15); PRODUCT(B3:F17); etc), and arguments.
    More on that right below…

b- Simple Excel formulas

Simple Excel formulas are simple formulas that contain just operands and operators.

In a basic Excel formula, an operand is a cell reference or a number.

To better understand what are really Excel formulas in their simple form, let’s consider the range A2:C7 (from the cell A2 to C7) and provide some examples to perform calculations with the help of simple Excel formulas.

Note: The formulas are of course to be entered into the cells’ results that you designate yourself; in our following examples, the formulas were entered in the respective cells of the D column (see the screenshot below).
And of course, ignore column F in our screenshot; it is there just for explanatory purposes.

– Examples of simple Excel formulas:

  • Addition formula: =A2+B2+C2
  • Subtraction formula: =A3-B3
  • Division formula: =C4/B4
  • Multiplication formula: =A5*B5*7.26
  • Power formula: =A6^C6
  • Mixed formula: =(A7+B7)/C7*100
Simple Excel formulas examples
Some simple Excel formulas examples. The real formulas are entered and executed in the D column; the F column is just there to show you the corresponding formulas that were used in the D column to get the results.

In all these simple Excel formulas, we have an equal sign (as all Excel formulas), operators (mathematical symbols), and operands (cells’ references and constants).

For example:

  • In the first formula =A2+B2+C2: the operator is the plus symbol (+) for addition, and the operands are the cells references A2, B2, and C2.
  • In the last formula =(A7+B7)/C7*100: the operators are the plus symbol (+) for addition, the slash symbol for division, and the asterisk symbol for multiplication; and the operands are the cells references A7, B7, and C7, and the constant 100.

c- Excel functions

Built-in functions can be of great help in your formulas. They provide a very useful way to perform calculations, and each one of them has its own particular layout and arguments, which is called “Syntax“.

But a general syntax for Excel formulas with functions would be:

=FUNCTION_NAME(arguments_if_any)

with 3 forms:

  • Function with no arguments: =FUNCTION_NAME()
  • Function with one argument: =FUNCTION_NAME(one_argument)
  • Function with multiple arguments: =FUNCTION_NAME(argument1, argument2, argument3...)

Arguments of a function are any data this function would need to perform its task of calculation; and depending on the function used and the formula, these arguments could be a cell reference, a range of cells, a number, a text, or even another function.

Function arguments are always contained within parentheses and separated from each other with a comma.

As we have done with simple formulas, and to better understand what are really Excel formulas with functions, let’s consider the range A2:C5 (from the cell A2 to C5) and provide some examples to perform calculations with the help of some Excel formulas with functions:

– Examples of Excel formulas with functions:

  • Function with no arguments:
    • Function PI that returns the value of the mathematical constant Pi: =PI()
    • Function TODAY that returns today’s date: =TODAY()
  • Function with one argument:
    • Summation formula using SUM function: =SUM(A2:A5)
    • Average calculation formula using AVERAGE function: =AVERAGE(A2:A5)
    • Formula of finding the greatest value of a range using MAX function: =MAX(A2:A5)
  • Function with multiple arguments:
    • Summation formula of 2 ranges of cells using SUM function: =SUM(A2:A5, C2:C5)
    • Summation formula of a range of cells based on a condition, using SUMIF: =SUMIF(A2:A5, E2, C2:C5)

2- How to create an Excel formula

In the subject of creating a formula in Excel, we will discuss 2 aspects: WHERE to create the formula, and HOW to do it.

a- Where to create an Excel formula

There are 2 placements where you can create your formula :

Inside the output cell itself (the cell where you want the result to be), by clicking on it.

In the Formula bar after selecting/clicking the output cell.

Entering an Excel formula in the formula bar
Entering an Excel formula in the “Formula Bar”

b- How to create an Excel formula

There are 3 ways of creating a formula in Excel:

– Manually writing it in the output cell itself or in the Formula bar

This method can be used for ALL Excel formulas.

Well, you just need to write it yourself in the output cell or, after selecting this cell, in the Formula bar; then press Enter on the keyboard.

As an example, let us consider the SUM function:

If we need to get the summation of values of all the cells from A1 to A10, we just have to write in the cell where we need to output our result, the following formula:

=SUM(A1:A10)

And hit Enter.

That’s it; so simple!

Excel formula manually

– Clicking on the fx button (Insert Function) next to the Formula bar

Excel - "Insert Function" button
Excel – “Insert Function” button

This method is to be used for Excel formulas with functions.

You should first select the output cell, then click on the “Insert Function” button; Excel will then display the “Insert Function” dialog box (see the image below), where you can select or search for the function you need.

"Insert Function" dialog box in Excel
“Insert Function” dialog box

Once you select a function, you’ll get, below the functions list, the syntax of this function and a description of what it does; see the screenshot above.

When you click OK, Excel will display another dialog box called “Function Arguments” where you can enter the arguments (if any) for the function in the required fields. In the example illustrated below, we chose the “IF function”.

Excel - "Function Arguments" dialog box
“Function Arguments” dialog box – Example of the IF function

Information in the lower section of this dialog box explains what this function is for (purpose of the function) and what information is needed to perform it (Arguments definition). Click in each of the value boxes and read the information about it.

I won’t go further here as there are lots of functions in Excel and each function has its own syntax, which will go beyond the scope of this general and introductory article; but you’ll find more detail explanations in other Excel tutorials on this website.

– Using the Formulas tab of the ribbon

Excel - Insert formula

This method too is to be used for Excel formulas with functions.

As you can see from the above image, Excel makes it easy for users to find the right functions by grouping them into some categories.

Excel has function groups like financial, date time, math and trigonometry, statistical, lookup, database, text, logical, information, engineering and many more.

Here again, once you choose a function from a category (except the “AutoSum” drop-down list), Excel will display the “Function Arguments” where you can enter the arguments (if any) for the function in the required fields.

"Function Arguments" dialog box - AND function as an example
“Function Arguments” dialog box – AND function as an example

As explained in the previous section “Clicking on the fx button (Insert Function) next to the Formula bar”, the “Function Arguments” dialog box displays in its bottom a description of the function’s purpose and a definition of the arguments, if any.

As for the “AutoSum” category in the “Formulas” tab of the ribbon, Excel will directly insert the function in the output cell waiting for you to manually enter its arguments.

3- How to edit an Excel formula

Sometimes we may want to modify an existing formula. To do it, just follow the next steps:

  • Select the cell containing the formula you want to edit.
  • Click in the “Formula bar” to modify the formula.
    You can also double-click the cell to view and modify the formula directly within the cell.
  • A border will appear around any referenced cells.
  • When you’re finished, press Enter on your keyboard or click the checkmark in the formula bar.
  • The formula will be updated, and the new value will be displayed in the cell.

– How to cancel changes while editing a formula

While editing your formula, you may make some accidental or unwanted changes to it; if this occurs, you can just press Esc key of your keyboard to undo them.

4- How to copy an Excel formula to a column or row

a- Copy Excel formula using the fill handle

To copy an Excel formula to a column or row, just:

  • Select the cell containing your formula by clicking on it.
  • Drag the fill handle to copy the formula to the desired range of cells in a row or column. The fill handle is the small square in the bottom-right of a selected cell; see the screenshot below.
    Or you can just double-click the fill handle and Excel will automatically fill the remaining cells of the range, provided there are no merged cells in the way.
Copy Excel formula by dragging the fill handle
Copy Excel formula by dragging the fill handle

b- Copy Excel formula using the fill command of the ribbon

To copy an Excel formula to a column or row, you can also use the “Fill” command from the ribbon.

Start by entering your formula into the first cell of the whole range you want to copy it to; then, select this range of cells, including the first one containing your formula. After that, go to the “Home” tab of the ribbon, and in the group of commands “Editing” click on the “Fill” drop-down list, and choose the right option for your case (“Down“, “Right“, “Up“, or “Left“).

Copy Excel formula using the fill command of the ribbon
Copy Excel formula using the fill command of the ribbon

Meaning, if you want to copy a formula across a column, choose “Down” or “Up” depending on the location of the cell you enter the formula in; the first cell of the range, or the last one.

Similarly, if you want to copy a formula across a row, choose “Right” or “Left” depending on the location of the cell you enter the formula in; the first cell of the range, or the last one.

c- A shortcut to copy Excel formula

Dragging the fill handle across an Excel column or row with too many cells would be a bit of a hassle.

If this is your case, you would rather like to use a keyboard shortcut to quickly get the job done, wouldn’t you?

Fortunately, you can really do that in Excel: Just Select the whole range of cells in your column or row where you want to copy the formula to; then, enter your formula (if you have previously entered it before selecting the cells, just enter it again) and press the keys combination “Ctrl + Enter” (hold down the Ctrl key and press Enter of your keyboard).

Excel automatically fills the other cells of the range with the formula, respecting of course the cells references.

5- More on cell references in Excel

a- Types of cell references: relative, absolute, and mixed references

We couldn’t talk about different types of cell references (relative, absolute, and mixed references) earlier in this Excel tutorial, as this would make no sense if you don’t know why and how to copy an Excel formula; the absolute or mixed references of cells may be used only when copying an Excel formula to another cell or through a range of cells.

a1- Relative references in Excel

By default, a cell reference in Excel is relative; meaning, it depends on the position of the cell containing the formula, and of course on the cells referenced in it. So, if you copy the formula to another cell, this formula will adjust its references itself.

We already saw that in the previous section when copying an Excel formula through a column or row, but let’s see it again in an example:

Suppose that we have the following data of prices of products, and we want to calculate the commission rate of 70% for each price:

To calculate it for the price in the cell C4, we type in the result cell D4, the following formula:

=C4*70/100

Relative cell references in Excel
Relative cell references in Excel

Then, we want to copy the formula to the cells of the range D5:D11.

Let’s start by doing it just to the cell D5; we copy the formula from the cell D4 and paste it in the cell D5, and Excel changes the pasted formula to the following:

=C5*70/100

Which is great as that’s what we want really to have!

Copy Excel formula with relative cell references
Copying Excel formula with relative cell references

How is that?

Cell references are relative by default in Excel.

In our example, Excel knows from the formula in the first cell D4 that it uses a reference to the first cell on its left; so, when you paste the formula in the new cell D5, Excel considers the position of this new cell and accordingly changes the reference within it to match the cell on the left of D5, which is C5.

Let’s now check the other types of cell references, absolute and mixed ones.

a2- Absolute references in Excel

Unlike the relative references, absolute references in Excel don’t change with the position of the cell containing the formula; they are the same everywhere. So, nothing will change in a formula containing absolute references when you copy it to another cell or through a range of cells.

OK, but how to get an absolute reference since cell references are by default relative in Excel?

Just use the $ symbol to fix both the references to the column and to the row of the cell.

For example, to use absolute references in the Excel formula =C4, we should use two $ symbols; one, before the reference of the column, and one before the reference of the row: =$C$4.

Let’s take the following concrete example, where we want to calculate the percent of each price per the total.

We use the formula =C4/C12 in the result cell D4, as illustrated below.

need of absolute references in Excel

And we want now to copy the formula to the cells of the range D5:D11. When we copy it to the cell D5, we got an error of “dividing by zero or by an empty cell”; see the following screenshot:

Error due to relative references

This isn’t an error from Excel, but instead from our logic. When we copy the formula down, Excel just adjusts the relative references in it which leads to an error since the cell C13 doesn’t have any content in it. Our initial intent was to divide all the cells by the same cell C12 containing the total of the prices. To get this right while copying the formula to other cells, we should fix the reference to the cell C12 in the formula with the use of the $ symbol.

The formula in the first cell D4 should then be: =C5/$C$12

Here is the result after editing the formula in the cell D4 with absolute references and copying it through the range of cells D5:D11.

Copy Excel formula with absolute cell references

a3- Mixed references in Excel

As their name suggests, mixed references are a mix of relative and absolute references: they either contain a relative reference to column and absolute reference to row, or relative reference to row and absolute reference to column.

For example:

If the formula =C4 has relative references, and the formula =$C$4 has absolute references, the formula with mixed references would be =C$4 or =$C4.

With the formula =C$4, we are fixing the reference to the row 4 and the column reference will change while copying the formula;

And, with the formula =$C4, we are fixing the reference to the column C and the row reference will change while copying the formula.

b- How to quickly apply a relative, absolute, or mixed reference in Excel

You know now that to indicate an absolute or mixed reference in an Excel formula, you should use the $ symbol. But, you need to know that you can have the same result without typing it yourself in the formula; just use the F4 key of your keyboard.

So, to quickly switch between relative, absolute and mixed references in an Excel formula, click in the “Formula Bar” on the reference part of your formula you want to change, and press the F4 key of your keyboard to change the reference; press it again and again until you get what you want.

F4 key changes cell references in Excel

c- Reference to another Excel sheet of the same workbook

I deliberately let this section until this stage, giving you enough time to well assimilate the basics concepts of Excel formulas and functions.

You may sometimes need to refer to some values from another sheet of the same workbook (refer to a cell or a range of cells); so, to do that:

You just need to use the name of the sheet with an exclamation point before the cell or the range of cells reference.

Let’s take the following example where we have two Excel sheets: “Summary” and “Prices”.

Prices and products data in an Excel sheet
  • Refer to a cell from another sheet of the same workbook

In the “Summary” sheet, we want to have the value of, let’s say, the price of “Product 1”.

So, in our result cell in the “Summary” sheet, which is the cell B2 (see the following screenshot), we will enter this formula:

=Prices!C4

Meaning, we’re getting the value of the cell C4 from the sheet “Prices”, into our cell B2 of the “Summary” sheet.

Refer in Excel formula to a cell from another sheet
Refer in Excel formula to a cell from another sheet
  • Refer to a range of cells from another sheet of the same workbook

Let’s now see another case from the same example where we will use a function in our formula, applied to a range of cells:

For example, the Excel SUM function to get, into our “Summary” sheet, the sum of the range of cells C4:C11 from “Prices” sheet.

So, in our result cell in the “Summary” sheet (cell B4), we’ll enter the following formula:

=SUM(Prices!C4:C11)

Meaning, we’re asking Excel to calculate the sum of the range of cells C4:C11 from “Prices” sheet and put the result into our result cell (B4) of the  “Summary” sheet.

Refer in Excel formula to a range of cells from another sheet
Refer in Excel formula to a range of cells from another sheet

Note about the name of the sheet in the formula:

Get in mind that the name of the sheet in an Excel formula is a text; so, for your formula to be accepted by Excel, the name of the sheet used in should be of one word; otherwise if the name contain a space or a number, you should surround it by an apostrophe (‘).

Note that for a name as a number or containing a number, even if you don’t use the apostrophes, Excel will correct your formula and add them automatically!

Examples:

  • If our “Prices” sheet was called “Prices of Products”, the above formulas should be:

='Prices of Products'!C4

And,

=SUM('Prices of Products'!C4:C11)

  • If our “Prices” sheet was called “Prices2019”, the above formulas should be:

='Prices2019'!C4

And,

=SUM('Prices2019'!C4:C11)

  • If our “Prices” sheet was called “2019”, the above formulas should be:

='2019'!C4

And,

=SUM('2019'!C4:C11)

6- How to show formulas in Excel

a- How to display the formula of a cell in Excel

To view the formula of a single cell containing an Excel formula, you just need to select this cell (i.e. click on it), then view its formula in the “Formula Bar”.

View formula of a cell in Excel

– If you want to display the formula in the cell itself (in addition of course to the Formula bar) with highlighting all the cells involved in it:

Just click in it in the “Formula Bar”; Excel will highlight all the cells involved in the formula with different colors, so you can notice them easily.

Show cells involved in Excel formula

– A shortcut to show the formula of a cell and highlight all other involved cells

Click the cell containing the formula and press F2 key of your keyboard.

To get back to the values display, press Esc (Escape key) of your keyboard.

b- How to display all formulas of an Excel sheet

b1- Display all formulas in Excel using the ribbon

To display all formulas of an Excel sheet, go to the “Formulas” tab of the ribbon, then in the “Formula Auditing” group of commands, click on “Show Formulas“.

Show formulas in Excel using the ribbon

Excel shows formulas instead of calculated values in all the cells containing formulas.

For example, we have some formulas in the following Excel sheet that we want to display:

Here is the Excel sheet before:

Excel sheet containing some formulas
Excel sheet containing some formulas

Here is the Excel sheet after showing all its formulas:

Show formulas in Excel sheet
Excel sheet after showing formulas

b2- Display all formulas in Excel with a shortcut

You can use the Ctrl + ' shortcut to show all formulas of an Excel sheet.

Meaning, you hold down the Ctrl key and press the single quotation mark of your keyboard.

This shortcut is a toggle one; so, to get back to the values instead of the formulas, just use it again.

c- How to highlight all cells of an Excel sheet containing a formula

Sometimes, you would need to know which cells in your Excel sheet are containing a formula, without the need to show all these formulas.

The solution is to let Excel highlight them for you, using the following method:

Start by pressing the F5 key of your keyboard; Excel opens the “Go to” dialog box where you need to click on “Special“.

Excel "Go To" dialog box
Excel “Go To” dialog box

Excel opens another dialog box, the “Go To Special” one.

Excel "Go To Special" dialog box
Excel “Go To Special” dialog box

Select “Formulas” in this dialog box and click “OK“.

Excel will highlight all cells containing formulas in the sheet.

Here is how does this look in our example of Excel sheet:

Excel highlights cells containing formulas
Excel highlights cells containing formulas

d- How to trace the cells used by an Excel formula

Another feature that allows you to understand the behavior of an Excel formula in a cell and visually discern all cells involved in it, is to trace the precedents and the dependants of this cell.

You’ll find this feature in the “Formulas” tab of Excel ribbon, within the “Formula Auditing” group of commands.

Tracing the precedents of a cell containing an Excel formula is finding the cells used in the formula; i.e. affecting the result.

On the other hand, tracing the dependants of a cell is finding the cells using this particular cell in a formula; i.e. finding the cells affected by our selected cell.

Let’s put this into practice in the following sections.

d1- Trace the precedents of a cell containing an Excel formula

In the example illustrated below, we want to trace the precedents of the cell E19 containing the formula =SUM(E2:E18).

So, we select this cell E19 and click in the “Formulas” tab on the “Trace Precedents” command. Excel draws an arrow indicating the process of our formula, from the range of cells E2:E18 to the result cell E19.

Trace precedents of a cell containing an Excel formula
Trace precedents of a cell containing an Excel formula

Clicking one time again on the “Trace Precedents” command gets us to the second level and reveals the precedents of the range of cells E2:E18 itself.

See the result in our example:

Second level of tracing precedents of cell containing an Excel formula
Second level of tracing precedents of a cell containing an Excel formula

To cancel the effect of the “Trace Precedents” command and remove the arrows, go to the d3 section.

d2- Trace the dependants of a cell contributing to an Excel formula

In the same example illustrated right above, we will trace the dependants of the cell C2 for example, to see which formulas this cell is contributing to.

So, we select the cell C2 and click in the “Formulas” tab on the “Trace Dependants” command.

Excel draws two arrows from our cell C2 to the cell E2 and the cell C19, indicating that C2 is used in the formula contained in E2 (which is =C2*D2), as well as in the other formula contained in C19 (which is =SUM(C2:C18)).

Trace dependants of a cell in Excel
Trace dependants of a cell in Excel

To cancel the effect of the “Trace Dependants” command and remove the arrows, see the following section.

d3- How to remove the arrows of  tracing precedents and dependants of a cell

To cancel the effect of the “Trace Precedents” and/or the “Trace Dependants” commands, meaning to remove the arrows they drew on your data, just go the “Formula” tab of the ribbon, and in the “Formula Auditing” group of commands, click on the “Remove Arrows” command to remove all the arrows drawn by the “Trace Precedents” command and/or the “Trace Dependants”, or expand its drop-down list to choose the option you want.

Remove arrows of trace precedents and dependants in Excel
Remove arrows of trace precedents and dependants in Excel

If you like this tutorial, please spread the word!

Leave a Reply

Your email address will not be published. Required fields are marked *