Create reports automatically in word from Excel's data sheet

When making reports in Word using data from Excel spreadsheets, people usually do two jobs:

- Job A : Enter the data into the Excel table to calculate.

- Job B : Open Word documents to update calculation results from Excel (the text of the report is generally not changed much).

Create reports automatically in word from Excel's data sheet Picture 1

Figure 1 : Excel, previous period data used for July 2005 reports

Manually updating Word documents is confusing and costly when there are many data. There is a way to automatically link data from Excel to Word documents so you only need to do B work once, then every time you finish working A, Word automatically updates the changes and you only printing a report without having to do B again.

For example, we consider the passenger transport report of 4 car manufacturers HTX, LTD, QD and CT. Every month you need to include the data of the current month and the previous month to compare the increase and decrease. Currently we need to do data analysis in August 2005.

Step 1 ( work A ): In H.1, we have available an Excel spreadsheet with data of the previous period (July 2005) with the formulas for calculating the total number of passengers, increasing the passenger rate compared to last month . we just need to enter the data of the report month, Excel will automatically calculate the remaining numbers. After the import is completed, it is calculated with the current period data (August 2005) as H.2.

Create reports automatically in word from Excel's data sheet Picture 2

Figure 2 : Excel, current period data used for August 2005 reports

Step 2 : In H.3, analyze the data obtained in H.1. What needs to be done is to correct the data of the previous period into the data of this period in that analysis table (ie, modify H.3 to H.4). Now we will link data from Excel to Word so that the following periods need only be corrected in Excel and then tell Word to automatically update data from Excel.

First, go to Excel spreadsheet, copy the data box needed as C2 in H.2 (the box is highlighted in H.2) and then go to Word and put the cursor in the place where you need to correct the data (immediately before the number 272,601 in H.3). Delete the value of 272,601 in H.3 then click on the Edit> Paste special menu, the Paste special panel appears. You select Paste link> Unformated Text and then OK (these operations are temporarily called Paste link), the new part is pasted as 273,780 as in H.4 but the background of this number 273.780 is gray. Do the same in other figures that need modification. (Note that when copying from Excel, you must copy the whole cell, not just fill in the number and copy).

Create reports automatically in word from Excel's data sheet Picture 3

Figure 3 : Word, analyzing data of H.1

Note, if the area is Paste link is not gray, do the following: go to Tools> Options menu, select the View tab, Field shadding select Always then OK .

Particularly for figures that may have a negative or positive value, and this value affects the text in the Word document, it is a bit more complicated. As for the reduction of 44.44% in H.3: the previous month decreased by 44.44% because the value in Excel was -44.44 respectively (in H.1). This month the value in Excel spreadsheet is 16.39 (in H.2), we must display the text as an increase of 16.39% in Word. How to do the following: first you open the Excel spreadsheet (H.2), for those columns that show a decrease or decrease (in this example, columns D and E) we have to create 2 extra columns to use show text for each column. We make column D as follows (column E does the same):

Create reports automatically in word from Excel's data sheet Picture 4 Figure 4 : Word, analyzing data of H.2 - Insert 2 columns F and G to complement column D as H.5. In which column F represents the sign of column D and column G represents the absolute value of column D.

- Enter the formula F2 box:

= IF (D2 = 0; "does not increase or decrease"; IF (D2> 0; "increase"; "decrease"))

This function considers the value of cell D2, if = 0, displays "no increase or decrease", if> 0, displays "increase", if
Copy the above formula for cells F3, F4, F5, F6.

- Enter in cell G2 formula:

= IF (D2 <> 0; ABS (D2); "")

This function takes the absolute value of cell D2.

Copy the above formula for cells G3, G4, G5, G6. You will get the same details as H.5.

Now go back to Word in H.3:

Create reports automatically in word from Excel's data sheet Picture 5 - Delete the word "reduced", by Excel copy the box F5 in H.5 then Paste link to Word.

- Delete "44,44", through Excel copy the G5 cell in H.5 and then Paste the link into Word.

Do the same for other parts that need modification.

If done correctly you will get the August 2005 report like H.4.

From the following month, simply enter the data in the Excel spreadsheet, open the Word document, click the Edit> Select All menu and then click Edit> Links> Update Now menu, immediately update the data from Excel. Word.

Note, for Word to not automatically update the data when opening the file: go to Tools> Options> General menu , uncheck the " Update automatic links at Open " section. If not, every time you open a Word file with linked data, Word will always ask for updates.

ISOTOPES
Email : dongvi@gmail.com