Mega or Array Formulas

This method is slightly slower, but it's less likely to cause problems with more complicated code.

The code.
Enter the following code by right-clicking on your sheet tab and selecting ViewCode (pre-2007, Tools → Macro → Visual Basic Editor)
or
pressing Alt/Option-F11,
then selecting Insert → Module and pasting this code:

Sub MakeAbsoluteorRelativeSlow( )Dim RdoRange As Range, rCell As RangeDim i As IntegerDim Reply As String'Ask whether Relative or AbsoluteReply = InputBox("Change formulas to?" & Chr(13) & Chr(13) _& "Relative row/Absolute column = 1" & Chr(13) _& "Absolute row/Relative column = 2" & Chr(13) _& "Absolute all = 3" & Chr(13) _& "Relative all = 4", "OzGrid Business Applications")'They cancelledIf Reply = "" Then Exit SubOn Error Resume Next'Set Range variable to formula cells onlySet RdoRange = Selection.SpecialCells(Type:=xlFormulas)'determine the change typeSelect Case ReplyCase 1 'Relative row/Absolute columnFor Each rCell In RdoRangeIf rCell.HasArray ThenIf Len(rCell.FormulaArray) < 255 ThenrCell.FormulaArray = _Application.ConvertFormula _(Formula:=rCell.FormulaArray, _FromReferenceStyle:=xlA1, _ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)End IfElseIf Len(rCell.Formula) < 255 ThenrCell.Formula = _Application.ConvertFormula _(Formula:=rCell.Formula, _FromReferenceStyle:=xlA1, _ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)End IfEnd IfNext rCellCase 2 'Absolute row/Relative columnFor Each rCell In RdoRangeIf rCell.HasArray ThenIf Len(rCell.FormulaArray) < 255 ThenrCell.FormulaArray = _Application.ConvertFormula _(Formula:=rCell.FormulaArray, _FromReferenceStyle:=xlA1, _ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)End IfElseIf Len(rCell.Formula) < 255 ThenrCell.Formula = _Application.ConvertFormula _(Formula:=rCell.Formula, _FromReferenceStyle:=xlA1, _ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)End IfEnd IfNext rCellCase 3 'Absolute allFor Each rCell In RdoRangeIf rCell.HasArray ThenIf Len(rCell.FormulaArray) < 255 ThenrCell.FormulaArray = _Application.ConvertFormula _(Formula:=rCell.FormulaArray, _FromReferenceStyle:=xlA1, _ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)End IfElseIf Len(rCell.Formula) < 255 ThenrCell.Formula = _Application.ConvertFormula _(Formula:=rCell.Formula, _FromReferenceStyle:=xlA1, _ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)End IfEnd IfNext rCellCase 4 'Relative allFor Each rCell In RdoRangeIf rCell.HasArray ThenIf Len(rCell.FormulaArray) < 255 ThenrCell.FormulaArray = _Application.ConvertFormula _(Formula:=rCell.FormulaArray, _FromReferenceStyle:=xlA1, _ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)End IfElseIf Len(rCell.Formula) < 255 ThenrCell.Formula = _Application.ConvertFormula _(Formula:=rCell.Formula, _FromReferenceStyle:=xlA1, _ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)End IfEnd IfNext rCellCase Else 'TypoMsgBox "Change type not recognised!", vbCritical, _"OzGrid Business Applications"End Select'Clear memorySet RdoRange = NothingEnd Sub

Click the top-right X (or press Alt-Q) to get back to Excel proper, and then save your workbook.

Again, to run the code, select the range of cells you want to change, press Alt/Option-F8, select the macro name, and click Run.

If you want to change only one formula, you could toggle through the four reference types a formula can use by selecting the cell that housing the formula, clicking in the Formula bar, then clicking inside the reference part of your formula (e.g., A1, $A$1) and pressing F4. Each press of F4 will toggle the reference type.