Excel The Comprehensive Guide

... Preface ... 29






... Excel Standalone and Excel Subscription ... 29






1 ... Basic Knowledge for Working with Excel ... 31






1.1 ... Starting with a Cost Comparison ... 31






1.2 ... Basic Concepts ... 39






1.3 ... Starting and Closing Excel ... 44






1.4 ... Construction Site for Tables and Diagrams ... 48






1.5 ... File Formats ... 73






1.6 ... Options for Working with Excel ... 78






1.7 ... Office Add-Ins ... 88






1.8 ... Managing Documents ... 90






1.9 ... Saving to the Cloud ... 116






1.10 ... Excel Help ... 118






2 ... The Structure of Spreadsheets ... 121






2.1 ... Planning and Designing Calculation Models ... 121






2.2 ... Navigation and Selection ... 124






2.3 ... Efficient Data Entry and Editing ... 135






2.4 ... Automatically Generating Data ... 151






2.5 ... Data Entry Validation ... 161






2.6 ... Reorganizing and Restructuring Sheets ... 165






2.7 ... Efficient Workbook Management ... 187






3 ... Working with Formulas ... 201






3.1 ... Formula Structure ... 201






3.2 ... Entering Formulas and Functions ... 208






3.3 ... Relative and Absolute References ... 223






3.4 ... Descriptive Range Names ... 229






3.5 ... Array Formulas ... 239






3.6 ... Ensuring Quality and Preventing Errors ... 244






3.7 ... Referencing Tables with Formulas ... 255






3.8 ... Impact of Removing Cells ... 259






3.9 ... Recalculation Control ... 259






4 ... Designing Worksheets ... 265






4.1 ... Formats for Cells and Cell Ranges ... 266






4.2 ... Formatting Tools ... 266






4.3 ... Choosing the Number Format ... 268






4.4 ... Font Style and Alignment ... 282






4.5 ... Borders and Patterns ... 295






4.6 ... Enhancing Sheets with Themes ... 303






4.7 ... Protecting Sheets and Workbooks ... 307






4.8 ... Consistent Design Using Styles ... 311






4.9 ... Table Styles ... 317






4.10 ... Data Analysis with Conditional Formatting ... 322






4.11 ... Improving Clarity with Outline Levels ... 330






4.12 ... Data Entry Using Controls ... 335






5 ... Analysis and Forecasting ... 339






5.1 ... Calculations Without Formulas ... 339






5.2 ... Consolidating Results ... 340






5.3 ... Add-Ins for Statistical Data Analysis ... 344






5.4 ... What If Analysis ... 346






5.5 ... Planning Scenarios ... 350






5.6 ... Forecasting Based on Existing Data ... 358






5.7 ... Automatic Data Analysis ... 361






6 ... Optimization ... 363






6.1 ... Goal Seek ... 363






6.2 ... Finding Solutions with Solver ... 365






7 ... Presenting Data Graphically ... 377






7.1 ... Graphical Analysis with Charts ... 377






7.2 ... Chart Types in Excel ... 382






7.3 ... From the Table to the Chart ... 383






7.4 ... Linking the Table and Chart ... 393






7.5 ... Optimizing Charts ... 394






7.6 ... Designing Charts ... 401






8 ... Using Charts Effectively ... 427






8.1 ... Criteria for Choosing a Chart Type ... 427






8.2 ... Standard Charts ... 427






8.3 ... Value Differentiation with Area and Range Charts ... 433






8.4 ... Multiple Distributions and Cycles: Doughnut and Radar Charts ... 436






8.5 ... Value Relationships: Scatter and Bubble Charts ... 438






8.6 ... Combo Charts ... 441






8.7 ... 3D Effects and True 3D Charts ... 443






8.8 ... 3D Surface Charts: Ideal for Continuous Data Visualization ... 448






8.9 ... Additional Chart Types ... 450






9 ... Data Visualization with Sparklines ... 457






9.1 ... Features and Uses ... 457






9.2 ... Inserting Sparklines ... 458






9.3 ... Display Options ... 460






9.4 ... Editing Sparklines ... 461






10 ... Refining Worksheets with Graphics ... 465






10.1 ... Overview of the Graphic Tools ... 465






10.2 ... Drawing Preset and Freeform Shapes ... 466






10.3 ... Fine-Tuning Graphic Objects ... 473






10.4 ... Creating Organizational Charts in a Hurry ... 489






10.5 ... Importing and Editing Graphics ... 490






10.6 ... Using Icons ... 500






10.7 ... 3D Models and 3D Maps ... 502






10.8 ... Freehand Drawing ... 505






10.9 ... Finding Images on the Web ... 508






10.10 ... Table Snapshots ... 509






