Today, we are going to talk about mixed cell references and we will start with the simplest example, which is the multiplication table. Multiplication is simple. We just have to multiply the row header by the column header. We are going to write a formula in cell B3, then we are going to copy the formula down and to the right. First, we are referring to cell A3. Then we have to ask ourselves how our reference should change while copying it down and to the right. Let’s look at our data which we will be referring to. We’re starting with row headers. Our row headers are in one column and many rows (Fig. 1).
It means that the rows are changing but the columns stay the same. Thus, we have to lock the column name, not the row number. We can use the F4 key which cycles through cell reference types. It turns out that we had to press the F4 key three times, so that the $ sign is before the column name, not before the row number (Fig. 2).
=$A3
It means that the column name should stay the same, but the row number should change. We want to multiply them so we are adding cell B2. Let’s look at the headers once again. As we can see, our column headers are placed in one row, but many columns.
=$A3*B2
This time, we want to lock the rows, not the columns. We have to click the F4 key two times, so that the $ sign is before the row number, not the column name, which means that the rows should stay the same, but the columns should change (Fig. 4).
=$A3*B$2
After entering the formula, copy it down and to the right. We can see that the results are proper (Fig. 5)
Let’s check the last cell. We can see that we have proper cell reference to row headers, as we still refer to column A, and the rows changed. Analogically, for column headers, we can see that the columns changed, and the row stayed the same (Fig. 6).
That’s how mixed cell reference works. It will work in many different examples, like the one below. We have to divide profits for shareholders. The situation is analogical. Our profits are in one column and many rows (Fig. 7)
We have to lock the column name, i.e. the $ sign should be placed before the column name, not before the row number. Then, we want to multiply it by percentages. They are in one row — row number 3, which shouldn’t change (Fig. 8).
=$B6*
So, when we refer to cell C3, we have to lock the rows, not the columns because they are changing. Let’s press the F4 key twice and lock the rows (Fig. 9).
=$B6*C$3
After copying the formula down and to the right, we have proper results.
That’s how you use mixed cell references.