Excel 2007 Course

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

	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