Today I'm going to share with you an Excel feature that can make your Excel life easier. It helped me a lot in making that Excel dashboard. And it's also a smarter way to work with Excel. It's called Named Range.
And here's how you use it.
As you expect, I have made a sample data for illustration. It's a sales report for a fruit grocery store.
Named Range allows me to group all the Product cells together and give them a name. And do likewise for the Unit Price and Quantity.
So let's do that for the Products. Highlight the Fruits, from Banana to Strawberry.
Click on Formulas tab, and Name Manger.
The Name Manager settings box will pop up.
Click on New.
It will automatically detect the cells you highlighted as the Range and give the Range the name Banana.
Click on OK. And you'll be taken back to the initial settings box, but now with the Banana Named Range.
Do the same for the other fields.
You can safely close the Name Manager box.
It's now time to put the named ranges to use. To begin working smarter on Excel.
Remember that in the original table, the Sales Amount hasn't been computed.
Now we are going to compute it for each product, in a smart way.
For the Banana, just key in the formula: =Unit_Price*Quantity
And copy it down, to Strawberry.
And now you're using Excel as a Pro. 20 years from now, your Excel sheet will make sense without you racking your brain.
Anytime you look at the formulas, they will always make sense.
And the best part is they make writing Excel macros more fun. You don't have to update your codes every time you change the positions of data in the Excel sheet or add new entries. Just update the named range, and you are done.
In fact, I'm now working on a big Excel macro to automate a giant report. And you can be sure that I'll be using named ranges.
great post, thanx
ReplyDeleteYou're welcome sir!
ReplyDelete