The first Excel Boot Camp topic is on anchoring cells. Newcomers to Excel must master this skill early on to be successful.

To enter formulas into Excel, simply click in a cell with the mouse, type “=” and enter the formula. Notice that you can use references to other cells in your formulas, and THAT is why spreadsheets are so useful.

Spreadsheets are NOT just tables of numerical values. They are complex interrelated formulas that change output when the input changes.

Spreadsheets are also incredibly visual which is why I favor them when teaching the applied mathematics related to physical chemistry.

So back to the main topic.

The dollar sign symbol “$” is the anchor. If you create a cell formula in cell B5 for a linear equation such as “y = mx + b” using cell references, it might look like this. “=B1*A5+B2”

B1 is the slope value at the top of the page, B2 is the intercept value at the top of the page, and A5 contains the x values for the equation. If you copy this formula down for several x values, the formula in B6 will have a slope of B2 and an intercept of B3. This is bad! The “constants” have been pulled down by when the formula is copied down the column.

The relative cell references act like boats that can be pulled along wherever the formula is pasted. But if the formula contains a constant cell reference you need to anchor that boat on a specific cell.

Use the dollar sign symbol to anchor the cell references so that the formula in B5 is “=$B$1*A5+$B$2”. Now, the formula will use the slope in B1 and the intercept in B2 no matter where it is pasted.

And that’s how you anchor cells.

Pro-Tip

If you are typing your formulas into Excel, you are prone to make mistakes! Use the mouse to select the cells for your formulas. When you click on a cell that should be anchored, hit the function key “F4” at the top of the keyboard. This automatically adds the dollar signs to the cell reference. Sometimes you only want to anchor the column, or the row, so hit “F4” multiple times, and the anchoring will cycle through all the options.

We learn by doing, so watch the video and try it yourself!

clip-anchoring