How to Use Named Ranges in Excel

Using Named Ranges in Excel

1. Start with your assumption inputs

• Set up a small assumptions area with values such as sales tax, commission rate, average ticket, and emergency price multiplier
• These are the kinds of cells that benefit most from named ranges because they are referenced repeatedly throughout a model

2. Create a named range for a single cell

• Click the cell you want to name
• Go to the Name Box to the left of the formula bar
• Type a clear business name such as Sales Tax
• Press Enter to save it

This replaces a generic cell reference with a label that is much easier to understand in formulas.

3. Use the named range in a formula

• Reference the named range directly instead of using the cell address
• In the video, the assumption cells were used this way so formulas could read like business logic rather than spreadsheet coordinates
• This makes models easier to read, review, and maintain

4. Create named ranges from a labeled input section

• Highlight the range that includes labels and values
• Go to the Formulas tab
• Open Create from Selection
• Choose the option that matches where the labels are located, such as the left column

This quickly creates multiple named ranges at once for inputs like average ticket and emergency price multiplier.

5. Create named ranges for a full data table

• Highlight the table
• Go to the Formulas tab
• Choose Create from Selection
• Use the top row as the source for the names

This allows each column header to become a named range so you can reference entire columns by name.

6. Use named ranges with summary functions

• Once the table columns are named, you can use them directly with functions such as SUM and COUNTA
• In the video, this was used to total invoice values, total parts revenue, and count the number of jobs
• This makes summary formulas much more readable than standard cell or column references

7. Use named ranges inside business logic formulas

• Named ranges also work well with decision logic using functions like IF
• In the example, a commission calculation was built using a job type condition and a commission rate assumption
• Because the ranges had meaningful names, the formula clearly showed what was happening without needing to decode cell references

8. Use named ranges to build more readable calculations

• The video showed an example where total invoice was combined with sales tax and then divided by a count field to calculate an average result
• With named ranges, the formula reads more like plain business language
• This is especially useful when sharing files with other people or revisiting a model later

9. Add more named ranges as your model grows

• You can continue naming new inputs one at a time from the Name Box
• In the video, hourly rate was used as another example of a value that could be added later and named the same way
• This keeps the workbook organized as more assumptions and metrics are introduced

10. Manage, edit, or delete named ranges

• Go to the Formulas tab and open Name Manager
• From there, you can review all named ranges in the workbook
• You can edit names, update references, or delete ranges you no longer need

This is helpful when cleaning up a model or checking how named ranges are connected.

11. Understand workbook-level and sheet-level naming

• Named ranges can apply across the full workbook or only within a specific sheet
• In the video, this was mentioned as a useful option when the same term might exist in different sheets
• This gives you more control over how names behave in larger files

12. Compare readability against normal references

• The final comparison in the video showed the difference between a formula built with named ranges and one built with standard references
• Even when both formulas return the same result, the named-range version is much easier to interpret
• That is the main advantage of this approach: clearer formulas, faster auditing, and better long-term maintenance