1. Named ranges
As we have discussed earlier, setting up your raw data in the first place is paramount. We can use a feature called Named ranges to make our data easier to work with.
2. What is a named range?
A Named range can refer to one cell or a range of cells, a constant value, one that rarely changes, or a formula with a given name.
It is a user-friendly name for a range of cells since it's easier to remember a name over a range reference. This makes formulas simpler to understand, especially when referencing a range from another sheet.
Remember formulas of reference? We can name our tables and lists with user-friendly names, so, rather than using cell references, we can use these names to make things clearer. Again, the range reference can be changed globally in one place.
3. Selecting data for the named range
There are a couple of ways you can create a Named range.
The first, and I believe the easiest way, is to highlight the range you want to name, then go to the toolbar and select Data, then Named ranges.
You will see the default name, NamedRange1, at the top of the Named ranges menu on the right of your screen. Replace this with a meaningful name, remembering that Named ranges do not like spaces. Either use an underscore or combine words together without spaces. Click Done when you have finished.
4. Creating a named range with the toolbar
The other way to name a range is using the main toolbar.
To create the named range this way, select Data, then Named ranges, then Add a range and give the named range a meaningful name. Directly underneath this, select the grid icon to the right, then select the data to be used in your range and click Done.
I find this method more cumbersome, but it's really up to you.
5. Editing your named ranges
Once you have created your Named ranges, they are easy to edit. Just go to Sheets, Data, Named range, and you will see all the Named ranges you added to your workbook in alphabetical order.
To edit a range, select the Edit button to the right of the Name and either Trash, or delete it, or Edit it and click Done. If you want to change the cell range, a good habit to get into is to actually select the range by clicking the grid to the right, rather than manually changing the cell references. You are less prone to error if you have a visual of your range.
6. Other Common Named Ranges
There are other types of Named ranges that are very useful, like naming a constant to make your formulas easier to read. It's much easier to understand a formula that reads equals income minus expenditure rather than equals A1 minus F1. Constants are just values that have been given a meaningful name.
A dynamic named range is also very popular and will expand and contract according to the data in the range. This is useful for lists that expand and contract regularly.
We are only looking at the basic Named range in this course.
7. Let's practice!
Enough about Named Ranges. We have only scratched the surface, but they are a really good habit to get into when you are working with data.
Let's practice naming tables and lists.