Excel Day 2: Tables

Objectives

  • Students will understand how to use conditional formatting
  • Students will understand how to use pivot tables and why they’re useful
  • Students will be able to create and manipulate tables

Outline

Hook: Show the class what the text file looks like and then show them what the same data looks like organized in Excel

Introduction

  • Does anyone have any questions since last time?

Review Import, Data Cleaning, Data Formats

Power Query Editor

  • What it is
  • Why we use it
  • How to undo
  • Use first row as headers
  • Data type
  • Split column / format / extract
  • Add Column
    • Custom / conditional
  • Transform Data

Create and manipulate tables

  • Shortcuts (Cmd+Shift+Over/Down)
  • Format as a Table
  • Themes
  • Sorting and Multilevel Sorting
  • Filtering
  • Totals Row
  • Slicers
    • Slicer Settings

Pivot Tables

  • Why?
  • Pivot Table Analyze
  • Design tab

Conditional Formatting

  • Less than
  • Greater than
    • Color scales
    • Data bars
    • Top / bottom rules
  • Clear / Manage rules
  • Custom new rule

extra: CONCATENATE()

Conclusion

  • Recap
  • Next time:
    • Functions/Formulas in depth 
  • Help desk/Instagram/Upcoming classes