References in Excel 2016 Formulas - dummies

By Ken Bluttman

References abound in Excel formulas. You can reference cells. You can reference ranges. You can reference cells and ranges on other worksheets. You can reference cells and ranges in other workbooks. Formulas and functions are at their most useful when you’re using references, so you need to understand them.

And if that isn’t enough to stir the pot, you can use three types of cell references: relative, absolute, and mixed. Okay, one step at a time here. Try a formula that uses a range.

Formulas that use ranges often have a function in the formula, so use the SUM function here:

  1. Enter some numbers in many cells going down one column.

  2. Click another cell where you want the result to appear.

  3. Type =SUM( to start the function.

  4. Click the first cell that has an entered value, hold the left mouse button down, and drag the mouse pointer over all the cells that have values.

  5. Release the mouse button.

    The range address appears where the formula and function are being entered.

  6. Type ).

  7. Press Enter.

  8. Give yourself a pat on the back.

Wherever you drag the mouse to enter the range address into a function, you can also just type the address of the range, if you know what it is.

Excel is dynamic when it comes to cell addresses. If you have a cell with a formula that references a different cell’s address, and you copy the formula from the first cell to another cell, the address of the reference inside the formula changes. Excel updates the reference inside the formula to match the number of rows and/or columns that separate the original cell (where the formula is being copied from) from the new cell (where the formula is being copied to). This may be confusing, so try an example so you can see this for yourself:

  1. In cell B2, type 100.

  2. In cell C2, type =B2 * 2.

  3. Press Enter.

    Cell C2 now returns the value 200.

  4. If C2 is not the active cell, click it once.

  5. Press Ctrl + C, or click the Copy button in the Clipboard category on the Home Ribbon.

  6. Click cell C3.

  7. Press Ctrl + V, or click the Paste button in the Clipboard category on the Home Ribbon.

  8. If you see a strange moving line around cell C2, press the Esc key.

    Cell C3 should be the active cell, but if it is not, just click it once. Look at the Formula Bar. The contents of cell C3 are =B3 * 2, and not the =B2 * 2 that you copied.

Did you see a moving line around a cell? That line’s called a marquee. It’s a reminder that you are in the middle of a cut or copy operation, and the marquee goes around the cut or copied data.

What happened? Excel, in its wisdom, assumed that if a formula in cell C2 references the cell B2 — one cell to the left — the same formula put into cell C3 is supposed to reference cell B3 — also one cell to the left.

When you’re copying formulas in Excel, relative addressing is usually what you want. That’s why it is the default behavior. Sometimes you do not want relative addressing, but absolute addressing. This is making a cell reference fixed to an absolute cell address so that it does not change when the formula is copied.

In an absolute cell reference, a dollar sign ($) precedes both the column letter and the row number. You can also have a mixed reference in which the column is absolute and the row is relative, or vice versa. To create a mixed reference, you use the dollar sign in front of just the column letter or row number. Here are some examples:

Reference Type Formula What Happens After Copying the Formula
Relative =A1 Either, or both, the column letter A and the row number 1 can
change.
Absolute =$A$1 The column letter A and the row number 1 do not change.
Mixed =$A1 The column letter A does not change. The row number 1 can
change.
Mixed =A$1 The column letter A can change. The row number 1 does not
change.