Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams (2023 Update)
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11 (2023 Update)
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Data Analytics
        • Introduction to Analytics and Artificial Intelligence
        • Data Analytics in Excel
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Copy Only Visible Cells in Excel? 3 Easy Ways

How to Copy Only Visible Cells in Excel? 3 Easy Ways

(Note: This guide on how to copy only visible cells in Excel is suitable for all Excel versions including Office 365)

Copying and pasting data are two commonly used operations while working on any application. Copying data eliminates the need to create or enter the data again manually.  

In Excel, you can copy data between cells, sheets, and even workbooks. However, when working on large data, you might only need to copy data that is visible or that have been filtered.

In this article, I will show you how copying works and how to copy only visible cells in Excel using 3 easy methods.

You’ll Learn:

  • How Does Copying Work in Excel?
  • How to Copy Only Visible Cells in Excel?
    • By Using Go To Special Option 
    • By Using the Quick Access Toolbar
    • By Using Shortcut Keys

Watch our video on how to copy visible cells in Excel

download wrap text worksheet
How to Copy Only Visible Cells in Excel – Workbook

Related Reads:

How to Cut, Copy and Paste Data in Microsoft Excel 2016

How to Fix the #REF Error in Excel? 3 Easy Methods

How to Count Unique Values in Excel? 3 Easy Ways to Count Unique and Distinct Values

How Does Copying Work in Excel?

When you want to replicate or move data from one place to another, you copy the necessary data and paste them into the destination.

When working with large amounts of data in real-time applications, there might be a need for you to sort out, filter, and hide any unwanted data. 

So, when you copy data which have been filtered out or hidden, what does Excel do? Let us see with an example. 

Consider a table where you have a list of students from different universities selected for the annual scholarships.

Example for Copy only visible cells in Excel
Example for How to Copy only visible cells in Excel

And, the Carlton College students have already got their scholarships. To avoid any confusion, let’s hide them from the main list of students. So, we’ll hide rows 6 and 9 which consist of the student data with IDs 2 and 5 respectively. 

Hide visible cells
Hide visible cells

Now, let’s copy the data and paste it into a different destination and see what Excel does.

Copy and Paste visible cells
Copy and Paste visible cells

Now, when we copy the data with only 4 entries and paste them into the destination, the copied data shows 6 entries. When we use standard copying methods, Excel copies the original data entirely. In other words, the data we copied is pasted with all the data in its original format with the hidden values and no filter being applied.

But, what will you do if you only want to copy the visible cells? Worry not, Excel has a way for that too.

Also Read:

How to Fix the #Div/0 Error in Excel? 2 Easy Methods

Scientific Notation in Excel – A Step-by-Step Guide

How to Create an Excel Slicer? 2 Easy Ways

How to Copy Only Visible Cells in Excel?

By Using Go To Special Option 

One way to copy visible cells only in Excel is by using the Go To Special option from the Home menu.

To copy only the visible cells, select the cells you want to copy.

Select the cells
Select the cells

Navigate to Home, under Editing, click on the dropdown from Find and Select. Select the Go To Special option from the dropdown.

Select the Go To option
Select the Go To option

This opens up a Go To Special dialog box. From the dialog box, select Visible cells only and click OK. This option only copies the cells that are visible and ignores any hidden or filtered cells.

Select visible cells only
Select visible cells only

Another simple way to enable the Visible cells only option is by using a shortcut key. 

Select the cells you want to copy and press Ctrl+G.

This opens up a Go To dialog box. In the dialog box, select Special.

Click on Special
Click on Special

This takes you to the Go To Special dialog box. Select Visible cells only, and click OK.

Select Visible Cells only
Select Visible Cells only

If you look closely at the cells, you can see the cells are separated by a fine gray line. This denotes that any operation made will pertain only to the visible cells.

The selected cells appear in a different format
The selected cells appear in a different format

Now, as usual, you can copy the cells. You can either right-click on the cells and select Copy or use the keyboard shortcut Ctrl + C. You can see the selected cells appearing with a moving dots animation.

Selected cells appear with moving dots animation
Selected cells appear with moving dots animation

Select the destination cells and paste the copied data. You can either right-click on the cell and select Paste (or use any of the paste options) or you can use the keyboard shortcut Ctrl+V. 

Copy only the visible cells
Copy only the visible cells

By Using the Quick Access Toolbar

This is another easy way to copy visible cells in Excel. This method will be most useful if you have to deal with more filters and values. You can easily toggle this option to copy visible cells whenever you want.

