Naimul Hasan Arif
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.
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.
- 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.
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.
- Press the ENTER button to have the output.
- Finally, use Fill Handle to AutoFill the rest cells.
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.
- 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),,)
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.
- AutoFill the rest cells.
Read More: How to Mirror Text in Excel (5 Easy Ways)
Practice Section
For more expertise, you can practice here.
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.
