Named Range in Origin 2022

Introduction

Origin 2022 offers a new feature called “Named Range”. If you are an Excel user, you may be familiar with the concept. Well, now we have them in Origin. For those who are unfamiliar with Named Ranges, they are essentially like a variable that points to a range of cells in a worksheet making it easier to refer to the range or a single cell. That variable can then be used in:

• Set Column Values formula.
• Cell formula.
• Scaler parameter in X-Functions tools
• Reference Line/Function Plot expression

Some features of named ranges (Names) include:

• Assign name to a cell, a range of cells, or a column
• Names can be defined with scope set to sheet, book or project level
• Use the defined names in cell formula and column formula for calculations
• Names can also be used for Reference Lines in graphs
• Name Manager dialog to mange defined names
• Use auto-complete to quickly find and insert functions and range names to complete your formula
• View list of Named Ranges in Object Manger. Double-click to open Named Range Manager dialog.

How to Define and Manage a Name

Defining a Name is simple. There are three methods:

First Method

Select a single or contiguous range of cells and, from the Data menu, choose Define Name… . In the dialog, provide a name for the Name and define its scope. The Name must consist of a minimum of four ASCII alphanumeric characters (A-Z,a-z,0-9), start with a letter,  and contain no spaces or punctuation marks. The scope can be one of Sheet, Book, or Project.

Second Method

Select a single or contiguous range of cells and, from the Formula Bar, enter a Name in the leftmost edit box. When defining the Name in this way, the scope is automatically set to Book.

Third Method

The third method for creating a Name is to use the Mini Toolbar when selecting a single cell. This method only works for single cell selection.

A Cool Trick

Here’s a cool trick: Let’s say you want to define a Name based on a cell and there is a text identifier in the cell just to the left of the selected cell. When defining the Name, the dialog will automatically pick up that identifier as the default name of the Name. Watch:

Managing Named Ranges

Defined Names can be managed via the Name Manager found under the Data menu. The dialog supports changing a Name, Scope, the Refers To cell range, and adding a Comment. Right-clicking allows you to Insert a new Name or Delete an existing one.

Named ranges of active book can also be viewed in Object Manager. Different Icons are used to represent named ranges of different levels. Click on it. It will select the corresponding range in the sheet. Double click it will open Name Manager for further editing.

Using Named Ranges

In Cell formula or Column Formula

As I said in the intro, Names can be used in a number of places, most notably in various types of formula. They are very simple to use — just put them into the formula like this …

total(MyName) + stddev(MyName)

… where MyName is a Name pointing to a range of cells in a column.

Remember scope! The Name is only valid for the scope it is defined for! In fact, we have found that setting Names to Book-level scope is the most practical and useful scope.

In X-Function Tools

E.g. in the following example, define a name for D1 cell as factor (scaler variable). Then in Analysis: Data Manipulation: Reduce to Evenly Spaced X… dialog, specify factor as Rescample Factor to use. The reduced data are put to Column E. If change value in D1 cell, the recalculation icon will turn yellow if recalculation mode is Manual or the output will auto update if recalculation is set to Auto.

This applies to many X-Function tools such as data manipulation, smoothing, which requires some scaler parameters in dialog. The only limitation is that sheet or book scope is only for active, so if you trigger value changes in another book, their scope won’t be used, the active one is used instead

In Reference Line Expression

Define project level name for cell and then use it to define reference line in Graph

Define project level name for cell and use it in function plot expression

Bonus Feature- Names as Functions

Lastly, Names can be used as “lookup functions” for XY column pairs. The idea is that, if you define a Name on an entire Y column, you have the option to Define as Function that Name. Origin will then associate that Y column’s X column forming a “lookup mechanism” for values in the Y column. By default it’s doing linear interpolation. But you can also enter MyFunc(C, bspline) for bspline interpolation.

In the following animation, I Define as Function a Name based on column B. Then, I use that Name as a function in column D to look up values from B based on values in C. It’s easiest to watch!

Conclusion

Well that’s a quick introduction to Named Range in Origin 2022. Thanks for reading!