Convert Formulas to Values Fast. A

[DennisFromBoulder]

Convert Formulas to Values Fast.
Avoid the lengthy Copy/Paste Special sequence when you need to turn a range of formulas into their results:

This is probably the fastest way:
1. Select a cell range containing formulas
2. Right-drag a border a short distance until the tip indicator displays a different range than the selected range -- then drag right back on top of the original range and release the mouse button.
3. Select Copy Here as Values Only

 

[KenWright]

Or you can use a short VBA routine and just assign to a keyboard combo as shortcut:-

Sub PasteValues()

Application.ScreenUpdating = False

With Selection
.Copy
.PasteSpecial Paste:=xlPasteValues
End With

Application.CutCopyMode = False
Application.ScreenUpdating = False

End Sub

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

Personally, I just have a pastespecial Values keyboard shortcut setup for Ctrl+z

so I can just go

Ctrl+c Ctrl+z
or Ctrl+x Ctrl+z

Not sure how you can get much quicker than that....
Note to Ken - I'm not saying your isn't faster but it only pastes in the same place - I do a lot of copying formulae from one place and pasting values into another so this setup works for me

Dennis - nice tip tho

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[GlennUK]

Another useful method is by using toolbar buttons.

Customize the toolbars and drag the "Paste Values" toolbar button next to the "Copy" button.

When you need to convert formulae to values simply select the required area of the spreadsheet that needs converting and click the "Copy" button and then click the "Paste Values" button.

Cheers, Glenn.

 

[KenWright]

Same as you Geoff, I have one for each - Was just replying to Dennis's note for fixing in situ. I do a lot of both and it always drove me nuts to have to use menus or the icon for Paste Special Values. Weird thing is though that I'm not consistent - I prefer icons for some things and shortcuts for others - Guess it's just habits you get into.

Like you said though, Nice tip from Dennis, and it's always worth reitarating them now and then, as many people will never have seen it. Whilst we're on the subject, anyone that hasn't seen it might also like the similar right click options you get if instead of dragging rows/columns via their headers with the left mouse button, you drag it with the right. Once you let go you are presented with a wealth of options for moving/copying/shifting etc.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[xlbo]

'Specially useful for filling dates - there's a fil Weekdays, fill Months, fill Years option at least - as well as a coupla others from what I recall

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244