Translating Excel into Word.

I am something of a Wiz at MSExcel.  Being an accountant, that is almost mandatory.  With our new accounting system though, I need to become very proficient in MSWord.  For the geeky types, that is like being really good at Algebra, but needing to be just as good in English Lit. or fluent in Spanish, but needing the same proficiency in German.  They are completely different ways of thinking.  I am using Office 2007, so the instructions are geared for that release.

The challenge has been to take forms that my customers need, that were done in Excel, and convert them to a Word template.  Once that is done, our new accounting system uses the mail merge function to fill in added fields automatically.  After it is set up, I only need to enter information in once and I am done.  In the old system, the same information was being entered up to four different times.

The first task was just to get the form to fit on the page.  I found you could select all the cells you need in Excel, copy & paste into Word, but it always goes screaming off the page.  I could select the table, right click and set it to auto fit the window, but all the writing got scrunched up.  At first I thought I needed to play with font and cell size, but it ended up being much easier.  After you resize, you keep the table selected and go to page layout, shrink the margins for the selected text only and boom, done.  Almost no modification of the table is needed after that.  I did find it is best to go through the form in Excel  and do any merging of cells first.  It will bring over fewer cells to mess with in the table in the Word doc, though you can still merge cells there too.

The second task(s) was even sillier; getting the date to auto fill.  The merge source has certain dates it fills like “last payment” and “period to”, but no current date info or the first of the month.  In Excel, you use various formulas to get the info you need, in Word it is just a matter of format.  Again, after playing with it tenaciously (which is how I play most of the time) I found a simple answer.  Here were the two I needed (for now):

  1. Notary date – ex. 18 day of May, 2011
  2. First of the month – ex. 5/1/2011
In both cases, you go to the Insert tab, in the Text section, select Quick Parts, field and date.  I selected the first one and in the box above, you can dictate the format you want the date in.  Here are the formats to get the two I needed:
  1. Notary date – [DD] day of [MMMM, YYYY] – You have to enter two date fields, one for your day and one for the month and year since word sees the letters in “day of” as formatting.
  2. First of the month – DD/1/YYYY
Now the date is auto filled for the day I print the document.  I am sure I will encounter many other obstacles, but like any other learning process, it gets easier as you go.