Disable formulas in multiple cells at once

[AmericanSeiko]

Hi everyone,

I was wondering if there is a way to select a range of cells and turn off their formulas.

For example,

=average(range1)
=average(range2)

might become

'=average(range1)
'=average(range2)

without going into each cell and manually inputting '

Is there a way to do this efficiently?

Thanks for the help!

- AS

 

[mmmarks]

Hi,
If I understand your requirement. try this Hit Ctrl+~ at a time

 

[dispelthemyth]

Try the below

It goes cell by cell to change the values,

Sub ReplaceFormulas()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Dim RCell As Range
For Each RCell In Selection


    RCell.Formula = "'" & RCell.Formula


Next RCell


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
    
    End Sub

 

[Jonmo1]

You can use the Find/Replace tool..

Find What: =
Replace With: '=

 

[AmericanSeiko]

@mmmarks

Pretty cool hotkey! I tried it out, and it looks like it disables all formulas on the spreadsheet, whereas I was hoping to disable a selection. Are there any hotkeys that do that?

Thanks for the reply!

 

[AmericanSeiko]

@dispelthemyth

Nice macro, that looks like that will do exactly what I want! I'm assuming there is no way to do it without a macro?

 

[AmericanSeiko]

@Jonmo1

Good idea! However I was hoping to only disable a selection, it looks like your approach would go through all my formulas.

Thanks!

 

[Jonmo1]

@Jonmo1
it looks like your approach would go through all my formulas.

It will do it just within the range you have selected.