This page has 0 threads | Add post

Courseware

Advanced Excel 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 Advanced Excel manual, or review how to license our courseware.

Chapter 1 - FORMULAE AND FUNCTIONS▼

Section	Title	Subsections	Page
1.1	Basic Formulae	Operators Brackets in Formulae	9
1.2	Creating Formulae		10
1.3	Editing Formulae		11
1.4	Copying Formulae	The Easiest Way to Copy a Formula Other Ways to Copy a Formula How Relative Cell Referencing Works	12
1.5	Functions	Basic Functions Structure of a Function Typing a Function Using AutoSum to Create Quick Totals, Averages, Etc. The Quickest Way to Sum The Function Wizard	14
1.6	Status Bar Calculations		18

Chapter 2 - MOVING AND SELECTING IN EXCEL▼

Section	Title	Subsections	Page
2.1	Moving Around in Excel	Using the Mouse to Move Around Keyboard Shortcuts for Moving Around Zooming the View	19
2.2	Selecting Cells	Selecting Single Cells Selecting a Range of Cells Selecting a Large Range of Cells Selecting Multiple Ranges Selecting Entire Rows and Columns Selecting an Entire Worksheet De-Selecting Cells Using the Keyboard to Select Cells	21

Chapter 3 - ABSOLUTE AND RELATIVE REFERENCES▼

Section	Title	Subsections	Page
3.1	Relative Referencing		25
3.2	Absolute References	When Relative Referencing doesn’t Work The Solution – Use Absolute References Fixing Just the Row or Column	26

Chapter 4 - RANGE NAMES▼

Section	Title	Subsections	Page
4.1	What are Range Names?	Going to a Range Name	28
4.2	Creating Range Names	Creating Range Names using the Name Box Defining Range Names	29
4.3	Creating Formulae using Range Names	Creating Formulae using Range Names (General) Creating Formulae using Range Names for Single Cells Editing Range Name Formulae	30
4.4	Spill Formulae	Creating Formula using Range Names for a Group of Cells - SPILL How Spill Formulae Work Preventing Excel using SPILL Implicit Intersection Opening Old Excel Workbooks using Implicit Intersection Dealing with #SPILL Errors	32
4.5	Changing Range Names	Editing and Deleting Range Names Avoiding the Need to Change Range Names	36
4.6	Creating Range Names from Selected Cells		37
4.7	Scope of Range Names	Choosing the Scope of a Range Name Copying Worksheets Copies their Range Names with Local Scope	38
4.8	Managing Range Names	Listing out Range Names Applying Range Names	39
4.9	Quirks of Range Names	Getting Aggregation Functions to Work Viewing Range Names 3-Dimensional Ranges	40

Chapter 5 - IF FUNCTIONS▼

Section	Title	Subsections	Page
5.1	Overview of IF Functions	Relational Operators	42
5.2	Creating IF Functions	Typing IF Functions Using the Function Wizard	43
5.3	Nesting IF Conditions	Step 1 – Define the Conditions Step 2 – Create the Nested IF Function An Alternative: the IFS function	45
5.4	The SWITCH Function		47
5.5	Combining IF with Other Logical Functions		48

Chapter 6 - THE VLOOKUP FUNCTION▼

Section	Title	Subsections	Page
6.1	Types of Lookup Table	Exact Matches or Not? Horizontal or Vertical?	49
6.2	Inexact Matches (Continuous Value Lookups)	Creating the Lookup Table Giving the Lookup Table a Range Name Creating the VLOOKUP Formula	50
6.3	Exact Match Lookup Tables	Syntax of the Exact VLOOKUP Function Creating a Range Name for your Lookup Table Creating the Lookup Formula	52
6.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	54

Chapter 7 - OTHER LOOKUP FUNCTIONS▼

