Skip to Content
View all events

Foundations of Microsoft Excel

Published by O'Reilly Media, Inc.

Beginner content levelBeginner

Functions, tables, pivot tables, and Power Query

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. You’ll uncover the mysteries of PivotTtables and see how they give you a flexible way to summarize your data in multiple ways. You'll find out how to use Power Query to split, extract and import data. Along the way, you’ll discover tips, tools, and features to help you get more out of Excel and become more productive.

What you’ll learn and how you can apply it

  • 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 PivotTables let you summarize and perform calculations on more complex datasets
  • How Power Query lets you transform data and import it from other sources

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 live event 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:

Schedule

The time frames are only estimates and may vary according to how the class is progressing.

Ranges, references, and number unctions (60 minutes)

  • Demonstrations: Ranges; absolute and relative references; performing calculations with SUM, COUNT, MAX, MIN, and AVERAGE; rounding values
  • Exercises: Use ranges and functions to calculate results
  • Q&A
  • Break

Text functions (30 minutes)

  • Demonstrations: Using functions to join text, change text case, extract characters, and find and replace text
  • Exercises: Use functions to manipulate text
  • Q&A

Conditional functions (30 minutes)

  • Demonstrations: Using functions with conditions, such as IF, COUNTIF, SUMIF, and AVERAGEIF
  • Exercises: Use conditional functions to calculate results
  • Q&A
  • Break

Date and time functions (30 minutes)

  • Demonstrations: Understanding dates and times in Excel; calculating the difference between two dates, including working days; other useful date/time functions
  • Exercises: Use date/time functions to calculate results
  • Q&A

Troubleshooting formulas (30 minutes)

  • Demonstrations: Common types of error; troubleshooting formulas with formula auditing tools
  • Q&A
  • Break

Tables (25 minutes)

  • Demonstrations: Why tables are useful; creating a table; sorting and filtering; using a total row; adding a calculated column
  • Exercises: Create a table with a total row and calculated column
  • Q&A

PivotTables (35 minutes)

  • Demonstrations: When to use a pivot table; creating a PivotTable; using Rows, Columns, and Values; using secondary rows and columns
  • Exercises: Create a PivotTable with secondary rows and interpret the results
  • Q&A
  • Break

Extra features and tools (30 minutes)

  • Demonstrations: Using extra Excel features including inserting a chart, AutoFill, Flash Fill,Paste Special, and the Quick Access Toolbar
  • Exercises: Use extra tools and features
  • Q&A

Power Query (30 minutes)

  • Demonstrations: Power Query overview; splitting data into columns and extracting codes; importing data from a file; importing data from files in a folder
  • Q&A

Your Instructor

  • Dawn Griffiths

    Dawn Griffiths is an author and trainer with over 20 years of experience using Excel. Her most recent book is Excel Cookbook, and she’s also written several books in the Head First series, including Head First Statistics, _Head First Android Development, and Head First Kotlin. Dawn also developed the animated video course The Agile Sketchpad with her husband, David, to teach key concepts and techniques in a way that keeps your brain active and engaged.

    Xlinksearch

Skill covered

Microsoft Excel