How to Copy and Paste Only Visible Cells in an Excel Worksheet

Jobs have been hard to come by these days for inexperienced accounting staff among others. The other day a friend of mine who is looking for a job told me that during interview for a junior accountant job he was asked on different occasions to take a basic Microsoft excel skills test. One of his challenges was how to filter data and then copy and transfer the data to another excel worksheet with only the filtered information. But every time he copied and pasted the data in another worksheet, the result was that all the data pasted was the original unfiltered data. This was very frustrating to him given that time was of essence! Does this sound familiar that at one time you knew what you needed to accomplish but did not know how? This time I will explain in plain English supported with an example of how to copy filtered data from one excel sheet to another using the ‘Go To Special Excel’ tool, a very powerful tool.

‘Go To Special’ Excel tool can be used in a variety of ways that may include copying and pasting only wanted piece of data, eliminating blanks among other uses. Assume you download from the company financial information system a trial balance report but you realize that after each account details information, there is blank space between each set of data. See below for a specimen of the downloaded report:

Account # Account Description Amount
11000 Travel Expense $433,221
     
11001 Training Expense $3,456
     
11003 Meals Expense $6,458,654
     
11004 Hotel & Lodging Expense $5,463
     
11005 Medical Expense $876,554
     
11008 Salaries and wanges $346

You realize that the data runs several pages and the only option you have is to delete each unwanted space manually. Unfortunately time is not on your side. So you wonder what to do next. Worry no more!

If you are using Microsoft Excel 2007 version then follow the following steps:

  1. Select the entire table
  2. Go ‘Data’ tab
  3. Click ‘Filter’
  4. Click the  ‘filter arrow’ under the account number
  5. Uncheck ‘blanks’
  6. Select the entire table again
  7. Under the home tab go to ‘Find & Select’  menu at the far right corner
  8. Select ‘Go to Special’ menu
  9. A ‘Go to special’ window opens
  10. Check ‘visible cells only’
  11. Click  ‘ok’
  12. Copy all the table again
  13. Paste the table to your destination worksheet. This command copies only the visible cells and not any hidden cells. See results below.
Account # Account Description Amount
11000 Travel Expense $433,221
11001 Training Expense $3,456
11003 Meals Expense $6,458,654
11004 Hotel & Lodging Expense $5,463
11005 Medical Expense $876,554
11008 Salaries and wages $346
Share
This entry was posted in Microsoft Excel Simplified. Bookmark the permalink.

32 Responses to How to Copy and Paste Only Visible Cells in an Excel Worksheet

  1. buyecig says:

    I love your esolutions-cpas-online.com
    electronic cigarette

  2. betathome says:

    I like esolutions-cpas-online.com, bookmarked
    bet and home

  3. I liked up to you’ll receive performed proper here. The comic strip is attractive, your authored subject matter stylish. nevertheless, you command get bought an nervousness over that you would like be handing over the following. in poor health surely come further formerly again as exactly the same nearly a lot regularly within case you shield this increase.

  4. Rex Ryan says:

    Very nice post. I just stumbled upon your weblog and wished to say that I have really enjoyed browsing your blog posts. In any case I will be subscribing to your feed and I hope you write again very soon!

  5. Some really wonderful posts on this internet site , thankyou for contribution.

  6. Wow, amazing blog layout! How long have you been blogging for? you make blogging look easy. The overall look of your web site is fantastic, let alone the content!

  7. esolutions-cpas-online.com is bookmarked for future reference!
    betathome

  8. Very fantastic info can be found on web blog . “Wealth may be an ancient thing, for it means power, it means leisure, it means liberty.” by James Russell Lowell.

  9. Would you be all in favour of exchanging hyperlinks?

  10. I think this website has some very good information for everyone. “The best friend is the man who in wishing me well wishes it for my sake.” by Aristotle.

  11. Its great as your other content : D, appreciate it for putting up. “In the spider-web of facts, many a truth is strangled.” by Paul Eldridge.

  12. Hiya very nice web site!! Guy .. Beautiful .. Superb .. I’ll bookmark your website and take the feeds additionally…I’m happy to seek out so many useful info here within the submit, we need develop extra strategies on this regard, thanks for sharing.

  13. You are my inspiration , I own few web logs and infrequently run out from to post .

  14. I was studying some of your articles on this website and I conceive this internet site is very informative ! Continue putting up.

  15. Hi there very cool website!! Guy .. Beautiful .. Wonderful .. I will bookmark your site and take the feeds also…I’m happy to seek out numerous helpful information right here in the post, we need develop more strategies in this regard, thank you for sharing.

  16. You are my breathing in, I possess few blogs and very sporadically run out from brand :). “The soul that is within me no man can degrade.” by Frederick Douglas.

  17. Hello, a brilliant info man. Thnx Unfortunately I’m experiencing trouble with your rss . Unable to subscribe to it. So anyone having same rss feed issue? Anyone who knows please respond. Thnkx

  18. You’re not the common blog writer, man. You certainly have something important to contribute to the net. Such a good blog. I will revisit again for more.

  19. I was reading some of your articles on this website and I think this web site is rattling instructive! Continue putting up.

  20. I was examining some of your posts on this website and I believe this web site is really instructive! Retain posting.

  21. Merely wanna input that you have a very decent site, I love the style and design it actually stands out.

  22. nike shoes says:

    Dead indited subject matter, Really enjoyed reading through.

  23. acai berry says:

    I was looking through some of your blog posts on this website and I think this website is real instructive! Keep on putting up.

  24. Thnx so much for this! I haven’t been this thrilled by a blog for a long time! You’ve got it, whatever that means in blogging. Anyway, You’re certainly somebody that has something to say that people need to hear. Keep up the outstanding work. Keep on inspiring the people!

  25. An attention-grabbing discussion is price comment. I believe that it’s best to write more on this matter, it may not be a taboo topic but usually persons are not sufficient to talk on such topics. To the next. Cheers

  26. girl bags says:

    I your writing style truly loving this internet site. “You simply *must* stop taking advice from other people.” by Melissa Timberman.

  27. I believe this website has got very excellent composed content content.

  28. canada atvs says:

    Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.

  29. Perfectly pent content material, Really enjoyed reading through.

  30. I saw a lot of website but I conceive this one has something special in it. “I have only one superstition. I touch all the bases when I hit a home run.” by Babe Ruth.

  31. MUNEER says:

    dear sir
    Very nice post. I just stumbled upon your weblog and wished to say that I have really enjoyed browsing your blog posts. In any case I will be subscribing to your feed and I hope you write again very soon!

Leave a Reply to MUNEER Cancel reply

Your email address will not be published. Required fields are marked *