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

Sometimes, we have to deal with a huge amount of data stored in several worksheets. In that case, we can mirror the cells in excel instead of writing them on several occasions. There are 3 very simple ways mirror cells in Excel with formula that we are going to discuss in this article. I hope this article will be helpful in lessening the workload and saving time.

For more clarification, I have used a dataset on Ballon d’Or Finalists’20 in Serial No and Ballon d’Or Finalists’20 columns.

Formula to Mirror Cells in Excel


Download Practice Workbook


What Are Mirror Cells in Excel?

In general, the act of mimicking another person’s gesture, speaking pattern, or attitude is known as mirroring. But in Excel, Mirror cells mean linking cells across sheets in a defined pattern.


3 Simple Ways to Mirror Cells with Formula in Excel

1. Formula with Cell Reference to Mirror Cells

The simplest way to mirror cells is to use the cell reference in the formula. In this way, we can create mirror across the sheets.

Steps:

  • Select a cell in a new worksheet where you want to mirror cells. Here, I have created a worksheet named Cell Ref and selected cell C5 for mirroring.
  • Then, input the Equal Sign (=) on that cell.

Formula with Cell Reference to Mirror Cells

  • Go to the sheet from where you want to mirror cells and select the cells. In my case, I went to the sheet named Ballon d’Or and selected cells from C5 to C9.

  • Finally, press ENTER.

Formula with Cell Reference to Mirror Cells

Thus, we can mirror cells across sheets.

Read More: How to Mirror Data in Excel (3 Easy Ways)


2. Using INDIRECT and ROW Functions to Mirror Cells

There is another formula with a combination of the INDIRECT function and the ROW function to mirror cells. The whole procedure is mentioned below.

Steps:

  • Firstly, pick a cell in a new worksheet to mirror cells. Here, I have created a worksheet named INDIRECT and selected cell C5 for mirroring.
  • Next, input the following formula.
=INDIRECT("'Ballon d''Or'!C" & ROW())

Here, the ROW function identifies the row number. Then, it gets merged with strings inside the INDIRECT function and creates cell mirror.

Using INDIRECT and ROW Functions

  • Press the ENTER button to have the output.

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


3. Applying OFFSET and ROW Functions to Mirror Cells

A combined formula with the OFFSET function and the ROW function can also be a terrific way to mirror cells. In this process, we can have the mirror cells in the opposite order.

Steps:

  • I have created a dataset of the Ballon d’Or Finalists’20 in an order. But the Ballon d’Or’21 ranking was exactly in the opposite order.

Applying OFFSET and ROW Functions

  • Create a new dataset to have the output in the opposite order.
  • Next, input the following formula in that cell (i.e. C13).
=OFFSET($C$10,ROW($C$10)-(ROW(C10)+1),,)

Applying OFFSET and ROW Functions

Formula Breakdown

ROW($C$10)-(ROW(C10)+1) —> Here, the ROW function returns the row number
Output: {-1}

OFFSET($C$10,ROW($C$10)-(ROW(C10)+1),,) —> The OFFSET function returns the cell value according to the number from the cell C10.
Output: Robert Lewandowski

  • Press ENTER to have the result.

Applying OFFSET and ROW Functions

  • AutoFill the rest cells.

Applying OFFSET and ROW Functions

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


Practice Section

For more expertise, you can practice here.

Practice section


Conclusion

That’s all for today. I have tried to explain 3 very simple ways to mirror cells in Excel with formula. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.


Related Articles

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo