An mixed reference in Excel is a reference where part of the reference is absolute and part is relative. For example, the following references have both relative and absolute components:

=$A1 // column locked
=A$1 // row locked
=$A$1:A2 // first cell locked

Mixed references can be used to set up formulas that can be copied across rows or columns without the need for manual editing. In some cases (3rd example above) they can be used to create a reference that will expand when copied.

Mixed references are a common feature in well-designed worksheets. They are harder to set up, but they make formulas much easier to enter. In addition, they significantly reduce errors since they allow the same formula to be copied to many cells without manual edits.

Example shown

In the example shown the formula in E5 is:

=$C5*(1-E$4)

This formula is carefully constructed with two mixed references so that it can be copied across the range E5:G7 without manual changes. The reference to $C5 has the column locked to make sure the formula continues to pick up price from column C as it’s copied. The reference to E$4 has the row locked so that as the formula is copied down from row 5 to row 7, the formula will continue to pick up the percentage value in row 4.

Toggle between absolute and relative addresses

When entering formulas, you can use a keyboard shortcut to toggle through relative and absolute reference options , without typing dollar signs ($) manually.

A multi-cell array formula is an array formula that returns multiple results to more than one cell at the same time. In the example shown, the formula in B3:B12 is:

{=ROW(1:10)}

Here, the ROW function returns an array with 10 items:

{1;2;3;4;5;6;7;8;9;10}

and each item in this array is displayed in a different cell.

Steps to enter a multi-cell array formula

To enter a multi-cell array formula, follow these steps:

  1. Select multiple cells (cells that will contain the formula)
  2. Enter an array formula in the formula bar
  3. Confirm formula with Control + Shift + Enter

Multi-cell array formulas have unique characteristics:

  1. All cells display the same formula (relative references don’t change)
  2. You can’t insert cells into the range that contains a multi-cell array formula
  3. You must select the full range in order to edit the formula
  4. You must confirm changes with control + shift + enter

To select all the cells in a multi-cell array formula, you can use the keyboard shortcut control + /