Excel - Beginner to Advanced in 4 Hours

Explore relational data by identifying primary keys as unique identifiers and foreign keys as references to related tables, using employee and location examples to illustrate.

Format cells in Excel with number formats for dates, times, currency, percentages, fractions, and custom formats. Use format painter, decimals, thousand separators, and options for negative numbers and color.

Explore conditional formatting in Excel using color scales and highlight rules to visualize scores, set thresholds (less than 60, greater than 89), and refine rules with stop if true.

Apply data validation in Excel to enforce correct entries, including dates, positive integers, and text length, across columns. Use department lists with drop-downs, remove duplicates, and sort for consistency.

Learn practical data cleaning in Excel, handling large datasets from multiple sources, format dates, split columns, convert text to numbers, remove characters, and use text to columns.

Master the text to columns feature to split semicolon-delimited data from a web page into separate columns in Excel, using the data tab and delimiter settings.

Master data filtering in Excel to view specific records. Apply filters to bold headers, subset by position or points, and use text filters such as contains.

Learn how to join multiple text strings using concatenation, using the concatenate function or the ampersand method, to create full names and addresses with proper punctuation, spaces, and line breaks.

Learn to extract text segments in Excel using left, right, mid, find, and len functions, with practical examples like area codes, zip codes, middle digits, and city names.

Learn how to use Excel's substitute and replace functions to swap characters, control instances, and replace specific character ranges with new text, such as replacing dashes with asterisks.

Discover how to apply the upper, lower, and proper functions in Excel to transform text. See examples converting A2 text to uppercase, lowercase, and proper case to capitalize each word.

Learn to clean and standardize a large NFL data set in Excel by correcting names, uppercase team abbreviations, and formatting dates, using mid, left, find, upper, and auto fill.

Use vlookup to pull cost per unit from inventory, apply exact matches, fix table arrays with absolute references, and compute profit and quantity across worksheets.

Learn to use index and match to retrieve quantity available and cost per unit, replacing vlookup with nested functions, exact matches, and dynamic ranges.

Learn how the if function works, yielding true or false through boolean logic. Apply vlookup for quantity checks, and combine and, or, and nested if to handle multiple conditions.

Explore the iferror function in Excel to handle division by zero and other errors, returning a safe value; apply it with vlookup across inventory lists to compute cost and profit.

Learn to use sumif, countif, and averageif for NBA box scores, and extend to sumifs, countifs, averageifs, maxifs, and minifs with multiple criteria.

Master multi-criteria lookups in Excel using array formulas and concatenated keys to retrieve quantities and costs. Learn alternatives with helper columns and the large function with if, including ctrl-shift-enter entry.

Use the large and small functions to identify the best and worst NBA fantasy games from the fantasy points column, applying K values and noting negative points from turnovers.

Explore pivot tables to slice and dice data, summarize totals and averages, apply filters, group dates, and drill down for detailed insights in Excel.

Learn to visualize and compare data using bar and pie charts, create and customize charts, compare budgeted versus actual sales, and build pivot charts for large data sets.