The following list is a list of all the documents changed during the last three months.  They are tagged New if created in the past month and updated if they have been updated in the past three months...

Understanding Range References and Address Modes (Login reuired) 

This Excel Fundamentals article explains how the addressing modes work in Excel and how to understand range references in expressions.

Value Types In Excel (Ready Reference 4.0) - Test Page (Login reuired) 

This Ready Reference article lists and describes the value types that can be stored in Excel, together with the limits to those values.

Understanding Functions (Login reuired) 

This Excel Fundamentals article explains how functions operate and how they are used in workbook formulas.

Understanding Defined Names (Login reuired) 

This Excel Fundamentals article explains how Defined Names are created and how they are used for navigation and in formulas.

Understanding Excel Values and How They Are Displayed (Login reuired) 

This Excel Fundamentals article explains how Excel stores values and how they are displayed. It covers basic understanding of formatting.

Understanding Excel Formulas (Login reuired) 

This Excel Fundamentals article explains the basic workings of Excel formulas.

Module Twenty-Nine - Other Uses for Data Tables (Login reuired) 

In this module, an alternative approach to cumbersome complex calculations is introduced, using a set of sample calculations for one member of a set and the Data Table feature to repeat the calculations for all members of the set.

Module Twenty-Eight - PivotTable Reporting (Login reuired) 

In this module, the lookup functions are introduced, and their usage demonstrated. The use of lookup functions is compared and contrasted to reference functions.

Module Twenty-Seven - PivotTables Introduced (Login reuired) 

In this module, the lookup functions are introduced, and their usage demonstrated. The use of lookup functions is compared and contrasted to reference functions.

Module Twenty-Six - Using Linked Graphics to Present Data (Login reuired) 

In this module, the lookup functions are introduced, and their usage demonstrated. The use of lookup functions is compared and contrasted to reference functions.

Module Twenty-Four - Dynamic Array Formulas for Analysis and Tabulation (Login reuired) 

This module introduces the use of dynamic array formulas for building analytical reports, including tabulating summaries and building cross-tabulations.

Module Twenty-Three - Introduction to Dynamic Names (Login reuired) 

This module introduces a technique for defining names whose referenced ranges change dynamically as the entries in the referenced worksheets change. This is a powerful technique that allows efficient references to datasets whose dimensions change without the need to be constantly editing formulas or redefining names.

Module Twenty-One - Introduction to Array Formulas and Functions (Login reuired) 

Excel has provided array formula functionality since its earliest days, but the introduction of dynamic arrays in Office 365 has seriously improved the power of array calculations, whilst making them vastly easier to understand and manage.

Module Twenty - String Manipulation Functions Introduced (Login reuired) 

The text string manipulation functions allow the modeller to modify, compose and break-down information stored in text strings. They can be useful in creating dynamic headings, labelling charts, converting data for lookups and adding content to presentation elements.

Review Exercise One Sample Solution (Login reuired) 

Once you have attempted the review exercise, this article will become available, giving you access to the step-by-step instructions for a sample solution.

Module Eighteen - Adding Scenario Management Scenario Analysis and Data Table Summary (Login reuired) 

In this module, we will add a scenario management mechanism to the model, and then use a data table to provide scenario comparison of all the scenarios run simultaneously.

Module Nineteen - Introduction to Lookup Functions (Login reuired) 

In this module, the lookup functions are introduced, and their usage demonstrated. The use of lookup functions is compared and contrasted to reference functions.

Module Seventeen - Matching and Referencing Functions Introduced (Login reuired) 

Excel provides a range of functions for locating and retrieving data from tables and calculation blocks. This module introduces the matching and referencing functions. In a later module, the related lookup functions will be introduced.

Module Sixteen - Introduction to Data Validation (Login reuired) 

A useful addition to Excel back in 1997 was the Data Validation setting and associated commands that allows the workbook builder to assign validation rules to limit the entries in cells and identify those cells that do not meet validation requirements.

Module Fifteen - Introduction to Custom Number Formatting (Login reuired) 

In this module, we will introduce custom number formatting and demonstrate the use of custom number formatting strings to change the appearance of values in cells by modifying styles in the Pacific Juices forecasting model.

Module Thirteen - Conditional Functions including IF, IFS and SWITCH (Login reuired) 

This module introduces alternatives to the IF function for handling conditional expressions. Two new functions were introduced in 2016, but we have not seen them widely adopted, but they are very powerful when applied to a number of multi-conditional calculations.

Module Twelve - Adding Variability to Sensitivity Inputs (Login reuired) 

In this module, we will look at the relatively simple, yet not so obvious wiring needed to make the inputs to the sensitivity table variable, based on user manageable settings.

