Duplicating tables to other worksheets

There are a few ways to duplicate a table of data onto another worksheet.

Copy and Paste

The simplest way to duplicate a table onto another worksheet is to simply copy it and paste it onto the other worksheet, but this will create a separate copy that won’t update when the first table is changed.

If copying and pasting is unfamiliar to you, you can see an in-depth explanation in our Essential Skills Books and E-books.

Cross-worksheet formulas

Excel allows you to create formulas that extract values from other worksheets using a syntax similar to:

=Sheet1!A1

You could use these formulas to extract an entire table onto another worksheet, but if rows are added or deleted from the original table the formulas will need to be revised.

Cross-worksheet formulas are also fully covered by our Essential Skills Books and E-books.

Using Get & Transform

Excel 2016’s new Get & Transform tool allows you to extract data from a Table and load it anywhere else in a workbook. First the data must be defined as a Table, then it can be loaded into Get & Transform using:

Data > Get & Transform Data > From Table/Range

If data is duplicated using Get & Transform it will account for rows being added or removed whenever it is refreshed.

Get & Transform is a true Expert feature and is covered in great dpeth in our Expert Skills Books and E-books.

Leave a Comment

Your email address will not be published.