Paste values from single cell to merged cell in excel

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

RECOMMENDED COURSE

Data Management and Reporting

Data Management is the key success of construction projects reporting. This self-study course will teach you using a step by step case…

• 1,680 students
• WhatsApp support group
• Certificate

View course

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 the Resources of this Article

Paste values from single cell to merged cell in excel 400.04 KB 6807 downloads

This File is included in the following course(s)

Basic Planning Course, Data Management

---

Keep in Touch

Join our Email Newsletters and receive FREE weekly useful content!

Your Name

Email

---

Related posts:

Human Resource Management Definitions Calculate Duration in P6 based on scope Understanding Merge Hotspot, Diverge Hotspot, and Logic Hotspot in Project Scheduling Work what you love to enjoy working How to connect Primavera P6 to Power BI (Full Guide)