Effective use of Templates in Excel

Purpose

Effective use of templates allows Excel tables to be constructed efficiently, and logically. It is particularly useful for the Excel developers who use mathematical formulae.

In short if you are repeatedly using the same tables, and then reformatting the tables or reapplying the formulae manually then this course is essential to help you be more efficient.

Note that here when I refer to Excel tables I simply mean data that is formatted within a group. I do not mean a structured Excel Table (previously called a list).

Background

This course includes content from an older course on formatting in Excel. Based on feedback it was decided to expand the subject of Templates to become a separate course.

Some of the Excel files used in this course are also provided in the older course and therefore the file names also refer to the previous file names as follows:

• Template1 (Unformatted7)
• Template2 (Unformatted8)
• Template3(Unformattted9)

Section 1 Introduction

We introduce the concepts for templates and outline what will be covered in this course.

Lecture 1 Template Concepts

This lecture explains template concepts in preparation for following lectures.

You will learn:

• The importance of using templates

• Appropriate layouts for templates

• The difference between an original template and a clone template

• Considerations for setting up of easy to follow templates

The corresponding sections are:

2. Using a clone template for a simple set of data.

3. Creating formulae that can be globally applied

4. Using a clone template for more complex data

5. Close

The user is not required to know VBA , named ranges or arrays. These will be contained in extension courses.

Section 2 Using a clone template for a simple set of data

There is an original template, a clone template and progeny tables. You will create an original template and a clone template which can be copied to create progeny tables.

Lecture 2 Creating an original Template

Here a table is set up (using Template1Unformatted7 Excel workbook) that is to be copied to form clones. Here we set up the original template. This allows for efficient redesigning of tables.

You will therefore learn skills in setting up an original template.

Lecture 3 Setting up Clone Template

This lecture demonstrates setting up a clone template in Template 2(Unformatted8) Excel workbook. Here a clone is similar to the original template but has some modifications.

You will therefore learn skills in setting up a clone template.

Lecture 4 Clone Template Use

This lecture demonstrates the use of the clone template to create other tables of the same style in the Template2(Unformatted8) Excel workbook. By doing this Excel is used more efficiently.

You will learn skills in:

• Copying a clone template

• Adjusting a clone template

• Adding data to a clone template

Section 3 Creating formulae that can be globally applied

Often people create formulas that can be applied locally and when copied the formulas have to be modified. This type of strategy is manual and prone to error. You will learn to create formulae so that when they are copied they do not need to be modified. You will find applying this approach is more automated and less prone to error. You will develop a deeper understanding of how to use relative referencing.

Lecture 5 Templates Formula for Status

This lecture demonstrates the use of Templates Formula for status in the Template3(Unformatted9) Excel workbook. You will learn to use nested IF statements applied to text.

Lecture 6 Templates Percent Available

In this lecture we consider what the original developer is trying to achieve. Here a minor redesign is demonstrated in Unformatted9.xlsx workbook by adding a calculation.

Lecture 7 Templates Percent Lost

This lecture demonstrates appropriate redesigning in the Template3(Unformatted9) Excel workbook by making equations much simpler. An equation with some 20 terms is converted to one with about 5. A key part of formatting Excel is to use equations in such a manner that they are as simple as possible. This allows templates to be both efficient and clear.

Section 4 Using a clone template for a more complex dataset

Thus far templates have been based on self-contained data. Here we talk about templates which are linked to external data.

You will learn the concepts of how to create a template so that the progeny tables can be easily modified with respect to the external data sources.

Lecture 8 Application of Templates to an engineering system

The use of templates where there are linkages is the basis of flowsheet modelling, or visual modelling.

Here we explore an example which is used in mineral processing using Complex Template Excel file and Complex Simulation Excel file.

You will learn how to modify progeny tables so as to be able to link to external data sources.

Section 5 Close

In this course we have focused on creating copyable templates. The efficient use of templates allows development of workbooks to be completed much more efficiently than with manual methods.

Lecture 9 Template Close

This lecture reviews the benefits of using templates. A summary of the steps in using templates effectively in Excel is provided.

Lecture 10 Bonus