11 ... Preparing Documents for Publishing ... 511






11.1 ... Document Inspection ... 511






11.2 ... Encrypt Documents ... 518






12 ... Publishing Workbooks ... 519






12.1 ... Preparing Worksheets for Printing ... 519






12.2 ... Printer Selection and Printer Settings ... 537






12.3 ... Sending Worksheets by Email ... 541






12.4 ... Creating a PDF or XPS Copy ... 542






13 ... Excel Data on the Web ... 545






13.1 ... Integration with the Internet and Intranet ... 545






13.2 ... From Excel to HTML and Back ... 545






13.3 ... Providing Data for the Web ... 548






13.4 ... Linking Documents with Hyperlinks ... 550






13.5 ... Processing XML Data ... 557






14 ... Collaborating on Workbooks ... 567






14.1 ... Teamwork in Local Networks ... 567






14.2 ... Collaborating via OneDrive ... 572






15 ... Table Functions ... 577






15.1 ... Functions Introduced in Excel 2013 ... 577






15.2 ... Functions Introduced Since Excel 2016 ... 578






15.3 ... Functions Introduced Since Excel 2021 ... 579






15.4 ... Structure and Use of Functions ... 579






15.5 ... Financial Mathematical Functions ... 581






15.6 ... Date and Time Functions ... 591






15.7 ... Mathematical Functions ... 596






15.8 ... Statistical Functions ... 608






15.9 ... Lookup and Reference Functions ... 624






15.10 ... Database Functions ... 638






15.11 ... Cube Functions ... 640






15.12 ... Text Functions ... 641






15.13 ... Logical Functions ... 649






15.14 ... Information Functions ... 660






15.15 ... Technical Functions ... 661






15.16 ... Web Functions ... 667






16 ... Organizing and Managing Information as Tables ... 669






16.1 ... Tables, Data Lists, and Data Tables ... 669






16.2 ... A Table for an Inventory List ... 669






16.3 ... Applications for Using Tables ... 670






16.4 ... Defining Table Structure ... 671






16.5 ... Table Ranges ... 673






16.6 ... Sorting Data ... 681






16.7 ... Group Data ... 689






17 ... Data Queries and Data Extracts ... 693






17.1 ... What’s the Best Way to Formulate Queries? ... 693






17.2 ... Filtering Relevant Data ... 693






17.3 ... Complex Queries with Advanced Filters ... 704






17.4 ... Further Processing of Filtered Data ... 712






17.5 ... Calculations with Database Functions ... 713






18 ... PivotTables and Charts ... 715






18.1 ... Interactive Tables and Charts ... 716






18.2 ... Applications ... 717






18.3 ... Suitable Data ... 717






18.4 ... Data Analysis with PivotTables ... 717






18.5 ... Dynamic Charts from PivotTables ... 757






18.6 ... Data Models with Multiple Tables ... 760






18.7 ... Power Pivot ... 763






19 ... Working with External Data ... 773






19.1 ... Importing Access Data ... 776






19.2 ... Querying XML Files ... 779






19.3 ... Importing a CSV File ... 782






19.4 ... Working with Linked Data Types ... 784






19.5 ... Importing Data from Pictures ... 787






20 ... Export and Import of Files ... 789






20.1 ... Data Formats and Filters ... 789






20.2 ... Supported File Formats ... 789






20.3 ... Importing Text Files ... 793






20.4 ... Distributing Text Across Columns ... 797






21 ... Exchanging Data with Other Applications ... 799






21.1 ... Exchanging Data via the Clipboard ... 799






21.2 ... Linking Files Dynamically ... 802






22 ... Automating Routine Tasks with Macros ... 807






22.1 ... Recording Macros ... 807






22.2 ... Integrating Macros into the Workflow ... 814






22.3 ... Flipping a Table with a Macro ... 817






22.4 ... Macros for Chart Formatting ... 820






22.5 ... Macros from Older Excel Versions ... 821






23 ... Visual Basic for Applications ... 823






23.1 ... Basics of VBA ... 823






23.2 ... The Development Environment ... 839






23.3 ... Input and Output ... 854






23.4 ... Designing Forms ... 861






23.5 ... Table Functions in VBA ... 870






24 ... Preview ... 875






24.1 ... Office Scripts: The Alternative to Macros ... 875






24.2 ... Copilot: The Helpful Assistant ... 877






A ... Function Keys, Keys, and Keyboard Shortcuts ... 881






A.1 ... Overview of General Key Functions ... 881






A.2 ... Edit Keys and Keyboard Shortcuts ... 885






A.3 ... Navigation and Selection Keys ... 886






B ... The Author ... 889






... Index ... 891