This sounds silly, I know. To achieve what is stated in the above scenario, what we need to do is simply
- Select “January to March” from the slicer
- Copy the range C4:C9
- 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:
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:
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
Side-Story
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:
- A mistake can lead you to interesting finding (sometimes)
- Some stupid works are inevitable. But we can do it in a smarter way. 🙂
wmfexcel 