In
part one of this three part series about Word tables, you learned the basics of
creating and formatting tables in Word documents. You learned a lot about how
to make tables look nice, how to add and remove columns and rows, and how to
make sweeping formatting changes with just a few clicks of the mouse.

You’ve
probably noticed by now how similar a Word table looks when compared with an Excel spreadsheet. That fact is Word tables and Excel
spreadsheets share a few things in common, such as the ability to perform
calculations on numbers in each cell. In this article, I will go over these
similarities, as well as pointing out where Word tables fall short of Excel.

A little about this series

This series is broken into three parts:

  • How
    tables work
  • Using tables as mini-spreadsheets
  • Using tables to create professional-looking forms

Simple Word formulas

If you’ve
used Excel very much, you’re familiar with the concept of a formula that allows
you to perform calculations based on information in your spreadsheet. A Word
table provides you with similar capability, but nowhere close to the extent
provided by Excel. In Excel, for example, you can create complex formulas that
references information from multiple spreadsheets and manipulate the output so
that it appears the way you like. Word can’t. Bear in mind that Word is a word
processor and Excel is a spreadsheet, so you shouldn’t expect the tools to have
features that stray into the other’s area. With that said, Word does provide
you with some capability to perform
calculations based on information in your table.

Suppose,
for example, you have the following budget table (Figure A) that you want to include in a Word document.

Figure A
We’ll use this simple budget table for the examples in this article.

Notice
that there are two totals areas in the table shown in Figure A. One runs down the right-hand side of the table and the
other runs underneath the table. The column at the right will hold values that
indicate the total spending in each category while the row at the bottom of the
table will hold values that indicate the total spent each month.

But,
why dig out the calculator, punch in the numbers, and type the totals into the
cells when you can have Word do the work for you?

To
make Word do the work, position your cursor in one of the month cells in the
last row of the table and choose Table |
Formula. You will get a window similar to the one shown below in Figure B.

Figure B
The formula window is simple, but has quite a bit of functionality.

When
you first open the window, the Formula box will read =SUM(ABOVE), as long as you have positioned
your cursor in the bottom row of the table. If you place your cursor in one of
the cells in the right-hand total column, the Formula box will instead read =SUM(LEFT). Word is smart enough to take a
look at your data to figure out which formulas make the most sense.

Immediately
below the Formula box, you see the Number
format box. As you might expect, the options in this box allow you to
indicate how you want to format the result. In the example shown in Figure B, the output would be formatted
with a leading dollar sign. However, you don’t actually have to indicate
anything in the Number format box. Word
also helps you with this. If, for example, one of the numbers in your table
column has a dollar sign on it, Word will assume that the formula result should
also be a monetary figure and will tack on the dollar sign to the result for
you. Take a look at Figure C to see
what I mean.

Figure C
For these formulas, I left the Number format box empty.

Note
that for the examples shown in Figure C,
I left the Number format option empty, which allowed Word to make its own
decision about how to format the results. For any column or row that had even a
single figure with a dollar sign, Word formatted the results as a monetary
figure. For column and row calculations in which all of the figures were just
numbers with no dollar sign, Word maintained this plain formatting.

Another
item to note: I mentioned that Word makes an attempt to give you the appropriate
formula depending on where you locate your cursor. Take a look at the table in Figure C again and locate the cell that
holds the total value spent for electricity. The figure in it is 405. When I
selected this cell and went to Table |
Formula, Word didn’t give me =SUM(LEFT), but gave me =SUM(ABOVE) instead. This is because there was a figure already present immediately
above this cell (the cell that has the $2625.00 result). As I mentioned, Word tries to help you out, but can’t always
get it right.

Here
are some examples of other functions available using Word’s formula window and
the results they provide.

Figure D
The Count function just counts the number of cells while the Max function
locates the largest value.

Here’s
another caveat: Suppose you want to use the Average or Count function and your
table has a heading. In older versions of Word, your formula would include the
heading cell, and treat it as a zero. Therefore, the Average value for the
second column in Figure D would read
200 instead of 250.

Word
2003 is a little smarter, but not a whole lot. Word 2003 instead looks at the
data and tries to determine where your heading row is located and exclude it from
the results. But, this only works if you’re using different kinds of
information. If you data is text instead of numbers,
Word can’t make any kind of determination and gives you an incorrect result. Take
a look at Figure E. Note the change
in the column labeled Heading 3.

Figure E
Word can’t always figure out what you want.

The
solution to the problem: Use a more complex formula.

More complex Word formulas

So
far, you’ve seen some simple Word formulas such as =SUM(LEFT), which adds p the values in the
cells to the left of the formula. But, what if you want to perform a somewhat
more complex or granular calculation, such as multiplying two of the numbers in
your table, or including just specific cells in a calculation? Word has you
covered.

Recall
how Excel names cells. The column letter and the row number are concatenated to
form a cell reference such as A4 or B6. Excel even provides you with a nice
grid to help you keep track of where you are. Word uses the same cell naming
scheme, but does not provide you with the nice grid, so you have to do a little
counting and reciting of the alphabet. Figure
E gives you a look at how cells are named.

Figure F
Columns get letters and rows get numbers.

Using
these cell references instead of a word like ABOVE and LEFT allows you to avoid
problems like the one I showed you in Figure
E, and allows you to perform direct calculations on values in your table.

Take a
look at Figure G, where I’ve
provided some example formulas for you to look at.

Figure G
I’ve provided both the formula and the result. The shaded cells indicate
which cells are involved in the formula for a particular column.

In Figure G, I’ve provided you with a look
at a bunch of different things. First, you can see that I’ve used cell
references this time around. Second, you also see that I’ve done some direct
calculations, such as C3 * C5 (the * character is used to denote
multiplication) and D2-D4.

Finally,
you’ve seen two different ways to address cell ranges in a formula. In column B
(August), I’ve used the formula =SUM(B3,B4), which, when read literally, reads “Add
up cells B3 and B4”. Now, look at column E (November) and look at the
formula, which reads =SUM(E2:E5). When read literally, this formula
would read “Add up cells E2 through E5, inclusive.” The only
difference is a comma versus a colon. A comma allows you to provide a list of
cells to add up whereas a colon allows you to specify a range of cells.

Note
also that the second total column showing the formulas has a bunch of curly
braces. The reason: Those cells actually contain the formula and I’ve set the Toggle Field Codes option to on. When
you select a cell with a formula and a result and choose to Toggle Field Codes on, Word shows you
the formula instead of the result. This can be really handy. To do this
yourself select a cell with a formula, right-click it, and, from the resulting
shortcut menu, choose Toggle Field Codes.

Recalculating formulas

This
will be a quick one since it’s pretty straightforward. Suppose you make a
change to your table’s data. One big difference between Word and Excel: Excel
recalculates your entire spreadsheet every time you make a change. Word does
not. You need to tell Word to redo its calculations. To make this happen,
select your entire table and press the F9 key on the keyboard. This will
recalculate your formulas so you don’t need to enter them all over again.

Shortfalls

Word
tables can be very useful when used as mini-spreadsheets, but Word tables will
never replace the power and flexibility of Excel, a program designed to do
nothing more than crunch numbers. Here are a couple of areas where Word tables
fall seriously short of Excel:

  • Word has a fraction of the functions provided by Excel.
  • Excel calculates changes on-the-fly.

For
many, Word tables are more than sufficient. If you need more, but also need the
power of Word’s document-creation capabilities, a future article in this series
will detail how you can combine the best of both products into a single
document.