Sign in or start a free trial to avail of this feature.
7. Creating Table Calculations
Word tables are not as sophisticated as Excel spreadsheets, but they do support some basic calculations. Some of these calculations are quite limited, but others can be quickly and easily deployed.
Discern the uses and limitations of Word table formulas.
Simple calculations in Word tables are possible. You can add, subtract, divide and multiply values from different numeric cells. However, there are two major flaws. The first is that you must manually type the cell reference without any cell reference guide. In other words, you must find the reference yourself by counting the row and column position of each cell. This becomes very cumbersome for larger tables.
The second is that it is not easy to copy these formulas to other cells. You must manually write the formula for every cell.
As a result, the quickest way to create entire columns of calculations like these is to use Excel. Simply copy the numeric data into an empty Excel spreadsheet. Type the first calculation and click on the appropriate cells instead of manually typing the reference. Hit enter and then apply the same calculation to all rows by dragging the square icon at the bottom right of the cell downwards. Copy the new calculated cells, return to Word and select the exact cells you wish to place the cells you copied from Excel. Paste Text only to ensure you don’t upset the formatting of your table.
Word tables are efficient at paste functions. These work by giving the formula a range of cells. For example, the range ABOVE applies the formula to all numeric cells above the cell containing the formula. Other ranges include BELOW, LEFT and RIGHT.
Common paste functions include SUM, which adds all the values in the range; AVERAGE, which calculates the average value in the range; and COUNT, which simply counts the number of cells in the range.
In the previous lesson, we made some changes to the table layout. In this lesson, we'll discern the uses and limitations of Word table formulas.
Like Excel spreadsheets, Word tables can use formulas to create calculations.
Word table formulas are basic and inefficient compared to Excel.
In some cases, it's easier and quicker to create calculations in Excel and copy the results into Word.
However, some useful formulas can be quickly deployed without the use of Excel. We'll continue in the case study document and return to the table we've been working on in the last few lessons.
In a previous lesson, we copied the information from an older table into this table and left the mistakes from the original. The values in some of these cells were determined by calculating numeric data in other cells. However, many of these calculations were incorrect. We'll start from scratch by removing all texts from these cells. We'll select all the cells beneath the Combined column, and type Delete.
We'll also delete the text in the cells adjacent to the Average Scores cell.
We now need to populate the cells in the Combined column.
To do this, we must multiply the quality score by the Customer Care score.
We'll start by placing the cursor in the first empty Combined cell, navigate to the Contextual Layout tab, and click the Formula command.
You will notice that the default formula is SUMLEFT. This will add all numeric values to the left, giving us a value of six. We need to multiply these values so we'll delete this formula.
We'll then enter the equal symbol, which must be at the beginning of every formula.
To multiply the previous two cells, we need to give a reference to these cells.
Let's see if we can click the cell to automatically insert the reference.
As you can see, this does not work. We'll have to manually type the cell references.
If you're familiar with Excel, you'll know that columns are labeled with letters, and rows are labeled with numbers.
The reference to a particular cell is the column letter and the row number.
Quality is column c, and Customer Care is column d.
The current row is four. Therefore, our formula is C4 times D4.
We'll select OK, and see that it correctly calculates the product of the values in those cells. It's obvious that this is not as efficient as Excel. We must manually count the columns and rows to find our reference.
We must also manually enter the formula for each row.
Excel doesn't have these limitations, so it often make sense to build these calculations in Excel, and copy the results to the Word table. We'll do this right now.
We'll copy the relevant cells, and open a new Excel spreadsheet.
We'll then right click the top left cell, and paste by matching destination formatting.
In the next empty combined cell, we'll type the equal sign, click the value in the Quality column, type star, click the value in the Customer Care column and type Enter.
Next, we'll click the cell we just calculated.
You'll notice a small square at the bottom right of this cell.
We'll hover over this square until a black plus icon appears. We'll then hold down the left mouse button, and drag to the bottom row.
This copies the formula for each row in the data set.
We'll now copy the new data and return to Word.
At this point, we need to select the exact cells we're replacing.
Once selected, we'll right click any value and select Keep Text Only. This solution is not ideal, but it's quicker than doing each calculation in Word and it reduces the likelihood of errors.
The exception to this is aggregate formulas, which apply calculations to entire columns or rows.
The bottom row of this table originally contained average scores for each column.
Let's re-create these with an aggregate formula.
We'll click in an empty cell at the bottom of the Quality column and open the Formula menu again.
By default, Word is offering to sum all the values above this cell.
This is not what we want, so we'll delete the text after the equal sign, and select Average in the Paste function drop-down.
We'll then type above and click OK.
This formula has provided a correct calculation for the Average Quality score.
We'll move the cursor to the next column, and repeat the step.
Calculating the average combined score is a little different.
The correct way to calculate this is by multiplying average Quality by average Customer Care.
Using the average formula on the Combined column can be inaccurate.
Note that I just typed the value directly.
For one-off calculations like this, using Word or Excel is often slower than doing the calculation by hand or on a calculator. We've now completed every calculation in this table.
Before we finish, you may have noticed that the values we pasted in are not the correct font or size. We'll fix this by selecting those cells, navigating to the Home tab, changing the font to Arial, and the size to 10.5.
We'll stop here.
As we saw in this lesson, Word tables can perform some calculations but this capability is somewhat limited. In many cases, you'll be better off performing table calculations in Excel and copying the results into your document.