This page has 0 threads | Add post

Courseware

Intermediate 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 Intermediate 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	7
1.2	Creating Formulae		8
1.3	Editing Formulae		9
1.4	Copying Formulae	The Easiest Way to Copy a Formula Other Ways to Copy a Formula How Relative Cell Referencing Works	10
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	12
1.6	Status Bar Calculations		16

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	17
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	19

Chapter 3 - ABSOLUTE AND RELATIVE REFERENCES▼

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

Chapter 4 - RANGE NAMES▼

Section	Title	Subsections	Page
4.1	What are Range Names?	Going to a Range Name	26
4.2	Creating Range Names	Creating Range Names using the Name Box Defining Range Names	27
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	28
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	30
4.5	Changing Range Names	Editing and Deleting Range Names Avoiding the Need to Change Range Names	34
4.6	Creating Range Names from Selected Cells		35
4.7	Scope of Range Names	Choosing the Scope of a Range Name Copying Worksheets Copies their Range Names with Local Scope	36
4.8	Managing Range Names	Listing out Range Names Applying Range Names	37
4.9	Quirks of Range Names	Getting Aggregation Functions to Work Viewing Range Names 3-Dimensional Ranges	38

Chapter 5 - IF FUNCTIONS▼

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

Chapter 6 - CONDITIONAL STATISTICS▼

Section	Title	Subsections	Page
6.1	Overview of Conditional Statistical Functions	Using IF instead of Conditional Statistical Functions The Special Functions Available	47
6.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	48

Chapter 7 - CONDITIONAL FORMATTING▼

Section	Title	Subsections	Page
7.1	Creating Conditional Formats	Applying a Pre-set Conditional Format Creating your own Conditional Format – a Case Study	52
7.2	Editing and Deleting Conditional Formats	Changing the Order in which Rules Apply	54
7.3	More Sophisticated Rules	Picking out Statistical Trends Using Formulae in Rules Using Formulae in Rules to Highlight a Whole Row	55
7.4	Viewing Conditionally Formatted Cells/Rules	Viewing All Conditionally Formatted Cells Viewing all Conditional Formatting Rules	58

Chapter 8 - TABLES▼

Section	Title	Subsections	Page
8.1	Definition of a Table		59
8.2	Creating and Removing Tables	Creating Tables from Existing Data Creating Tables from Scratch Removing Tables	60
8.3	Sorting Tables	Simple Sorting – 3 Methods Sorting by Two or More Columns Sorting by Colour	62
8.4	Simple Filtering	Step 1 – Enabling Filtering Step 2 – Filtering by Columns	65
8.5	Removing Filters	Clearing a Filter from a Single Column Clearing All Filters Removing Filtering	67
8.6	Filtering Specific Types of Data	Filtering Numbers Filtering Text Filtering Dates	68

Chapter 9 - PRINTING▼

Section	Title	Subsections	Page
9.1	Views of a Worksheet	Page Layout View Page Break Preview Print Preview	70
9.2	Printing	Choosing to Print Print Settings	71
9.3	Printing Areas of a Worksheet	Setting a Print Area Setting Multiple Print Areas	72
9.4	Page Breaks	Viewing Page Breaks Moving Page Breaks Inserting Page Breaks Removing Page Breaks	73
9.5	Fitting Worksheets to Pages	Page Orientation Page Margins in Excel Changing Margins Using the Ribbon Changing Margins in Print Preview Scaling a Page Using Page Breaks to Scale a Page The Page Setup Dialog Box	75
9.6	Headers and Footers	Adding a Header and Footer Inserting Headers and Footers with the Ribbon	79
9.7	Printing Titles	Setting Print Titles	80

Chapter 10 - NUMBER AND DATE FORMATS▼

Section	Title	Subsections	Page
10.1	What are Number Formats?	Examples of Number Formats Examples of Date Formats	82
10.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	83
10.3	Standard Number Formats	Standard Number Formats using the Ribbon More Number Format Options using Format Cells	87
10.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	89
10.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	91

Chapter 11 - DATE AND TIME FUNCTIONS▼

Section	Title	Subsections	Page
11.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	94
11.2	Manipulating Dates	Returning Different Dates Returning the Difference between Dates	96

Chapter 12 - MULTIPLE WORKSHEETS▼

Section	Title	Subsections	Page
12.1	Moving between Worksheets		97
12.2	Common Tasks	Selecting and De-selecting Worksheets Renaming Worksheets Changing Worksheet Tab Colours Hiding and Unhiding Worksheets	98
12.3	Inserting and Deleting Worksheets	Inserting Single Worksheets Inserting Multiple Worksheets Deleting Worksheets	100
12.4	Moving and Copying Worksheets	Moving Worksheets within a Workbook Copying Worksheets within a Workbook Moving and Copying to a Different or New Workbook	101
12.5	Group Mode		103
12.6	Summing across Sheets		104

Chapter 13 - DATA VALIDATION▼

Section	Title	Subsections	Page
13.1	What is Data Validation?		105
13.2	Creating Validation Rules	Step 1 – Saying what is Allowed Step 2 – Defining your Punishment for Mistakes	106
13.3	Referencing Formulae in Validation		108
13.4	Dropdown Lists	Step 1 - Creating a Range Name Step 2 – Setting the List	109
13.5	Input Messages		110
13.6	Copying and Clearing Validation Settings	Copying Validation Clearing Validation Settings	111
13.7	Highlighting Validated Cells	Highlighting Cells which have Validation Applied Circling Invalid Data	112

Chapter 14 - PROTECTION▼

Section	Title	Subsections	Page
14.1	Types of Protection		113
14.2	Protecting Worksheets	Unlocking Cells A Note on Unprotecting Cells using Styles Protecting and Unprotecting Worksheets	114
14.3	Protecting a Workbook’s Structure		116
14.4	Password-Protecting Workbooks		117
14.5	Preventing Accidental Changes to a Workbook		118
14.6	Hiding Worksheets	Very Hidden Worksheets	119
14.7	Hiding Cells and Formulae	Hiding Cells Hiding Formulae	120

Chapter 15 - CHARTS▼

Section	Title	Subsections	Page
15.1	Overview		121
15.2	Selecting Data	The Golden Rules for Selecting Chart Data Selecting a Simple Data Range Selecting Multiple Ranges Selecting Non-Contiguous Ranges	122
15.3	Creating Charts		124
15.4	Chart Location	Embedded Charts Printing Embedded Charts Separate Sheet Charts Switching between Embedded and Separate Sheet Charts	125
15.5	Chart Types, Templates and Defaults	Changing a Chart’s Type (Including Chart Templates) Creating Chart Templates Setting the Default Chart	127
15.6	Formatting Charts		129
15.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	130
15.8	Showing/Hiding Parts of a Chart		133
15.9	Specific Chart Element Consideration	Working with Data Tables Data Labels Axes, Scaling, Tick Marks and Gridlines	135
15.10	Editing Data	Changing the Data being Charted Visually Switching Rows and Columns Filtering Data and Labels	137

Chapter 16 - EXCEL SHORTCUT KEYS▼

Section	Title	Subsections	Page
16.1	CTRL and SHIFT Key Combinations	SHIFT and CTRL keys General CTRL Keys in Excel CTRL Keys with Letters	139
16.2	Function Keys	F1 to F6 Keys F7 to F12 Keys	142
16.3	Other Special Keys	Special Keys – Part 1 Special Keys – Part 2 Special Keys – Part 3	144

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