fbpx
Search
Close this search box.

Copy & paste visible cells only [Excel Trick]

Share

Facebook
Twitter
LinkedIn

Here is something annoying with Excel.

Open any Excel file with few columns of data. Hide some of those columns (select the columns and press CTRL+0). Now, copy a few rows of data. Paste it else where. Excel will paste the values in hidden columns too. We thought Excel would omit the values in hidden columns.

paste-includes-values-in-hidden-columns-too

 

What the filter Excel?!? I thought we were friends, but you annoy me with some of these quirks.

 

How to copy and paste just visible cells only?

There are two ways to do this.

1. Select visible cells, Copy and Paste

  1. Select the entire range you need to copy.
  2. Press F5, this opens Go to dialog. Click on Special button.
  3. Now select visible cells option. You can use the short cut sequence – F5 ALT SY to do all this.
  4. Now press CTRL + C
  5. Then go to target sheet and press CTRL + V to paste.

While this method works, it is too long and if you need to copy and paste several times, then it can be annoying.

Related: More about Go to special

This is where the second trick comes handy.

2. Cheat Excel in to copying just visible cells

If your source data is filtered, then when you copy, Excel copies visible cells only. But how to filter when you need to see all the data. Simple, we cheat.

  1. Go the end of your data and type something like “X” in the blank cell beneath.
  2. Now apply filters to the data. Press CTRL+Shift+L to do this.
  3. Then filter the new value you added in step 1.
  4. Copy now.
  5. When you paste, Excel will paste just visible cells. Viola!!!

copy-paste-visible-cells-only-trick

 

How do you copy paste visible cells?

We are almost at the end. Let me confess. I never had this problem. I know about Go to Special > Visible cells and use it if needed. But Jo (my wife) faced this issued yesterday and called me to ask how to copy visible cells. When I told her the option about visible cells thru Goto special, she is not all that thrilled, so I got thinking. I think the filter approach is better because you just do what you normally do – which is Ctrl C, Ctrl V.

What about you? Have you ever had to paste visible cells only? How did you deal with that? Share you tips and experience in comments section.

And oh yeah, Check out our quick tips section for some knock-your-socks-off level of awesomeness.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

20 Responses to “Copy & paste visible cells only [Excel Trick]”

  1. select all the cells.
    press alt+semi colon (;).
    only visible cells will be selected.
    copy.
    paste elsewhere.
    voila - only visible cells are pasted.

  2. Aquayum says:

    I think, the "x" option at the bottom is a really cool cheat option that I wasn't aware of, although I normally use the Alt semi Colon option which Nishkarsh mentioned above.

  3. B says:

    Alt + HFDSY all day

  4. Jason says:

    Is there a way to copy and paste visible cell values?

    • Salahuddin says:

      Assuming you are referring to spreadsheet which has formula and it derives values using those formula. And, you just want to copy the values and not the formula. In this case, select your range, then press ALT+ (Semicolon) together, this will select visible cells only. then go to the sheet/cell where you want to paste the data, there, instead directly pasting the data, perform paste special values. there are many ways to do that, easiest one is press ALT+Cntrl+V together, this will show a pop up window, select Values and press ok (or simply type v and enter). this should do 🙂

  5. DME says:

    I agree with Nishkarsh, alt + semi-colon is a great time saver for a task like this!

  6. Lyn says:

    Hi I just learnt yesterday that if you Use ALT + ; it will select only visible cells. I am new to excel 2016 and finding it really hard to find my way around all the ribbons.

  7. Akhter says:

    This was very informative & i usually do this by using ctrl+G 🙂

  8. Nicolas says:

    I daily copy/paste visible cells from filtered sheets to another (master) sheet. I do it with a macro because copying visible cells is quite easy, however I never achieved finding an excel function to paste onto visible cells.

    • Ella says:

      Do you still have this macro? I'm looking for something like this to help with a monthly spreadsheet where I spend a lot of time copying/pasting from filtered/visible cells on one spreadsheet into filtered/visible cells onto another spreadsheet.

  9. GraH says:

    This is really one of the mysteries of Excel to me. I never had to do one of the above tricks. For ages I can just go and select visible cells and paste them elsewhere, completely hassle-free.
    I only learnt last year that this is not the standard Excel behaviour and an eye-opener to me, why with macros I always need to write that stuff with SpecialCells in the syntax.
    But it makes no sense to me why most of my colleagues can copy visible cells only, like I do and why only a minority cannot. They experience the stuff explained in the post. Though we all are using the same Office installation.
    I've been looking for a setting somewhere, but I haven't found it.

  10. Alec McQueen says:

    I am confused guys... there's a specific function icon you can use to do exactly this. I have it in my quick access menu at the top of excel. You just highlight what you want to copy, click the icon, and this copies only visible cells. No need for macros or shortcuts or trickery. Just exactly what you want with a single extra click on an icon.
    Cheers
    alec

  11. Chandoo says:

    @Alec, Nishkarsh, DME & Salahuddin: Thanks for sharing more ways to deal with this problem. I have written a follow up article explaining this. 🙂

  12. Rishi Kumar says:

    use Alt + ; for selecting visible cells only

  13. Bulakorn says:

    I'm use X cheat
    It's work !!!!!!!

  14. chin says:

    what if i have a formula in some visible cells(where the data is filtered) and i want to paste special on the cells it self. i always want to take out the filter and then select the entire columns where i want to paste special. is there any way so that you can paste speial without taking out the filter

Leave a Reply