Share via

Copy One-Variable Data Table Within Same Sheet Without Re-Entering Input Cell

Anonymous
2025-06-08T07:33:37+00:00

Hi all,

I'm working with a one-variable data table in Excel and would like to copy and paste it within the same sheet while keeping it fully functional — including the array behavior and input cell reference.

Currently, when I copy and paste the data table to a new location:

  • The pasted result shows only static values (numbers).
  • The data table array/formula disappears.
  • I have to go back to Data > What-If Analysis > Data Table and re-enter the input cell reference each time.

Is there a way to duplicate a data table in the same worksheet without needing to manually reset the input cell reference every time?

Any suggestions or workarounds would be appreciated. Thank you!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-06-11T19:04:24+00:00

    At this time, there is no built-in workaround to copy and duplicate a one-variable data table within the same worksheet while maintaining its dynamic functionality. When you copy the table, Excel converts it into static values because the data table is treated as an array formula with hidden internal references, and those references do not get carried over in a standard copy-paste operation.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2025-06-09T07:13:15+00:00

    Any workaround

    Was this answer helpful?

    0 comments
  3. Anonymous
    2025-06-08T18:29:17+00:00

    Hello Alifhm,

    Your engagement sparks valuable discussions, and we're grateful for your input.

    As per your description, when you copy and paste a one-variable data table within the same sheet, Excel converts it to static values by default.

    This happens because data tables are array formulas with internal references that aren’t preserved during a standard copy/paste. Currently, there isn’t a built-in method to duplicate the data table and keep its dynamic functionality intact.

    As you mentioned, you can do this by copying the range of input values and then using Data > What-If Analysis > Data Table to re-enter the input cell reference. This the best approach is to recreate the data table in the new location.

    I hope the details provided serve as a helpful resolution guide.

    Regards,

    Matt | Microsoft Community Moderator

    Was this answer helpful?

    0 comments