Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to change/convert absolute reference to relative reference in Excel?

Normally there are several types of cell references you can use in a formula, and each type of cell references can help you achieve different purposes in the formula. You can have the following types of cell references, such as absolute cell reference, relative cell reference, relative row reference absolute column reference and absolute row reference and relative column reference. But sometimes you may need to change the using of the formula purpose by changing the cell references in the formula. The following tricky methods will you tell how to change absolute reference to relative in Excel.

Change absolute reference to relative reference with F4 key

Change absolute reference to relative reference with VBA code

Quickly change absolute reference to relative reference with Kutools for Excel

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial

Absolute Reference

Relative Reference


arrow blue right bubble Change absolute reference to relative reference with F4 key


With the shortcut key F4, we can easily toggle the absolute reference to relative reference, please do as the following steps:

Put the cursor behind $A$1, then press F4 three times, it will become A$1, $A1, A1 successively. See screenshot:

According to this step, put the cursor behind $B$1 to get B1.

And this cell reference will become a relative reference from absolute reference.

If there are multiple formulas’ cell references need to be changed, this way will be tedious and time-consuming.


arrow blue right bubble Change absolute reference to relative reference with VBA code

With VBA code, you can quickly change a range of formulae cell references from absolute references to relative references at a time.

1. Select the range that you want to change.

2. Click Developer > Visual Basic or you can press Alt + F11, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following code into the Module:
VBA: Convert absolute to relative reference.

Sub ConverFormulaReferences()
'Updateby20140603
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
Dim xIndex As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
xIndex = Application.InputBox("Change formulas to?" & Chr(13) & Chr(13) _
& "Absolute = 1" & Chr(13) _
& "Row absolute = 2" & Chr(13) _
& "Column absolute = 3" & Chr(13) _
& "Relative = 4", xTitleId, 1, Type:=1)
For Each Rng In WorkRng
    Rng.Formula = Application.ConvertFormula(Rng.Formula, XlReferenceStyle.xlA1, XlReferenceStyle.xlA1, xIndex)
Next
End Sub

3. Then click doc-absolute-relative-6 button to run the code, and a prompt box will pop out  for selecting a range to convert, then click OK and another dialog displays to prompt you which type you want to use. Then you can choose the right type you need. For this example, I will insert 4. See screenshots:

4. Then click OK. All of the absolute references in selection have been changed to relative references in the formula.

This is a multifunction VBA code, with this code; you can also change relative to absolute reference, change absolute row or change absolute column.


arrow blue right bubble Quickly change absolute reference to relative reference with Kutools for Excel

Kutools for Excel: with more than 100 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now

Kutools for Excel let you change absolute to relative reference or vice versa quickly and easily. Please do as follows:

Step 1. Go to select the range that contains formulas you want to change cell references in worksheet.

Step 2. Click Kutools > Convert Refers. It will display Convert Formula References dialog box. See screenshot:

Step 3. Check To relative and click Ok or Apply in the Convert Formula Reference dialog. It will change the absolute reference to relative reference.

If you would like to change the cell references to column absolute or row absolute, please check To column absolute option or To row absolute option.

For more detailed information about Convert Reference, please visit Convert Reference feature description.


Relative article:Change relative reference to absolute reference


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Jon · 1 years ago
    Excel 2010. I clicked on cancel when the prompt comes up, but excel still ran the script and it froze my computer for several hours.

    I only had 1 cell highlighted anyway, so even if the script did run, why did it take so long to run? [i realize now that despite pressing cancel, the code ran and made every cell in the worksheet relative ! :( ]

    I was assigning it to a form button vs pressing play within VBA window.

    I've used other scripts from KuTools and never experienced this nonsense.
  • To post as a guest, your comment is unpublished.
    Mark Palmer · 5 years ago
    The F4 toggle works in all versions. but you MUST be in edit mode first.
    I always press F2 (puts you into edit mode) then F4 to toggle between the four options.
  • To post as a guest, your comment is unpublished.
    Chad Sellers · 5 years ago
    With the shortcut key F4, we can easily toggle the absolute reference to relative reference, please do //as// the following steps.
    Forgot to mention F4 didn't work with my excel (windows 7) but your macro and invite to Kutools is great. Sorry about repeat sends, I thought the code wasn't working.
  • To post as a guest, your comment is unpublished.
    Chad Sellers · 5 years ago
    Thank you very much. Descriptive, easy to follow information.
  • To post as a guest, your comment is unpublished.
    Chad Sellers · 5 years ago
    Thank you very much, descriptive, easy to follow information.