Microsoft Excel Tutorial for Beginners - Full Course

This challenge focuses on comparing the costs of three different vacation options: a Caribbean cruise, Orlando, or Chicago. The spreadsheet is structured to break down costs into per-person expenses (e.g., airfare, attraction tickets) and fixed expenses (e.g., hotel). Students need to input given costs, calculate subtotals, and determine the least expensive vacation option, presenting the results in a bar graph. A second part involves recalculating for a different number of travelers.

This challenge involves comparing three different printers based on purchase price and ink cartridge costs over a two-year period. Key calculations include the cost per page for each printer, annual pages printed based on daily usage, and the total two-year ownership cost. The goal is to determine the most cost-effective printer for two different users with varying printing needs, visualizing the total costs with a bar chart.

The final problem-solving challenge is an in-depth analysis of car ownership costs for three different cars (economical, mid-range, luxury) over their entire lifespan (250,000 miles). The spreadsheet calculates initial costs (purchase price, taxes) and annual costs (insurance, license, gas). A detailed gas cost calculation requires understanding miles driven per year, MPG, and gas price. The challenge also includes calculating the total lifetime cost and average annual cost, with a variation for a buyer who needs a loan (adding 40% to the initial car price).

The course concludes by congratulating students on completing the Excel journey from beginner to expert. The instructor, Shad Sluiter, thanks viewers and invites them to explore his YouTube channel, 'Tech Made Simple,' where he teaches programming, software development, websites, and mobile applications, in addition to Excel.

The introduction outlines a Microsoft Excel course designed for beginners, taught by Shad Sluiter, a computer science and applications teacher. The course features six real-life projects, inspired by successful classroom applications. These projects cover payroll creation, gradebook management, career decision-making, sales database analysis, car inventory tracking, and various problem-solving challenges.

This section introduces fundamental Excel skills, including data entry, navigation, saving files, and creating basic mathematical formulas for calculations like overtime, commissions, averages, maximums, and minimums. It also explains how to create various charts (pie, line, scatter) and introduces advanced features such as relative and absolute cell references, data import/export (CSV), VLOOKUP for searching, pivot tables for data summarization, and text manipulation functions like splitting and concatenating text.

The first project involves setting up a basic payroll spreadsheet. It covers entering employee names, hourly wages, and hours worked. The lesson demonstrates how to use simple formulas to calculate total pay, apply currency formatting, and use fill down features to quickly populate calculations for many employees. It concludes with calculating maximum, minimum, average, and total pay for wages and hours worked, and proper printing techniques.

Building on the payroll project, this section introduces the 'IF' formula to accurately calculate overtime hours. It explains how to insert new columns, create conditional formulas to determine overtime (only if hours exceed 40), and apply a time-and-a-half bonus for overtime. The lesson emphasizes modifying formulas to avoid negative overtime values and correctly calculating total pay, including overtime bonuses.

This project extends the payroll spreadsheet to include multiple weeks of pay. It demonstrates inserting multiple columns simultaneously and using formulas to automatically calculate dates. A key focus is on understanding and applying absolute cell referencing (using dollar signs in formulas) to ensure that specific cells, like hourly wages, remain fixed when formulas are copied across different weeks. It also covers coloring cells to organize data and calculating cumulative totals for the month.

This project focuses on creating a gradebook, introducing conditional formatting and data visualization. It covers setting up test scores, calculating percentages, and using absolute references for correct percentage calculation. Conditional formatting is used to visually highlight top/bottom performers and identify failing scores. The lesson also demonstrates creating bar charts to visualize test results and dynamically linking chart labels to employee names.

This project guides users in creating a decision-making tool for career choices. It involves listing various jobs and factors (pay, job market, enjoyment, talent, schooling), assigning subjective ratings, and then introducing an 'importance factor' for each criterion. The core of this lesson is multiplying job ratings by their importance factors to generate a weighted score, helping users make informed career decisions, and visualizing the results with conditional formatting.

This project delves into analyzing sales data, covering text manipulation, conditional summing, sorting, filtering, and pivot tables. It demonstrates using 'Text to Columns' to split names, calculating profit and commission (using the 'IF' function), and efficient fill down methods for large datasets. Key features include the 'SUMIF' function for conditional aggregation, applying sorting and filtering to data, and creating pivot tables and pie charts to summarize salesperson performance.

This advanced project focuses on managing a car inventory database. It begins with importing data from a text file into Excel. The lesson then covers text functions like 'LEFT', 'MID', and 'RIGHT' to extract information from coded IDs. It introduces 'VLOOKUP' with absolute references to convert abbreviations into full names and addresses data inconsistencies. The project also covers calculating car age, miles per year (handling division by zero errors), warranty status, and creating new car IDs using 'CONCATENATE' and 'UPPER' functions. It concludes with creating pivot tables for driver mileage and scatter charts for age vs. mileage, and exporting results to Microsoft Word.

This section introduces a series of problem-solving challenges designed to reinforce Excel skills. The instructor presents real-world scenarios (vacation planning, school shopping, pet ownership, printer selection, car purchase) and provides a partial Excel solution setup. The goal for students is to complete the spreadsheets by applying learned formulas and generating comparative charts, thereby stretching their analytical and problem-solving abilities.

This is the first of the problem-solving challenges. It demonstrates how to calculate simple interest on four different loans with varying interest rates. The task involves setting up a spreadsheet to calculate total interest paid, total loan amount, and monthly payments. The lesson shows how to create a bar chart to visually compare the monthly payments of different loans, highlighting the impact of interest rates.

This challenge involves creating a spreadsheet to compare the cost of a school shopping list across three different stores. Students need to input item prices, quantities needed, and use formulas to calculate total spending per item and overall store totals. The objective is to identify the cheapest store and represent the findings with a bar chart, with a subsequent challenge for a second person with a different shopping list.

This problem guides users to analyze the cost of owning a dog versus a cat for one year. The spreadsheet setup differentiates between initial one-time costs and ongoing monthly expenses. Students must apply formulas to calculate total costs for each pet, considering quantities for monthly items, and then create a bar chart to visually compare the financial implications of owning either pet.