Advanced Excel Essentials
Published on 10. November 2014
XIII, 216 pages
978-1-4842-0734-5 (ISBN)
Description
Advanced Excel Essentials is the only book for experienced Excel developers who want to channel their skills into building spreadsheet applications and dashboards. This book starts from the assumption that you are well-versed in Excel and builds on your skills to take them to an advanced level. It provides the building blocks of advanced development and then takes you through the development of your own advanced spreadsheet application. For the seasoned analyst, accountant, financial professional, management consultant, or engineer-this is the book you've been waiting for!
Author Jordan Goldmeier builds on a foundation of industry best practices, bringing his own forward-thinking approach to Excel and rich real-world experience, to distill a unique blend of advanced essentials. Among other topics, he covers advanced formula concepts like array formulas and Boolean logic and provides insight into better code and formulas development. He supports that insight by showing you how to build correctly with hands-on examples.
More details
Other editions
Additional editions
Content
- 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
- 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
