Vonhoegen Helmut / Фонхоген Гельмут - Excel: The Comprehensive Guide / Excel: Полное руководство [2026, PDF/EPUB, ENG]

Preface .......................................................................................................................... 29
1Basic Knowledge for Working with Excel 31
1.1Starting with a Cost Comparison ................................................................................... 31
1.1.1Starting with the Labels ....................................................................................... 31
1.1.2Entering Data ........................................................................................................... 34
1.1.3Calculating What It Costs .................................................................................... 35
1.1.4Saving the Results .................................................................................................. 38
1.2Basic Concepts ........................................................................................................... 39
1.2.1Workbook, Worksheet, and Cell ........................................................................ 40
1.2.2Cell Content and Cell Format ............................................................................. 42
1.2.3Cell Ranges ................................................................................................................ 42
1.3Starting and Closing Excel ................................................................................................. 44
1.3.1Startup Options ....................................................................................................... 44
1.3.2Starting with an Existing Document ............................................................... 46
1.3.3Exiting Excel ............................................................................................................. 47
1.4Construction Site for Tables and Diagrams ............................................................... 48
1.4.1Two Operating Modes .......................................................................................... 48
1.4.2Local and Network Users ..................................................................................... 51
1.4.3The Application Window ..................................................................................... 53
1.4.4The Ribbon ................................................................................................................ 55
1.4.5The Quick Access Toolbar .................................................................................... 56
1.4.6The Quick Analysis Tool ........................................................................................ 57
1.4.7The Backstage View ............................................................................................... 58
1.4.8Keyboard Shortcuts and Key Sequences ......................................................... 59
1.4.9The Formula Bar ...................................................................................................... 60
1.4.10The Status Bar .......................................................................................................... 62
1.4.11The Workbook Area ............................................................................................... 65
1.4.12Alternative Views ................................................................................................... 66
1.4.13Customizing the Ribbon ....................................................................................... 67
1.4.14Customizing the Quick Access Toolbar ........................................................... 69
1.4.15Task Panes ................................................................................................................. 71
1.4.16The Undo and Redo Commands ........................................................................ 72
1.5File Formats .................................................................................................................... 73
1.5.1The XML Family ....................................................................................................... 73
1.5.2The Standard Open XML Format ....................................................................... 74
1.5.3Advantages of Container Formats .................................................................... 74
1.5.4Strict Open XML Workbooks ............................................................................... 75
1.5.5The Structure of the Open XML Formats ........................................................ 75
1.5.6File Extensions ......................................................................................................... 76
1.5.7File Conversion ........................................................................................................ 76
1.5.8An Alternative: OpenDocument Spreadsheets ............................................ 77
1.6Options for Working with Excel ...................................................................................... 78
1.6.1Customizing Excel to Suit Your Needs ............................................................ 79
1.6.2Showing and Hiding Screen Elements ............................................................ 82
1.6.3Editing Options ....................................................................................................... 84
1.6.4Save Options ............................................................................................................ 86
1.6.5Integrating Add-Ins ................................................................................................ 86
1.6.6List of Built-In Excel Add-Ins (VBA) ................................................................... 88
1.6.7List of Built-In COM Add-Ins ............................................................................... 88
1.7Office Add-Ins ............................................................................................................. 88
1.8Managing Documents ................................................................................................... 90
1.8.1Effective File Management ................................................................................. 90
1.8.2Saving Documents ................................................................................................. 91
1.8.3Choosing the View ................................................................................................. 93
1.8.4Choosing the Desired Folder ............................................................................... 95
1.8.5Quick Access ............................................................................................................. 95
1.8.6Creating New Folders ............................................................................................ 96
1.8.7Choosing a File Name and File Type ................................................................. 96
1.8.8Adding Metadata to a File ................................................................................... 97
1.8.9Opening Recently Used Files .............................................................................. 99
1.8.10Creating New Workbooks .................................................................................... 99
1.8.11Working with Online Templates ....................................................................... 100
1.8.12Storing Custom Templates ................................................................................. 101
1.8.13Opening Existing Files ........................................................................................... 102
1.8.14Opening a File Dialog ............................................................................................ 102
1.8.15Opening Multiple Files at Once ......................................................................... 104
1.8.16Finding Files by Using Search Patterns ........................................................... 104
1.8.17Searching by Using the Search Box .................................................................. 104
1.8.18Opening Finished Documents as Read-Only ................................................. 105
1.8.19Selecting a Folder ................................................................................................... 106
1.8.20Local File Management ........................................................................................ 106
1.8.21Security Options: Password Protection and Encryption ............................ 107
1.8.22Automatic Backup and Recovery ....................................................................... 109
1.8.23Version Control ....................................................................................................... 110
1.8.24The Security Center ................................................................................................ 111
1.9Saving to the Cloud ........................................................................................................ 116
1.9.1OneDrive .................................................................................................................... 116
1.10Excel Help .................................................................................................................... 118
1.10.1The Help Assistant ................................................................................................. 119
1.10.2The Help Tab ............................................................................................................ 119
2The Structure of Spreadsheets 121
2.1Planning and Designing Calculation Models ............................................................ 121
2.1.1What to Consider When Building Tables ....................................................... 121
2.1.2Labels, Values, and Calculation Rules .............................................................. 122
2.1.3Defining the Structure of an Income and Expense Table ......................... 123
2.2Navigation and Selection ................................................................................................... 124
2.2.1Sheet Selection and Group Editing ................................................................... 125
2.2.2Selecting Cells and Cell Ranges ......................................................................... 125
2.2.3Moving and Selecting with the Keyboard ...................................................... 129
2.2.4Selecting Specific Content ................................................................................... 133
2.3Efficient Data Entry and Editing ...................................................................................... 135
2.3.1Text and Character Strings .................................................................................. 135
2.3.2Entering Numbers .................................................................................................. 137
2.3.3Input and Output Formats .................................................................................. 138
2.3.4Fractions, Leading Zeros, Dates, and Times ................................................... 140
2.3.5Changing, Searching, and Deleting Content ................................................. 142
2.3.6Clearing Methods ................................................................................................... 147
2.3.7Clearing Large Areas .............................................................................................. 148
2.3.8Spell-Check and AutoCorrect .............................................................................. 148
2.3.9Automatically Replacing Entries ....................................................................... 150
2.4Automatically Generating Data ...................................................................................... 151
2.4.1Dragging Data Series with a Mouse ................................................................ 152
2.4.2Working with Series on a Touchscreen ........................................................... 153
2.4.3Series or Copies? ..................................................................................................... 153
2.4.4Ascending and Descending Sequences ........................................................... 154
2.4.5Time Series ................................................................................................................ 154
2.4.6Arithmetic Series .................................................................................................... 155
2.4.7Geometric Series ..................................................................................................... 155
2.4.8Creating a Trend Analysis .................................................................................... 155
2.4.9Special Options for Date Values ........................................................................ 156
2.4.10Creating a Series in the Dialog Box .................................................................. 157
2.4.11Custom Series .......................................................................................................... 158
2.4.12Input Using Pattern Recognition ....................................................................... 159
2.5Data Entry Validation .......................................................................................................... 161
2.5.1Validation Rules for a Price Column ................................................................. 161
2.5.2Highlighting Incorrect Data ................................................................................ 163
2.5.3Input Lists .................................................................................................................. 163
2.5.4Validation with Formulas .................................................................................... 164
2.5.5Editing or Deleting Rules ..................................................................................... 165
2.5.6Applying Rules ......................................................................................................... 165
2.6Reorganizing and Restructuring Sheets ...................................................................... 165
2.6.1Rearranging and Copying Cell Ranges ............................................................ 165
2.6.2Copying to Adjacent Cells .................................................................................... 169
2.6.3Copying with Reference to the Adjacent Column ....................................... 170
2.6.4Copying to Nonadjacent Cells ............................................................................ 170
2.6.5Copying and Pasting .............................................................................................. 170
2.6.6Copying to Other Sheets or Workbooks .......................................................... 171
2.6.7Copying via the Clipboard ................................................................................... 171
2.6.8Copying to Multiple Sheets ................................................................................. 176
2.6.9Copying Multiple Ranges at Once ..................................................................... 177
2.6.10Copying Formats ..................................................................................................... 179
2.6.11Transposing When Copying ................................................................................ 180
2.6.12Deleting and Inserting Cells ................................................................................ 180
2.6.13Adjusting Column Width ..................................................................................... 184
2.6.14Hiding and Unhiding Columns .......................................................................... 185
2.6.15Changing the Row Height ................................................................................... 186
2.6.16Hiding and Unhiding Rows ................................................................................. 186
2.7Efficient Workbook Management ................................................................................. 187
2.7.1Workbooks as Organizational Tools ................................................................ 187
2.7.2Adding Worksheets ............................................................................................... 187
2.7.3Inserting Windows ................................................................................................. 189
2.7.4Hiding Workbooks, Windows, or Sheets ........................................................ 190
2.7.5Deleting Unnecessary Sheets ............................................................................. 191
2.7.6Rearranging the Order of Sheets ....................................................................... 191
2.7.7Copying Sheets ........................................................................................................ 191
2.7.8Navigating Large Worksheets ............................................................................ 192
2.7.9Navigation ................................................................................................................ 194
2.7.10Defining Worksheet Views .................................................................................. 194
2.7.11Defining a View ....................................................................................................... 195
2.7.12Views in Shared Workbooks ............................................................................... 195
2.7.13Notes and Comments ........................................................................................... 196
3Working with Formulas 201
3.1Formula Structure ........................................................................................................ 201
3.1.1Automatic Calculations in the Status Bar ...................................................... 201
3.1.2The Role of Formulas ............................................................................................. 202
3.1.3Types of Formulas .................................................................................................. 202
3.1.4Data Types ................................................................................................................ 203
3.1.5Operators and Their Precedence ....................................................................... 203
3.1.6Operator Table ........................................................................................................ 204
3.1.7Addition and Subtraction .................................................................................... 205
3.1.8Multiplication and Division ................................................................................. 205
3.1.9Concatenating Text ............................................................................................... 206
3.1.10Testing Logical Formulas ..................................................................................... 207
3.1.11Functions ................................................................................................................... 208
3.2Entering Formulas and Functions ................................................................................... 208
3.2.1Constants in Formulas .......................................................................................... 209
3.2.2Entering References ............................................................................................... 210
3.2.3Range References ................................................................................................... 210
3.2.4Tips for Entering References ............................................................................... 212
3.2.53D References .......................................................................................................... 213
3.2.6Entering External References ............................................................................. 213
3.2.7Help with Entering Functions ............................................................................ 214
3.2.8Manually Entering Functions ............................................................................. 215
3.2.9Entering Formulas with the Insert Function Dialog .......................................................... 217
3.2.10Editing Functions .................................................................................................... 219
3.2.11Nested Functions .................................................................................................... 220
3.2.12Calculating Total Sums ......................................................................................... 222
3.3Relative and Absolute References ................................................................................. 223
3.3.1Working with Relative References .................................................................... 223
3.3.2Absolute and Mixed References ........................................................................ 224
3.3.3Types of References ............................................................................................... 224
3.3.4Mixed Absolute References ................................................................................ 226
3.3.5Summation with Mixed References ................................................................. 227
3.3.6Range Unions and Intersections ....................................................................... 227
3.3.7Calculating with the Quick Analysis Tool ....................................................... 228
3.4Descriptive Range Names .................................................................................................. 229
3.4.1Benefits of Range Names ..................................................................................... 229
3.4.2Naming ...................................................................................................................... 231
3.4.3Setting Range Names ............................................................................................ 232
3.4.4Defining a Name ..................................................................................................... 233
3.4.5The Name Manager ............................................................................................... 234
3.4.6Defining Named Formulas .................................................................................. 234
3.4.7Named Values or Text Elements ....................................................................... 235
3.4.8Importing Names from Labels ........................................................................... 235
3.4.9Using Names in Formulas ................................................................................... 236
3.4.10Correcting Name Definitions ............................................................................. 237
3.4.11Formulas with Undefined Names ..................................................................... 238
3.4.12Inserting Names into a Formula ........................................................................ 238
3.5Array Formulas ............................................................................................................ 239
3.5.1Array Ranges ............................................................................................................ 239
3.5.2Working with Array Formulas ............................................................................ 240
3.5.3Simplifying Calculations ...................................................................................... 243
3.5.4Modifying an Array Formula ............................................................................... 243
3.6Ensuring Quality and Preventing Errors ...................................................................... 244
3.6.1Verifiability ............................................................................................................... 244
3.6.2Flexibility ................................................................................................................... 244
3.6.3Error-Free Operation ............................................................................................. 245
3.6.4Avoiding Errors in Formulas ................................................................................ 245
3.6.5Syntax Checks .......................................................................................................... 247
3.6.6Errors Caused by Values ....................................................................................... 248
3.6.7Background Error Checking ................................................................................. 249
3.6.8Auditing Formulas .................................................................................................. 250
3.6.9Value Monitoring in the Watch Window ....................................................... 253
3.6.10Circular Formulas ................................................................................................... 253
3.6.11Stepping Through Formulas ............................................................................... 254
3.6.12Documenting Formulas ....................................................................................... 255
3.7Referencing Tables with Formulas ................................................................................ 255
3.7.1External Reference Notation .............................................................................. 255
3.7.2Using External References ................................................................................... 256
3.8Impact of Removing Cells .................................................................................................. 259
3.9Recalculation Control .......................................................................................................... 259
3.9.1Calculation Options ............................................................................................... 259
3.9.2Managing Iterative Calculations ....................................................................... 261
3.9.3The Number of Iterations and Minimum Deviation ....................................................... 262
3.9.4Multithreading ........................................................................................................ 263
3.9.5Workbook Options ................................................................................................. 263
4Designing Worksheets 265
4.1Formats for Cells and Cell Ranges .................................................................................. 266
4.2Formatting Tools ............................................................................................................. 266
4.3Choosing the Number Format ......................................................................................... 268
4.3.1Clarity and Accuracy .............................................................................................. 268
4.3.2Predefined and Custom Formats ...................................................................... 268
4.3.3How the Standard Format Works ..................................................................... 268
4.3.4Input Format Determines Output Format ..................................................... 269
4.3.5Format Icons ............................................................................................................. 269
4.3.6Defining a Specific Number Format ................................................................. 271
4.3.7International Currency Formats ........................................................................ 272
4.3.8Date and Time Formats ........................................................................................ 274
4.3.9Text and Special Formats ..................................................................................... 274
4.3.10Custom Formats ..................................................................................................... 275
4.3.11Format Codes ........................................................................................................... 276
4.3.12The Problem with Zeros ....................................................................................... 279
4.3.13Currency Formats ................................................................................................... 280
4.3.14Years ............................................................................................................................ 280
4.4Font Style and Alignment .................................................................................................. 282
4.4.1Choosing the Right Fonts ..................................................................................... 283
4.4.2Aligning Labels and Cell Values ......................................................................... 289
4.4.3Centering Headings Across Multiple Columns ............................................. 290
4.5Borders and Patterns ........................................................................................................... 295
4.5.1Border Line Palette ................................................................................................. 295
4.5.2Drawing Borders ..................................................................................................... 297
4.5.3Colors and Fill Patterns ......................................................................................... 298
4.5.4Using Colors as an Organizational Tool .......................................................... 301
4.5.5Checkboxes ............................................................................................................... 301
4.5.6Image Backgrounds ............................................................................................... 302
4.6Enhancing Sheets with Themes ...................................................................................... 303
4.6.1Applying a Different Theme ............................................................................... 304
4.6.2Customize Themes ................................................................................................ 305
4.7Protecting Sheets and Workbooks ................................................................................ 307
4.7.1Allowing or Preventing Changes ....................................................................... 308
4.7.2Unlocking Input Ranges ....................................................................................... 309
4.7.3Selective Range Protection .................................................................................. 310
4.8Consistent Design Using Styles ....................................................................................... 311
4.8.1Copying Formats ..................................................................................................... 311
4.8.2Reusing Styles .......................................................................................................... 312
4.8.3Importing Styles into Other Workbooks ......................................................... 315
4.8.4Defining Styles Without a Sample Cell ........................................................... 316
4.9Table Styles ................................................................................................................. 317
4.9.1Applying a Table Style ........................................................................................... 317
4.9.2Applying a Table Style ........................................................................................... 319
4.9.3Designing a Table Style ........................................................................................ 320
4.9.4Deleting Table Styles ............................................................................................. 321
4.10Data Analysis with Conditional Formatting .............................................................. 322
4.10.1Data Bars ................................................................................................................... 322
4.10.2Color Scales ............................................................................................................... 324
4.10.3Icon Sets ..................................................................................................................... 325
4.10.4Simple Comparison Rules .................................................................................... 326
4.10.5More Complex Rules ............................................................................................. 327
4.10.6The Rule Manager .................................................................................................. 328
4.10.7Quick Formatting with the Quick Analysis Tool ........................................... 329
4.11Improving Clarity with Outline Levels ......................................................................... 330
4.11.1Managing the Outlining Feature ...................................................................... 331
4.11.2Creating Subtotals ................................................................................................. 333
4.11.3Manual Outlining ................................................................................................... 334
4.12Data Entry Using Controls ................................................................................................. 335
4.12.1Selecting Data with a Combo Box .................................................................... 335
4.12.2The Scroll Bar and Spin Button .......................................................................... 337
5Analysis and Forecasting 339
5.1Calculations Without Formulas ...................................................................................... 339
5.1.1Multiplying a Price Column by a Percentage ................................................ 339
5.1.2Combining Ranges ................................................................................................. 340
5.2Consolidating Results .......................................................................................................... 340
5.2.1Consolidating by Position .................................................................................... 340
5.2.2Consolidating by Category .................................................................................. 343
5.3Add-Ins for Statistical Data Analysis ............................................................................. 344
5.3.1A Histogram Showing the Distribution of Deviations ............................... 344
5.4What If Analysis ..................................................................................................................... 346
5.4.1One-Variable Data Tables .................................................................................... 347
5.4.2Multiple Operations with Two Variables ....................................................... 349
5.5Planning Scenarios ................................................................................................................ 350
5.5.1What Scenarios Are For ........................................................................................ 350
5.5.2Planning Alternatives for the Advertising Budget ...................................... 351
5.5.3Defining a Scenario ................................................................................................ 352
5.5.4Editing Scenarios .................................................................................................... 355
5.5.5Summary Reports ................................................................................................... 356
5.6Forecasting Based on Existing Data .............................................................................. 358
5.7Automatic Data Analysis .................................................................................................... 361
6Optimization 363
6.1Goal Seek ....................................................................................................................... 363
6.1.1Determining the Maximum Loan Amount .................................................... 364
6.2Finding Solutions with Solver .......................................................................................... 365
6.2.1Advanced Solver Options ..................................................................................... 365
6.2.2How Solver Works .................................................................................................. 365
6.2.3Example: Material Cost Optimization ............................................................. 368
6.2.4Steps for Solving the Packaging Problem with Solver ............................... 368
6.2.5Evaluating Results and Reports ......................................................................... 373
6.2.6Additional Notes ..................................................................................................... 373
7Presenting Data Graphically 377
7.1Graphical Analysis with Charts ....................................................................................... 377
7.1.1Chart Elements ........................................................................................................ 377
7.1.2Nonrectangular Coordinate Systems .............................................................. 378
7.1.3Data Series and Data Points ............................................................................... 380
7.1.4Additional Chart Elements .................................................................................. 380
7.1.5Chart Area and Plot Area ...................................................................................... 381
7.2Chart Types in Excel .............................................................................................................. 382
7.2.1Charts with Rectangular Coordinate Systems .............................................. 382
7.2.2Discrete or Continuous Subdivisions ............................................................... 382
7.2.3Charts with Nonnumeric X-Axes ....................................................................... 382
7.2.4Charts Without Rectangular Coordinate Systems ...................................... 383
7.3From the Table to the Chart .............................................................................................. 383
7.3.1Creating a Column Chart: First Attempt ........................................................ 383
7.3.2 Recommended Charts ......................................................................................... 385
7.3.3The Quick Chart ....................................................................................................... 386
7.3.4Overview of Chart Design Tools ........................................................................ 387
7.3.5Chart Filter ................................................................................................................ 389
7.3.6Titles, Legends, and Other Options .................................................................. 389
7.3.7Setting the Chart Location .................................................................................. 392
7.4Linking the Table and Chart .............................................................................................. 393
7.4.1Converting a Chart into an Image .................................................................... 394
7.5Optimizing Charts ................................................................................................................. 394
7.5.1Formatting the Current Selection ..................................................................... 395
7.5.2Context Menus ........................................................................................................ 396
7.5.3Combining Chart Types ........................................................................................ 398
7.5.4Improving Shapes ................................................................................................... 399
7.5.5Resizing and Repositioning the Chart ............................................................. 400
7.6Designing Charts .................................................................................................................... 401
7.6.1Changing the Chart Type ..................................................................................... 401
7.6.2Chart Layouts and Chart Styles .......................................................................... 402
7.6.3Custom Templates ................................................................................................. 405
7.6.4Arranging Data Series ........................................................................................... 405
7.6.5Inserting and Formatting Labels ....................................................................... 409
7.6.6Axis Formatting ....................................................................................................... 410
7.6.7Improving Readability with Gridlines .............................................................. 415
7.6.8Formatting Data Series and Data Points ........................................................ 416
7.6.9Data Labels ............................................................................................................... 419
7.6.10Trend Calculation ................................................................................................... 421
7.6.11Drop Lines, High/Low Lines, and Up/Down Bars ......................................... 423
7.6.12Columns Made from Images .............................................................................. 424
8Using Charts Effectively 427
8.1Criteria for Choosing a Chart Type ................................................................................. 427
8.2Standard Charts ......................................................................................................... 427
8.2.1Column Charts ........................................................................................................ 427
8.2.2Bar Charts: Ideal for Long Category Labels .................................................... 429
8.2.3Line Charts: Ideal for Showing Trends ............................................................. 430
8.2.4Pie Charts: For Showing Proportions ............................................................... 431
8.3Value Differentiation with Area and Range Charts ............................................... 433
8.3.1Area Charts ............................................................................................................... 434
8.3.2Range Charts: Not Just for Stocks ..................................................................... 434
8.4Multiple Distributions and Cycles: Doughnut and Radar Charts ..................... 436
8.4.1Doughnut Charts for Comparing Data Groups ............................................ 436
8.4.2Radar Charts for Cycles ......................................................................................... 437
8.5Value Relationships: Scatter and Bubble Charts ..................................................... 438
8.5.1A Rather Limited Selection of Subtypes .......................................................... 439
8.5.2Editing a Chart Type .............................................................................................. 439
8.5.3Bubble Charts as a Type of XY Chart ................................................................ 440
8.6Combo Charts .......................................................................................................................... 441
8.6.1Balancing Differences in Scale ........................................................................... 441
8.6.2Combination Types ................................................................................................ 442
8.73D Effects and True 3D Charts ......................................................................................... 443
8.7.1Real and Pseudo 3D Charts ................................................................................. 443
8.7.2The Viewing Angle Is Key ..................................................................................... 443
8.7.3Charts with Three Axes ........................................................................................ 445
8.7.4Examples of 3D Chart Uses ................................................................................. 446
8.7.5The True 3D Subtypes ........................................................................................... 446
8.7.6A 3D Chart with Equally Weighted Series and Categories ....................... 447
8.83D Surface Charts: Ideal for Continuous Data Visualization ............................. 448
8.8.1Wireframe and Bird’s-Eye View ......................................................................... 450
8.9Additional Chart Types ........................................................................................................ 450
8.9.1Statistical Charts .................................................................................................... 450
8.9.2Waterfall Charts ..................................................................................................... 452
8.9.3Sunbursts .................................................................................................................. 453
8.9.4Treemaps ................................................................................................................... 454
8.9.5Funnel Charts ........................................................................................................... 454
8.9.6Map Charts ............................................................................................................... 455
9Data Visualization with Sparklines 457
9.1Features and Uses ................................................................................................................. 457
9.2Inserting Sparklines .............................................................................................................. 458
9.3Display Options ...................................................................................................................... 460
9.3.1Highlighting Points ................................................................................................ 460
9.4Editing Sparklines .................................................................................................................. 461
9.4.1Changing the Type ................................................................................................. 461
9.4.2Assigning Colors ..................................................................................................... 461
9.4.3Axis Settings ............................................................................................................. 461
9.4.4Handling Empty Cells ............................................................................................ 462
9.4.5Group or Individual Handling ............................................................................. 462
9.4.6Ungrouping Cells .................................................................................................... 463
9.4.7Clearing Sparklines ................................................................................................ 463
9.4.8Editing Data Sources ............................................................................................. 463
10Refining Worksheets with Graphics 465
10.1Overview of the Graphic Tools ........................................................................................ 465
10.2Drawing Preset and Freeform Shapes .......................................................................... 466
10.2.1Drawing a Simple Shape ...................................................................................... 466
10.2.2Freeform Lines ......................................................................................................... 468
10.2.3Creating a Flowchart ............................................................................................. 470
10.3Fine-Tuning Graphic Objects ............................................................................................ 473
10.3.1Adjusting Object Size, Proportion, and Rotation ......................................... 473
10.3.2The Format Shape Task Pane .............................................................................. 474
10.3.3Moving and Copying Objects ............................................................................. 475
10.3.4Object Attributes .................................................................................................... 476
10.3.5Outline and Fill ........................................................................................................ 476
10.3.6Editing Bézier Curves ............................................................................................. 478
10.3.7Techniques for Complex Drawings .................................................................. 480
10.3.8Shape Effects ........................................................................................................... 483
10.3.9Freely Movable Text Boxes .................................................................................. 486
10.3.10Text Decoration for Tables .................................................................................. 488
10.4Creating Organizational Charts in a Hurry ................................................................. 489
10.5Importing and Editing Graphics ...................................................................................... 490
10.5.1Inserting Pictures into Cells ................................................................................ 491
10.5.2Inserting Images over Cells ................................................................................. 492
10.5.3Inserting Images by Using a Function ............................................................. 493
10.5.4Editing Images Directly ........................................................................................ 495
10.5.5Inserting Screenshots ........................................................................................... 499
10.6Using Icons ................................................................................................................................ 500
10.6.1Using the Icons Library ......................................................................................... 500
10.6.2Inserting Custom Vector Graphics ................................................................... 501
10.73D Models and 3D Maps .................................................................................................... 502
10.7.1A 3D Illustration from a File ................................................................................ 502
10.7.2Importing an Online 3D Illustration ................................................................. 504
10.8Freehand Drawing ................................................................................................................. 505
10.8.1Highlights and Handwritten Comments ....................................................... 506
10.8.2Creating Sketches ................................................................................................... 506
10.8.3Inserting Mathematical Equations ................................................................... 507
10.9Finding Images on the Web .............................................................................................. 508
10.9.1Inserting a Photo into a Worksheet ................................................................. 508
10.9.2Inserting Images from OneDrive ....................................................................... 508
10.10Table Snapshots ..................................................................................................................... 509
11Preparing Documents for Publishing 511
11.1Document Inspection ........................................................................................................... 511
11.1.1Document Inspector .............................................................................................. 512
11.1.2Accessibility .............................................................................................................. 514
11.1.3Checking for Compatibility ................................................................................. 515
11.1.4Capacity Issues ........................................................................................................ 516
11.1.5Finalizing Documents ........................................................................................... 517
11.2Encrypt Documents ............................................................................................................... 518
12Publishing Workbooks 519
12.1Preparing Worksheets for Printing ................................................................................ 519
12.1.1Setting the Print Range ........................................................................................ 520
12.1.2Page Layout .............................................................................................................. 522
12.1.3Choosing the Paper Size and Print Layout ..................................................... 525
12.1.4Headers and Footers .............................................................................................. 530
12.1.5Setting the Margins ............................................................................................... 533
12.1.6Interactive Page Break Preview .......................................................................... 535
12.1.7Reviewing the Layout in Print Preview ............................................................ 536
12.2Printer Selection and Printer Settings .......................................................................... 537
12.2.1Quick Print and Printing Options ...................................................................... 538
12.2.2Quick Print ................................................................................................................ 539
12.2.3Choosing Printing Options .................................................................................. 539
12.3Sending Worksheets by Email ......................................................................................... 541
12.3.1Sending a Workbook as an Attachment ......................................................... 541
12.3.2Sending a Workbook as a PDF ............................................................................ 542
12.4Creating a PDF or XPS Copy ............................................................................................... 542
13Excel Data on the Web 545
13.1Integration with the Internet and Intranet ............................................................... 545
13.2From Excel to HTML and Back .......................................................................................... 545
13.2.1Component Distribution ...................................................................................... 546
13.2.2Web Archives ........................................................................................................... 547
13.2.3Web Options ............................................................................................................ 547
13.3Providing Data for the Web .............................................................................................. 548
13.3.1Publishing Excel Data on the Web .................................................................... 548
13.4Linking Documents with Hyperlinks ............................................................................. 550
13.4.1Jumping from a Cell ............................................................................................... 551
13.4.2Automatic Link Creation ...................................................................................... 552
13.4.3Linking to Documents ........................................................................................... 552
13.4.4A Hyperdocument Composed of Workbooks ................................................ 552
13.4.5Downloads ................................................................................................................ 554
13.4.6Email Links ................................................................................................................ 554
13.4.7Hyperlinks Using the Table Function ............................................................... 554
13.4.8Formatting Hyperlinks .......................................................................................... 556
13.4.9Hyperlinking from a Graphic Object ................................................................ 556
13.4.10Editing Hyperlinks .................................................................................................. 557
13.5Processing XML Data ............................................................................................................ 557
13.5.1Importing XML Data .............................................................................................. 557
13.5.2Linking the Data Source to the Table ............................................................... 564
14Collaborating on Workbooks 567
14.1Teamwork in Local Networks ........................................................................................... 567
14.1.1Sharing a Workbook .............................................................................................. 568
14.1.2Showing Changes ................................................................................................... 569
14.1.3Reviewing Changes ................................................................................................ 570
14.1.4Sharing on a Single Workstation ....................................................................... 571
14.1.5Limitations of Shared Workbooks .................................................................... 572
14.2Collaborating via OneDrive ............................................................................................... 572
14.2.1Sharing Workbooks ................................................................................................ 573
14.2.2Collaborate ............................................................................................................... 575
15Table Functions 577
15.1Functions Introduced in Excel 2013 .............................................................................. 577
15.2Functions Introduced Since Excel 2016 ....................................................................... 578
15.3Functions Introduced Since Excel 2021 ....................................................................... 579
15.4Structure and Use of Functions ....................................................................................... 579
15.4.1Function Arguments .............................................................................................. 579
15.4.2Functions in Macros .............................................................................................. 581
15.5Financial Mathematical Functions ................................................................................. 581
15.5.1Functions for Annuity Calculations .................................................................. 581
15.5.2Loan Calculations ................................................................................................... 582
15.5.3Depreciation Calculation ..................................................................................... 582
15.5.4Example of a Depreciation Calculation ........................................................... 583
15.5.5Functions for Securities Calculations .............................................................. 584
15.5.6List of Financial Math Functions ....................................................................... 587
15.6Date and Time Functions ................................................................................................... 591
15.6.1The Advantages of Using Serial Numbers ...................................................... 591
15.6.2Calculating Periodic Date Series ........................................................................ 592
15.6.3Calculating Periodic Time Series ....................................................................... 593
15.6.4Calculating Working Hours ................................................................................. 593
15.6.5List of Date and Time Functions ........................................................................ 594
15.7Mathematical Functions .................................................................................................... 596
15.7.1Sums and Conditional Sums ............................................................................... 596
15.7.2Rounding Values ..................................................................................................... 598
15.7.3Basic Mathematical Functions ........................................................................... 600
15.7.4Factorials and Combinations .............................................................................. 601
15.7.5Generating Random Numbers and Sequences ............................................ 601
15.7.6Trigonometric Functions ...................................................................................... 603
15.7.7Hyperbolic Functions ............................................................................................ 604
15.7.8List of Mathematical Functions ......................................................................... 604
15.8Statistical Functions .................................................................................................... 608
15.8.1Overview of Statistical Functions ..................................................................... 608
15.8.2Samples and Populations .................................................................................... 609
15.8.3Random Variables and Probability ................................................................... 609
15.8.4Sample Analysis ...................................................................................................... 610
15.8.5Statistical Tests ....................................................................................................... 610
15.8.6Distribution Functions .......................................................................................... 611
15.8.7Calculating the Standard Deviation of Test Results ................................... 613
15.8.8Conditional Maximum and Minimum Values .............................................. 614
15.8.9List of Statistical Functions ............................................................................... 614
15.8.10List of Compatible Functions ............................................................................ 621
15.9Lookup and Reference Functions ................................................................................... 624
15.9.1Filtering, Sorting, and Reducing Data ............................................................ 624
15.9.2Querying Lookup Tables ..................................................................................... 626
15.9.3Working with INDEX() Functions .................................................................... 629
15.9.4Example of the CHOOSE() Function ............................................................... 630
15.9.5Lookup Functions ................................................................................................. 631
15.9.6Inserting Images as Cell Content .................................................................... 632
15.9.7Additional Functions for Arrays ....................................................................... 632
15.9.8Aggregating Values from Tables ..................................................................... 635
15.9.9List of Lookup and Reference Functions ....................................................... 636
15.10Database Functions .............................................................................................................. 638
15.10.1Analyzing a Table ................................................................................................. 638
15.10.2List of Database Functions ................................................................................ 639
15.11Cube Functions ....................................................................................................................... 640
15.11.1Special Features of Cube Functions ............................................................... 640
15.11.2List of Cube Functions ......................................................................................... 641
15.12Text Functions ......................................................................................................................... 641
15.12.1Extracting Parts of Strings ................................................................................. 641
15.12.2Operations with Regular Expressions ........................................................... 642
15.12.3Concatenating Strings ........................................................................................ 644
15.12.4Sorting with Text Functions ............................................................................. 646
15.12.5Including Logical Values in Text ...................................................................... 646
15.12.6Combining Text with a Date ............................................................................ 646
15.12.7Detecting Languages and Translating .......................................................... 647
15.12.8List of Text Functions .......................................................................................... 647
15.13Logical Functions ................................................................................................................... 649
15.13.1TRUE or FALSE as Arguments ........................................................................... 650
15.13.2Checking Conditions ........................................................................................... 651
15.13.3Checking Multiple Conditions ......................................................................... 652
15.13.4Automatically Adjusting Text .......................................................................... 653
15.13.5Conditional Text Display .................................................................................... 654
15.13.6Text Checking ........................................................................................................ 654
15.13.7Checks with Complex Conditions ................................................................... 654
15.13.8Multiple Branching .............................................................................................. 655
15.13.9Array Functions ..................................................................................................... 656
15.13.10LET() and LAMBDA() ............................................................................................. 657
15.13.11List of Logical Functions ..................................................................................... 659
15.14Information Functions ......................................................................................................... 660
15.14.1Example: Preventing Errors ................................................................................. 660
15.14.2List of Information Functions ............................................................................. 660
15.15Technical Functions .............................................................................................................. 661
15.15.1Converting Units of Measure ............................................................................. 661
15.15.2Bessel Functions ..................................................................................................... 662
15.15.3Conversions Between Number Systems ......................................................... 663
15.15.4Calculations with Complex Numbers .............................................................. 664
15.15.5List of Technical Functions .................................................................................. 664
15.16Web Functions ........................................................................................................................ 667
15.16.1Web Queries ............................................................................................................. 667
15.16.2List of Web Functions ............................................................................................ 668
16Organizing and Managing Information as Tables 669
16.1Tables, Data Lists, and Data Tables ............................................................................... 669
16.2A Table for an Inventory List ............................................................................................. 669
16.3Applications for Using Tables .......................................................................................... 670
16.4Defining Table Structure .................................................................................................... 671
16.4.1An Inventory Table for a Wine Warehouse .................................................... 671
16.4.2Data Types and Field Lengths ............................................................................. 672
16.4.3Rules for Choosing Column Names .................................................................. 673
16.5Table Ranges ............................................................................................................................ 673
16.5.1Converting Cell Ranges to Table Ranges ........................................................ 673
16.5.2Formatting Tables .................................................................................................. 675
16.5.3Table Options ........................................................................................................... 676
16.5.4Freezing Labels ........................................................................................................ 676
16.5.5Entering Data ........................................................................................................... 677
16.5.6Uniqueness and Duplicates ................................................................................ 677
16.5.7Expanding and Formatting Ranges .................................................................. 678
16.5.8Expanding Tables ................................................................................................... 678
16.5.9Inserting Totals Rows ............................................................................................ 679
16.5.10Calculated Columns .............................................................................................. 679
16.5.11Working with Structured References .............................................................. 680
16.6Sorting Data ............................................................................................................................. 681
16.6.1Sorting for Different Purposes ........................................................................... 682
16.6.2The Sort Key .............................................................................................................. 682
16.6.3The Sort Order ......................................................................................................... 683
16.6.4Custom Sorting ....................................................................................................... 685
16.6.5Sorting by Formatting ........................................................................................... 687
16.6.6Sorting by Using a Custom Order ..................................................................... 688
16.7Group Data ............................................................................................................................... 689
16.7.1Inserting Subtotals ................................................................................................. 690
16.7.2Calculations for Subgroups ................................................................................. 692
17Data Queries and Data Extracts 693
17.1What’s the Best Way to Formulate Queries? ............................................................ 693
17.2Filtering Relevant Data ....................................................................................................... 693
17.2.1Location-Independent Filtering and Sorting ................................................. 695
17.2.2Text Filters ................................................................................................................ 695
17.2.3Number Filters ......................................................................................................... 696
17.2.4Date Filters ................................................................................................................ 697
17.2.5Color Filters ............................................................................................................... 698
17.2.6Sorting ........................................................................................................................ 699
17.2.7Searching and Filtering ......................................................................................... 700
17.2.8Filtering and Sorting by Cell Values ................................................................. 702
17.2.9Combining Filters ................................................................................................... 702
17.2.10Filtering with Slicers .............................................................................................. 703
17.3Complex Queries with Advanced Filters ..................................................................... 704
17.3.1Table and Criteria Ranges .................................................................................... 704
17.3.2Data Extracts in the Output Range .................................................................. 705
17.3.3Checking Inventory with the Advanced Filter ............................................... 706
17.3.4What Selection Criteria Are Available? ........................................................... 708
17.3.5Searching with Calculated Criteria ................................................................... 711
17.4Further Processing of Filtered Data ............................................................................... 712
17.5Calculations with Database Functions ........................................................................ 713
18PivotTables and Charts 715
18.1Interactive Tables and Charts .......................................................................................... 716
18.2Applications ............................................................................................................................. 717
18.3Suitable Data ........................................................................................................................... 717
18.4Data Analysis with PivotTables ....................................................................................... 717
18.4.1From Source Data to PivotTable ........................................................................ 717
18.4.2PivotTable Layout ................................................................................................... 720
18.4.3Recommended PivotTables ................................................................................. 725
18.4.4Adding Value Columns ......................................................................................... 725
18.4.5Changing the PivotTable Layout ....................................................................... 726
18.4.6Options for the PivotTable Report .................................................................... 729
18.4.7Adding Fields ............................................................................................................ 730
18.4.8Sorting in the PivotTable ...................................................................................... 731
18.4.9Quick Data Extracts for Individual Values ...................................................... 733
18.4.10Slicers .......................................................................................................................... 734
18.4.11Changing Settings for Individual Fields .......................................................... 737
18.4.12Showing and Hiding Subtotals and Grand Totals ....................................... 739
18.4.13Options for Layout and Printing ........................................................................ 739
18.4.14Changing the Calculation Method ................................................................... 740
18.4.15Special Options for Report Filters ..................................................................... 742
18.4.16Showing and Hiding Detailed Information ................................................... 742
18.4.17Creating New Groups ............................................................................................ 743
18.4.18Organizing Numerical Data ................................................................................ 744
18.4.19Inserting Timelines ................................................................................................ 747
18.4.20Calculated Fields and Items in PivotTables ................................................... 749
18.4.21Formatting PivotTables ........................................................................................ 751
18.4.22Quickly Selecting Data Groups .......................................................................... 753
18.4.23Conditional Formatting in PivotTables ........................................................... 753
18.4.24Changing, Moving, and Deleting a Data Source .......................................... 754
18.4.25Data Types and Images in PivotTables ............................................................ 754
18.4.26PivotTables from External Data ......................................................................... 756
18.4.27Default Format for PivotTables ......................................................................... 756
18.5Dynamic Charts from PivotTables .................................................................................. 757
18.5.1Slicers for a PivotChart .......................................................................................... 759
18.6Data Models with Multiple Tables ................................................................................. 760
18.6.1Building a Model for a PivotTable ..................................................................... 760
18.6.2Defining Relationships ......................................................................................... 762
18.7Power Pivot ............................................................................................................. 763
18.7.1Activating the Add-In ............................................................................................ 763
18.7.2Data Preparation .................................................................................................... 764
18.7.3Interactive Linking of Tables ............................................................................... 767
18.7.4Access via CUBE Functions .................................................................................. 770
18.7.5Creating Calculated Columns ............................................................................ 770
19Working with External Data 773
19.1Importing Access Data ........................................................................................................ 776
19.2Querying XML Files ............................................................................................................... 779
19.3Importing a CSV File ............................................................................................................. 782
19.4Working with Linked Data Types ................................................................................... 784
19.5Importing Data from Pictures .......................................................................................... 787
20Export and Import of Files 789
20.1Data Formats and Filters .................................................................................................... 789
20.2Supported File Formats ....................................................................................................... 789
20.2.1Output Formats ...................................................................................................... 790
20.2.2Import Formats ....................................................................................................... 791
20.2.3Working in Compatibility Mode ........................................................................ 792
20.2.4Text Formats ............................................................................................................ 792
20.3Importing Text Files .............................................................................................................. 793
20.3.1Importing an Address List .................................................................................... 793
20.3.2The Fixed Width Data Format ............................................................................ 796
20.4Distributing Text Across Columns .................................................................................. 797
21Exchanging Data with Other Applications 799
21.1Exchanging Data via the Clipboard ............................................................................... 799
21.1.1Word Imports Data from Excel .......................................................................... 799
21.1.2Exporting Data via the Clipboard ...................................................................... 800
21.1.3How the Clipboard Works ................................................................................... 801
21.1.4Importing Text from Word .................................................................................. 802
21.2Linking Files Dynamically ................................................................................................... 802
21.2.1Linking Between Documents ............................................................................. 803
21.2.2Update Control ........................................................................................................ 803
22Automating Routine Tasks with Macros 807
22.1Recording Macros .................................................................................................................. 807
22.1.1Preparations ............................................................................................................. 808
22.1.2Recording a Schedule ............................................................................................ 810
22.1.3What Does the Recording Look Like? ............................................................... 811
22.1.4Saving the Workbook with the Macro ............................................................. 813
22.1.5Running the Macro ................................................................................................ 813
22.2Integrating Macros into the Workflow ....................................................................... 814
22.2.1Quick Start with Icons ........................................................................................... 814
22.2.2Starting a Macro with Buttons or Graphic Objects ..................................... 816
22.3Flipping a Table with a Macro .......................................................................................... 817
22.3.1Transposing with a Macro ................................................................................... 817
22.4Macros for Chart Formatting ............................................................................................ 820
22.5Macros from Older Excel Versions ................................................................................. 821
23Visual Basic for Applications 823
23.1Basics of VBA ........................................................................................................................... 823
23.1.1The Excel Object Model ........................................................................................ 823
23.1.2Events Control the Program Flow ..................................................................... 825
23.1.3Variables and Constants in VBA ........................................................................ 825
23.1.4Basic Units and Language Elements ................................................................ 830
23.2The Development Environment ...................................................................................... 839
23.2.1Project Explorer and Module Window ............................................................ 839
23.2.2Editing Aids ............................................................................................................... 844
23.2.3Testing Programs .................................................................................................... 851
23.2.4Printing Code and Forms ..................................................................................... 854
23.3Input and Output ................................................................................................................... 854
23.3.1Simple Input Dialog ............................................................................................... 855
23.3.2Message Dialogs ..................................................................................................... 856
23.3.3Selecting Ranges in Worksheets ....................................................................... 856
23.3.4Assigning Values to Cells ..................................................................................... 858
23.3.5Entering Formulas .................................................................................................. 859
23.3.6Reading Data from Worksheets ........................................................................ 860
23.3.7A Macro for a Sheet List ........................................................................................ 860
23.4Designing Forms .................................................................................................................... 861
23.4.1Developing an Input Form ................................................................................... 862
23.4.2Inserting Input Controls ....................................................................................... 863
23.4.3Adding Buttons ....................................................................................................... 864
23.4.4Entering the Procedures ....................................................................................... 865
23.4.5Expandable Tool Collection ................................................................................. 868
23.5Table Functions in VBA ....................................................................................................... 870
23.5.1Calling Built-In Functions .................................................................................... 870
23.5.2Custom Table Functions ...................................................................................... 871
23.5.3Retrieving the Sheet Name ................................................................................. 873
24Preview 875
24.1Office Scripts: The Alternative to Macros ................................................................... 875
24.2Copilot: The Helpful Assistant ......................................................................................... 877
Appendices 881
AFunction Keys, Keys, and Keyboard Shortcuts ......................................................... 881
A.1Overview of General Key Functions .................................................................881
A.2Edit Keys and Keyboard Shortcuts ....................................................................885
A.3Navigation and Selection Keys ...........................................................................886
BThe Author ................................................................................................................................ 889
Index .......................................................................................................................................................... 891
Service Pages .............................................................................................................................................. I
Legal Notes .................................................................................................................................................. II