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:
Watch our video on how to copy visible cells in Excel
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.
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.
Now, let’s copy the data and paste it into a different destination and see what Excel does.
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.
Navigate to Home, under Editing, click on the dropdown from Find and Select. Select the Go To Special option from the dropdown.
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.
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.
This takes you to the Go To Special dialog box. Select Visible cells only, and click OK.
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.
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.
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.
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.
From the dropdown, click on Show Quick Access Toolbar.
This shows another toolbar below the main menu pane.
Click on the dropdown on the left of the Quick Access Toolbar and click on More Commands.
This opens up an Excel Options dialog box. Under Choose commands from: dropdown, click on 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.
This adds the Select Visible Cells button on the Quick Access toolbar.
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.
Now, copy the cells and paste them into their destination. You can see only the visible cells are copied and pasted.
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.
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.
You can now use the shortcut key Ctrl+C to copy the selected cells. The copied cells appear with a moving dots animation.
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.
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!
