- Excel 365
- Excel 2021
- Excel 2019
- Excel 2016
- February 12, 2022
- No Comments
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’s 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.
Covered in Session 12 – Power Pivot, Data Modelling, OLAP and Business Intelligence
Covered in Lesson 12-20: Use the CUBEVALUE function to query a data model.
Covered in Session 13: An Introduction to DAX
Covered in Lesson 12-6: Use an OLAP pivot table to analysze data residing in a data model.
Covered in Lesson 11-33 Create a merged query using fuzzy logic.
Covered in Session 10: 3D Maps.
Covered in Lesson 11-7: Create and use a custom data type.
Covered in Lesson 9-2: Use Natural Language Queries.
Covered in Lesson 11-8: Use custom data types in formulas.
Covered in Lesson 9-1: Use automatic data analysis to create data insights.
These are the only up-to-date Excel books currently published and includes the new Dynamic Arrays features.
They are also the only books that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.
These are the only up-to-date Excel books currently published and includes the new Dynamic Arrays features.
They are also the only books that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.
