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.
This lesson is a preview from our Excel Bootcamp Online (includes software) and Excel Expert Certification Online (includes software & exam). Enroll in a course for detailed lessons, live instructor support, and project-based training.
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. I’m going to select those cells and then go to the Define Names group on the formulas tab and click Define Name. Here, I can add a name for the cells when I’d 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 might want to reuse that name elsewhere. And if I limit it to that one worksheet, I’ll be able to do so.
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 want them to understand why I named the range and what it refers to, which is, of course, the range I just selected. I’ll click OK.
Other methods to name a range include simply right-clicking and choosing 'Define Name.' You get the same dialog box, or you can click in the name box and type the name there. So, let’s calculate the sum of assets.
That’s going to be equal to sum open parentheses. And if I start typing 'assets,' it will appear alongside the other functions. If I see it highlighted, I can press TAB to select it.
Then I can press TAB again. And now I have the total. I’ll use the other methods to name the liabilities and equity ranges.
For liabilities, I’ll select the range. I’ll right-click, choose Define Name, and come up with a nickname for liabilities. I’ll call it 'LIA.'
Then I’ll press ENTER. While I’m at it, let me create a name for equity. I’ll select the cells and go straight up to the name box and type 'equity,' E-Q-U-I-T-Y.
Press ENTER. Make sure to 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 create a nickname for that, so I’ll 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 will simply be the sum of 'LIA' and '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.
