Word 10 Tables – how to add, multiply and percent

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 :

basic table 1

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 :

basic table product 1

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’.

select formula

A popup will appear :

select formula popup

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.

select formula popup productClick ‘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 :

select formula popup product brackets left

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 :

select formula popup product brackets left done

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 :

coordinates in excel

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].

Onwards.

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 :

parts total popup

Click OK and our table should now look like this :

parts total table

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].

TABLE COORDINATES 2This 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 :

=(f5/10)

percent popup

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 :

percent popup done

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 :

grand total

Now click OK and see the results of our hard work!

grand total 2

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 :

auto fit column 1With the Layout tab open, click Autofit, and select ‘Autofit contents’.

auto fit column 2

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 :

align right

Click, and ta dah!

align right 2

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.

Cheers

Meeks

About these ads

About acflory

I am the kind of person who always has to know why things are the way they are so my interests range from genetics and biology to politics and what makes people tick. For fun I play online mmorpgs, read, listen to a music, dance when I get the chance and landscape my rather large block. Work is writing. When a story I am working on is going well I'm on cloud nine. On bad days I go out and dig big holes... View all posts by acflory

14 responses to “Word 10 Tables – how to add, multiply and percent

Don't be shy!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 895 other followers

%d bloggers like this: