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!
Video
Overview
Cell References
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.
The Problem
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 Solution
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.
_
Additional Features
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!
_
Download
WHAT'S IN THE ZIP?
The zip file contains: the add-in file, installation guide, and user guide.
ONLINE HELP PAGES
How to Install an Excel Add-in Guide
Absolute Reference Add-in Help Page
Additional Resources
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!
as always, fantastic work Jon. appreciate the freebie as well.
Thanks Don! Free is always the best… 🙂
Good add-in: thank you, Jon! Tables are an incredibly useful feature, however in my experience many users get bogged down with the STR syntax, and yes, it’s fiddly to make column references absolute.
Not sure how easy to code, but it would be extra-yummy if you could include the @ in your toggle utility.
– Juanito
Hi Juanito,
When you say include the @ in the toggle, are you looking to toggle between a column reference with the @ symbol or without the @ symbol? This would toggle the reference between referring to “this row” and the entire column. I just want to be clear on your suggestion.
Thanks!
I suppose what I mean is that F4 should take you through all the permutations of aboslute, relative and composite, just as F4 with a grid formula does for rows and-or columns. Happy to expand further if what I mean is still not clear, Jon
– Juanito
It’s a magnificent add-in ,
I thought it would fix my problem but not :/
here is my case :
have an Excel Addin that generate data from database. after loading data on sheet (sheet1) , I write excel functions(in sheet2)depending on that data , at the beginning it works great, but when I re-exrcute my function(sheet1)and then refresh the sheet2 I get the #REF error !!
=+MAX(SUM(Table1[[#ALL];[column1]]);SUM(Table1[[#ALL];[Column2]))
Link:
http://stackoverflow.com/questions/21554665/ref-excel-error-add-in/21554943?noredirect=1#21554943
Hi Ahmed,
I would be happy to help. Can you send me your file to [email protected].
Thanks!
Hey Jon!
I’ve been looking for an add-in like this for ages. It’s brilliant work and it would save me a lot of time. Unfortunately, I’m having no success using it. Pressing F4 does not result in anything although I have enabled the add-in. Any ideas on how to solve this? Excel 2007.
Hi Thomas,
Is the formula being loaded into the add-in window properly? When you click the Absolute Refs button on the Add-ins Tab, does the window pop-up with formula loaded in the text box?
The cursor has to be located inside of the column reference before pressing F4.
I can take a look at your file if you want to send it to me. [email protected]
Hey…great free tool!! However I did see an error occur or it is not working for me. When I am utilizing the “Totals” option for a table the add-in does not recognize this. Where a value has “#” for the Total column or Row. Otherwise a great tool thank you!
Thanks for letting me know about that error Robby. I will fix it and let you know when it’s updated. I’m glad you found the tool useful. 🙂
Hi Jon
This is great work you did there. Your explanation on referencing tables is great and this add-in can save a lot of time and typos.
I found a situation where your add-in returns an error.
I’m usually breaking down my formulas with carriage returns to make them easier to read. When in Automatic update mode, your add-in is unable to process it properly.
Here is a formula example:
=SUMIFS(
Table1[Cost],
Table1[Item], Table2[@[Item])
When pressing F4 in Automatic update mode, I get the error “Formula contains an Error and cannot be inserted in the active cell”.
This error is not present when I switch to Copy Formula to Clipboard and the CR are intact when I copy it back into the cell.
Still, thank you for sharing your knowledge!
Hi Dominic, Thanks for letting me know about this error. It is probably not handling the carriage returns properly when automatically updating the formula. I might be able to fix that.
I agree that the carriage returns make the formulas easier to read.
Thanks again!
Jon
[…] can download the Absolute Reference Add-in here. Be sure to say thanks to Jon for this excellent […]
Dear Jon,
Thank you so much for a wonderful add-in. Cannot thank you enough and wish you keep doing great stuff with Excel. Thanks a ton, you’ve got a loyal visitor / reader of your blog in me, and here’s wishing excel campus all the best, always!
Adi
P.S. On a side note, the excel TV interview was very interesting and count your journey in Excel as a source of inspiration! 🙂
Thank you Adi! I really appreciate that. I’m glad you enjoyed the interview. It was fun to talk with the Excel TV crew.
All the best wishes to you as well!
Jon
This tool is just awesome, Jon. I’ve just put screenshots of it in my book. That means you should get a substantial increase in traffic from my mother and all her friends, once the book comes out! Mind you, she doesn’t even know what Excel is…
Wooohooo! Thanks Jeff! 🙂 Well the first thing your mother is going to want to learn is structured references. I will have to thank her for raising such an evil genius, I mean superhero! 😉
Thank you very much! It is much more than I expected, the add-in works perfectly!
Hi Jon,
Thanks for this; it seems really useful and I’ve just downloaded it. However, I don’t seem to get it to work on my MAC. I am using excel 2011 for mac (and will soon switch to excel 2016 for MAC).
Do you have an updated version for MAC?
Thanks again
Hi Moises,
Unfortunately this add-in only works on the Windows versions of Excel. I don’t have plans to make a Mac version at this time. Sorry about that. Please let me know if you have any other questions. Thanks!
Hi Joe
Would you have any advice for cell referencing in excel 2016 using F4 key.
It is not working.
Tried to contact Microsoft and it like trying to rob the Bank of England. Very frustrating.
Can’t understand why Microsoft have changed so many things around. It used to be so easy to navigate
Regards
David
Hi David,
I’m not sure I fully understand your question. Are you saying the F4 key is not toggling the cell reference to absolute?
Hi Jon
I installed your addin on Excel 2016 running windows 10 and i get the following error message when I press f4 as described: “Formula contains an error and cannot be inserted in the active cell.
The formula does NOT contain an error.
Can you suggest how i can make this nice addin work ?
Hi Ben,
Can you share the formula?
Hi Jon
I’ve experienced the same issue as Ben when trying to toggle to absolute reference with the F4 key. Windows 10, Excel 2016 (Office 365).
The formula I’m using is as follows: =INDEX($K$4:$M$9,MATCH([@[Transaction type]],$K$4:$K$9,0),MATCH(Table1[[#Headers],[Jan-17]],$M$4:$M$9,0))
When I click on the add-in, the pop-up box appears with the formula in it but when I click on F4 I get the following error message: “Formula contains an error and cannot be inserted in the active cell”
Before I pressed F4, I tried F3 to move the cursor in the pop up box to the next Table formula and this worked fine.
Would be great if you could take a look at it.
Thanks
Jon
I would like to incorporate this add-in in an add-in i created for the people in my company. Would you be so kind as to provide me with the password for the VBA or send me the code?
I have an idea how you did it, but if possible i would like to reuse the work you did instead of writing everything from scratch again 🙂
you can email me at jonas.arnout [at] gmail.com
Hi Jon,
I downloaded the add-in, inserted the path among the trusted ones, and it doesn’t work. I click on the cell that I want to toggle (adding the $ sign), I click then on the add-in button, the pop-up window comes up, I press the F4 button, and nothing happens, not even an error message. F3 doesn’t work either. What am I doing wrong? I use Excel 2016.
Many thanks in advance!
Cati
Jon:
I have come to the conclusion after a number of hours trying to get the absolute reference key F4 to work that it does not work for Excel 2016. The installation Guide seems to support this conclusion. Even though it says Excel 2007 + the documentation only indicates 2013. Is there another resource for this valuable tool?
Jack
Hi Jack,
The F4 key does work for my in Excel 2016. If you are using a laptop then you might have to press the Fn key in combination with F4. Some laptops have Fn Lock options so you can make the function keys primary, and not have to press Fn.
I hope that helps.
The problem may be our configuration at the college; laptop on a docking station with MS Keyboard 4000 and dual monitors. The keyboard does not have a designated function key and my research so far has not turned up a solution. Are you aware of a keyboard that might this designated key?
On a positive note, your blog post on the MX Master 2S mouse for Excel was dead on! I got one and love it!
Jack
I found the issue! So for everyone else in my situation; the “F Lock” Key (the last key on the right in the Function Row) on some Microsoft Keyboards is the secret. The “F Lock” must be ON in order for F4 to work!
Thanks, Jon.
Jack
Hey Jack,
Thank you for letting us know. Happy to hear you got it figured out. Also happy to hear you are enjoying the mouse! Awesome! 🙂
Dear Jon,
Thanks a lot for your add-in, it is exactly what I was looking at. However, when I do F4, I have an “error 13 : type mismatch”. I am using Windows 10 and Office 2013. Have you ever face this difficulty ? Thanks in advance
Dear Jon,
Maybe a detail, I am working using a french version of excel. Formulas names are not in english, but in french. Do you think that could be the explanation of the error ? The problem might come from a code line with “.fomula” instead of “formula.local” ? I am just supposing, but I cannot check, as the VBA project is secured. Thanks in advance,
Regards,
Damien
Is this supposed to be compatible with Excel 365?
Hello sir,
The Form Size is small.
Not properly visible.
Desde Perú, excelente herramienta, muchas gracias por compartir.
Hi Jon, I was looking to find a solution to this specific problem and came across your blog. Now this seems exactly what I need and more, but I cant seem to get it to work. Does this still work in 2024? Im using Office 2021.
Hi Jon,
Quick question – you mention that this add-in works in Excel 2007, 2010 and 2013. I am running Excel version 16.78.3 on Office LTSC Standard for Mac 2021. Do you know if this add-in works in versions later than 2013 and especially on Mac please?
Many thanks for your help.