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.
Read More: How to Copy and Paste in Excel with Merged Cells
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.
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.
- 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
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
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
- Formula to Copy and Paste Values in Excel
- How to Copy and Paste in Excel without Formulas
- How to Copy and Paste Multiple Cells in Excel
- How to Copy and Paste Without Changing the Format in Excel
- How to Copy Only Highlighted Cells in Excel
- How to Copy Horizontal and Paste Vertical in Excel
- How to Copy Paste Vertical to Horizontal in Excel
- Formula to Copy and Paste Values in Excel Automatically
- How to Copy and Paste in Excel and Keep Cell Size
- Copy Rows from One Sheet to Another Based on Criteria in Excel
- How to Copy and Paste a Column in Excel
- How to Disable Copy and Paste in Excel without Macros
- How to Copy Rows in Excel
- How to Copy Alternate Rows in Excel
- How to Copy Excluding Hidden Rows in Excel
- How to Copy Rows Automatically in Excel to Another Sheet
- [Solved] Excel Copy Paste Loses Formatting
- How to Copy Formatting in Excel
- Formula to Copy Cell Value and Format in Excel
- How to Copy Formatting in Excel to Another Sheet
- How to Copy and Paste Formulas Without Changing Cell References in Excel
- [Fixed]: Right Click Copy and Paste Not Working in Excel
- Copy and Paste is Not Working in Excel
- [Fixed!] Paste Link Not Working in Excel
- [Fixed!] Copy and Paste Not Working Between Workbooks in Excel
- How to Copy Number Not Formula in Excel
- How to Fill Across Worksheets in Excel
- [Fixed!]: Microsoft Excel Cannot Paste the Data as Picture
- [Fixed!] CTRL C Not Working in Excel