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.
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.
- Third, select the entire main table like the image below.
- 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:
Read More: How to Create a Table with Existing Data 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.
- Then select the table you want to mirror and click on the ‘>’ marked button. That will show you all the columns of that table.
- Again, click on the Next button in the consequent 2 dialog box and select Finish in the last dialog box.
- 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.
- 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.
Similar Readings
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
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
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.
Let us change the main dataset as before.
So, after entering Sheet4 again, we can see the changes like below.
Read More: How to Create a Table with Headers Using Excel VBA
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:
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.
- 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.
Again, considering the previous example of changing the table, the changes reflect as follows:
Read More: How to Create Table from Another Table with Criteria 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. For any Excel-related problems, you can visit our website Exceldemy for solutions.
Related Articles
- Create Table in Excel Using Shortcut
- Create a Table in Excel Based on Cell Value
- How to Create a Table Without Data in Excel
- How to Create a Table with Merged Cells in Excel
- How to Create a Table in Excel with Multiple Columns
- How to Make a Table in Excel with Lines
- How to Create a Table with Subcategories in Excel
- How to Create a Lookup Table in Excel
- How to Make 3D Table in Excel
- How to Make a Conversion Table in Excel
- How to Make a Decision Table in Excel
- How to Create a League Table in Excel
- How to Make a Table Bigger in Excel
- How to Add New Row Automatically in an Excel Table
