Excel

The Comprehensive Guide

Book Information

• Publisher: SAP PRESS
• Authors: Helmut Vonhoegen
• Year: 2026
• Edition: 1
• Pages: 910
• Language: English

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!

• Your A-to-Z guide to Microsoft Excel
• Master basic features, data analysis, visualizations, and more
• Work with formulas, table functions, and VBA

Key Highlights

• Mastering basic features
• Creating spreadsheets
• Working with formulas
• Evaluating what-if analyses
• Designing charts and diagrams
• Visualizing data with sparklines
• Organizing data in tables
• Exporting, importing, and exchanging data
• Automating tasks with macros
• Using Visual Basic for Applications (VBA)

You'll learn about

• 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.
• 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.
• 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.

Table of Contents

• Preface
• Excel Standalone and Excel Subscription
• Updates
• What This Book Offers

• 1 Basic Knowledge for Working with Excel
• 1.1 Starting with a Cost Comparison
• 1.1.1 Starting with the Labels
• 1.1.2 Entering Data
• 1.1.3 Calculating What It Costs
• 1.1.4 Saving the Results
• 1.2 Basic Concepts
• 1.2.1 Workbook, Worksheet, and Cell
• 1.2.2 Cell Content and Cell Format
• 1.2.3 Cell Ranges
• 1.3 Starting and Closing Excel
• 1.3.1 Startup Options
• 1.3.2 Starting with an Existing Document
• 1.3.3 Exiting Excel
• 1.4 Construction Site for Tables and Diagrams
• 1.4.1 Two Operating Modes
• 1.4.2 Local and Network Users
• 1.4.3 The Application Window
• 1.4.4 The Ribbon
• 1.4.5 The Quick Access Toolbar
• 1.4.6 The Quick Analysis Tool
• 1.4.7 The Backstage View
• 1.4.8 Keyboard Shortcuts and Key Sequences
• 1.4.9 The Formula Bar
• 1.4.10 The Status Bar
• 1.4.11 The Workbook Area
• 1.4.12 Alternative Views
• 1.4.13 Customizing the Ribbon
• 1.4.14 Customizing the Quick Access Toolbar
• 1.4.15 Task Panes
• 1.4.16 The Undo and Redo Commands
• 1.5 File Formats
• 1.5.1 The XML Family
• 1.5.2 The Standard Open XML Format
• 1.5.3 Advantages of Container Formats
• 1.5.4 Strict Open XML Workbooks
• 1.5.5 The Structure of the Open XML Formats
• 1.5.6 File Extensions
• 1.5.7 File Conversion
• 1.5.8 An Alternative: OpenDocument Spreadsheets
• 1.6 Options for Working with Excel
• 1.6.1 Customizing Excel to Suit Your Needs
• 1.6.2 Showing and Hiding Screen Elements
• 1.6.3 Editing Options
• 1.6.4 Save Options
• 1.6.5 Integrating Add-Ins
• 1.6.6 List of Built-In Excel Add-Ins (VBA)
• 1.6.7 List of Built-In COM Add-Ins
• 1.7 Office Add-Ins
• 1.8 Managing Documents
• 1.8.1 Effective File Management
• 1.8.2 Saving Documents
• 1.8.3 Choosing the View
• 1.8.4 Choosing the Desired Folder
• 1.8.5 Quick Access
• 1.8.6 Creating New Folders
• 1.8.7 Choosing a File Name and File Type
• 1.8.8 Adding Metadata to a File
• 1.8.9 Opening Recently Used Files
• 1.8.10 Creating New Workbooks
• 1.8.11 Working with Online Templates
• 1.8.12 Storing Custom Templates
• 1.8.13 Opening Existing Files
• 1.8.14 Opening a File Dialog
• 1.8.15 Opening Multiple Files at Once
• 1.8.16 Finding Files by Using Search Patterns
• 1.8.17 Searching by Using the Search Box
• 1.8.18 Opening Finished Documents as Read-Only
• 1.8.19 Selecting a Folder
• 1.8.20 Local File Management
• 1.8.21 Security Options: Password Protection and Encryption
• 1.8.22 Automatic Backup and Recovery
• 1.8.23 Version Control
• 1.8.24 The Security Center
• 1.9 Saving to the Cloud
• 1.9.1 OneDrive
• 1.10 Excel Help
• 1.10.1 The Help Assistant
• 1.10.2 The Help Tab

