Excel 2007 Courses
Excel 2007
Horizontal Line
Module 2 (Expert Skills) Course Outline


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: Tables, Ranges and Databases    
  Session Objectives    
  Lesson 1 1: Apply a simple filter to a range    
  Lesson 1 2: Apply a top 10 and custom filter to a range    
  Lesson 1 3: Apply an advanced filter with multiple OR criteria    
  Lesson 1 4: Apply an advanced filter with complex criteria    
  Lesson 1 5: Apply an advanced filter with function-driven criteria    
  Lesson 1 7: Convert a range into a table with a total row    
  Lesson 1 8: Format a table using table styles and convert a table into a range    
  Lesson 1 9: Create a custom table style    
  Lesson 1 10: Sort a range or table by rows    
  Lesson 1 11: Sort a range by columns    
  Lesson 1 12: Sort a range or table by custom list    
  Lesson 1 13: Name a table and create an automatic structured table reference    
  Lesson 1 14: Create a manual structured table reference    
  Lesson 1 15: Use special items in structured table references    
  Lesson 1 16: 1 16: Understand unqualified structured references    
Session Two: Data Integrity, Subtotals and Validations    
  Session Objectives    
  Lesson 2 1: Keep data atomic using Text to Columns    
  Lesson 2 2: Remove duplicate values from a range or table    
  Lesson 2 3: Automatically subtotal a range    
  Lesson 2 4: Create nested subtotals    
  Lesson 2 5: Consolidate data from multiple data ranges    
  Lesson 2 6: Use data consolidation to generate quick subtotals from tables    
  Lesson 2 7: Validate numerical data    
  Lesson 2 8: Create user-friendly messages for validation errors    
  Lesson 2 9: Create data entry Input Messages    
  Lesson 2 10: Add a formula-driven date validation and a decimal validation    
  Lesson 2 11: Add a dynamic list validation based upon a table    
  Lesson 2 12: Use a function-driven custom validation to enforce complex business rules    
  Lesson 2 13: Use a custom validation to add a unique constraint to a column    
Session Three: Advanced functions and formulas    
  Lesson 3 1: Understand precedence rules and use the Evaluate feature    
  Lesson 3 2: Use common functions with Formula AutoComplete    
  Lesson 3 3: Use the formula palette and the PMT function    
  Lesson 3 4: Use the PV and FV functions to value investments    
  Lesson 3 5: Use the IF logic function    
Lesson 3 6: Use the SUMIF and COUNTIF logic functions to create conditional totals
  Lesson 3 7: Understand date serial numbers    
  Lesson 3 8: Understand common date functions    
  Lesson 3 9: Use the DATEDIF function    
  Lesson 3 10: Use date offsets to manage projects using the scheduling equation    
  Lesson 3 11: Use the DATE function to offset days, months and years    
  Lesson 3 12: Enter time values and perform basic time calculations     
  Lesson 3 13: Perform time calculations that span midnight    
  Lesson 3 14: Understand common time functions and convert date serial numbers to decimal values    
  Lesson 3 15 Use the TIME function to offset hours, minutes and seconds    
  Lesson 3 16 Use the AND and OR functions to construct complex Boolean criteria    
  Lesson 3 17 Understand calculation options (manual and automatic)    
  Lesson 3 18: Concatenate strings using the concatenation operator (&)    
  Lesson 3 19: Use the TEXT function to format numerical values as strings    
  Lesson 3 20: Extract text from fixed width strings using the LEFT, RIGHT and MID functions    
  Lesson 3 21: Extract text from delimited strings using the FIND and LEN functions    
  Lesson 3 22: Use a VLOOKUP function for an exact lookup    
  Lesson 3 23: Use an IFERROR function to suppress error messages    
  Lesson 3 24: Use a VLOOKUP function for an inexact lookup    
Session Four: Session Four: Using Names and the Formula Auditing Tools    
  Lesson 4‑1: Automatically create single-cell range names     
  Lesson 4‑2: Manually create, single cell range names and named constants    
  Lesson 4‑3: Use range names to make formulas more readable    
  Lesson 4‑4: Automatically create range names in two dimensions    
  Lesson 4‑5: Use intersection range names and the INDIRECT function    
  Lesson 4‑6: Create dynamic formula-based range names using the OFFSET function    
  Lesson 4‑7: Create table-based dynamic range names    
  Lesson 4‑8: Create two linked drop-down lists using range names    
  Lesson 4‑9: Understand the #NUM!, #DIV/0! and #NAME? Error Values    
  Lesson 4‑10: Understand the #VALUE!, #REF! and #NULL! Error Values    
  Lesson 4‑11: Understand background error checking and error checking rules    
  Lesson 4‑12: Manually error check a worksheet    
  Lesson 4‑13: Audit a formula by tracing precedents    
  Lesson 4‑14: Audit a formula by tracing dependents    
  Lesson 4‑15: Use the watch window to monitor cell values    
  Lesson 4‑16: Use Speak Cells to eliminate data entry errors    
