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
Get & Transform (now included in all current Excel versions and previously called Power Query) is able to create a linked table. Linked tables can be configured to auto-refresh every minute so the linked table will always be an exact duplicate of the original table. You will need a lot of background understanding of the principles behind Get & Transform in order to use this feature. Many Excel books rule Get & Transform to be “out of scope” but this amazing tool is covered in great depth in our Expert Skills Books and E-books.