• 2 The Structure of Spreadsheets
• 2.1 Planning and Designing Calculation Models
• 2.1.1 What to Consider When Building Tables
• 2.1.2 Labels, Values, and Calculation Rules
• 2.1.3 Defining the Structure of an Income and Expense Table
• 2.2 Navigation and Selection
• 2.2.1 Sheet Selection and Group Editing
• 2.2.2 Selecting Cells and Cell Ranges
• 2.2.3 Moving and Selecting with the Keyboard
• 2.2.4 Selecting Specific Content
• 2.3 Efficient Data Entry and Editing
• 2.3.1 Text and Character Strings
• 2.3.2 Entering Numbers
• 2.3.3 Input and Output Formats
• 2.3.4 Fractions, Leading Zeros, Dates, and Times
• 2.3.5 Changing, Searching, and Deleting Content
• 2.3.6 Clearing Methods
• 2.3.7 Clearing Large Areas
• 2.3.8 Spell-Check and AutoCorrect
• 2.3.9 Automatically Replacing Entries
• 2.4 Automatically Generating Data
• 2.4.1 Dragging Data Series with a Mouse
• 2.4.2 Working with Series on a Touchscreen
• 2.4.3 Series or Copies?
• 2.4.4 Ascending and Descending Sequences
• 2.4.5 Time Series
• 2.4.6 Arithmetic Series
• 2.4.7 Geometric Series
• 2.4.8 Creating a Trend Analysis
• 2.4.9 Special Options for Date Values
• 2.4.10 Creating a Series in the Dialog Box
• 2.4.11 Custom Series
• 2.4.12 Input Using Pattern Recognition
• 2.5 Data Entry Validation
• 2.5.1 Validation Rules for a Price Column
• 2.5.2 Highlighting Incorrect Data
• 2.5.3 Input Lists
• 2.5.4 Validation with Formulas
• 2.5.5 Editing or Deleting Rules
• 2.5.6 Applying Rules
• 2.6 Reorganizing and Restructuring Sheets
• 2.6.1 Rearranging and Copying Cell Ranges
• 2.6.2 Copying to Adjacent Cells
• 2.6.3 Copying with Reference to the Adjacent Column
• 2.6.4 Copying to Nonadjacent Cells
• 2.6.5 Copying and Pasting
• 2.6.6 Copying to Other Sheets or Workbooks
• 2.6.7 Copying via the Clipboard
• 2.6.8 Copying to Multiple Sheets
• 2.6.9 Copying Multiple Ranges at Once
• 2.6.10 Copying Formats
• 2.6.11 Transposing When Copying
• 2.6.12 Deleting and Inserting Cells
• 2.6.13 Adjusting Column Width
• 2.6.14 Hiding and Unhiding Columns
• 2.6.15 Changing the Row Height
• 2.6.16 Hiding and Unhiding Rows
• 2.7 Efficient Workbook Management
• 2.7.1 Workbooks as Organizational Tools
• 2.7.2 Adding Worksheets
• 2.7.3 Inserting Windows
• 2.7.4 Hiding Workbooks, Windows, or Sheets
• 2.7.5 Deleting Unnecessary Sheets
• 2.7.6 Rearranging the Order of Sheets
• 2.7.7 Copying Sheets
• 2.7.8 Navigating Large Worksheets
• 2.7.9 Navigation
• 2.7.10 Defining Worksheet Views
• 2.7.11 Defining a View
• 2.7.12 Views in Shared Workbooks
• 2.7.13 Notes and Comments

