Due to a strange accounting error, I had to reduce 7 invoices by 80%. I had filtered to those 7 invoices. Copied 0.2 to the clipboard. I was about to Paste Special Multiply, when I wondered if it would work correctly. Episode 1900 shows you the result.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1900.
Paste Special Multiply Into Filtered Results.
Hey, welcome back to the MrExcel netcast.
Today's question was sent in by me, that's right.
I was doing some accounting work today and I realized that there was a huge error there, was a five times overstatement on all of the Category B items.
Category B, and so I came out here to do 0.2, I was going to Paste Special, Multiply and I said: all right let's go to category B, select category B, filter by selection, Ctrl+C to copy and then I was going to edit, Paste Special, Multiply.
Now here is the big question: if I would have copied these records and gone to a new worksheet, what would have happened?
I would have gotten just the visible cells.
But what happens, when I Paste Special, Multiply into those filtered records?
Is it going to do just the 4 cells that I see, or is it going to do something really bad to everything from row 3 to row 13.
All right, take your vote, right now.
Let's see what happens, click OK.
Alright, so those ones that I see are reduced from 100 to 20, that's great, but let's clear the filter.
And hell, oh my lord, everything from row 3 down to row 13 is wiped out or at least reduced.
Now in this case it was 7 invoices out of a thousand invoices and I had created the filter using an advanced filter, and just as I was about to do that Paste Special, Multiplies in… Hmm, I better go check this on a small spreadsheet.
And sure enough I'm glad I did, because what a disaster that would have been.
Now here is what you would do, if you really wanted to Paste Special, Multiply into the filtered results.
First apply the filter, I'm using filter by selection there, that's the auto filter icon on the quick access toolbar.
I'll come out to the right-hand side, 0.2, copy that 0.2 to the clipboard, that's great.
But then, right here, after I select those cells, I'm going to do Alt+;, Alt+; which is the same as going HOME, Find & Select, Go To Special, Visible cells only.
Alt+; is a shortcut for all of that.
And now when I do Paste Special, All, choose Multiply and OK – exact same result that I saw before.
All of the visible cells went from 100 to 20, but when I clear the filter.
DATA, Clear filter, you see that the other things, the category D and A, and C that happened to be between the first B and the last B, are left alone.
What a subtle, subtle difference that is.
The Alt+; ending up with the right result and not doing some Alt+; ending up with horrible, horrible, bad results.
So lucky for me, I was smart enough to think about that before I did it.
And I thought I'd pass that along in case you ever have a similar situation.
Well hey, I want to thank you for stopping by, see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode 1900.
Paste Special Multiply Into Filtered Results.
Hey, welcome back to the MrExcel netcast.
Today's question was sent in by me, that's right.
I was doing some accounting work today and I realized that there was a huge error there, was a five times overstatement on all of the Category B items.
Category B, and so I came out here to do 0.2, I was going to Paste Special, Multiply and I said: all right let's go to category B, select category B, filter by selection, Ctrl+C to copy and then I was going to edit, Paste Special, Multiply.
Now here is the big question: if I would have copied these records and gone to a new worksheet, what would have happened?
I would have gotten just the visible cells.
But what happens, when I Paste Special, Multiply into those filtered records?
Is it going to do just the 4 cells that I see, or is it going to do something really bad to everything from row 3 to row 13.
All right, take your vote, right now.
Let's see what happens, click OK.
Alright, so those ones that I see are reduced from 100 to 20, that's great, but let's clear the filter.
And hell, oh my lord, everything from row 3 down to row 13 is wiped out or at least reduced.
Now in this case it was 7 invoices out of a thousand invoices and I had created the filter using an advanced filter, and just as I was about to do that Paste Special, Multiplies in… Hmm, I better go check this on a small spreadsheet.
And sure enough I'm glad I did, because what a disaster that would have been.
Now here is what you would do, if you really wanted to Paste Special, Multiply into the filtered results.
First apply the filter, I'm using filter by selection there, that's the auto filter icon on the quick access toolbar.
I'll come out to the right-hand side, 0.2, copy that 0.2 to the clipboard, that's great.
But then, right here, after I select those cells, I'm going to do Alt+;, Alt+; which is the same as going HOME, Find & Select, Go To Special, Visible cells only.
Alt+; is a shortcut for all of that.
And now when I do Paste Special, All, choose Multiply and OK – exact same result that I saw before.
All of the visible cells went from 100 to 20, but when I clear the filter.
DATA, Clear filter, you see that the other things, the category D and A, and C that happened to be between the first B and the last B, are left alone.
What a subtle, subtle difference that is.
The Alt+; ending up with the right result and not doing some Alt+; ending up with horrible, horrible, bad results.
So lucky for me, I was smart enough to think about that before I did it.
And I thought I'd pass that along in case you ever have a similar situation.
Well hey, I want to thank you for stopping by, see you next time for another netcast from MrExcel.