Session Five: Pivot Tables    
  Lesson 5‑1: Create a one dimensional pivot table report from a table    
  Lesson 5‑2: Create a grouped pivot table report    
  Lesson 5‑3: Understand pivot table rows and columns    
  Lesson 5‑4: Use an external data source    
  Lesson 5‑5: Apply a simple filter and sort to a pivot table    
  Lesson 5‑6: Use report filter fields    
  Lesson 5‑7: Use report filter fields to automatically create multiple pages    
  Lesson 5‑8: Format a pivot table using PivotTable styles    
  Lesson 5‑9: Create a custom PivotTable style    
  Lesson 5‑10: Understand pivot table report layouts    
  Lesson 5‑11: Add/remove subtotals and apply formatting to pivot table fields    
  Lesson 5‑12: Display multiple summations within a single pivot table    
  Lesson 5‑13: Add a calculated field to a pivot table    
  Lesson 5‑14: Add a calculated item to a pivot table    
  Lesson 5‑15: Group by Text    
  Lesson 5‑16: Group by Date    
  Lesson 5‑17: Group by numeric value ranges    
  Lesson 5‑18: Show row data by percentage of total rather than value    
  Lesson 5‑19: Create a pivot chart from a pivot table    
  Lesson 5-20:  Embed multiple pivot tables onto a worksheet    
Session Six: Session Six: What If Analysis and Security    
  Lesson 6 1: Create a single-input data table    
  Lesson 6 2: Create a two-input data table    
  Lesson 6 3: Define Scenarios    
  Lesson 6 4: Create a scenario summary report    
  Lesson 6 5: Use Goal Seek    
  Lesson 6 6: Use Solver    
  Lesson 6 7: Hide and unhide worksheets, columns and rows    
  Lesson 6 8: Create custom views    
  Lesson 6 9: Prevent unauthorized users from opening or modifying workbook    
  Lesson 6 10: Control the changes users can make to workbooks    
  Lesson 6 11: Restrict the cells users are allowed to change    
  Lesson 6 12: Allow different levels of access to a worksheet with multiple passwords    
  Lesson 6 13: Create a digital certificate    
  Lesson 6 14: Add an invisible digital signature to a workbook    
  Lesson 6 15: Add an visible digital signature to a workbook    
Session Seven: Working with the Internet, Other Applications and Workgroups    
  Lesson 7‑1: Publish an Excel worksheet as a web page    
  Lesson 7‑2: Publish multiple worksheets as a web site    
  Lesson 7‑3: Hyperlink to worksheets and ranges     
  Lesson 7‑4: Hyperlink to other workbooks and the Internet    
  Lesson 7‑5: Hyperlink to an e-mail address and enhance the browsing experience    
  Lesson 7‑6: Execute a web query     
  Lesson 7‑7: Embed an Excel worksheet object into a Word document     
  Lesson 7‑8: Embed an Excel chart object into a Word document    
  Lesson 7‑9: Link an Excel worksheet to a Word document    
  Lesson 7‑10: Understand the three different ways to share a document    
  Lesson 7‑11: Share a workbook using the lock method    
  Lesson 7‑12: Share a workbook using the merge method    
  Lesson 7‑13: Share a workbook on a network    
  Lesson 7‑14: Accept and reject changes to shared workbooks    
Session Eight: Session Eight: Forms and Macros    
  Lesson 8‑1: Add group box and option button controls to a worksheet form    
  Lesson 8‑2: Add a combo box control to a worksheet form    
  Lesson 8‑3: Set form control cell links    
  Lesson 8‑4: Connect result cells to a form    
  Lesson 8‑5: Add a check box control to a worksheet form    
  Lesson 8‑6: Use check box data in result cells    
  Lesson 8‑7: Add a temperature gauge chart to a form    
  Lesson 8‑8: Add a single input data table to a form    
  Lesson 8‑9: Improve form usability     
  Lesson 8‑10: Understand macros and VBA    
  Lesson 8‑11: Record a macro with absolute references    
  Lesson 8‑12: Understand macro security    
  Lesson 8‑13: Implement macro security    
  Lesson 8‑14: Record a macro with relative references    
  Lesson 8‑15: Use shapes to run macros    
  Lesson 8‑16: Run a macro from a button control    
 
Home