How to remove the cell reference in a sheet at once
 

Hi,

I have a sheet where there are many cells are fix (reference cells)
using the F4 key. Is there any way i can remove the effect of the F4
without going to each formula and them keep pressing F4 to remove the
reference???

Answer: How to remove the cell reference in a sheet at once
 

Yes, there is a way to remove the cell reference in a sheet at once. You can use the "Find and Replace" feature in Excel to replace all the cell references with their actual values.

Here are the steps to do it:

1. Select all the cells that contain formulas with cell references.
2. Press "Ctrl + H" to open the "Find and Replace" dialog box.
3. In the "Find what" field, type the
   Code:

   ---

   $A

   ---

   .
4. Leave the "Replace with" field blank.
5. Click on "Replace All" button.
6. Excel will replace all the cell references with their actual values.

Note: This will replace all the cell references in the selected cells with their actual values. Make sure you have a backup of your original data before doing this, as it will permanently remove the cell references.

How to remove the cell reference in a sheet at once
 

With formulas like =$A$1+$B$1 and pulled down for 30 cells, I used Edit
Find $ replace with "nothing" OK

HTH
Regards,
Howard

"The Greek" wrote in message
...
Hi,

I have a sheet where there are many cells are fix (reference cells)
using the F4 key. Is there any way i can remove the effect of the F4
without going to each formula and them keep pressing F4 to remove the
reference???

How to remove the cell reference in a sheet at once
 

Stp1- Select range where you want to replace fixed references with relative
references.

Stp2- Press Ctrl + H, this will show Replace dialogue box.

Stp3- In "Find" space, type single $

Stp4- Leave "Replace" space empty

Stp5- Click "replace all".

You are done.. chk it.

Regards,
Pritesh


"The Greek" wrote:

Hi,

I have a sheet where there are many cells are fix (reference cells)
using the F4 key. Is there any way i can remove the effect of the F4
without going to each formula and them keep pressing F4 to remove the
reference???
.

select the content and use Cltr + H (find and replace for $).

note: please make sure it effect only selected cells not the entire spreadsheet.

all the best

How to remove the cell reference in a sheet at once
 

You can use the editreplace as suggested.

Or use a macro to make changes in selected cells.

Sub Absolute()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

Sub AbsoluteRow()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlAbsRowRelColumn)
End If
Next
End Sub

Sub AbsoluteCol()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub

Sub Relative()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelative)
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 24 May 2010 22:12:03 -0700 (PDT), The Greek
wrote:

Hi,

I have a sheet where there are many cells are fix (reference cells)
using the F4 key. Is there any way i can remove the effect of the F4
without going to each formula and them keep pressing F4 to remove the
reference???