{"id":9299,"date":"2022-04-04T14:12:15","date_gmt":"2022-04-04T14:12:15","guid":{"rendered":"https:\/\/officetuts.net\/excel\/?p=9299"},"modified":"2024-03-28T13:49:29","modified_gmt":"2024-03-28T13:49:29","slug":"select-only-the-visible-cells","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/","title":{"rendered":"Select Only the Visible Cells in Excel"},"content":{"rendered":"\n
Select-Only-the-Visible-Cells-in-Excel<\/a>Download File<\/a><\/div>\n\n\n\n

When we work with an Excel spreadsheet, sometimes some columns are becoming too wide to fit on the screen or maybe there are not important at that moment and make unnecessary clutter.<\/p>\n\n\n\n

Example<\/h2>\n\n\n\n

In the following example, there is a list of a few people.<\/p>\n\n\n\n

\"Graphical<\/figure>\n\n\n\n

If you hide one column and select the entire table, use copy, and then paste, the hidden rows or columns will also be copied. It doesn’t matter if you use Ctrl + A<\/strong>, Ctrl + Shift + End<\/strong>, or select values by dragging<\/strong>.<\/p>\n\n\n\n

What you need to do is to select values inside the range and only the visible cells. You can do it using different methods.<\/p>\n\n\n\n

Go To Special<\/h2>\n\n\n\n

Let’s hide column<\/a> C<\/strong> and drag values from A1<\/strong> to D7<\/strong>.<\/p>\n\n\n\n

\"A<\/figure>\n\n\n\n

Now, go to Home >> Editing >> Find & Select >> Go To Special<\/strong>. From the dialog box, select Visible cells<\/a> only<\/strong>.<\/p>\n\n\n\n

\"Graphical<\/figure>\n\n\n\n

Click OK<\/strong>.<\/p>\n\n\n\n

Now, if you look at the selection, the C<\/strong> column is not selected.<\/p>\n\n\n\n

\"A<\/figure>\n\n\n\n

Copy cells and paste them to see that the column was indeed not selected.<\/p>\n\n\n\n

\"A<\/figure>\n\n\n\n

Using Keyboard Shortcut<\/h2>\n\n\n\n

You can quickly select only visible cells using the Left Alt + ;<\/strong> keyboard shortcut for Windows and Cmd + Shift + Z<\/strong> for Mac on selected cells.<\/p>\n\n\n\n

What about filtered rows?<\/h2>\n\n\n\n

If you are dealing with filtered rows that are not visible on the screen, you don’t have to worry about them as they are not selected by default.<\/p>\n\n\n\n

\"A<\/figure>\n\n\n\n

Make a shortcut using Quick Access Toolbar<\/h2>\n\n\n\n

If you don’t want to remember these steps to access the dialog window or memorize the keyboard shortcut, you can create a shortcut on Quick Access Toolbar.<\/p>\n\n\n\n

If your QAT is hidden, you must show it first. To do it, right-click any part of the ribbon and select Show Quick Access Toolbar<\/strong>.<\/p>\n\n\n\n

\"Graphical<\/figure>\n\n\n\n

This is the part that should appear:<\/p>\n\n\n\n

\"Graphical<\/figure>\n\n\n\n

If you can’t see it, look at the top of the ribbon as you can change its position.<\/p>\n\n\n\n

Right-click the QAT and choose Customize Quick Access Toolbar\u2026<\/strong>.<\/p>\n\n\n\n

From Excel Options<\/strong>, select All Commands<\/strong> and then add Select Visible Cells<\/strong>.<\/p>\n\n\n\n

\"Graphical<\/figure>\n\n\n\n

If you click OK<\/strong>, a new icon on the Quick Access Toolbar appears.<\/p>\n\n\n\n

\"A<\/figure>\n\n\n\n

Now, you can select cells, click this button and all the visible cells inside the selected area will be selected.<\/p>\n\n\n\n

Create a macro to select visible cells<\/h2>\n\n\n\n

The problem with adding a shortcut to QAT is that you must select cells first. This time we are going to create a Macro<\/a> that will select only visible cells inside the whole current range with the data.<\/p>\n\n\n\n

Click inside data on the spreadsheet and go to View >> Macros >> Macros >> Record Macro\u2026<\/strong>. You can also create a keyboard shortcut here.<\/p>\n\n\n\n

\"Graphical<\/figure>\n\n\n\n

Press OK<\/strong> to start recording.<\/p>\n\n\n\n

Click Ctrl + A<\/strong> to select the current region and then Left Alt + ;<\/strong> to select only visible cells.<\/p>\n\n\n\n

Sub select_visible()\n'\n' select_visible Macro\n'\n' Keyboard Shortcut: Ctrl+Shift+Q\n'\n    Range(\"A1:D7\").Select\n    Range(\"B5\").Activate\n    Selection.SpecialCells(xlCellTypeVisible).Select\nEnd Sub\n<\/code><\/pre>\n\n\n\n
Run the macro to select the desired range. The problem with this macro is that it works only for this specific example because the exact range is written into the code.<\/p>\n\n\n\n
What we want to do, is to select the current region (the region with an active cell).<\/p>\n\n\n\n
Let’s replace the two first lines on the procedure with the line selecting the current region. The full procedure looks like this:<\/p>\n\n\n\n
Sub select_visible()\n'\n' select_visible Macro\n'\n' Keyboard Shortcut: Ctrl+Shift+Q\n'\n    ActiveCell.CurrentRegion.Select\n    Selection.SpecialCells(xlCellTypeVisible).Select\nEnd Sub\n<\/code><\/pre>\n\n\n\n
You can also add this Macro as a shortcut to QAT.<\/p>\n\n\n\n
This time instead of selecting All Commands<\/strong>, select Macros<\/strong>.<\/p>\n\n\n\n
\"Graphical<\/figure>\n\n\n\n
Macros added to a toolbar have long and not the best-looking name, also the icon is the standard Macro icon.<\/p>\n\n\n\n
\"Graphical<\/figure>\n\n\n\n
We can easily change that by clicking the Modify\u2026<\/strong> button. Here you can change the name and icon.<\/p>\n\n\n\n
\"Whiteboard\n\nDescription<\/figure>\n\n\n\n
Click OK<\/strong> and the shortcut will appear on the Quick Access Toolbar<\/strong>.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Click a range of data with hidden rows or columns and notice that the whole region is selected with only visible data.<\/p>\n\n\n\n
\"Table\n\nDescription<\/figure>\n","protected":false},"excerpt":{"rendered":"
When we work with an Excel spreadsheet, sometimes some columns are becoming too wide to fit on the screen or maybe there are…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nSelect Only the Visible Cells in Excel<\/title>\n<meta name=\"description\" content=\"Learn how to select only visible cells in Excel. Avoid hidden rows\/columns when copying data. Use Go To Special and other methods. Download the file.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Select Only the Visible Cells in Excel\" \/>\n<meta property=\"og:description\" content=\"Learn how to select only visible cells in Excel. Avoid hidden rows\/columns when copying data. Use Go To Special and other methods. Download the file.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-04-04T14:12:15+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-28T13:49:29+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/04\/graphical-user-interface-application-table-exce-2.png\" \/>\n<meta name=\"author\" content=\"Tomasz Decker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tomasz Decker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"Select Only the Visible Cells in Excel\",\"datePublished\":\"2022-04-04T14:12:15+00:00\",\"dateModified\":\"2024-03-28T13:49:29+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/\"},\"wordCount\":647,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/04\/graphical-user-interface-application-table-exce-2.png\",\"keywords\":[\"file\",\"pinterest\"],\"articleSection\":[\"examples\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/\",\"url\":\"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/\",\"name\":\"Select Only the Visible Cells in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/04\/graphical-user-interface-application-table-exce-2.png\",\"datePublished\":\"2022-04-04T14:12:15+00:00\",\"dateModified\":\"2024-03-28T13:49:29+00:00\",\"description\":\"Learn how to select only visible cells in Excel. Avoid hidden rows\/columns when copying data. Use Go To Special and other methods. Download the file.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/04\/graphical-user-interface-application-table-exce-2.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/04\/graphical-user-interface-application-table-exce-2.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Select Only the Visible Cells in Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Select Only the Visible Cells in Excel","description":"Learn how to select only visible cells in Excel. Avoid hidden rows\/columns when copying data. Use Go To Special and other methods. Download the file.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/","og_locale":"en_US","og_type":"article","og_title":"Select Only the Visible Cells in Excel","og_description":"Learn how to select only visible cells in Excel. Avoid hidden rows\/columns when copying data. Use Go To Special and other methods. Download the file.","og_url":"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/","article_published_time":"2022-04-04T14:12:15+00:00","article_modified_time":"2024-03-28T13:49:29+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/04\/graphical-user-interface-application-table-exce-2.png"}],"author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"Select Only the Visible Cells in Excel","datePublished":"2022-04-04T14:12:15+00:00","dateModified":"2024-03-28T13:49:29+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/"},"wordCount":647,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/04\/graphical-user-interface-application-table-exce-2.png","keywords":["file","pinterest"],"articleSection":["examples"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/","url":"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/","name":"Select Only the Visible Cells in Excel","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/04\/graphical-user-interface-application-table-exce-2.png","datePublished":"2022-04-04T14:12:15+00:00","dateModified":"2024-03-28T13:49:29+00:00","description":"Learn how to select only visible cells in Excel. Avoid hidden rows\/columns when copying data. Use Go To Special and other methods. Download the file.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/04\/graphical-user-interface-application-table-exce-2.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2022\/04\/graphical-user-interface-application-table-exce-2.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/examples\/select-only-the-visible-cells\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Select Only the Visible Cells in Excel"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/9299"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=9299"}],"version-history":[{"count":7,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/9299\/revisions"}],"predecessor-version":[{"id":14714,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/9299\/revisions\/14714"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=9299"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=9299"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=9299"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}