Rubayed Razib Suprov
On many occasions, we can see that there is some cell values that are mirrored or appear in other cells. Most of the cases they are undesirable. If you are curious to know how you can Stop cell Mirroring in Excel, then this article may come in handy for you. In this article, we discuss how you can Stop cell Mirroring in Excel with an elaborate explanation.
Download Practice Workbook
Download this practice workbook below.
5 Easy Ways to Stop Cell Mirroring in Excel
For the demonstration purpose, we are going to use the following dataset. This includes the Products and their Id, Cost, Unit Price, and the Revenue generated throughout selling.
1. Ungrouping Worksheets
When worksheets are grouped together, they can create an unpleasant situation where cell values from one worksheet are mirrored to the other worksheet automatically. For this, we need to ungroup the worksheet present in the workbook to Stop cell Mirroring in Excel.
Steps
- When multiple sheets in the workbook are in a grouped situation, then entering anything into any cell will input something into the same cell address across all of the grouped sheets.
- This can be annoying and seems like input from one cell is now in a mirror state in multiple cells.
- To resolve this, we need to remove the grouping of the sheets, and after then the Mirroring will Stop.
- To ungroup a set group of sheets in Excel, right-click on any sheet in the grouped sheets.
- After then, click on the Ungroup Sheets from the context menu.
- Right after you click on the Ungroup Sheets, you will notice that the Mirroring of the cells in multiple sheets Stop
- Now entering anything in any of the Ungrouped cells results in only change only in that cell.
2. Modify Formulas Across Worksheet
While typing or using formulas across various cells in a worksheet, sometimes we mistakenly create or input formulas in the worksheet which is copying the cell value to another cell only. For this, we have to detect and eliminate/edit the formula, in order to Stop cell formatting in Excel.
Steps
- In the below dataset, we can notice that the formula used in the range of cells G5:G13 are actually for the profit calculation. For Profit calculation, we have to subtract the Cost of good production from the Revenue earned.
- Then we entered appropriate formulas to calculate Profit in the range of cell G5:G13.
- If we look at the formula distributions, then we can observe that there are some formulas that are displaced. And they need to be modified.
- In the cells G6, G10, and G13. We can see some formulas that are not supposed to be there.
- Those formulas will be modified in the subsequent steps.
- As those range of cell values is supposed to calculate profit, we need to delete the Cost from the Revenue.
- After modifying the highlighted formulas, we can successfully Stop cell monitoring in Excel.
Read More: How to Mirror Cells with Formula in Excel (3 Simple Ways)
3. Delete External Data Link
For a variety of reasons, we need to add database information, like CSV/Excel/DBS type of external information. If those files stay connected, their values can influence the destination files, and may seem that the cell value from the parent file is now having a mirror effect in the destination file. We, therefore, need to remove the file link or query connection from the Excel file.
Steps
- If there is any external database like Excel is connected to another Excel workbook, then any input in the source dataset will result in the data input in the destination dataset.
- Then head to the Data tab, and then click on the Queries and Connections.
- There will be a new side panel on the right side named Queries and Connections.
- In the side panel, right-click on it, and from the context menu click Delete.
- There will be a warning sign, as shown below.
- Any input in the source file is not causing any change in the destination file.
Read More: How to Mirror Data in Excel (3 Easy Ways)
4. Prevent Cell Mirroring in Excel Table
In the Excel tables, we can see that any kind of change in the Excel formula can result in the same impact on the cells the whole range of cells. Which is undesirable. To prevent this, we need to deactivate Autocorrect settings, in order to Stop cell Mirroring in the Excel table.
Steps
- In Excel Table, there is an issue with changing the cell formula. If you want to change any cell formula in the worksheet, this will also affect the other cells.
- In other words, it will cause Mirroring in the other cells in the full column.
- For example, we can observe that the formula used in the range of cells G5:G13, in the range of cells H5:H13.
- They use the formula that estimates the Bonus which is 5% of the Profit.
- But we have a new condition, the Bonus for the Product B-000154 will be zero. Meaning the seller wouldn’t receive any profit for this product.
- For this, we need to multiply 0.0 with F9 in cell G9 as shown in the image.
- Right after we press Enter after inputting the formula, we can see that all the other cells in the range of cells are also multiplying the Profit values with 0.
- The result is that the whole range of cell G5:G13 is now showing 0.
- Immediately after this, press Ctrl+Z, doing this will undo the previous step.
- Which will bring back the original cell values, except for the cell we selected previously.
- Cell G9 will show 0.0 as the formula suggested.
- To resolve this issue, click on the File tab on the corner of the sheet.
- Then from the Start menu, click on the Options command.
- In the Excel Options dialog box, click on Proofing.
- Then click on the Autocorrect options.
- There will be an Autocorrect options dialog box.
- In that box, untick the Fill formulas in tables to create calculated columns.
- Finally, click on OK.
- Right after this, try to enter 0.02 instead of 0.0 in the cell G9 formula.
- You will notice that there is no change in the other cells in the range of cell G5:G13.
- This indicates that we successfully eliminated the Mirroring or affecting other cells issues.
Read More: How to Mirror Table in Excel (4 Easy Methods)
5. Apply User-Defined Function
Mirroring the text can be because of the reverse order of the text presented in the worksheet, which can create the impression of cell Mirroring. We, therefore, need to reverse the order in order to Stop the Mirroring cell value in the Excel
Steps
- We have a dataset where the cell value is in reverse order.
- The text value in the range of cell B5:B13 is now in the reverse order in the range of cell C5:C13.
- If there is any way this type of dataset containing formula ended up for you, then you need to roll the text back to the original.
- To this, we are gonna need a custom VBA function.
- First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’for opening the Visual Basic Editor.
- Then there will be a new dialog box, in that dialog box, click on the Insert > Module.
- Next, in the Module editor window, type the following code:
Public Function Stop_Cell_Mirror(x As Range)
Stop_Cell_Mirror = StrReverse(x.Text)
End Function
- Then close the Module window.
- The custom function is now all set.
- Select cell D5 and enter the following formula:
=Stop_Cell_Mirror(C5)
Entering this formula will reverse the order of the text in cell C5.
- Then drag the Fill Handle to cell D13.
- Doing this will fill the range of cell D5:D13 with the reversed order or mirrored text of the range of cells C5:C13.
Read More: How to Mirror Text in Excel (5 Easy Ways)
Conclusion
To sum it up, the issue of how we can Stop cell Mirroring in Excel is described here in 5 separate ways. The VBA Macro method requires prior VBA-related knowledge to understand from scratch.
For this problem, a macro-enabled workbook is available to download where you can practice these methods.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.
