Your company system produces a data export where all of the numbers in a column appear as negative instead of positive. Episode 355 will show you a trick with Paste Special Multiply to correct this problem.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel podcast, I'm Bill Jelen.
A question that came up in one of the power Excel seminars last week is: Someone pulls in data from their company system every week, and this column needs to be completely negative, but the system sends it over as positive.
Is there some way to quickly change that entire column to be negative, other than, of course, creating a new temporary column, and then using Paste Special Values to go back?
The simple solution is: just go to a blank cell somewhere that's not being used, and putting the number -1.
With the -1 selected, use Edit, Copy or Ctrl+C, to copy the -1 to the clipboard, and then select your data, the entire column of data, and use Edit, Paste, Special.
And from the Operation tab choose Multiply, that will multiply all of the selected cells by whatever's on the clipboard, which in this case happens to be a -1.
So as not to change your formatting, go ahead and choose Values here, click OK, and Excel will very quickly multiply all of the numbers in the column to be negative.
There you have it, using Paste Special Multiply to quickly change a column from negative to positive.
Thanks for stopping by, we'll see you next time for another podcast from MrExcel!
A question that came up in one of the power Excel seminars last week is: Someone pulls in data from their company system every week, and this column needs to be completely negative, but the system sends it over as positive.
Is there some way to quickly change that entire column to be negative, other than, of course, creating a new temporary column, and then using Paste Special Values to go back?
The simple solution is: just go to a blank cell somewhere that's not being used, and putting the number -1.
With the -1 selected, use Edit, Copy or Ctrl+C, to copy the -1 to the clipboard, and then select your data, the entire column of data, and use Edit, Paste, Special.
And from the Operation tab choose Multiply, that will multiply all of the selected cells by whatever's on the clipboard, which in this case happens to be a -1.
So as not to change your formatting, go ahead and choose Values here, click OK, and Excel will very quickly multiply all of the numbers in the column to be negative.
There you have it, using Paste Special Multiply to quickly change a column from negative to positive.
Thanks for stopping by, we'll see you next time for another podcast from MrExcel!
