Enhance your Excel efficiency by mastering named ranges to simplify cell referencing and calculations. Learn practical methods for defining and utilizing named ranges in your worksheets.
Key Insights
- Named ranges allow users to assign descriptive names (such as "assets," "LIA," and "equity") to selected cell ranges, streamlining the referencing of these cells in formulas and calculations.
- Users can define named ranges using multiple methods: through the formulas tab's "Define Name" option, by right-clicking cells and selecting "Define Name," or by entering a name directly into the name box.
- Named ranges can be scoped either to a single worksheet or the entire workbook, allowing flexibility when working with multiple worksheets or collaborating with others.
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. 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 on the formulas tab, I'm going to head over to the define names group and I'm going to click define name. Here I can add in the name for the cells when I would like to use them in a 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 can 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 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. I'll click okay.
Other methods you have to name a range are simply right clicking and choosing define name. You get the same dialog box or just clicking up over in the name box and typing the name there. So let's come up with the sum of assets.
That's going to be equal to sum 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 define name, and I'll come up with a nickname for liabilities. I'll call it LIA.
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-U-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 their sum. So the sum of liabilities will be equal to the sum of LIA. Enter.
Then the sum of equity will be equal to the sum of E-Q-U-I-T-Y. 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 LIA, E-Q-U-I-T-Y.
When I press ENTER, I have the total right there. So named ranges 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.