244

How To Copy & Paste Visible Cells Only

Have you ever tried to copy and paste a range with hidden rows or columns and didn't get the results you expected?

Copy Paste Select Visible Cells in Excel

This problem can be frustrating and time consuming!  In this post and video I will explain how to solve this problem with a very simple shortcut.  The shortcut will select the visible cells only in a range, ignoring the hidden rows and columns.

Video

Please leave a comment below with any questions.

Why Are My Hidden Rows & Columns Being Pasted?

When you copy a range of cells that contains hidden rows or columns, Excel includes ALL of the selected rows.  It does not matter if they are visible or hidden.  When you go to paste the range, all the cells in the selection will be pasted.

This is actually a nice feature, but it's not always what we are expecting to happen.  Sometimes we only want to copy and paste the visible cells, and exclude the hidden rows & columns.

This commonly happens when you are working with a range that has filters applied to it.  Or when you have hidden rows or columns from a collapsed group in an outline.  The Subtotals feature automatically creates row groupings that can cause rows to be hidden, as shown in the example above.

Select the Visible Cells Only

Select Visible Cells Only in Excel

Excel gives us an option to select the visible cells only using the keyboard shortcut Alt+; (hold down the Alt key, then press the semi-colon key).  The Mac shortcut is Cmd+Shift+Z.

This shortcut will exclude all the hidden rows and columns from the selection.

Copy & Paste Visible Cells

Here are instructions on how to copy and paste visible cells only (please see the video above for details):

  1. Select the entire range you want to copy.
  2. Press Alt+; to select the visible cells only.  You will notice that the selection is cut up to skip the hidden rows and columns.
  3. Copy the range – Press Ctrl+C or Right-click>Copy
  4. Select the cell or range that you want to paste to
  5. Paste the range – Press Ctrl+V or Right-click>Paste

Alternatives to Alt+;

If you don't use this shortcut often then it might be hard to remember the keyboard shortcut.  Here are a few alternatives for selecting visible cells.

Add a Button to the Quick Access Toolbar (QAT)

Select Visible Cells in the Quick Access Toolbar

Please see the video for step-by-step instructions on how to add this button to the QAT.  Pressing the button will select visible cells only.  It also shows the keyboard shortcut when you hover your mouse over the button.

The Go To Special Menu

You can also find the Select Visible Cells Only option on the Go To Special menu.

Go To Special Menu in Excel Visible Cells Only

This can be a very useful menu for selecting other types of cells besides just visible cells.  As you can see in the image above there are a lot of options for different types of cells you can select.

There are a few different ways to open the Go To Special menu.

1. On the Home Tab of the Ribbon, press the Find & Select button on the far right side.  Then choose Go To Special…

Find and Select GoTo Special Menu

2. Press Ctrl+G or the F5 key on the keyboard from anywhere in a worksheet.  Then press the Special button.

Go To Menu in Excel - Special Button

So Many Different Ways to Select Visible Cells

As you can see, there are a lot of different ways to select the visible cells in Excel.  For me, the keyboard shortcut (Alt+;) is by far the fastest.  If you don't think you'll remember it then put the button on the QAT. 🙂

Either way, this is one shortcut that should help save you a lot of time!

Paste to the Visible Cells Only

If you have ever received the following error message in Excel, “The command cannot be used on multiple selections”, then you know that you cannot paste to the visible cells in range that contains hidden rows or columns.  This is a limitation of Excel, and it can be frustrating.

Paste Visible Cells That Command Cannot Be Used on Multiple Selections Error in Excel

The video below explains two workarounds for this.

Click the following link to learn more about the Paste Visible feature and download a free trial of the Paste Buddy add-in.

Click here to get Paste Buddy

Copy Visible Cells to Outlook Email

Mandy asked a great question in the comments below about how to paste the visible cells to an email in Outlook.  The following screencast animation shows how to do this.

Copy Visible Cells to Outlook Email

Here are the steps to copy the visible cells to an email :

  1. Select visible cells in Excel – Alt+;
  2. Copy the range – Ctrl+C
  3. Open an email in Outlook
  4. Choose from the Paste Options on the Message tab

The Paste button on the Message tab of the Ribbon contains a small drop-down arrow underneath it.  Click this arrow to see the Paste Options.  You can hover over each of the options to get a preview of what the paste will look like in the email.

When copying and pasting from Excel to Outlook, I usually use either the “Picture” or “Keep Text Only” options.  If I know the user will be copying the data out of Outlook, then it's best to give them the text.  If I know the user will only be looking at the data, then I will paste a picture because I know the formatting will remain in tact.

Please leave a comment below with any questions.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 244 comments
J - October 12, 2018

How to copy paste hidden cells on hidden cells (only values) -i´m looking for quick way to add running numbers to a table that includes hidden cells and I want to paste the runnig numbers to specific rows (when there are hidden cells).

Reply
Miranda Van Volkenburg - September 18, 2018

Thank you!

Reply
    Rosy Geraldo - October 6, 2018

    Its superb as your other posts : D, regards for posting .

    Reply
April - August 10, 2018

Hello John, I followed the above instruction and all worked brilliantly. Except when I attempted to pasted those into the range I needed,it only pasted 6 of 54 numbers in random order. I REALLY don’t want to have to copy and paste each cell individually
Thank you kindly, April

Reply
Judi Galbreath - June 19, 2018

I would like to sign up for your offer. I have a question though. It pastes a lot fewer values in my pasted column than there are in my column where I selected and copied the visible values only. Why?

Reply
    Jon Acampora - June 20, 2018

    Hi Judi,

    I’m not sure I understand your question. It might be easier to see an example. Are you pasting to a range or sheet that does NOT have hidden or filtered rows? If so, then it might be that all the rows in your filtered range were not selected before selecting visible and copying. I hope that helps.

    Reply
RAJESH - June 12, 2018

Great Tip – Helped me in saving the time of using excel. Thanks

Reply
Kirsten - June 11, 2018

Great tip- thank you. I’d like to subscribe to your offer.
Appreciate it.

Reply

Leave a Reply: