This post explains how to quickly create absolute references for table formulas using the Absolute References Add-in. This add-in will save you time when writing formulas with structured references (table syntax), and it's FREE!
When creating formulas with cell references, we can simply but a $ symbol in front of the column letter or row number to lock or anchor the cell reference. When the formula is copied or dragged across rows/column, the cell reference will not change. The absolute or relative references can be toggled on/off by pressing the F4 key on the keyboard when the cursor is placed in the cell address.
Table References (Structured References)
Creating an absolute reference with table references is a bit more complicated. To create an absolute reference, the column reference must be duplicated as if it were referring to multiple columns.
Relative: =Table1[Column1] Absolute: =Table1[[Column1]:[Column1]]
Please see my article and video on Absolute Structured References for a detailed explanation on this process. It is important to note that when copying the formulas across columns, you must drag the formula with the fill handle using the mouse; copy/paste does NOT work.
Creating absolute references using the duplicate column technique works well, but there is no built-in way to quickly toggle between relative/absolute references. Therefore, you have to type the column name a second time and you also have to type the brackets. This can be very time consuming. Especially if your formula is long with a lot of references, or you have a lot of formulas in your model that need updating.
The Absolute Reference Add-in makes this process much faster by allowing you to use the F4 key on the keyboard to toggle between absolute/relative column references.
This mimics the built-in functionality for the F4 key for cell references, and makes it very fast to update your table formulas with absolute or relative references.
How Does It Work?
Once the add-in is installed, an “Absolute Refs” button is added to the ribbon (toolbar) in the Add-ins Tab. When the button is pressed a simple form is opened and the cell's formula is loaded in a text box.
To create an absolute reference, you simply place the cursor in a column reference and press the F4 key on the keyboard.
The goal of the add-in is to make it very fast to update your formulas with absolute references. Here are the features that help make it so fast.
- F4 Key: The F4 key toggles between relative and absolute table references. The first press of F4 will change the reference to absolute [[Column1]:[Column1]]. Pressing F4 again will revert it back to a relative reference [Column1].
- Cursor Placement:When the Absolute Refs window is opened and formula loaded, the mouse cursor is automatically placed in the first table reference in the formula. You don't have to navigate to it with the mouse or keyboard arrows.
- F3 Key: The F3 key will jump the cursor to the next column reference in the formula. This means you can continue pressing the F3 key to jump to each column reference in the formula, so you don't have to waste time using the mouse or keyboard arrows. When you land on the reference you want to change, press the F4 key to change the reference.
- Formulas Automatically Updated: The actual formula in the cell is automatically updated every time F4 is pressed in the Absolute Refs window. This is the default option and also the fastest option. See the user guide for details on additional options.
- Enter Key: Pressing the Enter key when the Absolute Refs window is open will close the form and return you to the worksheet.
- Update Multiple Formulas: The Absolute Refs window can remain open if you want to update multiple formulas. Press the “Get” button (located to the left of the formula box) to load the formula from the selected cell in the worksheet.
- Auto Expand: The add-in window will automatically expand for longer formulas.
- Add to QAT: The add-in button can be added to the Quick Access Toolbar (QAT) so you can quickly open the window with a keyboard shortcut or mouse click. This saves time because you don't have to click on the Add-ins tab in the ribbon to open the add-in window. See my article on Keyboard Shortcuts for the Quick Access Toolbar for more details.
- Create Absolute References in under 3 seconds: Once the add-in button is added to the QAT, you can update your formulas entirely with the keyboard, and never have to use the mouse. This 3 step process is very fast!
Absolute Reference Add-in.zip (1.3 MB)
WHAT'S IN THE ZIP?
The zip file contains: the add-in file, installation guide, and user guide.
ONLINE HELP PAGES
Please see my video on Excel Tables: A Beginners Guide for Windows & Mac if you are new to tables.
Please see my article and video on Absolute Structured References for a detailed explanation on how to create absolute references.
WHAT DO YOU THINK?
The goal of this add-in is to save you time, and I want you to love it. Please leave a comment below with any questions, comments, or feature requests. Thanks!