« TechSoup for Libraries | Main | Browsers and Responsive Design Modes »

More to Paste Special than Values


I was working with a spreadsheet and needed to convert some numbers from negative to positive.  While I knew I could add another  column with a formula to multiply each of the numbers by -1 and then copy and use paste special: values to paste the results back into the spreadsheet or I could select the column of numbers and do a find and replace to replace the – with nothing, I decided to see if Excel had a quick widget that would do the work for me.

I didn’t find a quick “switch from positive to negative” button but I did find a different way to go about changing the numbers.  

  1. Put -1 in one of the Excel cells.
  2. Copy that cell.
  3. Select the numbers you want to convert.
  4. Right click and choose Paste Special. 
  5. In the middle of the Paste Special window, there’s a section called Operation.  Select Multiply and click on Ok.  Your numbers have now been switched.

I’d seen the Operation choices before but, frankly, I’d ignored them. Until now I’ve always wanted one of the choices from the upper section (Formulas, Values, Formats, etc.) so I’d never really looked at any of the Operation (Add, Subtract, Multiply and Divide) options.

Now while a simple find and replace would have worked for my original problem, something like this could really come in handy for other situations. For example, if I needed to subtract 10% off of a set of numbers, I could put .9 in the cell instead of -1 and I'd get the numbers without having to insert a column and create a formula.


Feed You can follow this conversation by subscribing to the comment feed for this post.

Post a comment