A named range is one or more cells that have been given a name. Using named ranges can make formulas easier to read and understand. They also provide simple navigation via the Name Box .
In the example, the formula in F6 is:
=MAX(sales)
where “sales” is the named range C4:C10. Other examples of formulas that use this same named range are:
=MIN(sales)
=AVERAGE(sales)
=SUM(sales)
Note: named ranges are absolute references by default.
How to create a named range
To quickly create a named range:
- Select the range
- Type a name into the name box and hit return
Video: How to create a named range .
More detailed information: Named ranges in Excel .
Nesting is the technique of placing one formula or function inside another. The idea is that one function requires a value that can be delivered by another. By nesting a function inside another, and placing the inner function where a function argument would appear, the inner function can pass a result directly to the outer function.
In other words, the general concept of nesting is that the “outer” formula is doing something useful and the “inner” formula is providing something the “outer” formula needs to do the job. Usually, the inner function(s) make it possible to avoid hard-coding information into the “outer” function, since hard-coding makes a formula less useful and portable.
Example
In the example shown, the formula in D4 uses nesting to calculate the current age of a person based on their birthdate:
=INT(YEARFRAC(C4,TODAY()))
In this formula, the TODAY function is nested inside of the YEARFRAC function , which is nested inside the INT function . The TODAY function returns the current date, which is passed into the YEARFRAC function as the “end date”. The start date is provided by cell C4. YEARFRAC returns a decimal value like this in D4:
18.425 // YEARFRAC result on Oct 19, 2019
which is passed directly to the INT function. The INT function removes the decimal portion of the number and returns 18 as a final result.
More examples of nesting
Nesting is a common technique in more advanced Excel formulas. Here are a few more examples:
- Basic INDEX and MATCH
- IF this AND that
- Cell contains specific text
- Working days in year
