Effective Use of Named Ranges in Excel

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.

You will explore concepts such as ensuring that mathematical models in an Excel workbook are clearly defined, as well as associated inputs, outputs and parameters.

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.

You will learn how to create a workbook Named Range using two approaches:

1. Creating a Named Range using the Name Box

2. Creating a Named Range using the Name Manager.

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

Worksheet Named Ranges have less ‘scope’ than workbook Named Ranges. In this lecture we discuss what is meant by scope. Put simply 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 learn to create a worksheet Named Range and access the Named Range from different worksheets.

Data Validation is a way of ensuring that cell inputs are restricted to specified values. This ensures consistency. That is, 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 learn 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.

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 you to visually identify the cells that make up a Named Range

You will identify dataflow using a flowchart with a view to creating 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

You will therefore learn to create and apply Named Ranges so that all existing formulae in the worksheet use Named Ranges and there is no A1 notation.

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.

You will therefore learn to copy a worksheet (that is a template) and by doing so also copy correctly the associated Named Ranges and equations.

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).

If a participant has a pre-Office 365 version then the objective is to apply an array equation.

If a participant does not have Office 365, they are informed of what to do should they be required to use an early version.

In this lecture you will be able to summarise what you have achieved in Part 1 of the course:

• 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.

You will review the advantages of using Named Ranges.

This lecture explains the 5 sections in Part 2 of the course: the introduction, VBA, Named Ranges, Arrays and the close. You will be introduced to the importance and advantages of linking VBA, Named Ranges and Arrays.

In this lecture we provide a simple function in which Named Ranges and Arrays will later be applied using VBA. The explained problem is fundamental to mineral processing. It is a very simple problem that anyone with basic maths skills can understand. You will understand the formula that will later be used to explain VBA, Named Ranges and Arrays.

You will be able to set up a basic function in an Excel spreadsheet in order to later apply VBA, Arrays and Named Ranges.

VBA is visual basic for applications. This is not a VBA course. You will identify the part of VBA to be used for linking arrays and Named Ranges.

In this lecture you will apply a simple function (not using either Named Ranges or arrays) to the specified problem. You will learn how to create a simple function.

We don't want to repeat the same code in different workbooks instead we want to create an add-in which can then be applied by different workbooks. In this lecture you will learn how to create an add-in.

Once an add-in is created you can apply it to different workbooks.

In this lecture you will create a simple function to estimate the standard deviation based on the confidence of a measurement’s accuracy. You will create the function and add it to an add-in, and then apply it to a workbook.

In this lecture we extend the code of the previous lecture based on more options for the confidence of a measured variable. Here we introduce the select Case method which is a more elegant approach than nested if statements. You will create the function, add it to the add-in and apply it to a workbook.

You will develop an overall understanding of what object oriented programming is. In the context of VBA, you will gain a basic idea of what object oriented programming is. By getting an object we gain access to all methods, properties and events associated with that object. The most common object is a worksheet object. You will have opportunity to practice using objects.

When we create VBA functions, we want them to be efficient. We don't want to repeat the same calculations over and over again. If we set up parameters as global variables they can be accessed once and then utilised repeatedly creating VBA error.

Spooling was incorporated in Excel 365. This made Arrays much easier to use. Spooling automatically determines the cells where an array function is to be applied.

Thus far, functions have been very simple. In an array function we want to be able to apply a function to a set of data rather than one cell at a time. This is why we use arrays. You will understand what is an array and why it is necessary to create VBA array functions.

You will be able to create VBA functions that are applied to arrays. You will use an example of an array function applying it to the simple problems already specified.

Named Ranges are useful if we want to avoid A1 notation so that the functions have more meaning and therefore are easier to understand. You will review how to create Named Ranges.

VBA isn't directly connected to Excel. That is, if we change the position of data in Excel and we refer to it in VBA it may cause an error. By using Named Ranges we can make the VBA code easier to understand and less error prone. In this lecture you will use Named Ranges in VBA code.

Named Ranges are applied to a set of data but suppose we want to copy that data onto the same worksheet to create a new set of data. We can do that but we can't simultaneously copy the Named Range. An add-in is provided by the instructor that you can use to copy Named Ranges within a worksheet.

In normal Excel, if we copy an array function which is applied to A1 notation data it should apply correctly to the new copied data.

But it won't do that if we use Named Ranges so again we have to come up with a strategy in order to apply any copied array function that uses Named Ranges to be applied to the new copied Named Ranges. You will learn an effective strategy using Find and Replace.

This course has been introductory only but it shows the advantages of linking your arrays, VBA and Named Ranges.

You will learn about implementing Excel Engineering methods in your workplace.