How to Copy a Table in Excel Without Losing Formatting

How-to basics · Updated November 20, 2026

To copy a table in Excel for Microsoft 365 on Windows, select the whole table, press Ctrl+C, click an empty destination cell, and press Ctrl+V. Excel copies the headers, data, and usually the table formatting; use Paste Special if you want only values or a different part of the table. Have the workbook open and choose a destination with enough clear space before you begin.

What You’ll Need

  • An Excel workbook containing the table you want to copy. An Excel table has header filters and a consistent table style; a plain range of cells can also be copied, but it will not necessarily behave as a table after pasting.
  • A destination in the same workbook or another open workbook. If you are copying into a new workbook, create or open it first.
  • Enough empty cells at the destination for the copied table. Avoid placing the copy on top of data or inside another table unless you specifically intend to replace or extend that content.

Step-by-Step

  1. Open the source workbook and locate the table. Click any cell inside the table and check that the contextual Table Design tab appears on the ribbon. This helps distinguish an actual Excel table from a formatted range. If there is no Table Design tab, you can still copy the cells, but some table-specific behavior may not be included.

  2. Select the complete table. With a cell in the table selected, choose Table Design > Select > Entire Table. This is the most reliable ribbon method for including the table as a whole. Depending on the table, the selection can include its header row, data, and enabled Total Row. Look at the highlighted cells before copying. If you want to omit the Total Row or copy only certain columns, select just the specific range you need instead.

    You can also select the table range with the mouse by dragging from its top-left header cell to its bottom-right cell. In many desktop versions, pressing Ctrl+A while the active cell is inside a table selects the table data, and pressing it again includes the headers and totals. Selection behavior can depend on which cell is active, so confirm the highlighted range rather than relying on the shortcut alone.

  3. Copy the selection. Press Ctrl+C, or choose Home > Copy. A moving dashed outline around the selected cells indicates that Excel has copied them. Do not press Ctrl+X unless you want to move the table and remove it from its original location.

    On a Mac, use Command+C. In Excel for the web, select the cells and use Ctrl+C on Windows or Command+C on a Mac; browser security or clipboard permissions can sometimes affect copying.

  4. Choose the destination. Click the top-left cell where the copied table should begin. For a copy on the same worksheet, pick a clear area far enough away from the source. For another worksheet, click its sheet tab and then the destination cell. For a different workbook, switch to that workbook and select an empty cell there.

    If the copy is going to a new worksheet, use the plus button beside the sheet tabs to create one, then select the starting cell. When pasting into another workbook, keep both workbooks open and make sure the destination workbook is not protected against editing.

  5. Paste the table. Press Ctrl+V on Windows, or Command+V on a Mac. You can also choose Home > Paste. Excel fills the destination starting at the cell you selected. If you copied the whole table, check that the pasted result includes the headers and all rows you expected.

  6. Check whether the result is still an Excel table. Click a cell in the pasted result. If the Table Design tab appears, Excel recognizes the result as a table. A copied table needs a distinct name from the original, so Excel may assign a different table name automatically. If the tab does not appear but you need table features such as filter buttons and structured references, select the pasted range and choose Insert > Table. In the dialog, confirm the range and check My table has headers if the first row contains column names.

  7. Review formulas and references. Excel normally adjusts relative cell references when formulas are copied to a new location. For example, a formula that refers to the cell immediately to its left may refer to the new row’s cell immediately to its left after pasting. Absolute references marked with dollar signs stay fixed. If the table uses formulas, linked data, or structured references, inspect a few copied cells and confirm that they calculate from the intended data.

  8. Save the workbook. Save after checking the copied table. If you copied between workbooks, save the destination workbook too. When moving the file to another format, such as CSV, remember that the format may not retain Excel table styling, multiple sheets, or formulas as Excel features.

Common Mistakes

Copying only the data rows. Selecting the body of a table without its header row can leave the pasted copy without column names. That can make it harder to identify fields and may prevent Excel from treating the result as a table with headers. Use Table Design > Select > Entire Table, or verify that the header row is included in the highlighted selection.

Pasting over existing information. Pasting a multirow, multicolumn selection can overwrite cells in the destination. Excel may warn you about replacing content, but do not count on a warning in every situation. Choose a genuinely blank destination and inspect the selection size before pasting.

Confusing copy with move. Ctrl+X cuts the selected cells. The source can disappear when you complete the paste. To keep the original and create a second copy, use Ctrl+C.

