Full Course Outline (Essential
Skills and Expert Skills courses run consecutively across two
days)
Months of planning, thousands of hours of work, six hundred pages of course notes, and years of
Excel teaching experience were involved in designing this
amazing new Smart Method Excel 2007 course.
We didn't just take the 2003 course
and make detail changes. Every single lesson in
this course has been designed from first principles purely
for the new 2007 release.
When we saw the first betas of Excel
2007 we were stunned with the huge advances
Microsoft had made. There wasn't really much
difference between versions 97, 2000, 2002 and 2003.
Excel 2007 isn't really an "upgrade" from Excel 2003 - it
is a completely new experience and massively better in so
many ways.
We knew at once that we'd have to
invest a huge amount of time and effort designing a course
that would empower our clients to take full advantage of
this radical new tool.
You can't take this course anywhere
else, it is unique to The Smart Method. Check out the
course outline below and remember that we'll teach you all of
this in two eight hour days!
Session One: Basic
Skills
Lesson 1 1: Start Excel and check your
program version
Lesson 1 2: Maximize, minimize, re-size,
move, close and zoom the Excel window
Lesson 1 3: Understand the Application and
Workbook windows
Lesson 1 4: Open and navigate a workbook
Lesson 1 5: Save a workbook
Lesson 1 6: Pin a document, use the scroll
bars; view, add and remove worksheet tabs
Lesson 1 7: Use the Ribbon
Lesson 1 8: Understand Ribbon components
Lesson 1 9: Customize the Quick Access
Toolbar and preview the printout
Lesson 1 10: Use the Mini Toolbar, Key Tips
and keyboard shortcuts
Lesson 1 11: Understand Views
Lesson 1 12: Use full screen view
Lesson 1 13: Use the help system
Session Two: Doing
Useful Work with Excel
Session Objectives
Lesson 2 1: Enter text and numbers into a
worksheet
Lesson 2 2: Create a new workbook and view
two workbooks at the same time
Lesson 2 3: Enter data into a range
Lesson 2 4: Use AutoSum to quickly calculate
totals
Lesson 2 5: Select a range of cells and
understand Smart Tags
Lesson 2 6: Understand calculation options
Lesson 2 7: Select cells, rows and columns
both contiguous and non-contiguous
Lesson 2 8: Use AutoSum to quickly calculate
averages
Lesson 2 9: Use AutoSum to add a
non-contiguous range
Lesson 2 10: Create your own formulas
Lesson 2 11: Resize a column and enter
functions using Formula AutoComplete
Lesson 2 12: Use AutoFill for text and
numeric series
Lesson 2 13: Use AutoFill to adjust formulas
Lesson 2 14: Use AutoFill Options
Lesson 2 15: Speed up your Auto Fills and
create a custom fill series
Lesson 2 16: Use the zoom control
Lesson 2 17: Print out your spreadsheet
Session Three: Taking
Your Skills to the Next Level
Session Objectives
Lesson 3 1: Use AutoComplete
Lesson 3 2: Cut, copy and paste
Lesson 3 3: Use Paste Special and the
Multiple Item Clipboard
Lesson 3 4: Cut, copy, paste and paste
special by drag and drop
Lesson 3 5: Insert and delete rows and
columns
Lesson 3 6: Use Undo and Redo
Lesson 3 7: Transpose a range
Lesson 3 8: Check spelling
Lesson 3 9: Insert cell comments
Lesson 3 10: Understand absolute, relative
and mixed cell references
Lesson 3 11: Use AutoCalculate
Lesson 3 12: Create a template
Lesson 3 13: Use a template
Lesson 3 14: Freeze columns and rows
Lesson 3 15: Use the split bars
Lesson 3 16: Advanced use of the Quick
Access Toolbar
Session Four: Making
Your Worksheets Look Professional
Session Objectives
Lesson 4 1: Format fonts
Lesson 4 2: Format numbers using built-in
number formats
Lesson 4 3: Create custom number formats
Lesson 4 4: Understand date serial numbers
Lesson 4 5: Format dates
Lesson 4 6: Adjust row height and column
width
Lesson 4 7: Align the contents of cells
Lesson 4 8: Add borders and lines
Lesson 4 9: Text wrapping
Lesson 4 10: Add color and gradient effects
to cells
Lesson 4 11: Use the Format Painter
Lesson 4 12: Understand themes
Lesson 4 13: Use cell styles
Lesson 4 14: Quickly format a table using
AutoFormat
Lesson 4 15: Create a custom cell style
Lesson 4 16: The Format as Table gallery
Lesson 4 17: Create your own gallery styles
Lesson 4 18: Copy custom styles from one
workbook to another
Lesson 4 19: Use simple Conditional
Formatting
Lesson 4 20: Bring data alive with
visualization
Lesson 4 21: Create a custom visualization
Lesson 4 22: Apply multiple conditional
formats using the Rules Manager
Lesson 4 23: Rotate text
Lesson 4 24: Use WordArt and SmartArt
Lesson 4 25: Understand layers
Session Five: Charts
Session Objectives
Lesson 5 1: Create a simple chart with a
single click
Lesson 5 2: Move, re-size, copy and delete a
chart
Lesson 5 3: Choose a standard chart layout
Lesson 5 4: Create a custom chart layout
Lesson 5 5: Understand chart elements
Lesson 5 6: Move, re-size and delete chart
elements
Lesson 5 7: Format individual chart elements
Lesson 5 8: Add and edit a chart legend
Lesson 5 9: Add, remove and manipulate chart
labels
Lesson 5 10: Create a chart with numerical
axis
Lesson 5 11: Change the source data of a
chart by click and drag
Lesson 5 12: Change the source data of a
chart to a non contiguous range
Lesson 5 13: Dealing with hidden data and
empty data points
Lesson 5 14: View data by rows or by columns
and add a data table
Lesson 5 15: Display data labels next to
each data point
Lesson 5 16: Add a trend line to a chart
Lesson 5 17: Add a secondary chart axis to a
chart
Lesson 5 18: Change the chart type for a
single data series
Lesson 5 19: Emphasize data by manipulating
pie charts
Lesson 5 20: Emphasize data by manipulating
chart axis
Lesson 5 21: Add drawing objects to charts
Lesson 5 22: Add a graduated fill for a
professional chart background
Lesson 5 23: Create your own chart templates
Lesson 5 24: Change the default chart type
Session Six: Working
With Multiple Worksheets and Workbooks
Session Objectives
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
Lesson 6 5: Group worksheets
Lesson 6 6: Create three dimensional ranges
Lesson 6 7: Create cross worksheet formulas
Lesson 6 8: Create cross workbook formulas
using a three dimensional range
Lesson 6 9: Use find and replace
Session Seven: Printing
Your Work
Session Objectives
Lesson 7 1: Print with a single click using
Quick Print
Lesson 7 2: Understand page layout view
Lesson 7 3: Set margins and center the
worksheet on the printed page
Lesson 7 4: Set page orientation, paper size
and scale
Lesson 7 5: Add auto-headers and
auto-footers
Lesson 7 6: Add custom headers and custom
footers
Lesson 7 7: Specify different headers and
footers for the first, odd and even pages
Lesson 7 8: Start page numbering at a value
higher than one
Lesson 7 9: Insert, delete preview and
adjust page breaks
Lesson 7 10: Print only part of a worksheet
Lesson 7 11: Set and clear the print area
Lesson 7 12: Add row and column headings and
grid lines to printed output
Lesson 7 13: Change the paper size
Lesson 7 14: Use page layout view
Lesson 7 15: Use page setup options
Lesson 7 16: Check the printout before
printing using Print Preview
Session
Eight: Security
Session Objectives
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
Nine: Advanced
functions and formulas
Session Objectives
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
Ten: Tables
Lesson 10 1: Understand tables and ranges
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
Eleven: An
introduction to macros and VBA
Lesson 11 1: Record and play a simple macro
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
Twelve: Use
Excel with other applications and external data
Lesson 12 1: Animate an Excel chart with
PowerPoint
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
Thirteen: Excel
and the Internet
Lesson 13 1: Import data from the Internet
using a web query
Lesson 13 2: Use hyperlinks
Lesson 13 3: Publish an Excel worksheet as a
web page
Session
Fourteen: Pivot
Tables
Lesson 14 1: Overview of pivot tables
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
Fifteen:
Summarizing data
Lesson 15 1: Create automatic subtotals
Lesson 15 2: Use summarize functions
Lesson 15 3: Grouping and outlining
Session
Sixteen: What If
Analysis
Lesson 16 1: Create a one-input what-If
analysis data table
Lesson 16 2: Create a two-input what-If
analysis data table
