Home Page Icon
Home Page
Table of Contents for
Title Page
Close
Title Page
by John Walkenbach
Excel 2016 Bible
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
Identifying What Excel Is Good For
Seeing What's New in Excel 2016
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: Essential Worksheet Operations
Learning the Fundamentals of Excel Worksheets
Controlling the Worksheet View
Working with Rows and Columns
Chapter 4: Working with Cells and Ranges
Understanding Cells and Ranges
Copying or Moving Ranges
Using Names to Work with Ranges
Adding Comments to Cells
Chapter 5: Introducing Tables
What Is a Table?
Creating a Table
Changing the Look of a Table
Working with Tables
Chapter 6: Worksheet Formatting
Getting to Know the Formatting Tools
Using Different Fonts to Format Your Worksheet
Changing Text Alignment
Using Colors and Shading
Adding Borders and Lines
Adding a Background Image to a Worksheet
Using Named Styles for Easier Formatting
Understanding Document Themes
Chapter 7: Understanding Excel Files
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
Excel File Compatibility
Chapter 8: Using and Creating Templates
Exploring Excel Templates
Understanding Custom Excel Templates
Chapter 9: Printing Your Work
Basic Printing
Changing Your Page View
Adjusting Common Page Setup Settings
Adding a Header or a Footer to Your Reports
Other Print-Related Topics
Part II: Working with Formulas and Functions
Chapter 10: 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 11: Creating Formulas That Manipulate Text
A Few Words About Text
Text Functions
Advanced Text Formulas
Chapter 12: Working with Dates and Times
How Excel Handles Dates and Times
Date-Related Worksheet Functions
Time-Related Worksheet Functions
Chapter 13: Creating Formulas That Count and Sum
Counting and Summing Worksheet Cells
Basic Counting Formulas
Advanced Counting Formulas
Summing Formulas
Conditional Sums Using a Single Criterion
Conditional Sums Using Multiple Criteria
Chapter 14: Creating Formulas That Look Up Values
Introducing Lookup Formulas
Functions Relevant to Lookups
Basic Lookup Formulas
Specialized Lookup Formulas
Chapter 15: Creating Formulas for Financial Applications
The Time Value of Money
Loan Calculations
Investment Calculations
Depreciation Calculations
Financial Forecasting
Chapter 16: Miscellaneous Calculations
Unit Conversions
Solving Right Triangles
Area, Surface, Circumference, and Volume Calculations
Rounding Numbers
Chapter 17: Introducing 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 18: Performing Magic with Array Formulas
Working with Single-Cell Array Formulas
Working with Multicell Array Formulas
Part III: Creating Charts and Graphics
Chapter 19: Getting Started Making Charts
What Is a Chart?
Understanding How Excel Handles Charts
Creating a Chart
Hands On: Creating and Customizing a Chart
Working with Charts
Understanding Chart Types
New Chart Types for Excel 2016
Learning More
Chapter 20: Learning Advanced Charting
Selecting Chart Elements
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
Learning Some Chart-Making Tricks
Chapter 21: Visualizing Data Using Conditional Formatting
About Conditional Formatting
Specifying Conditional Formatting
Conditional Formats That Use Graphics
Creating Formula-Based Rules
Working with Conditional Formats
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: Enhancing Your Work with Pictures and Drawings
Using Shapes
Using SmartArt
Using WordArt
Working with Other Graphics Types
Using the Equation Editor
Part IV: Using Advanced Excel Features
Chapter 24: Customizing the Excel User Interface
Customizing the Quick Access Toolbar
Customizing the Ribbon
Chapter 25: Using Custom Number Formats
About Number Formatting
Creating a Custom Number Format
Custom Number Format Examples
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
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: Excel and the Internet
Saving a Workbook on the Internet
Saving Workbooks in HTML Format
Opening an HTML File
Working with Hyperlinks
E-Mail Features
Discovering Office Add-Ins
Chapter 30: Protecting Your Work
Types of Protection
Protecting a Worksheet
Protecting a Workbook
VBA Project Protection
Related Topics
Chapter 31: Making Your Worksheets Error Free
Finding and Correcting Formula Errors
Using Excel Auditing Tools
Searching and Replacing
Using AutoCorrect
Part V: Analyzing Data with Excel
Chapter 32: Importing and Cleaning Data
Importing Data
Data Cleanup Techniques
A Data Cleaning Checklist
Exporting Data
Chapter 33: Introducing Pivot Tables
About Pivot Tables
Creating a Pivot Table Automatically
Creating a Pivot Table Manually
More Pivot Table Examples
Learning More
Chapter 34: Analyzing Data with Pivot Tables
Working with Nonnumeric Data
Grouping Pivot Table Items
Creating a Frequency Distribution
Creating a Calculated Field or Calculated Item
Filtering Pivot Tables with Slicers
Filtering Pivot Tables with a Timeline
Referencing Cells Within a Pivot Table
Creating Pivot Charts
Another Pivot Table Example
Using the Data Model
Learning More About Pivot Tables
Chapter 35: Performing Spreadsheet What-If Analysis
A What-If Example
Types of What-If Analyses
Chapter 36: Analyzing Data Using Goal Seeking and Solver
What-If Analysis, in Reverse
Single-Cell Goal Seeking
Introducing Solver
Solver Examples
Chapter 37: 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 38: Working with Get & Transform
Get & Transform: An Overview
Data Source for Get & Transform
Example: A Simple Query
How Your Actions Are Recorded
Example: Returning Summarized Data
Example: Transforming Data from a Web Query
Example: Merging Two Web Queries
Example: Getting a List of Files
Example: Choosing a Random Sample
Example: Unpivoting a Table
Tips for Using Get & Transform
Learning More
Part VI: Programming Excel with VBA
Chapter 39: Introducing Visual Basic for Applications
Introducing VBA Macros
Displaying the Developer Tab
About Macro Security
Saving Workbooks That Contain Macros
Two Types of VBA Macros
Creating VBA Macros
Learning More
Chapter 40: Creating Custom Worksheet Functions
Overview of VBA Functions
An Introductory Example
About Function Procedures
Executing Function Procedures
Function Procedure Arguments
Debugging Custom Functions
Inserting Custom Functions
Learning More
Chapter 41: Creating UserForms
Why Create UserForms?
UserForm Alternatives
Creating UserForms: An Overview
A UserForm Example
Another UserForm Example
More on Creating UserForms
Learning More
Chapter 42: Using UserForm Controls in a Worksheet
Why Use Controls on a Worksheet?
Using Controls
Reviewing the Available ActiveX Controls
Chapter 43: Working with Excel Events
Understanding Events
Entering Event-Handler VBA Code
Using Workbook-Level Events
Working with Worksheet Events
Using Nonobject Events
Chapter 44: VBA Examples
Working with Ranges
Working with Workbooks
Working with Charts
VBA Speed Tips
Chapter 45: Creating Custom Excel Add-Ins
What Is an Add-In?
Working with Add-Ins
Why Create Add-Ins?
Creating Add-Ins
An Add-In Example
Part VII: Appendixes
Appendix A: Worksheet Function Reference
Appendix B: Excel Shortcut Keys
End User License Agreement
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Appendix B: Excel Shortcut Keys
Next
Next Chapter
Copyright
Microsoft
®
Excel
®
2016
BIBLE
John Walkenbach
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset