Excel 2007 Course
Excel 2007
Horizontal Line
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    

Part II - Expert Skills

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 a worksheet as a single 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: 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