Formulas & Functions

Getting Started

Formulas and functions differ in Excel by one key feature. Formulas are written by you, and functions pre-exist in the program. They have many similarities, as you will see in this page and the following pages covering formulas and functions. 

Here are some basic things to know about writing formulas in Excel. 

ALL formulas and functions must begin with an '='. Without an 'equals,' there will be no formula or function!

Basic mathematical operators that you can use when writing a formula are:

*   for multiplication
/    for division 
+   for addition
-    for subtraction

The use of these operators follows the basic order of operations: *, /, +, - (muliplication, division, addition, subtraction). You can use ( ) parentheses if you would like operations completed in a different order (e.g. to add two numbers before multiplying them by another number: put the parentheses around the summation).

Writing Formulas

To get practice writing a formula, let's return to the dataset from the U.S. Census in 2010 and let's assume that the information for percent change between 2000 and 2010 was missing (or hadn't been provided).

Column H is blank, but that is information we'd like to have. Fortunately, we can calculate it by writing a formula in Excel. You'll need to know how to calculate the percent change in order to write a formula for Excel to do it. In this particular case, we'll divide the amount of change between the two years (found in column G) and by the total population in 2000 (column E). And we'll do this for each individual Census Tract (each row).  

To write a formula, first click on the cell where you want the output to be. Then type '='.

You'll see the '=' in both the cell in which you are typing and in the formula bar. We could type "-1689 / 12780 * 100" following the '=' sign and this would give us the correct result, however, I'm just going to tell you flat out that you don't want to do that.

By writing the actual numbers into your function you are making Excel no better than a calculator, but there are a lot of ways that Excel can save you time and save you from making mistakes if you use it well. Instead of typing the actual numbers, you want to select the cells that contain the numbers. You can do this by clicking on the cell with the mouse OR by typing the cell number/letter reference. 

You'll see that the selected cells are color coded and that the colored cell number/letter references are referring to the cells that are outlined in the same color. This can make finding a cell used in a formula (or function!) easier when scanning a large dataset. Also, actual numbers remain black (like the *100). When you are done entering the function, press ENTER.

Using Cell Referents

It is important to use the cell referents instead of the actual numbers in case the numbers in the cells are changed. In this particular case, that shouldn't happen because this is Census data and it would be unnecessary (not to mention inappropriate and unethical) to change it. However, you could be managing a budget or designing an experiment and would like to test how different values will affect the total outcome. Then you will be very pleased to be able to change numbers in cells without having to rewrite your formulas!

Referencing a Cell in Another Worksheet

If you are working with multiple worksheets, you might want to refer to a cell in Sheet2 while you're working Sheet1. Let's use a simple summation as an example. To add up the value of C2 in your current worksheet and the value of C2 in Sheet2, simply type:

=C2+Sheet2!C2

Using Auto Fill with Formulas

Another reason it is important to use the cell referents instead of typing the number is because we can repeat a formula (and a function) using Auto Fill instead of typing each iteration. Need a refresher on Auto Fill?

By using Auto Fill all of the selected cells now contain the formula. Notice that the formula changes for each cell it is copied into. The formula in H10 refers to the information in cells for G10 and E10, whereas our original formula in H9 referred to information in cells in G9 and E9. 

This is great when we want all of the referents in a formula to change, but it isn't so great when we don't want them all to change. (If you are concerned about what's going on in cell H16, you can jump to it here, or you can continue along and get to it after a page or two...)

Relative VS Absolute Referencing

As just mentioned, when using Auto Fill the formula changes referents so that each new entered formula refers to different cells. This is called relative referencing, but there are times when you do not want the cell referents to change when copying the formula into a new cell.  You can change the formula to have absolute referencing in which a particular cell will always be referred to in the function. 

Let's work through an example. So let's say, instead of typing 100 into the formula, we typed it into a single cell and referenced it in the formula. Our new formula in H9 would look like this.

But if we used Auto Fill as before, the cells will look like this. 

Do you know why we get one correct output and then the rest of the cells have zeros? Double check your thinking here. 

The key is that we want the formula to always refer specifically to cell I6 since that cell contains the typed 100. To create an absolute reference we use the '$' inserted before the column letter, the row number, or both. What the '$' precedes will not change. So if we always want to reference column I, but change the row number, then the '$' should only precede the column letter. However, if we want the columns to change, but not the rows, the '$' should precede (have you got it?) the row. And if we want neither the column letter nor the row number to change (we want to always refer to a specific cell no matter what) the '$' should go in front of both the column letter and row number. 

To edit a formula in a cell, double click on the cell. This will allow you to edit the formula in the formula bar or in the cell itself. You can also single click on the cell and edit the formula in the formula bar. Just be aware that if you are editing the cell itself, the arrow keys will not navigate within the cell, but will navigate to adjacent cells. 

In our particular case, it is a specific cell that we don't want to change, so we could put the '$' in front of both the column letter and row number. 

Then when we Auto Fill the rest of the cells, they are always multiplied by I6.

However, if we put the '$' only in front of the column letter, our output would look the same as before:

This is because the row numbers are not absolute and will change. If our Auto Fill were occurring horizontally rather than vertically, then the row number would not change, even if it were a relative reference but the column letter would.

Additionally, because we are calculating the percent change vertically (we are NOT dragging the Auto Fill horizontally), we could get away with putting the '$' only in front of the row number instead of putting it in front of both. 

I encourage you to play around with absolute and relative references to get a sense of when each is most useful, or contact us if you have questions. 

Function Basics

Excel has a library of functions that allows you to quickly make specific calculations. Functions in Excel take arguments. Most functions will require at least one argument. Arguments can be a range of data, a specified output, or other parameters, depending on the particular function. Arguments are typically presented in parentheses following the function.

Here are a number of common functions that you are likely to use: 

• Average(number1, number2) - calculates the average (mean) of a range of cells
• Stdev(number1, number2) - calculates the standard deviation of a range of cells
• Sum(number1, number2) - adds all of the values of a range of cells
• Count(value1, value2) - counts the number of items in a range of cells (blank cells will not be counted & neither will cells that are formatted as text)
• Median(number1, number2) - calculates the median of a range of cells
• Mode(number1, number2) - calculates the mode of a range of cells
• Max(number1, number2) - gives the maximum value of a range of cells
• Min(number1, number2) - gives the minimum value of a range of cells
• If(logical_test, value_if_true, value_if_false ) - allows one to perform a logical test (e.g. IF A1 >= 5, then 0 if true, or else 1 if false)

This is not an exhaustive list, by any means. We'll talk more about finding functions in the section below, (which you can jump to here) but first we should go over how to use functions in Excel.

Inserting a Function

For example, let's say you wanted to take the average of the increase in population of the first ten census tracts which you would need some sort of logical argument for doing (such as demonstrating a function for a tutorial). Click on the cell where you want the average to appear. Type '=' (because without an 'equals,' the function won't run), then type average, and then the open parenthesis. Excel will prompt you for the arguments. 

The required arguments are specified as 'numbers', however you can also use cell referents, so long as the cells are formatted as Number or General. The square brackets [ ] around an argument mean that the argument is optional. For the average function you must have at least one cell referent (or number) to average (of course, the average of a single value isn't very interesting- what's the point of calculating it really?). When entering arguments you can enter each one and separate them with a comma, as the argument list is presented below. This would be quite time consuming if you have a ton of numbers to average. You can also select the entire range of cells that you want to average with the mouse.

You can also type "G9:G18" if you prefer to remain at the keyboard instead of switching back and forth between the keyboard and the mouse. 

In the formula bar we see the function and in the cell we see the output. Let's walk through another example using the sum function.

Excel Functions: Sum Example

For example, let's say a small grocery store (or maybe your local bodega) was interested in how much customers spent on eggs and milk each day in a given week. After setting up the headers...

Insert the data.

We can also format the entered data by selecting the '$' in the Number group. We can extend the formatting to the Total cells too. 

We can also widen column A so that all of Wednesday isn't clipped off. But finally, let's use a function!

In the Total column, we want the sum of the amount spent on eggs and milk. In cell D2 we type "=sum". As we type, Excel will give us a number of prompts for possible functions.

We're sticking to the basic sum function, so we'll continue by typing the open parenthesis. Then select cells B2 & C2 with the mouse (or by typing them).

Notice that the function also appears in the formula bar. Then we can either type the ending parenthesis or simply press ENTER. Typically, we don't need to type the end parenthesis, because Excel will automatically add it for us, UNLESS we are writing a formula instead of using one Excel provides (or if we have a mistake in our argument list. We'll go over errors on the next page). After pressing ENTER we see this:

