Overview

The Excel Absolute Relative Reference Change Software provides an easy method to change all of the formulas in a selection, sheet or workbook from relative to absolute, or from absolute to relative. This software is installed as an Add-In to MS Excel.

Formula References

When entering a formula into a cell a user often needs to refer to another cell as a parameter to one of the functions. For example if the formula:

=A1

Was entered into cell E7 the value in E7 would be the same as the value in cell A1. This would be handy for replicating a single cell somewhere else on a sheet.

Copying A Formula

What if a user wanted to replicate not just a single cell, but a whole row, or block of cells? Excel has made this easy. Simply by copying the cell with the formula and pasting it, the row and column values will automatically be incremented or decremented to match the distance from the original cell with the original formula. So for example if we copied the =A1 formula shown above from cell E7 to E8 it would automatically change to =A2. The row of the reference was incremented by the same distance of the copy. This is because the original formula entered into E7 uses a Relative Reference to refer to a cell.

Sometimes, however, this is not the desired behavior. A user may wish to copy a cell that contains a formula and have the formula remain the same, not automatically incremented or decremented. This can be done by using Absolute References in the formula. An Absolute Reference has a $ before the row or column in the reference. This basically informs Excel that when the cell with an Absolute Reference in it is copied, do not automatically change any row or column that has a dollar sign in front of it. For example

=$A1 means do not increment or decrement the column if the cell containing the formula is copy and pasted.

=A$1 means do not increment or decrement the row if the cell containing the formula is copy and pasted.

=$A$1 means do not increment or decrement the row or column if the cell containing the formula is copy and pasted.

Main Screen

The Absolute Relative Reference Change Software can be started by double clicking the icon on the user’s desktop or choosing it from the Start Menu. Since the application is installed as an Add-In to MS Excel, doing so will execute Excel with an Add-In called Abs/Rel as shown below:

In addition to the Main Screen, if you are using the free version of the software an additional window is displayed. This window offer you links to “Buy Now”, “Enter License”, “Support” and “sobolsoft.com”.

The “Buy Now” link will use your default internet browser to display a page that will allow you to purchase the software and receive a License. The license may be entered into the dialog that is displayed when you choose the “Enter License” link. The “Support” link uses your default browser to display a list of frequently asked questions at https://sobolsoft.com/question/ and the “sobolsoft.com” link will bring you directly to the Sobolsoft website at www.sobolsoft.com.

Once a license has been entered in the “Enter License” window this window is no longer available but all of the information is still available at the sources listed above.

The Abs/Rel Add-In Menu

Clicking on the Abs/Rel Add-In Entry will bring up the menu displayed below:

The “Product Website” item will display the website below in your default browser:

https://sobolsoft.com/excelabs/

The “Watch Help/Demo Video” will show the following avi formatted video:

How To Load Software Into Excel 2003 and 2007.avi

Email Author will display the following website in your default browser:

https://sobolsoft.com/question/

And open a new email to support@sobolsoft.com in your default email client.

The “Enter Personal License” item will display the following dialog:

This will allow you to enter the license number purchased from Sobolsoft. The “Remove this Addin From Excel” will remove the “Abs/Rel Add-In” from the Excel Add-In menu

Changing References To Absolute

To change references in formulas to Absolute References as described above, press on the “Make Absolute” menu item. Doing so will present the following three menu items:

· Selection

· Sheet

· Entire Workbook

The menu is shown below:

Choosing “Selection” will change all reference in all formulas in all of the cells currently selected to Absolute References. Choosing “Sheet” will change all reference in all formulas in all of the cells in the current sheet to Absolute References. Choosing “Entire Workbook” will change all reference in all formulas in all of the cells in the whole workbook to Absolute References. In all three cases, a dollar sign will be added in front of both the row and the column of the reference.

For example:

=A1

=$A1

=A$1

Will all become:

=$A$1

Changing References To Relative

To change references in formulas to Relative References as described above, press on the “Make Relative” menu item. Doing so will present the following three menu items:

· Selection

· Sheet

· Entire Workbook

The menu is shown below:

Choosing “Selection” will change all reference in all formulas in all of the cells currently selected to Relative References. Choosing “Sheet” will change all reference in all formulas in all of the cells in the current sheet to Relative References. Choosing “Entire Workbook” will change all reference in all formulas in all of the cells in the whole workbook to Relative References. In all three cases, a dollar sign will be stripped from both the row and the column of the reference. For example

=$A$1

=A$1

=$A1

Will all become:

=A1