How to Copy an Excel Table With Formulas

Formulas & functions · Updated November 20, 2026

To copy an Excel table and keep its formulas, select the table, press Ctrl+C, click a blank destination, and press Ctrl+V. Excel copies the formulas along with the table’s data; relative cell references may adjust to the new location, while absolute references stay fixed. Before you start, decide whether you want a second working table with formulas or just a copy of the displayed results.

What You’ll Need

  • A workbook open in Microsoft Excel for Microsoft 365 on Windows, with the source table and its formulas.
  • A blank destination large enough for the copied table. Leave room for its rows and columns, and make sure it will not overlap another table.
  • If formulas refer to other cells or tables, know whether those references should shift or continue pointing to the original locations.

Step-by-Step

  1. Check that the source is an Excel table. Click a cell in the data. If the Table Design tab appears on the ribbon, the data is an Excel table. If it does not, you may be working with a regular cell range; the copy process still works, but table-specific features such as calculated columns will not apply. Check that the formulas currently show the results you expect before copying.
  2. Choose what to copy. To copy the entire table, click inside it, then select Table Design > Select > Entire Table. This includes the headers and data rows. To copy only certain columns or rows, select just those cells instead; include the header row if you want the copied range to have headings. Avoid selecting entire worksheet columns unless you really intend to copy all of them.
  3. Copy the selection. Press Ctrl+C on Windows. A moving dashed border indicates that Excel has copied the cells. On a Mac, use Command+C. Keep the selection intact until you paste; clicking elsewhere is fine, but pressing another copy command replaces what is on the clipboard.
  4. Choose a clear destination. Click the top-left cell where the copied table should begin. Use an empty area on the same sheet or open another worksheet and choose its top-left destination cell. The new range must not overlap the original table or another table. If you are pasting into a different workbook, open that workbook first and confirm it is editable.
  5. Paste the table. Press Ctrl+V on Windows, or Command+V on a Mac. You can also use Home > Paste. A full table copy normally brings over the headers, contents, formulas, and table formatting. If Excel creates a separate table, clicking inside the pasted area should display the Table Design tab. Give the new table a distinct name under Table Design > Table Name if you plan to use structured references or refer to it elsewhere.
  6. Check how formula references changed. Click a formula cell in the copied table and inspect the formula bar. Ordinary relative references usually shift by the same number of rows or columns as the paste. Absolute references marked with dollar signs stay fixed. Structured references that refer to the current row, such as a formula using the copied table’s Quantity and Unit Price columns, generally continue to refer to the corresponding columns in that table. References to a different table usually remain directed at that other table.
  7. Confirm that the copied formulas calculate correctly. Compare several copied results with their inputs, including a row near the top and one near the bottom. For example, with table columns named Quantity and Unit Price, and a row containing 4 and 12.5, the formula =[@Quantity]*[@Unit Price] returns 50. If that row is copied into a new table with the same column headings, the current-row references can calculate from the new row’s values. Check that the copied table has not inherited an unintended reference to the original data.
  8. Use a formulas-only paste if you do not want the source formatting. Copy the source cells, click the destination, then choose Home > Paste > Formulas from the Paste menu. This brings over formulas rather than the complete look of the source. Relative references can still adjust during this paste, so inspect them afterwards. If the destination needs a particular appearance, apply its formatting separately.
  9. Save and verify the result. Save the workbook after checking formulas, headers, and table formatting. If the copy is important, save a separate version before making further changes. This makes it easier to recover the original if a pasted formula or table name is not what you intended.

To duplicate an entire worksheet instead of just its table, right-click the worksheet tab, choose Move or Copy, select Create a copy, and choose where to place it. This is useful when the sheet contains notes, charts, or other linked content that should travel with the table. It copies the worksheet, not just the table, so review formulas that refer to other sheets before relying on the duplicate.

Common Mistakes

Copying the results instead of the formulas. A standard paste normally copies formulas, but choosing a Values paste option replaces them with fixed results. That can be useful for a snapshot, but the copied numbers will not recalculate when their inputs change. Click a copied cell and look at the formula bar: a formula begins with an equals sign; a displayed number alone is a value.

Assuming every reference behaves the same way. A formula that points to a nearby cell can shift when pasted, while a reference such as =$B$2*C5 keeps B2 fixed but allows C5 to adjust. With names in A2:A20 and sales in B2:B20, that example is not a complete calculation layout by itself; it simply illustrates the difference between a fixed reference and a relative one. Review the actual formula in your workbook rather than relying on how it looks in the source.

Pasting on top of existing data. A paste can overwrite cells without an obvious warning, and Excel tables cannot occupy overlapping ranges. Choose an empty destination and check its surrounding cells first. If Excel reports that the destination is not valid, move the paste to a clear area or create a new worksheet.

Copying only part of a table unintentionally. Selecting the data body but not the headers may leave the duplicate without clear column names. Selecting too much can bring unwanted blank rows or columns. Use Table Design > Select > Entire Table for a full duplicate, then inspect the selected range before copying.

