Sometimes, we need to put the same data in different columns or rows for calculation and visualization purposes. In this regard, it is quicker and easier if we mirror those data between those cells or worksheets with some tricks. In this article, I will show you 3 easy ways to mirror data in Excel.
How to Mirror Data in Excel: 3 Easy Ways
Say, you have a sales report for 8 employees of a particular month. Now, for all other months, when entering the employees’ names, you can simply mirror the Employee cells with your dataset. You can accomplish this mirroring in any of the 3 following ways.
In this article, we have used the Office 365 version of Microsoft Excel. But, you can use any other version of Excel to follow these ways. If you face any problems regarding versions, please leave a comment below.
1. Link Cells to Mirror Data
You can simply link your required cells to mirror data. Go through the steps below to do this.
📌 Steps:
- First and foremost, click on the B5 cell and insert the following formula.
='Sample Dataset'!B5- Subsequently, hit the Enter button.
- As a result, you will get the name of the first employee of your dataset.
- Now, for all other employees, place your cursor in the bottom right position of the B5 cell.
- Consequently, a black fill handle will appear.
- Subsequently, drag it below to copy the same formula dynamically.
Thus, the mirroring would be successful and you would get the names of the employees through this. And, for example, the result would look like this.
Read More: How to Reverse Data in Excel Cell
2. Mirror Data Using INDIRECT and ROW Functions
Besides, you can use the INDIRECT function along with the ROW function to mirror data in Excel dynamically. Follow the steps below to accomplish this.
📌 Steps:
- At the very beginning, click on the B5 cell and insert the following formula.
=INDIRECT("'Sample Dataset'!B" & ROW())- Afterward, hit the Enter button.
- As a result, you will get the name of the first employee from your dataset.
- Now, place your cursor in the bottom right position of the cell.
- Following, a black fill handle will appear. Now, drag it below to copy the same formula below.
As a result, you will be able to mirror data in Excel successfully. And, the outcome should look like this.
3. Using Microsoft Query Feature
Moreover, you can use the Microsoft Query feature to mirror data in Excel. Follow the steps below to achieve your desired result this way.
📌 Steps:
- First, you need to create a named range of the cells you want to mirror.
- To do this, select the cells B4: B12 >> go to the Formula tab >> Defined Names group >> Create from Selection tool.
- As a result, a window named Create Names from Selection would appear.
- Following, check the Top Row option here and click on the OK button.
- Now, go to your sheet where you want mirroring to occur.
- Afterward, go to the Data tab >> Get Data tool >> From Other Sources option >> From Microsoft Query option.
- Consequently, the Choose Data Source window will appear.
- Following, choose the Excel Files* option from the Databases tab. And, click on the OK button.
- Now, the Select Workbook window will appear.
- Subsequently, browse your Excel file from Drive, Directories, and Database Name options. Finally, click on the OK button.
- Now, the Query Wizard – Choose Columns window will arrive.
- Following, select Employee >> select John >> click on the Next button.
- Afterward, the Query Wizard – Filter Data window will appear.
- Now, choose the option John and click on the Next button.
- At this time, the Query Wizard – Sort Order window will appear. Click on the Next button.
- Following, the Query Wizard – Finish window would appear.
- Choose the first option here and click on the Finish button.
- As a result, the Import Data window will appear.
- Subsequently, write your cell reference where you want to put the mirrored cells (B5 here).
- Last but not least, click on the OK button.
- Now, the mirrored cells would appear as a table.
- For a better look, go to the Table Design tab >> untick the Filter Button option.
Thus, you have successfully mirrored the desired cells in your desired position. And, for example, the output should look like this.
💬 Things to Remember
Using the Microsoft Query Feature would enable you to mirror data between worksheets. But, the other two ways described here would be able to mirror data between worksheets only.
Download Practice Workbook
You can download our practice workbook here for free!
Conclusion
So, I have shown you the 3 easy and effective ways to mirror data in Excel. Go through the full article carefully to understand it better and apply it afterward according to your needs. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to contact me.
