help with excel 'paste special'

January 18, 2008 10:41 AM

excel n00b question: why is paste special not working for me?

i'm using excel 2007 for vista. i have a big 'ole column of values that are positive and i want them to be negative. my thought was to put -1 in a cell and then copy that and paste special-multiply to make all the positives into negatives.

but that gets me nothing. nothing at all. just a couple of selected cells and no change. i also tried with selecting the values option in paste special, the all option, and the transpose option in various combinations and still got nothing...

so i tried inserting a new column besides my first column and putting in a formula to muliply a cell in the first column times -1 (then i would just copy it down the row) but then it gives me a #VALUE! error message and says that 'A value used in this formula is of the wrong data type.' wtf? i specifically changed the format of all the value cells to 'number'.

i feel like i'm missing something obvious here. any thoughts?

posted by emmatwofour to Computers & Internet (5 answers total)

I wouldn't do "times -1", I would make a column that's zero minus the value.

Then once I had that, I would copy the column and then paste special "as values" wherever I wanted the column.
posted by bcwinters at 10:50 AM on January 18, 2008

I have had that problem from time to time. In my case, Excel kept treating the numbers as if they were text, even though I format and reformat the cells as numbers. This has happened most when the original column of numbers were cut and paste from another application.

As an experiment, retype one of the numbers that's already there and retry the cut and paste special, and see if it works. Try it once by typing over the number already there, and then try it in a new cell.

I was facing retyping all the numbers but I came across a VBA macro that seemed to do the trick.

Alt-F11 > Insert > Macro

enter the following into the module

Sub Enter_Values()
For Each xCell In Selection
xCell.Value = xCell.Value
Next xCell
End Sub

then select the cells you want to convert, then Tools > Macro > Macros and click on Enter_Values to run it.

Backup your original, and try this on a copy of the spreadsheet, just in case. Hope it helps
posted by jasper411 at 11:05 AM on January 18, 2008 [1 favorite]

I've had that same problem, with excel treating the numbers as text when they came from another data source. When you try to change the cell format to number it doesn't work. My go-to solution in that situation has been to do almost exactly what you're trying to do: use paste special to multiply them by 1. Not sure why 1 would work when -1 doesn't, but you could give it a shot.

Oh, if this is your problem, that excel doesn't know they're numbers, you won't be able to add them or do other math on them either. Can you?
posted by yarrow at 11:47 AM on January 18, 2008

As people are saying, it sounds like your values are really text.

In that other column (say it's B, and your data's in A1 down) put in B1:


=-1*VALUE(A1)


and fill down.

(which is just what Jasper's macro does, but avoids VBA)
posted by pompomtom at 1:48 PM on January 18, 2008

Is there a little green mark in the top left corner (possibly right corner, I can't remember at the moment)? If so, highlight a few cells and see if there is a box you can click. There should be an option to "Convert to Number" if they are text cells imported from another data source.

This can be a blessing and a curse when dealing with vlookups.
posted by Octoparrot at 4:51 PM on January 18, 2008