This page has 0 threads | Add post

Courseware

Excel Modelling course manual

Here are the chapters included in this course manual (we use exactly the same manuals for our classroom and online Microsoft Excel courses).

Click here to download a sample of the first two chapters of this Excel Modelling manual, or review how to license our courseware.

Chapter 1 - SEVEN MODELLING GUIDELINES▼

Section	Title	Subsections	Page
1.1	Guideline 1 – Use a Cover Sheet		7
1.2	Guideline 2 – Separate Inputs and Calculations	Checking for Rogue Formulae	8
1.3	Guideline 3 – Use Styles		9
1.4	Guideline 4 – Use Range Names		10
1.5	Guideline 5 – Keep Formulae Simple		10
1.6	Guideline 6 – Copy Formulae Across		11
1.7	Guideline 7 – Use Consistent Period Headings		11

Chapter 2 - RANGE NAMES▼

Section	Title	Subsections	Page
2.1	What are Range Names?	Going to a Range Name	12
2.2	Creating Range Names	Method 1 – Using the Name Box Method 2 – Defining Names Method 3 - Creating Range Names from Selected Cells	13
2.3	Range Names in Formulae	Referencing Range Names in Formulae Dealing with Spill Errors The Possible Spill Errors Implicit Intersection using @ Two-Dimensional Range Formulae Getting Aggregation Functions to Work using +	15
2.4	Working with Range Names	Editing and Deleting Range Names Viewing Range Names	19
2.5	The Scope of Range Names	Choosing the Scope of a Range Name Copying Worksheets Copies their Range Names with Local Scope	20
2.6	3-Dimensional Ranges		21

Chapter 3 - STYLES▼

Section	Title	Subsections	Page
3.1	Overview of Styles	Benefits of Styles (1) – Consistent Formatting Benefits of Styles (2) – Protection	22
3.2	Applying Styles	Adding Styles to the Quick Access Toolbar	23
3.3	Creating Styles	Creating Partial Styles	24
3.4	Changing Styles		25
3.5	Merging Styles		26

Chapter 4 - NUMBER AND DATE FORMATS▼

Section	Title	Subsections	Page
4.1	What are Number Formats?	Examples of Number Formats Examples of Date Formats	27
4.2	Formatting Dates	How Dates and Times are Stored Quick Ways to Enter Dates and Times Quick Formatting Dates and Times Selecting Different Date/Time Formats Custom Date Formats Custom Date Formatting Codes Custom Time Formatting Codes Displaying Elapsed Times without Overflow	28
4.3	Standard Number Formats	Standard Number Formats using the Ribbon Standard Number Formats using Format Cells	32
4.4	Custom Number Formats	Creating Custom Number Formats General Rules for Custom Number Formats Example of a Typical Custom Number Format Re-using Custom Number Formats	34
4.5	Tricks with Number Formats	Tricks with Text Hiding Parts of a Number Format Colouring Parts of a Number Format Getting Positive and Negative Numbers to Line Up Getting Decimal Points to Line Up Fractions Scaling Large Numbers Filling Spaces	36

Chapter 5 - CONDITIONAL FORMATTING▼

Section	Title	Subsections	Page
5.1	Overview		39
5.2	Creating Conditional Formats	Applying a Pre-set Conditional Format Creating your own Conditional Format – a Case Study	40
5.3	Editing and Deleting Conditional Formats	Changing the Order in which Rules Apply	42
5.4	More Sophisticated Rules	Picking out Statistical Trends Using Formulae in Rules Using Formulae in Rules to Highlight a Whole Row	43
5.5	Viewing Conditionally Formatted Cells/Rules	Viewing All Conditionally Formatted Cells Viewing all Conditional Formatting Rules	46

Chapter 6 - NOTES▼

Section	Title	Subsections	Page
6.1	Overview of Notes		47
6.2	Working with Notes	Creating Notes Changing the Default User Name Editing and Deleting Notes Formatting Notes Printing Notes	48
6.3	Ways to Show Notes	Hiding Red Triangles Showing/Hiding All Notes Selecting Cells Containing Notes	51

Chapter 7 - DATA VALIDATION▼

Section	Title	Subsections	Page
7.1	What is Data Validation?		52
7.2	Creating Validation Rules	Step 1 – Saying what is Allowed Step 2 – Defining your Punishment for Mistakes	53
7.3	Referencing Formulae in Validation		55
7.4	Dropdown Lists	Step 1 - Creating a Range Name Step 2 – Setting the List	56
7.5	Input Messages		57
7.6	Copying and Clearing Validation Settings	Copying Validation Clearing Validation Settings	58
7.7	Highlighting Validated Cells	Highlighting Cells which have Validation Applied Circling Invalid Data	59