Section	Title	Subsections	Page
7.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	56
7.2	The XMATCH Function	The XMATCH Function Arguments	60
7.3	The CHOOSE Function		61
7.4	The OFFSET Function	Basic Offsetting Setting the Number of Rows/Columns Returned	62
7.5	The INDIRECT Function	Example: Picking Out Figures from a Chosen Worksheet	64

Chapter 8 - THE XLOOKUP FUNCTION▼

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

Chapter 9 - INSPECTION AND ERROR FUNCTIONS▼

Section	Title	Subsections	Page
9.1	Inspection Functions	Non-Error Inspection Functions Error Inspection Functions	69
9.2	Handling Errors	General Error Trapping using IFERROR Avoiding the Error in the First Place	70

Chapter 10 - DATA VALIDATION▼

Section	Title	Subsections	Page
10.1	What is Data Validation?		71
10.2	Creating Validation Rules	Step 1 – Saying what is Allowed Step 2 – Defining your Punishment for Mistakes	72
10.3	Referencing Formulae in Validation		74
10.4	Dropdown Lists	Step 1 - Creating a Range Name Step 2 – Setting the List	75
10.5	Input Messages		76
10.6	Copying and Clearing Validation Settings	Copying Validation Clearing Validation Settings	77
10.7	Highlighting Validated Cells	Highlighting Cells which have Validation Applied Circling Invalid Data	78

Chapter 11 - DYNAMIC RANGE NAMES▼

Section	Title	Subsections	Page
11.1	Examples of Dynamic Range Names		79
11.2	Making a Range Name Dynamic	The Principle: the OFFSET Function Creating the Range Name Using Dynamic Range Names	80
11.3	Dynamic Range Names and Charts	Creating the Range Names Making the Chart Refer to these Ranges	82

Chapter 12 - INFORMATION FUNCTIONS▼

Section	Title	Subsections	Page
12.1	The CELL and INFO Functions	The INFO Function The CELL Function	83
12.2	Sheet Number / Numbers	The SHEET Function The SHEETS Function	84

Chapter 13 - TEXT FUNCTIONS▼

Section	Title	Subsections	Page
13.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	85
13.2	Text Functions – a Case Study		88

Chapter 14 - MULTIPLE WORKSHEETS▼

Section	Title	Subsections	Page
14.1	Moving between Worksheets		89
14.2	Common Tasks	Selecting and De-selecting Worksheets Renaming Worksheets Changing Worksheet Tab Colours Hiding and Unhiding Worksheets	90
14.3	Inserting and Deleting Worksheets	Inserting Single Worksheets Inserting Multiple Worksheets Deleting Worksheets	92
14.4	Moving and Copying Worksheets	Moving Worksheets within a Workbook Copying Worksheets within a Workbook Moving and Copying to a Different or New Workbook	93
14.5	Group Mode		95
14.6	Summing across Sheets		96

Chapter 15 - TABLES▼

Section	Title	Subsections	Page
15.1	Definition of a Table		97
15.2	Creating and Removing Tables	Creating Tables from Existing Data Creating Tables from Scratch Removing Tables	98
15.3	Sorting Tables	Simple Sorting – 3 Methods Sorting by Two or More Columns Sorting by Colour	100
15.4	Simple Filtering	Step 1 – Enabling Filtering Step 2 – Filtering by Columns	103
15.5	Removing Filters	Clearing a Filter from a Single Column Clearing All Filters Removing Filtering	105
15.6	Filtering Specific Types of Data	Filtering Numbers Filtering Text Filtering Dates	106

Chapter 16 - ADVANCED TABLES▼

Section	Title	Subsections	Page
16.1	Removing Duplicate Rows		108
16.2	Using the Data Form	Adding the Data Form to the Quick Access Toolbar Using the Data Form to View Records Using the Data Form to Add New Records Using the Data Form to Delete Records Using the Data Form to Find Records Matching your Criteria	109
16.3	Advanced Filtering (Using OR and AND)	Step 1 – Creating and Setting a Criteria Range Step 2 – Creating an Extract Range Step 3 – Applying Advanced Filtering Examples of Criteria Multiple Extract Ranges Extracting to other Sheets	113

