Printable View
I need som urgent help from you all guys. I have data in MS Word that is in table format and i need to show it in Excel in the same layout. But when i am doing it normally by Ctrl + C, Copy a Word table into Excel, dates are not displaying properly, etc.. In short, the data is getting worst.
Could you guys please let me know how can i copy a Word table into Excel in the same layout ? Help appreciated
Copying Word table in Excel is easy but you need to clean it up so that you can take advantage of the calculation features in Excel after pasting the data. Anyways, to copy the table from Word to Excel in the same layout, just go through the mentioned steps below:-
- In Microsoft Word, select the rows and columns of the table you want to copy.
- If you want to select the entire table, click the table, point to Select on the Table menu, and then click Table.
- Click Copy on the Standard toolbar.
- Switch to your Microsoft Excel worksheet.
- Click in the upper-left corner of the worksheet area where you want to paste the table. Cells from the copied table replace any existing cells in the area.
- Click Paste .
- To adjust the formatting, click Paste Options next to the data, and then click Match Destination Formatting to use any formatting that was previously applied to the Excel cells, or Keep Source Formatting to match the formatting of the Word table as closely as possible.
Cleaning data of the data or table that is taken from Word to Excel is much important to display it properly and with the entire calculation. The basic steps for cleaning data are as follows:-
- Import the data from an external data source.
- Create a backup copy of the original data in a separate workbook.
- Ensure that the data is in a tabular format of rows and columns with: similar data in each column, all columns and rows visible, and no blank rows within the range. For best results, use an Excel table.
- Do tasks that don't require column manipulation first, such as spell-checking or using the Find and Replace dialog box.
- Next, do tasks that do require column manipulation. The general steps for manipulating a column are:
- Insert a new column (B) next to the original column (A) that needs cleaning.
- Add a formula that will transform the data at the top of the new column (B).
- Fill down the formula in the new column (B). In an Excel table, a calculated column is automatically created with values filled down.
- Select the new column (B), copy it, and then paste as values into the new column (B).
- Remove the original column (A), which converts the new column from B to A.
Excel pastes the contents of each cell in the Word table to a separate Excel cell. If you want to split the data in the cells further, for example to divide first and last names so they're in separate cells, use the Text to Columns command on the Data menu.
To periodically clean the same data source, consider recording a macro or writing code to automate the entire process. There are also a number of external add-ins written by third-party vendors, listed in the Third-party providers section, that you can consider using if you don't have the time or resources to automate the process on your own.