Chapter 8 - PROTECTION▼

Section	Title	Subsections	Page
8.1	Types of Protection		60
8.2	Protecting Worksheets	Unlocking Cells A Note on Unprotecting Cells using Styles Protecting and Unprotecting Worksheets	61
8.3	Protecting a Workbook’s Structure		63
8.4	Password-Protecting Workbooks		64
8.5	Preventing Accidental Changes to a Workbook		65
8.6	Hiding Worksheets	Very Hidden Worksheets	66
8.7	Hiding Cells and Formulae	Hiding Cells Hiding Formulae	67

Chapter 9 - GROUPING (OUTLINING)▼

Section	Title	Subsections	Page
9.1	Using Grouping		68
9.2	Creating Grouping/Outlining	Creating Automatic Outlines Manually Grouping Rows/Columns	69
9.3	Problems with Grouping/Outlining	Changing Outlining Position Using Grouping/Outlining with Protection	70
9.4	Removing Outlining/Grouping	Removing Outlining/Grouping from Selected Rows/Columns Removing All Outlining	71

Chapter 10 - IF FUNCTIONS▼

Section	Title	Subsections	Page
10.1	Overview of IF Functions	Relational Operators	72
10.2	Creating IF Functions	Typing IF Functions Using the Function Wizard	73
10.3	Nesting IF Conditions	Step 1 – Define the Conditions Step 2 – Create the Nested IF Function An Alternative: the IFS function	75
10.4	The SWITCH Function		77

Chapter 11 - INFORMATION FUNCTIONS▼

Section	Title	Subsections	Page
11.1	The CELL and INFO Functions	The INFO Function The CELL Function	78
11.2	Sheet Number / Numbers	The SHEET Function The SHEETS Function	79

Chapter 12 - TEXT FUNCTIONS▼

Section	Title	Subsections	Page
12.1	List of Text Functions	Searching and Replacing Text Extracting one String from Another Converting Strings Other Text Functions Joining Multiple Items Converting Numbers and Dates to Text	80
12.2	Text Functions – a Case Study		83

Chapter 13 - DATE AND TIME FUNCTIONS▼

Section	Title	Subsections	Page
13.1	Returning Date Information	Returning Parts of a Date Returning today’s Date/Time Returning Parts of a Time Converting Numbers/Text into Dates/Times	84
13.2	Manipulating Dates	Returning Different Dates Returning the Difference between Dates	86

Chapter 14 - THE VLOOKUP FUNCTION▼

Section	Title	Subsections	Page
14.1	Types of Lookup Table	Exact Matches or Not? Horizontal or Vertical?	87
14.2	Inexact Matches (Continuous Value Lookups)	Creating the Lookup Table Giving the Lookup Table a Range Name Creating the VLOOKUP Formula	88
14.3	Exact Match Lookup Tables	Syntax of the Exact VLOOKUP Function Creating a Range Name for your Lookup Table Creating the Lookup Formula	90
14.4	Trapping Errors	Method 1 - Preventing Errors Happening with Data Validation Method 2 - Adding Outliers to your Input Table Method 3 – Converting Invalid Values using IF Method 4 – Trap Errors when they Occur	92

Chapter 15 - INSPECTION AND ERROR FUNCTIONS▼

Section	Title	Subsections	Page
15.1	Inspection Functions	Non-Error Inspection Functions Error Inspection Functions	94
15.2	Handling Errors	General Error Trapping using IFERROR Avoiding the Error in the First Place	95

Chapter 16 - THE XLOOKUP FUNCTION▼

Section	Title	Subsections	Page
16.1	Introduction to XLOOKUP	Our Example for this Chapter The XLOOKUP Function Arguments	96
16.2	Using XLOOKUP	Basic Lookups Reversing the Sort Order Using Wildcards Setting a Not Found Value Returning Arrays	96

Chapter 17 - CONDITIONAL STATISTICS▼

Section	Title	Subsections	Page
17.1	Overview of Conditional Statistical Functions	Using IF instead of Conditional Statistical Functions The Special Functions Available	100
17.2	SUMIFS, COUNTIFS, etc	Syntax of the Functions Worked Example Example with Multiple Criteria and Wildcards AVERAGEIF, COUNTIF and SUMIF Using with a Summation Range Combining the Criteria and Summation Range	101

Chapter 18 - LOGICAL FUNCTIONS / MASKING▼

Section	Title	Subsections	Page
18.1	Masking		104
18.2	Logical Functions		105

Chapter 19 - OTHER LOOKUP FUNCTIONS▼