Using Auto Fill with Functions

Now we want to sum the information for the rest of the days of the week. We can repeat what we just did: type "=sum(" and then select the cells, OR we can use Auto Fill. Remember that? Click on the cell with the function (in this case D2) and move the mouse over the bottom right corner until it makes a plus sign, and then click and hold down the mouse to select all the cells into which you want to copy the function. You'll see a blue outline showing the cells that are being selected. 

When you release the mouse, those cells will have the function copied into them, BUT changed to refer to the cells in each row. For example, look at D3 in the formula bar. 

Essentially, Excel is copying: Dx = Bx + Cx, where x is the row number. You can also use Auto Fill horizontally. In this particular example, it doesn't make sense, but just to see how it works: select the cell with the function (in this case D2, again) and move the mouse over the bottom right corner to get the plus sign. Then click and hold the mouse down and drag to the right. You'll see the blue outline highlighting the selected cells. 

When you release the mouse, those cells will have the function. See E2 in the formula bar?

In this case, instead of copying: Dx = Bx + Cx, Excel is copying Y2 = (Y-2)2 + (Y-1)2, where Y represents the column letter and Y-1 represents one column letter to the left of Y, etc. To translate that- the columns change, not the rows. E2 is the sum of the cost of milk (C2) and the total cost of eggs and milk (D2), so in this dataset, summing this way makes absolutely no sense, but it is good to know how to do because there are datasets where it will make sense to Auto Fill horizontally instead of vertically. 

