WD: How to Set Up a Table as a Spreadsheet in Word

• Microsoft Word for Windows, versions 6.0, 6.0a, 6.0c
• Microsoft Word for the Macintosh, versions 6.0, 6.0.1, 6.0.1a
• Microsoft Word for Windows NT, version 6.0
• Microsoft Word for Windows 95, versions 7.0, 7.0a
• Microsoft Word 97 for Windows
• Microsoft Word 98 Macintosh Edition

SUMMARY

This article describes how to create simple spreadsheets in Microsoft Word, using tables and the Formula (=) field. In these spreadsheets, you can add, subtract, multiply, and divide values entered in cells.

MORE INFORMATION

REFERENCING CELLS

Individual cells are referenced by column letter and row number in the form of "A1" where A represents the column and 1 represents the row. For example, A2 refers to the cell located in the first column second row. To select a row only, the syntax would be "2:2," and to select a column the syntax would be "A:A."

Examples refer to the following table and can be placed in any blank cell in the table:

   -------------
   | 2 | 5 | 4 |
   -------------
   | 7 | 3 | 6 |
   -------------
   | 8 | 1 | 0 |
   -------------
   |   |   |   |
   -------------

1. Place your insertion point into the blank cell A4.

2. On the Table menu, click Formula.

3. Under Formula, an automated formula (=SUM(ABOVE)) is shown. You can delete that entry and enter any type of formula as shown in the next section of this article.

4. You can either leave the Number Format box blank or select an item on the list.

5. Click OK.

Operations You Can Use to Manipulate Values in Cells

Addition:

                               Use a formula      Result from
   To do this                  similar to this    the sample
   ------------------------------------------------------------

   Add a number to a cell      =(a1) + 3          5

   Add two adjacent cells      =sum(a1:a2)        9
                               -or-
                               =(a1+a2)

   Add two non-adjacent cells  =(a1+a3)           10

   Add a range of cells        =sum(b:b)          9
                               -or-
                               =sum(b1:b3)

                                 Use a formula      Result from
   To do this                    similar to this    the sample
   ------------------------------------------------------------

   Subtract a number from a cell =(a1)- 3           1

   Subtract two cells            =(a3-b2)           5

                                    Use a formula      Result from
   To do this                       similar to this    the sample
   ---------------------------------------------------------------

   Multiply a number by a cell      =(a1)* 3           6

   Multiply two adjacent cells      =product(a1:a2)    14

   Multiply two non-adjacent cells  =product(a1,a3)    16

   Multiply a range of cells        =product(b:b)      15
                                    -or-
                                    =product(b1:b2)

                                   Use a formula    Result from
   To do this                      similar to this  the sample
   ------------------------------------------------------------

   Divide a number by a cell       =(a1)/3         .67

   Divide two cells                =c2/a1           3

Referring to Cells

Syntax:

Unlike earlier versions of Word, individual cell references do not need to be enclosed in brackets and parentheses. However, cell ranges, which are separated with a colon such as (A1:A6), must be enclosed in parentheses.

Referencing Cell Ranges:

If the cells are in a contiguous block (they cover a rectangular area), you can shorten the reference to them. For instance, the eight-cell area from A1 to D2 can be referenced as A1:D2.

Referencing Nonadjacent Cells:

When you add or multiply nonadjacent cells, you can refer to each cell individually, or you can separate the references themselves with commas.

NOTE: You can use commas only with the Sum and Product functions.

For example, these formulas are equivalent:

   Sum Example
   -----------

   {=sum(a1) + sum(c2) + sum(b2)}
   {=sum(a1,c2,b2)}

   Product Example
   ---------------

   {=product(a1) + product(c2) + product(b2)}
   {=product(a1,c2,b2)}

     {=sum(a1) - sum(c2) - sum(b3)}
     {=sum(a1) / sum(c2) / sum(b3)}

Relative references are useful when you want a generic formula that doesn't refer to specific cells. You can add and delete rows and maintain a correct result. Relative references are also useful when you don't want to retype a formula. For instance, if you want each cell in the fifth column to contain the sum of the numbers of the first four columns in the same row, you can use a relative reference. In this example, the field would resemble the following:

   {=sum(left)}

Bookmark References:

You can apply a bookmark to the contents of any cell or field and use that bookmark in a calculation. This can often simplify the construction of other fields and can save typing.

For example, if you apply a bookmark called "quantity_sold" to the contents of cell a3, you can refer to it in another cell.

      {=quantity_sold*.10}
      {=quantity_sold*sum(b2)}
      {=quantity_sold*commission}

If you refer to an entire row or column and place the field within that range, Word includes the field result in the calculation. Each time you update the calculation, it will include itself in the result. This produces a different result (even if none of the other numbers in the row or column have changed) and yields unreliable results.

   Examples of the Type of Field to Avoid
   --------------------------------------

   {=sum(c:c)} adds the entire column, including the result. Each time
   you update this field, the result is the sum of the column plus
   the field's previous total. To prevent this behavior, use an absolute 
   reference instead, and place the field in another column.

MORE INFORMATION

For more information, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: :Q110656
   TITLE       :Calculation in Form Field Shows Wrong Result

   ARTICLE-ID: :Q157213
   TITLE       :Incorrect Value Referencing Table Cell

   ARTICLE-ID: :Q95618
   TITLE       :WinWord Err Msg: "! Zero Divide" When Using Expression
                Field

REFERENCES

"Microsoft Word Users Guide," version 6.x, pages 308-312