Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to mirror/link cells across worksheets in Excel?

Normally we can apply the formula =Sheet1!A1 to mirror the cell content in Cell A1 of Sheet1 to another worksheets. But this article provides much easier and more convenient ways to batch mirror or link cells/ranges across worksheets in Excel.

Mirror/link cells across worksheets with From Microsoft Query feature

Mirror same cell across multiple worksheets with Kutools for Excel

Mirror multiple ranges across multiple worksheets with Kutools for Excel

Easily combine multiple worksheets/workbooks into single worksheet/workbook

It may be tedious to combine dozens of sheets from different workbooks into one sheet. But with Kutools for Excel's Combine (worksheets and workbooks) utility, you can get it done with just several clicks! Full Feature Free Trial 30-day!

ad combine sheets books 1

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

This method will walk you through mirroring table of another workbook with the From Microsoft Query feature in Excel.

1. Click Data > From Other Sources > From Microsoft Query. See screenshot:

doc mirror cells across sheets 1

2. In the Choose Data Source dialog box, select the Excel Files* in the left box, and click the OK button.

doc mirror cells across sheets 2

3. In the Select Workbook dialog box, please:

(1) Select the drive which contains the workbook you will mirror data from in the Drives drop down list;

(2) Double click to open the folder which contains the workbook you will mirror from in the Directories box;

(3) Click to highlight the workbook in the Database Name box.

(4) Click the OK button.

doc mirror cells across sheets 3

4. A warning dialog box will comes out as below screenshot shown. Click the OK button to go ahead.

5. Now Query Wizard opens. Click the Options button to open the Table Options button, and then check the System Tables option, and click the OK button. See screenshot:

doc mirror cells across sheets 5

6. Now you return to the Query Wizard and worksheets are listing in the left box. Please expand the worksheet you will mirror data from in the left box, next click the column you will mirror, and then click thedoc mirror cells across sheets 8button to add it to the right Columns in your query box. See screenshot:

Note: you can mirror/add multiple columns from a specified worksheet  to the Columns in your query box, and continue the following steps.

7. Click the Next buttons repeatedly until the Import Data dialog box comes out. In the Import Data dialog box, please check the Table option, next specify the destination range you will place the mirror data at, and click the OK button. See screenshot:

And now the specified columns from the specified workbook are mirrored and linked into current workbook.

Note: When you change cell values, add/delete rows in original workbook, the mirrored data in the destination range will change automatically after clicking Data > Refresh All.


This method will introduce Kutools for Excel’s Dynamically Refer to Worksheets utility to batch mirror the same cell across multiple worksheets into a new worksheet in Excel.

1. Create a new worksheet with clicking the New button or in the Sheet Tab bar.

2. Select the cell in the new worksheet whose cell content you will copy from other worksheet, and click Kutools > More (in the Formula group) > Dynamically Refer to Worksheet. See screenshot:

doc dynamically refer to sheets 1

3. In the opening Fill Workbooks References dialog box, please:

(1) Select the Fill Vertically cell after cell from the Fill order drop down list;

(2) In the Worksheet list section, check the worksheet where you will mirror cell content from;

(3) Click the Fill Range button and Close button successively.

doc mirror cells across sheets 12

And then you will see the same cells from all specified worksheets are mirrored into the new worksheet. See above screenshot:

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


This method will introduce Kutools for Excel’s Print Multiple Selections Wizard utility to mirror multiple ranges from many worksheets as picture in a new worksheet.

1. Click Enterprise > Printing > Print Multiple Selections Wizard. See screenshot:

doc mirror cells across sheets 13

2. In the opening Print Multiple Selections Wizard, click the Add button  to add ranges repeatedly, and then click the Finish button. See screenshot:

doc mirror cells across sheets 15

And then you will see all specified ranges from multiple worksheets are mirrored as pictures in a new worksheet.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

doc mirror cells across sheets 16


Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.