How to Copy Column Widths and Row Heights Between Excel Sheets
Question details
The user needs to apply the same column widths, row heights, and layout settings across multiple sheets to avoid reformatting imported PDF data manually every day.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Reformatting daily PDF data imports across different worksheets for consistency.
- Observed behavior
- The user wants a way to seamlessly transfer existing formatting and dimensions from a preformatted sheet to new data sheets.
Ensure your imported PDF data is organized in a tabular format before pasting it into your spreadsheet. Having a clean, unmerged data set will prevent alignment issues when applying template column widths and row heights.
Use a Preformatted Template Sheet
Creating a dedicated template sheet allows you to retain exact column widths and row heights. You can duplicate this sheet daily and paste new data as values.
Adjust all column widths, row heights, and cell formatting exactly as desired on a blank worksheet. Name this sheet 'Template'.
Right-click the 'Template' sheet tab, select 'Move or Copy', check the 'Create a copy' box, and click 'OK'.
Copy your newly imported PDF data. Go to the duplicated sheet, right-click cell A1, and choose 'Paste as Values' to keep the template's column widths and row heights intact.
Go to File > Save As, and choose 'Excel Template (*.xltx)' if you need to use this setup across entirely new workbooks.
Import Structured Data via Power Query
Instead of manual copying and pasting, use Power Query to directly import and format PDF data into your spreadsheet.
Use Paste Special for Column Widths
If you only need to copy column widths from one existing sheet to another without duplicating the whole sheet, use the Paste Special feature.
Handle PDF Imports and Sheet Formatting Easily with WPS Office
WPS Spreadsheet offers powerful tools to manage daily data imports. Paste formatting, utilize templates, and convert PDFs to spreadsheets seamlessly within a single application.
- 1. Open WPS Spreadsheet: Launch WPS Office and create or open your spreadsheet workbook.
- 2. Configure your template: Set your desired row heights and column widths, then right-click the sheet tab to select 'Move or Copy Sheet' for daily use.
- 3. Convert PDF to Excel (Alternative): Use the built-in 'PDF to Excel' tool in the WPS Office toolkit to automatically import PDF data into neatly formatted rows and columns.
Frequently Asked Questions
Does Excel have a Paste Special option for row heights?
No, Excel's Paste Special menu includes an option for 'Column widths' but not for 'Row heights'. To keep row heights consistent, duplicating a pre-formatted template sheet is the most reliable method without using VBA macros.
How can I format column widths and row heights on multiple sheets at the same time?
You can group sheets to apply formatting universally. Hold down the Ctrl key and click multiple sheet tabs at the bottom. Once grouped, any column width or row height adjustment made on the active sheet will automatically apply to all grouped sheets.
Why does copying from a PDF mess up my Excel columns?
Standard copy-pasting from a PDF often brings in hidden line breaks or merges text into single columns because PDFs are visually-structured document formats, not tabular databases. Using 'Get Data from PDF' or a dedicated PDF to Excel converter ensures the data remains in its proper columns.
Can I use Format Painter for column widths and row heights?
No, the Format Painter tool only copies cell formatting (like background colors, borders, and fonts). It does not copy or apply column widths or row heights to new ranges.