Chapter 17 - FORMATTING TABLES▼

Section	Title	Subsections	Page
17.1	Formatting Tables	Changing the Scope of Formatting Applying a Format When Formatting appears not to Work	118
17.2	Creating Table Styles	Step 1 - Creating the Table Style Step 2 – Formatting Parts of your Style Step 3 – Setting the Stripe Size	120

Chapter 18 - SUBTOTALS▼

Section	Title	Subsections	Page
18.1	What Subtotals Do		123
18.2	Creating Subtotals	Step 1 – Sorting the Data Correctly Step 2 – Checking you can Create Subtotals Step 3 – Creating Subtotals Step 4 – Grouping and Outlining Step 5 – Copying the Visible Cells Only	124
18.3	Removing Subtotals		127

Chapter 19 - GROUPING (OUTLINING)▼

Section	Title	Subsections	Page
19.1	Using Grouping		128
19.2	Creating Grouping/Outlining	Creating Automatic Outlines Manually Grouping Rows/Columns	129
19.3	Problems with Grouping/Outlining	Changing Outlining Position Using Grouping/Outlining with Protection	130
19.4	Removing Outlining/Grouping	Removing Outlining/Grouping from Selected Rows/Columns Removing All Outlining	131

Chapter 20 - PIVOT TABLES▼

Section	Title	Subsections	Page
20.1	Overview		132
20.2	Creating Pivot Tables	Step 1 – Ensure you have a Table Step 2 – Create your Pivot Table Step 3 – Configure your Fields	133
20.3	Deleting Pivot Tables		135
20.4	Drilling Down		136
20.5	Working with Pivot Tables	Renaming Fields Changing how Values are Calculated Changing Number Formatting Moving Rows and Columns Sorting Pivot Tables	137
20.6	Refreshing Pivot Tables	Getting Pivot Tables to Refresh when you Open Workbooks	140
20.7	Working with Dates	Grouping by Date	141
20.8	Filtering and Slicers	Filtering a Pivot Table Creating Slicers Removing Slicers (or their Filters) Changing the Number of Slicer Columns Changing the Style of a Slicer	142
20.9	Formatting Pivot Tables		145
20.10	Choosing what to Display	Hiding Field Captions Inserting Blank Rows Subtotals and Totals Changing Report Layout Repeating Item Labels	146
20.11	Creating New Groups in a Pivot Table	Ungrouping	148
20.12	Displaying % of Grand Totals		149
20.13	Report Filter Pages		150

Chapter 21 - GETTING DATA▼

Section	Title	Subsections	Page
21.1	A Potted History of Get & Transform		151
21.2	What Get & Transform Does		152
21.3	Getting Data	Step 1 - Creating the Query Step 2 – Load or Edit?	153
21.4	Managing Queries		155
21.5	Linking to a Website		156
21.6	Linking to an XML File		157
21.7	Other Data Sources		158

Chapter 22 - TRANSFORMING DATA▼

Section	Title	Subsections	Page
22.1	Editing a Query	The Query Editor	159
22.2	Editing Query Steps	The Hard Way – the M Language The Easy Way – Query Steps	160
22.3	Adding Simple Transforms	Removing Columns Reordering Columns Filtering Rows Sorting Columns	161
22.4	Splitting Columns, etc. – a Case Study	Step 1 – Splitting by Number of Characters Step 2 – Splitting by Delimiter Step 3 – Replacing Values Step 4 – Changing Data Types Step 5 - Merging Columns Step 6 – Inserting a Start Date Column Step 7 – Getting the Month Number Step 8 – Aggregating the Data Step 9 – Renaming Query Steps Step 10 – Running your Query	163
22.5	Managing your Queries	Renaming Queries Grouping Queries	169
22.6	Recent Data Sources		170

Chapter 23 - CHARTS▼

