Named Ranges Excel: Free Video Tutorial

Named Ranges

Named Ranges allow you to assign a name to a range of cells to make it easier to reference those ranges in calculations. 

Exercise 1

In this exercise, we're going to create a named range for assets, liabilities, and equity and we'll use those named ranges in calculations. So the first step is to name the cells for assets. So I'm going to select those cells. 

And then over in the formulas tab, I'm going to head over to the defined names group and I'm going to click the find name here. I can add in the name for the cells when I would like to use them in their calculation. So I'm going to call them simply assets. 

That's an appropriate name that actually describes what those cells represent. I could choose the scope for this name. This name could be available throughout the entire workbook or it could be available on just one worksheet. 

There are certain situations where I may want to limit a name to a particular worksheet because I may meet worksheet because I may want to use that name over again somewhere else. And if I limit it to that one worksheet, I'll be able to do that. But if I make it available throughout the entire workbook, I'm basically taking that name out of circulation. 

I can enter a comment here in case I'm collaborating with someone. And I want them to be able to know why I named the range. 

And what I'm using it for and refers to is, of course, the range that you just selected. Click OK. Other methods you have to name a range are simply right. Clicking and choosing to find name. 

You get the same dialog box or just click up over in the name box and type the name there. So let's come up with the sum of assets that's gonna be equal to S U M open parentheses and if I start to type assets it will start to show up along with the other functions. 

And if I see it highlighted, I can simply press tab to select it and then I can press tab again. And now I have the total. I'll use the other methods to name liabilities and equity. So for liabilities I'll select the range. 

I'll right-click, choose defined name and I'll come up with a nickname for liabilities. I'll call it m.i.a.. Then I'll just press enter while I'm at it. Let me also create a name for equity. I'll select the cells and go straight up to the name box and then just type equity. E Q You i t y press enter. Make sure you press enter. 

And now I've named those ranges. Now I can just use my keyboard to come up with the total for liabilities and equity. 

And finally, there's some. So the sum of liabilities will be equal to the sum of ally a enter, then the sum of equity will be equal to the sum of E. Q You i t why? I didn't come up with a nickname for that, so I'm going to spell out equity and there it is. I'll press enter. 

Then I want to come up with the total of liabilities and equity. That'll simply be the sum of like a comma. Q You I t y when I press enter, I have the total right there. So named drenches. 

Make it very simple for you to write your functions by referencing the cells. You won't need your mouse to highlight and select the cells that you want to include in your calculations.