easiest way of turning a sheet into values only ?

[DBenz]

Hi,



Excel 2010,
I need to convert sheets to values only, what is the quickest way of doing that retaining the formatting ?


At the moment I am creating a new sheet beside a source sheet then name it with suffix nf then select corner of source , select copy, then corner of new and copy paste >values to it then format it same as previous sheet.


There has to be an easier way I hope.



DBenz

[Pecoflyer]

There is a way, it needs some practice
- Select the range

- Move the mouse to the upper limit of the selection, the arrow becomes a cross
- While keeping the right mouse button down, you can move the range around
- Move it to where you need it ( it can be the same place as long as you moved the range around a bit)
- Release the mouse button
-From the menu that pops up select " copy here as values only"


Does that help ?

[DBenz]

Hi, Tried that just dont get to see a cross at all.


also tried edit cut then click in top left of selection expecting to see paste values with source formatting but alas that option isnt there.



DBenz

[ArviLaanemets]

When you don't need formulas anymore on old location, then copy, and then Paste Values into same range.

When you want to keep formulas in old sheet, then right-click on sheet tab, Select Move or Copy, check Create a copy checkbox, and click OK. After that, copy all data in new worksheet, and use Past Special to write the datarange over with values.

Not much faster, but you don't have to format the sheet, and to set column widths.