How to Copy Merged and Filtered Cells in Excel (4 Methods)

Copying merged and filtered cells in Excel can be a frustrating process. Merged and filtered cells are notorious in spreadsheets for producing problems, especially when sorting, copying, pasting, or relocating data. As you have to tell Excel the way it can understand. In this article, We will guide you through 4 simple and easy methods on how to copy merged and filtered cells in Excel. We will use a sample data set, for your better understanding.
The data set contains Date, Sales Rep, State, and Sales amount.

Download Practice Workbook

4 Ways to Copy Merged and Filtered Cells in Excel

In our methods, we will take the help of Go to Special, Paste Option and Quick Access bar to solve issues that you face while copying merged and filtered cells in Excel.

Method 1: Copy Merged Cells Using Paste Option

In our first method, we will see how to copy merged cells. As you can see from the data set, state names are merged. But if we copy that, we will get the result as follows.

But, we don’t want it this way rather we want to copy it in single cells. Let’s see, how to do it.
Steps:

• First, select the cells and press CTRL+C. And paste it anywhere in the worksheet you want by pressing CTRL+V.

As you can see the cells are still merged.

• Now, just click on the Paste option (Ctrl) and select Formulas and Number as shown in the image above.

That’s it. All the merged cells are unmerged and copied to single columns without their formatting.

Read More: How to Copy and Paste in Excel with Merged Cells (2 Methods)

Method 2: Go to Special Option to Copy Filtered Cells

When it comes to filtered data, though, copy-pasting isn’t always easy. Have you ever tried copying something into a filtered table? It’s not as simple as it appears.
Suppose, our given data set looks like the following image.

We want data based on the state of California, so we will filter the state to California.

After filtering, our dataset looks like this.

Just, look at the row numbers, Excel has hidden all other rows. That’s why, if we want to copy this data and paste it anywhere else, it looks like the following image.

As you can see, Excel has only copied data from the fifth row. So, what is the solution?

Steps:

• First, select all the cells that you want to copy. Go to the Home tab, and select Go to special as shown in the image.

• A dialogue box will pop up. From here, we will select only visible cells and click OK

As you can see from the image, all cells are selected.

• Finally, copy and paste it to another sheet and it will look like the following image.

Read More: How to Copy and Paste in Excel When Filter Is On (5 Methods)

Similar Readings

• How to Copy and Paste Thousands of Rows in Excel (3 Ways)‌
• Copy and Paste Exact Formatting in Excel(Quick 6 Methods)
• Excel Formula to Copy Cell Value from Another Sheet
• Update One Excel Worksheet from Another Sheet Automatically
• How to Copy Visible Cells Only in Excel (4 Fast Ways)

Method 3: Copy Filtered Cells Using Quick Access

In this method we will see, how to add the Select Visible Cells option in the Quick Access bar.

Steps:

• First, go to the menu bar and click on the drop down arrow as shown in the image.

• After that, select All commands > Select Visible Cells > Add > Click OK 

You can see in the following image that the command option is visible now.

• Now, you can select the cells you want and click on Visible cells, then the cells will be automatically selected.

So, now, you can copy the cells where ever you want in another sheet.

Read More: How to Copy Rows in Excel with Filter (6 Fast Methods)

Method 4: Copy Both Merged and Filtered Cells in Excel

Now, we have a data set where cells are both merged and filtered.

Suppose, you want to copy Florida filtered data, but the result shows as follows when you filter.

Steps:

• Select all the cells unmerge it and press CTRL+G and a dialogue box will pop up.

• Now, select Special, and Blank cells as shown in the image.

After that, type =, the press up arrow key and press CTRL+ ENTER

So, the problem is sorted now you can follow Method 2, to copy filtered cells.

Read More: [Fixed!] Cannot Copy Merged Cells in Excel (4 Solutions)

Practice Workbook

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, I’ve attached a practice workbook where you may practice these methods.

Conclusion

These are 4 different ways to compare two columns with a missing value. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.

Related Articles

• VBA Code to Compare Two Excel Sheets and Copy Differences
• [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)
• How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)
• Difference Between Paste and Paste Special in Excel
• How to Copy Alternate Rows in Excel (4 Ways)
• Copy and Paste in Excel and Keep Cell Size (7 Examples)
• How to Copy and Paste in Excel without Formulas (7 Easy Tricks)