Cell Address Types in Microsoft Excel on Windows

Hi, this is Dawn Bonizio and I’d like to explain to you when and how to use certain
cell address types in Microsoft Excel on Windows. As you may recall from Kelly’s screencast,
Excel allows you to use formulas in order to make calculations with data entered into
a spreadsheet. Here is an example of a useful way to use Excel to analyze data about candy
sales in an elementary school. To make this whole process efficient, you’ll
want to type a formula once and reuse or copy it into multiple cells without retyping it.
This is when you must know the difference between relative, absolute, and mixed cell
address types. After we work through creating a spreadsheet like the Candy Bar example,
you’ll better understand what I mean. Before we look at the details of the candy
bar spreadsheet, let’s take a look at a simpler one involving students’ test scores
that you’ve already seen in Kelly’s screencast on working with formulas in Excel. Rows are
horizontally across the template, numbered vertically on the left. Columns are vertically
up and down the template, and are labeled with letters in alphabetical order across
the top. “Student name” is located in the cell whose address is A1, “Test 1”
is located in B2, and John’s second test score is recorded in C3.
Recall from Kelly’s screencast that we can use the AVERAGE formula to get the test average
for the first student, and then fill it down for the remaining students. In order to fill
in the averages for the rest of the students, you’ll want to grab the bottom right corner
of F2 and drag down through the rest of column F all the way to Elizabeth’s information.
As you can see, when you swipe across the spreadsheet, the formula changed down column
F as needed in order to correctly calculate each student’s test score average.
“Relative” cell address types were used throughout this spreadsheet. “Relative”
means that as the formula was copied, each cell address’s row number changed relative
to the row where it was located. For example, see that B2 changed to B3 changed to B4 and
so on all the way down the column. Now, let’s return to the Candy Bar spreadsheet.
In cell C6, I created a formula that calculates the projected funds raised from candy bar
sales based on students per grade level and number of candy bars sold per student. This
formula in cell C6 reads “=B6*C5*C3”. So notice that this correctly calculates the
funds that would be raised by 60 kindergarteners if they sold 5 candy bars each. It multiplies
the number of students by the number of candy bars by the net profit per candy bar. Now
just like in our earlier example, I should be able to drag the bottom right corner of
this cell down through C11 and over to column F in order to fill out the rest of the spreadsheet.
Upon observations of the data in the spreadsheet, you should notice that something clearly went
wrong. “What went wrong” you ask? The calculations are inaccurate because relative
cell addresses were used in the spreadsheet. Sometimes, like in this example, we don’t
want the cell addresses in a formula to change row number and column letter relative to location.
In the formula typed into C6, I multiplied 60 Grade K students, 5 candy bars, and the
net profit per candy bar in order to figure out what the total funds raised would be if
all Grade K students sold 5 candy bars each. The number of Grade K students has the cell
address of B6. As I use my formula throughout the spreadsheet, I want to change the Students
per Grade Level, which is a change relative to row but not column. The 5 candy bars per
student has the cell address of C5. As I use my formula throughout the spreadsheet, I want
to change the Number of Candy Bars sold per student, which is a change relative to column
but not row. The net profit per candy bar has the cell address C3. As I use my formula
throughout the spreadsheet, I do not want this cell address to change relative to row
OR column. If you want a cell address to change throughout
a table relative to row but not column, you have to use the MIXED cell address type that
requires a dollar sign next to the column label. That dollar sign means that you do
not want the column letter to change relative to the column of the cell that the formula
is put into. If you want a cell address to change throughout a table relative to column
but not row, you have to use the MIXED cell address type that requires a dollar sign next
to the row label. That dollar sign means that you do not want the row number to change relative
to the row of the cell that the formula is put into.
If you want a cell address to stay the same regardless of the location where you reuse
a formula, you have to use an ABSOLUTE cell address with a dollar sign to the left of
the column letter and a dollar sign to the left of the row number.
In order to have my first cell address of the formula change relative to row but not
column, I have to use the proper mixed cell address type. The proper mixed cell address
in this case would be $B6. Notice that the RELATIVE cell addresses used previously did
not have any dollar signs, but in this mixed cell address there is a dollar sign to the
left of the column label. In order to have my next cell address of the
formula change relative to column but not row, I have to use the mixed cell address
C$5, with a dollar sign to the left of the ROW label.
In order to have the last cell address of my formula, the net profit per candy bar,
not change relative to NEITHER row NOR column, I have to use an ABSOLUTE cell address type.
This looks like $C$3, with dollar signs next to the ROW AND COLUMN.
Now I’ll be able to get the correct calculations in my table. The last step would be to drag
the bottom right corner of cell C6 down through the last row and across the last column of
the spreadsheet. WALA! Now we have the correct calculations, because
we used the correct cell addresses. For example, in cell D8 the formula is looking for the
number of students in cell B8 and the number of candy bars sold per student in D5 and multiplying
these values together by the net profit per candy bar located in C3. In all of the cells
that I copied the formula to, it is always looking to multiply what is in C3, the net
profit per candy bar. This all is exactly what we want to happen.
With a little knowledge of types of cell addresses, you only had to type in the formula once and
could then copy it all the way across and down the spreadsheet without retyping it for
each cell, and then, could end up with correct calculations! If you were wondering about
projected total funds raised from candy bar sales in this school, now you’ve got lots
of great information to work with. I hope that now you can understand what relative,
mixed, and absolute cell address types are and when you’ll want to use each type. With
this new knowledge, you can go ahead and complete accurate calculations quickly and easily.