Effective use of Named Ranges

Description

Section 1 Introduction

Excel has a functionality called “Defined Names”. In general Defined Names can have multiple uses. The main use is to give a name to a set of cells. This is called a Named Range; and is the focus of the course.

Section 1 is an introduction. Section 1 is split into two lectures:

1. An overview of the course, and a brief explanation of the benefits of Named Ranges

2. Modelling concepts.

Lecture 1 Effective Use of Named Ranges Introduction

Provides an overview of the benefits of using Named Ranges and briefly describes what is covered in the entire course.  Advantages of effective use of Named Ranges are introduced:

• Reduced error

• Improved clarity of the workbook

• Improved automation

• Improved workbook navigation

These outcomes are referred to as the ‘listed advantages’.

In brief there are two ways to use Excel (from a modelling viewpoint). The first way is to use A1 notation (or formula referencing). The second way is to use Named Ranges. The most common approach is A1 notation, and the second approach is to use A1 notation with only some Named Ranges. A dedicated Named Range approach is uncommon.

Lecture 2 Modelling Concepts

Although there are many courses that discuss Named Ranges. There are very few (if any) that consider how to use Named Ranges effectively. That is many Excel users are able to use Named Ranges, but very few can use them efficiently to achieve the listed advantages. Understanding how to use Named Ranges effectively commences by first considering modelling concepts. This lecture is conceptually mathematical but does not require you to use mathematics per say.

The lecture simple explores concepts such as ensuring that mathematical models in a workbook are clearly defined, as well as associated inputs, outputs and parameters.

Section 2 Methods

The methods section includes the fundamental methods of how to use Named Ranges effectively.

Skills taught include:

• Creating workbook-scoped Named Ranges

• Avoiding error using Named Ranges

• Creating worksheet-scoped Named Ranges

• Applying Data validation using Named Ranges.

Clearly there are two types of Named Ranges: workbook scope and worksheet-scope.

The advantages of one type of scoped Named Range over another are also discussed.

Lecture 3 Creating workbook-scoped Named Ranges

Workbook-scoped Named Ranges can be easily applied throughout an entire workbook.

For beginners using Named Ranges, creating workbook-scoped Named Range is the most common approach.

In this lecture it is explained how to create a workbook Named Range using two approaches:

• Creating a Named Range using the Name Box

• Creating a Named Range using the Name Manager.

Lecture 4 Avoiding error using Named Ranges

There are many sources of error when using Excel. Perhaps the most common source of error is caused by interworkbook linkage (where cells from one workbook are linked to another workbook).

In this lecture we discuss how the problem is avoided using Named Ranges.

You will learn:

• How to create interworkbook linkage.

• How error is created by not using Named Ranges

• How to link data from different workbooks using Named Ranges

• How error is avoided by using Named Ranges

Lecture 5 Creating worksheet Named Ranges

Worksheet Named Ranges have less ‘scope’ than workbook Named Ranges. In this lecture we discuss what is meant by scope. Simply put, a worksheet-scoped Named Range is immediately seen only for a single worksheet.

However even though it is only seen immediately in a worksheet it can still be applied to different worksheets. In this lecture we discuss the advantages and disadvantages of using worksheet Named Ranges compared to workbook Named Ranges. You will create a worksheet Named Range.

Lecture 6 Data Validation using Named Ranges

Data validation is a way of ensuring that cell inputs are restricted to specified values. This ensures consistency. i.e. If a data validation list includes ‘No’ but not ‘false’, it is much easier to later interrogate the results if the answers are consistent.

There are many uses of Data Validation but here we focus on Lists.

You will be taught how to create a data validation list with:

• unnamed cells,

• workbook –scoped Named Ranges,

• worksheet-scoped Named Ranges

However using worksheet Named Ranges for data validation in different worksheets is a little bit tricky and it may be necessary to create a ‘defined name’ that corresponds to a worksheet-scoped Named Range.

Section 3 Practical Use of Applying Named Ranges

In this section we apply Named Ranges to a worksheet that uses A1 notation.

We convert all equations to Named-ranged based equations.

Lecture 7 Creating a flowchart for Named Range design

The workbook and worksheet for which the Named Ranges are to be applied are introduced.

The particular worksheet is a template and is later to be copied.

The next step in applying Named Ranges is to create a flowchart. This allows one to visually identify the cells that make up a Named Range.

Lecture 8 Creating and applying Named Ranges

Using the flowchart design, Named Ranges are created. At this stage Named Ranges can have workbook-scope.

The Named Ranges are then applied to the existing formulae.

All formulae are converted from A1 notation to Named Range notation meaning that the worksheet is now:

• Easier to understand

• Less error prone

• More automated

Lecture 9 Copying Named Ranges

Excel is not specifically designed for copying Named Range within a worksheet. However if one copies a worksheet, then all Named Ranges (whether workbook or worksheet scope) are also copied.

This means that if a worksheet is a template, then copying the worksheet allows all Named Ranges and equations to be copied.

In this lecture we copy a worksheet and notice that all equations are copied correctly.

Section 4 Close

In this section we include 3 lectures:

• Using arrays for early versions of Excel

• Summary of course

• Lecture 12

Lecture 10 Using arrays for early versions of Excel

Excel has substantially improved over the years, particularly in the handling of arrays. Indeed arrays is now so effective many Excel users are unaware they are using arrays.

An array is when an equation is applied to a set of data rather than individual cells, which are then autofilled to different cells. That is, the autofill is applied automatically by Excel rather than the user.

In earlier versions of Excel array handling is a bit more manual with the user required to select the range of data when an array equation is to be applied.

In this lecture we apply an array equation using early versions of Excel.

(Here it appears that later versions of Excel are part of Office 365, whereas earlier versions are pre-Office 365).

Lecture 11 Summary

In this lecture you will be able to summarise what you have achieved:

• How to create Named Ranges (both workbook and worksheet scope).

• How to use Named Ranges for Data Validation

• How to identify appropriate Named Ranges by considering a flowchart diagram

• How to apply Named Ranges to a worksheet in order to make all formulae based on Named Ranges including how to use array equations

• How to copy Named Ranges via copying a worksheet.

The result of those skills is that your workbooks are:

• easier to understand,

• less error-prone,

• and more automated.

Lecture 12

TAKE THIS COURSE