How to use formulas in a table in Microsoft Word
If you have been using Microsoft Word for a reasonable amount of time, you should be already aware that you can use formulas in a table. But using formulas in a Word table is not as intuitive as it is in Excel. Also, Word supports only the basic functions. Nevertheless, the available functions are sufficient for most situations and knowing how to use formulas in a Word table can come in handy.
Of course, instead of using a table you could also embed an Excel spreadsheet inside of a Word document and get all the powerful features that Excel offers. But using Excel can be overkill when a simple table can do the job. And what if you are given a Word document that already contains a table.
Anyway, this article is about inserting formulas into a word table. So lets get started and see how to do that.
By the way, if some of you are wondering whether the plural of formula is formulas or formulae, today, both are accepted as being correct.
Basic arithmetic operators used in formula
- ‘+’ used for addition
- ‘-‘ used for subtraction
- ‘*’ used for multiplication
- ‘/’ used for division
- ‘^’ used for exponential power
Important functions used in formula
||Returns the sum of a list of values|
||Returns the average of a list of values|
||Returns the smallest value in a list|
||Returns the largest value in a list|
||Returns the result of the product of a list of values|
Besides the above functions there are others like ABS, DEFINED, MOD, AND, OR, NOT and a few others, which you may not use much.
Referring to Table Cells
Just like in Excel, in a table, the columns are referenced using letters and the rows are referenced using numbers, though unlike Excel, you don’t see them.
(Obviously, in the image above, the A,B,C and the 1,2,3 in bold are just shown for illustration. Don’t expect to see them when you insert a table).
This is not much of a problem unless you have too many columns or rows, in which case you might have a hard time counting the columns and rows. Then again, most often we would need to refer to the whole column or row, which is quite easy.
To use cell references in formulas, use a comma to separate references of individual cells. For example, AVERAGE(A1,B2,C3). If the cells that you want to use are contiguous, either in a row or column, you can just mention the first and the last cell references separated by a colon. For example, SUM(A1:C1). And, if you want to refer to a grid cells you could use AVERAGE(A1:C3) which is essentially the same as using AVERAGE(A1,B1,C1,A2,B2,C2,A3,B3,C3).
You can also use the following references in a formula:
- LEFT: reference to all cells to the left side of the selected cell.
- RIGHT: reference to all cells to the right side of the selected cell.
- ABOVE: reference to all cells above the selected cell.
- BELOW: reference to all cells below the selected cell.
For example, to calculate the sum of the values of all cells above the selected cell, you could use the formula =SUM(ABOVE).
Inserting a formula in a table cell
In Word 2007/2010 the Formula command is found on the Table Tools Layout tab, in the Data group. Place the cursor in the cell you want to insert the formula and click the Formula button (In earlier versions of Word, from the Table menu select Formula.). The Formula dialog box pops up.
From the Paste function box, select a function. Based on the numbers in the table and the location of the cell in which you want to place the formula, Word will guess what type of formula you may want (e.g., to add all cells to the left of the formula, =SUM (LEFT) may be placed in the Formula text box). However, if Word guesses wrongly or you want to enter a different formula, you can do so in the Formula text box. When finished, click Ok.
Unlike Excel, Word does not recalculate the formulas every time a cell value changes. So if you change the value of a cell after inserting a formula, you need to recalculate it. There are several ways you can do it.
To update a single formula:
- Place your cursor in the cell, before the numerals. Notice that the entire result turns grey.Press [F9] or Press [ALT] + [Shift] + [U].
- Place your cursor in the cell, before the numerals. Right click the cell » select Update Field.
To recalculate the values of the entire table:
- Select the entire table and Press [F9] or press [ALT] + [Shift] + [U]. All formulas are recalculated.
If you have any questions, please ask in the comments below.