How to mirror/link cells across worksheets in Excel?

For scenarios where you need to mirror a table or range of data from a different workbook or worksheet and prefer to have live links that update when the source data changes, Excel's From Microsoft Query feature provides an advanced and automated solution. With this approach, you can pull data directly from another workbook, making it easy to consolidate or reference up-to-date information without manual copying. This method is especially useful when handling large or regularly updated datasets across files, for example in financial reporting or cross-departmental consolidation tasks.

1. Click Data > From Other Sources > From Microsoft Query to begin importing data from another workbook. See screenshot:

2. In the "Choose Data Source" dialog box, select Excel Files* from the list on the left to specify that your data source is an Excel file, and click OK.

3. In the "Select Workbook" dialog box:
(1) Open the Drives drop-down and select the drive where your source workbook is located.
(2) Navigate in the Directories window to the folder containing your workbook.
(3) Click once to select the desired workbook in the Database Name box.
(4) Click OK to continue.

4. If a warning dialog appears as shown below, simply confirm by clicking OK. This is a regular prompt when connecting to another workbook.

5. The Query Wizard will now start. Click the Options button, then in the Table Options dialog, check the System Tables option so you can view and select worksheets as tables. Confirm by clicking OK.

6. In the Query Wizard, you will see a list of worksheets in your source workbook. Expand the worksheet containing your data, select the column(s) you wish to mirror, and click to move them to the Columns in your query box.

Tip: You can select and add multiple columns if you need to mirror more than one data field. Be sure to check all necessary columns before proceeding.

7. Proceed by clicking the Next button in the Query Wizard repeatedly, following the on-screen prompts. When the "Import Data" dialog appears, choose Table for how to import, specify where to position the mirrored data, and then click OK.

Your mirrored data from the source workbook will now be displayed in the chosen location of your current workbook, and will remain linked to the original data for dynamic updates.

Note: Whenever changes are made to the source data—such as edits, additions, or deletions—simply click Data > Refresh All in your workbook to update the mirrored data automatically.

Benefits: Maintains automatic synchronization with the source data, ideal for regular reporting and cross-workbook references. However, initial setup can be complex for new users and best suits cases involving multiple columns or bulk data.

For quick, simple references between cells or ranges in different worksheets within the same workbook, using an Excel formula is the most direct method, especially suitable when you only need to mirror a handful of cells or when you want formulas that instantly reflect changes in the source data.

1. In the cell where you want to display the mirrored value, enter the following formula (replace Sheet1 and A1 with your actual source worksheet and cell):

=Sheet1!A1

2. Press Enter to confirm the formula. The value from Sheet1 cell A1 will now be shown in your target cell and will update automatically if the source changes.

3. To mirror multiple adjacent cells or an entire range, select the target range, enter the formula for the top-left cell as above, then drag the fill handle down or across to copy the formula to other desired cells. Remember to adjust or fix cell references with $ if necessary.

Tip: To link cells across workbooks instead of sheets, the reference format expands to include the file path, e.g.
= '[WorkbookName.xlsx]Sheet1'!A1
If the source workbook is moved or renamed, you'll need to update the reference.

Benefits: Extremely easy and fast for single cells or small ranges; works instantly and doesn't require any setup beyond basic formula entry. However, manual formula entry and copy-pasting can be error-prone for large or irregular ranges, and changes in worksheet names or cell structure may break the reference.

Common Issues: If you see a #REF! error, it usually means the referenced worksheet or cell no longer exists or the workbook has been renamed or moved. Always check that sheet names match exactly and that your formulas don't reference deleted cells.

For users looking to quickly mirror or link the same cell across multiple worksheets into a summary sheet, Kutools for Excel’s Dynamically Refer to Worksheets utility offers a streamlined and easy-to-follow approach. This feature is particularly effective for consolidating the same key value—like a monthly sales total or project milestone—from several team or department sheets into a central report. Compared to manual copying or formula referencing, this batch processing greatly improves consistency and reduces the risk of errors.

1. Start by adding a new worksheet for your mirrored results. You can do this by clicking the New button or in the Sheet Tab bar. Name your sheet descriptively, such as "Summary" or "Mirror Results", to easily identify it later.

2. Select the cell in the new worksheet where you want to display content mirrored from the same location across other sheets. Then navigate to Kutools > More (found in the Formula group) > Dynamically Refer to Worksheet. See screenshot:

3. In the Fill Worksheets References dialog box:
(1) Choose Fill Vertically cell after cell from the Fill order drop-down list to arrange mirrored values down the column, or pick Fill Horizontally for a row-wise summary.
(2) In Worksheet list, check each worksheet you want to mirror data from.
(3) Click Fill Range and then Close once done.

The specified cells are now dynamically mirrored from all selected worksheets to the summary sheet. Any updates in the original sheets—such as daily entry, team input, or data corrections—will be automatically reflected in the summary after calculation refresh.

Advantages: Fast, user-friendly batch operation with automatic linkage. Particularly well-suited to ongoing projects needing repeated refreshes. However, direct links only apply to identical cell positions across sheets.

Practical tip: If you need to copy or expand the mirrored area, simply repeat the above steps. Double-check that all new entries are formatted and named consistently to avoid ambiguity in the summary output.

When you need to mirror multiple separate ranges (across multiple worksheets) into a single worksheet for printing, reporting, or dashboard display, using Kutools for Excel’s Print Multiple Selections Wizard can save a significant amount of manual setup. This feature is particularly handy in scenarios such as compiling highlight sections from different departmental reports or creating a custom printout that draws content from various sheets without rearranging the underlying data. The mirrored content is displayed as images, preserving original formatting and making layout adjustments for presentations straightforward.

1. On the menu, click Kutools Plus > Printing > Print Multiple Selections Wizard to start the process. 

2. In the Print Multiple Selections Wizard that opens, click the Add button for each range or selection you want to mirror. You can add ranges from several worksheets and, if needed, remove or reorder them before finishing. Once all target ranges are listed, click Finish.

All selected ranges from multiple worksheets are now mirrored as images in a new worksheet, which helps retain layout and formatting for reporting or printing purposes.

Advantages: Preserves original cell formatting, supports flexible layouts, and streamlines reporting tasks involving multiple contributors. New data automatically updates whenever the source data changes.

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