How to copy cell data with row height and column width in Excel?
In daily work with Excel, you may often find yourself needing to not only copy cell data, but also ensure that the pasted information perfectly matches the original layout—including row heights and column widths. However, by default, when you simply copy and paste content in Excel, only the cell values and formulas are transferred, and the precise formatting, such as row height and column width, is not fully retained.
Copying both the data and the exact cell dimensions can be very useful, especially when preparing reports, consolidating tables from different worksheets, or ensuring consistent formatting across workbooks. This saves significant time and prevents the manual task of repeatedly adjusting column widths and row heights to match the source formatting. In this article, we'll explore different methods to copy cell data together with row and column sizing in Excel and discuss when each approach may be most suitable.
Copy cell data with row height and column width with the Excel function
Easily copy cell data with row height and column width with Kutools for Excel
Copy cell data with row height and column width with the Excel function
When you want to transfer both data and cell sizing in Excel, it's helpful to know the capabilities and limitations of built-in functionality. While Excel’s copy and paste options are convenient for most basic tasks, they do not allow you to copy cell data along with both row heights and column widths in one single step. Instead, you’ll need to carry out a couple of separate actions to fully preserve the appearance of your original cells.
This method is suitable for simple tables and when you need control over which formatting features you want to keep. It supports copying either column widths or row heights, but not both in a single operation. You will need to use a combination of built-in paste options and format tools as described below:
1. Begin by selecting the cells you wish to copy, ensuring that you include all the cell contents and formatting you need. With the cells selected, press Ctrl + C to copy them.
2. Click in a blank cell or area where you want to paste the copied data. Make sure there is enough space to accommodate the full range.
3. Navigate to Home > Paste > Keep Source Column Widths. This enables the pasted content to match the original column widths, resulting in a seamless appearance between the original and the copied cells. See screenshot:
At this stage, your chosen values and formulas, along with the column widths, are pasted as intended.
Note: If your aim is to copy only the column width but not the data, click Home > Paste > Paste Special to bring up the Paste Special dialog. Here, check Column widths and then click OK. This will apply just the column size settings to your target location, leaving the existing values and formulas intact.
4. For situations where the row height also needs to be consistent, select the rows (not just the cells) from the original range in order to copy their row height settings. On the Home tab, click Format Painter. This tool lets you copy the formatting—including row height—of your selection.
5. When the Format Painter brush icon appears, drag across the corresponding target rows you want to format. Upon releasing the mouse button, the original row heights are replicated to the new location. Make sure that you select the entire row for this step to fully carry over height settings.
Practical tips:
- If your copied area is large or complex, double-check that your target range is empty to avoid overwriting important data.
- Format Painter can be applied multiple times by double-clicking the Format Painter icon—the brush will remain active until you press Esc.
- If row heights or column widths do not match the original after pasting, confirm that you selected the entire rows or columns rather than just cell blocks.
Applicable scenarios: Useful for quick copying between worksheets or small data ranges. However, it is not as efficient for copying large numbers of non-adjacent ranges or handling frequent tasks requiring this formatting preservation.
Limitations: Needs to be done in at least two separate steps to fully reproduce spacing and layout. Does not natively support copying both row heights and column widths in a single command.
Troubleshooting suggestions:
- If some formatting is missing, check if your copied content is being pasted into merged or already formatted cells, which can sometimes override paste options.
- Remember that copying just using Ctrl + V will only paste values and basic formatting; always use the Paste Special menu to choose advanced options.
Copy cell data with row height and column width with Kutools for Excel
For those who frequently need to duplicate data ranges together with their complete formatting—including both row height and column width—Kutools for Excel provides an extremely handy solution with its Copy Ranges utility. By using Kutools, you can accomplish in one simple operation what would normally require multiple manual steps in Excel's native functions.
1. Select the cells or ranges you want to copy, making sure to include all cells whose data, row height, and column width you wish to retain. Then click Kutools > Copy Ranges.
Tips: You can select multiple non-contiguous ranges by holding the Ctrl key as you make your selections. This is especially helpful when preparing reports from complex source data or combining tables from different worksheets.
2. In the Copy Multiple Ranges dialog box, customize how you want to copy the content:
3. After confirming your selection, a prompt will appear to select the range where you want your data—with the row height and column width settings—inserted. Specify a blank cell or appropriate area, and click OK.
Upon completion, you'll find both the data and the original formatting are successfully and accurately pasted to the destination cells, matching the layout of the source.
- Advantages: Supports multiple, even non-contiguous, ranges; speeds up workflow for repetitive or large-format copying tasks; eliminates manual resizing entirely.
- Precautions: Before using, make sure Kutools is properly installed and activated in your version of Excel. If copying to an area with pre-existing formatting, some conflicts may occur, so consider pasting into a blank area.
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
Demo: Easily copy cells data with row height and column width with Kutools for Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in