How to Mirror Data in Excel (3 Easy Ways)

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.


Download Practice Workbook

You can download our practice workbook here for free!


3 Easy Ways to Mirror Data in Excel

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.

Sample Dataset to Mirror Data in Excel

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.

Link Cells to Mirror Data in Excel

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

Drag Fill Handle to Mirror Data in Excel

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.

Mirrored Data in Excel

Read More: How to Stop Cell Mirroring in Excel (5 Easy Ways)


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.

Use the INDIRECT Function to Mirror Data in Excel

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

Drag Fill Handle to Copy Formula

As a result, you will be able to mirror data in Excel successfully. And, the outcome should look like this.

Mirrored Data in Excel

Read More: How to Mirror Cells with Formula in Excel (3 Simple Ways)


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.

Create a Named Range

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

Naming the Range

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

Access to the 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.

Choose Data Source as Another Excel File

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

Browse Your Desired Excel File

  • Now, the Query Wizard โ€“ Choose Columns window will arrive.
  • Following, select Employee >> select John >> click on the Next button.

Choose Desired Excel Column

  • Afterward, the Query Wizard โ€“ Filter Data window will appear.
  • Now, choose the option John and click on the Next button.

Query Wizard - Filter Data Window

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

Query Wizard - Finish Window

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

Choose Your Cell Reference

  • Now, the mirrored cells would appear as a table.
  • For a better look, go to the Table Design tab >> untick the Filter Button option.

Untick Filter Button

Thus, you have successfully mirrored the desired cells in your desired position. And, for example, the output should look like this.

Mirrored Data in Excel

Read More: How to Mirror Text in Excel (5 Easy Ways)


๐Ÿ’ฌ 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.


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.

And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo