📊

Excel from the Beginning - Notes

Jun 16, 2024

Excel from the Beginning - Lecture Notes

Introduction

  • Instructor: Shad Sluiter
  • Course Offered By: Free Code Camp
  • Previous Experience: Taught computer science and computer applications; successful with students from high school to university levels
  • Course Scope: Learn Excel through six practical projects

Overview of Course Projects

  1. Payroll Setup
    • Track employee payments and overtime
    • Basic Excel functions, entering data, and navigating through spreadsheets
  2. Gradebook Setup
    • Calculating percentages, determining class rankings
  3. Career Decision Tree
    • Evaluate career based on preferences, pay, and benefits
  4. Sales Database
    • Sort data, determine best salespeople, calculate commissions, make charts
  5. Car Inventory Project
    • Database actions: concatenate fields, split fields, make reports
  6. Problem Solving Exercises
    • Six challenge assignments to apply concepts learned

Key Excel Concepts

Basic Operations

  • Data Entry & Navigation: How to enter data, navigate through a spreadsheet, and save it
  • Formulas: Simple mathematical formulas to solve problems (e.g., compute overtime, commissions, averages, maxima/minima)
  • Charts: Creating pie charts, line charts, scatter charts
  • Shortcuts: Efficient ways to handle large data sets

Advanced Features

  • Relative and Absolute References: Using fixed and flexible cell references
  • Data Import/Export: Handling CSV files, interfacing with other programs offline and online
  • VLOOKUP: Searching for specific data in Excel
  • Pivot Tables: Summarizing large data sets, creating readable reports
  • Text Functions: Splitting and concatenating text fields

Example Projects Detailed Walkthroughs

Project 1: Payroll Setup

  • Step-by-Step Tutorial: Setting up employee data, hourly wage, hours worked
  • Formulas: Basic calculations for pay, overtime, etc.
  • Shortcuts: Copying and pasting formulas
  • Data Formatting: Adjusting column widths, using currency formats
  • Aggregates: Calculating maximum, minimum, average, total values

Project 2: Extended Payroll

  • Adding Overtime: Calculating and factoring in overtime pay
  • IF Formulas: Conditional operations for varying situations

Project 3: Monthly Payroll Extension

  • Multiple Weeks: Adding data for several weeks in a month
  • Fill Options: Advanced use of copy, paste, and fill tools in Excel
  • Absolute Referencing: Ensuring correct formula applications across different cells
  • Data Visualization: Utilizing colors to differentiate data sets

Project 4: Gradebook Project

  • Conditional Formatting: Highlighting data based on values
  • Percent Calculation: Converting grades/totals to percentages
  • Sorting & Filtering: Categorizing and refining data visibility
  • Charts: Creating and customizing different types of charts (e.g., bar charts)

Project 5: Career Decision Maker

  • Weighted Decision Making: Evaluating career options based on multiple weighted factors
  • SUMIF Functions: Applying functions based on conditions
  • Conditional Formatting: Visualization tools for decision outcome

Project 6: Sales Report

  • Extensive Data Handling: Working with large data sets, sorting, filtering
  • Profit Calculation: Creating profit formulas
  • SUMIF & Pivot Tables: Summarizing sales data efficiently
  • Charts: Visual representation of summarized data

Project 7: Car Inventory Database

  • Importing Data: Converting text files to Excel data
  • Text Functions: LEFT, MID, RIGHT, and other text-related functions
  • VLOOKUP: Automating data entry from reference tables
  • Complex Formulas: IF, CONCATENATE, etc.
  • Summarizing Data: Using Pivot Tables for high-level summaries

Practical Problem Solving Exercises

Problems Covered

  1. Shopping List Comparison
    • Compare total costs across multiple stores
  2. Pet Cost Comparison
    • Compare costs of owning a cat vs. a dog
  3. Vacation Cost Analysis
    • Compare costs for different vacation options (Caribbean, Orlando, Chicago)
  4. Printer Purchase Decision
    • Compare costs for different printers based on usage
  5. Cell Phone Plan Analysis
    • Break down and compare expenses for different cell phone plans
  6. Car Purchase Decision
    • Compare total ownership costs for different cars

Conclusion

  • Final Notes: Use Excel to solve real-world problems both personally and professionally
  • Extra Resources: Check out instructor’s YouTube channel for more tutorials on programming, software development, and web applications

Full transcript