Building calculations in Word documents

Excel isn’t the only Office application that can perform calculations. Of course, you can embed an Excel object in Word, Outlook or PowerPoint but it’s important to note that embedding part of an Excel workbook in another application, whether the embedded object is created from scratch or by copying part of an existing workbook, can make the entire workbook contents available through that application. As an alternative, here we are going to look at performing calculations in Word using a Word Formula field.

Generally, Word fields can be used to automate a document in some way, such as including the current date and time or entering mail merge fields that retrieve specific information from another document or file.

Just to demonstrate how a Word Formula field works, we can go to the Insert Ribbon tab, Text group, Quick Parts dropdown and choose Field… This will display a list of all the fields available in Word, with the ability to limit the list displayed to fields in certain categories by selecting from the Categories: dropdown:

Because the Formula field shows up in the list as =(Formula) it will be at the top of the list when All categories are displayed (as shown above), or when the Equations and Formulas category is chosen from the dropdown.

We can just click on the Formula… button to create a formula field. You will be prompted to enter the formula and also to set a Number format. The Number format should be familiar if you have ever created a custom Excel number format. The # characters are place holders that allow you to specify the thousand separator character as a comma. You can choose a format from the Number format: dropdown. The format codes can also be edited, or created from scratch:

Our example just multiplies 2 by 3 so we should see:

£ 6.00

It’s important to note that, unlike Excel formulas, Word formulas do not update automatically. You need to select the field, or a range of text that includes the field, and press the F9 key, or right-click and choose Update Field, to recalculate. By default, Word displays the Formula field result rather than the Field Code itself. To see the Field Code, you can right-click on the field result and choose Toggle Field Codes or you can use the Alt+F9 keyboard shortcut to switch Word from displaying field results to the field codes throughout the document.

The code for our simple formula should be:

{=2*3 \# "£#,##0.00;(£#,##0.00)"}

As you can see, the code starts with the calculation and then includes \# followed by our format code in speech marks to set the format.

So far, we haven’t achieved anything particularly useful but, if we insert a small table in Word and enter some numbers, we can create a Formula field code to add those numbers:

Here, we have inserted a simple 4 row table and right-justified the cell contents before entering numbers in the top three cells. We have then clicked in our bottom cell and used the Table, Layout contextual Ribbon tab, Data group, Formula command. This opens a dialog to guide us through creating our field code including a number format. The dialog defaults to using the Formula =SUM(ABOVE). Here is the result in our original table and beneath it, a copy of the table where we have used the Toggle Field Codes command to display the field code rather than the result:

Functions in fields

As we have seen above, it is possible to use some functions as part of a Word formula field, although the list of available functions is very limited compared to Excel. Clicking on the Paste Function dropdown in the Formula dialog box will show the functions that can be used. Most of the available functions are aggregate functions such as SUM(), MIN() and MAX() or logical functions such as AND(), IF() and OR() but usefully the ROUND() functions is also included.

Again, as we can see from our example, Word can refer to cells by position such as ABOVE and LEFT. When using these operators, the formula will take into account the range of cells up to the end of the table or up to a cell containing an invalid value:

200

Sales

600

600.00

Referencing other values

Within a table, cell referencing can also be used such as =A3:

200
Sales	600
600
600.00

It is even possible to allocate a bookmark to a value somewhere in the document and use the bookmark as a reference within a formula. In the following example, we have entered 0.2 in text and then selected it and used the Insert Ribbon tab, Links group, Bookmark command to allocate our Bookmark which we have named VATRate. We have then used the Paste function: dropdown and the Paste bookmark: dropdown in the Formula dialog box to help create our formulas, including using the ROUND() function.

Interesting as this example might be, it is probably getting close to where it would be easier to use an embedded Excel object, particularly when you consider the need to manually recalculate all the formula fields whenever anything changes. You can use the keyboard shortcut Control+A to select the entire document and then press the F9 key to make this easier. There is also an option available in the Display, Printing Options section, to ensure that fields are automatically updated before printing.

Conclusion

You can explore several other Word techniques, and a great deal more, in the ICAEW archive