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