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.

Copy Merged & Filtered Cells


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.

Copy Merged & Filtered Cells in Excel

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.

Copy Filtered Cells in excel

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

After filtering, our dataset looks like this.

Copy Merged & Filtered Cells

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.

Copy Filtered Cells 9

  • 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.

Copy Filtered Cells using go to special

  • 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


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.

Copy Merged & Filtered Cellsusing quick access bar

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

Copy Merged & Filtered Cellsusing quick access bar

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.

Copy Merged & Filtered Cellsusing quick access bar

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.

Copy Merged & Filtered Cells using

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.

Copy both Merged & Filtered Cells

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

Copy Merged & Filtered Cells in Excel

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.

Copy Merged & Filtered Cells in Excel


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

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo