Absolute References Add-in Help

This is the help page for the Absolute References Add-in.

Install

Please see the Excel Add-in Installation Guide for details.

Compatibility

The add-in is compatible with Excel 2007, 2010, and 2013 for Windows.

Overview

The Absolute Reference Add-in allows you to create absolute references in your table formulas (structured references) using the F4 key on the keyboard.

Once the add-in is installed you will see the Absolute Refs button in the Add-ins tab of the Ribbon.  Pressing this button opens the Absolute References Add-in window.

Guide

The goal of the add-in is to save you time when creating absolute/relative references in your formulas, and it is very easy to use.

Quick Guide to Creating an Absolute Reference

The following is a quick step-by-step instruction for creating an absolute reference.

1. Select a cell that contains the formula you want to update.  The formula should contain structured references (references to table columns using the table syntax/nomenclature).
2. Click the Absolute Refs button on the Add-ins tab of the ribbon.
3. The Absolute Reference Add-in window will appear and the formula of the selected cell will be loaded in the text box.
4. The cursor will automatically be placed in the first column reference (enclosed in brackets [column1]) of the formula.
5. Press the F4 key on the keyboard to change the reference from relative to absolute.
   =Table1[Column1]  to =Table1[[Column1]:[Column1]] By default, the cell's formulas is automatically updated with the same formula that is in the add-in window.
6. Press the Enter key to close the form.

Press the Guide button in the add-in window to see some brief instructions.

Additional Features

F4 Key

To create an absolute reference, place the cursor inside the brackets of column reference and press the F4 key.  The column reference will be changed to include the duplicate column reference (absolute).

Pressing the F4 key again will change the reference back to a single column reference (relative).  The F4 key mimics the built-in functionality of the F4 key with cell references ($A$1) in the formula bar, and acts as a toggle.

There are a few options for what happens AFTER the F4 key is pressed.

• Automatically Update Formula – This is the default option.  When the F4 key is pressed in the formula box, the cell containing the formula will automatically be updated with the changes to the formula.  The benefit of this method is that it is very fast to update the formula.  The drawback is that the undo history for the formula is lost.  You will not be able to undo your changes.
• Copy Formula to the Clipboard – When the F4 key is pressed, the formula in the add-in window is copied to the clipboard.  Then you can paste over the formula in the cell with the new formula.  This method is a bit slower, but allows you to retain the undo history.
• No Action – When the F4 key is pressed, the formula in the add-in window changes but nothing else happens.  You can manually copy and paste your formula to the cell.

F3 KEY

The F3 key can be used to jump the cursor to the next column reference in the formula.  This saves you time from having to select it with the mouse or arrow over with the arrow keys.

When the cursor is placed inside the column reference you want to change, simply press the F4 key to toggle between relative and absolute.

When the add-in window is initially opened, the mouse cursor will be placed inside the first column reference in the formula.  This is another feature that saves you time.

Enter KEY

Press the Enter key to close the form.  Simple and fast.

GET Button

The “Get” button loads the formula from the selected cell into the add-in window.  You can leave the window open and update multiple formulas in your workbook.

Select a cell in your worksheet, then press the “Get” button to view it in the add-in window.

This saves time when multiple formulas need to be updated, however it is probably not best to always leave this window open.  You could experience issues when switching between different workbooks.

Add to the QAT

Adding the Absolute Refs button to the Quick Access Toolbar (QAT) will allow you to open the window with a keyboard shortcut or the mouse, so you don't have to click on the add-ins tab first.

To add the button to the QAT, right click on the button and choose “Add to Quick Access Toolbar”.

The button will be added to the QAT and you can access it using the Alt key plus the position number of the button on the QAT.  See my article on Keyboard Shortcuts for the Quick Access Toolbar for more details.

Auto Expansion

The add-in window will automatically expand as the formula gets longer.  The structured reference formulas are longer by nature, and it helps to be able to see the entire formula in the box.

Still Have Questions?

Please leave a comment below with any questions or contact me directly.  I really want this tool to work for you and hope it helps save you time.

New Features

New features will be based on your feedback and requests.  So please don't hesitate to leave a comment or contact me with anything you'd like to see added.  You request it, I'll do my best to create it. 🙂

Download

The add-in is available for download on the Absolute References Add-in page.