Interesting behavior of Copy and Paste

Excel Tip - Interesting behavior of Copy and Paste

This sounds silly, I know.  To achieve what is stated in the above scenario, what we need to do is simply

  1. Select “January to March” from the slicer
  2. Copy the range C4:C9
  3. Paste to C13

Then repeat the above steps with different slicer selection and then Copy and then Paste.

In short, we need to perform Copy and Paste three times.  The following demonstrates this typical approach:

Excel Tip - Interesting behavior of Copy and Paste

Nine steps in total.  No big deal.  And there are no quicker way to do this kind of manual work, except to move the mouse quickly, or to press the shortcut key faster…

If you think so, you should continue to read this post.

You may download a Sample File to follow along.

Copy Once, Paste many times

It is logical to copy the same range of data again and again, because the values get updated every time we change the slicer selection.  Indeed, we need to Copy the range once only, and then paste different values (out of the same range) as many times as we want, although the values keep changing.

Not sure what I am talking about?  Watch this:

Excel Tip - Interesting behavior of Copy and Paste 2

See?! As long as the “Marching Ants” are working, we can paste the “updated” values to destination(s) without “Copy” again, even though the values have changed.

Don’t underestimate the time we can save with this little trick.  I timed the above two scenarios.  This first one took 30s to complete; while the second took less than 17s.  It’s a huge improvement, isn’t it?

Tip: This works across Excel, Word, PowerPoint


Believe it or not, I am required to prepare a weekly highlight report out of an interactive Pivot Table based report I created.  All users can get whatever period they want to look at by selecting the Period slicer.  However they want a “static” report listing WTD, and MTD  for different channels (controlled by another slicer).  Don’t ask me why they don’t simply interact with the Excel report…… it’s a mindset thing that I cannot change easily.  You know…

Because of this, I spend 30 minutes in doing stupid copy and paste every week.

Well, many people are doing copy and paste in office, I know. ;p 

One day, I forgot to “Copy” after I changed the slicer selection…… but to my pleasure surprise, the updated values got pasted correctly.  I thought I was dreaming.  After testing and testing, I realized that as long as the “copy” range is active (denoted by the “Marching ants”), the paste action will return whatever values showed on the range.  Such a time-saver since then. 🙂

Moral of this story:

  1. A mistake can lead you to interesting finding (sometimes)
  2. Some stupid works are inevitable.  But we can do it in a smarter way. 🙂

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.