This is going to be another one of those frustration fueled posts, so apologies in advance. I’ve literally just spent all day trying to work out how to do simple sums in the Word tables function [for my training course assignment]. After so much effort I just had to share. Hope this helps some other frustrated user out there.
Okay, I’m going to start with the assumption that most people will know how to create a simple table in Microsoft Word 10. I’m further going to assume that most people are like me, and put their headings in the first row – so we know what data to enter where. The assumption about the headings is important because it will have an impact later on.
So, I created the following table with dummy data :
Given how few items there are in this table you could easily do the sums in your head and just type them in. But why whip out the calculator when the table should be able to do the sums for you?
The first sum we need to do is to multiply the unit cost of Part #123 by the Unit Qty – i.e. 100 x 2. For this we will need the formula using PRODUCT. This is what we want to see :
And this is how we do it :
Click inside the Sub-Total cell of Part #123. Next, click the ‘Layout’ tab at the top of your screen, and select ‘Formula’.
A popup will appear :
Under Formula, Word automatically displays the formula it thinks you might want to use. ‘=SUM(LEFT)’ is not the one we want so either backspace over it, or highlight the whole thing and delete.
Hit the equals sign on your keyboard ['=']. Now click the arrow next to ‘Paste function’ to display the list of available functions. The one we want is PRODUCT.
Click ‘PRODUCT’ and Word will paste it into the ‘Formula’ box for you. It will also add two brackets – ( ) – and the cursor will be inside those brackets, ready for you to tell Word which numbers you want multiplied. Type the word left. Yes, the actual word for left. See below :
While you’re there, click the arrow next to ‘Number format’, scroll down the list and select the decimal format. Click OK and Word will multiple all the numbers to the left of the current cell until it reaches a blank cell, or, as in our case, it reaches some plain text. The number that results from that multiplication will be inserted into the Sub-Total field of the table.
Now, as we all know that 500 x 1 = 500, you could just type the next two numbers in the Sub-Total fields, but use Product instead for practice. When you’re done, your table should look like this :
Now we’re going to add up those three numbers, and have Word put the result in the Total column. But before we do that I need to explain about co-ordinates.
In spreadsheets, the column names and row numbers are always visible, like so :
In Word tables, however, we have to imagine those nice, neat co-ordinates. I didn’t imagine them properly and spent hours trying to work out why my formulas weren’t working. You see, I counted the first row of data as row 1. Wrong. That honour goes to the column headings I put in. Thus the co-ordinates I need to enter into the formula to add up the three Sub-Total amounts are e2:e4 [not e1:e3].
Click inside the empty cell in the Total column, go to Layout, Formula. Because Word can’t find any numbers to work with, the popup just shows the equals sign ['=']. As before, display the list of available functions, but this time, select SUM.
With the cursor inside the SUM brackets, type :
e2:e4 [e2 colon e4]
Remember to select the decimal number format, and the popup should look like this :
Click OK and our table should now look like this :
Just two more formulae to do!
No quote or invoice would be complete without our beloved GST. We could just move the decimal point one place to the left to get our 10% GST, but that would be too easy. ;)
In the empty cell next to GST, click inside the cell, open Layout, select Formula and delete the existing formula from the popup. This time we are doing it ALL manually.
The Total column corresponds to column F, so the total amount for parts corresponds to f5 [because the headings are on row 1].
This may sound obvious, but trust me, it’s very easy to forget, especially when you’re tired.
Now we need to write a formula to divide f5 by 10. The formula is :
Note : the ‘divide’ sign is found on the top of the numeric key pad of your keyboard. Don’t forget to select decimals as the number format. Click OK and you should see something like this :
Our last task is going to be the easiest of the lot. All we have to do is add the GST amount to the figure for total Parts.
Click inside the empty TOTAL cell, click on Layout [unless you've already done so], and select Formula. The popup shows the exact formula we need, already selected for us – i.e. =SUM(ABOVE). This formula will add up all the numbers that occur above the cell in which the formula is located. That means it will add Parts and GST only because we kept the rest of the column empty.
Before we click OK, select $ from the number format list in the popup :
Now click OK and see the results of our hard work!
And of course, the $ sign is in the wrong place. -sigh- To make this beast look as pretty as we can, we’re going to make the Total column fit the widest entry in it.
Move your cursor to the top of the Total column. When it changes to a down arrow, click to highlight the whole column :
The Total column is now wide enough to show the $ sign properly, however the numbers still don’t line up nicely with the right hand side of the column.
With the Total column still highlighted, search the Layout tab until you find the ‘Align right’ icon :
Click, and ta dah!
Before I shuffle off for a well-deserved cup of coffee, I have to say that this has been a lot of work to achieve something Excel probably could have done faster. The point is though, with a spreadsheet you end up with a spreadsheet. With Word tables, you end up with something that can be incorporated into a complete, professional looking document. That said, I wouldn’t dream of using a table for masses of data – that is not what it is designed for. Tables are a quick [once you know what you're doing] and dirty tool for smallish jobs.
Remember, use the right tool for the right job. :)
Okay, I’m done.