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 a single eight hour day!
Session
One: 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
Two: 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
Three: 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
Four: 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
Five: 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
Six: 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
Seven: 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
Eight:
Summarizing data
Lesson 15 1: Create automatic subtotals
Lesson 15 2: Use summarize functions
Lesson 15 3: Grouping and outlining
Session
Nine: 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
