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.

How to Copy Merged and Filtered Cells in Excel: 4 Ways

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.

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.

Method 3: Copy Filtered Cells Using Quick Access Bar

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.

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: How to Copy Cell If Condition Is Met in Excel

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.

Download Practice Workbook

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

• If Value Exists in Column Then Copy Another Cell in Excel
• How to Copy Only Highlighted Cells in Excel
• How to Copy and Paste Multiple Cells in Excel
• If Value Exists in Column Then TRUE in Excel
• How to Copy Above Cell in Excel
• How to Copy Multiple Cells to Another Sheet in Excel

<< Go Back to Copy a Cell | Copy Paste in Excel | Learn Excel