Efnisyfirlit

• Cover
• Introduction
• Is This Book for You?
• Software Versions
• Conventions Used in This Book
• How This Book Is Organized
• How to Use This Book
• What’s on the Website
• Part I: Getting Started with Excel
• CHAPTER 1: Introducing Excel
• Understanding What Excel Is Used For
• Looking at What’s New in Excel 2019
• Understanding Workbooks and Worksheets
• Moving around a Worksheet
• Using the Ribbon
• Using Shortcut Menus
• Customizing Your Quick Access Toolbar
• Working with Dialog Boxes
• Using Task Panes
• Creating Your First Excel Workbook
• CHAPTER 2: Entering and Editing Worksheet Data
• Exploring Data Types
• Entering Text and Values into Your Worksheets
• Entering Dates and Times into Your Worksheets
• Modifying Cell Contents
• Applying Number Formatting
• CHAPTER 3: Performing Basic Worksheet Operations
• Learning the Fundamentals of Excel Worksheets
• Controlling the Worksheet View
• Working with Rows and Columns
• CHAPTER 4: Working with Excel Ranges and Tables
• Understanding Cells and Ranges
• Copying or Moving Ranges
• Using Names to Work with Ranges
• Adding Comments to Cells
• Working with Tables
• CHAPTER 5: Formatting Worksheets
• Getting to Know the Formatting Tools
• Formatting Your Worksheet
• Using Conditional Formatting
• Using Named Styles for Easier Formatting
• Understanding Document Themes
• CHAPTER 6: Understanding Excel Files and Templates
• Creating a New Workbook
• Opening an Existing Workbook
• Saving a Workbook
• Using AutoRecover
• Password-Protecting a Workbook
• Organizing Your Files
• Other Workbook Info Options
• Closing Workbooks
• Safeguarding Your Work
• Working with Templates
• CHAPTER 7: Printing Your Work
• Doing Basic Printing
• Changing Your Page View
• Adjusting Common Page Setup Settings
• Adding a Header or a Footer to Your Reports
• Exploring Other Print-Related Topics
• CHAPTER 8: Customizing the Excel User Interface
• Customizing the Quick Access Toolbar
• Customizing the Ribbon
• Part II: Working with Formulas and Functions
• CHAPTER 9: Introducing Formulas and Functions
• Understanding Formula Basics
• Entering Formulas into Your Worksheets
• Editing Formulas
• Using Cell References in Formulas
• Using Formulas in Tables
• Correcting Common Formula Errors
• Using Advanced Naming Techniques
• Working with Formulas
• CHAPTER 10: Using Formulas for Common Mathematical Operations
• Calculating Percentages
• Rounding Numbers
• Counting Values in a Range
• Using Excel’s Conversion Functions
• CHAPTER 11: Using Formulas to Manipulate Text
• Working with Text
• Using Text Functions
• CHAPTER 12: Using Formulas with Dates and Times
• Understanding How Excel Handles Dates and Times
• Using Excel’s Date and Time Functions
• CHAPTER 13: Using Formulas for Conditional Analysis
• Understanding Conditional Analysis
• Performing Conditional Calculations
• CHAPTER 14: Using Formulas for Matching and Lookups
• Introducing Lookup Formulas
• Leveraging Excel’s Lookup Functions
• CHAPTER 15: Using Formulas for Financial Analysis
• Performing Common Business Calculations
• Leveraging Excel’s Financial Functions
• CHAPTER 16: Using Formulas for Statistical Analysis
• Working with Weighted Averages
• Smoothing Data with Moving Averages
• Using Functions to Create Descriptive Statistics
• Bucketing Data into Percentiles
• Identifying Statistical Outliers with an Interquartile Range
• Creating a Frequency Distribution
• CHAPTER 17: Using Formulas with Tables and Conditional Formatting
• Highlighting Cells That Meet Certain Criteria
• Highlighting Values That Exist in List1 but Not List2
• Highlighting Values That Exist in List1 and List2
• Highlighting Based on Dates
• CHAPTER 18: Understanding and Using Array Formulas
• Understanding Array Formulas
• Creating an Array Constant
• Understanding the Dimensions of an Array
• Naming Array Constants
• Working with Array Formulas
• Using Multicell Array Formulas
• Using Single-Cell Array Formulas
• CHAPTER 19: Making Your Formulas Error-Free
• Finding and Correcting Formula Errors
• Using Excel Auditing Tools
• Searching and Replacing
• Using AutoCorrect
• Part III: Creating Charts and Other Visualizations
• CHAPTER 20: Getting Started with Excel Charts
• What Is a Chart?
• Basic Steps for Creating a Chart
• Modifying and Customizing Charts
• Understanding Chart Types
• New Chart Types for Excel
• CHAPTER 21: Using Advanced Charting Techniques
• Selecting Chart Elements
• Exploring the User Interface Choices for Modifying Chart Elements
• Modifying the Chart Area
• Modifying the Plot Area
• Working with Titles in a Chart
• Working with a Legend
• Working with Gridlines
• Modifying the Axes
• Working with Data Series
• Creating Chart Templates
• CHAPTER 22: Creating Sparkline Graphics
• Sparkline Types
• Creating Sparklines
• Customizing Sparklines
• Specifying a Date Axis
• Auto-Updating Sparklines
• Displaying a Sparkline for a Dynamic Range
• CHAPTER 23: Visualizing with Custom Number Formats and Shapes
• Visualizing with Number Formatting
• Using Shapes and Icons as Visual Elements
• Using SmartArt and WordArt
• Working with Other Graphics Types
• Using the Equation Editor
• CHAPTER 24: Implementing Excel Dashboarding Best Practices
• Preparing for a Dashboard Project
• Implementing Dashboard Modeling Best Practices
• Implementing Dashboard Design Best Practices
• Part IV: Managing and Analyzing Data
• CHAPTER 25: Importing and Cleaning Data
• Importing Data
• Cleaning Up Data
• Exporting Data
• CHAPTER 26: Using Data Validation
• About Data Validation
• Specifying Validation Criteria
• Types of Validation Criteria You Can Apply
• Creating a Drop-Down List
• Using Formulas for Data Validation Rules
• Understanding Cell References
• Data Validation Formula Examples
• Using Data Validation without Restricting Entry
• CHAPTER 27: Creating and Using Worksheet Outlines
• Introducing Worksheet Outlines
• Creating an Outline
• Working with Outlines
• CHAPTER 28: Linking and Consolidating Worksheets
• Linking Workbooks
• Creating External Reference Formulas
• Working with External Reference Formulas
• Avoiding Potential Problems with External Reference Formulas
• Consolidating Worksheets
• CHAPTER 29: Introducing PivotTables
• About PivotTables
• Creating a PivotTable Automatically
• Creating a PivotTable Manually
• Seeing More PivotTable Examples
• Learning More
• CHAPTER 30: Analyzing Data with PivotTables
• Working with Non-numeric Data
• Grouping PivotTable Items
• Using a PivotTable to Create a Frequency Distribution
• Creating a Calculated Field or Calculated Item
• Filtering PivotTables with Slicers
• Filtering PivotTables with a Timeline
• Referencing Cells within a PivotTable
• Creating PivotCharts
• Using the Data Model
• CHAPTER 31: Performing Spreadsheet What-If Analysis
• Looking at a What-If Example
• Exploring Types of What-If Analyses
• CHAPTER 32: Analyzing Data Using Goal Seeking and Solver
• Exploring What-If Analysis, in Reverse
• Using Single-Cell Goal Seeking
• Introducing Solver
• Seeing Some Solver Examples
• CHAPTER 33: Analyzing Data with the Analysis ToolPak
• The Analysis ToolPak: An Overview
• Installing the Analysis ToolPak Add-In
• Using the Analysis Tools
• Introducing the Analysis ToolPak Tools
• CHAPTER 34: Protecting Your Work
• Types of Protection
• Protecting a Worksheet
• Protecting a Workbook
• Protecting a VBA Project
• Related Topics
• Part V: Understanding Power Pivot and Power Query
• CHAPTER 35: Introducing Power Pivot
• Understanding the Power Pivot Internal Data Model
• Loading Data from Other Data Sources
• CHAPTER 36: Working Directly with the Internal Data Model
• Directly Feeding the Internal Data Model
• Managing Relationships in the Internal Data Model
• Removing a Table from the Internal Data Model
• CHAPTER 37: Adding Formulas to Power Pivot
• Enhancing Power Pivot Data with Calculated Columns
• Utilizing DAX to Create Calculated Columns
• Understanding Calculated Measures
• Using Cube Functions to Free Your Data
• CHAPTER 38: Introducing Power Query
• Understanding Power Query Basics
• Getting Data from External Sources
• Getting Data from Other Data Systems
• Managing Data Source Settings
• CHAPTER 39: Transforming Data with Power Query
• Performing Common Transformation Tasks
• Creating Custom Columns
• Grouping and Aggregating Data
• CHAPTER 40: Making Queries Work Together
• Reusing Query Steps
• Understanding the Append Feature
• Understanding the Merge Feature
• CHAPTER 41: Enhancing Power Query Productivity
• Implementing Some Power Query Productivity Tips
• Avoiding Power Query Performance Issues
• Part VI: Automating Excel
• CHAPTER 42: Introducing Visual Basic for Applications
• Introducing VBA Macros
• Displaying the Developer Tab
• Learning about Macro Security
• Saving Workbooks That Contain Macros
• Looking at the Two Types of VBA Macros
• Creating VBA Macros
• Learning More
• CHAPTER 43: Creating Custom Worksheet Functions
• Introducing VBA Functions
• Seeing a Simple Example
• Learning about Function Procedures
• Executing Function Procedures
• Using Function Procedure Arguments
• Debugging Custom Functions
• Inserting Custom Functions
• Learning More
• CHAPTER 44: Creating UserForms
• Understanding Why to Create UserForms
• Exploring UserForm Alternatives
• Creating UserForms: An Overview
• Looking at a UserForm Example
• Looking at Another UserForm Example
• Enhancing UserForms
• Learning More
• CHAPTER 45: Using UserForm Controls in a Worksheet
• Understanding Why to Use Controls on a Worksheet
• Using Controls
• Reviewing the Available ActiveX Controls
• CHAPTER 46: Working with Excel Events
• Understanding Events
• Entering Event-Handler VBA Code
• Using Workbook-Level Events
• Working with Worksheet Events
• Using Special Application Events
• CHAPTER 47: Seeing Some VBA Examples
• Working with Ranges
• Working with Workbooks
• Working with Charts
• VBA Speed Tips
• CHAPTER 48: Creating Custom Excel Add-Ins
• Understanding Add-Ins
• Working with Add-Ins
• Understanding Why to Create Add-Ins
• Creating Add-Ins
• Looking at an Add-In Example
• Index
• End User License Agreement