When I delete E2, I get this: 

Can you figure out why $96.09 repeats in F2 & G2? Click here to find out if you are right. 

Finding Functions

For more possible functions, check out this external link to Microsoft which lists a variety of functions categorized based on their potential usage found in Excel 2010. 

Additionally, you can search for functions in Excel. Go to the Formula tab and click on Reference. You will see a wide array of categories. Clicking on any of these categories will provide you with a list of related functions.

Excel for Mac nicely includes a description of what the function actually does beside its name.  

You'll need to know the arguments necessary for each new function. This is presented in the parentheses. Even when Excel says that the function takes a number you can use a cell referent with a number, as opposed to entering the specific number. Remember all the reasons it is better to use a cell referent for a formula? (If not, go back to this page.) The same reasons apply to functions.

To select a function, click "Formula Builder." When you select the function (in this case we are looking at the ABS function which will give us the absolute value of a number) it will allow you to enter the appropriate arguments at the bottom of the dialogue box. This can make navigating a new function much easier. 

We are entering the function in cell B1 and will use cell A1 as the argument. You can either click on the cell or you can type 'A1' into the argument box.

After clicking OK (or pressing ENTER) we get this:

Notice that the function is visible in the formula bar when we click on B2, but the cell shows the output. As with writing formulas, if you want to edit the function, you can do so in the formula bar or by double clicking on the cell. 

And don't forget: no 'equals' equals no function!

Finding Functions

Some Excel versions for Mac don't include the "Function Wizard" command, which allows you to search for a function by typing a brief description of what you want. However, you can use Formula Builder to search. 

For example, I might want to count a series of numbers. Beside the magniying glass, I type "count" and it brings up the "COUNT" function which does this.

When you single click on a function, the required arguments and a description of what it does appears below the box. You can also get additional help with a function (if you are connected to the internet) by clicking on the Help with this function link in the bottom left corner. Once you decide on the function you want, click OK and it will open the Function Arguments dialogue box that we saw when we were exploring the ABS function above.

Which function do you think would best be able to count the number of zeros in a set of data? Click here to find out. 

