Absolute Reference Add-in F4 Key in Structured Table Formulas | Excel Campus
26

Absolute Reference Add-in – Use the F4 Key in Structured Table Formulas

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!

Absolute References Add-in F4 Key

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.

Absolute Cell Reference Example

_

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]]

Absolute Table Reference Example

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.

Absolute References Formula Slow

_

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.

Absolute Reference Add-in Userform Screenshot

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.

Absolute References Add-in Full Screenshot

To create an absolute reference, you simply place the cursor in a column reference and press the F4 key on the keyboard.

Absolute Reference Add-in F4 Key Before-After3

_

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].
    Absolute Reference Add-in F4 Key Before-After3
  • 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.
    Absolute Reference Add-in Cursor First Col
  • 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.
    Absolute Reference Add-in F3 Key Move Cursor
  • 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.
    Absolute Reference Add-in Automatic Update
  • 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.
    Absolute Reference Add-in Get Button
  • Auto Expand: The add-in window will automatically expand for longer formulas.
    Absolute Reference Add-in Auto Expansion
  • 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.
    Absolute Reference Add-in QAT
  • 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 QAT Demo

_

Download

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.

Absolute Reference Add-in Zip Contents

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!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 26 comments
Ben - January 25, 2017

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 ?

Reply
    Jon Acampora - February 4, 2017

    Hi Ben,
    Can you share the formula?

    Reply
      Angus - August 8, 2017

      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

      Reply
David - June 29, 2016

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

Reply
    Jon Acampora - June 30, 2016

    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?

    Reply
Moises - June 2, 2016

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

Reply
    Jon Acampora - June 2, 2016

    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!

    Reply
Alexis Petridis - July 24, 2015

Thank you very much! It is much more than I expected, the add-in works perfectly!

Reply
Jeff Weir - March 18, 2015

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…

Reply
    Jon Acampora - March 18, 2015

    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! 😉

    Reply
Adi - September 7, 2014

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! 🙂

Reply
    Jon Acampora - September 13, 2014

    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

    Reply
Excel Tables Absolute Structured References - August 19, 2014

[…] can download the Absolute Reference Add-in here. Be sure to say thanks to Jon for this excellent […]

Reply
Dominic - May 7, 2014

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!

Reply
    Jon Acampora - May 7, 2014

    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

    Reply
Robby - April 25, 2014

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!

Reply
    Jon Acampora - April 26, 2014

    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. 🙂

    Reply
Thomas - February 13, 2014

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.

Reply
    Jon Acampora - February 14, 2014

    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. jon@excelcampus.com

    Reply
ahmed - February 4, 2014

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

Reply
Juanito - December 5, 2013

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

Reply
    Jon Acampora - December 6, 2013

    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!

    Reply
      Juanito - December 7, 2013

      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

      Reply
don - October 23, 2013

as always, fantastic work Jon. appreciate the freebie as well.

Reply

Leave a Reply:

Free Webinar: The 5 Secrets to Understanding Pivot Tables

x