Data merge word and excel how to#

Notice that Excel filled the cells down in that column, using the VLOOKUP formula. The Orange part means 'look in all the cells in the Orange table.' The 2 means 'get the value from the second column,' and the 0 means 'return the value only if there's an exact match.' Press Enter, and the completed formula looks like this: Type another comma, 2, another comma, and 0-like this: ,2,0 Type a comma, and select the entire Orange table with your mouse so that 'Orange' is added to the formula. The ID]] part means 'get the value in this same row from the Order ID column.' The partially completed formula looks like this: In the Blue table, pick the first cell in the Order ID column, 20050.

In the Blue table, in the first cell beneath Sales ID, start writing this formula: Now, the Blue table is five columns wide, including the new Sales ID and Region columns. Paste the headings into the cell, to the right of the Product ID heading of the Blue table. To do this, let's paste the table headings Sales ID and Region into the cells to the right of the Blue table, and use VLOOKUP formulas to get the correct values from the Sales ID and Region columns of the Orange table.Ĭopy the headings Sales ID and Region in the Orange table (only those two cells). We need to ensure that the Sales ID and Region values for each order align correctly with each unique order line item. After you paste it into the worksheet, press Ctrl+T to convert it into a table, and then rename the table Orange. After you paste it into the worksheet, press Ctrl+T to convert it into a table, and then rename the Excel table Blue.

Here's the data for the Blue table, which you can copy into a blank worksheet. If we were to simply copy-and-paste the data from the Orange table, the Sales ID and Region values for the second line item of order 20050 would be off by one row, which would change the values in the new columns in the Blue table. Order ID values repeat in the Blue table, but Order ID values in the Orange table are unique. We want to merge the Sales ID and Region columns with the Blue table, based on matching values in the Order ID columns of the Orange table. So, Order ID 20050 has two items, Order ID 20051 has one item, Order ID 20052 has three items, and so on. In the example shown below, you'll see two tables that previously had other names to new names: 'Blue' and 'Orange.' In the Blue table, each row is a line item for an order. Merge two tables using the VLOOKUP function By using VLOOKUP, you can avoid some of the alignment problems. Merging rows is actually quite simple, but merging columns can be tricky if the rows of one table don't correspond with the rows in the other table. In this case also, the table will increase to accommodate the new columns. If the rows in both tables match up, you can merge the columns of one table with another-by pasting them in the first empty cells to the right of the table. The table will increase in size to include the new rows. Each record will occupy a row of the table, and each field will occupy a cell in the row.You can merge (combine) rows from one table into another simply by pasting the data in the first empty cells below the target table. As you create these data files, you can place the information in a Word table so the data is easier to visualize and work with. Notice, also, that the third data record does not list any data corresponding to the data field named middle. The other four records are the data records. The first record indicates the field names it is the header record. For instance, consider the following excerpt from a data file. Each field in each data record of the file must correspond to one of the header fields. The names in this header record correspond to the field names used in your master document. The header record indicates the field names to be assigned to the fields in each record. If you are creating a simple, one-time mail merge document, chances are good that you will be using data that you put in a Word document.īasically, the Word data file must have a header record and as many data records as you desire. This data can be in a database program, in the Excel spreadsheet program, or directly in Word.

Data merge word and excel how to#

If you plan on printing documents that merge information from a data file, such as form letters, you will need to create the data file in such a way that Word knows how to process it.