| Session |
Lesson
|
Lesson
PDF |
High
Res
Video |
Low Res
Video |
Sample Files |
Session
PDF |
Cost |
|
|
Contents |
[PDF] |
|
|
|
|
 |
|
|
How to Use This Course - Important
Information (only 9 mins long)! |
[PDF] |
[Video] |
|
|
|
|
Session One: Basic Skills |
Session Objectives |
[PDF] |
|
|
[Sample Files] |
[PDF] |
|
|
Lesson 1 1: Start Excel and check your program
version |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 1 2: Maximize, minimize, re-size, move
and
close the Excel window |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 1 3: Understand the Application and Workbook
windows |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 1 4: Open and navigate a workbook |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 1 5: Save a workbook |
[PDF] |
[Video] |
[YouTube] |
|
Excel Workbook (the Open XML format)
|
|
|
|
|
Excel Macro Enabled Workbook
|
|
|
|
|
Excel 97-2003 Workbook
|
|
|
|
|
Excel Binary Workbook
|
|
|
|
|
Lesson 1 6: Pin a document, and understand file organization |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 1-7 View, add, remove and navigate
worksheet tabs |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 1
8: Use the Ribbon |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 1
9: Understand Ribbon components |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 1
10: Customize the Quick Access Toolbar
and preview the printout |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 1 11: Use the Mini Toolbar, Key Tips
and keyboard shortcuts |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 1 12: Understand Views |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 1 13: Use full screen view |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 1 14: Use the help system |
[PDF] |
[Video] |
[YouTube] |
|
Session 1: Exercise |
[PDF] |
|
|
|
Session 1: Exercise answers |
[PDF] |
|
|
Session Two: Doing Useful
Work with Excel |
Session Objectives |
[PDF] |
|
|
[Sample Files] |
[PDF] |
|
|
Lesson 2 1: Enter text and numbers into a worksheet |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 2 2: Create a new workbook and view two
workbooks at the same time |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 2 3: Use AutoSum to quickly calculate
totals |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 2 4: Select a range of cells and
understand Smart Tags |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 2 5: Enter data into a range and copy
data across a range |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 2 6: Select adjacent
and non-adjacent rows and columns |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 2 7: Select
non-contiguous cell ranges and view summary
information |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 2 8: AutoSelect a range of cells |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 2‑9: Re-size rows and columns |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 2‑10: Use AutoSum to sum a non-contiguous
range |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 2‑11: Use AutoSum to quickly calculate
averages |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 2‑12: Create your own formulas |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 2 13:
Create functions
using Formula AutoComplete |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 2 14: Use AutoFill for text and numeric
series |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 2 15: Use AutoFill to adjust formulas |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 2 16: Use AutoFill Options |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 2 17: Speed up your Auto Fills and create
a custom fill series |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 2 18: Use the zoom control |
[PDF] |
[Video] |
[YouTube] |
|
Lesson 2 19: Print out a worksheet |
[PDF] |
[Video] |
[YouTube] |
|
Session 2: Exercise |
[PDF] |
|
|
|
Session 2 Exercise Answers |
[PDF] |
|
|
|
Session 2: Endpiece |
[PDF] |
|
|
| End of
Free Course |
| |
Essential
Skills Book and DVD-ROM contains all of the above along with
the following sessions
Click here to
find out how you can buy the book and DVD-ROM
for less than the RRP of £14.99/$21.99
A few sample videos and book excerpts have been included below -
look for the lessons with the PDF links! |
 |
