paste values from single cell to merged cell in excel

paste values from single cell to merged cell in excel, merged cells causing lots of problems in copy paste right? How many times you needed to copy from single cells to a merged cells and you had to do it one by one ??

I faced this problem in my bar chart sheet (Create Gantt Chart and cash flow using excel with sample file), Therefore i searched and found a Excel Formula to Paste Values from 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,…, Lets see how to do it.

1- Use my secret formula to tell the excel i want to get the information from column A to column E:

Select all values in the column E and then type this formula =INDEX($A$1:$A$7,COUNTA(E$1:E1)) 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:

2- Now check the result

the result of our formula will be Error message 🙂 ha ha ha , do not worry we still working, you will receive the following error message circular reference warning, just click ok , see the figure below:

3- fix excel error message:

we will tell the excel, common Mr. Microsoft excel we are too lazy to copy paste one by one all this values from single cell to merged cell (imagine if you have hundred’s of cells), Please let us use the circular reference to solve this problem. from file tap —— click option—— then from formula tap———- click enable iterative calculation—— then OK , see figures below.

4- See the correct result and download the sample sheet:

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