Formula Builder for Microsoft Word

Formula Builder for Microsoft Word is an add-in (plug-in) for Microsoft Word that aids in the creation of formulas in Word documents. The program is compatible with Microsoft Word 2003, and . Formula Builder comes in two versions. The Basic Edition provides the core tools for building formulas in Word. The Accounting Edition includes all the features of the Basic Edition, plus tools that help create and manage tables containing financial data
The program includes tools that assist in creating formulas. Formulas are written like they are in Microsoft Excel using the A1 reference style for cell references. Cell references are added to a formula by double-clicking the cell. The program also includes an auto-sum component. Formulas may be written to include relative and absolute cell references. Relative cell references in formulas are updated as expected when the Formula Builder's built-in copy and paste functions are used. Other program capabilities include a tool for highlighting a formula's cell references, a tool for linking data from a Word table to the written portion of the report, and a tool that formats numeric data as fixed, standard, currency, percent, or custom. The Accounting Edition includes a tool for creating preformatted tables for financial reporting.
Formula Builder Handles Certain Microsoft Word Formula Limitations
Formula Builder was developed to handle many limitations of Word's built-in formula function.
Cell Reference Display
Although formulas may be written using the A1 style(i.e., =B2+B3) Word doesn't display the cell reference. Formula Builder, however, provides cell references in a number of different ways. For example, cell references may be added to a formula by double-clicking the cell.
Range Functions
There are issues regarding the use of ABOVE, BELOW, LEFT, or RIGHT as arguments in functions like SUM and AVERAGE that can lead to unexpected results. For example, when using =SUM(ABOVE), the program works from the bottom up in determining the range used in the formula. Unfortunately, it stops prematurely when it hits an empty cell, a cell with text, or a negative number surrounded by parenthesis, so the range intended may not be the range used in the computation. This may not be what the user wants. With Formula Builder, the user highlights the range of cells she wants included in the calculation. The range gets added to the formula by clicking a button, so she knows exactly what cells are included in the calculation.
Cell Reference in Formulas
A key advantage of using a spreadsheet like Microsoft Excel is the ability to design a formula once, and then copy that formula down other rows or columns. Because spreadsheets update relative cell references when formulas are copied and pasted to other cells, the user doesn't need to create each formula separately. However, when formulas are copied in Word the pasted cell references remain the same. For example, if the user copies the formula A3-B3 in cell C3, and pastes it one row down to cell C4, the pasted formula will still be A3-B3 although the user's intent was for the cell references to change to =A4-B4. Using this same scenario, let's say the user wanted to subtract Column A from Column B with the results displayed in Column C from rows 3 through 22. To achieve this in Word, the user would need to write twenty separate formulas.
Formula Builder has its own copy and paste functions called Formula Plus Copy and Paste. Formula Builder updates relative cell references when Formula Plus Copy and Paste is used to copy formulas. If the user needs a formula to maintain a mix of relative and absolute cell references, this can be achieved by assigning bookmark names to those cell references that need to stay absolute. When bookmarks are used in formulas, the user must be careful to bookmark the contents of a cell, not the cell itself, or else the formula calculations will be incorrect.
When a bookmark is created using the Formula Builder Wizard, just the cell's content is bookmarked.
Microsoft Word Formula Limitations Not Handled by Formula Builder
Some Microsoft Word formula limitations are not solved by Formula Builder.
Formula Updates
Word doesn't update formula fields automatically. When data is changed, formula fields must be update manually by selecting the field and clicking F9. Word 2007 has an option to update fields before printing. Formula Builder recalculates automatically before the document is saved or printed (although this can be disabled).
Problems When Tables Are Modified
If a table containing formulas is modified, formula cell references are not updated. For example, if a row is inserted ten rows from the top, cells referenced in formulas above the insertion would still be correct because their row numbers didn't change. However, cells referenced in formulas below the inserted row will be incorrect because their row numbers should have been incremented by +1 but remained unchanged instead. This issue could go unnoticed until formulas are recalculated.
If the user is satisfied with a formula's calculation and doesn't want the results changed there are a couple of options. Word allows the user to "lock" fields so that the results are not updated. To lock a field select it and press CTRL+F11. To unlock it press CTRL+SHIFT+F11. <ref name="WDField" /> Formula Builder has a tool that converts selected formula fields to text.

Main Menu

See Also

Main Menu