Excel 2016 Bible

Excel 2016 Bible

by John Walkenbach
Released October 2015
Publisher(s): Wiley
ISBN: 9781119067511

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

The complete guide to Excel 2016, from Mr. Spreadsheet himself

Whether you are just starting out or an Excel novice, the Excel 2016 Bible is your comprehensive, go-to guide for all your Excel 2016 needs. Whether you use Excel at work or at home, you will be guided through the powerful new features and capabilities by expert author and Excel Guru John Walkenbach to take full advantage of what the updated version offers. Learn to incorporate templates, implement formulas, create pivot tables, analyze data, and much more.

Navigate this powerful tool for business, home management, technical work, and much more with the only resource you need, Excel 2016 Bible.

  • Create functional spreadsheets that work
  • Master formulas, formatting, pivot tables, and more
  • Get acquainted with Excel 2016's new features and tools
  • Customize downloadable templates and worksheets

Whether you need a walkthrough tutorial or an easy-to-navigate desk reference, the Excel 2016 Bible has you covered with complete coverage and clear expert guidance.

Table of contents

  1. Introduction
    1. Is This Book for You?
    2. Software Versions
    3. Conventions Used in This Book
    4. How This Book Is Organized
    5. How to Use This Book
    6. What's on the Website
  2. Part I: Getting Started with Excel
    1. Chapter 1: Introducing Excel
      1. Identifying What Excel Is Good For
      2. Seeing What's New in Excel 2016
      3. Understanding Workbooks and Worksheets
      4. Moving Around a Worksheet
      5. Using the Ribbon
      6. Using Shortcut Menus
      7. Customizing Your Quick Access Toolbar
      8. Working with Dialog Boxes
      9. Using Task Panes
      10. Creating Your First Excel Workbook
    2. Chapter 2: Entering and Editing Worksheet Data
      1. Exploring Data Types
      2. Entering Text and Values into Your Worksheets
      3. Entering Dates and Times into Your Worksheets
      4. Modifying Cell Contents
      5. Applying Number Formatting
    3. Chapter 3: Essential Worksheet Operations
      1. Learning the Fundamentals of Excel Worksheets
      2. Controlling the Worksheet View
      3. Working with Rows and Columns
    4. Chapter 4: Working with Cells and Ranges
      1. Understanding Cells and Ranges
      2. Copying or Moving Ranges
      3. Using Names to Work with Ranges
      4. Adding Comments to Cells
    5. Chapter 5: Introducing Tables
      1. What Is a Table?
      2. Creating a Table
      3. Changing the Look of a Table
      4. Working with Tables
    6. Chapter 6: Worksheet Formatting
      1. Getting to Know the Formatting Tools
      2. Using Different Fonts to Format Your Worksheet
      3. Changing Text Alignment
      4. Using Colors and Shading
      5. Adding Borders and Lines
      6. Adding a Background Image to a Worksheet
      7. Using Named Styles for Easier Formatting
      8. Understanding Document Themes
    7. Chapter 7: Understanding Excel Files
      1. Creating a New Workbook
      2. Opening an Existing Workbook
      3. Saving a Workbook
      4. Using AutoRecover
      5. Password-Protecting a Workbook
      6. Organizing Your Files
      7. Other Workbook Info Options
      8. Closing Workbooks
      9. Safeguarding Your Work
      10. Excel File Compatibility
    8. Chapter 8: Using and Creating Templates
      1. Exploring Excel Templates
      2. Understanding Custom Excel Templates
    9. Chapter 9: Printing Your Work
      1. Basic Printing
      2. Changing Your Page View
      3. Adjusting Common Page Setup Settings
      4. Adding a Header or a Footer to Your Reports
      5. Other Print-Related Topics
  3. Part II: Working with Formulas and Functions
    1. Chapter 10: Introducing Formulas and Functions
      1. Understanding Formula Basics
      2. Entering Formulas into Your Worksheets
      3. Editing Formulas
      4. Using Cell References in Formulas
      5. Using Formulas in Tables
      6. Correcting Common Formula Errors
      7. Using Advanced Naming Techniques
      8. Working with Formulas
    2. Chapter 11: Creating Formulas That Manipulate Text
      1. A Few Words About Text
      2. Text Functions
      3. Advanced Text Formulas
    3. Chapter 12: Working with Dates and Times
      1. How Excel Handles Dates and Times
      2. Date-Related Worksheet Functions
      3. Time-Related Worksheet Functions
    4. Chapter 13: Creating Formulas That Count and Sum
      1. Counting and Summing Worksheet Cells
      2. Basic Counting Formulas
      3. Advanced Counting Formulas
      4. Summing Formulas
      5. Conditional Sums Using a Single Criterion
      6. Conditional Sums Using Multiple Criteria
    5. Chapter 14: Creating Formulas That Look Up Values
      1. Introducing Lookup Formulas
      2. Functions Relevant to Lookups
      3. Basic Lookup Formulas
      4. Specialized Lookup Formulas
    6. Chapter 15: Creating Formulas for Financial Applications
      1. The Time Value of Money
      2. Loan Calculations
      3. Investment Calculations
      4. Depreciation Calculations
      5. Financial Forecasting
    7. Chapter 16: Miscellaneous Calculations
      1. Unit Conversions
      2. Solving Right Triangles
      3. Area, Surface, Circumference, and Volume Calculations
      4. Rounding Numbers
    8. Chapter 17: Introducing Array Formulas
      1. Understanding Array Formulas
      2. Creating an Array Constant
      3. Understanding the Dimensions of an Array
      4. Naming Array Constants
      5. Working with Array Formulas
      6. Using Multicell Array Formulas
      7. Using Single-Cell Array Formulas
    9. Chapter 18: Performing Magic with Array Formulas
      1. Working with Single-Cell Array Formulas
      2. Working with Multicell Array Formulas
  4. Part III: Creating Charts and Graphics
    1. Chapter 19: Getting Started Making Charts
      1. What Is a Chart?
      2. Understanding How Excel Handles Charts
      3. Creating a Chart
      4. Hands On: Creating and Customizing a Chart
      5. Working with Charts
      6. Understanding Chart Types
      7. New Chart Types for Excel 2016
      8. Learning More
    2. Chapter 20: Learning Advanced Charting
      1. Selecting Chart Elements
      2. User Interface Choices for Modifying Chart Elements
      3. Modifying the Chart Area
      4. Modifying the Plot Area
      5. Working with Titles in a Chart
      6. Working with a Legend
      7. Working with Gridlines
      8. Modifying the Axes
      9. Working with Data Series
      10. Creating Chart Templates
      11. Learning Some Chart-Making Tricks
    3. Chapter 21: Visualizing Data Using Conditional Formatting
      1. About Conditional Formatting
      2. Specifying Conditional Formatting
      3. Conditional Formats That Use Graphics
      4. Creating Formula-Based Rules
      5. Working with Conditional Formats
    4. Chapter 22: Creating Sparkline Graphics
      1. Sparkline Types
      2. Creating Sparklines
      3. Customizing Sparklines
      4. Specifying a Date Axis
      5. Auto-Updating Sparklines
      6. Displaying a Sparkline for a Dynamic Range
    5. Chapter 23: Enhancing Your Work with Pictures and Drawings
      1. Using Shapes
      2. Using SmartArt
      3. Using WordArt
      4. Working with Other Graphics Types
      5. Using the Equation Editor
  5. Part IV: Using Advanced Excel Features
    1. Chapter 24: Customizing the Excel User Interface
      1. Customizing the Quick Access Toolbar
      2. Customizing the Ribbon
    2. Chapter 25: Using Custom Number Formats
      1. About Number Formatting
      2. Creating a Custom Number Format
      3. Custom Number Format Examples
    3. Chapter 26: Using Data Validation
      1. About Data Validation
      2. Specifying Validation Criteria
      3. Types of Validation Criteria You Can Apply
      4. Creating a Drop-Down List
      5. Using Formulas for Data Validation Rules
      6. Understanding Cell References
      7. Data Validation Formula Examples
    4. Chapter 27: Creating and Using Worksheet Outlines
      1. Introducing Worksheet Outlines
      2. Creating an Outline
      3. Working with Outlines
    5. Chapter 28: Linking and Consolidating Worksheets
      1. Linking Workbooks
      2. Creating External Reference Formulas
      3. Working with External Reference Formulas
      4. Avoiding Potential Problems with External Reference Formulas
      5. Consolidating Worksheets
    6. Chapter 29: Excel and the Internet
      1. Saving a Workbook on the Internet
      2. Saving Workbooks in HTML Format
      3. Opening an HTML File
      4. Working with Hyperlinks
      5. E-Mail Features
      6. Discovering Office Add-Ins
    7. Chapter 30: Protecting Your Work
      1. Types of Protection
      2. Protecting a Worksheet
      3. Protecting a Workbook
      4. VBA Project Protection
      5. Related Topics
    8. Chapter 31: Making Your Worksheets Error Free
      1. Finding and Correcting Formula Errors
      2. Using Excel Auditing Tools
      3. Searching and Replacing
      4. Using AutoCorrect
  6. Part V: Analyzing Data with Excel
    1. Chapter 32: Importing and Cleaning Data
      1. Importing Data
      2. Data Cleanup Techniques
      3. A Data Cleaning Checklist
      4. Exporting Data
    2. Chapter 33: Introducing Pivot Tables
      1. About Pivot Tables
      2. Creating a Pivot Table Automatically
      3. Creating a Pivot Table Manually
      4. More Pivot Table Examples
      5. Learning More
    3. Chapter 34: Analyzing Data with Pivot Tables
      1. Working with Nonnumeric Data
      2. Grouping Pivot Table Items
      3. Creating a Frequency Distribution
      4. Creating a Calculated Field or Calculated Item
      5. Filtering Pivot Tables with Slicers
      6. Filtering Pivot Tables with a Timeline
      7. Referencing Cells Within a Pivot Table
      8. Creating Pivot Charts
      9. Another Pivot Table Example
      10. Using the Data Model
      11. Learning More About Pivot Tables
    4. Chapter 35: Performing Spreadsheet What-If Analysis
      1. A What-If Example
      2. Types of What-If Analyses
    5. Chapter 36: Analyzing Data Using Goal Seeking and Solver
      1. What-If Analysis, in Reverse
      2. Single-Cell Goal Seeking
      3. Introducing Solver
      4. Solver Examples
    6. Chapter 37: Analyzing Data with the Analysis ToolPak
      1. The Analysis ToolPak: An Overview
      2. Installing the Analysis ToolPak Add-In
      3. Using the Analysis Tools
      4. Introducing the Analysis ToolPak Tools
    7. Chapter 38: Working with Get & Transform
      1. Get & Transform: An Overview
      2. Data Source for Get & Transform
      3. Example: A Simple Query
      4. How Your Actions Are Recorded
      5. Example: Returning Summarized Data
      6. Example: Transforming Data from a Web Query
      7. Example: Merging Two Web Queries
      8. Example: Getting a List of Files
      9. Example: Choosing a Random Sample
      10. Example: Unpivoting a Table
      11. Tips for Using Get & Transform
      12. Learning More
  7. Part VI: Programming Excel with VBA
    1. Chapter 39: Introducing Visual Basic for Applications
      1. Introducing VBA Macros
      2. Displaying the Developer Tab
      3. About Macro Security
      4. Saving Workbooks That Contain Macros
      5. Two Types of VBA Macros
      6. Creating VBA Macros
      7. Learning More
    2. Chapter 40: Creating Custom Worksheet Functions
      1. Overview of VBA Functions
      2. An Introductory Example
      3. About Function Procedures
      4. Executing Function Procedures
      5. Function Procedure Arguments
      6. Debugging Custom Functions
      7. Inserting Custom Functions
      8. Learning More
    3. Chapter 41: Creating UserForms
      1. Why Create UserForms?
      2. UserForm Alternatives
      3. Creating UserForms: An Overview
      4. A UserForm Example
      5. Another UserForm Example
      6. More on Creating UserForms
      7. Learning More
    4. Chapter 42: Using UserForm Controls in a Worksheet
      1. Why Use Controls on a Worksheet?
      2. Using Controls
      3. Reviewing the Available ActiveX Controls
    5. Chapter 43: Working with Excel Events
      1. Understanding Events
      2. Entering Event-Handler VBA Code
      3. Using Workbook-Level Events
      4. Working with Worksheet Events
      5. Using Nonobject Events
    6. Chapter 44: VBA Examples
      1. Working with Ranges
      2. Working with Workbooks
      3. Working with Charts
      4. VBA Speed Tips
    7. Chapter 45: Creating Custom Excel Add-Ins
      1. What Is an Add-In?
      2. Working with Add-Ins
      3. Why Create Add-Ins?
      4. Creating Add-Ins
      5. An Add-In Example
  8. Part VII: Appendixes
    1. Appendix A: Worksheet Function Reference
    2. Appendix B: Excel Shortcut Keys
    3. End User License Agreement

Product information

  • Title: Excel 2016 Bible
  • Author(s): John Walkenbach
  • Release date: October 2015
  • Publisher(s): Wiley
  • ISBN: 9781119067511