What is a Defined Name?
In Excel, you have the option to assign a Name to a cell, or to a Range of cells. This name is reffered to as a Defined Name.
A Defined Name will give more flexibility than before, you can refer to it in your formulas, or chart series.
Also, the name can be located anywhere in your workbook, you don’t need to use cell or range references.
There are 3 Reference Operators in Excel:
- “:” (colon) is the Range operator, which produces one reference to all the cells between two cells references, including the cells written in the reference text, like: A11:A13
- “,” (comma) is the Union operator, which combines multiple Ranges into one reference, like A11:A13 , A11:C11 , A11 (you can refer to simple cells too, not only to ranges with multiple cells)
- ” ” (space) is the Intersection operator, which returns a reference to the cell or to the range of cells found at the intersection of the ranges. In this example, only cell A11 is found in both ranges: A11:A13 A11:C11
How can the Cell Modes affect your work?
When you try to edit a Conditional Formatting formula, and you click the left or right arrows to move across formula’s text, Excel inserts unwanted cell references. Same thing happens when you try to create or edit a Defined Name formula, or a Chart series…
If you are not aware of the active Cell Mode, then working with formulas, defined names formulas or conditional formatting formulas, editing chart series can become very annoying.