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

Functions (60 minutes)

  • Lecture and hands-on walk-thoughs: Introduction to functions; functions and ranges; absolute references; aggregate functions and rounding values; text functions; how text functions work; date and time functions and how to use them; troubleshooting functions
  • Hands-on exercise: Determine which range returns the correct result; use aggregate functions to calculate results; use functions to manipulate text; use date and time functions to perform calculations
  • Group discussion: Dealing with common function errors
  • Q&A
  • Break (10 minutes)

Conditional functions and tables (50 minutes)

  • Lecture and hands-on walk-thoughs: Using functions with conditions; using COUNTIF, SUMIF, and AVERAGEIF; tables; creating and using a table; adding total rows and columns; analyzing a table to visualize your data
  • Hands-on exercise: Use conditional functions to return results; create a table with calculations; add extra features to the table
  • Q&A
  • Break (10 minutes)

Pivot tables (50 minutes)

  • Lecture and hands-on walk-thoughs: Pivot tables; how to create and use a pivot table; adding secondary rows and extra columns
  • Hands-on exercise: Create a pivot table and get results; add secondary rows to the pivot table
  • Q&A