Learn Excel 2007 Expert Skills with the Smart Method: Courseware Tutorial Teaching Advanced Techniques

This book is for users who want to upgrade their basic Excel skills to true expert level. It will teach you to master all of Excel's advanced features. If you are an absolute beginner you need our Essential Skills book. The book is very, very different to any other computer book you have ever read. The Smart Method provides an entirely new and better way to master advanced Excel features. Here's just four of the many ways in which the book is unique: * This book won't waste your time by teaching basic Excel skills that you already know. All of that material is covered already in our beginner's Essential Skills book. By assuming that you are already able to use Excel's basic features, far more ground can be covered. * Instruction is logically structured into sessions and lessons. While this makes the book ideal for self-instruction, you can also use it to run your own, highly effective, adult training courses. * Every lesson is presented on two facing sheets of A4 paper (the pages are much bigger than in any other Excel book). * Each lesson has a sample file that models a real-world business problem. You'll immediately appreciate the value and relevance of each skill you learn.Advanced concepts are explained without needless technical jargon. Everything is concisely described in a simple, no-nonsense way. No matter what your background is, this book will enable you to master Excel's most advanced features.From the Back Cover Amongst other things you'll learn how to: * Master Excel 2007's new dynamic tables * Enforce complex business rules using function-driven custom validations * Perform What-If analysis to model and compare business scenarios * Use advanced functions to solve real-world business problems * Eliminate errors using Excel's advanced formula auditing tools * Create intuitive user interfaces using form controls * Use Excel's three different file sharing methods for efficient teamwork * Record macros and then execute them from button controls * Understand and use all of Excel's security features * Design advanced Pivot Tables and Pivot Charts * Use your Excel skills to publish a complete web site from a workbook * Create and use dynamic and table-based range names Companies who have taken Smart Method courses include: AOL Time Warner, The United States Army, Daimler Chrysler, Motorola, HSBC, Barclays, American Express, Allied Irish Banks, Imperial Tobacco, Volvo, The BBC, British Petroleum, The Foreign and Commonwealth Office, Unilever, The Institute of Chartered Accountants, The Ministry of Defence, Keele University, Deutsche Bank, HBOS, Transport For London, The Performing Rights Society, Scottish Power, The Office of the Parliamentary Ombudsman, BAE Systems, RBS, Marks & Spencer, Virgin, O2, BMW. ..and many, many others.

Table of Contents:
Session One: Tables, Ranges and Databases Session Objectives Lesson 1 1: Check your program and operating system version Lesson 1 2: Apply a simple filter to a range Lesson 1 3: Apply a top 10 and custom filter to a range Lesson 1 4: Apply an advanced filter with multiple OR criteria Lesson 1 5: Apply an advanced filter with complex criteria Lesson 1 6: Apply an advanced filter with function-driven criteria Lesson 1 7: Extract unique records using an advanced filter Lesson 1 8: Convert a range into a table and add a total row Lesson 1 9: Format a table using table styles and convert a table into a range Lesson 1 10: Create a custom table style Lesson 1 11: Sort a range or table by rows Lesson 1 12: Sort a range by columns Lesson 1 13: Sort a range or table by custom list Lesson 1 14: Name a table and create an automatic structured table reference Lesson 1 15: Create a manual structured table reference Lesson 1 16: Use special items in structured table references Lesson 1 17: Understand unqualified structured table references Session 1: Exercise Session 1: Exercise Answers Session Two: Data Integrity, Subtotals and Validations Session Objectives Lesson 2 1: Split fixed width data using Text to Columns Lesson 2 2: Split delimited data using Text to Columns Fixed width data. Delimited data. 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 text length validation Lesson 2 11: Add a table-based dynamic list validation Lesson 2 12: Use a function-driven custom validation to enforce complex business rules Lesson 2 13: Remove duplicate values from a range or table Lesson 2 14: Use a custom validation to add a unique constraint to a column Session 2: Exercise Session 2: Exercise Answers Session Three: Advanced Functions Session Objectives 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 Present Value Future Value 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 How Excel stores dates The world began in 1900 In Excel, every time is a date, and every date is a time 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 Serial number recap 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 (&) About strings The concatenation operator (&) Lesson 3 19: Use the TEXT function to format numerical values as strings Custom format strings recap 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 3: Exercise Session 3: Exercise Answers Session Four: Using Names and the Formula Auditing Tools Session Objectives 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 check a worksheet for errors 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 4: Exercise Session 4: Exercise Answers Session Five: Pivot Tables Session Objectives 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 5: Exercise Session 5: Exercise Answers Session Six: What If Analysis and Security Session Objectives 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 What is 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 workbooks 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 Why digital certificates are needed. Self-certification and third-party certification. Lesson 6 14: Add an invisible digital signature to a workbook Lesson 6 15: Add a visible digital signature to a workbook Session 6: Exercise Session 6: Exercise Answers Session Seven: Working with the Internet, Other Applications and Workgroups Session Objectives 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 The lock method The merge method Sharing workbooks on a network 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 7: Exercise Session 7: Exercise Answers Session Eight: Forms and Macros Session Objectives 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 appearance and usability Lesson 8 10: Understand macros and VBA Macros record keystrokes and mouse-clicks Lesson 8 11: Record a macro with absolute references Lesson 8 12: Understand macro security Why is security needed? The Excel Workbook and Excel Macro Enabled Workbook formats. Three ways to trust a macro enabled workbook. 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 Session 8: Exercise Session 8: Exercise Answers