Session Three: Taking Your
Skills to the Next Level |
Session Objectives |
[PDF] |
|
|
[Sample Files] |
This session is published in
the book
and the
DVD.
Book owners can
also download the sample files for each lesson. |
|
Lesson 3 1: Insert and delete rows and columns |
|
|
|
|
Lesson 3 2: Use AutoComplete and fill data from
adjacent cells |
|
|
|
|
Lesson 3 3: Cut, copy and paste |
|
|
|
|
Lesson 3 4: Cut, copy and paste using drag and
drop |
|
|
|
|
Lesson 3 5: Use Paste Values and
increase/decrease decimal places displayed |
|
|
|
| Lesson 3
6: Transpose a range |
|
|
|
|
Lesson 3 7: Use the Multiple Item Clipboard |
|
|
|
|
Lesson 3 8: Use Undo and Redo |
|
|
|
| Lesson 3 9: Insert cell comments |
|
|
|
|
Lesson 3 10: View cell comments |
[PDF] |
|
|
|
Lesson 3 11: Print cell comments |
|
|
|
|
Lesson 3 12: Understand absolute and relative
cell references |
|
|
|
|
Lesson 3 13: Understand mixed cell references |
|
|
|
| Lesson 3 14: Create a template |
|
|
|
| Lesson 3 15: Use a template |
|
|
|
| Lesson 3 16: Freeze columns and rows |
|
|
|
|
Lesson 3 17: Split the window into multiple
panes |
|
|
|
| Lesson 3
18: Check spelling |
|
|
|
| Session 3: Exercise |
[PDF] |
|
|
| Session 3 Exercise Answers |
|
|
|
Session Four: Making Your
Worksheets Look Professional |
Session Objectives |
[PDF] |
|
|
[Sample Files] |
This session is published in
the book
and the
DVD.
Book owners can
also download the sample files for each lesson. |
| Lesson 4
1: Format dates |
|
|
|
| Lesson 4
2: Understand date serial numbers |
|
|
|
| Lesson 4
3: Format numbers using built-in number
formats |
|
|
|
| Lesson 4
4: Create custom number formats |
|
|
|
| Lesson 4
5: Horizontally Align the contents of cells |
|
|
|
|
Lesson 4 6: Merge cells, wrap text and
expand/collapse the formula bar |
|
|
|
|
Lesson 4 7: Vertically align the contents of
cells |
|
|
|
| Lesson 4
8: Understand themes |
[PDF] |
|
|
Font sets |
|
|
|
Color sets |
|
|
|
|
Lesson 4 9: Use cell styles and change themes Effects |
|
|
|
|
Lesson 4 10: Add color and gradient effects to
cells |
|
|
|
|
Lesson 4 11: Add borders and lines |
|
|
|
|
Lesson 4 12:
Create your own custom theme |
|
|
|
|
Lesson 4 13: Create
your own custom cell styles |
|
|
|
|
Lesson 4 14: Use a master style book to merge
styles |
|
|
|
| Lesson 4 15: Use simple Conditional Formatting |
|
|
|
| Lesson 4
16: Manage multiple conditional formats
using the Rules Manager |
|
|
|
| Lesson 4
17: Bring data alive with visualizations |
|
|
|
|
Lesson 4 18: Create a formula driven conditional
format |
|
|
|
| Lesson 4 19: Use the Format Painter |
|
|
|
| Lesson 4 20: Rotate text |
|
|
|
| Session 4: Exercise |
[PDF] |
|
|
| Session 4 Exercise Answers |
|
|
|
| Session Five: Charts |
Session Objectives |
[PDF] |
|
|
This session is published in
the book
and the
DVD.
Book owners can
also download the sample files for each lesson. |
| Lesson 5 1: Create a simple chart with
two
clicks |
|
|
|
[Sample Files] |
| Lesson 5 2: Move, re-size, copy and delete a
chart |
|
|
|
|
Lesson 5 3: Change the chart layout and add a
data table |
|
|
|
|
Lesson 5 4: Format chart element fills and
borders |
|
|
|
|
Lesson 5 5: Format 3-D elements and align text |
|
|
|
| Lesson 5 6: Move, re-size and delete chart elements |
|
|
|
|
Lesson 5 7: Change a chart's source data |
|
|
|
|
Lesson 5 8: Assign non-contiguous source data by
click and drag |
|
|
|
|
Lesson 5 9: Change source data using the Select
Data Source dialog tools |
|
|
|
|
Lesson 5 10: Chart non-contiguous source data by
hiding rows and columns |
|
|
|
| Lesson 5 11: Create a chart with numerical axis |
|
|
|
|
Lesson 5 12: Deal with empty
data points |
|
|
|
|
Lesson 5 13: Add data labels to a chart |
|
|
|
|
Lesson 5 14: Highlight specific data points with
color and annotations |
|
|
|
|
Lesson 5 15: Add gridlines and scale axes |
|
|
|
| Lesson 5 16: Emphasize data by manipulating
pie charts |
|
|
|
|
Lesson 5 17: Create a chart with two vertical
axis |
|
|
|
|
Lesson 5 18: Create a combination chart
containing different chart types |
[PDF] |
|
|
|
Lesson 5 19: Add a trend line |
|
|
|
|
Lesson 5 20: Switch chart rows/columns and add a
gradient fill |
|
|
|
| Lesson 5 21: Create your own chart templates |
|
|
|
| Session 5: Exercise |
[PDF] |
|
|
| Session 5 Exercise Answers |
|
|
|
Session Six: Working With
Multiple Worksheets and Workbooks |
Session Objectives |
[PDF] |
|
|
[Sample Files] |
This session is published in
the book
and the
DVD.
Book owners can
also download the sample files for each lesson. |
| Lesson 6 1: View the same workbook in different
windows |
|
|
|
| Lesson 6 2: View two windows side by side and
perform synchronous scrolling |
|
|
|
| Lesson 6 3: Duplicate worksheets within a workbook |
|
|
|
| Lesson 6 4: Move and copy worksheets from one
workbook to another |
[PDF] |
|
|
|
Lesson 6 5: Hide and unhide a worksheet |
|
|
|
| Lesson 6
6: Create cross worksheet formulas |
|
|
|
|
Lesson 6 7 Understand worksheet groups |
|
|
|
| Lesson 6
8: Use find and replace |
|
|
|
| Session 6: Exercise |
[PDF] |
|
|
| Session 6 Exercise Answers |
|
|
|
| Session Seven: Printing
Your Work |
Session Objectives |
[PDF] |
|
|
[Sample Files] |
This session is published in
the book
and the
DVD.
Book owners can
also download the sample files for each lesson. |
|
Lesson 7 1: Print Preview and change paper
orientation |
|
|
|
|
Lesson 7 2: Use page layout view to adjust
margins |
|
|
|
|
Lesson 7 3: Use Page Setup to set margins more
precisely and center the worksheet |
|
|
|
| Lesson 7 4: Set paper size
and scale |
|
|
|
| Lesson 7
5: Insert, delete and preview page breaks |
|
|
|
|
Lesson 7 6: Adjust page breaks using Page Break
Preview |
|
|
|
|
Lesson 7 7: Add auto-headers and auto-footers
and set the starting page number |
|
|
|
| Lesson 7
8: Add custom headers and footers |
[PDF] |
|
|
| Lesson 7
9: Specify different headers and footers
for the first, odd and even pages |
|
|
|
| Lesson 7 10: Print only part of a worksheet |
|
|
|
|
Lesson 7 11: Add row and column data labels and
grid lines to printed output |
|
|
|
|
Lesson 7 12: Print several selected worksheets
and change the page order |
|
|
|
|
Lesson 7 13: Suppress error messages in
printouts |
|
|
|
| Session 7: Exercise |
[PDF] |
|
|
| Session 7 Exercise Answers |
|
|
|
| End of
Essential Skills Course (Book 1 ) |
| |
| Expert
Skills Course Begins Here (Book 2 |
| Session Eight: Security |
Session Objectives |
|
|
|
[Sample Files] |
This session is published in
the book
and the
DVD.
Book owners can
also download the sample files for each lesson. |
| Lesson 8 1: Preventing unauthorized users from
opening your workbooks |
|
|
|
| Lesson 8 2: Hide and unhide columns and rows |
|
|
|
| Lesson 8 3: Hide and unhide a worksheet |
|
|
|
| Lesson 8 4: Control the changes users can make
to your workbooks |
|
|
|
| Lesson 8 5: Apply more sophisticated protection
to worksheets |
|
|
|
| Lesson 8 6: Only allow users to enter information
in specific cells |
|
|
|
| Lesson 8 7: Create custom views |
|
|
|
| Session 8: Exercise |
|
|
|
| Session 8 Exercise Answers |
|
|
|
| Session Nine: Advanced functions
and formulas |
Session Objectives |
|
|
|
[Sample Files] |
This session is published in
the book
and the
DVD.
Book owners can
also download the sample files for each lesson. |
| Lesson 9 1: Precedence and parenthesis |
|
|
|
| Lesson 9 2: Create a formula that demands parenthesis |
|
|
|
| Lesson 9 3: Use percentages |
|
|
|
| Lesson 9 4: Create custom views |
|
|
|
| Lesson 9 5: Use the formula palette |
|
|
|
| Lesson 9 6: Create, use and document range names |
|
|
|
| Lesson 9 7: Use intersection range names |
|
|
|
| Lesson 9 8: Understand range name scope |
|
|
|
| Lesson 9 9: Use range names to store constants
and formulas |
|
|
|
| Lesson 9 10: Create and use a three dimensional
range name |
|
|
|
| Lesson 9 11: Use the function library |
|
|
|
| Lesson 9 12: Insert a function inside an existing
function |
|
|
|
| Lesson 9 13: Understand calculation options
(manual and automatic) |
|
|
|
| Lesson 9 14: Use the IF() logic function |
|
|
|
| Lesson 9 15: Use the PMT() Payment function |
|
|
|
| Lesson 9 16: Use a Vlookup() function |
|
|
|
| Lesson 9 17: Use a nested function |
|
|
|
| Lesson 9 18: Audit a worksheet by displaying
formulas in another window |
|
|
|
| Lesson 9 19: Use Find and Select to identify
worksheet errors |
|
|
|
| Lesson 9 20: Understand formula errors and resolve
circular references |
|
|
|
| Lesson 9 21: Audit a formula by evaluating it |
|
|
|
| Lesson 9 22: Audit a formula by tracing precedents
and dependents |
|
|
|
| Lesson 9 23: Use the watch window to monitor
cell values |
|
|
|
| Lesson 9 24: Use text to speech to find errors |
|
|
|
| Session 9: Exercise |
|
|
|
| Session 9 Exercise Answers |
|
|
|
| Session Ten: Tables |
Lesson 10 1: Understand tables and ranges |
|
|
|
[Sample Files] |
This session is published in
the book
and the
DVD.
Book owners can
also download the sample files for each lesson. |
| Lesson 10 2: Appreciate Excel’s limitations |
|
|
|
| Lesson 10 3: Create a table from a range |
|
|
|
| Lesson 10 4: Apply table styles |
|
|
|
| Lesson 10 5: Sort a table |
|
|
|
| Lesson 10 6: Sort by custom
list |
|
|
|
| Lesson 10 7: Sort by colors and icons |
|
|
|
| Lesson 10 8: Filter a table |
|
|
|
| Lesson 10 9: Add totals to a table |
|
|
|
| Lesson 10 10: Insert and delete table rows and
columns |
|
|
|
| Lesson 10 11: Name a table and refer to it by
name in formulas |
|
|
|
| Lesson 10 12: Add a calculated column to a table |
|
|
|
| Lesson 10 13: Select table rows and columns |
|
|
|
| Lesson 10 14: Understand database design rules |
|
|
|
| Lesson 10 15: Keep data atomic using Text to
Columns |
|
|
|
| Lesson 10 16: Comply with the keep data unique
rule by removing duplicates |
|
|
|
| Lesson 10 17: Consolidate data from multiple
data ranges |
|
|
|
| Lesson 10 18: Use a data form |
|
|
|
| Lesson 10 19: Use an advanced filter |
|
|
|
| Lesson 10 20: Automatically subtotal a range |
|
|
|
| Lesson 10 21: Use data validation |
|
|
|
| Session 10: Exercise |
|
|
|
| Session 10 Exercise Answers |
|
|
|
| Session Eleven: An introduction
to macros and VBA |
Lesson 11 1: Record and play a simple macro |
|
|
|
[Sample Files] |
This session is published in
the book
and the
DVD.
Book owners can
also download the sample files for each lesson. |
| Lesson 11 2: Record and play a more complex
macro |
|
|
|
| Lesson 11 3: Add a macro to the Quick Access
Toolbar |
|
|
|
| Lesson 11 4: An object-orientated primer |
|
|
|
| Lesson 11 5: Understand the VBA code generated
by the macro recorder |
|
|
|
| Lesson 11 6: Cut and paste code between macros |
|
|
|
| Lesson 11 7: Use a simple VBA function |
|
|
|
| Lesson 11 8: Use a logical construct from within
VBA code |
|
|
|
| Session 11: Exercise |
|
|
|
| Session 11 Exercise Answers |
|
|
|
| Session Twelve: Use Excel
with other applications and external data |
Lesson 12 1: Animate an Excel chart with PowerPoint |
|
|
|
[Sample Files] |
This session is published in
the book
and the
DVD.
Book owners can
also download the sample files for each lesson. |
| Lesson 12 2: Incorporate Excel tables and charts
into Word documents |
|
|
|
| Lesson 12 3: Understand the XLSX and XLS file
formats |
|
|
|
| Lesson 12 4: Export Excel data into a database |
|
|
|
| Lesson 12 5: Import data from a database |
|
|
|
| Lesson 12 6: Import data from a text file |
|
|
|
| Lesson 12 7: Save a workbook as an Adobe Acrobat
PDF file |
|
|
|
| Session 12: Exercise |
|
|
|
| Session 12 Exercise Answers |
|
|
|
| Session Thirteen: Excel
and the Internet |
Lesson 13 1: Import data from the Internet using
a web query |
|
|
|
[Sample Files] |
| Lesson 13 2: Use hyperlinks |
|
|
|
| Lesson 13 3: Publish an Excel worksheet as a
web page |
|
|
|
| Session 13: Exercise |
|
|
|
| Session 13 Exercise Answers |
|
|
|
| Session Fourteen: Pivot
Tables |
Lesson 14 1: Overview of pivot tables |
|
|
|
[Sample Files] |
This session is published in
the book
and the
DVD.
Book owners can
also download the sample files for each lesson. |
| Lesson 14 2: Create a one dimensional pivot
table from a table |
|
|
|
| Lesson 14 3: Understand report layout options |
|
|
|
| Lesson 14 4: Group dates by day, month and year |
|
|
|
| Lesson 14 5: Group dates by weeks, fortnights
or any other time period |
|
|
|
| Lesson 14 6: Group numeric and text fields |
|
|
|
| Lesson 14 7: Show levels of detail by collapsing
and expanding |
|
|
|
| Lesson 14 8: Use a pivot table to view data
in two dimensions |
|
|
|
| Lesson 14 9: Filter pivot table dimensions |
|
|
|
| Lesson 14 10: Show totals and subtotals |
|
|
|
| Lesson 14 11: Display average values |
|
|
|
| Lesson 14 12: Use Show Values As for sophisticated
summation |
|
|
|
| Lesson 14 13: Link to pivot table cells |
|
|
|
| Lesson 14 14: Apply a conditional format to
the entire pivot table using smart tags |
|
|
|
| Lesson 14 15: Apply a pivot table style |
|
|
|
| Lesson 14 16: Create a custom pivot table style |
|
|
|
| Lesson 14 17: Automatically generate multiple
pivot tables for a given entity |
|
|
|
| Lesson 14 18: Connect a pivot table directly
to a database |
|
|
|
| Lesson 14 19: Refresh pivot table data |
|
|
|
| Lesson 14 20: Create a pivot chart from a pivot
table |
|
|
|
| Session 14: Exercise |
|
|
|
| Session 14 Exercise Answers |
|
|
|
| Session Fifteen: Summarizing
data |
Lesson 15 1: Create automatic subtotals |
|
|
|
[Sample Files] |
Thse sessions
are published in
the book
and the
DVD.
Book owners can
also download the sample files for each lesson. |
| Lesson 15 2: Use summarize functions |
|
|
|
| Lesson 15 3: Grouping and outlining |
|
|
|
| Session 15: Exercise |
|
|
|
| Session 15 Exercise Answers |
|
|
|
| Session Sixteen: What If
Analysis |
Lesson 16 1: Create a one-input what-If analysis
data table |
|
|
|
[Sample Files] |
| Lesson 16 2: Create a two-input what-If analysis
data table |
|
|
|
| Lesson 16 3: Create a scenario summary report |
|
|
|
| Lesson 16 4: Use Goal Seek |
|
|
|
| Lesson 16 5: Use Solver |
|
|
|
| Lesson 16 6: Use document recovery |
|
|
|
| Lesson 16 7: Use Excel add-ins |
|
|
|
| Lesson 16 8 Create your own Excel add-in |
|
|
|
| Session 16: Exercise |
|
|
|
| Session 16 Exercise Answers |
|
|
|
| Appendix A: Keyboard Shortcuts |
|
|
|
|
[Sample Files] |
|
| Appendix B: Useful Links |
|
|
|
|
| Index |
|
|
|
|
