What's in a name? Retroactively and easily make your newly created Range Names appear in prior formulas in Excel 2010.
...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Excel 2010 In Depth, Chapter 18 - Apply Names.
Oh hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Chapter 19 of the book is all about using names in Excel and you know let's just take a look at the formula here.
So, we have a formula B5+B4 and you're probably used to that formula nomenclature, but once you take a look at these cells, this cell cost of goods sold I've named that cell COGS, and this cell I've named that cell Revenue.
So, if I come down here and enter a formula of equal that minus that, you'll notice that the formula doesn't use cell B6-B8, instead it uses the names.
All right you have to admit that's kind of easier when you're looking at the formula Revenue - COGS.
It kind of makes sense.
Self documenting, all right you know, you might say oh, well that's really cool.
I would like to go back and change all of these formulas, so we came up here and say it right.
We're going to call this cell Net_Sales.
I click here and you can get Net_Sales.
Alright, and then this cell we'll call it interest, and then we go look at our formula, and it hasn't changed.
All right the fact that we named the cell doesn't mean that the formulas get rewritten.
You know, you have to re-enter the formula, or use this trick back here on the formulas tab under Define Name.
There's a drop down there called Apply Names.
We will choose apply names and I am going to just choose all of these and say hey any of these cells that are used in formulas, and we have a reference we want to use the name instead.
So, I click OK and now you'll see that my formula becomes Interest plus+Net_Sales instead of B4+B5.
Clever they're hidden.
Not many people would find it.
Underneath the define name drop-down called apply names.
Great way to retro actively.
Go back and change the formula as you created before you were smart enough to.
Hey there, well thank you for stopping by.
See you next time for another netcast from MrExcel.
Excel 2010 In Depth, Chapter 18 - Apply Names.
Oh hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Chapter 19 of the book is all about using names in Excel and you know let's just take a look at the formula here.
So, we have a formula B5+B4 and you're probably used to that formula nomenclature, but once you take a look at these cells, this cell cost of goods sold I've named that cell COGS, and this cell I've named that cell Revenue.
So, if I come down here and enter a formula of equal that minus that, you'll notice that the formula doesn't use cell B6-B8, instead it uses the names.
All right you have to admit that's kind of easier when you're looking at the formula Revenue - COGS.
It kind of makes sense.
Self documenting, all right you know, you might say oh, well that's really cool.
I would like to go back and change all of these formulas, so we came up here and say it right.
We're going to call this cell Net_Sales.
I click here and you can get Net_Sales.
Alright, and then this cell we'll call it interest, and then we go look at our formula, and it hasn't changed.
All right the fact that we named the cell doesn't mean that the formulas get rewritten.
You know, you have to re-enter the formula, or use this trick back here on the formulas tab under Define Name.
There's a drop down there called Apply Names.
We will choose apply names and I am going to just choose all of these and say hey any of these cells that are used in formulas, and we have a reference we want to use the name instead.
So, I click OK and now you'll see that my formula becomes Interest plus+Net_Sales instead of B4+B5.
Clever they're hidden.
Not many people would find it.
Underneath the define name drop-down called apply names.
Great way to retro actively.
Go back and change the formula as you created before you were smart enough to.
Hey there, well thank you for stopping by.
See you next time for another netcast from MrExcel.
