Excel Formula and Functions Tutorial
Essentials of Formulas and Functions in Excel
Formulas are what makes Microsoft Excel so powerful. You can use formulas to derive results that are dependent upon the data that is entered into the worksheet. If you change the data in the worksheet, then the results of the formulas will change. When a formula is entered into a cell, it performs a calculation and returns the result of the calculation into the cell. Formulas usually refer to other cells that contain data and use that data as components of the formula. If the data in the cells are changed, so will the result of the formula. The use of formulas that refer to worksheet cells for their components allows the formulas to do most of the work when you update the hard coded data within your worksheet. A formula can consists of the following elements:
Below are a few examples of some formulas:
- Mathematical Operators such as + (for addition ), - (for subtraction), / (for division) and * (for multiplication).
- Cell references
- Values and text
- Worksheet functions such as SUM, PRODUCT, QUOTIENT etc…
|=200*.15 ||This formulas multiplies 200 by .15 and will display a result of 30 which is 15% of 200. This formula consists of only values and does not refer to other cells. |
|=B2+C3 ||This formula refers to cells B2 and C3 and uses the addition operator to sum the two cells together. The result will be the sum of whatever values are entered into cells B2 and C3. If you change the values in the cells the result of the formula will change since it is derived from the values entered into the cells. |
|=SUM(A1:B4) ||This is a function that calculates the sum of the values entered into the cell range A1 through B4. |
|=Sales-Operating Expenses ||This formula subtracts the value entered into the cell name Operating Expenses from the value entered into the cell names Sales. |
Operators Used in Excel Formulas
|+ || Addition |
|- ||Subtraction |
| / || Division |
|* || Multiplication |
| ^ ||Exponential |
| & ||Concatenation |
| = || Logical Comparison (Equal to) |
| > ||Logical Comparison (greater than) |
| < || Logical Comparison (less than) |
| >= || Logical Comparison (greater than or equal to) |
| <= || Logical Comparison (less than or equal to) |
| <> ||Logical Comparison (not equal to) |
Excel Formula Operators
In Excel you can use as many operators as you want in a single formula. Below I have provided a few examples of operators actually being used and show you what the they actually do when applied in Excel.
|Operator||Formula||What it Does |
| & || ||Concatenates (joins) the two strings of content together. |
| & || ||Concatenates (joins) the string of content in cell A1 with the string of content in cell B1. |
| ^ || ||Raises 5 to the 3rd power which is also 5 multiplied by itself 5 times (5x5x5). |
| ^ || ||Raises 125 to the 3rd power which is also the cubed root of 125. |
| =A1 || ||If the value in cell A1 is less than the value in A2 then the formula will return TRUE. If the value in A1 is greater than or equal to the value in A2 then the formula will return FALSE. |
| =A1<=A2 || ||If the value in cell A1 is less than or equal to the value in A2 then the formula will return TRUE. If the value in A1 is greater than the value in A2 then the formula will return FALSE. |
| =A1<>A2 || ||If the value in cell A1 is not equal to the value in cell A2 then the formula will return TRUE. If the values in both cells are equal then the formula will return FALSE. |
Order of Operations Operator Precedence (PEDMAS)
When using formulas you must understand the order of operations. In excel this would mean that certain operators take precedence over other operators. Below is a table that lists the operators in order of how they will be performed by Excel.
Multiplication and division have the same precedence. You could perform multiplication or division first and it wouldn’t change the result of the formula. The same rule applies for addition and subtraction. Any formula within parenthesis takes priority over all other parts of the formula. Even if is not absolutely necessary to use parenthesis it can still be useful as it makes it easier to organize and read formulas. See the examples below. If the previous formula was performed without the parenthesis it would be performed as shown below. As just mentioned, it is useful to use parenthesis even when they are not absolutely necessary. Below is an example of a formula with 5 sets of parenthesis of which 4 sets are nested within the 5th set. =((A1*B1)+(A2*B2)+(A3*B3)+(A4*B4))*C1 This formula would return the same result if we were to remove all but the outside parenthesis, but the parenthesis make the formula much easier to read and understand. =(A1*B1+A2*B2+A3*B3+A4*B4)*C1
- Exponentiation (^): Order = 1
- Multiplication (*): Order = 2
- Division (/): Order = 2
- Addition (+): Order = 3
- Subtraction (-):Order = 3
- Concatenation (&):Order = 4
- Equal to (=): Order = 5
- Less than (<): Order = 5
- Greater than (>): Order = 5
PEDMAS There is an acronym that seems to really help people remember the order of operations. The acronym is PEDMAS which stands for Parenthesis, Exponentiation, Division, Multiplication, Addition, Subtraction.
Excel Formula Error If you make a mistake by forgetting or misplacing a parenthesis, Excel will return an error with a proposed correction. The error reads "Microsoft Excel found an error in the formula you entered. Do you want to accept the correction proposed below?" It may be tempting to simply accept the proposed formulas but be careful because it might not be the correct formula. If you get this error be sure to check your formula for mistakes and correct it manually.
Excel Worksheet Functions and Formulas
If you were to simply use operators for your Excel formulas, you would be limited to what you could do. Luckily Excel has worksheet functions that perform calculations that would be very difficult if not impossible to perform. Worksheet functions greatly simplify calculations for you allowing you to perform more powerful formulas in an efficient manner. Below is an example of a formula which uses operators and then we perform the same formula using a function. Here in cell P1 we added up all the values in cells A1 through O1. This we very time consuming as we had to manually click on each cell and enter the addition (+) operator. This would have been much easier if we just used the sum formula as shown below. Here in cell P1 we used the sum formula which is =SUM(A1:O1). This was much more efficient than manually using the operators as we did previously. Below is an example of us calculating the average of the values entered into cells A1 through I1 using the operators and not the function. If you look at the example below we performed the same calculation except we used the Average formula by entering =AVERAGE(A1:I1) in cell J1. This is a much cleaner, easier and error free way of performing the same calculation. Excel also offers functions that can eliminate manual editing that would otherwise take hours. An example of this is the PROPER function. The PROPER function converts content such as names and titles to their “proper” case structure. See the example below. There are also functions that allow you to perform calculations that would not be available using only the operators. An example of this is the MAX function. The MAX function detects the largest value in a range of cells. Another type of formula that Excel offers is the IF Function which is a decision making formula. The IF Function basically says "If "this" happens then "this" is the result and if "that" happens then "that" is the result." An example of the IF Function is shown below.
Excel Function Arguments One thing that all Excel worksheet functions have in common is that they use parenthesis. The information that is listed inside the parenthesis is known as the list of arguments. Different functions use different types of arguments. The types of arguments Excel functions use are
An example of a function that doesn’t use an argument is the TODAY function. The TODAY function still requires a set parenthesis, but you simply don’t enter any information inside the parenthesis. If you were to enter the TODAY function into a cell you would do by entering =TODAY() and the result would be today’s date. A function that uses a fixed set of arguments would be the Quotient argument. When you enter the Quotient formula into a cell then open the parenthesis, Excel will display a small information box describing how to enter your argument into the parenthesis. For the Quotient formula Excel requires a fixed set of two arguments, the numerator and the denominator. See the image below. An example of a formula the uses and indefinite number of arguments would be the PRODUCT formula. The PRODUCT formula finds the “product” or all the arguments by multiplying them together. You can enter as many arguments as you want but you just need to make sure that each argument is separated by a comma. An example of the product formula is shown below. In my example of the Product formula I entered nine arguments but I could have entered as many as I want as implied by the information box where is says “…[Number8], [Number9], [Number10], …)” An example of a formula the uses and indefinite number of arguments would be the PRODUCT formula. The PRODUCT formula finds the “product” or all the arguments by multiplying them together. You can enter as many arguments as you want but you just need to make sure that each argument is separated by a comma. An example of the product formula is shown below. In my example of the Product formula I entered nine arguments but I could have entered as many as I want as implied by the information box where is says “…[Number8], [Number9], [Number10], …)” The Absolute function is an example of a single argument function. In Excel the Absolute function is abbreviated with ABS, so you would enter =ABS into a cell and then in the parenthesis you would enter your argument which is a single number or a cell which contains a number. If we enter =ABS(-247) in a cell, the function will calculate and return the absolute value of -247 which is 247. See the example below.
- No Argument
- A fixed set of arguments
- An indefinite number of arguments
- A single argument
- Optional Arguments
Types of Excel Arguments Arguments in Excel can consist of cell references, literal values, literal text strings, expressions, or other functions.
- Cell reference: =PRODUCT(A1:A15)
- Literal value: =ABS(-247)
- Literal text string: =PROPER("this is the title of a book")
- Expression: =SQRT(15+10)
- Other functions: =SQRT(PRODUCT(A1:A5))
How to Insert Functions in Excel (Manual, AutoComplete, Function Library, Insert Function Dialog Box)
In Excel there are many different ways to enter formulas and functions into a cell. You can enter formulas and functions using one of the methods listed below.
- The Function Library
- Insert Function Dialog Box
Inserting Functions Manually If you have made it this far into our lessons, you should already know how to enter a function into a cell manually. We will remind you just in case. To insert a function manually you need to first enter the equal sign (=) into the cell follow by the function. You will then enter your arguments inside the parenthesis.
Inserting Functions Using AutoComplete In Excel 2007 Microsoft introduced the Formula AutoComplete Feature making entering formulas much easier and less error prone. Suppose you want to enter a formula but you don’t know the formula in Excel. When you begin to enter a formula into a cell the Formula AutoComplete Feature provides a quick easy list of formulas along with a descriptions of which each formula does. Below we have a list of players and their batting averages. The goal is to find the batting average of the team combined.
- In cell C13 enter the = and type the letter A after the equal sign. You will notice that a list appears providing you with a list of all functions that begin with the letter A.
- Click on the different functions in the list and you will see that descriptions is provided for each function.
- Continue scrolling through the list until you find the Average Function .
- Select and double click on the Average Function and it will be entered into your cell with open parenthesis. It is your job to fill in the arguments and close the parenthesis. You can see that there is a box that lets you know how you are supposed to enter the arguments into the parenthesis.
- Select the range of cells C3 through C12 and then close the parenthesis and press enter.
Inserting Function using the Function Library Another way to insert functions in Excel is to use the Function Library which is located in the ribbon under the Formula tab. The Function Library contains nine different buttons to help you find the formula you need. These buttons are AutoSum, Recently Used, Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trig, and More Functions.
AutoSum If you select a cell and click the AutoSum button it will find the sum of the values in the cells directly above or to the left of the cell. If you click on the AutoSum drop-down menu you will find a list of other commonly used functions. When you select one of these functions from the menu it will work just as the AutoSum button did except the function will be different. For example, if you selected a cell and then selected the Average function from the AutoSum menu, Excel would find the Average of all consecutive numerical values entered into the cells directly above or to the left of the cell.
Recently Use The Recently Used button is useful because it provides you with a list of the most recently used functions. More than likely you are to use these function again so Excel has made this button available to you to make your use of Excel more efficient.
Financial Here you can find financial functions such as the Net Present Value, Internal Rate of Return and many other formulas used in the world of Finance.
Logical You can browse this list to find logical function such as the IF function.
Text Here you will find functions related to text. These are function such as the PROPER function which converts names and titles to their correct grammatical format. These functions do not calculate numbers.
Date & Time The Date & Time button list functions used to make calculation related to dates and time. For example, you would find the NOW function in this list. The NOW function will always provide the exact date and time in the cell that it is inserted in.
Lookup & Reference Here you can find functions that help you work with arrays of data. You will find functions such as the VLOOKUP function which looks up a supplied value in the first column of a table, and returns the corresponding value from another column.
Math & Trig Here you can find all the functions and formulas you learned in your high school and college math classes. You will find functions such as the Square Root (SQRT) function which finds the square root of a number.
More Functions Under this button you will find even more categories of functions. Here Excel provides a list of Statistical functions, Engineering functions, Cube functions, Information functions and Compatibility functions.
Insert Function Dialog Box The Insert Function Dialog Box is a very useful tool for finding and inserting functions in Excel. With this dialog box you can type a description of the formula you need or you can select functions from a category. You can access the Insert Function Dialog Box in many different ways.
- Click on the Formula tab from the Function Library select Insert Function.
- Select the Insert Function command which is at the bottom of all the formulas list in the Function Library group.
- Click the Insert Function icon located directly to the left of the Formula Bar..
Excel Insert Function and Function Arguments Dialog Boxes
The most efficient way to locate the Insert Function Dialog Box is probably by clicking the Insert Function button (fx) button located directly to the left of the address bar. You will notice that the Insert Function Dialog Box has three fields.
To find and insert a function into a cell simply choose your category and then search the list of functions for the one you need. TIP: When you select function from the function list, Excel provides a description of the function directly under the Function List. Once you have located the function you need, simply select the function and click OK and the Function Arguments dialog box will appear.
- The Search for a Function text box: Here you can enter a description of the function you are looking for and Excel will list the functions in most relevant order.
- The Select a Function Category list: Here you can narrow your search for a function down into particular function categories.
- The Select a Function list: This is a list of function which is determined by the function category.
The Function Arguments Dialog Box The Function Arguments Dialog Box varies depending on the function you are inserting. Remember that there are different types of function arguments, therefore the Function Arguments Dialog Box adapts to the function you are inserting. For example, if you insert the SUM function your arguments would be an indeterminate number of arguments. The Function Argument Box for the SUM function initially appears with two text boxes for you to enter your arguments, Number1 and Number2. However, since the SUM function can consist of an infinite number of arguments, more text boxes will appear once you enter arguments into the last text box. For example, if we were to click in the text box for argument Number2, a text box for argument Number3 would appear and so on… In each argument box you can enter a literal number like 5, a cell address such as D7 a range of cells such as D10:D13, a calculation consisting of operators, literal values and/or cell references such as 5+D11, another function such as PRODUCT(D13,D15), or you can enter a combination of all as a single argument. There are other functions that only require a single argument. An example of this would be the SQRT function which finds the square root of a number. The Function Dialog Box for this function only has a single argument titled Number. This is because you are only able to find the square root of a number. In the argument box you can enter a literal number, a cell reference that contains a numeric value, a calculation using operators, literal number and/or cell references, or other functions. For the SQRT function you cannot simply enter a range of cells. If you want to find the square root of a range of cells you will need to enter the range of cells as a function so Excel know how you would like to calculate that range of cells. By simply entering a range of cells you are not telling Excel how you would like that range of cells to be calculated. Other functions require a fixed number of arguments, a good example would be the IF function. The IF requires three arguments, no less and no more. The three arguments are the logical test, the value if true and the value if false. If you leave an argument blank, Excel will treat it as a 0. In the Logical_test argument box you can only refer to a cell or enter a numeric value. For value_if_true and value_if_false you can enter a numeric value, refer to a cell, enter a formula, enter a function or enter text enclose in quotations. You cannot enter a range of cells. With the examples I have provided, you should be pretty familiar with the Functions Dialog Box. Remember that it is important to know the functions you are using and to experiment and practice to gain a better understanding.
How to Use Excel Functions and Cell Range Names
If your Excel worksheet consist of range names, you are able to use the range names with formulas. You can either select the named range of cells from a drop down list or by pressing F3. For my example I named my range of cells Batting_Average.
Using the Drop-Down Menu
- Enter your formula with open parenthesis and then type the first letter of the named range of cells you are going to use.
- From the list select your range of cells, close the parenthesis and press Enter.
- Enter your formula with Open parenthesis.
- Now press F3 and the Paste Name dialog box will appear. In this dialog box, double-click on named cell range, close the parenthesis and press Enter.
How to Search for and Insert Functions Using the Insert Function Dialog Box
The Insert Function Dialog Box is a very useful tool for finding and inserting functions in Excel. With the Insert Function Dialog Box you can select functions from a drop-down list when a function category is selected. If you are not sure about the function you need, you can type a description of the function in the Search for a function field and Excel will try to find a matching function. You can access the Insert Function Dialog Box in many different ways.
As mentioned, you are able to search for a function using the Search for a function feature which is located at the Insert Function Dialog Box. To use the Insert Function Dialog Box to search for and insert a function simply follow the step listed below. Step 1: Open the Insert Function Dialog Box by clicking on the Insert Function button (Fx) directly to the left of the address bar. Step 2: Enter a description of the function you are looking for into the Search for a function box and click GO. In the function list Excel will provide a list of Recommended Functions listed in order or relevance. The first function in the list is most likely the function you need. Step 3: Select the function and click OK. The Function Arguments dialog box will appear. Step 4: Enter you arguments in the Function Arguments dialog box or you . The arguments are different depending on the function. The Function Arguments dialog box will off your text boxes for each argument. Step 5: After all your arguments have been entered into the Function Arguments dialog box click OK.
- Click on the Formula tab from the Function Library select Insert Function.
- Select the Insert Function command which is at the bottom of all the formulas list in the Function Library group.
- Click the Insert Function icon located directly to the left of the Formula Bar..
Cell Reference for Formulas and Functions
Most of your formulas will refer to cells or cell ranges for their function arguments. By referring to cell you make your worksheet dynamic, efficient and less error prone. So what exactly is cell reference? When you refer to a cell for a function argument or a component of a formula the result of the formula or function will be dependent on the value entered into the cells that the formula or function refers to. If we were to enter =A1+B1 into a cell then the result would be the value inside Cell A1 plus the value entered into cell B1. If we changed the values entered into either cell the result of the formulas would also change since it is dependent on the values in the referred cells. There are three different types of cell reference: Relative cell reference: If you copy and paste a formula that uses a relative cell reference the cells that the formulas refers to will change relative to the cell it was pasted into. For example if we enter =SQRT(A1) into cell A2 and then copy and paste that formula into cell B2 it will then refer to cell B1 for its argument. Since we moved the formula one column to the right the cell that it referred to for its argument also moved one column to the right. Locked cell reference: When we lock a cell reference this means that no matter where we copy and paste the formula or function the cell reference will not change. The way that we lock cell references is by inserting the $ symbol before the column letter and the row number. If we entered =SQRT($A$1) into a cell and then copy and pasted that function into another cell, it would still refer to cell A1 since the cell reference is lock by the $ symbols. Mixed of locked and relative cell reference: We can also mix locked and relative. For example if we entered =SQRT($A1) into a cell and the copy and pasted the function into a different cell the column would always be locked in column A however the row would change relative to the row that the function is pasted. For example we could paste the function several columns to the right but remain in the same row it was initially inserted and it would still refer to cell A1. This is because we remained in the same row and only change columns. Since the column was locked the cell reference didn’t change even though we pasted it several column to the right. However, if we were to move down one row then the function would refer to cell A2 since the row number of the cell reference is relative. In the screenshot below we are referring to the previous months balance in cell B19 and the adding the current months Income in cell C17 and subtracting the current month’s expenses in cell C18. If we use to autofill feature to complete this budget the cells that the formula refers to will be the same relative to the location of the cell they are pasted into. Now look at the picture below where I used the Show Formulas feature. You can see that a formula is entered into all columns of the Balance row. The formulas are the same except that the cells the formulas refer to are relative to their location. The formula is could be stated as “one column to the left + two rows above – one row above” If you take a look at the screen shot below you can see that we added another aspect to this budget and the formula. We add the factor that the account the balance is in is earning a small interest rate. We added the interest to the formula for the balance by multiplying the previous months balance by the monthly interest rate in cell A23. You can see that we lock the cell reference to cell A23 for the interest rate by entering $A$23. Now no matter what cell we insert this formula, it will always refer to cell A23 for the interest rate.
How to Change the Type of Cell Reference You can change your type of cell reference manually by inserting or removing the $ symbols or you can use the cell reference keyboard shortcut by simply pressing the F4 key on your keyboard. First you point your mouse at the cell reference in your formula or function and then simply press F4. If you continuously press F4 you will cycle through all the different types of cell reference.
How to Refer to cell in a Different Worksheet You are not limited to referring to only cells within a single worksheet, you can also refers to cells in different worksheets and even entirely different workbooks. The notation used to refer to cells in a different worksheet is SheetName!CellAddress. If you were working in a workbook and wanted to refer to cell B2 in a sheet named Sheet2 then you could simply select cell B2 in Sheet2 or you could manually enter your cell reference by typing Sheet2!B2. Your sheet name will always be separated by an exclamation point (!). If your sheet name has a space in the name for example Sheet 2 then you must enclose the sheet name in single quotation marks. For example, ’Sheet 2’!B2.
How to Refer to Cells in a Different Excel Workbook If you want to refer to a cell within a different workbook you would refer to the cell using the following format: =[WorkbookName]SheetName!CellAddress So if we wanted to refer to cell B2 in Sheet2 in a workbook named Data.xlsx then we would refer to that cell by entering [Data.xlsx]Sheet2!B2. If there are spaces in the name of the other workbook or the worksheet in the other workbook then you will enter the workbook name and worksheet name into single quotations as follows: =’[Workbook Name]Sheet2’!CellAddress So if you were referring to cell B2 in a sheet names Sheet2 in a workbook named Original Data then your would refer to that cell by entering the following: =’[Original Data]Sheet2’!B2 It is easiest to refer to cells by select the cell reference with your mouse than it is to manually type the cell reference, especially when referring to a closed workbook that is save on your computer. This is because when the workbook is closed you must specify the path and folders that your workbook is saved in. For example, if we referred to the previous workbook except we didn’t have it open we would have to enter the following cell reference: =’C:\My Documents\[Original Data]Sheet2’!B2 You can also refer to a workbook that is located on another computer if the computers are linked in a network of computers. For example if you were going to refer to cell B2 in a workbook names Original Data that was located in Documents and the computer was named Computer1 then you would refer to that cell by inserting the following: =’\\Computer1\C:\Documents\[Original Data]Sheet2’!B2 Just remember that the path needs to be accurate or your cell reference won’t work. As you can see cell reference can actually get pretty complicated, it is best to always enter your cell reference select the cell you are referring to with your mouse. Excel will automatically make the correct changes to the cell reference when you do things link change the name of a worksheet or workbook and when you open and close workbooks or change the location of the files on your computer. When referring to a cell in a different workbook or a different worksheet Excel always uses the locked cell reference therefore if you copy and paste a formula that refers to another worksheet or workbook be sure to change the cell reference to relative.
Excel Formulas and Functions in the Tables Total Row
In this tutorial I will show how formulas and functions refer to cells within an Excel table. Below you can see that I have created a simple table that shows a list of employees and list each representative’s sales for 2011 and 2012. The first thing that I am going to show you is how simple it is to add a Total Row which will greatly simplify using formulas and functions in Excel tables. Step 1: Select any cell within the table and the Table Tools should appear providing you with a new tab in the ribbon that is labeled Design. Step 2: Click on Design. Step 3: Locate the Table Style Options group and check the Total Row box. Now in the very bottom row of the table a Total Row will appear with a value showing the sum of the very last column in the table, 6904000 in our case. Step 4 Select a cell within the Total Row and you will notice that a box with a downward arrow appears to the right. Click on this downward arrow and you will see a list of common summary functions that are capable of using a range of cells as a function argument. Now that you have your total row you can use functions without having to manually insert them. Excel simply does all the work for you. If you don’t see the function you need then click on the More Functions option and the Insert Function dialog box will appear. Here you can search for the function you need. When you select your function the Function Arguments dialog box will appear. If your function is capable of using a range of cells as an argument then it will automatically be inserted in the Function Arguments dialog box. However, if you use a function that cannot use a range of cell as an argument then you will need to specify your argument as we did in previous tutorial. (Search for Function Arguments to locate this lesson).
Function Arguments in the Total Row of an Excel Table Take a look in the formula bar when the total row for 2012 sales is selected. The formula shown in the formula bar is: =SUBTOTAL(109,[2012 Sales]) In the SUBTOTAL function the first argument, number 109, is a numeric representative of SUM and the second argument, 2012 Sales, represent the 2012 Sales column in our table which is all the values within the 2012 Sales column of our table.
Excel Tables Formula Cell Reference
Below you can see that we have a table that we used in our previous tutorial. It shows sales for each representative for 2011 and 2012. Suppose that we wanted to add another column in our table that shows the difference in sales for 2012 and 2011. To do this follow the steps below. Step 1: Activate cell E2 and type Sales Difference and then press Enter. Notice that Excel automatically added a new column to the table. Step 2: Activate cell E3 and enter = to let Excel know that you are about to insert a formula. Step 3: Now press the left arrow. You can see that Excel displays =[@[2012 Sales]] as your first argument. Step 4: Now insert the minus sign and then press the left arrow twice. You can see that Excel displays [@[2011 Sales]] as your second argument for your formula. Step 5: Press Enter and you will end your formula. Excel will automatically fill in the rest of the column with your formula to show the difference in sales from 2012 and 2011 for each sales representative. Notice that when the formula for the difference refers to the other cells within the table it does not display the cell address. It displays the formula as follows: =[@[2012 Sales]]-[@[2011 Sales]] Analyze the formula. The @ symbol stands for “this row.” So for the argument the @ symbol represents the row number like it would with formulas that don’t refer to a table. You will also notice that this formula consists of nested brackets. The reason 2010 Sales is enclosed in brackets is because the name of the table column has a space in it. However it would work without the brackets. If we would have named our column headers 2011Sale and 2012Sales instead of 2011 Sales and 2012 Sales then Excel would have not enclosed the column header names in brackets. We could have inserted the formula by manually typing =D3-C3 and Excel would have performed the correct calculation and filled in the rest of the cells in the column just as it did when we pointed to the cells.
Additional Cell Reference Techniques for Excel Tables When referring to tables or cells within tables you are able to refer to the table name and then additionally specify what part of the table you want to refer to. You could refer to the entire table, an individual column within the table, a specific cell within the table or other parts of the table. To do this you don’t need to create a name for the table because Excel automatically creates generic table names such as “Table1”. However you always do having the option of giving your tables custom names. Note: To name a table simply select a cell within the table, then click the Design Tab and locate the Properties group in the ribbon. In the properties group you will see that there is a box where you can add a table name. Simply type in the name you want to give to your table and press Enter. Take a look at the image below. In a cell outside of our table we began entering the sum formula and typed the letter T in for our first argument. As soon as we entered a letter for our first function argument Excel provided us with a list that consists of mainly functions however it also included our table in the list “Table 1”. All that I have to do is double click on “Table1” in the list and then close the function arguments parenthesis and the result of my function will be the sum of all numeric values within my table not including the column headers and the total row. My function reads =SUM(Table1). If we were to manually type this function into a cell it would provide the same result. Notice in the image above that we entered =SUM(Table1[. Here we began entering our function but have not yet finished entering our function arguments. For our first argument we typed in the name of our table “Table1” and then opened brackets. By entering the open bracket Excel provides a list of options. Here I can choose an individual column within Table1. I could choose to find the sum of the values within the 2011 Sales column, the 2012 Sales column, or the Sales Difference column. I can also choose to find the sum of all the data including headers and totals by selecting #All, I could find the sum of all numerical values not including the Total row or the headers by selecting #Data. If I wanted to find the sum of the values entered into the headers row I would select #Headers and if I wanted to find the sum of all the values in the Totals row I would select #Totals. To complete our function argument we will have to close the brackets and the parenthesis. If we wanted to find the sum of the 2011 Sales column within Table 1 our complete formula will be =SUM(Table1[2011 Sales]). If we wanted to find the sum of all the values in Table1 our complete formula would be =SUM(Table1[#All]). NOTE: Excel will treat all data that is not a numeric value as 0.
Excel Circular Reference and Enabling Iterations to Fix Circular Reference Formulas
A circular reference error arises when a formula either refers to the cell it is inserted in or refers to another cell that refers to its own cell. For example, if we were to enter =A1+B1+C1 into cell C1 we would get a circular reference warning. This is because we entered a formula into cell C1 that refers to cell C1 as an argument. The problem with formulas that contain circular references is that every time the formula is calculated, it must be calculated again. This could go on infinitely. When you enter a formula with a circular reference a warning box will appear as shown below. When this box appears you can click OK and Excel will just display a 0 as the result of your formula, or you can click Help and Excel will display a help screen providing more information about circular references. There are times when circular references are necessary. For example, suppose you owned a company and your employees’ salaries were a percentage of net profit. Since net profit is calculated as Revenues – Expenses and salaries are an expense, we would run into a circular reference problem. We must know what net profits are before we can calculate salaries and we must know salaries before we can calculate net profits. In this case we can use circular references to our advantage. Examine the data and formulas in the Excel screenshot provided below. I am using the show formulas feature so you can see what exactly is entered into the cells. As you can see we have a circular reference problem because Net Profits in cell B4 is =B1-B2-B3 but cell B3 contains a formula that refers to cell B4. The formulas entered into this worksheet are resolvable formulas and if the formula is repeated enough times a desired result can be reached for both cell B3 and cell B4. To solve this circular reference problem we simply need to enable iterative calculations. To enable iterative calculations follow the steps below: Step 1: Select File and then click on Options. The Excel options dialog box will appear. Step 2: In the Excel options dialog box click the Formulas tab. Step 3: In the Calculation Options group check the box where it says Enable iterative calculation. Now Excel will run the maximum allowed iterations until it reaches a result where Salaries are exactly 10% of Net Profit. Look at the screenshot below to see the final result.
Excel Formula Errors
Sometimes Excel will return a formula error when you enter a formula into a cell. Formula errors start with a hash mark (#) and signals that there is something wrong with your formula or something wrong with a cell that your formula refers to. Sometimes an error will cause a ripple effect throughout your entire workbook with multiple cells resulting in formula errors. This happens when all of these cells are dependent on that cell that contains the formula error.
- #DIV/0! Your formula is trying to divide by 0 or is trying to divide by an empty cell.
- #NAME? You will get this error if your formula uses a name that Excel doesn’t recognize. For example table name or cell range name that does not exist in your Excel workbook.
- #N/A This error occurs when the value is not available yet. Other functions such as the VLOOKUP function can return this error.
- #NULL! You will get the #NULL! error when a function argument is not separated with a comma, if the cell ranges or cell references are not separated by formula operators or if a cell range is not separated by a colon between its start and end point (for example A1:B3).
- #NUM! There is a problem with the number. For example you would get this error if you inserted the Square Root function and referred to a cell that contained a negative number.
- #REF! You will get this error if your formula refers to a cell that is invalid. This can happen when a cell has been deleted from your workbook.
- #VALUE! This error arises when a formula refers to a cell that contains a value that cannot be calculated. For example, If while inserting the SUM formula we referred to a cell that contained qualitative data such as a name, Excel would return this error because it is impossible to calculate the sum of a name.
How to Turn Off Automatic Calculations for Excel Formulas and Functions
By default Excel performs calculations automatically. When you make a change to the value inserted into a cell, all cell that are dependent on that value will automatically change. There may be times when you don’t want the formulas to change automatically. If this is the case you can turn off automatically calculated formulas. To turn off automatic calculations click on the File tab and select Options. The Options dialog box will appear. In the Options dialog box select the Formula tab and in the Calculation Options group click the Manual button under Workbook Calculation. Then click OK to close the Excel Options dialog box. Alternatively you can turn off automatic calculations by clicking Formula > Calculation > Calculation Options and finally selecting Manual from the Calculation Options drop-down menu. When you have your formulas set to Manual calculation mode, the status bar will say CALCULATE if you have uncalculated cells. To calculate cells simply press F9 to calculate all formulas in all open workbooks. If you want to only calculate formulas within the active workbook press Shift+9 and if you want to force a complete recalculation of all formulas press Ctrl+Alt+F9. Finally, it is important to understand the order in which the cells refer to each other because this is the order in which Excel will perform its calculations. For example, if you wanted to make changes to a hard coded value in cell A1, and cell A2 was dependent on cell A1 and cell A3 was dependent on cell A2 then after changing the value in cell A1 you must first recalculate cell A2 before you can recalculate cell A3.
Excel How to Define Cell Names and Refer to Named Cells in Formulas and Functions
Excel allows you to assign names to cells and cell ranges. This can be useful especially when referring to cells in formulas. It would probably be much easier to understand a function argument that reads Tax_Rate than it would be if it was simply the address of a cell such as Sheet1!A1. To understand this in more depth I’ll perform an example. First, examine the screenshot below. Here in a worksheet named Assumptions I have entered two assumptions. In cell B2 I have entered a tax rate and in cell B3 I have entered a commission rate. Both of these assumptions will be used to calculate the amount of taxes and salary a company will have to pay. This image above is from a different worksheet named Income which is located in the same workbook. Here we have a very consolidated income statement that is not yet complete. To complete this income statement we need to define the names of our cells in the Assumptions worksheet.
How to Name a Cell in Excel Step 1: First we need to activate the Assumptions worksheet. Step 2: Now we are going to activate the cell that I want to name. In our case it is cell B2 where I have inserted the tax rate. Step 3: Next we click on the Formula tab and click the Define Name button which is located in the Defined Names group in the ribbon. The New Name dialog box will appear. The New Name dialog box contains 4 boxes, Name, Scope, Comment & Refers to. In the Name box you can see that the name “Tax_Rate” has already been suggested. This is because Excel recognized that the cell directly to the left has the name Tax Rate inserted into it. You can override this suggestion by entering a different name. In the Scope box you can select the scope where you want the new name to be applied. You can select the entire workbook or you can apply the name to a specified worksheets within the workbook. In the comments box you can add any necessary comments about the name. Finally we have the Refer to box. The address of the cell or range of cells that we are referring to and naming is inserted here. The address will be in the following format, =WorksheetName!$ColumnLetter$RowNumber. In our case it’s =Assumptions!$B$2. Since we are naming a specific cell the cell reference is absolute. Step 4: Make the changes to the options in the New Name dialog box and click OK. NOTE: Defined names must start with either a letter or an underscore, cannot contain spaces or invalid characters and cannot be a name that conflicts with other names in the Excel workbook. Another way to access the New Name dialog box to name a cell is by using the shortcut menu. Simply activate the cell or range of cells that you want to name. Right-click and the short-cut menu will appear. From the short-cut menu select Define Names and the New Name dialog box will appear.
How to Refer to Named Cells in an Excel Formula Now that we have named our cells that are assumptions are inserted to, we can refer to the cells with the cell name instead of the cell address. Again I will do an example. Step 1: First I am going to activate the Income worksheet where I have my income statement. Step 2: Now I need to calculate my taxes. In cell B5 I am going to begin inserting my formula by typing =T. Before I can even finish inserting my formula, Excel provides a list for me to choose my arguments. In this list you can see the there is a tag that is labeled Tax_Rate. By selecting this I am select cell B2 in the Assumptions worksheet. Step 3: I am going to select Tax_Rate from the drop-down list by double clicking it. Then I will finish inserting my formula by multiplying Tax_Rate by the cell that contains Profits which is cell B4 in my example. The final formula to solve for the amount of taxes paid will be =Tax_Rate*B4. When we press Enter the result of our formula will be $271,181.82 which is 34% of the Profits entered into cell B4. Now we can calculate our Net Profits by subtracting Taxes from Profits resulting in $540,000 of net profits.
How to Name Formulas in Excel 2013 Tutorial
In Excel you can also define the name of a formula. The formula doesn’t even need to be inserted into a cell. So you can gain a better understanding I am going use an example. Suppose we have a worksheet that needs to calculate the semi-annual percentage rate. Instead of manually entering the semi-annual interest rate formula every time we need it. We could instead give a name to the formula. We could then refer to the formula name whenever we need the semi-annual interest rate. Below is a screen shot of a few cells where I have entered some constants. I have entered the annual percentage rate, the loan principle and the years to maturity. Now we are going to create and name a formula that will calculate the semi-annual interest rate. Follow the steps below Step 1: Select any cell within the worksheet. Step 2: Right-click and from the shortcut menu click on Define Name and the New Names dialog box will appear. Step 3: In the Name box enter the name you want to give to your formula. In our case it is going to be Semi_Annual_Rate. NOTE: Names must begin with a number or an underscore, cannot contain spaces or illegal characters and must not coflict with other names. Step 4: In the Refers to: box we are going to enter our formula. We first insert the equals sign (=), then point our mouse at cell B1 (our annual interest rate) and then type /2. Our formula should read =Sheet1!$B$1/2. When referring to cells in the New Name dialog box Excel always uses absolute cell reference. Step 5: Now we will click OK and any time we need to insert the semi-annual interest rate will can simple insert the name Semi_Annual_Rate instead of manually entering the formula. In the screen shot below you can see that we are entering a calculation that refers to names instead of formulas. The formula solves the semi-annual payment by multiplying the principle by the semi-annual interest rate. The name Principle refers to cell B2 and the name Semi_Annual_Rate refers to the formula we named.
Excel Cell Range Names References and Intersects
Just as you can name individual cells and formulas, you are also able to name cell references. Below you can see that I have a simple sales report that shows the quarterly sales for surf shops located in three different states, Florida, California and Hawaii. I am going to define names for cell ranges in this worksheet. I will give a name for each state's cell range of quarterly sales, and I will name each quarters range of cells. To name a cell range follow the steps below: Step 1: First we select the range of cells that we are going to name. Step 2: Now we right-click and select Define Name... from the short-cut menu. The New Name dialog box will appear. Since we have already label the row Florida, Excel recognized that the content in the row label is most likely the name we want to give to our cell range. In our case we are going to keep this name but we could change it to something different if we wanted to. Step 3: Now we simple click OK and we have named our cell range. We are going to continue doing this for all cell ranges for each column and each row. After we have named all cell ranges in our worksheet we can refer to cells by using cell names and reference operators for cell ranges.
Cell Reference Operators for Cell Ranges : (colon) Specifies a range of cells , (comma) Separates formula arguments, cell references and cell range references. (space) Specifies and intersect of cell ranges. If I were to enter =California Qtr_2 into a cell, Excel would return the value inserted into the cell where those two ranges intersect. My result will be the value inserted in cell C3 because cell C3 is the cell that the cell range Califonia and the cell range Qtr_2 intersect. In our cell reference we separated our two cell references with a space. If I we to enter =sum(Florida Qtr_1,California Qtr_1) into a cell, Excel would give me the sum of Florida's quarter 1 sales and California's quarter 1 sales. Notice the we used a comma to separate our two arguments. Florida Qtr_1 is cell B2 where Florida and Qtr_1 intersect and California Qtr_1 is cell B3 where California and Qtr_1 intersect. Basically our formula is finding the sum of cell B2 and cell B3.
Find the next tutorial in the links below.