Range Names Part 3
Discover how to use a range names in a formula.
When to use
A range name is used to replace a cell reference with text. In spreadsheets, used appropriately and sparingly, great value can be obtained from using range names, as it can make formulae easier to read.
- Highlighting cells C4:F8 and then employing the keyboard shortcut CTRL + SHIFT + F3 would generate the following dialog box:
- Highlighting C4:F8 and using the keyboard shortcut CTRL + SHIFT + F3 once more should generate the Create Names dialog box as above with both ‘Top row’ and ‘Left column’ checked. This means that D4:D8 will be called ‘Apples’, E4:E8 will be called ‘Oranges' and so on. This would take considerably longer to perform manually.
- This example also reinforces why spaces are illegal characters in range names (and for that matter, should not be added to formulae either). Space is the intersect operator in Excel. If you were to type the following formula:
- Excel would return the value in cell E6 (the intersection of the two ranges, above), i.e. $180. This can be a powerful yet quick and simple analytical tool for key outputs.
Lesson notes are only available for subscribers.