• 3 Working with Formulas
• 3.1 Formula Structure
• 3.1.1 Automatic Calculations in the Status Bar
• 3.1.2 The Role of Formulas
• 3.1.3 Types of Formulas
• 3.1.4 Data Types
• 3.1.5 Operators and Their Precedence
• 3.1.6 Operator Table
• 3.1.7 Addition and Subtraction
• 3.1.8 Multiplication and Division
• 3.1.9 Concatenating Text
• 3.1.10 Testing Logical Formulas
• 3.1.11 Functions
• 3.2 Entering Formulas and Functions
• 3.2.1 Constants in Formulas
• 3.2.2 Entering References
• 3.2.3 Range References
• 3.2.4 Tips for Entering References
• 3.2.5 3D References
• 3.2.6 Entering External References
• 3.2.7 Help with Entering Functions
• 3.2.8 Manually Entering Functions
• 3.2.9 Entering Formulas with the Insert Function Dialog
• 3.2.10 Editing Functions
• 3.2.11 Nested Functions
• 3.2.12 Calculating Total Sums
• 3.3 Relative and Absolute References
• 3.3.1 Working with Relative References
• 3.3.2 Absolute and Mixed References
• 3.3.3 Types of References
• 3.3.4 Mixed Absolute References
• 3.3.5 Summation with Mixed References
• 3.3.6 Range Unions and Intersections
• 3.3.7 Calculating with the Quick Analysis Tool
• 3.4 Descriptive Range Names
• 3.4.1 Benefits of Range Names
• 3.4.2 Naming
• 3.4.3 Setting Range Names
• 3.4.4 Defining a Name
• 3.4.5 The Name Manager
• 3.4.6 Defining Named Formulas
• 3.4.7 Named Values or Text Elements
• 3.4.8 Importing Names from Labels
• 3.4.9 Using Names in Formulas
• 3.4.10 Correcting Name Definitions
• 3.4.11 Formulas with Undefined Names
• 3.4.12 Inserting Names into a Formula
• 3.5 Array Formulas
• 3.5.1 Array Ranges
• 3.5.2 Working with Array Formulas
• 3.5.3 Simplifying Calculations
• 3.5.4 Modifying an Array Formula
• 3.6 Ensuring Quality and Preventing Errors
• 3.6.1 Verifiability
• 3.6.2 Flexibility
• 3.6.3 Error-Free Operation
• 3.6.4 Avoiding Errors in Formulas
• 3.6.5 Syntax Checks
• 3.6.6 Errors Caused by Values
• 3.6.7 Background Error Checking
• 3.6.8 Auditing Formulas
• 3.6.9 Value Monitoring in the Watch Window
• 3.6.10 Circular Formulas
• 3.6.11 Stepping Through Formulas
• 3.6.12 Documenting Formulas
• 3.7 Referencing Tables with Formulas
• 3.7.1 External Reference Notation
• 3.7.2 Using External References
• 3.8 Impact of Removing Cells
• 3.9 Recalculation Control
• 3.9.1 Calculation Options
• 3.9.2 Managing Iterative Calculations
• 3.9.3 The Number of Iterations and Minimum Deviation
• 3.9.4 Multithreading
• 3.9.5 Workbook Options

• 4 Designing Worksheets
• 4.1 Formats for Cells and Cell Ranges
• 4.2 Formatting Tools
• 4.3 Choosing the Number Format
• 4.3.1 Clarity and Accuracy
• 4.3.2 Predefined and Custom Formats
• 4.3.3 How the Standard Format Works
• 4.3.4 Input Format Determines Output Format
• 4.3.5 Format Icons
• 4.3.6 Defining a Specific Number Format
• 4.3.7 International Currency Formats
• 4.3.8 Date and Time Formats
• 4.3.9 Text and Special Formats
• 4.3.10 Custom Formats
• 4.3.11 Format Codes
• 4.3.12 The Problem with Zeros
• 4.3.13 Currency Formats
• 4.3.14 Years
• 4.4 Font Style and Alignment
• 4.4.1 Choosing the Right Fonts
• 4.4.2 Aligning Labels and Cell Values
• 4.4.3 Centering Headings Across Multiple Columns
• 4.5 Borders and Patterns
• 4.5.1 Border Line Palette
• 4.5.2 Drawing Borders
• 4.5.3 Colors and Fill Patterns
• 4.5.4 Using Colors as an Organizational Tool
• 4.5.5 Checkboxes
• 4.5.6 Image Backgrounds
• 4.6 Enhancing Sheets with Themes
• 4.6.1 Applying a Different Theme
• 4.6.2 Customize Themes
• 4.7 Protecting Sheets and Workbooks
• 4.7.1 Allowing or Preventing Changes
• 4.7.2 Unlocking Input Ranges
• 4.7.3 Selective Range Protection
• 4.8 Consistent Design Using Styles
• 4.8.1 Copying Formats
• 4.8.2 Reusing Styles
• 4.8.3 Importing Styles into Other Workbooks
• 4.8.4 Defining Styles Without a Sample Cell
• 4.9 Table Styles
• 4.9.1 Applying a Table Style
• 4.9.2 Applying a Table Style
• 4.9.3 Designing a Table Style
• 4.9.4 Deleting Table Styles
• 4.10 Data Analysis with Conditional Formatting
• 4.10.1 Data Bars
• 4.10.2 Color Scales
• 4.10.3 Icon Sets
• 4.10.4 Simple Comparison Rules
• 4.10.5 More Complex Rules
• 4.10.6 The Rule Manager
• 4.10.7 Quick Formatting with the Quick Analysis Tool
• 4.11 Improving Clarity with Outline Levels
• 4.11.1 Managing the Outlining Feature
• 4.11.2 Creating Subtotals
• 4.11.3 Manual Outlining
• 4.12 Data Entry Using Controls
• 4.12.1 Selecting Data with a Combo Box
• 4.12.2 The Scroll Bar and Spin Button

