Add Columns of Numbers in ID/QXP

July 27, 2007 - 1:00am ||| 2 Comments | Add new

The other day I was laying out a data-heavy table in InDesign. The bottom row was supposed to contain sum totals of the numbers in each column, but I didn't have those figures. All I had was the client's Microsoft Word file containing the original columnar data, and no sums there either.

Sighing, I hauled out my adding machine (translation: I pressed F12 on my Mac to open up the Calculator widget) and was about to start entering each column's numbers to get totals when I thought, "This is ridiculous! I'm on a computer more powerful than the ones used to send people to the moon! This is not a $2500 adding machine!" I pressed F12 again to close the widgets and thought about it for a bit.

It is very aggravating that a program that allows you to create tables doesn't include even the simplest functions to add up the numbers in one of its columns. Can I get a witness? Yeah, yeah, I know a layout program is not an accounting or spreadsheet program, and designers often use tables to create things without a single number in it.

But. Still. This is a computer! C'mon! 

——-
Microsoft Word to the Rescue
——-
After a minute of thinking, I realized that of course, I already had a program that can add up numbers in a column: Microsoft Excel. Unfortunately, I'm one of the three business owners on the planet who doesn't understand Excel. I mainly use it to open OPS's (Other People's Spreadsheets) and wonder how they did that, and why didn't they just use Filemaker.

I *do* know Microsoft Word, though, so I opened up the original Word file the client had sent. 

Now, if you're an experienced Word user, you're probably already thinking this: "Use Word's AutoSum feature." For those of you who seldom use Word (it's amazing to me how many designers never open it up), what the Word geeks are thinking of is the the exact feature I want in my layout programs; an "add this up!" button in Word's Tables and Borders toolbar (View > Toolbars > Tables and Borders).

The AutoSum button is simple to use. You put your cursor in an empty cell below a column of numbers, click the AutoSum button in the toolbar, and Word inserts the sum of the numbers above into the cell. Numbers in parentheses or prefixed with a negative sign (a hyphen) are correctly calculated as negative amounts. Alternatively, you can put your cursor in an empty column at the far right of a table, and click AutoSum to get the total of the numbers in the row.

But, my fellow Word freaks, AutoSum only works on tables, which my document didn't have. Instead of a table, the user had aligned the columns of numbers with multiple tabs and space runs. I'd need to spend time cleaning up all the extra spaces and tabs separating the columns so that I could convert it to a clean table. And that almost defeats the purpose of looking for a fast way to sum up columns of numbers in a layout program.

Then I thought, aha! I do have a table! I could flip back to InDesign, select the table, copy it, and paste it into a Word document, where it comes in as an editable Word table. I love that you can round-trip tables from Word to InDesign and back, though some formatting may be lost in transit. (Unfortunately, QuarkXPress tables aren't constructed the same. You'd need to convert a table to text in XPress first, copy the tabular data, paste it into Word, and then convert it back to a table via Word's Table > Convert > Text to Table feature.)

Anyway, once I had a clean table in Word, I could just insert an empty row at the bottom of it and do the AutoSum magic for each column, then copy/paste the last row back into InDesign (or XPress, if I was using it). But then I remembered: AutoSum requires every cell in the column it's going to add to be filled with a number. The columns in my table were riddled with text entries (for example, "n/a" or "see below"), merged into other cells, or were empty. Argh! I'd still need to do clean-up work just to get the sums.

——-
Secret Word Feature: Calculate
——-
Finally, I remembered that Word has a far more forgiving and flexible AutoSum feature called "Calculate." It doesn't require a table, and it's smart enough to ignore empty paragraphs and text in a selection. Not even Excel can do that.

"Calculate" used to get some respect in Microsoft Word. It was a full-fledged command in the Tools menu back in the day. But about ten years ago, I think with the release of version 52 B.C., Microsoft retired it into a dusty, neglected corner in the Tools > Customize > Commands dialog box.

Luckily you can still retrieve it. Open up that dialog box (in either the Mac or Windows versions of Word), click on the Tools category, and scroll down until you see the command "Tools Calculate." Drag the command and drop it onto a toolbar you use all the time. (I dropped it onto the Standard toolbar.)

Now you can make just about any sort of selection in your Word document — if you don't have a Word document to begin with, just copy/paste a table or text from your layout program into a new Word file and run it from there — and Tools Calculate will tell you the sum of the numbers contained in the selection.

And that's the beauty of it. Unlike AutoSum or even Excel, the Calculate command ignores everything that's not a number in the selection when it computes a total. It works with selections of table columns or rows (even if the selection includes text, merged cells, or empty cells). It works with columns of numbers made with tabs and spaces. It even works on sentences and paragraphs!

For example, if you select the sentence, "Mary had 15 apples, Joe had 10, and Jennifer had 3;" and click on the Tools Calculate button, the status bar at the bottom of the document window reports, "The result of the calculation is 28." If poor Joe had -10 apples (negative 10), the message would say, "The result of the calculation is 8." Pretty smart!

You don't have to memorize what the status bar says. Word automatically puts a copy of the calculation in your computer's Clipboard memory. So after running the Calculate command, you could click a text insertion point in the Word file (or in any document in any program), choose Edit > Paste (Command/Control-V), and the calculation result is pasted into the text flow, matching the current formatting.

And that's how I ended up adding up the columns of numbers in my InDesign table. In the original Microsoft Word document, I selected a single column's worth of numbers (with some unavoidable text and empty tabs mixed in) by holding down the Option key while dragging with the text cursor. (That's another little-known Word feature, by the way, that I wish my layout programs had. Option/Alt-dragging in Word lets you make columnar text selections through multiple paragraphs.)

With a column selected, I clicked the Tools Calculate button on my toolbar, flipped over to InDesign and pasted in the result at the bottom of the matching column in the table. Rinse and repeat a few times, and I was done.

Who needs a calculator when you've got Word? And it can be used as a word processor, too.

Comments (Subscribe to Comments RSS)

1 June 17, 2011 - 2:38am by petern (not verified):

It would have been simpler if you just copy-pasted the numbers from Word to Excel, then had Excel to calculate the sums in each columns (in a single step by using Σ), then copy-pasted back them to iD (in a single step again).
 :)

2 February 9, 2012 - 3:15am by Phil Inman (not verified):

yeah this is a really old thread. but tonight im getting ready to load windows 3.1 so i can use word 1.1. which will allow you to select numbers in a selection box and add with calculate. Invaluable for my ramdom calculations for my small job quotes in construction work. think. type select add.

if you ever look anymore.

Post new comment

The content of this field is kept private and will not be shown publicly.
By submitting this form, you accept the Mollom privacy policy.