Frequently, you might need to convert the Word table to Excel for various purposes. In this article, I’ll show you 6 methods, which include tricks for a simple table as well as a complex table, to convert the Word table to Excel spreadsheet.
Download Practice Workbook
6 Methods to Convert Word Table to Excel Spreadsheet
Assuming that you have a table like the following one in your Word document. Here, the Sales Report of Fruit Items is given along with the necessary information i.e. Product ID, Fruit Items, Unit Price, and Sales in USD.
Now, you need to convert the above table into an Excel spreadsheet program using the following methods. The first 5 methods are suitable for converting a simple table. And the rest method is handy for converting a complex table.
1. Use Copy and Paste Tool
In the beginning method, I’ll show you the simple method using the copy and paste tool to convert the Word table to Excel. Please follow the below steps.
- Click over the upper-left arrow of the table for selecting the entire table.
- Then, right-click and choose the Copy option from the Context Menu.
- Next, go to the Excel spreadsheet and select any cell within the workbook e.g .B2 cell. Lastly, choose the Paste option from the Clipboard ribbon(in the Home tab).
Finally, you’ll get the following output.
After necessary formatting and adjusting the column width, the output will look as follows.
2. Using Keyboard Shortcut
If you’re accustomed to using the keyboard shortcut, you can follow this method.
- Just, click over the upper-left arrow and press CTRL + C to copy the entire table.
- Then, go to the Excel spreadsheet and press CTRL + V to paste the copied table.
Eventually, you’ll get the following output.
3. Drag and Drop Word Table to Excel
Instead of pressing any key or tools, you may copy the word table to Excel quickly! All you need to do is drag the table and drop it to the desired place. Follow the procedures to understand the process.
- Firstly, bring the word and Excel side by side.
- Secondly, drag the word table and drop the table into any specific cell within the spreadsheet.
So, you’ll get the following output.
After applying the formatting, the output will look as follows.
4. Convert Word Table to Excel with Formatting
Sometimes, you may have predefined formatting in your Excel spreadsheet. And, you need to keep the formatting after copying the Word table.
- Initially, copy the word table (pressing CTRL + C).
- Later, choose the Match Destination Formatting paste option.
So, the output will be as follows where the formatting has also prevailed.
5. Apply Convert to Text and Text to Columns Features
Apart from these methods, you may convert the table to text in Word and then copy the texts into Excel.
- Primarily, select the table and click on the drop-down list of the Data option in the Layout tab. Then, select the Convert to Text option.
- Later, you’ll see a dialog box namely Convert Table To Text where you have to choose any delimiter (e.g. Commas). And, press OK.
- Then, you’ll get the following output and you need to save this output as a .txt file. For doing this, go to File > Save As.
- Now, specify the format as Plain Text and click over the Save button.
If you open the text file using the Notepad, you’ll see the following output.
- So, select the texts and copy them by pressing CTRL + C.
- Then, go to the Data tab > pick the Text to Columns option from the Data Tools tab.
After Converting text to Excel with columns, you’ll get the following output.
6. Convert Word Table to Excel without Splitting Cells
If you have line breaks in your word table, you cannot convert such a type of table to an Excel spreadsheet using the above discussed methods. For example, relevant information (i.e. Full Name, State, and Email) of Sales Rep is given as shown in the below table.
Now, if you use the copy and paste tool, you’ll get the following output where the cells are split.
Let’s explore why the cells are splitting. If you turn on the Show/Hide ¶ (Pilcrow character) from the Home tab in the Word document, you’ll see the Pilcrow character for every line break.
However, you need to convert the table in Excel without splitting. Do the following steps.
- While working in Word document, press CTRL + H first to open the Find and Replace dialog box. Alternatively, you can open the dialog box from the Home tab > Replace option (from the Editing ribbon).
- Later, insert the Paragraph mark (^p) in the box after the Find what option and -line break- after the Replace with option.
- Lastly, press the Replace All button.
Immediately, you’ll see the following message.
And, the output will be as follows.
- Now, copy the entire table and paste it into any cell within the Excel spreadsheet.
- Again, open the Find and Replace dialog box tool in Excel (simply you can press CTRL + H).
- Then, insert -line break- after the Find what option and press CTRL + J to insert a line break in the space after the Replace with option.
- At last, pick the Replace All button.
- Furthermore, select the B5:B9 cells and choose the AutoFit Row Height from the Format option.
Eventually, you’ll get the following output.
Things to Remember
- While pasting the Word table in Excel, make sure that the cells are empty. Because the copied table will replace any existing data.
- While using the Text Import Wizard, remove the unnecessary space inside the text file.
That’s the end of today’s session. I firmly believe that you can easily convert a Word table to an Excel spreadsheet using the above methods. Anyway, don’t forget to share your thoughts in the comment section below.