You can make your formulas easier to comprehend by assigning names to a group of cells and then using those named ranges in your formulas.
- [Instructor] So far, all of the formulas I've shown you use cell references such as A3 or the range A2:A5 to designate which cells should be summarized in the formula. Those references work fine, but it can make it hard to understand exactly what data your formula summarizes. You can make your formulas easier to comprehend by assigning names to groups of cells and using those named ranges in your formulas. I'll demonstrate several ways to create named ranges in this movie. My sample file is the NamedRanges workbook, and you can find it in the Chapter Three folder of your Exercise Files collection.
This workbook contains a single worksheet, and on it I have a cross tab which displays monthly data for physical years of 2013 through 2018. Let's say that I want to define 2013 as a named range. For that, I can select my values which are B5 through B16. Those are the 12 months. I am not including the header or the total. The quickest way is to click in the name box and then type in a name for your range.
So I'll type in FY2013, but I can't create it just yet. And the reason is that cell FY2013 actually exists in this workbook, so what I need to do is to add a character and I'll put an underscore after it, and press return, and I now have FY2013 as a named range. So I'll click away, in this case, to cell G5 but it could be anywhere outside of the range. Then, I'll go back to the name box, click its down arrow, click FY2013_, and that range is selected.
Another way that you can create a cell range, let's see I'll do it for FY2018, I'll select cells G5 through G15. Again ignoring the header and the total. Then go to the Formulas tab of the ribbon and click define name. Doing so displays the Define Name dialog box. I can enter a name for the data range, in this case FY2018_ because Excel looked at the column header.
And then select the range of cells, and in this case it is correctly defined. If I wanted to change it I could either edit it here, within the box, or I could click the rough edit button, as it's called, and select a cell range in the workbook. In this case I don't have to so I'll click OK and I get my range and you can also see that because the range is currently selected, it appears in the name box. You can also create multiple named ranges at the same time by creating from selection.
So, I will select cells C4 through F15, and note that I am including the headers in this case. Then, on the Formulas tab, I will click Create From selection. Doing so displays the Create Names dialog box, and Excel has correctly identified that the names for the ranges appear in the top row. That's row four. Now click OK, and now when I go to the Define Name dialog box, I see FY2013, 2014, and so on.
If I scroll down I see FY2018, and they all have the underscore, which was required to make the name legal. I'll click close to stop editing. Now, you can use those named ranges and formulas. So let's say that I want to find the total for 2017. So I'll click in cell I5 then type equal, and then sum, followed by left parentheses. And now I can start typing the name, so that'll be FY, and I get my list of named ranges that start with that character string FY, so I have FY2017_ right parentheses and return, and there's the total.
And note that it is the same as the value in F17. And we can do the same thing for 2018 so I'll type equal, sum, left parentheses, FY2018_, right parentheses, return, and there's that value as well. Named ranges make your formulas much easier to understand. If you refer to the same cell ranges over and over, take the time to create a named range and use them in your formulas. You'll be glad you did.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks
Skill Level Beginner
What you should know1m 11s
1. Getting Started with Excel
2. Managing Workbooks
3. Working with Worksheets, Cells, and Cell Data
4. Sorting, Filtering, and Managing Worksheets
5. Summarizing Data Using Formulas and Functions
6. Formatting Worksheet Elements
7. Working with Charts
8. Working with External Data and Objects
9. Exploring PivotTables
10. Reviewing and Sharing Spreadsheets
Further information1m 2s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.