Excel 2007 Courses

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

	Send this page to a friend
	Send us feedback
	Printer friendly version of this page
	Sign up for newsletter

Related Pages


	Ltd
29 Harley Street, London W1G 9QR Telephone/Fax Numbers are Low Cost Local Call Rate: Tel: +44 (0)845 458 3282 Fax: +44 (0)845 458 3281 EMail: informationrequest@thesmartmethod.com. Copyright © 2003, 2004, 2005, 2006 The Smart Method Ltd, All rights reserved. Web design by miriad. Localised link sites: London, Manchester, Birmingham