Section	Title	Subsections	Page
19.1	Combining MATCH and INDEX	Reasons to Prefer MATCH/INDEX to VLOOKUP The Range Names for our Example The Syntax of MATCH Using MATCH to Find Row/Column Numbers for our Example Syntax of the INDEX Function Creating the INDEX Function for our Example Combining MATCH and INDEX in a Single Function	106
19.2	The XMATCH Function	The XMATCH Function Arguments	110
19.3	The CHOOSE Function		111
19.4	The OFFSET Function	Basic Offsetting Setting the Number of Rows/Columns Returned	112
19.5	The INDIRECT Function	Example: Picking Out Figures from a Chosen Worksheet	114

Chapter 20 - THE SUMPRODUCT FUNCTION▼

Section	Title	Subsections	Page
20.1	Our Example		115
20.2	Solutions without SUMPRODUCT	Summing the Results of IF Functions Using SUMIFS	116
20.3	The SUMPRODUCT Function	What Summing Products Means Vectors of TRUE / FALSE values Forcing SUMPRODUCT to Calculate True/False Correctly Weighted Averages using SUMPRODUCT	117

Chapter 21 - DYNAMIC RANGE NAMES▼

Section	Title	Subsections	Page
21.1	Examples of Dynamic Range Names		119
21.2	Making a Range Name Dynamic	The Principle: the OFFSET Function Creating the Range Name Using Dynamic Range Names	120
21.3	Dynamic Range Names and Charts	Creating the Range Names Making the Chart Refer to these Ranges	122

Chapter 22 - CASHFLOW▼

Section	Title	Subsections	Page
22.1	Template for a Cashflow Calculation		123
22.2	Calculating Interest	Step 1 – Splitting the Cash Stream into Positive and Negative Amounts Step 2 – Calculating the Deposit/Loan Rate Step 3 – Calculating the Deposit/Loan Amount	124

Chapter 23 - INVESTMENT APPRAISAL▼

Section	Title	Subsections	Page
23.1	Discount Rates		126
23.2	Discounted Cashflow		127
23.3	NPV and IRR Functions	NPV Function IRR Function Using IRR or NPV to Assess an Investment Non-Periodic Cashflows using XNPV and XIRR Modified Internal Rate of Return Function	128

Chapter 24 - MULTIPLE WORKSHEETS▼

Section	Title	Subsections	Page
24.1	Moving between Worksheets		131
24.2	Common Tasks	Selecting and De-selecting Worksheets Renaming Worksheets Changing Worksheet Tab Colours Hiding and Unhiding Worksheets	132
24.3	Inserting and Deleting Worksheets	Inserting Single Worksheets Inserting Multiple Worksheets Deleting Worksheets	134
24.4	Moving and Copying Worksheets	Moving Worksheets within a Workbook Copying Worksheets within a Workbook Moving and Copying to a Different or New Workbook	135
24.5	Group Mode		137
24.6	Summing across Sheets		138

Chapter 25 - SCENARIOS▼

Section	Title	Subsections	Page
25.1	Overview of Scenarios		139
25.2	Creating a Scenario	Base case scenarios Starting the Scenario Manager Creating a Scenario	140
25.3	Showing Different Scenarios	Showing Different Scenarios using the Ribbon Switching Scenarios using the Quick Access Toolbar	142
25.4	Editing, Deleting and Merging Scenarios	Editing or Deleting Scenarios Merging Scenarios	143
25.5	Summarising Scenarios		144

Chapter 26 - GOAL-SEEKING▼

Section	Title	Subsections	Page
26.1	Goal-Seeking Example		145
26.2	Using Goal-Seeking		146

Chapter 27 - DATA TABLES▼

Section	Title	Subsections	Page
27.1	Overview of Data Tables	One-Way and Two-Way Data Tables Example of a Data Table	147
27.2	One-Way Data Tables	Step 1 – Create your Formulae Step 2 – Create your Inputs Step 3 – Create the Data Table Itself	148
27.3	Two-Way Data Tables	Step 1 – Create your Formula Step 2 – Create your Inputs Step 3 – Create the Data Table	150

Chapter 28 - MODELLING SHORT-CUTS▼

Section	Title	Subsections	Page
28.1	Creating a Row of Data		152
28.2	Other Keyboard Shortcuts	Shortcuts for Moving and Selecting Shortcuts for Formatting Shortcuts for Range Names Shortcuts for Formulae and Data	154

If you book a place on one of our courses you will automatically get access to the relevant courseware manual online, and also have the option to receive a paper copy of it.

This page has 0 threads

Add a new post

Choose software

Excel course manuals