jump to navigation

#50-The Use of Formulas in Word Tables

July 08, 2006

Overview

Have you had to complete a task in Excel rather than in Word because you needed to use formulas in your document? For example, you might have a data table in a report in which you would also like to be able to conduct statistical analyses. Or perhaps you have a survey that uses a Likert scale (or some other scale) and you would like to be able to tabulate the answers when the survey is completed. Many of us are familiar with the use of formulas in Excel to conduct calculations, but did you know this can also be done in Word tables! This LTA will show you how insert mathematical formulas into a Word document. Some familiarity with Word and Excel is required.

LTA Credits

Nancy Bowers
Instructional Design and Technology Solutions
66 Birch Dr, Potsdam, NY 13676
315-566-9151
nbowers@twcny.rr.com

LTA Audience

This LTA requires the use of the table function in Word. Some experience with Word’s table feature would be useful, although not required. A “basic” example is provided for users who may not be comfortable generating tables and a more “advanced” example is provided for proficient Word users. Anyone who uses Word in their day-to-day work will find this LTA enlightening.

Since there is a lot of formatted content, we are making this LTA available as a PDF for downloading
 LTA #50 — The Use of Formulas in Word Tables
Acrobat PDF File: lta_50.pdf [460k PDF]

LTA Outcomes

After completing this LTA, users should be able to:

  1. insert a formula within a Word table.
  2. update the data in a cell that uses a formula.
  3. edit a formula after it has been entered.

How Much Time Will this Take?

If you are a beginner, I recommend that you open Word and follow along step-by-step with the first basic example. This example should take you approximately 30 minutes to complete. For more advanced users, you may want to jump directly to the more complex example. This one should take you about 45 minutes if you work along in your own Word file.

  • Basic Example – this example will show you how to create a formula for a simple calculation. The example will show you step-by-step how to add a table into a Word document and then how to insert, edit and update a basic formula. The scenario used is that of a computer literacy survey that uses a Likert scale.
  • Advanced Example – this example is for more advanced Word users and assumes that the user already knows how to insert tables in Word. You will learn how to add multiple, complex formulas and how to calculate values using numbers from multiple tables. The scenario used is a financial one in which data is collated from multiple quarters.

Basic Example – Create a Short Survey in Word

This example of how to use formulas in a Word file will take the user step-by-step through the creation of a computer literacy survey that uses a Likert scale. A formula will be used to calculate the results of the survey. It is recommended that you open Word and follow along!

1. Create your Survey

Open Word and at the top of the page type:

Lta50 Ex1Next, type in the instructions for completing the survey:

Lta50 Ex2The survey will need to reside within a table in order to use Word’s formula feature. Your table will need a header row, one row for each statement in your survey, and one row for the “Total.” If we have a survey with five questions, then we will need seven rows. One column will be needed for the statement and one column for the response. To insert a table, be sure your cursor is below the survey instructions, click on “Table” in the menu bar, then “Insert” and finally “Table…”.

Lta50 Basic1

A dialog box will appear where you can indicate how many rows and how many columns you want. Enter seven rows and 2 columns. Leave everything else as the default.

Lta50 Basic2

Here is what you should have so far:

Let’s go ahead and enter our headings and statements. Don’t worry about the width of the columns, we can adjust them later.

Lta50 Ex4-1The highlighted cell is where we will soon be enter our formula. Right now though, let’s adjust the width of our Table’s columns. To do this, place your cursor on the vertical line between the two columns. Your cursor should change so that it looks like this: Lta50 Ex5 Then click and hold down the left mouse button and drag your mouse to the right so that the column is as wide as the word “Response.”

2. Enter your Formula

The calculation we need our formula to compute is to add up all of the numbers in the second column (column b), rows 2-6. To insert a formula, click in the highlighted cell. Next, click on “Table” in the menu bar and select “Formula.” The formula we need to enter is:

= sum(b2:b6)

The dialog box should look like the one below.

Lta50 Basic3

Click on “OK” after entering your formula. There should now be a “zero” in the cell where you entered your formula.

3. Test your Formula

Enter responses into your table. After entering your responses, your table should look like this:

Lta50 Ex6To update the data calculated by your formula, you need to right-click (or control-click in mac OS X) on the “zero” and in the menu that appears, click on “Update Field”.

Lta50 Basic4

Notice that when you use your “Right” mouse button (or control-click in Mac OS X), the “zero” becomes highlighted with grey. Here is what your table should look like after you have updated the field:

Lta50 Ex7I recommend that you include a statement next to the word “Total” that explains to students what they must do to see their Total.


* * To get your total, right click (or control click) on the "0"  and in the menu that appears, select Update Field.* *
4. Edit your Formula

Let’s go ahead and add another question to our survey so that we can see how to edit our formula. Click in the row where it says “Total.” Then, click on “Table” in the menu bar, select “Insert” and then “Rows above.” Enter the statement “Replace the motherboard in your computer” into the new row.

Lta50 Ex8To edit your formula, right click on the current total (in this case 14) and in the menu that appears, select “Edit field.”

Lta50 Basic5

