Anatomy of Word: Taking Word tables to the next level

by Scott Lowe MCSE | Feb 16, 2006 8:00:00 PM

Tags: Word processors, Microsoft Office, PRODUCTIVITY, Scott Lowe MCSE, Microsoft Word, Word Tables, Microsoft Excel

4 comment(s)

Takeaway: Word tables and Excel spreadsheets share a few things in common, such as the ability to perform calculations on numbers in each cell. This document explores these similarities and points out where Word tables fall short of Excel.

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.

4 comment(s)