O'Reilly logo
live online training icon Live Online training

Foundations of Microsoft Excel

Functions, tables, and pivot tables

Dawn Griffiths

Do you use Excel for simple lists but get lost when you need to do something more complex? Are you tired of wasting time trying to manually make sense of your data?

Do more with the data you have. Join expert Dawn Griffiths to learn how to make calculations and summarize your data like a real Excel power user. You’ll learn how to use Excel’s powerful built-in functions to quickly get the results that you want and discover how to use tables to organize, sort, filter, and summarize your data. Finally, you’ll uncover the mysteries of pivot tables and see how they give you a flexible way to summarize your data in multiple ways.

What you'll learn-and how you can apply it

By the end of this live, hands-on, online course, you’ll understand:

  • How Excel’s built-in functions help you get more out of your raw data
  • How tables help you organize and summarize your data
  • How pivot tables let you summarize and perform calculations on more complex datasets

And you’ll be able to:

  • Use functions to perform calculations on your data
  • Turn your raw data into a table, allowing you to easily filter, sort, and summarize it
  • Create pivot tables that can flexibly summarize your data in multiple ways, giving you the answers that you want when you need them

This training course is for you because...

  • You use Excel to hold lists or tabular information, and you want to be able to do something useful with all that data.
  • You have rudimentary Excel skills and want to become an Excel power user.

Prerequisites

  • Only rudimentary knowledge is required, such as how to create a worksheet and enter data into cells.

Recommended preparation:

Recommended follow-up:

  • Read Microsoft Excel 2019 Bible (coming soon)

About your instructor

  • Dawn Griffiths has a First-Class Honours degree in Mathematics and over 20 years experience using Excel. She has written several books in the Head First series, including Head First Statistics, Head First 2D Geometry, Head First Android Development and Head First Kotlin. Dawn also developed the animated video course The Agile Sketchpad with her husband, David, as a way of teaching key concepts and techniques in a way that keeps your brain active and engaged.

Schedule

The timeframes are only estimates and may vary according to how the class is progressing

Number and Text Functions (60 minutes)

  • Presentation: Introduction to functions
  • Walkthrough: Functions and ranges
  • Exercise: What’s the result of each range?
  • Walkthrough: Absolute and worksheet references
  • Walkthrough: Aggregate functions and rounding values
  • Exercise: Use aggregate functions to calculate results
  • Q&A
  • Presentation: Text functions
  • Walkthrough: Use text functions to manipulate text
  • Exercise: Use text functions to produce results
  • Q&A
  • Break

Date/Time and Conditional Functions (40 minutes)

  • Presentation: Date/time functions
  • Walkthrough: Useful date/time functions
  • Exercise: Calculate the differences between dates, including weekends and holidays
  • Q&A
  • Presentation: Using functions with conditions
  • Walkthrough: COUNTIF, SUMIF and AVERAGEIF
  • Exercise: Using conditional functions to calculate results
  • Q&A

Troubleshooting (10 minutes)

  • Presentation: Errors and troubleshooting
  • Walkthrough: How to track down errors
  • Q&A
  • Break

Tables (25 minutes)

  • Presentation: Why tables are useful
  • Walkthrough: Creating a table, and adding a total row
  • Exercise: Create a table with a total row
  • Walkthrough: Add a calculated column
  • Exercise: Add a total column to the table
  • Q&A

Pivot tables (25 minutes)

  • Presentation: When to use a pivot table
  • Walkthrough: How to create and interpret a pivot table
  • Exercise: Create a pivot table and read results
  • Walkthrough: Adding secondary rows and columns
  • Exercise: Add secondary rows to the pivot table
  • Q&A