'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Copy a PDF or Web Table into Excel Without Losing Formatting
Question details
The user wants to copy data tables from PDF files or websites into Excel while retaining the original spacing and formatting.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing or copying table data from an external source (PDF or Web) into a spreadsheet.
- Observed behavior
- Pasting the table directly into Excel or Notepad causes the data to lose its original layout, spacing, and formatting, particularly when the table contains empty cells.
Ensure you have the source PDF document saved locally on your computer, or copy the URL if extracting from a web page, and verify that the table data is clearly structured.
Import PDF or CSV Data Using Excel's Get Data Feature
Using Excel's built-in Get Data feature ensures that data structures, including empty cells and columns, are preserved correctly during the import process.
Directly pasting tables often ignores column breaks. By using the Power Query (Get Data) tool, Excel processes the background structure of the source file to maintain the correct layout.
If dealing with a web table, save the extracted data as a text or CSV file first. If dealing with a PDF, ensure it is saved securely on your device.
Open Excel, navigate to the 'Data' tab on the top ribbon menu, and click on 'Get Data' (or 'New Query' in older versions).
Choose 'From File' and then select 'From Text/CSV' or 'From PDF' (if available in your Office version). Browse your computer to locate and select the file.
A preview window will appear. If the layout looks correct, click 'Load'. If you need to fix empty cells or shift columns, click 'Transform Data' to adjust the layout in Power Query before loading it into your spreadsheet.
Use a Dedicated PDF-to-Excel Conversion Tool
A reliable PDF-to-Excel conversion tool automatically extracts tables and preserves their formatting, bypassing manual text adjustments.
Effortlessly Convert PDF Tables to Excel with WPS Office
WPS Office features a built-in PDF toolkit that allows you to seamlessly convert PDF files directly into editable Excel spreadsheets. It accurately recognizes table structures, preserving the original formatting and cell layouts without requiring complex data import queries.
- 1. Open the PDF File: Launch WPS Office and open the PDF document that contains the table you wish to extract.
- 2. Select PDF to Excel: Navigate to the 'Tools' tab on the top ribbon and click on the 'PDF to Excel' option.
- 3. Configure the Conversion: In the pop-up window, select the specific pages containing the tables you need, and choose your desired output location.
- 4. Convert and Edit: Click 'Convert'. The file will automatically open as a perfectly formatted spreadsheet in WPS Spreadsheet, ready for editing.
Frequently Asked Questions
Why does my PDF table paste into a single column in Excel?
When you paste directly, Excel often reads PDF text as a continuous string without column delimiters. Using the 'Get Data' feature or a dedicated PDF-to-Excel converter forces the program to recognize proper table structures.
Can I extract only a specific table from a multi-page PDF?
Yes. When using the 'Get Data' feature in Excel, the Navigator window lets you select the specific table or page you want to import. Similarly, most PDF converters allow you to specify an exact page range.
How do I import a table directly from a website into Excel?
Go to the 'Data' tab, select 'Get Data', choose 'From Other Sources', and click 'From Web'. Paste the URL of the webpage, and Excel will scan the site to detect and extract HTML tables.
What if the PDF contains scanned images instead of text?
If the PDF is an image or scanned document, standard copy-pasting or basic importing will not work. You will need to use a PDF tool equipped with OCR (Optical Character Recognition) to identify and extract the visual text into an editable Excel table.