• 5 Analysis and Forecasting
• 5.1 Calculations Without Formulas
• 5.1.1 Multiplying a Price Column by a Percentage
• 5.1.2 Combining Ranges
• 5.2 Consolidating Results
• 5.2.1 Consolidating by Position
• 5.2.2 Consolidating by Category
• 5.3 Add-Ins for Statistical Data Analysis
• 5.3.1 A Histogram Showing the Distribution of Deviations
• 5.4 What If Analysis
• 5.4.1 One-Variable Data Tables
• 5.4.2 Multiple Operations with Two Variables
• 5.5 Planning Scenarios
• 5.5.1 What Scenarios Are For
• 5.5.2 Planning Alternatives for the Advertising Budget
• 5.5.3 Defining a Scenario
• 5.5.4 Editing Scenarios
• 5.5.5 Summary Reports
• 5.6 Forecasting Based on Existing Data
• 5.7 Automatic Data Analysis

• 6 Optimization
• 6.1 Goal Seek
• 6.1.1 Determining the Maximum Loan Amount
• 6.2 Finding Solutions with Solver
• 6.2.1 Advanced Solver Options
• 6.2.2 How Solver Works
• 6.2.3 Example: Material Cost Optimization
• 6.2.4 Steps for Solving the Packaging Problem with Solver
• 6.2.5 Evaluating Results and Reports
• 6.2.6 Additional Notes

• 7 Presenting Data Graphically
• 7.1 Graphical Analysis with Charts
• 7.1.1 Chart Elements
• 7.1.2 Nonrectangular Coordinate Systems
• 7.1.3 Data Series and Data Points
• 7.1.4 Additional Chart Elements
• 7.1.5 Chart Area and Plot Area
• 7.2 Chart Types in Excel
• 7.2.1 Charts with Rectangular Coordinate Systems
• 7.2.2 Discrete or Continuous Subdivisions
• 7.2.3 Charts with Nonnumeric X-Axes
• 7.2.4 Charts Without Rectangular Coordinate Systems
• 7.3 From the Table to the Chart
• 7.3.1 Creating a Column Chart: First Attempt
• 7.3.2 Recommended Charts
• 7.3.3 The Quick Chart
• 7.3.4 Overview of Chart Design Tools
• 7.3.5 Chart Filter
• 7.3.6 Titles, Legends, and Other Options
• 7.3.7 Setting the Chart Location
• 7.4 Linking the Table and Chart
• 7.4.1 Converting a Chart into an Image
• 7.5 Optimizing Charts
• 7.5.1 Formatting the Current Selection
• 7.5.2 Context Menus
• 7.5.3 Combining Chart Types
• 7.5.4 Improving Shapes
• 7.5.5 Resizing and Repositioning the Chart
• 7.6 Designing Charts
• 7.6.1 Changing the Chart Type
• 7.6.2 Chart Layouts and Chart Styles
• 7.6.3 Custom Templates
• 7.6.4 Arranging Data Series
• 7.6.5 Inserting and Formatting Labels
• 7.6.6 Axis Formatting
• 7.6.7 Improving Readability with Gridlines
• 7.6.8 Formatting Data Series and Data Points
• 7.6.9 Data Labels
• 7.6.10 Trend Calculation
• 7.6.11 Drop Lines, High/Low Lines, and Up/Down Bars
• 7.6.12 Columns Made from Images

