Excel from the Beginning Lecture Notes

Jul 13, 2024

Excel from the Beginning

Course Overview

  • Instructor: Shad Sluiter
  • Platform: Free Code Camp
  • Projects: Six real-life projects to learn Excel.
  • Target Audience: High school to university students.

Course Projects

1. Payroll Setup

  • Track employee salaries and overtime.

2. Gradebook Setup

  • Compute student percentages and class rankings.

3. Career Decision Tree

  • Evaluate careers based on preferences and benefits.

4. Sales Database

  • Sort data, determine top salespeople, compute commissions, and create charts.

5. Car Inventory Project

  • Use database actions, concatenate fields, and create reports.

6. Challenge Assignments

  • Apply learned concepts to solve provided problems.

Key Excel Features Covered

Basic Skills

  • Entering Data: Learn how to input data into cells.
  • Navigating: Understand how to move within a spreadsheet.
  • Saving: Save your Excel file.

Formulas

  • Overtime Calculation: Compute additional hourly wages.
  • Percentages: Calculate percent-based values.
  • Averages, Maximum, Minimum: Basic statistical functions.

Charts

  • Types: Pie charts, line charts, scatter charts.
  • How to create charts from data.

Advanced Features

  • Relative and Absolute References: Use $ to maintain cell references.
  • CSV Import/Export: Import and export data using CSV format.
  • VLOOKUP: Lookup and retrieve data from a table.
  • Pivot Tables: Summarize and analyze large datasets.
  • Text Functions: Split and concatenate text.

Initial Lessons and Tutorials

Lesson 1: Setting Up Payroll

  • Create a basic payroll structure in Excel.
  • Step-by-Step: Enter data, set up column headings, invent names, calculate hours worked.
  • Formulas: Use '=' for calculations, right-click to copy formulas.
  • Formatting: Apply currency format to wages, expand columns.

Lesson 2: Adding Overtime Calculation

  • Insert columns for overtime and bonuses.
  • Formulas: IF statements for conditionally calculated overtime hours.
  • Final Calculations: Compute total pay including bonuses, and use fill-down options.

Lesson 3: Extending Payroll Over Multiple Weeks

  • Additional Columns: Insert more weeks of data.
  • Formulas: Adjust absolute references to compute weekly payments correctly.
  • Formatting: Color-code sections for clarity.

Lesson 4: Gradebook Setup

  • Term-Based Computation: Calculate grades, percentages.
  • Conditional Formatting: Highlight top and bottom students.
  • Charts: Graph student performance.

Lesson 5: Career Decision Maker

  • Weighted Factors: Pay, job market, enjoyment, etc.
  • Decision Matrix: Use VLOOKUP and SUM functions to evaluate decisions.
  • Summary Charts: Display results graphically.

Lesson 6: Sales Reports

  • Data Handling: Import sales data, sort, and filter.
  • Pivot Tables: Summarize data by salesperson.
  • Charts: Visualize best performers.

Lesson 7: Car Database Management

  • Import Data: Import and format text files into Excel.
  • Text Functions: Use LEFT, MID, RIGHT for string operations.
  • VLOOKUP: Fetch related data from reference tables.
  • Pivot Tables and Charts: Summarize and visualize car data.

Problem Solvers Section

  • Real-world problems to reinforce learning.
  • Example Problems: Vacation cost calculation, pet ownership costs, cell phone plans, etc.

Final Advice

  • Comprehensive Review: Practical applications enhance learning.
  • Encouragement: Continue practicing different Excel features.