Effective Use Of Templates In Excel

What you'll learn
Construct Excel tables efficiently, accurately and logically.
Be able to create templates with appropriate layouts.
To set up templates that are easy for others to follow.
Develop skills to set up an original template and a clone template
Redesign tables efficiently.
Appropriate redesign of templates by making equations much simpler.
Use templates where there are links to other tables.
Requirements
Access to Microsoft Excel is needed. No former experience with Microsoft Excel is necessary.
You should be familiar with basic formula skills. This would include relative referencing (i.e. using the $ sign in an equation).
This course is only applicable to Windows based systems.
Description
PurposeEffective 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).BackgroundThis 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 IntroductionWe introduce the concepts for templates and outline what will be covered in this course.Lecture 1 Template ConceptsThis 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 templatesThe corresponding sections are:2. Using a clone template for a simple set of data.3. Creating formulae that can be globally applied4. Using a clone template for more complex data5. CloseThe 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 dataThere 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 TemplateHere 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 TemplateThis 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 UseThis 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 templateSection 3 Creating formulae that can be globally appliedOften 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 StatusThis 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 AvailableIn 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 LostThis 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 datasetThus 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 systemThe 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 CloseIn 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 CloseThis lecture reviews the benefits of using templates. A summary of the steps in using templates effectively in Excel is provided.Lecture 10 Bonus
Who this course is for
This course is designed for people who use Excel professionally.,People who need to understand Excel workbooks that they work on or create workbooks that others use.,People who create Excel workbooks which may have a series of data flows or calculations.
Homepage

https://www.udemy.com/course/effective-use-of-templates-in-excel/