• 8 Using Charts Effectively
• 8.1 Criteria for Choosing a Chart Type
• 8.2 Standard Charts
• 8.2.1 Column Charts
• 8.2.2 Bar Charts: Ideal for Long Category Labels
• 8.2.3 Line Charts: Ideal for Showing Trends
• 8.2.4 Pie Charts: For Showing Proportions
• 8.3 Value Differentiation with Area and Range Charts
• 8.3.1 Area Charts
• 8.3.2 Range Charts: Not Just for Stocks
• 8.4 Multiple Distributions and Cycles: Doughnut and Radar Charts
• 8.4.1 Doughnut Charts for Comparing Data Groups
• 8.4.2 Radar Charts for Cycles
• 8.5 Value Relationships: Scatter and Bubble Charts
• 8.5.1 A Rather Limited Selection of Subtypes
• 8.5.2 Editing a Chart Type
• 8.5.3 Bubble Charts as a Type of XY Chart
• 8.6 Combo Charts
• 8.6.1 Balancing Differences in Scale
• 8.6.2 Combination Types
• 8.7 3D Effects and True 3D Charts
• 8.7.1 Real and Pseudo 3D Charts
• 8.7.2 The Viewing Angle Is Key
• 8.7.3 Charts with Three Axes
• 8.7.4 Examples of 3D Chart Uses
• 8.7.5 The True 3D Subtypes
• 8.7.6 A 3D Chart with Equally Weighted Series and Categories
• 8.8 3D Surface Charts: Ideal for Continuous Data Visualization
• 8.8.1 Wireframe and Bird’s-Eye View
• 8.9 Additional Chart Types
• 8.9.1 Statistical Charts
• 8.9.2 Waterfall Charts
• 8.9.3 Sunbursts
• 8.9.4 Treemaps
• 8.9.5 Funnel Charts
• 8.9.6 Map Charts

• 9 Data Visualization with Sparklines
• 9.1 Features and Uses
• 9.2 Inserting Sparklines
• 9.3 Display Options
• 9.3.1 Highlighting Points
• 9.4 Editing Sparklines
• 9.4.1 Changing the Type
• 9.4.2 Assigning Colors
• 9.4.3 Axis Settings
• 9.4.4 Handling Empty Cells
• 9.4.5 Group or Individual Handling
• 9.4.6 Ungrouping Cells
• 9.4.7 Clearing Sparklines
• 9.4.8 Editing Data Sources

• 10 Refining Worksheets with Graphics
• 10.1 Overview of the Graphic Tools
• 10.2 Drawing Preset and Freeform Shapes
• 10.2.1 Drawing a Simple Shape
• 10.2.2 Freeform Lines
• 10.2.3 Creating a Flowchart
• 10.3 Fine-Tuning Graphic Objects
• 10.3.1 Adjusting Object Size, Proportion, and Rotation
• 10.3.2 The Format Shape Task Pane
• 10.3.3 Moving and Copying Objects
• 10.3.4 Object Attributes
• 10.3.5 Outline and Fill
• 10.3.6 Editing Bézier Curves
• 10.3.7 Techniques for Complex Drawings
• 10.3.8 Shape Effects
• 10.3.9 Freely Movable Text Boxes
• 10.3.10 Text Decoration for Tables
• 10.4 Creating Organizational Charts in a Hurry
• 10.5 Importing and Editing Graphics
• 10.5.1 Inserting Pictures into Cells
• 10.5.2 Inserting Images over Cells
• 10.5.3 Inserting Images by Using a Function
• 10.5.4 Editing Images Directly
• 10.5.5 Inserting Screenshots
• 10.6 Using Icons
• 10.6.1 Using the Icons Library
• 10.6.2 Inserting Custom Vector Graphics
• 10.7 3D Models and 3D Maps
• 10.7.1 A 3D Illustration from a File
• 10.7.2 Importing an Online 3D Illustration
• 10.8 Freehand Drawing
• 10.8.1 Highlights and Handwritten Comments
• 10.8.2 Creating Sketches
• 10.8.3 Inserting Mathematical Equations
• 10.9 Finding Images on the Web
• 10.9.1 Inserting a Photo into a Worksheet
• 10.9.2 Inserting Images from OneDrive
• 10.10 Table Snapshots

• 11 Preparing Documents for Publishing
• 11.1 Document Inspection
• 11.1.1 Document Inspector
• 11.1.2 Accessibility
• 11.1.3 Checking for Compatibility
• 11.1.4 Capacity Issues
• 11.1.5 Finalizing Documents
• 11.2 Encrypt Documents

• 12 Publishing Workbooks
• 12.1 Preparing Worksheets for Printing
• 12.1.1 Setting the Print Range
• 12.1.2 Page Layout
• 12.1.3 Choosing the Paper Size and Print Layout
• 12.1.4 Headers and Footers
• 12.1.5 Setting the Margins
• 12.1.6 Interactive Page Break Preview
• 12.1.7 Reviewing the Layout in Print Preview
• 12.2 Printer Selection and Printer Settings
• 12.2.1 Quick Print and Printing Options
• 12.2.2 Quick Print
• 12.2.3 Choosing Printing Options
• 12.3 Sending Worksheets by Email
• 12.3.1 Sending a Workbook as an Attachment
• 12.3.2 Sending a Workbook as a PDF
• 12.4 Creating a PDF or XPS Copy