To enable the visible cells only option in the Quick Access toolbar, open your Excel spreadsheet.

Click on the small drop-down towards the top right of the menu pane called Ribbon Display Option. You can access this dropdown from any main menu option.

Select Ribbon Display Options
Select Ribbon Display Options

From the dropdown, click on Show Quick Access Toolbar.

Select Show Quick Access Toolbar
Select Show Quick Access Toolbar

This shows another toolbar below the main menu pane.

Click show toolbar
Click show toolbar

Click on the dropdown on the left of the Quick Access Toolbar and click on More Commands.

Select More Commands
Select More Commands

This opens up an Excel Options dialog box. Under Choose commands from: dropdown, click on All Commands.

Select All Commands
Select All Commands

This shows all the options to add to the Quick Access Toolbar. It’d be easier to find “Select Visible Cells” since the buttons are arranged alphabetically. Click on Add. This shows the selected button in the preview pane. Click OK.

Click on Add
Click on Add

This adds the Select Visible Cells button on the Quick Access toolbar.

Select Visible Cells
Select Visible Cells

Now, follow the same steps to copy and paste as mentioned in the above method.

To copy the visible cells, first, select the cells. Now, click on the Select Visible Cells button from the Quick Access toolbar.

Click on Select Visible Cells
Click on Select Visible Cells

Now, copy the cells and paste them into their destination. You can see only the visible cells are copied and pasted. 

Copy the selected cells
Copy the selected cells

Note: The Customize Quick Access toolbar option is found in the main menu pane only in Windows 11. For Windows 10, the Customize Quick Access toolbar dropdown can be seen on the title bar.

By Using Shortcut Keys

We know shortcut keys greatly help in simplifying the work which requires additional steps. For example, we often use Ctrl+C and Ctrl+V to copy and paste respectively rather than right-clicking and selecting the option. 

However, if you use the same shortcut keys, Excel copies all the data including the ones that are hidden or filtered out.  

To copy only the visible cells using shortcut keys, select the data first.

Select the cells
Select the cells

Now, press Alt+;. All the operation you perform after pressing Alt+; only pertains to the visible cells. You can see the borders of the selected cells displayed in gray.

Press Alt+;
Press Alt+;

You can now use the shortcut key Ctrl+C to copy the selected cells. The copied cells appear with a moving dots animation.

Copy the selected cells
Copy the selected cells

Now, select a destination cell and paste the data. You can either right-click the destination cell and select Paste or use the keyboard shortcut Ctrl+V to paste the data. This copies only the visible cells to the destination cell.

Copied cells
Copied cells

Note: Once the visible cells are copied and pasted, they contain no hidden elements or filtered data. So, if you delete the original data, you cannot retrieve the hidden or filtered information.

Suggested Reads:

How to Freeze Rows in Excel? 4 Easy Steps

How to Sort Dates in Excel? 6 Easy Methods

How to Remove Spaces in Excel? 3 Easy Methods

Frequently Asked Questions

What is the easiest way to copy only the visible cells in Excel?

Using the shortcut keys is the easiest and most effective method to copy only visible cells in Excel. Use the Ctrl and arrow keys to select the cells, press Alt+; and then copy and paste using Ctrl+C and Ctrl+V keys respectively.

How do I save the filtered data onto a new worksheet in Excel?

You can copy the visible cells using the shortcut key Alt+; or by using the Go To Special option and selecting Values. Then, press the shortcut key Ctrl+N to create a new workbook and paste the copied data.

How do I paste the copied values into Excel?

Select a destination cell. Then, right-click on the cell and select paste or any paste options, or, navigate to Home and click on Paste in the menu bar or simply use the keyboard shortcut Ctrl +V.

Closing Thoughts

The option to copy only visible cells is very useful to segregate and sort out data when large data are involved. Also, this greatly improves the readability and presentation of the data.

In this article, we have seen 3 easy ways on how to copy only visible cells in Excel. You can either use the Go To option if you only copy the visible cells rarely, or you can add the Select Visible Cells button in the Quick Access toolbar if you use it frequently. You can also use the shortcut key for an easier and time-saving way to copy visible cells.

If you need more high-quality Excel guides, please check out our free Excel resources center. Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Print Labels in Word (Text + Video Tutorial)

How to Add or Change your Signature in Outlook? A Step-by-Step Guide

The Excel SMALL Function – 3 Best Examples

Managing Catalogs in Photoshop Elements 11

How to Enable Full Screen in Excel? 3 Simple Ways

How to Delete a Page in Word?

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)