Excel from the Beginning Course Overview

Excel from the Beginning Course Overview

Instructor: Shad Sluiter

Course Structure

• Six Real-Life Projects: Each project will help you learn to use Excel in practical scenarios.
• Target Audience: Students range from high school to university level effectiveness validated through classroom use with Free Code Camp.

Projects Overview

1. Payroll Setup: Track employee wages and overtime pay.
2. Gradebook: Compute percentages, identify top and underperforming students.
3. Career Decision Tree: Use spreadsheets to help determine career choices based on preferences, pay, and benefits.
4. Sales Database: Analyze sales data, sort best salespeople, calculate commissions, and generate charts.
5. Car Inventory Project: Database-like actions with large data sets; concatenate and split fields, make reports.
6. Challenge Assignments: Problem-solving exercises based on earlier tutorials. Partial solutions provided as practice.

Topics Covered

• Basic Excel Operations:
  • Entering data
  • Navigating spreadsheets
  • Saving files
  • Setting up formulas (overtime calculations, commission percentages, averages, max/min)
• Chart Creation:
  • Pie charts, line charts, scatter charts
  • Choosing chart types based on data
• Advanced Excel Features:
  • Relative and absolute references
  • Data imports/exports (e.g., CSV files)
  • VLOOKUP for searching and retrieving data
  • Pivot tables for summary reports
  • Text splitting and concatenation

First Lesson: Payroll

• Objective: Setup a payroll sheet for employees tracking hourly wage and hours worked
• Steps:
  1. Launch Excel and start a new workbook
  2. Identify cell organization: columns (letters) and rows (numbers)
  3. Enter titles and data for employees (last name, first name, hourly wage, hours worked, total pay)
  4. Use simple formulas to calculate payroll:
     • Formula for pay: = hourly wage * hours worked
     • Copy and paste formulas for efficiency
  5. Adjust column widths, format cells (right/left alignment, dollar signs)
  6. Add summary calculations (max, min, average, total)
     • Use predefined formulas like MAX, MIN, AVERAGE, and SUM

Gradebook Expansion

• Objective: Extend payroll setup for multiple weeks, calculating overtime
• Steps:
  1. Insert new columns for additional weeks
  2. Calculate weekly totals and overtime
  3. Use absolute cell referencing for consistency across weeks
  4. Format and organize data with conditional formatting for readability
  5. Calculate yearly totals and averages, format for clarity

Gradebook Project

• Objective: Create a gradebook with conditional formatting and percentages
• Steps:
  1. Set up spreadsheet with names and test scores
  2. Calculate percentages for each test
  3. Use conditional formatting for quick visual analysis (icons, color scales)
  4. Add formulas for determining top and failing students
  5. Generate charts to visualize performance data

Career Decision Maker

• Objective: Determine best career based on various factors
• Steps:
  1. List potential jobs and assign ratings to factors (pay, job market, enjoyment, etc.)
  2. Calculate total score based on weighted importance of each factor
  3. Use conditional formatting to highlight best career choices
  4. Create charts for visual comparison

Sales Report Project

• Objective: Analyze and report on sales data
• Steps:
  1. Import sales data (CSV text files)
  2. Split and concatenate text fields for organization
  3. Use SUMIF function for conditional calculations
  4. Sort and filter data for specific insights
  5. Create pivot tables and charts to summarize data

Car Inventory Project

• Objective: Manage and analyze company car data
• Steps:
  1. Import car inventory data (text files)
  2. Use text functions (LEFT, MID, RIGHT) to clean and organize data
  3. Create lookup tables for matching car codes to names
  4. Calculate average miles, ages, and costs
  5. Generate pivot tables and scatter charts for analysis

Problem Solving Exercises

• Objective: Apply Excel skills to solve practical problems
• Tasks:
  1. Calculate interest payments on loans
  2. Shopping list cost comparison
  3. Pet ownership cost analysis
  4. Vacation cost comparison
  5. Printer cost analysis
  6. Car purchase cost analysis

Conclusion

• Final Remarks: Congratulations on completing the course. Check out further learning resources on instructor’s YouTube channel 'Tech Made Simple'.