You are previewing Excel 2016 All-in-One For Dummies.
O'Reilly logo
Excel 2016 All-in-One For Dummies

Book Description

Your one-stop guide to all things Excel 2016

Excel 2016 All-in-One For Dummies, the most comprehensive Excel reference on the market, is completely updated to reflect Microsoft's changes in the popular spreadsheet tool. It offers you everything you need to grasp basic Excel functions, such as creating and editing worksheets, setting up formulas, importing data, performing statistical functions, editing macros with Visual Basic—and beyond. In no time, your Excel skills will go from 'meh' to excellent.

Written by expert Greg Harvey, who has sold more than 4.5 million copies of his previous books combined and has taught and trained extensively in Microsoft Excel, this all-encompassing guide offers everything you need to get started with Excel. From generating pivot tables and performing financial functions to performing error trapping and building and running macros—and everything in between—this hands-on, friendly guide makes working with Excel easier than ever before.

  • Serves as the ideal reference for solving common questions and Excel pain points quickly and easily
  • Helps to increase productivity and efficiency when working in Excel
  • Fully updated for the new version of Excel
  • Covers basic and more advanced Excel topics

If working in Excel occasionally makes you want to scream, this will be the dog-eared, dust-free reference you'll turn to again and again.

Table of Contents

    1. Cover
    2. Introduction
      1. About This Book
      2. Foolish Assumptions
      3. How This Book Is Organized
      4. Conventions Used in This Book
      5. Icons Used in This Book
      6. Where to Go from Here
    3. Book I: Excel Basics
      1. Chapter 1: The Excel 2016 User Experience
        1. Excel 2016’s Sleek Look and Feel
        2. Excel’s Start Screen
        3. Excel’s Ribbon User Interface
        4. Getting Help
        5. Launching and Quitting Excel
      2. Chapter 2: Customizing Excel 2016
        1. Tailoring the Quick Access Toolbar to Your Tastes
        2. Exercising Your Options
        3. Using Office Add-ins
        4. Using Excel’s Own Add-Ins
    4. Book II: Worksheet Design
      1. Chapter 1: Building Worksheets
        1. Designer Spreadsheets
        2. It Takes All Kinds (Of Cell Entries)
        3. Data Entry 101
        4. Saving the Data
        5. Document Recovery to the Rescue
      2. Chapter 2: Formatting Worksheets
        1. Making Cell Selections
        2. Adjusting Columns and Rows
        3. Formatting Tables from the Ribbon
        4. Formatting Tables with the Quick Analysis Tool
        5. Formatting Cells from the Ribbon
        6. Formatting Cell Ranges with the Mini-Toolbar
        7. Using the Format Cells Dialog Box
        8. Hiring Out the Format Painter
        9. Using Cell Styles
        10. Conditional Formatting
      3. Chapter 3: Editing and Proofing Worksheets
        1. Opening a Workbook
        2. Cell Editing 101
        3. A Spreadsheet with a View
        4. Copying and Moving Stuff Around
        5. Find and Replace This Disgrace!
        6. Spell Checking Heaven
        7. Looking Up and Translating Stuff
        8. Marking Invalid Data
        9. Eliminating Errors with Text to Speech
      4. Chapter 4: Managing Worksheets
        1. Reorganizing the Worksheet
        2. Reorganizing the Workbook
        3. Working with Multiple Workbooks
        4. Consolidating Worksheets
      5. Chapter 5: Printing Worksheets
        1. Printing from the Excel 2016 Backstage View
        2. Quick Printing the Worksheet
        3. Working with the Page Setup Options
        4. Headers and Footers
        5. Solving Page Break Problems
        6. Printing the Formulas in a Report
    5. Book III: Formulas and Functions
      1. Chapter 1: Building Basic Formulas
        1. Formulas 101
        2. Copying Formulas
        3. Adding Array Formulas
        4. Range Names in Formulas
        5. Adding Linking Formulas
        6. Controlling Formula Recalculation
        7. Circular References
      2. Chapter 2: Logical Functions and Error Trapping
        1. Understanding Error Values
        2. Using Logical Functions
        3. Error-Trapping Formulas
        4. Whiting-Out Errors with Conditional Formatting
        5. Formula Auditing
        6. Removing Errors from the Printout
      3. Chapter 3: Date and Time Formulas
        1. Understanding Dates and Times
        2. Using Date Functions
        3. Using Time Functions
      4. Chapter 4: Financial Formulas
        1. Financial Functions 101
        2. The PV, NPV, and FV Functions
        3. The PMT Function
        4. Depreciation Functions
        5. Analysis ToolPak Financial Functions
      5. Chapter 5: Math and Statistical Formulas
        1. Math & Trig Functions
        2. Statistical Functions
      6. Chapter 6: Lookup, Information, and Text Formulas
        1. Lookup and Reference
        2. Information, Please …
        3. Much Ado about Text
    6. Book IV: Worksheet Collaboration and Review
      1. Chapter 1: Protecting Workbooks and Worksheet Data
        1. Password-Protecting the File
        2. Protecting the Worksheet
      2. Chapter 2: Using Hyperlinks
        1. Hyperlinks 101
        2. Using the HYPERLINK Function
      3. Chapter 3: Sending Workbooks Out for Review
        1. Preparing a Workbook for Distribution
        2. Workbook Sharing 101
        3. Workbooks on Review
      4. Chapter 4: Sharing Workbooks and Worksheet Data
        1. Sharing Your Workbooks Online
        2. Excel 2016 Data Sharing Basics
        3. Exporting Workbooks to Other Usable File Formats
    7. Book V: Charts and Graphics
      1. Chapter 1: Charting Worksheet Data
        1. Worksheet Charting 101
        2. Adding Sparkline Graphics to a Worksheet
        3. Printing Charts
      2. Chapter 2: Adding Graphic Objects
        1. Graphic Objects 101
        2. Inserting Different Types of Graphics
        3. Drawing Graphics
        4. Adding Screenshots of the Windows Desktop
        5. Using Themes
    8. Book VI: Data Management
      1. Chapter 1: Building and Maintaining Data Lists
        1. Data List Basics
        2. Sorting Data
        3. Subtotaling Data
      2. Chapter 2: Filtering and Querying a Data List
        1. Data List Filtering 101
        2. Filtering Data
        3. Using the Database Functions
        4. External Data Query
    9. Book VII: Data Analysis
      1. Chapter 1: Performing What-If Scenarios
        1. Using Data Tables
        2. Exploring Different Scenarios
        3. Hide and Goal Seeking
        4. Using the Solver
      2. Chapter 2: Performing Large-Scale Data Analysis
        1. Creating Pivot Tables
        2. Formatting a Pivot Table
        3. Sorting and Filtering the Pivot Table Data
        4. Modifying the Pivot Table
        5. Creating Pivot Charts
        6. Using the Power Pivot and Power View Add-Ins
        7. Using the Power Map feature
        8. Creating Forecast Worksheets
    10. Book VIII: Macros and VBA
      1. Chapter 1: Recording and Running Macros
        1. Macro Basics
        2. Assigning Macros to the Ribbon and the Quick Access Toolbar
        3. Macro Security
      2. Chapter 2: VBA Programming
        1. Using the Visual Basic Editor
        2. Creating Custom Excel Functions
    11. About the Author
    12. Cheat Sheet
    13. Connect with Dummies
    14. End User License Agreement