| Description |
1 online resource (1 volume) : illustrations |
|
text file |
|
PDF |
| Series |
The expert's voice in Excel |
|
Expert's voice in Excel.
|
| Note |
Includes index. |
| Summary |
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. |
| Contents |
Machine generated contents note: pt. I Core Advanced Excel Concepts -- ch. 1 Introduction to Advanced Excel Essentials -- What to Expect from this Book -- Example Files Used in This Book -- 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 -- Last Word -- ch. 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 -- Last Word -- ch. 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 -- Range Operator (:) -- Union Operator (,) -- 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 -- Legend of XOR()-oh -- Do We Really Need IF? -- Last Word -- ch. 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 -- Last Word -- ch. 5 Working with Form Controls -- Welcome to the Control Room -- Form Control Fundamentals -- ComboBox Control -- ListBox Control -- Scroll Bar Control -- Spinner Control -- CheckBox Control -- Least Favorites: Button, Label, Option Button, and GroupBox Controls -- Creating Scrollable Tables -- Highlighting Data Points on Charts -- Dynamic Legend -- Last Word -- pt. II Real World Example -- ch. 6 Getting Input from Users -- Of Input Forms and Excel -- Simple Input Form -- Custom Formats for Input Validation -- Creating a Spreadsheet-Based Wizard -- Layout Patterns for the Spreadsheet-Based Wizard -- Helper Tab -- Moving Between Views -- Views That Require Additional Instruction -- Components That Provide Information -- Last Word -- ch. 7 Storage Patterns for User Input -- World Health Organization: An Applied Example -- Design of Your Spreadsheet File -- Input Wizard -- Setting Focus to the First Input Cell -- Database -- Input Entry Table -- Database Information Table -- 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 -- Last Word -- ch. 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 -- Sort Column, Your New Best Friend -- Match Index Column, the Sort Column's Buddy -- You Have a "Unique" Problem -- Seeing It Work Altogether -- Last Word -- ch. 9 Perfecting the Presentation -- Implementation and Design of the Weight Adjustment System -- Displaying Data from the Intermediate Table -- Results Information Label -- 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 -- Presentation Display Buttons -- Going Back to the Menu -- Resetting the Weights -- Data Display and Aesthetics -- Weighted vs. Not-Weighted Metrics -- Color Choices -- Data Spacing -- Last Word. |
| Subject |
Microsoft Excel (Computer file)
|
|
Microsoft Excel (Computer file) |
|
Business -- Computer programs.
|
|
Electronic spreadsheets.
|
|
Tableurs. |
|
Business -- Computer programs |
|
Electronic spreadsheets |
| Other Form: |
Printed edition: 9781484207352 |
| ISBN |
1484207351 |
|
9781484207352 |
|
9781484207345 |
|
1484207343 |
| Standard No. |
10.1007/978-1-4842-0734-5 doi |
|
More Resources