Expecting a plain range to retain table features. A range can look like a table because it has colored rows, borders, or filter buttons, but visual formatting alone does not guarantee that it is an Excel table. Check for the Table Design tab after pasting and create a table with Insert > Table if needed.

Ignoring formulas and external links. A copied formula may update its references, while a link to another workbook may continue pointing to the original file. Review formulas, especially if the new table is going to a different workbook or folder.

Tips

Choose the paste result you need. After copying, use Home > Paste to view paste options. To paste results without formulas, choose Paste Values (the clipboard option marked with values, often shown as 123). To copy just the appearance, choose Paste Formatting. The exact icons can vary slightly by Excel build. These options are useful when you want a static report or want the destination to match a different style.

Use Paste Special for more control. On Windows, copy the selection, right-click the destination, and choose a suitable option under Paste Options, or open Home > Paste > Paste Special. In the dialog, select Values, Formats, or another available component, then click OK. On Mac, use the Paste Special command on the Home tab or the context menu; available options may differ by version. In Excel for the web, use the paste options that appear after pasting where available.

Preserve the source table when making a version for editing. Put the copy on a separate worksheet or in another workbook, and give the new table a clear name from the Table Design tab. Names must be unique within a workbook. A descriptive name makes formulas and later maintenance easier than leaving an automatically assigned name.

Copy only what the recipient needs. If someone needs a read-only snapshot, paste values so the result does not recalculate based on changing source cells. If they need to keep working with formulas, preserve the formulas and check their references. If they need the table’s filters and structured references, make sure the pasted cells remain or are converted into an Excel table.

Use the fill handle only for a small amount of adjacent data. Dragging the fill handle can duplicate cells, but it is easy to extend a formula or series unintentionally. For a complete table or a copy to another sheet, selecting the range and using copy and paste is clearer and easier to verify.

Troubleshooting

The paste does not appear. Confirm that the moving dashed outline is still around the copied cells, then select the destination and paste again. If you copied cells from another application or browser, clipboard permissions may be blocking the action. Try copying within Excel, use the ribbon’s Copy and Paste commands, or refresh Excel for the web and repeat the selection.

Excel says the destination is not valid or refuses to paste. Check whether the worksheet is protected, whether the destination overlaps a merged cell, or whether the copied range would overlap another table. Choose an unmerged, empty area on an editable sheet. If the sheet is protected, ask the workbook owner for permission or use an allowed destination.

Only part of the table was copied. Return to the source and inspect the selection outline. Use Table Design > Select > Entire Table again and make sure the header and the last row are included. A hidden or filtered row can make the result less obvious; check the pasted row count against the source before relying on the copy.

The formatting or filters are missing. If you used Paste Values or another limited paste option, Excel intentionally left out some formatting or table features. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac, then paste normally. If the result has formatting but no table controls, select it and choose Insert > Table, then confirm whether the first row contains headers.

The copied formulas show unexpected results. Select a formula cell and examine its references in the formula bar. Relative references change when pasted; absolute references stay fixed, and formulas that refer to another workbook may retain an external link. Correct the references if the copy should use different cells. If you pasted values, the formulas were replaced by their displayed results, so undo and paste normally if formulas are required.

The copy has the wrong table name or style. A table name must be unique, so Excel may rename a duplicate. Click inside the copied table, open Table Design, and edit the Table Name field if you want a clearer name. To change the look, choose a different option in the Table Styles gallery. This changes the table style, not the underlying values.

Frequently Asked Questions

Can I copy an Excel table to another worksheet?
Yes. Select the entire table, copy it, click the destination sheet, and paste into an empty cell. Check the pasted range for headers, formulas, and table controls.

How do I copy a table without formulas?
Copy the table, then choose Home > Paste > Paste Values at the destination. This keeps the displayed results rather than the formulas. Review the result because values will not update when the source data changes.

Will copying a table copy its formatting?
A normal copy and paste usually brings the table’s formatting with it. If you want only the look, use Paste Formatting. If formatting is missing, undo and repeat with the normal paste option.

Can I copy only one column or part of a table?
Yes. Select the specific cells or column you need, copy, and paste. Include the column header if you want its label. A partial selection may paste as ordinary cells rather than a separate Excel table, so check for the Table Design tab.

What is the shortcut to copy and paste on a Mac?
Use Command+C to copy and Command+V to paste. On Windows, the equivalents are Ctrl+C and Ctrl+V.

Can I copy a table in Excel for the web?
Yes. Select the table cells, copy, select an empty destination, and paste. Some desktop features and Paste Special options differ in the web version, so check the result and use Excel for Microsoft 365 on desktop if you need options that are unavailable in your browser.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.