• 13 Excel Data on the Web
• 13.1 Integration with the Internet and Intranet
• 13.2 From Excel to HTML and Back
• 13.2.1 Component Distribution
• 13.2.2 Web Archives
• 13.2.3 Web Options
• 13.3 Providing Data for the Web
• 13.3.1 Publishing Excel Data on the Web
• 13.4 Linking Documents with Hyperlinks
• 13.4.1 Jumping from a Cell
• 13.4.2 Automatic Link Creation
• 13.4.3 Linking to Documents
• 13.4.4 A Hyperdocument Composed of Workbooks
• 13.4.5 Downloads
• 13.4.6 Email Links
• 13.4.7 Hyperlinks Using the Table Function
• 13.4.8 Formatting Hyperlinks
• 13.4.9 Hyperlinking from a Graphic Object
• 13.4.10 Editing Hyperlinks
• 13.5 Processing XML Data
• 13.5.1 Importing XML Data
• 13.5.2 Linking the Data Source to the Table

• 14 Collaborating on Workbooks
• 14.1 Teamwork in Local Networks
• 14.1.1 Sharing a Workbook
• 14.1.2 Showing Changes
• 14.1.3 Reviewing Changes
• 14.1.4 Sharing on a Single Workstation
• 14.1.5 Limitations of Shared Workbooks
• 14.2 Collaborating via OneDrive
• 14.2.1 Sharing Workbooks
• 14.2.2 Collaborate

• 15 Table Functions
• 15.1 Functions Introduced in Excel 2013
• 15.2 Functions Introduced Since Excel 2016
• 15.3 Functions Introduced Since Excel 2021
• 15.4 Structure and Use of Functions
• 15.4.1 Function Arguments
• 15.4.2 Functions in Macros
• 15.5 Financial Mathematical Functions
• 15.5.1 Functions for Annuity Calculations
• 15.5.2 Loan Calculations
• 15.5.3 Depreciation Calculation
• 15.5.4 Example of a Depreciation Calculation
• 15.5.5 Functions for Securities Calculations
• 15.5.6 List of Financial Math Functions
• 15.6 Date and Time Functions
• 15.6.1 The Advantages of Using Serial Numbers
• 15.6.2 Calculating Periodic Date Series
• 15.6.3 Calculating Periodic Time Series
• 15.6.4 Calculating Working Hours
• 15.6.5 List of Date and Time Functions
• 15.7 Mathematical Functions
• 15.7.1 Sums and Conditional Sums
• 15.7.2 Rounding Values
• 15.7.3 Basic Mathematical Functions
• 15.7.4 Factorials and Combinations
• 15.7.5 Generating Random Numbers and Sequences
• 15.7.6 Trigonometric Functions
• 15.7.7 Hyperbolic Functions
• 15.7.8 List of Mathematical Functions
• 15.8 Statistical Functions
• 15.8.1 Overview of Statistical Functions
• 15.8.2 Samples and Populations
• 15.8.3 Random Variables and Probability
• 15.8.4 Sample Analysis
• 15.8.5 Statistical Tests
• 15.8.6 Distribution Functions
• 15.8.7 Calculating the Standard Deviation of Test Results
• 15.8.8 Conditional Maximum and Minimum Values
• 15.8.9 List of Statistical Functions
• 15.8.10 List of Compatible Functions
• 15.9 Lookup and Reference Functions
• 15.9.1 Filtering, Sorting, and Reducing Data
• 15.9.2 Querying Lookup Tables
• 15.9.3 Working with INDEX() Functions
• 15.9.4 Example of the CHOOSE() Function
• 15.9.5 Lookup Functions
• 15.9.6 Inserting Images as Cell Content
• 15.9.7 Additional Functions for Arrays
• 15.9.8 Aggregating Values from Tables
• 15.9.9 List of Lookup and Reference Functions
• 15.10 Database Functions
• 15.10.1 Analyzing a Table
• 15.10.2 List of Database Functions
• 15.11 Cube Functions
• 15.11.1 Special Features of Cube Functions
• 15.11.2 List of Cube Functions
• 15.12 Text Functions
• 15.12.1 Extracting Parts of Strings
• 15.12.2 Operations with Regular Expressions
• 15.12.3 Concatenating Strings
• 15.12.4 Sorting with Text Functions
• 15.12.5 Including Logical Values in Text
• 15.12.6 Combining Text with a Date
• 15.12.7 Detecting Languages and Translating
• 15.12.8 List of Text Functions
• 15.13 Logical Functions
• 15.13.1 TRUE or FALSE as Arguments
• 15.13.2 Checking Conditions
• 15.13.3 Checking Multiple Conditions
• 15.13.4 Automatically Adjusting Text
• 15.13.5 Conditional Text Display
• 15.13.6 Text Checking
• 15.13.7 Checks with Complex Conditions
• 15.13.8 Multiple Branching
• 15.13.9 Array Functions
• 15.13.10 LET() and LAMBDA()
• 15.13.11 List of Logical Functions
• 15.14 Information Functions
• 15.14.1 Example: Preventing Errors
• 15.14.2 List of Information Functions
• 15.15 Technical Functions
• 15.15.1 Converting Units of Measure
• 15.15.2 Bessel Functions
• 15.15.3 Conversions Between Number Systems
• 15.15.4 Calculations with Complex Numbers
• 15.15.5 List of Technical Functions
• 15.16 Web Functions
• 15.16.1 Web Queries
• 15.16.2 List of Web Functions

