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