Excel
Description
If you're using Excel, this is the all-in-one manual you need-brimming with solutions for every conceivable task! Learn how to design tables, use formulas to perform calculations, create analyses, graphically present your data, and share your results. With detailed coverage of both basic functions and advanced features, this book provides guidance for beginners and serves as a reliable reference if you're already familiar with Excel. Up to date for Excel 2024 and Microsoft 365!
In this book, you'll learn about:
a.Excel Fundamentals
Follow a workflow that guides you through Excel's user interface, introduces basic concepts, and demonstrates the core features for working with tables, diagrams, and files.
b.All Your Excel Tasks
Dive into everything Excel has to offer. Master spreadsheet functions, formula usage, and table design. Get step-by-step instructions for analyzing, visualizing, and publishing your data; then see how to automate and customize your tasks with macros and VBA.
c.Practical Examples
See Excel's features in action. Detailed examples and annotated screenshots guide you through each and every spreadsheet function, table design and analysis, data model management, and more.
Highlights include:
1)Mastering basic features
2)Creating spreadsheets
3)Working with formulas
4)Evaluating what-if analyses
5)Designing charts and diagrams
6)Visualizing data with sparklines
7)Organizing data in tables
8)Exporting, importing, and exchanging data
9)Automating tasks with macros
10)Using Visual Basic for Applications (VBA)
More details
Person
Content
... Preface ... 29
... Excel Standalone and Excel Subscription ... 29
1 ... Basic Knowledge for Working with Excel ... 31
1.1 ... Starting with a Cost Comparison ... 31
1.2 ... Basic Concepts ... 39
1.3 ... Starting and Closing Excel ... 44
1.4 ... Construction Site for Tables and Diagrams ... 48
1.5 ... File Formats ... 73
1.6 ... Options for Working with Excel ... 78
1.7 ... Office Add-Ins ... 88
1.8 ... Managing Documents ... 90
1.9 ... Saving to the Cloud ... 116
1.10 ... Excel Help ... 118
2 ... The Structure of Spreadsheets ... 121
2.1 ... Planning and Designing Calculation Models ... 121
2.2 ... Navigation and Selection ... 124
2.3 ... Efficient Data Entry and Editing ... 135
2.4 ... Automatically Generating Data ... 151
2.5 ... Data Entry Validation ... 161
2.6 ... Reorganizing and Restructuring Sheets ... 165
2.7 ... Efficient Workbook Management ... 187
3 ... Working with Formulas ... 201
3.1 ... Formula Structure ... 201
3.2 ... Entering Formulas and Functions ... 208
3.3 ... Relative and Absolute References ... 223
3.4 ... Descriptive Range Names ... 229
3.5 ... Array Formulas ... 239
3.6 ... Ensuring Quality and Preventing Errors ... 244
3.7 ... Referencing Tables with Formulas ... 255
3.8 ... Impact of Removing Cells ... 259
3.9 ... Recalculation Control ... 259
4 ... Designing Worksheets ... 265
4.1 ... Formats for Cells and Cell Ranges ... 266
4.2 ... Formatting Tools ... 266
4.3 ... Choosing the Number Format ... 268
4.4 ... Font Style and Alignment ... 282
4.5 ... Borders and Patterns ... 295
4.6 ... Enhancing Sheets with Themes ... 303
4.7 ... Protecting Sheets and Workbooks ... 307
4.8 ... Consistent Design Using Styles ... 311
4.9 ... Table Styles ... 317
4.10 ... Data Analysis with Conditional Formatting ... 322
4.11 ... Improving Clarity with Outline Levels ... 330
4.12 ... Data Entry Using Controls ... 335
5 ... Analysis and Forecasting ... 339
5.1 ... Calculations Without Formulas ... 339
5.2 ... Consolidating Results ... 340
5.3 ... Add-Ins for Statistical Data Analysis ... 344
5.4 ... What If Analysis ... 346
5.5 ... Planning Scenarios ... 350
5.6 ... Forecasting Based on Existing Data ... 358
5.7 ... Automatic Data Analysis ... 361
6 ... Optimization ... 363
6.1 ... Goal Seek ... 363
6.2 ... Finding Solutions with Solver ... 365
7 ... Presenting Data Graphically ... 377
7.1 ... Graphical Analysis with Charts ... 377
7.2 ... Chart Types in Excel ... 382
7.3 ... From the Table to the Chart ... 383
7.4 ... Linking the Table and Chart ... 393
7.5 ... Optimizing Charts ... 394
7.6 ... Designing Charts ... 401
8 ... Using Charts Effectively ... 427
8.1 ... Criteria for Choosing a Chart Type ... 427
8.2 ... Standard Charts ... 427
8.3 ... Value Differentiation with Area and Range Charts ... 433
8.4 ... Multiple Distributions and Cycles: Doughnut and Radar Charts ... 436
8.5 ... Value Relationships: Scatter and Bubble Charts ... 438
8.6 ... Combo Charts ... 441
8.7 ... 3D Effects and True 3D Charts ... 443
8.8 ... 3D Surface Charts: Ideal for Continuous Data Visualization ... 448
8.9 ... Additional Chart Types ... 450
9 ... Data Visualization with Sparklines ... 457
9.1 ... Features and Uses ... 457
9.2 ... Inserting Sparklines ... 458
9.3 ... Display Options ... 460
9.4 ... Editing Sparklines ... 461
10 ... Refining Worksheets with Graphics ... 465
10.1 ... Overview of the Graphic Tools ... 465
10.2 ... Drawing Preset and Freeform Shapes ... 466
10.3 ... Fine-Tuning Graphic Objects ... 473
10.4 ... Creating Organizational Charts in a Hurry ... 489
10.5 ... Importing and Editing Graphics ... 490
10.6 ... Using Icons ... 500
10.7 ... 3D Models and 3D Maps ... 502
10.8 ... Freehand Drawing ... 505
10.9 ... Finding Images on the Web ... 508
10.10 ... Table Snapshots ... 509
11 ... Preparing Documents for Publishing ... 511
11.1 ... Document Inspection ... 511
11.2 ... Encrypt Documents ... 518
12 ... Publishing Workbooks ... 519
12.1 ... Preparing Worksheets for Printing ... 519
12.2 ... Printer Selection and Printer Settings ... 537
12.3 ... Sending Worksheets by Email ... 541
12.4 ... Creating a PDF or XPS Copy ... 542
13 ... Excel Data on the Web ... 545
13.1 ... Integration with the Internet and Intranet ... 545
13.2 ... From Excel to HTML and Back ... 545
13.3 ... Providing Data for the Web ... 548
13.4 ... Linking Documents with Hyperlinks ... 550
13.5 ... Processing XML Data ... 557
14 ... Collaborating on Workbooks ... 567
14.1 ... Teamwork in Local Networks ... 567
14.2 ... Collaborating via OneDrive ... 572
15 ... Table Functions ... 577
15.1 ... Functions Introduced in Excel 2013 ... 577
15.2 ... Functions Introduced Since Excel 2016 ... 578
15.3 ... Functions Introduced Since Excel 2021 ... 579
15.4 ... Structure and Use of Functions ... 579
15.5 ... Financial Mathematical Functions ... 581
15.6 ... Date and Time Functions ... 591
15.7 ... Mathematical Functions ... 596
15.8 ... Statistical Functions ... 608
15.9 ... Lookup and Reference Functions ... 624
15.10 ... Database Functions ... 638
15.11 ... Cube Functions ... 640
15.12 ... Text Functions ... 641
15.13 ... Logical Functions ... 649
15.14 ... Information Functions ... 660
15.15 ... Technical Functions ... 661
15.16 ... Web Functions ... 667
16 ... Organizing and Managing Information as Tables ... 669
16.1 ... Tables, Data Lists, and Data Tables ... 669
16.2 ... A Table for an Inventory List ... 669
16.3 ... Applications for Using Tables ... 670
16.4 ... Defining Table Structure ... 671
16.5 ... Table Ranges ... 673
16.6 ... Sorting Data ... 681
16.7 ... Group Data ... 689
17 ... Data Queries and Data Extracts ... 693
17.1 ... What's the Best Way to Formulate Queries? ... 693
17.2 ... Filtering Relevant Data ... 693
17.3 ... Complex Queries with Advanced Filters ... 704
17.4 ... Further Processing of Filtered Data ... 712
17.5 ... Calculations with Database Functions ... 713
18 ... PivotTables and Charts ... 715
18.1 ... Interactive Tables and Charts ... 716
18.2 ... Applications ... 717
18.3 ... Suitable Data ... 717
18.4 ... Data Analysis with PivotTables ... 717
18.5 ... Dynamic Charts from PivotTables ... 757
18.6 ... Data Models with Multiple Tables ... 760
18.7 ... Power Pivot ... 763
19 ... Working with External Data ... 773
19.1 ... Importing Access Data ... 776
19.2 ... Querying XML Files ... 779
19.3 ... Importing a CSV File ... 782
19.4 ... Working with Linked Data Types ... 784
19.5 ... Importing Data from Pictures ... 787
20 ... Export and Import of Files ... 789
20.1 ... Data Formats and Filters ... 789
20.2 ... Supported File Formats ... 789
20.3 ... Importing Text Files ... 793
20.4 ... Distributing Text Across Columns ... 797
21 ... Exchanging Data with Other Applications ... 799
21.1 ... Exchanging Data via the Clipboard ... 799
21.2 ... Linking Files Dynamically ... 802
22 ... Automating Routine Tasks with Macros ... 807
22.1 ... Recording Macros ... 807
22.2 ... Integrating Macros into the Workflow ... 814
22.3 ... Flipping a Table with a Macro ... 817
22.4 ... Macros for Chart Formatting ... 820
22.5 ... Macros from Older Excel Versions ... 821
23 ... Visual Basic for Applications ... 823
23.1 ... Basics of VBA ... 823
23.2 ... The Development Environment ... 839
23.3 ... Input and Output ... 854
23.4 ... Designing Forms ... 861
23.5 ... Table Functions in VBA ... 870
24 ... Preview ... 875
24.1 ... Office Scripts: The Alternative to Macros ... 875
24.2 ... Copilot: The Helpful Assistant ... 877
A ... Function Keys, Keys, and Keyboard Shortcuts ... 881
A.1 ... Overview of General Key Functions ... 881
A.2 ... Edit Keys and Keyboard Shortcuts ... 885
A.3 ... Navigation and Selection Keys ... 886
B ... The Author ... 889
... Index ... 891