Expecting a formulas-only paste to preserve the whole table. Pasting formulas into a destination does not necessarily recreate the original table object, column widths, styles, or all table features. If you need a functional second table, copy the entire table first, then adjust formatting. If you need only calculations inside an existing table, a formulas-only paste may be more appropriate.

Tips

  • Choose the right paste for the job. Use a normal paste when you want a working duplicate with its formulas and formatting. Use Home > Paste > Formulas when you want formulas without the original cell formatting. Use the Values option only when you deliberately want fixed results.
  • Use table formulas for row-by-row calculations. If a calculated column uses structured references such as Quantity and Unit Price, Excel can fill the formula down the column as new rows are added. Matching column headings in the copied table make these formulas easier to understand and maintain.
  • Make reference changes deliberate. If all copied formulas should point to one fixed input cell, use an absolute reference in the original formula before copying. With a tax rate in B1 and a price in C2, the formula =C2*$B$1 multiplies the price by the fixed rate cell. For a price of 100 and a rate of 0.08, it returns 8. When copied down one row, C2 can shift to C3 while $B$1 remains fixed. Confirm the cell layout and intended calculation before adapting this example.
  • Keep table names unique. Excel tables in the same workbook need distinct names. If you create a duplicate and want to use its name in formulas, click within it and edit Table Design > Table Name. Choose a clear name without spaces, such as SalesCopy, and update any formulas that should refer to the new table.
  • Use the fill handle for a short formula continuation. If you only need to extend a formula down an existing column, select the formula cell and drag the small square at its lower-right corner over the destination cells. In a table’s calculated column, entering a formula in one row may fill the column automatically. Check the first and last resulting rows to ensure the references are appropriate.
  • Work on a copy when the source is valuable. If you are unsure whether references or formatting will change, duplicate the worksheet or save a separate workbook version first. You can then compare the copied formulas with the source without risking the original data.

Troubleshooting

The copied cells show numbers but no formulas. Check whether you used Paste Values, or whether the source cells were already values rather than formulas. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac if the paste just happened, then copy again and use a normal paste. If the source itself has no formulas, copying cannot recreate them.

The formula returns the wrong result after copying. Select the cell and inspect the formula bar. Look for a relative reference that shifted to an unintended row or column, or an absolute reference that is locking a cell you meant to move. Correct the reference in the copied formula, then fill or paste the corrected formula into the other rows as needed. Test it against a row where you can calculate the expected answer independently.

The pasted formulas do not update. Confirm that the copied cells contain formulas and that their input cells have changed. If calculations appear stale, choose Formulas > Calculation Options > Automatic. You can also press F9 to recalculate the workbook. If the formula is correct but refers to the old table or worksheet, change the reference to the intended source.

Excel refuses to paste or reports an overlap. Press Esc to clear the copy border, then select a blank destination outside all existing tables and try again. Check that the destination has enough empty cells for the full selection. If you are copying between worksheets, verify that the target sheet is not protected; unprotect it if you have permission and the required password.

The pasted area looks like a range, not a table. Click inside the pasted cells and check whether Table Design appears. If it does not, the content may have been pasted as an ordinary range or only part of the table was selected. You can select the pasted range and choose Insert > Table, then confirm whether the range has headers. Before converting it, check for blank header cells and duplicate headings.

The table formula appears as text. Check whether the cell is formatted as Text or whether an apostrophe was entered before the equals sign. Change the cell format to General, edit the formula, and press Enter. If calculation still fails, check for a missing equals sign, an incorrect column heading, or a typo in the formula.

Frequently Asked Questions

Will copying an Excel table keep its formulas?
Yes, a normal copy and paste usually copies formulas as formulas. The references inside those formulas may adjust based on the new location, so inspect important calculations after pasting.

How do I copy a table without changing its formulas?
Excel adjusts relative references as part of copying. Use absolute references, such as $B$1, for cells that must remain fixed. If you need an exact formula text copy, inspect the copied formulas and correct any references that changed; a normal paste is not a universal way to freeze every reference.

Can I copy a table to another worksheet?
Yes. Select the table, copy it, open the destination worksheet, click a blank top-left cell, and paste. Check formulas that refer to other sheets or tables because those references may continue pointing to the original sources.

Can I copy only formulas and not formatting?
Yes. Copy the cells, then use Home > Paste > Formulas. This pastes formulas rather than the full cell appearance. Formula references can still adjust, so verify the result in the destination.

Does this work in Excel for Mac?
Yes. The same general process applies. Use Command+C and Command+V instead of Ctrl+C and Ctrl+V. Ribbon labels or the location of table commands can vary slightly by Excel for Mac build, so use the contextual Table Design or Table tab when available.

Can I copy a table into another workbook?
Yes, if both workbooks are open and the destination is editable. Paste into a blank area, then check formulas that refer to the source workbook. Such references may remain linked to the original workbook rather than automatically becoming references to the destination workbook.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.