Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Converting from Relative to Absolute.
Written by Allen Wyatt (last updated April 13, 2024)
This tip applies to Excel 97, 2000, 2002, and 2003
Excel allows you to easily edit formulas. In doing so, you can quickly change a cell reference or a range reference from relative to absolute. What if you have a large number of cells in which you need to change from relative to absolute referencing? In this instance, the nature of the problem is well-suited to being solved through a macro.
By using the ConvertFormula method available in VBA, you can easily convert a formula from relative to absolute addressing. The following short macro uses this method to change the addressing method used in a range of cells:
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here:
Sub Relative2Absolute()
For Each c In Selection
If c.HasFormula = True Then
c.Formula = Application.ConvertFormula(c.Formula, _
xlA1, xlA1, xlAbsolute)
End If
Next c
End Sub
The key to how this macro works is, of course, in the ConvertFormula method. The last parameter used by the method is—in this case—xlAbsolute. If you want to adapt the macro so that it changes to other types of addressing, you can change xlAbsolute to xlRelative, xlAbsRowRelColumn, or xlRelRowAbsColumn. (I'm sure you can figure out the purpose of each constant by its name.)
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (1927) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Converting from Relative to Absolute.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
Want to have you macro completely hide the Excel interface? You can do so by using the Visible property for the Excel ...
Discover MoreGot a macro that you need to run on each of a number of workbooks? Excel provides a number of ways to go about this task, ...
Discover MoreProcessing workbooks using a macro often involves the possible creation and subsequent deletion of worksheets. When it ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments