How to Mirror Table on Another Sheet in Excel (4 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

While working on Excel, we often face such scenarios where we need a table repeatedly in multiple sheets for calculation and analysis. Copying the entire table and pasting it wherever we need is a very efficient way for small tables. But for large tables, it’s quite hard to copy the entire table and paste it every time. So in this article, we will learn how to mirror table on another sheet in Excel.


How to Mirror Table on Another Sheet in Excel: 4 Easy Methods

We can mirror a table on another sheet in multiple ways. Mirror refers to the specific copy of the table where changes in the main table reflect in the secondary or copied table in real-time. There are several methods to do so. So, all the methods to mirror the table on another sheet in Excel are described below with proper steps. For example, and illustration of your processes, we will use the following table.

excel mirror table on another sheet


1. Linking Existing Table on Another Sheet in Excel

If we create a table on the other sheet that is totally dependent on the previous table or main table, every time we update the main table, the dependent table will automatically update itself. The process is very simple. The steps are below.

Steps:

  • First, we need to select the cell on another sheet where we want to start our table. In our case the cell is D4.
  • Second, while the cell is selected, write ‘=’ in the formula bar and click on the sheet tab that contains the main table.

Linking Existing Table on Another Sheet in Excel

  • Third, select the entire main table like the image below.

Linking Existing Table on Another Sheet in Excel

  • At last, by pressing Enter, we will see the entire table is copied and any changes made to the main table will reflect in this table as well.

Here let us change the data in the first table. For example, we changed Tom’s ID to 32F.

After the change, the mirrored table changes like:

Linking Existing Table on Another Sheet in Excel

Read More: How to Create Table from Another Table in Excel


2. Using Microsoft Query to Mirror a Table on Another Sheet

In this method, we will use Microsoft Query to generate the secondary or mirror table on another sheet in Excel. This method is also applicable while mirroring a table in different workbooks. The steps are described below.

Steps:

  • Firstly, we will go to the Data tab in Ribbon. Here we will select Get Data > From Other Sources and then click on From Microsoft Query. A dialog box will appear.

  • Secondly, click on Excel Files* and click on OK. A file selection box will appear.

  • Thirdly, locate the file location that we are working on. If it’s not saved, save it first and try again. After selecting the file, click on OK, and Query Wizard will appear.

Using Microsoft Query to Mirror table on Another Sheet

  • Then select the table you want to mirror and click on the ‘>’ marked button. That will show you all the columns of that table.

Using Microsoft Query to Mirror table on Another Sheet

  • Again, click on the Next button in the consequent 2 dialog box and select Finish in the last dialog box.

Using Microsoft Query to Mirror table on Another Sheet

  • Again, a small dialog box will appear asking for the location where you want to keep the table. Select the destination cell.

  • Now, click on properties in that dialog box. A new dialog box will appear named Connection Properties.

  • After that, tick the Refresh every and Refresh data when opening the file. In the Refresh every option, input your desired interval on how frequently you want to update the table with the changes.

Using Microsoft Query to Mirror table on Another Sheet

  • Finally, press OK in both dialog boxes to complete the process. Now it will work as a mirror table where data will be updated according to your desired time interval.

Again, let us change the data in the first table. For example, we changed Tom’s ID to 32F.

As we set Refresh every 1 minute, so you can see the change in the query method after 1 minute.

Using Microsoft Query to Mirror table on Another Sheet

Read More: How to Create Table from Another Table with Criteria in Excel


3. Applying VBA to Mirror a Table on Another Sheet

When we are working with a large function or work process, we need to follow the VBA method to avoid complexity and save operational time. If mirroring a table on another sheet is a part of a big process, we may need to mirror a table with the help of VBA code. So, in this method, we will learn how to mirror a table on another sheet in Excel.

Steps:

  • At first, we need to go to the Developer tab in the Ribbon. In that tab, select Visual Basic. A window will appear named Microsoft Visual Basic for Application. We can do the same by pressing Alt+F11.

  • Then in the window, click on Insert and select Module.

  • Next in that Module, type the following code.
Sub Mirror_table()

Worksheets("Applying VBA Code").Range("B4:D8").Value = _
Worksheets("Sample Dataset").Range("B4:D8").Value

Sheets("Applying VBA Code").ListObjects.Add(xlSrcRange, _
Range("B4:D8"), , xlYes).Name = "Tablev"

End Sub

Applying VBA to Mirror a Table on Another Sheet

Code Breakdown

  • Here we created a Subroutine to mirror a table.
  • We used the Range.Value to get the values of the original table.
  • And we used Sheets().ListObjects.Add().Name to again convert the values into a table.

  • Now double-click on the sheet you want the VBA method to apply. In our case, the sheet is Sheet4. Again, copy and paste the following code.
Private Sub Worksheet_Activate()
Call Mirror_table
ThisWorkbook.RefreshAll
End Sub

Applying VBA to Mirror a Table on Another Sheet

Code Breakdown

  • Here, to create a Private Sub, we selected Worksheet from General and selected Activate as an event from declarations. Now, whenever we activate the sheet, the code will run automatically.
  • Next, we used a Call Statement to call the Sub procedure name Mirror_Table.
  • Then, we used the VBA Refresh.All property within Thisworkbook so that whenever anyone activates the sheet the entire workbook will be refreshed.

  • Now we will press on the Run icon in that window.

  • As a result, we will get the following table. It will be updated every time someone updates the main sheet or Sample Dataset.

Applying VBA to Mirror a Table on Another Sheet

Let us change the main dataset as before.

So, after entering Sheet4 again, we can see the changes like below.

Applying VBA to Mirror a Table on Another Sheet

Read More: How to Create Table from Multiple Sheets in Excel


4. Using Conditional Formula

It’s one of the easiest solutions to this problem. We will use the IF function and conditions to create a table like before. The process is described below.

Steps:

  • In the beginning, we will select a cell where we will start our table. In our case we selected B4.
  • Then we will write this formula in the formula bar:
=IF(Table2[[#Headers],[Name]]=””,””,Table2[[#Headers],[Name]])

Using Conditional Formula

Here Table2[[#Headers],[Name]] can be replaced according to your need. To do that, you need to select the cell > write ‘=’ in it > go to the worksheet containing the main table. Now select the top left table heading.

Using Conditional Formula

  • Next press Enter. This will bring the top left table header in cell B4. Drag the Fill Handle horizontally in the right direction to mirror all the table headers.

This will mirror all the table headers.

  • Furthermore, drag the Fill Handle vertically to automatically fill up the rest of the data.

The end result will look like the below image.

Using Conditional Formula

Again, considering the previous example of changing the table, the changes reflect as follows:

Read More: How to Create a Lookup Table in Excel


Things to Remember

  • Linking Existing Table and Using Conditional Formula methods will only return the values of those cells. So, the output is not exactly a table. To get the table form, we need to format it as a table again.
  • In the VBA method, the second code refreshes the worksheet which is important to update the changes according to the main table.
  • We can refresh the query manually by going to the Data tab and pressing the Refresh All option.

Download Practice Workbook

You can download the practice workbook from here.


Conclusion

This is how we mirror the table on another sheet in Excel. Hope this will help you. If you’re still having trouble with any of these methods, let us know in the comments. Our team is ready to answer all of your questions.


Related Articles


<< Go Back to Make a Table | Excel Table | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nasir Muhammad Munim
Nasir Muhammad Munim

Nasir Muhammad Munim has been an Excel and VBA Content Developer for over a year in Exceldemy and published more than 30 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Electrical and Electronic Engineering from the Islamic University of Technology. Apart from creating Excel tutorials, he is interested in developing PostgreSQL, MySQL, and Android applications. He is fascinated by CAD-based designing systems and building... Read Full Bio

2 Comments
  1. When you add a line or column to the source table this is not duplicated in the duplicate table. Is it possible to duplicate a table including when you add or remove lines or columns?

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo