How to Use Excel Formulas in Word Documents

Excel formulas let you automate your spreadsheets. But how do you use it in a Word document? Here are two ways to do it!

While you can always integrate Excel data into a Word document, it's often unnecessary when all you need is a small table. Fortunately, it's quite simple to create a table and use Excel formulas in a Word document. However, there is only a limited number of formulas that can be used.

For instance, if you're trying to insert sales data in a table, you could add a column for sales, another one for total cost, and a third one for calculating profit using a formula. For each of these columns, you can also calculate an average or a maximum.

Method 1: Paste Spreadsheet Data Into Word

If you already have data populated into a spreadsheet, you could just copy it into your Word document.

1. Copy the cells containing the data and open a Word document.

2. From the top ribbon, click on the arrow under the Paste button, and click on Paste Special.

   

3. You'll see a new window pop-up where you'll need to select what you want to paste the copied content as. Select Microsoft Excel Worksheet Object and select OK.

   
4. Your data should now appear in the Word document, and the cells should contain the formulas as well.

If you want to make any edits, you can double-click on the pasted content, and your Word document will transform into an Excel document, and you'll be able to do everything you would on a normal spreadsheet.

Related: Formatting Tips for Perfect Tables in Microsoft Word

Method 2: Add Formulas in a Table Cell in Word

1. Quickly insert a table in your Word document and populate the table with data.

2. Navigate to the cell where you want to make your computations using a formula. Once you've selected the cell, switch to the Layout tab from the ribbon at the top and select Formula from the Data group.

   
   Notice that there are two tabs called Layout. You need to select the one that appears under Table Tools in the ribbon.
3. When you click on Formula, you'll see a small window pop up.
4. The first field in the box is where you enter the formula you want to use. In addition to formulas, you can also perform basic arithmetic operations here. For instance, say you want to compute the profit, you could just use the formula:

   =B2-C2

   Here, B2 represents the second cell in the second column, and C2 represents the second cell in the third column.

   

5. The second field allows you to set the Number Format. For instance, if you wanted to calculate profit down to two decimal places, you could select a number format accordingly.

   

6. The Paste Function field lists the formulas you can use in Word. If you can't remember the name of a function, you could select one from the dropdown list, and it will automatically be added to the Formula field.

   
7. When you've entered the function, click OK, and you'll see the computed figure in the cell.

Positional Arguments

Positional arguments (ABOVE, BELOW, LEFT, RIGHT) can often make things simpler, especially if your table is relatively large. For instance, if you have 20 or more columns in your table, you could use the formula =SUM(ABOVE) instead of referencing each cell inside the parenthesis.

You can use positional arguments with the following functions:

• AVERAGE
• COUNT
• PRODUCT

For instance, we could calculate the average sales for the above example using the formula:

=AVERAGE(ABOVE)

If your cell is at the center of the column, you can use a combination of positional arguments. For instance, you could sum up the values above and below a specific cell using the following formula:

=SUM(ABOVE,BELOW)

If you want to sum up the values from both the row and the column in a corner cell, you could use the following formula:

=SUM(LEFT,ABOVE)

Even though Word offers only a few functions, they are quite robust in functionality and will easily help you create most tables without running into lack-of-functionality issues.

Updating Data and Results

Unlike Excel, Word doesn't update formula results in real-time. However, it does update the results once you close and reopen the document. If you want to keep things simple, just update the data, close, and reopen the document.

However, if you'd like to update the formula results as you continue to work on the document, you'll need to select the results (not just the cell), right-click on them, and select Update Field.

When you click Update Field, the formula's result should update instantly.

Cell References

There are several ways to reference a cell in a Word document.

1. Bookmarknames

Let's say you give your average sales value a bookmark name average_sales. If you don't know how to give a cell a bookmark name, select the cell and navigate to Insert > Bookmark from the ribbon at the top.

Assume that the average sales value is a decimal value, and you'd like to convert it to an integer. You could reference the average sales value as ROUND(average_sales,0) and this will round the value down to its nearest integer.

2. RnCn References

The RnCn referencing convention allows you to reference a row, column, or a specific cell in a table. The Rn refers to the nth row, while the Cn refers to the nth column. If you wanted to refer to the fifth column and second row, for instance, you'd use R2C5.

You can even select a range of cells using the RnCn reference, much like you would in Excel. For instance, selecting R1C1:R1C6 selects the first six cells of the first row. For selecting the entire row in which you're using the formula, just use R (or C for a column).

3. A1 References

This is the convention that Excel uses, and we're all familiar with. The letter represents the columns, while the numbers represent the rows. For instance, A3 refers to the third cell in the first column.

Word Tables Made Easy

Hopefully, the next time you'll need to use data on a Word document, you'll be able to do things much faster without having to first create a spreadsheet and then importing it into your Word document.

About The Author