Module Eleven - Data Tables Introduced - Sensitivity Made Easy (Login reuired) 

Two tasks concern us in carrying out sensitivity analysis. The first is managing the data that drives the various alternatives, and the second is comparing the possible outcomes. Out of the box, Excel provides an extremely powerful feature that makes sensitivity analysis relatively simple.

Module Ten - Avoiding Mistakes with Well Designed References (Login reuired) 

Defined names used judiciously aid in workbook construction and auditing. They also provide a home for more advanced calculations possible in Modern Excel. In this module, we will use defined names to identify the key drivers and critical elements in the model.

Module Nine - Defined Names Introduced (Login reuired) 

Defined names used judiciously aid in workbook construction and auditing. They also provide a home for more advanced calculations possible in Modern Excel. In this module, we will use defined names to identify the key drivers and critical elements in the model.

Module Eight - Model Layout and Segregation (Login reuired) 

Developing a clear and consistent layout for models is one of the most important tools for controlling construction and referential errors and ensuring that a model is as easy to maintain as possible. An added benefit is that a well laid-out model is also the easiest kind for adding scenario management, sensitivity analysis, charting and dashboarding.

Module Seven - Understanding and Working with Dates & Times in Excel (Login reuired) 

A critical element in modelling is the passing of time, and its impact on valuation and the timing of transactions. In this module we will examine the way in which Excel stores and calculates dates and times. We will examine key functions for working with dates and building dynamic titling and calculation elements for forecasting and modelling,

Module Five - Range Addresses and Reference Absoluteness (Login reuired) 

In this module we will discuss and explore the issues around address modes and the use of absolute, row-absolute, column-absolute, and relative addresses.

Module Four - Consolidating Multiple Worksheets (Login reuired) 

In this module, we will complete the model commenced in the previous task. It demonstrates a technique for working with consolidation of workbooks with multiple sheets all sharing a common layout. It reinforces the learnings from the previous module about pointing techniques when building formulas.

Module Three - Effective Techniques for Building Formulas (Login reuired) 

This module which introduces our first model, concerns itself with the basics of building formulas and ensuring consistency in those formulas. It builds on the techniques introduced in the previous module for pointing and selecting. Those techniques will be used for selecting ranges and sheets for input, and in selecting ranges referenced by the formulas.

Module Two - Effective Technicques for Range Selection and Navigation (Login reuired) 

In this module I will demonstrate important and effective techniques for navigating in a worksheet and workbook. These techniques are fundamental to being able to build formulas and work successfully and productively within Excel without unintended errors arising.

Our function reference article on the use and troubleshooting for the T function.

T01 - Function and Formula Fundamentals (Login reuired) 

This module of the Key Topic Lecture Series introduces important fundamental information about working with functions and formulas in Excel. It introduces and demonstrates the use of key formula entry and effective ways to simplify and streamline conditional expressions. It introduces the use of key functions for deriving values in models and analyses.

T02 - Advanced Analytic Formulas (Login reuired) 

The use of dynamic references, dynamic names, advanced conditional expressions, array formulas is canvassed in this session. Techniques to avoid redundant and complex calculations which directly impact on model size and performance are discussed and demonstrated. It also presents techniques for working with formulas to build more flexible analyses of performance data and structuring workbooks to facilitate these analyses.

T07 - Formula Auditing Error Tracing and Handling (Login reuired) 

This module of the Key Topic Lecture Series introduces important fundamental information about working with functions and formulas in Excel. It introduces and demonstrates the use of key formula entry and effective ways to simplify and streamline conditional expressions. It introduces the use of key functions for deriving values in models and analyses.

M01 - Fundamentals of Model Design and Development in Excel (Login reuired) 

This module demonstrates the rapid design and development of a forecasting model from scratch, given a number of key assumptions, implementing design elements to facilitate model maintenance and then a powerful technique for sensitivity analysis is introduced, incorporating a number of scenarios in the one model.

M02 - Implementing Complex Scenarios in Cash Flow Models (Login reuired) 

Given some starting assumptions, it builds a sample construction cash flow from scratch, implementing scenarios in which the costs are variable as to amount, timing, phasing and in which the cash flows can be automatically allocated on an S Curve basis. Finally it implements sensitivity analysis to allow the comparison of all scenarios within the one model.

M03 - Building Valuation Models with Less Complexity (Login reuired) 

This module demonstrates the rapid design and development of a valuation model together with an implementation of a flexible scenario management system, based on simply copying an assumptions worksheet.

Module Twenty-Five - The LET Function for More Complex Array Calculations (Login reuired) 

In this module, the lookup functions are introduced, and their usage demonstrated. The use of lookup functions is compared and contrasted to reference functions.