• 16 Organizing and Managing Information as Tables
• 16.1 Tables, Data Lists, and Data Tables
• 16.2 A Table for an Inventory List
• 16.3 Applications for Using Tables
• 16.4 Defining Table Structure
• 16.4.1 An Inventory Table for a Wine Warehouse
• 16.4.2 Data Types and Field Lengths
• 16.4.3 Rules for Choosing Column Names
• 16.5 Table Ranges
• 16.5.1 Converting Cell Ranges to Table Ranges
• 16.5.2 Formatting Tables
• 16.5.3 Table Options
• 16.5.4 Freezing Labels
• 16.5.5 Entering Data
• 16.5.6 Uniqueness and Duplicates
• 16.5.7 Expanding and Formatting Ranges
• 16.5.8 Expanding Tables
• 16.5.9 Inserting Totals Rows
• 16.5.10 Calculated Columns
• 16.5.11 Working with Structured References
• 16.6 Sorting Data
• 16.6.1 Sorting for Different Purposes
• 16.6.2 The Sort Key
• 16.6.3 The Sort Order
• 16.6.4 Custom Sorting
• 16.6.5 Sorting by Formatting
• 16.6.6 Sorting by Using a Custom Order
• 16.7 Group Data
• 16.7.1 Inserting Subtotals
• 16.7.2 Calculations for Subgroups

• 17 Data Queries and Data Extracts
• 17.1 What’s the Best Way to Formulate Queries?
• 17.2 Filtering Relevant Data
• 17.2.1 Location-Independent Filtering and Sorting
• 17.2.2 Text Filters
• 17.2.3 Number Filters
• 17.2.4 Date Filters
• 17.2.5 Color Filters
• 17.2.6 Sorting
• 17.2.7 Searching and Filtering
• 17.2.8 Filtering and Sorting by Cell Values
• 17.2.9 Combining Filters
• 17.2.10 Filtering with Slicers
• 17.3 Complex Queries with Advanced Filters
• 17.3.1 Table and Criteria Ranges
• 17.3.2 Data Extracts in the Output Range
• 17.3.3 Checking Inventory with the Advanced Filter
• 17.3.4 What Selection Criteria Are Available?
• 17.3.5 Searching with Calculated Criteria
• 17.4 Further Processing of Filtered Data
• 17.5 Calculations with Database Functions

• 18 PivotTables and Charts
• 18.1 Interactive Tables and Charts
• 18.2 Applications
• 18.3 Suitable Data
• 18.4 Data Analysis with PivotTables
• 18.4.1 From Source Data to PivotTable
• 18.4.2 PivotTable Layout
• 18.4.3 Recommended PivotTables
• 18.4.4 Adding Value Columns
• 18.4.5 Changing the PivotTable Layout
• 18.4.6 Options for the PivotTable Report
• 18.4.7 Adding Fields
• 18.4.8 Sorting in the PivotTable
• 18.4.9 Quick Data Extracts for Individual Values
• 18.4.10 Slicers
• 18.4.11 Changing Settings for Individual Fields
• 18.4.12 Showing and Hiding Subtotals and Grand Totals
• 18.4.13 Options for Layout and Printing
• 18.4.14 Changing the Calculation Method
• 18.4.15 Special Options for Report Filters
• 18.4.16 Showing and Hiding Detailed Information
• 18.4.17 Creating New Groups
• 18.4.18 Organizing Numerical Data
• 18.4.19 Inserting Timelines
• 18.4.20 Calculated Fields and Items in PivotTables
• 18.4.21 Formatting PivotTables
• 18.4.22 Quickly Selecting Data Groups
• 18.4.23 Conditional Formatting in PivotTables
• 18.4.24 Changing, Moving, and Deleting a Data Source
• 18.4.25 Data Types and Images in PivotTables
• 18.4.26 PivotTables from External Data
• 18.4.27 Default Format for PivotTables
• 18.5 Dynamic Charts from PivotTables
• 18.5.1 Slicers for a PivotChart
• 18.6 Data Models with Multiple Tables
• 18.6.1 Building a Model for a PivotTable
• 18.6.2 Defining Relationships
• 18.7 Power Pivot
• 18.7.1 Activating the Add-In
• 18.7.2 Data Preparation
• 18.7.3 Interactive Linking of Tables
• 18.7.4 Access via CUBE Functions
• 18.7.5 Creating Calculated Columns

