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 60-day!

ad combine sheets books 1


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.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft 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 Excel tools. Full feature free trial 60-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.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

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 Excel tools. Full feature free trial 60-day, no credit card required! Get it now!

doc mirror cells across sheets 16


In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!



Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
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.