·
1 years ago
Wao Excellent teh VBA Code Works Perfect, Thank You very Much
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
Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by
80%
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
|
Absolute Reference
|
|
Relative Reference
|
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.
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 
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.
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
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment...
The comment will be refreshed after 00:00.