excel how to copy and paste only filtered cells

Excel: Paste Only Filtered Cells - Step-by-Step

By Joseph Miller
AD

Ever wondered how to extract and utilize specific data from an extensive Excel spreadsheet? While the copy-paste function is a staple, it operates on entire ranges, including cells you might not need. But what if you've applied a filter, and you want to copy only the filtered cells? Here's a step-by-step guide to help you achieve just that.

Before we dive in, let's clarify what we mean by 'filtered cells'. When you apply a filter in Excel, it hides cells that don't meet the filter criteria. This guide will show you how to copy only these visible, or unhidden, cells.

Understanding Filtered Cells

First, let's ensure we're on the same page regarding filtered cells. When you filter a column, Excel hides the rows that don't match the filter criteria. The cells that remain visible are the 'filtered cells'.

AD
the poster shows how to use copy - only visible cells in an excel spreadsheet
the poster shows how to use copy - only visible cells in an excel spreadsheet
Don't copy and paste in Excel‼️ Instead Use Amazing Function #exceltips #excel #exceltricks #shorts
Don't copy and paste in Excel‼️ Instead Use Amazing Function #exceltips #excel #exceltricks #shorts
How to Copy Merged and Filtered Cells in Excel: 4 Methods - ExcelDemy
How to Copy Merged and Filtered Cells in Excel: 4 Methods - ExcelDemy

For this guide, let's assume you've filtered a column and only a subset of cells is visible.

Copying Filtered Cells

Now, let's discuss how to copy these filtered cells to use them elsewhere in your workbook or another document.

Copy Only Visible Cells in Excel with Alt + ;
Copy Only Visible Cells in Excel with Alt + ;
Filters in Excel: An Overview (with video tutorial)
Filters in Excel: An Overview (with video tutorial)

Method 1: Using Flash Fill

Excel's Flash Fill feature can copy and paste only visible cells efficiently. Here's how to use it:

1. Select the range containing your filtered cells.

AD

2. Click in the first cell where you want the copied values to appear.

3. Type the first few characters of the data in the first cell, then press Ctrl + E. Excel should automatically fill in the rest of the range with the copied values.

Method 2: Using a VBA Macro

If the Flash Fill method isn't working as expected, you can use a simple VBA macro to copy only the visible cells. Here's how:

AD

1. Press Alt + F11 to open the Visual Basic for Applications (VBA) window.

2. In the VBA editor, go to Insert > Module to add a new module.

3. Copy and paste the following code into the module:

AD

    Sub CopyVisibleCells()

        Range("A1").Select 'Change "A1" to the first cell in your filtered range.

        Selection.SpecialCells(xlCellTypeVisible).Select

        Selection.Copy

    End Sub

4. Close the VBA window, select the first cell where you want the copied values to appear, then press Alt + F8, select CopyVisibleCells, and click Run. Excel will copy only the visible cells to the new range.

Mastering these methods will save you time and effort when working with filtered cells in Excel. Happy copying!

AD