Performing Calculations in a Table

When you want to perform a calculation on numbers in a Word table, you can create a formula that uses a built-in mathematical function. You construct a formula by using the tools in the Formula dialog box, which you can access by clicking Formula in the Data group on the Layout contextual tab. A formula consists of an equal sign (=), followed by a function name (such as SUM), followed by parentheses containing the location of the cells on which you want to perform the calculation. For example, the formula =SUM(Left) totals the cells to the left of the cell containing the formula.

To use a function other than SUM in the Formula dialog box, you click the function you want in the Paste Function list. You can use built-in functions to perform a number of calculations, including averaging (AVERAGE) a set of values, counting (COUNT) the number of values in a column or row, or finding the maximum (MAX) or minimum (MIN) value in a series of cells.

Creating Table Styles

If none of the predefined table styles meets your needs, you can create your own styles for tables in much the same way you create styles for regular text.

To create a table style:

1. On the Design tab, in the Table Styles group, click the More button, and then click New Table Style.

   The Create New Style From Formatting dialog box opens.

2. In the Name box, type a name for the new style.

3. Click the Apply formatting to arrow, and in the list, select the table element for which you are creating the new style.

4. Select the formatting options you want, until the table shown in the Preview area looks the way you want it.

5. If you want the style to be available to tables in other documents based on this template, select that option, and then click OK.

To apply a custom table style:

1. Select the table element to which you want to apply the new style.

2. On the Design tab, in the Table Styles group, click the More button, and under Custom, click the thumbnail for your custom style.

Although formulas commonly refer to the cells above or to the left of the active cell, you can also use the contents of specified cells or constant values in formulas. To use the contents of a cell, you type the cell address in the parentheses following the function name. The cell address is a combination of the column letter and the row number-for example, A1 is the cell at the intersection of the first column and the first row. A series of cells in a row can be addressed as a range consisting of the first cell and the last cell separated by a colon, such as A1:D1. For example, the formula =SUM(A1:D1) totals the values in row 1 of columns A through D. A series of cells in a column can be addressed in the same way. For example, the formula =SUM(A1:A4) totals the values in column A of rows 1 through 4.

When the built-in functions don’t meet your needs, you can insert a Microsoft Office Excel worksheet in a Word document. Part of the Microsoft Office system, Excel includes sophisticated functions for performing mathematical, accounting, and statistical calculations. For example, you can use an Excel worksheet to calculate loan payments at various interest rates. You can insert Excel worksheet data into a Word document in the following ways:

• By copying and pasting. You can open Excel, enter the data and formulas, and then copy and paste the data as a table in a Word document. The data is pasted as regular text, with the formulas converted to their results.

• By linking. While pasting Excel worksheet data into a Word document, you can link the version in the document to the original source worksheet. You can then double-click the linked object in the document to open the source worksheet in Excel for editing. After you edit and save the worksheet, you can return to the document, right-click the linked object, and then click Update Link to display the edited version of the data.

• By embedding. You can create an Excel worksheet directly in a Word document by clicking the Table button in the Tables group on the Insert tab, and then clicking Excel Spreadsheet. The worksheet is created as an object with Excel row and column headers, and the Excel tabs and groups replace those of Word so that you can enter data and manipulate it using Excel.

  Tip 

  If you change a value in a Word table, you must recalculate formulas manually. If you change a value in an Excel worksheet, the formulas are automatically recalculated.

In this exercise, you will perform a few calculations in a Word table. Then you’ll copy and paste worksheet data, link the same data, and enter the same data in an Excel object so that you can see the three different ways of working with Excel data.

Use the 05_Calculations document and the 05_LoanData workbook. These practice files are located in the Chapter04 subfolder under SBS_Office2007.

Open the 05_LoanData workbook in Excel, and then open the 05_Calculations document in Word.

1. Save the practice file in the Chapter04 folder with the name My Calculations.

2. In the table displayed in the document, click the cell below the Total column heading, and on the Layout contextual tab, in the Data group, click the Formula button.

The Formula dialog box opens.

