How to Copy Filtered Data in Google Sheets
Copying data from a filtered list in Google Sheets seems like it should be straightforward, but it often ends with pasting far more than you intended. Instead of just the visible rows you wanted, you get everything, including all the hidden data. This article will show you three reliable methods to copy only your filtered data, from the simplest direct copy to a powerful dynamic formula.
Understanding the Filter-Copy Challenge in Google Sheets
When you apply a filter in Google Sheets, you aren’t deleting data, you're just temporarily hiding the rows that don’t match your criteria. The default copy action (Ctrl+C or Cmd+C) is designed to grab all the data in the range you select - both visible and hidden. This is why a simple copy-and-paste often fails to produce the desired result, leading to a frustrating cleanup task.
For example, you might filter a master sales list to see only transactions from the "West" region, select the results, copy them, and then paste them into a new tab. To your surprise, sales from every other region suddenly appear. Luckily, Google Sheets provides several ways to get around this default behavior and capture just the data you see on your screen.
Method 1: The Simple "Select, Copy, Paste" Tactic
For quick, one-off tasks on a sheet that you’re managing yourself, the most direct method usually works. Google Sheets has become much smarter about handling filtered data, so this simple copy-paste approach now works more reliably than it has in the past, or in other spreadsheet applications.
Here’s how to do it step-by-step.
Step 1: Apply Your Filter
First, turn on the filter for your dataset.
Select a cell anywhere within your data.
Navigate to the menu and click Data > Create a filter, or click the filter icon on your toolbar.
A small inverted pyramid (funnel icon) will appear in each of your header cells. Click the funnel icon in the column you want to filter.
Set your conditions. You can filter by condition (e.g., text contains, date is before) or by value (unchecking the items you want to hide). For instance, in a "Region" column, you might uncheck all values except "West."
After you click "OK," your sheet will only display the rows that match your filter criteria.
Step 2: Select and Copy the Filtered Data
Now, select only the rows you want to copy. Be careful not to use a broad-sweeping shortcut that might include headers or unintentionally grab hidden cells. The safest way is a manual selection.
Click on the first cell of your filtered results (e.g., A2).
Scroll down to the last row of your visible data.
Hold down the Shift key and click on the last cell in the last row of your results (e.g., F58). This action will highlight all the visible cells in between.
Press Ctrl+C (Windows/ChromeOS) or Cmd+C (Mac) to copy. Google Sheets is smart enough to recognize that you only want the visible cells from your filtered view.
When you now paste into a new location (using Ctrl+V or Cmd+V), only the data you saw on the screen should appear.
When to use this method: It’s perfect for quick pulls when you’re working alone. However, be aware that this filter is "live" and visible to anyone else who has the sheet open. If they change it, your view changes too, which can be disruptive in a collaborative environment.
Method 2: Using Filter Views for Collaboration
If you're working on a shared spreadsheet and don't want to disrupt your team's view, a Filter View is the professional choice. A Filter View lets you create a private, saved version of your filter that doesn't affect anyone else. You can also name and save multiple views for easy access later.
Step 1: Create and Name a New Filter View
Instead of applying a regular filter, go to Data > Filter views > Create new filter view.
Your spreadsheet will transform slightly, with dark gray borders at the top and left, indicating you're in a private view.
In the bar at the top, you'll see a field labeled "Name." Give your view a clear name, like "Q4 West Region Sales," so you can easily find it again.
Step 2: Apply Conditions and Copy the Data
Once your view is created, use the funnel icons in the header row to apply your filter criteria, just as you would with a regular filter.
Your data will be filtered, but only for you. Anyone else looking at the sheet will still see the full, unfiltered dataset.
Now, use the same selection and copy technique from Method 1: click the first cell, scroll to the bottom, Shift-click the last cell, and press Ctrl+C or Cmd+C.
Paste the data into its new destination. Again, only the nice, clean, visible data will be transferred over.
To exit a Filter View, simply click the "X" in the top right corner of the dark gray name bar. You can always re-apply this saved view by going to Data > Filter views and selecting it from the list.
Method 3: The FILTER Function for a Dynamic Report
Both methods above give you a static copy of your data at a single point in time. If the source data changes, you have to repeat the entire process. The FILTER function is a far more advanced solution that creates a live, dynamic report reflecting your filtered data that updates automatically.
Think of it as creating a permanent, real-time filtered output in a completely new location, without having to mess with the original dataset at all.
How the FILTER Function Works
The syntax for the function is:
range: The full range of data you want to retrieve (e.g.,
A2:F500).condition1: A logical test on a column that determines which rows to keep. It must be a column of the same height as your range (e.g.,
C2:C500 = "West").[condition2, ...]: Optional additional conditions to further narrow down the results.
Step-by-Step Example
Let’s say your main data lives on a sheet named "SalesData" in columns A through F. You want to pull all sales from the "West" region where the sale amount (in column D) is over $1,000.
Open a new sheet or click on an empty cell where you want your filtered report to start.
Type the following formula into that single cell:
=FILTER(SalesData!A2:F500, SalesData!C2:C500 = "West", SalesData!D2:D500 > 1000)
Press Enter. Google Sheets will automatically populate the results in the cells below and to the right of your formula. You do not need to drag the formula down.
This report is now "live." If you add a new sale in "SalesData" that meets these criteria, it will instantly appear in your filtered table. If a sale's value is changed, the report will update automatically. This turns a repetitive copy-paste task into a set-it-and-forget-it automated report.
Bonus Tip: Making Dynamic Data Static
Sometimes you need both the power of a dynamic formula and a static "snapshot" of the data at a specific moment. To do this, simply copy the output generated by your FILTER function, and then use the "Paste special" command: Paste as values (shortcut: Ctrl+Shift+V or Cmd+Shift+V). This will paste only the text and numbers, stripping out the underlying formula and making your data static.
Final Thoughts
Mastering how to copy filtered data is a vital Google Sheets skill that moves you beyond basic data entry. For quick personal tasks, the straightforward select-and-copy method works well. When working with a team, Filter Views are the considerate approach, and for building recurring or automated reports, the FILTER function is an incredibly powerful tool.
While these Sheets techniques are great for focused tasks, we know the reality is that the truly important insights often require pulling data from multiple platforms at once - like combining Google Analytics traffic with your Shopify sales data. We designed Graphed to solve exactly that challenge. Instead of manually exporting and blending datasets, we let you connect your sources, ask questions like "show me how Facebook campaign costs compare to sales revenue last month," and get an interactive, real-time dashboard in seconds, skipping the spreadsheet wrangling entirely.