Did you know Word can contain formulas? Yes. It can.
For simple calculations you don’t need to use Excel.
Estimated learning time 5 min
Contents
The Need
Word is not Excel.
Word tables can contain data. Therefore, Word does provide a limited set of formulas.
This eliminates the need to create an Excel file for performing simple calculations.
Let us see how we can use these to our advantage.
Tables and Formulas
Add a table containing some numbers. Keep the last column and last row empty. We will use these to add formulas.
Here is the base table.
Now go to the first row, last column and choose Table Tools – Layout – Formula
Word understands that you are in the rightmost cell of the table and suggests a formula
=SUM (LEFT)
For now, click Ok. The calculation is shown immediately.
Now let us do the same thing for the last row of the first column.
This time the formula is SUM (ABOVE)
Now, click inside the result and see that the background becomes gray in color.
This means that it is a field – a calculation.
If you click inside and it does not become gray, you have to change one option in Word – File – Options – Advanced (scroll down) as shown below. The Field Shading option should be When Selected. It means that show the gray shading when you click inside a field. Otherwise, it will look like regular text. This avoids distracting gray fields being shown all over the document. But also gives you a visual indication that it is a field, in case you are looking at it directly by clicking inside it.
Formulas are NOT calculated dynamically
Now change the numbers in the first column. None of the formulas are updated.
Select the table, right click and choose Update Fields to refresh the formula results.
If you have lots of formulas, select the entire document (CTRL A) and then right click and choose Update Field.
The numbers must be contiguous
If there is any text in between, the calculation fails. It needs contiguous numbers.
More functions
Open the Formula dialog dropdown to see more functions.
The following functions are available:
and, average, count, defined, false, if, int, max, min, not, or, mod, product, sign, sum, round, true
You can change number formats as well.
Bookmarks
These are like external variables. For example, there is a 5 % tax.
I have written 5 elsewhere (outside the table) and I want to use the tax rate in multiple places and tables.
No problem. Select that number and choose Insert – Bookmark – give it a name Tax.
Now the formula can use the bookmark.
Here is the result.
Excel like formulas also work
Think of each cell of the Word table as though it was an Excel sheet starting with A1.
Now type a formula exactly as you would in Excel.
Here is the result.
Automatic reference change does not work. If you copy that formula to all the rows, it will still show the same result. So think of it as a fixed formula $A$1 / $C$1
Add formulas anywhere
If you use bookmarks as shown above, you can use formulas anywhere – not just within tables.
For example, if I have two bookmarks Length and Breadth as shown here…
You can type a formula using these bookmarks.
To create a formula anywhere press CTRL F9
Curly braces will open with gray background. Type the formulas as shown here.
When you finish, press F9 to finalize the formula and calculate the result.
Change the input values and right click in the field – Update Field – to show new results.
When are Word formulas useful?
Simple totals, easy calculations where the row and column positions are unlikely to change. If you use Above, Left type of syntax, and if the rows or columns increase, you have to refresh the formulas manually.
Remember that table borders can be removed – still the formulas will work.
For complex calculations it is always better to use Excel.
Behind the Scenes: Word Fields
Actually, Word contains many things which are dynamically calculated – like formulas in Excel. These things are called Fields.
Many of you may not have used fields directly. But indirectly we use them all the time. The most commonly used dynamic formula in Word is the Page Number!
There are hundreds of them and they are very powerful.
How do you know there is a field? If you click inside the field and it becomes gray in color, that means it is a field.
Word provides fascinating collection of extremely powerful dynamic fields. We will cover fields in detail in separate articles.
***
One Response
did not know about using bookmarks in table formulas. very. nice. also whenever i did a Autosum for a COL and wanted same for subsequent COLs, have used F4 repeat key as it does not allow to copy formulas like we do in Excel. so found F4 very useful here. Thanku.