Paste values from a single cell to an Excel, merged cells causing lots of problems in copy-paste.

I faced this problem in my sheet, Therefore I searched and found an Excel formula to paste values from a single cell to merged cells and AutoFit merged cells in Excel in very simple steps

As per the above figure, we have two columns and we want to copy from the column which contains A, B, C…. to the column which contains 1,2,3,…, Let’s see how to do it.

First Step

Use this formula to make Excel get the information from column A to column E:

Select all values in column E and then type this formula:

“ =@INDEX($A$1:$A$7, COUNTA(C$1:C1)) “

then CTRL + Enter, it is important to type control + enter after you type the formula to apply the same formula to all selected cells, see the figure below:

Second Step

The result of our formula will be an Error message, do not worry it is still working, you will receive the following error message circular reference warning, just click ok, see the figure below

Third Step

Fixing Excel Error message:

We will tell the Excel, come on Mr. Microsoft Excel we are too lazy to copy and paste one by one all of these values from a single cell to a merged cell (Imagine if you have hundreds of cells), Please let us use the circular reference to solve this problem.

1. Go to the “File” Menu.

2. Find “Options”.

This menu will show up.

3. Go to “Formulas”.

All we need to change is the “Enable Iterative Calculation”.

4. Enable the Iterative Calculation and Press ok.

After we fixed the error message, now the sheet will be working correctly and if you change any value in column A it will be changed automatically in column E. See the figure below and download the sample sheet.

Final Step

This is how the Equation will work.

---

Download “Paste values from a single cell to merged cell” Excel Sheet