Learning objectives by chapter
Scroll down for Table of Contents

Chapter 1

Getting started: a bit of everything

  • 1. Understand the fundamental components of Excel: cells, ranges, worksheets, workbooks and tables.
  • 2. Understand the very basics of how Excel works as well as some useful tricks and shortcuts.
  • 3. Introduce the use of simple formulas and basic charts.
  • 4. Create formatted tables and understand the benefits of doing so.
  • 5. Get acquainted with what we call Best Practices, which will be applicable to all your future work in Excel.

Chapter 2

Text, date & time and logical formulas

  • 1. Understand the use of names for existing cells.
  • 2. Understand the basics of formula and function operation.
  • 3. Apply typical text formulas to solve common issues.
  • 4. Understand how Excel codes date and time from serial numbers and apply typical date & time formulas.
  • 5. Create simple IF statements (an introduction).

Chapter 3

Math formulas

  • 1. Reinforce the use of names.
  • 2. Understand the use of formulas that count and sum.
  • 3. Understand how to write an algebraic formula in Excel.
  • 4. Use hyperlinks and images to make your files more professional.
  • 5. Reinforce the use of IF statements for formula consistency.

Chapter 4

Lookup & reference formulas

  • 1. Apply lookup & reference functions and formulas to solve common exercises.
  • 2. Understand the concept of relational database and how to properly share data without retyping or copying.
  • 3. Reinforce the use of IF statements for formula consistency.

Chapter 5

Creating beautiful charts

  • 1. Become familiar with the different chart types offered by Excel.
  • 2. Understand the importance of producing beautiful and functional charts.
  • 3. Identify the best type of chart for a specific purpose.
  • 4. Maximize the aesthetic and professional appearance of charts.
  • 5. Minimize the time it takes to create and modify charts.

Chapter 6

Pivot tables and charts

  • 1. Understand the database structure required to produce pivot tables and pivot charts.
  • 2. Create and edit pivot tables and charts.
  • 3. Understand the flexibility of pivot tables and charts.
  • 4. Understand how pivot tables and charts are dynamically linked to the source data.
  • 5. Apply different pivot table formats.

Chapter 7

Error-proof files & other features

  • 1. Learn the three techniques to produce secure and error-proof spreadsheet files: data validation, cell protection and password protection.
  • 2. Understand and perform What-if Analysis with Scenario Manager.
  • 3. Understand the use of Goal Seek and Solver tools.
  • 4. Understand the concept of macros and apply basic examples.

Chapter 8

Statistical analysis

  • 1. Perform and interpret descriptive Statistics with the Data Analysis tool.
  • 2. Create histograms with the Data Analysis tool, pivot tables and built-in chart.
  • 3. Perform and interpret correlation with the Data Analysis tool.
  • 4. Perform and interpret regression with scatter plots.
  • 5. Perform and interpret analysis of variance with the Data Analysis tool.
Table of Contents

Preface

  • Who can use this book?
  • Why this book is unique
  • Excel is timeless and now for everyone: there is no excuse
  • Other books and software versions
  • For teachers: using this book in the classroom
  • About the author Acknowledgments and credits

Introduction and Best Practices approach

  • What you will accomplish with Excel at the end
  • Much more than an Excel manual
  • How to use this book
  • Downloading practice files and answer keys
  • Best Practices

Chapter 1

Getting started: a bit of everything

  • 1. Introduction and learning objectives
  • 2. Specific skills
  • 3. Background
    • 3.1  Excel’s functional components
    • 3.2  Excel’s basic building blocks
    • 3.3  Functions and formulas
    • 3.4  Inputs and outputs
    • 3.5  Transforming ranges into tables
    • 3.6  Summary of Best Practices
  • 4. Procedures
    • Part A — Getting started
    • Part B — Using formatted tables
  • 5. Review of functions used

Chapter 2

Text, date & time and logical formulas

  • 1. Introduction and learning objectives
  • 2. Specific skills
  • 3. Background
    • 3.1  Function and formula basics
    • 3.2  The use of names
    • 3.3  Types of functions
    • 3.4  Text formulas
    • 3.5  Date & time in Excel
  • 4. Procedures
    • Part A — Understanding text formulas
    • Part B — Understanding date & time formulas
    • Part C — Understanding logical formulas
    • Part D — Apply your knowledge to a typical example
  • 5. Review of functions used

Chapter 3

Math formulas

  • 1. Introduction and learning objectives
  • 2. Specific skills
  • 3. Background
    • 3.1  Using names for arrays, formulas and nonexistent locations
    • 3.2  Quick review of how math is performed in Excel
  • 4. Procedures
    • Part A — Simple counting and summing
    • Part B — Cool mathematical formulas
    • Part C — Apply your knowledge to a typical example
  • 5. Review of functions used

Chapter 4

Lookup & reference formulas

  • 1. Introduction and learning objectives
  • 2. Specific skills
  • 3. Background
    • 3.1  Lookup formulas
    • 3.2  Relational databases
  • 4. Procedures
    • Part A — Typical lookup procedures
    • Part B — Applied exercise
  • 5. Review of functions used

Chapter 5

Creating beautiful charts

  • 1. Introduction and learning objectives
  • 2. Specific skills
  • 3. Background
    • 3.1  Definition and importance of charts
    • 3.2  Excel and charts
  • 4. Procedures
    • Part A — Chart types
    • Part B — Applied exercise
  • 5. Summary of good practices when creating charts
    • Appendix - Default vs. personalized graphs

Chapter 6

Pivot tables and charts

  • 1. Introduction and learning objectives
  • 2. Specific skills
  • 3. Background
    • 3.1  Pivot tables
    • 3.2  Pivot charts
  • 4. Procedures
    • Part A — Basic pivot table and chart functions
    • Part B — Applying pivot tables and charts to your data
  • 5. Review of functions used

Chapter 7

Error-proof files & other features

  • 1. Introduction and learning objectives
  • 2. Specific skills
  • 3. Background
    • 3.1  Data validation and sheet protection
    • 3.2  What-If analysis
    • 3.3  What-If analysis in reverse: Goal Seek and Solver
    • 3.4  Macros
  • 4. Procedures
    • Part A — Data validation, What-If analysis and macros
    • Part B — Producing an error-proof, secure file
  • 5. Review of functions used

Chapter 8

Statistical analysis

  • 1. Introduction and learning objectives
  • 2. Specific skills
  • 3. Background
    • 3.1  Activating the Data Analysis tool
    • 3.2  Using the Data Analysis tool
  • 4. Procedures
    • Descriptive statistics
    • Histograms
    • Correlation
    • Regression
    • One-way analysis of variance (ANOVA)
    • Two-way analysis of variance (ANOVA)
  • 5. Review of functions used

Final words and indices

  • Further reading and training
  • Function index
  • Select formula index
  • Select formula explanation
  • Glossary of terms
  • Figure credits