As you might have noticed in the previous two pages (Formula Basics and Using Excel's Functions) there are a lot of commonalities between functions and formulas. These commonalities also extend to the possible error messages that may occur.

Some errors appear in a single cell and suggest some issue with the referents used in the formula or function. Other errors occur because of a typo or issue in the formula or function itself.

Cell Referent Errors

Dividing by Zero

You may have noticed this error in one of the cells in the previous example calculating the percent change in the Census 2010 dataset. 

#DIV/0! means that you are trying to divide a number by zero which you can't do because the result is undefined. Since we can't do it, Excel can't do it either! So we get this error message. In this example, we can interpret what the error message means. The census tract in question didn't exist in 2000 so we can't measure the percent growth from 2000 to 2010. The original dataset used a dash (-) in cells where a new census tract was created. 

Cell Value Error

Another error message that can sometimes appear is #VALUE!

If we look at the formula bar, we see that someone (gee, I wonder who?) tried to divide information in cell H15 by information in cell H7. They must have made a mistake because what were the expecting to get dividing a number by text? What you get is this error message.

How to Fix an Error in a Cell

There are other error messages that can come up, but rather than trying to remember them all before they happen, you can learn about them when they appear. Notice that when we click on the cell with the error message that there is a small box with a yellow diamond and an '!' to the left of the cell. When we scroll the mouse over it an arrow appears, as does a text box explaining the error. 

Furthermore, if the byline box doesn't clarify what the problem is, you can click on the arrow and get a number of options. 

Some Windows versions of Excel have an option called "Show Calculation Steps...," which can make this very easy to resolve. However, some versions on Mac don't include this option. Clicking "Help on this Error" will at least give you some initial guidance. 

In this case, it turns out this person is trying to divide a number by text! How ridiculous!!!  But also easily resolved. 

Formula & Function Errors

While the previous errors are derived from the information in the cells referred to in the function or formula, other errors occur because of a problem in the function (or formula) itself. 

Circular Reference

It happens to the best of us. Sometimes when you are selecting a range of data for a function you misjudge where the dataset ends and quickly press ENTER. Then you see this:

The range of data for calculating the average includes the cell (D8) where the average function is written. OOPS! By clicking OK Excel 2011 gives us this:

But this is easily remedied.

• Click on the cell (D8) and edit the data range in the formula bar OR
• Double click on the cell (D8) and edit the data range in the cell OR
• Delete the function and start over- making sure to stop the range selection at D7 (or typing D2:D7).

Missing Parenthesis & Formula Errors

I know I've said previously in this tutorial that when using a function in Excel you do not need to type the ending parenthesis- and this is true, try it yourself!  But that doesn't mean the Missing Parenthesis error message doesn't pop up from time to time, and I'll show you some common issues that cause it. 

If you type a function and include no arguments, but press ENTER you'll get this:

Even though the real problem is that the function contains no arguments, the error message is referring to the missing parenthesis. But since the error message recommends that you check the function, you'll see that there is more of a problem than just a missing parenthesis.

If your formula or function contains an error, and something you typed is inconsistent with Excel's syntax, you'll get the formula error message (even for functions).

In this example the average function was not given a number as an argument:

The error message offers suggestions that include checking out the Function Wizard. All magical entities are important to keep track of, so if you're not sure what it is, check it out here.

REMEMBER: A function, when given the correct arguments, doesn't need the ending parenthesis, but a formula does. A formula is something you write and a function pre-exists in Excel.

You would expect that the Missing Parenthesis error message would pop up if you are writing a formula that includes parentheses (for example, if you are adding or subtacting two numbers before multiplying or dividing them) and forget one end of the pair (either the open '(' or ending ')' parentheses), but check this out:

Instead Excel is helping us out by offering a proposed correction. Sometimes Excel's corrections are exactly what you want, but other times, like in this case, they miss the mark. 

CAUTION: Be wary that if you quickly press ENTER when seeing this message it will alter your formula, whereas, if you quickly press ENTER with the previous two error messages, they will give you the opportunity to edit your function. 

If you select NO to give yourself the option of editing the formula (which, in this case, you should), you will then get the Missing Parenthesis error message. 

Errors happen- it is a part of life and a potential side effect of being human. As long as you pay attention to the error messages that pop up, you will increase the accuracy of your data in Excel. 

However, there are errors that occur that Excel doesn't register, especially when manually entering data. Always be vigilant and double check, triple check, QUADRUPLE check your work!!

This page provides examples a few useful statistical functions you can use in Excel. Let's say you want to calculate two means, compare their standard deviations, and run a t-test to see if they're statistically distinguishable. 

Calculating the Mean

The AVERAGE function can calculate the mean of a distribution. Simply type "=AVERAGE(" and then fill in the column of numbers (which you can highlight as seen below). 

Then close the parentheses and hit enter. This will calculate the mean: 

You can click the cell and drag it over to calculate the mean for the next column as well:

Now we see that Column1 has a mean of 2.45 and Column2 has a mean of 3.3. 

Standard Deviation

You can get the standard deviation using the STDEV function. It uses the same logic as the AVERAGE function:

Close the brackets and hit enter to get the standard deviation for Column1. Then drag the cell to get the standard deviation for Column2 as well:

Column1 has a slightly larger standard deviation than Column2, but not by much. 

T-Test

Now we have some basic descriptive information about these two columns, but what about statistical significance? You can conduct a t-test in Excel using the TTEST function. This function requires four arguments. The first two are two arrays (columns or rows) you want to compare. The third argument is whether you want to use a 1- or 2-tailed test. Finally, the fourth argument is what type of t-test (Excel offers 3 options) you want. 

As seen below, we'll use B2:B21 as our first array and C2:C21 as our second array. Let's go with 2-tails and call for a type 2 t-test (more information about types can be found here):

Close the parentheses and hit enter to get a p-value:

A p-value of .04 tells us this difference is statistically significant at the standard .05 level.

Why use Macros?

At its simplest, a macro is a recording of all of the commands and actions that you use to perform a complex task. A macro will let you repeat these tasks with a single click. Macros are stored as simple bits of code within Excel and they allow you to create complex functions, loop over entries in your spreadsheet, or quickly change formatting in your data. If you find yourself repeating the same set of tasks over and over again while dealing with a dataset, it can often be quicker to create a macro. One simple example of a macro is given here (for formatting text in cells), but macros can be used to format figures, clean datasets, or many other complex tasks. 

Getting Started

Before you can create your first macro, you need to activate the "Developer" ribbon in Excel. First you need to find the icon at the right most of the ribbon, and select Ribbon Preference. In the pop-up window, scoll down the panel and check the "Developer" box at the bottom and click OK. This will allow you to select the developer ribbon from the tabs at the top of the Excel window.

Recording Macros

First, look at the options in the "Visual Basic" section of the Developer ribbon.

Select a cell in Excel you want to manipulate. As an example, we will create a macro that changes the font and formatting of text in a cell.

To record your macro, click the Record button. This brings up a set of macro recording options. Here you can enter a name for your macro, a description for what it does, a keyboard shortcut, and specify where you would like to store it. Usually it is sufficient to store your macro in "This Workbook". This allows it to be used for the currently open spreadsheet. However, if it is something that you will use across many different datasets, you can save it either in a new workbook, or in a personal workbook that stores all of your macros on your computer.  

Once you have entered the name for you macro, click OK.This will bring you back to the spreadsheet. Perform whatever alterations to the dataset you wish to record. For our example, we will change the font, size, orientation (refresher on this here), and spacing of the text.

Once you are satisfied, return to the Developer ribbon and click the Record button again. This will save your macro. The action required navigating 4 separate menus in Excel using multiple clicks. We can now repeat it for any cell with one click or keyboard shortcut.

Note: earlier in the tutorial we learned about using "$" before a cell entry to make an absolute cell reference versus a relative reference. By default, when you record a macro and refer to a cell value, it records that as an absolute reference. This means that if you run your macro again in a different position, it will always refer to the cell in the macro. By selecting "Relative References" you will be able to make your macros run using values relative to the cells where you are running it. 

Running your Macro

Clicking the Macros button will bring up a list of all of the macros you have stored.

Here you will see the full list of macros associated with this workbook. Selecting any macro and clicking Run will run the macro in the selected cell. If you assigned a keyboard shortcut when you named your macro, then you can also use the keyboard shortcut to activate your macro. 

Macros are stored in a language called Visual Basic. Selecting Edit will allow you to edit the code for a macro. This can be a quick way to correct simple errors in a macro. If you wish to delete a macro, simply click Delete.