You are previewing Advanced Excel Essentials.
O'Reilly logo
Advanced Excel Essentials

Book Description


Advanced Excel Essentials is the only book for the experienced Excel developer. This book starts from the assumption that you are well-versed in Excel and builds on your your skills to take them to the advanced level. This book will show you the building blocks of advanced development and then take you through the development of your own advanced spreadsheet application. For the seasoned analyst, accountant, financial professional, management consultant, and engineerthis is the youve been waiting for!

Covers advanced formula topics like array formulas and Boolean logic

Shows you how to build correctly with hands-on examples

Provides insight into better code and formulas development

Builds on a foundation of industry best practices and the authors hard won real-world experience


Table of Contents

  1. Cover
  2. Title
  3. Copyright
  4. Dedication
  5. Contents at a Glance
  6. Contents
  7. About the Author
  8. About the Technical Reviewer
  9. Acknowledgments
  10. Part I: Core Advanced Excel Concepts
    1. Chapter 1: Introduction to Advanced Excel Essentials
      1. What to Expect from this Book
        1. Example Files Used in This Book
      2. The Two Most Important Principles
        1. When It Makes Sense, Do More with Less
        2. Break Every Rule
      3. Available Resources
        1. Google
        2. Chandoo
        3. Cleary and Simply
        4. Contextures
        5. Excel Hero
        6. Peltier Tech
      4. The Last Word
    2. Chapter 2: Visual Basic for Applications for Excel, a Refresher
      1. Making the Most of Your Coding Experience
        1. Tell Excel: Stop Annoying Me!
        2. Make Loud Comments
        3. Pick a Readable Font
        4. Start Using the Immediate Window, Immediately
        5. Opt for Option Explicit
      2. Naming Conventions
        1. Hungarian Notation
        2. “Loose” CamelCase Notation
        3. Named Ranges
        4. Sheet Objects
      3. Referencing
        1. Shorthand References
        2. Worksheet Object Names
        3. Procedures and Macros
      4. Development Styles and Principles
        1. Strive to Store Your Commonly Used Procedures in Relevant Worksheet Tabs
        2. No More Using the ActiveSheet, ActiveCell, ActiveWorkbook, and Selection Objects
        3. Render Unto Excel the Things that are Excel’s, and Unto VBA the Things that Require VBA
        4. Encapsulating Your Work
      5. The Last Word
    3. Chapter 3: Introducing Formula Concepts
      1. Formula Help
        1. F2 to See the Formula of a Select Cell
        2. F9 for On-Demand and Piecewise Calculation
        3. Evaluate Formula Button
      2. Excel Formula Concepts
        1. Operators, in Depth
        2. The Range Operator (:)
        3. The Union Operator (,)
        4. The Intersection Operator ( )
      3. When to Use Conditional Expressions
        1. Deceptively Simple Nested IF Statements
        2. CHOOSE Wisely
        3. Why This Discussion Is Important
      4. Introduction to Boolean Concepts
        1. Condensing Your Work
        2. The Legend of XOR()-oh
      5. Do We Really Need IF?
      6. The Last Word
    4. Chapter 4: Advanced Formula Concepts
      1. Filtering and Highlighting
        1. Filtering with Formulas
        2. Conditional Highlighting Using Formulas
      2. Selecting
      3. Aggregating
        1. Using SUMPRODUCT for Aggregation
        2. You’re About To Be FOILed!
      4. Reusable Components
      5. The Last Word
    5. Chapter 5: Working with Form Controls
      1. Welcome to the Control Room
      2. Form Control Fundamentals
        1. The ComboBox Control
        2. The ListBox Control
        3. The Scroll Bar Control
        4. The Spinner Control
        5. The CheckBox Control
        6. The Least Favorites: Button, Label, Option Button, and GroupBox Controls
      3. Creating Scrollable Tables
      4. Highlighting Data Points on Charts
      5. The Dynamic Legend
      6. The Last Word
  11. Part II: A Real World Example
    1. Chapter 6: Getting Input from Users
      1. Of Input Forms and Excel
        1. A Simple Input Form
        2. Custom Formats for Input Validation
      2. Creating a Spreadsheet-Based Wizard
        1. Layout Patterns for the Spreadsheet-Based Wizard
        2. The Helper Tab
        3. Moving Between Views
        4. Views That Require Additional Instruction
        5. Components That Provide Information
      3. The Last Word
    2. Chapter 7: Storage Patterns for User Input
      1. The World Health Organization: An Applied Example
        1. Design of Your Spreadsheet File
        2. The Input Wizard
        3. Setting Focus to the First Input Cell
      2. The Database
        1. Input Entry Table
        2. Database Information Table
        3. The Backend Database Table
      3. Menu Screen Functionality
        1. Inserting a New Record
        2. Editing an Existing Record
        3. Deleting a Selected Record
        4. Linking the Column of Country Names to the Form Control ListBox
      4. Wizard Summary Buttons
      5. The Last Word
    3. Chapter 8: Building for Sensitivity Analysis
      1. Weighted Average Models
      2. Sensitivity Analysis on a Weighted Average Model
        1. One-Way Sensitivity Analysis
        2. Creating a Linked Values Table
        3. Linking to the Database
      3. Building the Tool
        1. Getting to the Backend, the Intermediate Table
        2. Scrolling Capability
        3. Adjusting the Scroll Bar
        4. Formula-based Sorting Data for Analysis
        5. The Sort Column, Your New Best Friend
        6. The Match Index Column, the Sort Column’s Buddy
        7. You Have a “Unique” Problem
        8. Seeing It Work Altogether
      4. The Last Word
    4. Chapter 9: Perfecting the Presentation
      1. Implementation and Design of the Weight Adjustment System
      2. Displaying Data from the Intermediate Table
        1. Results Information Label
        2. The Current Rank of Each Country
        3. Country Name
        4. Total Scores for Each Country
        5. In-cell Bar Charts for All Metrics
        6. Best Possible Comparisons
        7. Weight Box Progress Meters
      3. “Sort By” Dropdown and Sort Labels
        1. Dropdown Metric Selection
        2. Using Boolean Formulas to Define Which Metric Has Been Selected
        3. Connecting Everything with Conditional Format Highlighting
      4. The Presentation Display Buttons
        1. Going Back to the Menu
        2. Resetting the Weights
      5. Data Display and Aesthetics
        1. Weighted vs. Not-Weighted Metrics
        2. Color Choices
        3. Data Spacing
      6. The Last Word
  12. Index