The raw data we get often isn’t in precisely the form we would like to analyze. For example, our new vehicle data contains the number of new vehicles for each type of vehicle, but what if we wanted to see each type as a percent of total new vehicles instead?

We could calculate percentages one cell at a time using individual formulas, but this would result in a lot of time writing formulas instead of analyzing our data. Also, if we ever decided to change our analysis slightly, we’d be stuck updating each formula individually again. Fortunately, Excel lets us move, or drag, formulas while automatically updating cell references.

Let’s take a look at what this means. Suppose the cell B2 contains the formula =A4. If we move this formula from B2 to D1, we moved the formula two columns to the right and one row up. Excel then updated the formula from =A4 to =C3 following the same “path”: two columns to the right and one row up. A way to think of it is that Excel moved everything two steps over and one step up.

Sometimes, we won’t want the column or row to update. For example, let’s take a look at calculating the percentages of the new vehicle data in the next table.

We can calculate the first percentage in C by dividing the number of new cars by the total number of new vehicles. In Excel, we would type the formula = B2/B5 in the cell C2.

What happens when we move this formula to the cell C3? The column stayed the same, and the row moved one cell down. Excel will update the references in the formula =B2/B5 to follow the same path:

• B2 becomes B3
• B5 becomes B6

Now the cell C3 will have the formula =B3/B6.

This isn’t what we want to calculate! The formula =B3/B6 takes the number of new SUVs in B3 and divides it by whatever is in cell B6, not by the total which is in cell B5.

We want Excel to update the reference B2 to B3, so that we’re calculating the SUV percentage. But we don’t want Excel to update the reference to B5, since the total stays the same no matter which vehicle type we’re working with.

To stop Excel from updating this row, we need to place a $ before the original row reference. The first formula in C2 becomes =B2/B$5. When we move it down to C3 it updates to =B3/B$5. This takes the number of new SUVs and divides it by the total, which is what we want to do!

Note that if we wanted to stop the column from updating instead of the row, we would need to put the $ sign in front of the column letter in the formula instead of the row number.

Errors and Best Practices

Forgetting to use a $ when necessary sometimes results in an Excel error. These errors are displayed in the cell containing the reference.

The two most common errors are #DIV/0! and #VALUE!. Let’s look at how these could occur in our example, where Excel updated the percentage formula to =B3/B6, pointing to a cell B6 that isn’t in our table.

The #DIV/0! error indicates that Excel is trying to divide by 0. This could happen because the cell B6 contains a 0 or, more likely, because the cell B6 is empty. Excel assumes that empty cells are the same as 0.

The #VALUE! error likely indicates that the cell B6 contains some non-numeric value that Excel cannot use in the mathematical formula =B3/B6.

Even if no errors appear, our cell references might still be updating in a way we don’t want. If there was a nonzero number in cell B6, Excel would calculate =B3/B6 without an error. Unless we’re careful, we could use the wrong number in our analysis without realizing it.

In general, it is always good practice to audit every formula we intend to move, looking at every cell referenced to ask:

1. Should the row of this reference update when moved up/down?
2. Should the column of this reference update when moved left/right?

The learning environment has a slideshow illustrating each step of creating and moving a formula in Excel. You can advance the slides using the controls at the bottom. Once you feel ready to do this yourself, move on to the next set of instructions!