Learn

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.

A B C
1 Vehicle type # of new vehicles Percentage
2 car 1000
3 SUV 1500
4 truck 5000
5 TOTAL: 7500

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`.

A B C
1 Vehicle type # of new vehicles Percentage
2 car 1000 =B2/B5
3 SUV 1500
4 truck 5000
5 TOTAL: 7500

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`.

A B C
1 Vehicle type # of new vehicles Percentage
2 car 1000 =B2/B5
3 SUV 1500 =B3/B6
4 truck 5000
5 TOTAL: 7500

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!

A B C
1 Vehicle type # of new vehicles Percentage
2 car 1000 =B2/B\$5
3 SUV 1500 =B3/B\$5
4 truck 5000
5 TOTAL: 7500

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!

### Instructions

When you’re ready to practice what you’ve learned, download and work through our interactive exercise spreadsheet. A couple of important points to keep in mind:

• We’ve changed some of the default Excel formatting to make these easier to follow and more interactive. You’ll learn how to do formatting in later lessons.
• We’ve locked all cells except the ones we’re asking you to create formulas in. This is so we can automatically correct your answers and give you feedback without worrying that some data has been changed accidentally.
• When you’re done with the exercises, come back here and answer the reflection question!

Question: how do new cars produced in 2019 compare to the other varieties, given what you calculated in the range `I27:I31`?

Check Our Answer New cars are at only 67% of their 2015 production level, whiles new vans are at 85% and all other vehicle types are over 100% of their 2015 levels.

We have to be careful, however, because we are comparing each type to its own 2015 baseline. For example, while pickup trucks in 2019 are at 140% of their 2015 level, there are still only about half as many pickup trucks being produced as there are cars.