• 19 Working with External Data
• 19.1 Importing Access Data
• 19.2 Querying XML Files
• 19.3 Importing a CSV File
• 19.4 Working with Linked Data Types
• 19.5 Importing Data from Pictures

• 20 Export and Import of Files
• 20.1 Data Formats and Filters
• 20.2 Supported File Formats
• 20.2.1 Output Formats
• 20.2.2 Import Formats
• 20.2.3 Working in Compatibility Mode
• 20.2.4 Text Formats
• 20.3 Importing Text Files
• 20.3.1 Importing an Address List
• 20.3.2 The Fixed Width Data Format
• 20.4 Distributing Text Across Columns

• 21 Exchanging Data with Other Applications
• 21.1 Exchanging Data via the Clipboard
• 21.1.1 Word Imports Data from Excel
• 21.1.2 Exporting Data via the Clipboard
• 21.1.3 How the Clipboard Works
• 21.1.4 Importing Text from Word
• 21.2 Linking Files Dynamically
• 21.2.1 Linking Between Documents
• 21.2.2 Update Control

• 22 Automating Routine Tasks with Macros
• 22.1 Recording Macros
• 22.1.1 Preparations
• 22.1.2 Recording a Schedule
• 22.1.3 What Does the Recording Look Like?
• 22.1.4 Saving the Workbook with the Macro
• 22.1.5 Running the Macro
• 22.2 Integrating Macros into the Workflow
• 22.2.1 Quick Start with Icons
• 22.2.2 Starting a Macro with Buttons or Graphic Objects
• 22.3 Flipping a Table with a Macro
• 22.3.1 Transposing with a Macro
• 22.4 Macros for Chart Formatting
• 22.5 Macros from Older Excel Versions

• 23 Visual Basic for Applications
• 23.1 Basics of VBA
• 23.1.1 The Excel Object Model
• 23.1.2 Events Control the Program Flow
• 23.1.3 Variables and Constants in VBA
• 23.1.4 Basic Units and Language Elements
• 23.2 The Development Environment
• 23.2.1 Project Explorer and Module Window
• 23.2.2 Editing Aids
• 23.2.3 Testing Programs
• 23.2.4 Printing Code and Forms
• 23.3 Input and Output
• 23.3.1 Simple Input Dialog
• 23.3.2 Message Dialogs
• 23.3.3 Selecting Ranges in Worksheets
• 23.3.4 Assigning Values to Cells
• 23.3.5 Entering Formulas
• 23.3.6 Reading Data from Worksheets
• 23.3.7 A Macro for a Sheet List
• 23.4 Designing Forms
• 23.4.1 Developing an Input Form
• 23.4.2 Inserting Input Controls
• 23.4.3 Adding Buttons
• 23.4.4 Entering the Procedures
• 23.4.5 Expandable Tool Collection
• 23.5 Table Functions in VBA
• 23.5.1 Calling Built-In Functions
• 23.5.2 Custom Table Functions
• 23.5.3 Retrieving the Sheet Name

• 24 Preview
• 24.1 Office Scripts: The Alternative to Macros
• 24.2 Copilot: The Helpful Assistant

• A Function Keys, Keys, and Keyboard Shortcuts
• A.1 Overview of General Key Functions
• A.2 Edit Keys and Keyboard Shortcuts
• A.3 Navigation and Selection Keys

• B The Author

• Index

Disclaimer

SAP, other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP SE (or an SAP affiliate company) in Germany and other countries. Our Company is not affiliated to SAP SE or any of its affiliated companies including but not limited to: Sybase, Business Objects, Hybris, Ariba and SuccessFactors. All other names, brands, logos, etc. are registered trade or service marks of their respective owners.