Catalog

How to copy only visible cells in google sheets:A Step-by-Step Guide

November 20, 2023 4.6K views

Excel is an essential tool for students and professionals in various fields. Users face the problem related to copying data from Google Sheets. That brings in the need to copy only visible cells in Google Sheets. What is the step-by-step guidance for visual cell copying? How do we make filters and shortcuts in Excel? Learn all these in this Google Sheets copy visible cells tutorial.

What are Google Sheets Filters?

Filters in Google Sheets allow you to selectively display data by applying specific criteria, making it easier to focus on the information you need. To use filters in Google Sheets to display only the data you want to copy, follow these steps:

Step 1: Press anywhere in the data range, choose Home on the top menu > Sort & filter, then click the Filter button in the Toolbar.

Sort & Filter


Step 2: On the filter icon, click “Filter by condition” and then "Text contains"

Text contains in filter


Step 3: Enter the desired text, e.g., Wireless, and Press OK to apply the filter. Only the rows that meet the specified condition will be visible.

Enter specified content


Once you have filtered the data, you can copy only the visible cells.

How to copy and paste only visible values in Google Sheets on Windows and Mac (Ignore hidden rows)

The "Select Visible Cells" option allows you to select and work with only the visible cells in your dataset, excluding any hidden or filtered-out cells. To set it up,

Step 1: Press the F5 key on your keyboard.

Step 2: A POP-UP box appears; click on Special.

Click on Special


Step 3: In these many options, Select visible cells only.

Select ‘Visible cells only’


Below is the process to copy data without hidden rows and columns in Google Sheets;

Step 1: Launch Google Spreadsheet on Google.

Step 2: Open your project or worksheet.

Open project on worksheet


Step 3: Apply filters to the data as stated in the previous section. That will hide some rows based on filter conditions.

Step 4: Press the Ctrl key.

Press Control Key


Step 5: Click the visible cells to be copied. That is done by individually selecting the visual cells in your filtered dataset. It ensures that only the visible (unfiltered) cells are selected.

Click the visible cells to be copied


Step 6: Copy the desired using either the keyboard shortcut Ctrl + C or by right-clicking and selecting "Copy."

Press Ctrl + C


Step 7: You can paste the copied cells in a different location within the same worksheet or another file using the keyboard shortcut Ctrl + V or by right-clicking and selecting "Paste."

Paste to the Desired location.


Google Sheets offers a "Paste Special" option, which allows you to paste copied data with specific attributes. To access "Paste Special," follow these steps:

Step 1: Press the F5 key on your keyboard,

Step 2: A POP-UP box appears; click on Special.

Click on Special

Step 3: There will be tons of options to consider. We have comments, constants, formulas, etc. Choose the desired specific attributes and click OK.

Choose the options to consider.

How to Copy Only Visible Cells in Google Sheets on iPhone or iPad (With deleted hidden files)

To copy only visible cells in Google Sheets on an iPad, follow this step-by-step process:

Step 1: Open Google Sheets on your iPad:

Step 2: Open the project or worksheet where you want to work with the data.

Open project on worksheet

Step 3: Choose the rows you want to copy. To do this, tap on the first cell or row you want to copy, then drag your finger to select all the visible cells you need. This step ensures that you choose only the visible (unfiltered) cells, excluding any hidden rows.

Highlight the rows and click copy

Step 4: Copy the Selected Cells: You can do this by tapping the copy icon, usually represented by two sheets of paper or a clipboard icon, depending on the version of Google Sheets you are using.

Step 5: Paste the Value. Navigate to the new page or file where you want to paste the values. Tap on the cell where you want to paste the copied data.

Click Paste as shown above

The copied values will be pasted without hidden rows, ensuring that you only transfer the visible cells to the new location. You can repeat this process as necessary.

Copying only visible cells is beneficial in data reporting, analysis, and presentation. : When working with large datasets that contain filtered or hidden rows, copying only visible cells allows you to create concise and accurate reports without including irrelevant data. Additionally, during presentations, copying only visible cells helps maintain the focus on the key data points, preventing confusion with hidden or filtered data.

Best Alternative to Google Sheets - WPS Spreadsheet

WPS Office is a free all-in-one software offering a comprehensive suite of productivity tools as an alternative to Microsoft Office. Here are some key features of WPS Office;

  • Free Download and Free to Use: WPS Office is available for free, providing access to essential office applications, including Writer (Word), Spreadsheet (Excel), and Presentation (PowerPoint).

  • WPS Office offers full compatibility with Microsoft Excel, Word, and Google Sheets.

  • WPS Office includes a built-in PDF editor that offers essential features like reading, annotation, and conversion for free.

  • Users can access a rich library of office templates in WPS Office. That covers a wide range of document types, from resumes to presentations.

  • Delicate Office templates are free to download in the built-in WPS Template Library.

WPS AI was recently added to WPS Office, enhancing the software's capabilities. It integrates advanced features like document analysis, efficient formatting, and intelligent content recommendations. This AI-driven assistant aims to improve work efficiency by providing thoughtful suggestions and streamlining document creation and editing.

Trustpilotstars4.8
WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
avator
Algirdas Jasaitis
logo

FAQs

Q1: How do I sum only visible cells?

Step 1: Start by organizing the data in the table by clicking on Control+T or filter the desired data by applying the filter options.

Step 2: Next, select the cell immediately below the column you want to total, click the AutoSum button on the ribbon, and then the enter button on your keyboard.

 Click Autosum and then press ENTER

Click Autosum and then press ENTER

Q2: How do I delete only visible cells in Excel?

Step 1: Start by filtering so you are left with the visible cells you desire in Excel.

Step 2: Next, use this keyboard shortcut to delete only visible cells in a selection.

For example, if you have a selection of hidden cells, you can press the "Ctrl" and "-" keys to delete only the visible cells in the sample.

Q3: How do I select thousands of rows in Google Sheets?

Step 1: Choose a row or cell to use.

Step 2: Press the 3-dot on the top right, then Choose "Select cell or range."

 Choose

Choose "Select cell or range."

Step 3:Put in the range as shown below.

Put in the range as shown.

Put in the range as shown.

Step 4: Click OK.

Summary

This comprehensive 2023 guide teaches how to copy only visible cells in Google Sheets. That includes utilizing filters to display specific data and a step-by-step process to copy visible cells. This technique is invaluable for data reporting and analysis. Discover an excellent alternative to Google Sheets in WPS Office, offering compatibility with Microsoft Office, rich templates, and the recent addition of WPS AI for enhanced productivity.


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.