3. Select the contents of the Formula box, and then type =C2*B2.

4. Click the Number format arrow, and in the list, click $#,##0.00;($#,##0.00).

5. In the Number format box, delete .00 from both the positive and negative portions of the format, and then click OK.

You have told Word to multiply the first dollar amount under Unit Price by the quantity on the same row and to display the result as a whole dollar amount. Word enters the result, $60,000, in the cell containing the formula.

6. Repeat Steps 2 through 5 for the next two cells under Total, adjusting the cell addresses appropriately.

7. In cell B4, change 2 to 3, right-click the formula in cell D4, and then click Update Field.

Word recalculates the formula and enters the new result, $75,000, in the cell.

8. Change the Unit Price of the 24 ft. truck to $42,500, and then update the corresponding total.

9. Click cell D5, and in the Data group, click the Formula button.

10. With =SUM(ABOVE) in the Formula box, set the Number format to whole dollar amounts (following the method in Steps 3 and 4), and then click OK.

You have told Word to add the amounts in the Total column. Word enters the result, $177,500, in the cell containing the formula.

11. Press to move to the end of the document, and then on the Windows taskbar, click the Microsoft Excel button.

Troubleshooting 

If you have hidden your Windows taskbar, as we have, point to the bottom of the screen to make the taskbar appear so that you can click the Microsoft Excel button.

12. On Sheet1 in the 05_LoanData workbook, select cells A1:B8 by dragging through them. Then on the Home tab, in the Clipboard group, click the Copy button.

The worksheet data is copied to the Clipboard. From there it can be pasted into any Microsoft Office program.

13. Redisplay the My Calculations document. Then on the Home tab, in the Clipboard group, click the Paste button.

Word pastes a copy of the worksheet data in the document as a table.

14. Press , and then in the Clipboard group, click the Paste arrow, and click Paste Special.

The Paste Special dialog box opens.

15. In the As list, click Microsoft Office Excel Worksheet Object, select the Paste link option, and then click OK.

Word pastes a second copy of the worksheet data as a linked table on a new page.

16. Double-click the new table.

The linked worksheet opens in Excel.

17. Click cell B2, type 6, and then press .

Troubleshooting 

If someone has already worked through this exercise using the practice files on your computer, 6.0% might already appear in cell B2. In that case, change the value to 5.0%.

Excel recalculates the formulas in the worksheet to reflect the new interest rate.

18. Save and close the workbook, and quit Excel.

19. In Word, right-click the linked table, and then click Update Link.

Word updates the table to reflect the change you made to the worksheet data.

20. Press to move to the end of the document, press twice to add some space, and then save the document.

21. On the Insert tab, in the Tables group, click the Table button, and then click Excel Spreadsheet .

Word inserts an Excel object in the document.

22. In row 1, type Rate, press , and then type 5%.

23. Type the following in rows 2, 3, and 4:

2	Years 3
3	Amount $155,000
4	Payment

24. With cell B4 active, on the Formulas tab, in the Function Library group, click the Financial button, scroll the list, and then click PMT.

Excel enters =PMT() in cell B4 and then opens the Function Arguments dialog box so that you can enter the information needed to calculate the monthly payment on a loan of $155,000 at 5% interest for three years.

25. In the Rate box, type B1/12 (the annual rate per month), in the Nper box, type B2*12 (the number of years expressed as months), and in the Pv box, type B3. Then click OK.

Excel calculates the formula and enters the result, $4,645.49, expressed as a negative because it is money you are paying out.

Tip 

To express the payment as a positive, you can insert a minus sign between the equal sign and PMT in the formula.

26. Drag the black handle in the lower-right corner of the Excel object up and to the left, until the frame of the object is just big enough to enclose the cells with data in them. Then click a blank area of the page to deactivate the object.

The object appears on the page as a table with barely visible borders around its cells.

27. Double-click the object to activate it in Excel again, change the entry in cell B1 to 7%, press , and then click a blank area of the page.

The object’s formulas have updated the monthly payment to reflect the change.

Close the My Calculations document without saving your changes.