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 ones covering formulas and functions.
Here are some basic things to know about writing formulas in Excel.
ALL formulas and functions must begin with an '='. No 'equals' equals no function! (or formula!)
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).
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. So 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.
It is important to use the cell referents instead of the actual numbers in case the numbers in the cells change. In this particular case, that shouldn't happen because this is a collected 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!
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:
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...)
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.
But we want the formula to always refer specifically to cell I6 since that is where we 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.