In the dialog box that appears, click on button that says “Formula…” Another dialog box will appear that has the current formula in it. Edit it such that it includes the new row (row #7):

Lta50 Basic6

Click on “OK” to finish updating your formula. To use this file that you just created, you will need to remove your numeric responses and reset the “Total” value back to zero but updating your formula.

To finish up your survey, you will need to add a key that explains the scores. For example:

>18  Your computer literacy is high! 12-17   Your computer literacy is pretty good, but not great <12  Your computer literacy is pretty low

Advanced Example – Within and Among Table Calculations

In this example of how to use formulas in Word, we will use five different tables. In each of four tables we will use formulas to compute with-in table calculations, and in the fifth table, we will use formulas to compute calculations by referencing values within the other four tables! In this scenario, the first four tables will be used to collect regional data concerning expenses and cash-in. In actuality, Excel would probably be a better tool for the example used, but it is good approach to demonstrate the flexibility of formulas in Word.

1. Create your Tables

The table for the First Quarter is shown below; the other three tables are identical except for the titles. Do not create them yet though, we’ll copy this first table once we are done entering are formulas. The yellow-highlighted cells in the table below are the ones where we will use formulas:

Lta50 Ex10

2. Enter Formulas

First, we will create a formula to calculate the “Net income” for each of the regions. Click in the cell directly beneath “Net income.” Then, click on “Table” in the menu bar and select “Formula.” To calculate net income, we need to subtract expenses (column b row 2) from cash-in (column c row 2). So for the “Northeast,” the formula would be c2-b2. Enter this in the Formula box:

Lta50 Advanced1We would like this calculated value to be a dollar figure and to not use any decimal places, so use the dropdown box where it says “Number format” to select the dollar format:

Lta50 Advanced2

The default dollar format, includes the decimal places, so edit these out of the format such that the final “Number format” looks like $#,##0;($#,##0). Click on “OK” after you have edited the format. Repeat entering the formulas for each of the regions, being sure to change the row numbers as you go down (e.g., c3-b3, c4-b4).

Next, enter a formula to calculate the “Total Expenses” for the quarter and the “Total Cash-in.” For both, you will need to SUM either b2:b6 (expenses) or c2:c6 (cash-in). Enter both of these formulas; the one for “Total Expenses” is shown below:

Lta50 Advanced3The only formula left to be entered is that for “Total net income.” This value can be calculated in one of two ways; either by subtracting the “Total expenses” from the “Total Cash-in” (=c7-b7) or by summing all of the “Net incomes” (=SUM(d2:d6)). Enter either formula. Your table should now look like this:

Lta50 Ex11The First Quarter is set for values to be entered (cells without zeros). As each region’s numbers are entered, the “Net Incomes” can be calculated. Once all of the quarter’s data have been entered, the Totals can be calculated. To run the calculations, you will need to “update” the formula fields from zero to the actual numbers. To do this, right click on the zero and in the menu that appears, click on “Update field.”

Lta50 Advanced4To create the tables for the other three quarters, copy the first quarter table, paste it three times, and change the titles. All of the formulas will be copied this way and you will not need to re-enter them.

The final table we need to create will be called “Running Totals” that can be used to calculate the various totals after each quarter’s data are entered. It should look like this:

Lta50 Ex12The green cells will require formulas that draw data from the four quarterly tables. The yellow cells will use the same simple formulas used in the individual quarterly tables.

3. Bookmark your Tables

Because we need to use formulas that draw data from the previous four tables to calculate the running totals for “Expenses” and “Cash-in,” we need to “bookmark” each table individually. To do this, select the “First Quarter” table (in its entirety). Then, click on “Insert” in the menu bar and select “Bookmarks.”

Lta50 Advanced5

Each table will need a unique identifier. In the example below I have identified the first table as “Quarter1.” No spaces are allowed!

Lta50 Advanced6Go through and Bookmark each of the four tables. Notice that as you “Bookmark” each table, a set of gray brackets [ ] appear to surround your table.

4. Enter Formulas for Running Totals

Now we are ready to write some more complex formulas! The running total “Expenses” for the Northeast, for example, will be the sum of each individual quarter’s expenses and the formula will need to SUM the value in cell “b2” from all four tables. In the “Running Totals” table click on the cell pertaining to the Northeast’s “Expenses.” To enter a formula that uses bookmarked tables, we must click on “Insert” in the menu bar and then select “Field.”

Lta50 Advanced7

The dialogue box that appears is identical to that of when you use the Table Formula command. To begin writing the formula, use the dropdown box beneath “Paste bookmark” to select the first table (Quarter1).

Lta50 Advanced8

Next, hit the space bar and enter “b2”, indicating that you want to select the value in the “Quarter1” table that is in the “b2” cell. To continue with your formula, enter a comma, the use the dropdown box to select “Quarter2”, enter a space and then enter “b2.” Continue to do this for the other two tables as well. Your finished formula should look like this:

=SUM(Quarter1 b2, Quarter2 b2, Quarter3 b2, Quarter4 b2)

NOTE: If you go back and edit this formula, you will need to reintroduce spaces between the bookmark identifier and the cell identifiers.</p>

Remember to set your “Number format” as we did previously. Enter a formula for all of the expenses and all of the cash-ins using this same approach. To calculate the overall total “Net income” for each region and the overall total “Expenses” and “Cash-in,” enter the same formulas you used for these values in the previous tables.

Your file is now set for quarterly data to be entered. As new data are provided, the formulas can be updated. Below is a summary of what each of the formulas should look like.

Lta50 Ex13One final reminder: the cells that contain the formulas will need to be designated as such, since as soon as they are updated, they will look as though they are just text. Also, instructions on how to update the formulas will need to be included in the file.

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: