Retroactively apply ranges names to existing formulas in Excel.
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Excel 2010 In Depth chapter 18 – Apply Names to Formulas!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Chapter 19 in the book is all about using names in Excel.
And, you know, let's just take a look at formula here, so we have a formula B5+B4, and you're probably used to that formula nomenclature.
But I want you to take a look at these cells, Cost of Good 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 =that-that, you'll notice that the formula doesn't use cell B6-B8, instead it uses the names.
Alright, you have to admit, that's kind of easy when you're looking at the formula, Revenue-CostOfGoodsSold, it makes sense self-documenting.
Alright, you might say “Oh well, that's really cool, I would like to go back and change all of these formulas.” So we come up here and say “We're going to call this cell Net Sales.” I click here and do Net_Sales.
And that cell, we’ll call it Interest, and then we go look at our formula and it hasn't changed.
Alright, the fact that we named the cells doesn't mean that the formulas get rewritten, you know, you'd have to reenter 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'll choose Apply Names.
And I want 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+Net_Sales instead of B4+B5.
Kind of clever there, hidden, not many people would find it underneath the Define Name drop-down called Apply Names.
Great way to retroactively go back and change the formulas you created before you were smart enough to… (unclear) Hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
Excel 2010 In Depth chapter 18 – Apply Names to Formulas!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Chapter 19 in the book is all about using names in Excel.
And, you know, let's just take a look at formula here, so we have a formula B5+B4, and you're probably used to that formula nomenclature.
But I want you to take a look at these cells, Cost of Good 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 =that-that, you'll notice that the formula doesn't use cell B6-B8, instead it uses the names.
Alright, you have to admit, that's kind of easy when you're looking at the formula, Revenue-CostOfGoodsSold, it makes sense self-documenting.
Alright, you might say “Oh well, that's really cool, I would like to go back and change all of these formulas.” So we come up here and say “We're going to call this cell Net Sales.” I click here and do Net_Sales.
And that cell, we’ll call it Interest, and then we go look at our formula and it hasn't changed.
Alright, the fact that we named the cells doesn't mean that the formulas get rewritten, you know, you'd have to reenter 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'll choose Apply Names.
And I want 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+Net_Sales instead of B4+B5.
Kind of clever there, hidden, not many people would find it underneath the Define Name drop-down called Apply Names.
Great way to retroactively go back and change the formulas you created before you were smart enough to… (unclear) Hey, I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel!
