Erroneous Table Math

by Allen Wyatt
(last updated June 6, 2015)

You may have noticed that if you do table math in a Word table, some of your answers may not be exactly what you would expect. For instance, if you are using the =SUM(ABOVE) function in a table cell to add together the values in a table, the function will add any numeric value in any cell. Thus, if your headings have text in them, such as Quarter 1, Quarter 2, and so on, the function will pull the values 1 and 2 out of the cells and add them into your total.

There are several ways around this problem. The first is to use a specific range in the SUM function instead of the ABOVE attribute. The only drawback is if you edit the table, it could throw your SUM calculations off. Thus, if you add or delete rows from your table on a regular basis, you would need to check the SUM range over and over again to make sure it is always working on the proper range of cells.

A related approach is to make your summation field just a bit more complex—just subtract the first row from the sum. For instance, you could use the following field in the cell where you want the total to appear:

{ =SUM(ABOVE) – A1 }

This formulaic approach is a simple way to make sure that you compensate for the known "glitch" in how Word applies the ABOVE attribute.

Another option is to spell out the numbers in the cells you don't want added in. This would require using headings such as Quarter One and Quarter Two, rather than Quarter 1 and Quarter 2. You could also use Roman numerals, such as Quarter I and Quarter II. For many purposes, the approaches might not be acceptable.

Still another workaround is to insert a blank row between your headings and the body of your table. The =SUM(ABOVE) function stops adding when it reaches a blank row. If you don't want the appearance of a blank row, then you can always format the row height to be a very small value, such as a single point. (One point is the smallest value that you can use for a row height.) If the table borders are off, a row this small will be all but invisible.

A variation on this approach is to split your table into two: the first table would be just your headings, while the body would be in the next table. Then, either format the intervening paragraph so it is very small (again, 1 point is nice) or simply format the paragraph as hidden text and make sure hidden text doesn't print on your printout. The result is that the two tables print so close to each other that they look like one. The drawback is that if you need to subsequently change table column width, you must do it for each of the tables independently.

Perhaps one of the most interesting workarounds is to replace the space just before the number in the heading with a different symbol that looks like a space, but which causes Word to ignore the numbers following it. Try the following:

  1. Select the space in the heading, just before the quarter number.
  2. Choose Symbol from the Insert menu. Word displays the Symbol dialog box.
  3. Make sure the Symbol font is selected.
  4. Double-click on the very first character in the font table. It looks like a blank. Word replaces the space you selected in step 1 with the character.
  5. Click on Close.
  6. Recalculate the totals in the column.

WordTips is your source for cost-effective Microsoft Word training. (Microsoft Word is the most popular word processing software in the world.) This tip (1398) applies to Microsoft Word 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Continuous Formatting for Footnotes

If you've got a lot of short footnotes in a document, you might be looking for a way to save space by "crunching up" the ...

Discover More

Updating Automatically when Opening Under Macro Control

If your workbook contains links, you are normally given the opportunity to update those links when you open the workbook. ...

Discover More

Track Changes Thinks the Editor Has Changed

Track changes is a great feature to use when you want to show what has changed in a document. Word should note which editor ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

MORE WORDTIPS (MENU)

Freezing a Table

Tired of Word changing the dimensions of table cells to accommodate what you place in those cells? You can instruct Word to ...

Discover More

Resizing Table Columns with the Mouse

Once a table is inserted in your document, you can use the mouse to adjust the width of columns. The effect the mouse pointer ...

Discover More

Finding a Cell Reference

Want to know what the reference address is for a particular cell in a table? Word won't tell you, but you can use a macro to ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven minus 6?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share