Excel Relative Named Ranges - A Secret of Excel Pro Users

many of us all have seen a reference in the name manager for a cell at the bottom of the worksheet wondered how on earth did that happen when it's supposed to be referencing well in this example cell a4 in this video I'm going to answer this mystery known as relative named ranges and show you how you can exploit them for good of course I wrote a blog post on this topic a while back and it was hugely popular so stay tuned because I'm going to share some excel secrets held close by Excel power users a relative named range returns a result that's relative to the cell in which you use it now to understand this let's just take a moment to revisit a concept that every Excel user should know very well and that is the way a relative cell reference automatically updates as its copied from one cell to the next for example here in cell b7 if I enter a sum function you can see the reference b2 to b6 is relative that is there's no dollar signs to make the reference absolute a press Enter and then if I copy that across you can see that the formula is now reference in column C column D and column E that is it automatically adjusts the column reference relative to its new location and if I were to copy this formula and paste it down below and if we f2 to edit the cell you can see that the row references are also relative that is there now referencing rows 11 to 15 and obviously there's nothing in those cells so it returns zero so let me delete that relative named ranges work in the same way and we can use them in place of the individual sum formulas that we have up here in row 7 let's take a look for example here in cell b7 we have the formula total sales if I use the keyboard shortcut ctrl f3 to open the name manager and if I select the name total cells you can see in the refers to that it's summing cells b2 to b6 of the current sheet our total sales uses the sum function and therefore it's technically a named formula so I'll refer to it as such going forward if I close this and we look at cell c7 and again open the name manager we can see day sales now references column C likewise if we look at column D control f3 it now references column D and that's because the formula in the name manager is relative this reference here to d2 to d6 is a relative reference and it will be exactly the same for cell d7 in other words the name formula total sales will always sum the five cells immediately above the cell in which you place it and it does this because the cell references in their first two field are relative now it's important to understand if we look in the name manager again the scope here for this formula is the workbook meaning I can use it on any sheet however if we look at the refers to it specifies the sheet that it's going to sum so it's always going to sum these cells if I go to sheet 1 and type in equals total sales its summing the cells relative to cell b7 but on the relative named formula sheet so it's summing these cells here now if I wanted to use this name relative to any sheet then I can edit the name in here and remove the sheet name from the reference so let's just delete that so now all we have is the exclamation mark and then the relative cell references omitting the sheet name results in a dynamic sheet reference so while the named formula will have the scope of the workbook it will refer to the active sheet so let me press close will save those changes now if we go to sheet 1 you can see it returns nothing because it's summing these cells here so if I enter a hundred in each of these cells now my total sales is summing these cells here and if we look at the relative named formula sheet this total sales formula is still summing these cells here it doesn't matter where you use it it's going to sum the cells relative to the sheet in which it's on in other words I have a truly relative named formula relative to both the cells and the sheet now the trick to creating relative named ranges is patien that is the location of the cell you have selected prior to defining the name this example here was a relative named formula and we can see that if we go ctrl F 3 and we look at total sales it used a function so it's a formula inside of a name but you can also create a relative named range let's do that here so I want to named range it's going to reference these five cells and then when I use it in this cell I wanted to reference these five cells so I want it relative to this cell here that's my first cell I'm going to use in so I'm going to define the name we'll just call it product sales we'll get rid of the total it's just picked up the name from this row here and in here I want it to equal these five cells you can see it's automatically putting the absolute referencing so I'm just going to f4 three times to remove all the absolute references and then click OK now I can use it to sum the cells above so we want product sales there is their tab to select it you can see it's referencing the cells above and I press ENTER now I can copy that across and then if I f2 to edit you can see it's now referencing these five cells and these five cells and these ones so I've got a relative named range which I've used in a some formula so the key here when setting up your relative named ranges is to select the cell that you want the formula relative to before you define the name dynamic named ranges are a staple for the intermediate to advanced Excel user they allow us to return a range that adapts to our changing data for example we might use a dynamic named range as the source for a pivot table however typically these dynamic name Rangers aren't relative but an area where relative dynamic named ranges will come in handy is with sparklines and here in column a I've inserted a group of sparklines and you can see the data range is hard-coded to the cells c2 to i9 and this means that when new data is added for future months in column J onward we'll need to edit the sparkline data ranger and update it manually I know it's almost a swear word in Excel ideally we'd use a dynamic named range for the sparkline data range but you can't enter a dynamic named range for a group of sparklines only for individual spark lines and personally I don't fancy creating eight separate dynamic named ranges that's just way too much work luckily we can create one dynamic named range that's relative to the selatan and then reuse that for each individual spark line it's a lot quicker to copy and paste eight spark lines than create eight dynamic named ranges let's take a look so let's delete these spark lines and we'll create a relative dynamic named range now I want my range relative to the first spark line cell which is a two and I've selected that so now I can go up to the formulas tab and define a name we're going to call it spark line range in the refers to I'm just going to delete that and start from scratch so the first cell in my range will be c2 and then I need the range operator which is the colon it automatically puts in c2 again so I'm just going to backspace to delete that now in order to use my arrow keys inside this refers to dialog box I need to press f2 to bring it into edit mode now I can arrow back and delete the dollar sign for this reference as well because I want this to be relative so as I use this dynamic relative named range in the next sparkline I want it to reference cell c3 now we don't know the last cell in our data set at the moment we only have data up to July and I want to allow it to grow as I add more months so we use index to return the reference to the last cell that's currently got days for in it in row two now if you have office 365 you could use the new ex lookup function I'm sticking with index because everybody has that so what's the range that we want indexed well it's c2 and I'm going to allow for growth in my data through two column o now if you're expecting much more data then you just extend that range out further you could even select the whole row but I recommend just selecting a few more cells than you think you're going to need now again this needs to be relative so I'm going to f4 three times to remove the absolute referencing the next argument for index is the row number argument now I only have one row referenced so I can skip that I'm just going to enter another comma and the last argument for index is what's the column number well we don't know the column number because it's going to change so we're going to use the count our function for that and what are we counting well I'm going to count the column labels because they will always have data in them even if these values are blank if my column label has a month name in it then that's a good sign that there's going to be data underneath this it's just safer to count the column labels again I've taken it out to column oh so we're going to close the parentheses on counter close the parentheses on index and I'll click OK now before I insert my sparklines I want to test that my formula is evaluating correctly so I'm going to select cell a3 go back into the name manager select my dynamic named range click in the refers to field anyway will do and if I move the name manager box you can see the marching ants are around the cells that the formula is evaluating to and I can see it's correctly selecting Jan to July 4 row 3 so my dynamic relative named range is working as expected let's go ahead and insert the sparklines so on the insert tab i want a column sparkline my data range is going to be my new relative dynamic named range and I'll click okay and now will they need to do is copy it down like a left click and drag unfortunately you can't double-click sparklines to copy them down and you can see each sparkline is picking up its relative row now if I were to add more data for example if we have data for August if you keep an eye on the sparklines you'll see they incorporate it automatically and likewise if I were to add another row the sparkline would adjust to pick up the row that its relative to okay take a moment to download the excel file for this lesson from the link here or in the video description it contains all the examples covered in this video I hope you can make use of relative named ranges if you like this video please give it the thumbs up and subscribe to my channel for more and why not share it with your friends who might also be wondering why they're named ranges are mysteriously changing themselves to some random cell in the bottom of the worksheet

Channel: MyOnlineTrainingHub

Views: 37,083

Rating: 4.9831815 out of 5

Keywords: excel named ranges, excel relative named ranges, excel named range

Id: gCo0zL3-OtE

Length: 11min 51sec (711 seconds)

Published: Wed Feb 26 2020