Excel 2019 All-in-One For Dummies

Excel 2019 All-in-One For Dummies

by Greg Harvey
Released November 2018
Publisher(s): For Dummies
ISBN: 9781119517948

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

Make Excel work for you 

Excel 2019 All-In-One For Dummies offers eight books in one!! It is completely updated to reflect the major changes Microsoft is making to Office with the 2019 release. From basic Excel functions, such as creating and editing worksheets, to sharing and reviewing worksheets, to editing macros with Visual Basic, it provides you with a broad scope of the most common Excel applications and functions—including formatting worksheets, setting up formulas, protecting worksheets, importing data, charting data, and performing statistical functions.
   
The book covers importing data, building and editing worksheets, creating formulas, generating pivot tables, and performing financial functions, what-if scenarios, database functions, and Web queries. More advanced topics include worksheet sharing and auditing, performing error trapping, building and running macros, charting data, and using Excel in conjunction with Microsoft Power BI (Business Intelligence) to analyze, model, and visualize vast quantities of data from a variety of local and online sources.  

  • Get familiar with Worksheet design
  • Find out how to work with charts and graphics
  • Use Excel for data management, analysis, modeling, and visualization
  • Make sense of macros and VBA

If you’re a new or inexperienced user looking to spend more time on your projects than trying to figure out how to make Excel work for you, this all-encompassing book makes it easy!

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. Beyond the Book
    7. Where to Go from Here
  3. Book 1: Excel Basics
    1. Chapter 1: The Excel 2019 User Experience
      1. Excel 2019’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 2019
      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 2: 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 2019 Backstage View
      2. Quick Printing the Worksheet
      3. Working with the Page Setup Options
      4. Using the Print Options on the Sheet tab of the Page Setup dialog box
      5. Headers and Footers
      6. Solving Page Break Problems
      7. Printing the Formulas in a Report
  5. Book 3: 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 4: 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: Preparing a Workbook for Distribution
      1. Getting Your Workbook Ready for Review
      2. Annotating Workbooks
    4. Chapter 4: Sharing Workbooks and Worksheet Data
      1. Sharing Your Workbooks Online
      2. Excel 2019 Data Sharing Basics
      3. Exporting Workbooks to Other Usable File Formats
  7. Book 5: Charts and Graphics
    1. Chapter 1: Charting Worksheet Data
      1. Worksheet Charting 101
      2. Adding Sparkline Graphics to a Worksheet
      3. Adding Infographics to a Worksheet
      4. 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 10 Desktop
      5. Using Themes
  8. Book 6: 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 7: 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 Add-in
      7. Using the 3D Map feature
      8. Creating Forecast Worksheets
  10. Book 8: 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. Index
  12. About the Author
  13. Connect with Dummies
  14. End User License Agreement

Product information

  • Title: Excel 2019 All-in-One For Dummies
  • Author(s): Greg Harvey
  • Release date: November 2018
  • Publisher(s): For Dummies
  • ISBN: 9781119517948