Excel Optimize

Problem: Create a multiplication table to help your kids in school. In Fig. 176, you want to enter a single formula in cell B2 that can be copied to the entire table.

Strategy: In the last chapter, you learned how to use an absolute reference, such as $C$1, so that Excel would not change from column C or row 1 as it copied the formula. To create a multiplication table, you need to use a mixed reference. A mixed reference, such as $B1, will lock the formula to column B, while allowing the row to change. A mixed reference, such as B$1, will lock the row to row 1, while allowing the column to change.
The formula that you need for the multiplication table is a formula that will multiply whatever is in row 1 above the cell by whatever is in column A to the left of the cell. To have a reference that always points to row 1, use something in the format of B$1. To have a reference that points to column A, use a reference
in the format of $A2.
1) As shown in Fig. 177, the formula you want to enter in B2 is =$A2*B$1.

2) Copy the formula in B2 to the entire range, and it will always properly multiply row 1 by column A as shown in Fig. 178.

Summary: Using a single dollar sign in a cell reference will create a mixed reference. Only the row or column will be fixed as you copy the formula.