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.

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

Kareem Winters

Update 24 May 2019

You should read it

May be interested

How to print multiple Word pages in one sheet of paper
by default, when printing, you print a page of word documents on a sheet of paper. but for the needs and purposes of many people, to save or fit the purpose of using the prints, you want to print many pages of word in a single sheet of paper.
How to print multiple pages on 1 A4 sheet in Word
when printing a word document, you may need to print multiple pages on 1 a4 sheet to support continuous reading of content or save printing paper like when printing 2 pages on 1 side of paper.
Basic tasks in Word 2013
word 2013 is a word processing application that allows you to create a variety of documents including letters, flyers, reports and many other document documents.
How to create automatic table of contents effectively in Word 2007
tipsmake guides you how to create table of contents automatically in word 2007 in a beautiful, clear, scientific and effective way.
MS Access - Lesson 13: Creating Reports (report)
reports (reports) are a way to view and analyze a large amount of data. you can use the report wizard or create an arbitrary report that you need.
How to create automatic table of contents in Word 2016
you can create your own table of contents manually, but that takes a lot of time and effort. the following article details how to create a table of contents automatically in word 2016, with older versions of word you can do the same.
Instructions for copying or moving Worksheet in Excel 2013
the move or copy command allows you to easily move or copy the entire worksheet, including all data and formats in excel, in case you create a new sheet or a new book that wants to copy data from excel. old to.
How to create and use macros in Word
a macro is a command that executes a job script, built into word that automatically runs when you create it, eliminating the need for repetitive tasks in word.
How to create tables in Word on computers
how to create a table in word will help you easily list and summarize data in documents. this is one of the basic word skills that everyone needs to know, so you should master how to create word tables to start getting acquainted with this software.
Create Excel charts that automatically update data with these three simple steps
excel charts help shorten the decision-making process, because we can immediately see the results and things to change. however, the difficulty in processing data and charts is that users must constantly return to the chart and update new data. yes, this problem will be gone when you follow these three simple steps to create an excel chart that automatically updates data.

Create reports automatically in word from Excel's data sheet

You should read it

May be interested

Office information