Excel Technique: Making a Table for a Function of Two Independent

Stage: 3 and 4 Challenge Level: Challenge Level:2 Challenge Level:2
As an example here is a simple Tables grid:

Table of two variables

The cell C3 contains the formula: = C\$2 * \$B3

* means multiply in Excel, and although the functions we need to investigate will often involve more than simple multiplication, the process explained below is easily applied even when the function itself is complicated.
In the illustration above, C2 is the factor from the top row and B3 is the factor from the left hand column. The dollar sign, \$, used in the C3 formula, ensures that when the formula is copied from C3 across the whole C3:F6 range the first factor will continue to be taken from row 1 and the second factor will still be taken from column A.

Once the correct formula (above) has been entered in C3, the keystrokes for the copy manoeuvre are:

  • select C3 (just click on it)
  • choose Copy from the Edit menu
  • highlight (click and drag) the C3:F6 range
  • choose Paste from the Edit menu.

The formula from C3 is copied automatically to all cells in the C3:F6 range.

The formula is adjusted for each new position except where a \$ sign was placed to show that this automatic assistance is unwanted.

The NRICH Project aims to enrich the mathematical experiences of all learners. To support this aim, members of the NRICH team work in a wide range of capacities, including providing professional development for teachers wishing to embed rich mathematical tasks into everyday classroom practice. More information on many of our other activities can be found here.
Copyright © 1997 - 2012. University of Cambridge. All rights reserved.
NRICH is part of the family of activities in the Millennium Mathematics Project.