Inhaltsverzeichnis

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