Learn Excel 2007 Expert Skills with the Smart Method

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

By (author)


You save US$0.01

Free delivery worldwide

Dispatched from the UK in 4 business days

When will my order arrive?


This 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 learn Excel. Here are just five of the many ways in which this book is unique: The book will equip you with excellent Excel skills, good enough to impress any employer, but it doesn't confuse by attempting to teach skills that are not common in the workplace. Only users who have advanced requirements need progress to the Expert Skills book.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 as it is taught. Both IT professionals and absolute beginners will love this book because it avoids needless technical jargon and concisely explains everything you need in a simple and no-nonsense way.From the Back CoverAmongst other things you'll learn how to: Master Excel 2007's new Fluent User Interface and RibbonUnderstand the new Excel 2007 file formats and when to use them Create your own formulas with the new Formula AutoComplete featureCreate cross worksheet formulas Understand absolute, relative and mixed cell referencesCreate stunning, professional, presentation-quality chartsUse the new Themes feature to create visually excellent worksheets Use Excel 2007's new Trend Line featureBring your data alive with Excel 2007's stunning VisualizationsCreate custom conditional formatting rules with the new Rules ManagerAdd a professional sheen to your work with 3-D ElementsUnderstand and work with three-dimensional worksheet groupsCompanies 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.

show more

Product details

  • Paperback | 356 pages
  • 210.82 x 294.64 x 25.4mm | 816.46g
  • The Smart Method Ltd
  • Douglas, United Kingdom
  • English
  • black & white illustrations
  • 0955459931
  • 9780955459931
  • 67,914

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

show more