Section	Title	Subsections	Page
23.1	Overview		171
23.2	Selecting Data	The Golden Rules for Selecting Chart Data Selecting a Simple Data Range Selecting Multiple Ranges Selecting Non-Contiguous Ranges	172
23.3	Creating Charts		174
23.4	Chart Location	Embedded Charts Printing Embedded Charts Separate Sheet Charts Switching between Embedded and Separate Sheet Charts	175
23.5	Chart Types, Templates and Defaults	Changing a Chart’s Type (Including Chart Templates) Creating Chart Templates Setting the Default Chart	177
23.6	Formatting Charts		179
23.7	Formatting Parts of a Chart	The Parts of a Chart (Chart Elements) Selecting Part of a Chart Selecting a Data Point or Legend Entry Formatting Part of a Chart Using the Formatting Task Bar Worked Example: Setting the Gradient Fill for a Chart Title	180
23.8	Showing/Hiding Parts of a Chart		183
23.9	Specific Chart Element Consideration	Working with Data Tables Data Labels Axes, Scaling, Tick Marks and Gridlines	185
23.10	Editing Data	Changing the Data being Charted Visually Switching Rows and Columns Filtering Data and Labels	187

Chapter 24 - ADVANCED CHARTS (1)▼

Section	Title	Subsections	Page
24.1	Overview		189
24.2	Combination Charts		190
24.3	Trendlines	Some Types of Trendline Adding Trendlines to Charts Choosing the Trendline Type Displaying Regression Statistics Extrapolating Trendlines	191
24.4	Scatter and Bubble Diagrams	Scatter Diagrams Displaying a Regression Line Bubble Charts Customising Bubble Charts	193
24.5	Stock Charts		196
24.6	Lines and Up/Down Bars	Up/Down Bars Series Lines	197
24.7	Error Bars		198
24.8	Picture Charts	Tiling Pictures	199

Chapter 25 - ADVANCED CHARTS (2)▼

Section	Title	Subsections	Page
25.1	Sunburst Charts		201
25.2	Treemap Charts		202
25.3	Waterfall or Bridge Charts		203
25.4	Histograms		204
25.5	Box and Whiskers Charts	Creating a Box and Whiskers Chart Interpreting the Box and Whiskers	205
25.6	Pareto Charts		207

Chapter 26 - SPARKLINES▼

Section	Title	Subsections	Page
26.1	Introduction to Sparklines	What Sparklines Are Sparklines can Contain Data	208
26.2	Creating Sparklines		209
26.3	Grouping and Ungrouping Sparklines	Ungrouping Sparklines Grouping Sparklines	210
26.4	Removing Sparklines		211
26.5	Editing Sparklines	Changing the Sparkline Type Changing Line and Marker Colours Sparkline Markers Changing the Axis Settings	211

Chapter 27 - GOAL-SEEKING▼

Section	Title	Subsections	Page
27.1	Goal-Seeking Example		214
27.2	Using Goal-Seeking		215

Chapter 28 - SCENARIOS▼

Section	Title	Subsections	Page
28.1	Overview of Scenarios		216
28.2	Creating a Scenario	Base case scenarios Starting the Scenario Manager Creating a Scenario	217
28.3	Showing Different Scenarios	Showing Different Scenarios using the Ribbon Switching Scenarios using the Quick Access Toolbar	219
28.4	Editing, Deleting and Merging Scenarios	Editing or Deleting Scenarios Merging Scenarios	220
28.5	Summarising Scenarios		221

Chapter 29 - EXCEL SHORTCUT KEYS▼

Section	Title	Subsections	Page
29.1	CTRL and SHIFT Key Combinations	SHIFT and CTRL keys General CTRL Keys in Excel CTRL Keys with Letters	222
29.2	Function Keys	F1 to F6 Keys F7 to F12 Keys	225
29.3	Other Special Keys	Special Keys – Part 1 Special Keys – Part 2 Special Keys – Part 3	227

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