How to mirror/link cells across worksheets in Excel? (2024)

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:

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

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.

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:

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 thebutton 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.

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!

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

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 - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

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:

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.

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

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with 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 - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

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

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

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

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

Define named range across worksheets in Excel

Create chart across/from multiple worksheets in Excel

Copy data from/into multiple worksheets in Excel?

Apply conditional formatting across worksheets/workbooks

Best Office Productivity Tools

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...

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!