How to copy a letterhead from Word to Excel

Avoid the hassle of manually importing Word form data into Excel. With the help of an Excel wizard, you can quickly step through the process.

https://d1rytvr7gmk1sx.cloudfront.net/wp-content/uploads/2018/07/20180724_WordtoExcel_Brandon.mp4

Editor’s note: In the video, Brandon Vigliarolo walks you through the steps of transferring data from Word forms to an Excel worksheet in Microsoft Office 365.

Word forms provide a convenient way to gather information from users or clients so that you can analyze it later. But bringing the data into Excel can be a big chore. To avoid having to manually enter the data into a worksheet, let Excel’s Text Import wizard bring it in for you. To demonstrate, let’s say you’ve asked employees to complete the form shown in Figure A.

LEARN MORE: Office 365 Consumer pricing and features

Figure A

Follow these steps to save the data in each completed form to a text file that can be imported into Excel:

1. Open one of the completed forms.
2. Go to Tools | Options, click the Save tab, and select the Save Data Only For Forms check box. (In Word 2007, click the Office button, click Advanced, scroll to Preserve Fidelity When Sharing This Document, and select the Save Data As Delimited Text File check box.)
3. Click OK.
4. Save the file as a .txt file.
5. When the File Conversion dialog box appears (Figure B), click OK.

Figure B

You can now import the data in the text files into a spreadsheet by following these steps:

1. Open a blank worksheet in Excel.
2. Go to Data | Import External Data | Import Data. (In Excel 2007, click the Data tab, click Get External Data, and then select From Text.)
3. Click the text file you want to import, then click Import.
4. Select the Delimited option (Figure C) and then click Next.

Figure C

1. For this example, clear the Tab check box and then select the Comma check box (Figure D).

Figure D

1. Click Next and then click Finish.
2. Click in cell A1 and then click OK.

Figure E shows the resulting data. Note that headers have been inserted above the data in row one.

Figure E

To import the second text file, you just open the same Excel worksheet and click in the second row below the last row of data; in this case, A4. (The wizard forces you to skip a row each time you add a new row of data. You can delete these blank rows later.)

SEE: Comparison chart: Office suites (Tech Pro Research)

Important note: If you need to enter more than a handful of forms a day, you should consider using ADO (ActiveX Data Object) to create a code connection to the worksheet that will automate the data transfer process.

Also See

• How to add a drop-down list to an Excel cell (TechRepublic)
• How to become a cloud engineer: A cheat sheet (TechRepublic)
• 50 time-saving tips to speed your work in Microsoft Office (free PDF) (TechRepublic download)
• Cost comparison calculator: G Suite vs. Office 365 (Tech Pro Research)
• Microsoft Office has changed, how you use it should too (ZDNet)
• Best cloud services for small businesses (CNET)
• Best to-do list apps for managing tasks on any platform (Download.com)
• More must-read Microsoft-related coverage (TechRepublic on Flipboard)

When you want to move data from a Word table to Excel, you can avoid having to retype that data by copying it from Word directly. When you copy data from a Word table into an Excel worksheet, the data in each Word table cell is pasted in an individual cell on the worksheet.

Important: After pasting the data, you may have to clean it up so that you can take advantage of the calculation features in Excel. For example, there may be unwanted extra spacing in cells, numbers may have been pasted as text rather than as numeric values that you can calculate, or dates are not displayed correctly. For help with formatting numbers as dates, currency, percentages, etc., see Format numbers. For help with formatting the style of your table, see Format an Excel table.

1. In a Word document, select the rows and columns of the table that you want to copy to an Excel worksheet. Make sure there aren't any extra carriage returns in the cells of the table, otherwise this may cause extra rows in Excel. 

2. To copy the selection, press CTRL+C.

3. In the Excel worksheet, select the upper-left corner of the worksheet area where you want to paste the Word table.

   Note: Make sure that the paste area is empty before you paste the data. Data in Word table cells will replace any existing data in worksheet cells in the paste area. If necessary, review the table first in Word to verify its dimensions.

4. Press CRL+V.

5. To adjust the formatting, click Paste Options

   
   next to the data that you pasted, and then do the following:

   • To use the formatting that is applied to the worksheet cells, click Match Destination Formatting.

   • To use the formatting of the Word table, click Keep Source Formatting.

Note: Excel pastes the contents of each Word table cell into a single cell. After you paste the data, you can distribute the data across additional cells in a column (for example, to divide first and last names so that they appear in separate cells) by using the Text to Columns command. For more information, see Distribute the contents of a cell into adjacent columns.