the problem with Excel tables is there's no easy way to lock the references to the columns so when you copy the formula across to other columns it ends up in a mess in this video I'll show you how to lock table references and a shortcut to apply them so let's get started here I've got a table containing stock data and we can see on the table design tab it's called stock let's say I want to find the total stock on hand for the bread group i can use the sum ifs function for that we're going to sum the stock on hand column the criteria range is the group column and the criteria is in this cell here and that all works fine but the problem occurs when I want to copy this formula across to find the total value to facilitate this I'm just going to press the F4 key and absolute the reference to cell D4 and that'll lock that reference the F4 key doesn't work on the structured references so let's leave them as is and we'll see what happens when I copy the formula now there are two ways to copy the formula i canrl C and Ctrl +V and I get the same result because when you copy and paste a formula containing structured references they're treated as absolute happy days if that's what you want but most of the time you don't alternatively you can leftclick and drag to copy and what do you know i don't get the correct result here either which is super frustrating if I edit the formula you can see both the sum range and criteria range have shifted across one column in other words when you leftclick and drag table formulas the references are treated as relative and what I actually want is to lock the reference to the criteria range so that it's always referencing that and keep the sum range stock on hand relative so that it moves across to the value column now I can lock the reference to the criteria range by wrapping my reference in another set of square brackets and then add a colon opening square bracket and repeat the column name and then two square brackets to close that's now an absolute reference that basically says reference the group column to the group column so now when I left click and drag I get the correct result now I don't know about you but I find all that typing extra brackets and the colon a pain so let me undo that and I'll show you a shortcut what we want to edit is this stock group reference and all I need to do is reference two columns and notice Excel puts in the extra square brackets the colon and all I need to do now is copy the column name here and paste it in there press enter and I can left click and drag and I get the correct result with a lot less hassle it's not as handy as the F4 key but it's still much easier and you don't need to remember the correct syntax because Excel will write it for you the double square bracket trick doesn't just work for single columns it works for multiple columns too for example here I want to use XLOOKUP to find the product in cell J4 comma the lookup array is my product column here now I want to copy this formula across so I'm actually going to select two columns then I can copy this column name and repeat it here because I want this reference locked on the product column then comma I want all of these columns returned so notice as I select multiple columns it puts in the square brackets and this time it's going to return all columns from north through to west close parenthesis now xookup will spill that as a horizontal array i wanted to go down the column so we're going to wrap this in the transpose function and close parenthesis and there's my spilled array i can simply left click and drag to copy it across and my formula correctly picks up the right columns because of my absolute referencing with the double square brackets but what if I want a relative multicolumn reference for example let's say I want to find the sum for north to east i can select those two columns close parenthesis and then if I leftclick and drag you can see it's locked the reference so it's picking up the same result that's not what I want instead I can remove the double square brackets and I can repeat the table name after the colon and before the last column remove the other square bracket press enter i get the same result but this time when I copy it across if I edit the formula you can see it shifts across one column at a time locking row references is similar for example here I have overheads for admin and marketing that I need to allocate to the various product departments and I'm going to go into the formula bar and calculate that with a simple formula which is allocation times admin overhead notice Excel places the at sign before the column name which means it will get the value from the current row of each column press enter and the table automatically copies the formula down and if you look in the formula bar you can see the formula is the same the whole way down the column yet it returns a different result for each row that's the at sign at work now if I left click and drag the formula across I get the same problem I had before the references are not fixed i need the reference to the allocation percentage to be locked so let's undo that and what I need to do here instead of just referencing allocation I'm going to reference two cells and then copy the name here and paste it over this one now I have a fixed reference to the allocation column and my reference to admin overhead is relative and now I can copy it across and down and I get the correct result and if I want a total for the overheads I can use sum to sum those two columns notice in the formula bar it references the table name pops in the at sign and the double square brackets for me now one thing to note if you're referencing a row for a single column and that column name doesn't have any spaces like the allocation column here you don't need the additional set of square brackets after the at sign so whether it's a single column a range of columns or a row reference the double square brackets are the key and while it might seem like a lot remember the easy way to write these formulas is to let Excel do the heavy lifting by selecting a range of columns and then copying the column name that you want locked into the second reference job done i've also put this handy cheat sheet together that lists the different reference types and the syntax for inside the table and outside the table so be sure to download the file from the link in the video description so that's how you keep your Excel table formulas stable and if you're someone who loves finding fixes like this you'll definitely want to check out this video next it covers 13 practical Excel tricks that solve the most common and most annoying problems from cleaning messy data to debugging broken formulas i'll see you there