Login
Excel Yourself/The Complete Guide To Range Names 2022
Buy now

The Complete Guide to Range Names

Range names are a powerful Excel feature. It is however a topic that splits the Excel community. Some people think they add extra complexity to a file. I believe, when they are used correctly, that they add both structure and flexibility to your models and are a worthwhile addition to your Excel toolbox.

You can make up your own mind when you see all the advantages with using range names
  • formulas are easier and quicker to create
  • formulas are easier to read, maintain and modify
  • reduced use of the $ signs in formulas
  • you can define specific range and make selecting then a two click process
  • range work well with macros
  • create ranges that automatically expand or contract
This course includes recordings from two separate live range name webinars plus some bonus range name content. As an added bonus I have included a recording of a live webinar I ran on Formatted Tables. Formatted Tables work brilliantly with range names.

Range Name Essentials
Learn the basics, plus some powerful techniques to make your range names more dynamic. This session has everything you need to get started with using range names effectively.

Even if you have been using range names this session provides some useful shortcuts and techniques that you may not be aware of. This Essentials section covers
  • the three ways to create a name (some are much easier than others)
  • naming rules and suggestions
  • keyboard shortcuts
  • name intersections
  • formula-based names
  • using names with formatted tables

Advanced Range Names
The Advanced section covers techniques that can be applied to reporting models, dashboards, budgets and forecasts. The Advanced section covers
  • Naming conventions – what they are and how to use them to simplify formula creation
  • Non-formula uses for range names – create an unbreakable hyperlink
  • Linking between files – technique to reduce broken, or corrupted links
  • Advanced techniques – creating a sheet name range name and some advanced ways to refer to ranges
  • The INDIRECT function and range names
  • One Formula to Rule Them All – an amazing technique that can be applied to many situations – a single formula to extract Actuals, Budgets and Forecasts
  • Advanced range reference techniques

Level: Intermediate
Duration: 3+ hours with 1+ hours bonus
Price: AU$49

  • $49

The Complete Guide To Range Names 2022

Learn all about range names, includes bonus content.
Buy now

Contents

Range Name Essentials

Learn the basics, plus some powerful techniques to make your range names more dynamic. This session has everything you need to get started with using range names effectively.

Even if you have been using range names this session provides some useful shortcuts and techniques that you may not be aware of.

This session covers
  • the three ways to create a name (some are much easier than others)
  • naming rules and suggestions
  • keyboard shortcuts
  • name intersections
  • formula-based names
  • using names with formatted tables
As always, I will share a few general tips along the way.
Range Name Essentials 2022.pdf
  • 562 KB
Range Name Essentials 2022.xlsx
  • 144 KB
Range Name Essentials Video Recording
  • 60 mins
  • 200 MB
Range Name Essentials 2022 completed.xlsx
  • 149 KB
Range Name Essentials: Quiz

    Advanced Range Names

    This session covers many advanced techniques. These techniques can be applied to reporting models, dashboards, budgets and forecasts.

    The session covers
    • Naming conventions – what they are and how to use them to simplify formula creation
    • Non-formula uses for range names – create an unbreakable hyperlink
    • Linking between files – technique to reduce broken, or corrupted links
    • Advanced techniques – creating a sheet name range name and some advanced ways to refer to ranges
    • The INDIRECT function and range names
    • One Formula to Rule Them All – an amazing technique that can be applied to many situations – a single formula to extract Actuals, Budgets and Forecasts
    • Advanced range reference techniques
    Advanced Range Names 2022.pdf
    • 595 KB
    Excel Advanced Range Names 2022.xlsx
    • 144 KB
    Advanced Range Names Video Recording
    • (1h 02m 31s)
    • 225 MB
    Excel Advanced Range Names 2022 completed.xlsx
    • 148 KB
    Advanced Range Names: Quiz

      Custom Functions and Range Names

      This new session covers recent developments in Excel that allow you to create custom functions without VBA (macros). You will need the subscription version of Excel to follow along with this session. 

      The technique uses the new LAMBDA function and range names. A few other new functions are also covered.
      In this session we will cover
      • the advantages of custom functions
      • the LET function (it works well with LAMBDA)
      • how to create a LAMBDA and test it
      • how to create a custom function using range names and LAMBDA
      • how to document a custom function
      • how to solve an issue with dynamic arrays and summing
      • free resources to assist with custom functions
      • a macro to better document your custom functions
      As always, I will share a few general tips along the way.

      Custom Functions and Range Names 2022.pdf
      • 869 KB
      Custom Functions and Range Names 2022.xlsx
      • 249 KB
      Custom Functions and Range Names Video Recording
      • (1h 29m 19s)
      • 365 MB
      Custom Functions and Range Names Quiz

        Bonus Content

        Bonus 1 - Dynamic Names Bonus video
        • 6 mins
        • 31.5 MB
        Bonus 1 - Dynamic_Charts.xlsx
        • 20.8 KB
        Bonus 1 - Dynamic_Charts_completed.xlsx
        • 27.1 KB
        Bonus 2 - Deleting Duplicated Names in Excel.pdf
        • 165 KB
        Bonus 2 - Duplicate_Names.xlsb
        • 20.1 KB
        Bonus 2 - RemoveDuplicateNames video
        • 6 mins
        • 23.3 MB
        Bonus 3 - Retrofit a Factor to an Excel Budget.pdf
        • 262 KB
        Bonus 3 - Factors.xlsx
        • 11.9 KB
        Bonus 3 - RetroFit_Factor video
        • 5 mins
        • 24.1 MB
        Bonus 3 - Factors_After.xlsx
        • 13.1 KB

        Bonus - Format As Table Webinar Recording

        In this section you will learn all about Excel’s formatted tables.

        Many of Excel’s features and functions work seamlessly with formatted tables. They can help you improve the structure and reliability of your spreadsheet files.

        Formatted tables can allow you to create powerful reports like those in a relational databases.

        Topics covered
        • advantages and limitations of formatted tables
        • keyboard shortcuts
        • using formatted tables with formulas
        • solutions to some of the limitations of formatted tables
        • using range names with formatted tables
        • using formatted tables with data validations
        • creating a running total
        • using PivotTables
        • Relationships (Data tab)
        As always I will share a few other tips.
        Format as Table 2020.pdf
        • 488 KB
        Excel Format As Table 2020.xlsx
        • 540 KB
        FormatAsTable video
        • (1h 05m 32s)
        • 272 MB
        Excel Format As Table 2020 completed.xlsx
        • 1.03 MB