How to Copy Filtered Data to Another Sheet in Excel

Filtering data in Excel is a fundamental skill, but when it's time to share or analyze a specific subset, you might hit a snag. A straightforward copy-and-paste often grabs the hidden rows along with your filtered view, leading to an incorrect dataset in your new sheet. This guide will walk you through several effective methods to copy only your visible, filtered data, from a quick keyboard shortcut to a fully automated solution.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Why a Simple Copy-Paste Fails with Filtered Data

Before jumping into the solutions, it's helpful to understand the problem. When you apply a filter in Excel, you're not deleting rows, you're simply hiding them from view. Think of them as being temporarily tucked away. When you highlight a range that contains these hidden rows and use the standard Ctrl + C command, Excel often grabs everything - what you can see and what you can't.

The result is that when you paste this data into another sheet, the hidden rows reappear, defeating the purpose of filtering in the first place. Fortunately, Excel has several built-in tools designed specifically to select and copy only the cells that are visible on your screen.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Method 1: The 'Go To Special' Command (The Classic Approach)

The most tried-and-true method for copying visible cells is using the "Go To Special" command. It's reliable and gives you precise control over what gets copied. It might sound complex, but it takes just a few extra clicks once you know the process.

Step-by-Step Instructions:

1. Apply Your Filter: Start by setting up the filters on your data. Go to the Data tab and click the Filter icon. Use the dropdown arrows in your header row to filter your data down to the specific subset you need.
2. Select Your Filtered Range: Once your data is filtered, click and drag to select the entire range you want to copy, including the headers.
3. Open 'Go To Special':
4. Select Visible Cells Only: In the "Go To Special" dialog box, you'll see a list of selection options. Choose the radio button for Visible cells only and click OK.
5. Copy the Selection: You'll now notice faint white lines or a different selection pattern within your filtered range, indicating that Excel has selected only the visible cells, skipping the hidden ones. Now, press Ctrl + C to copy. The "marching ants" border will appear around the individual blocks of visible cells.
6. Paste to the New Sheet: Navigate to your destination worksheet, select the cell where you want your data to start (e.g., cell A1), and press Ctrl + V to paste. Only the data you could see in your filtered view will appear.

Method 2: The Fast Keyboard Shortcut (Alt + ,)

If you prefer using keyboard shortcuts to speed up your workflow, there's a specific one that does the exact same thing as the "Go To Special" method but in a single keystroke. This is perfect for those who perform this task frequently.

The shortcut is Alt + , (Alt key plus the semicolon key).

Step-by-Step Instructions:

1. Filter your data just as you did in the first method.
2. Highlight the entire range of filtered data you wish to copy.
3. Press Alt + , on your keyboard. While it might seem like nothing happened, you've just told Excel to select only the visible cells within your highlighted area.
4. Press Ctrl + C to copy the visible cells.
5. Go to the new spreadsheet and press Ctrl + V to paste.

This shortcut is incredibly efficient and is the go-to method for many long-time Excel users. It collapses steps 3 and 4 of the "Go To Special" method into one quick action.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Method 3: Format as Table (A Smarter Way to Work)

If your data isn't already formatted as an official Excel Table, you're missing out on a feature that can make this process even simpler. Excel Tables have built-in intelligence that often makes manually selecting visible cells unnecessary.

Step-by-Step Instructions:

1. Convert Data to a Table:
2. Filter and Copy the Table Data:
3. Paste into the New Sheet:

Working with Tables is a best practice in Excel, as it simplifies many analytics and management tasks, including copying filtered data.

Method 4: Use Advanced Filter

For more complex filtering criteria or to streamline the process of copying to another location, Excel's Advanced Filter feature is a powerful choice. This tool can filter and copy the data in a single operation, saving you separate steps.

Step-by-step Instructions:

1. Set Up Your Criteria Range: Unlike the simple filter, the Advanced Filter needs a separate range where you define your filtering rules.
2. Open the Advanced Filter Dialog Box: Click a single cell inside your main data table. Go to the Data tab and, in the Sort & Filter group, click Advanced. It's right next to the main Filter button.
3. Configure the Filter:
4. Execute: Click OK. Excel will instantly run the filter based on your criteria and paste the matching data into your specified location. There's no need to manually copy and paste.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Method 5: Power Query for Automated Reports

If you find yourself copying and pasting filtered data on a regular basis - like for a weekly sales report - all of the manual methods above are inefficient. This is the perfect use case for Power Query (also known as Get & Transform Data in modern Excel versions).

Power Query lets you set up filtering rules once. After that, you can update your report with a single click. It's a game-changer for repeatable tasks.

Step-by-Step Overview:

1. Load Data into Power Query: Click any cell in your data table (ideally an Excel Table via "Ctrl+T") and on the Data tab, click From Table/Range.
2. Filter Data in Power Query Editor: The Power Query Editor window will open. This interface looks a little different than a regular spreadsheet, but it's incredibly powerful. Just like you would in Excel, click the dropdown arrow on any column header to apply filters.
3. Close and Load the Filtered Data: Once you've applied your filtering rules, click the "Close and Load" button. By default, this will load your filtered data into a new worksheet as a beautifully-formatted Excel Table. If you want, you can also use the "Close & Load To..." to specify exactly where you want it to go.
4. Refresh with Just a Click: This is where the magic happens. Anytime your source data gets updated (new sales rows added, for example), simply go to the resulting data sheet, right-click your Power Query table, and click "Refresh." Excel will automatically re-run all your filtering steps in the background and update the report with the latest data. No manual copy and pasting needed ever again!

Final Thoughts

Mastering how to copy filtered data is a crucial Excel skill that keeps your reports clean and accurate. From the quick and easy "Visible Cells Only" method to the highly automated power of Power Query, there are various approaches depending on the frequency and complexity of your task.

While Excel is a fantastic tool for these kinds of data wrangling tasks, we built Graphed because we believe getting answers from your data shouldn't require manual copy-paste, formulas, or error-prone steps. Instead of manually pulling data from Excel or Google Sheets, you can connect your data sources directly to us and ask simple questions in plain English to get real-time dashboards and insights - without ever touching a CSV.