How to Master Mixed References in Excel ($A1 and A$1) to Build a Multiplication Table With One Formula

You need to copy a single formula across rows and columns, but the cell references keep changing incorrectly. This happens because standard relative references shift in all directions when copied. Mixed references lock either the column or the row, allowing you to build a grid with one starting formula. This article explains how to use the dollar sign ($) to create mixed references and build a complete multiplication table with a single formula.

Understanding Mixed Cell References

A cell reference tells Excel where to look for a value. By default, references are relative, like A1. When you copy a formula with A1 one cell down, it becomes A2. When you copy it one cell to the right, it becomes B1. This behavior is useful but fails when you need one part of the reference to stay fixed. Mixed references solve this by using the dollar sign ($) to lock either the column letter or the row number. You need a basic understanding of entering formulas to use this feature.

The Two Types of Mixed Reference

A mixed reference is a hybrid of a relative and an absolute reference. The dollar sign anchors the part of the reference it precedes. In $A1, the dollar sign is before the column letter ‘A’. This means the column is absolute and will not change when the formula is copied left or right. The row number 1 is relative and will change when the formula is copied up or down. The opposite is true for A$1. Here, the row is locked, so the number 1 stays constant during vertical copying, while the column letter A can change during horizontal copying.

Steps to Build a Multiplication Table With One Formula

The classic use for mixed references is creating a grid where one input is from a row header and another from a column header. For a multiplication table, you need the row number to change down a column and the column letter to change across a row. A single formula with the correct mixed references can be copied to fill the entire table.

1. Set up your row and column headers
   In cell B1, enter the number 1. In cell C1, enter 2. Select both cells and drag the fill handle to the right to create a row of headers (e.g., 1 to 10). In cell A2, enter 1. In cell A3, enter 2. Select both cells and drag the fill handle down to create a column of headers (e.g., 1 to 10).
2. Enter the master formula in the top-left cell of the grid
   Click in cell B2. This is where your first calculation will be. Type an equals sign (=) to start the formula.
3. Create a mixed reference to the column header
   With the cursor after the equals sign, click on cell B1 (which contains the column header ‘1’). Press the F4 key on your keyboard three times. You will see the reference cycle to $B$1, then B$1, then $B1. Stop when it shows $B1. This locks the column B but allows the row to change.
4. Add the multiplication operator and a mixed reference to the row header
   Type an asterisk (*) for multiplication. Then, click on cell A2 (which contains the row header ‘1’). Press the F4 key twice. The reference will cycle from A2 to $A$2, then to A$2. Stop when it shows A$2. This locks the row 2 but allows the column to change. Your complete formula in cell B2 should be =$B1*A$2.
5. Copy the formula to fill the table
   Select cell B2. Hover your cursor over the small square in the bottom-right corner of the cell border (the fill handle). Click and drag the fill handle to the right across all your column headers. Then, with the entire first row of formulas selected, drag the fill handle down to fill all rows. The single formula will correctly multiply each row header by each column header.

Common Mistakes and Limitations

Formula Returns a #VALUE! Error

This usually means one of your header cells contains text, not a number. Check that your row and column headers are numeric values. A cell with an apostrophe before a number, like ‘1, is text. Clear the cell and re-enter the number without any special formatting.

The Table Shows Zeros or Incorrect Numbers

This happens if the mixed references are reversed. If your formula in B2 is =B$1*$A2, the results will be incorrect because the wrong element is locked. The column header reference ($B1) must have the column locked, and the row header reference (A$2) must have the row locked. Re-enter the formula with the correct mixed references: =$B1*A$2.

Using Mixed References with Table Structured References

Mixed references do not work with Excel Table column references like [@[Column1]]. The @ symbol and structured references have their own anchoring behavior. For calculations within a Table, you typically use the column names directly. To build a grid calculation, it is often easier to use a standard cell range with mixed references instead of converting your data into a formal Table.

Relative, Absolute, and Mixed References Compared

Item	Relative Reference (A1)	Absolute Reference ($A$1)	Mixed Reference ($A1)	Mixed Reference (A$1)
Syntax	A1	$A$1	$A1	A$1
Column Behavior When Copied	Changes	Stays fixed	Stays fixed	Changes
Row Behavior When Copied	Changes	Stays fixed	Changes	Stays fixed
Best Use Case	Simple formulas copied in one direction	Constant value like a tax rate or unit cost	Reference to a column header in a grid	Reference to a row header in a grid
Keyboard Shortcut (F4)	Default state	Press F4 once	Press F4 three times	Press F4 twice

You can now use mixed references to create dynamic grids like multiplication tables, pricing matrices, and scheduling tools. Practice by changing your grid headers to see the table recalculate instantly. For a more advanced application, try nesting a mixed reference inside an INDEX and MATCH function to create a two-way lookup that finds a value at the intersection of a specific row and column.