Business Productivity Tools

Excel – Creating a Times Table

© Mike Splane  2006

 

 

For this exercise you will create a Multiplication Table in Excel. In the table you will create formulas to multiply the numbers in the top row times the numbers in the left hand column.

The purpose of this exercise is to learn how to use dollar signs in cell references to quickly enter formulas. Each time you attempt the exercise, study how the use of each dollar sign affected the results of copying the formula.

 

Open Excel.

  1. Highlight cells A1 through D1 and merge them using this toolbar icon: Picture of the merge and center icon used in Excel 2003

Office 2007 Look in the Home Tab, Alignment Group

  1. Type the words “Multiplication Table” into the merged cell. Format the text as bold and italicize it.
  2. In Cells B2 through D2, enter the numbers from 2 through 4. Format them as bold. Center the numbers.
  3. In Cells A3 to A21, enter the numbers from 2 through 20. Format them as bold. Center the numbers.
  4. Enter formulas in rows 3 to 5, columns B to D, to calculate the value of 2 * 2, 2 * 3,
    2 * 4, 3 * 2, 3 * 3, 3 * 4, 4 * 2, 4 * 3, and 4 * 4.

Example

A

B

C

D

1

Multiplication Table

2

 

2

3

4

3

2

= 2 * 2

=2 * 3

= 2 * 4

4

3

= 3 * 2

=3 * 3

= 3 * 4

5

4

= 4 * 2

=4 * 3

= 4 * 4

6

5

 

 

 
  1. When you enter the formula in cell B3, you may see the formula in both cell B3 and in the formula bar above the worksheet. This is what I call “mirror” view. If you see a formula in the formula bar and cell B3 shows 4, you are in what I call “flashcard” view. You can switch between the two views by simultaneously pressing CTRL and ~ on the keyboard. To start this exercise, you should be in mirror view. 
  2. Switch to flashcard view.  The result in cell B3 should be 4.  Return to mirror view.
  3. Edit the formulas to replace numbers with cell references. Cell B3 should say = A3 * B2

 

Example

A

B

C

D

1

Multiplication Table

2

 

2

3

4

3

2

= A3 * B2

= A3 * C2

= A3 * D2

4

3

= A4 * B2

= A4 * C2

= A4 * D2

 

  1. What are the two parts of the cell references which appear in every formula? (Hint: A, 3, B, or 2) You don’t want them to change as you copy the formula to new rows and columns.  The dollar sign tells Excel  “do not change the following symbol.”  Hint: you always want to multiply by a specific column and by a specific row.  You want that number and that row to stay unchanged when you copy the formula.
  2. What are the two parts of the cell references in these formulas which change from row to row or column to column?  (Hint: A, 3, B, or 2) Those are the parts that you want changing automatically as you copy the formula into new rows and columns.
  3.  Can you fix the formula in cell B3 so it will give the right results if you copy it into all of the cells in the Times Table?  Some possible choices are

= A$3 * $B2           = $A3 * $B2                = $A3 * B$2

= A$3 * B$2           = A3 * $B$2                            = $A$3  * B2 

Note: Only ONE of these will work properly. Don’t use three different formulas in the first row. The correct formula will work when copied both across and down.

 

  1. Edit the formula in cell B3. (To edit a formula: Select the cell and then type in the formula bar or select the cell and click on the F2 function key.)
  2. Copy the formula from B3 and paste it into the range B3:D21 (this is a rectangle with 19 rows and 3 columns. B3 and D21 are the corners) to complete the Multiplication Table.
  3. Check your results. Do the formulas in the multiplication table give the desired results? 
  4. Now look at the formulas. To do this, highlight all of the worksheet (Control + A keys). Then, hold down the Control and ~ Keys. What parts of the formulas changed when copied? What parts stayed the same? Why? Hold down the Control and ~ Keys to return to displaying the numbers.

 

Desired Result

A

B

C

D

1

Multiplication Table

2

 

2

3

4

3

2

4

6

8

4

3

6

9

12

 

  1. If the formulas don’t fill in the table with your desired numbers, return to step 12 and experiment with a different combination of dollar signs.  Repeats Steps 12, 13 and 14 until you get an accurate multiplication table. USE THE SAME FORMULA FOR EACH COLUMN AND ROW. DO NOT USE 3 FORMULAS!!
  2. When the formula produces the desired numbers, change the worksheet to mirror view so the formulas are showing instead of their results. To do this, highlight all of the worksheet (Control + A keys). Then, hold down the Control and ~ Keys.
  3. Print the worksheet. (Note: Formulas and numbers will be left-aligned. That’s ok.)
  4. Write your name and section number on the top of the printout and turn it in.