Microsoft Excel - Excel Rookie to Confident Crash Course

Explore the Excel interface, learn how to write formulas and functions, master relative, absolute, unmixed referencing, and use shortcuts to write formulas faster.

Define formulas and functions in Excel, show how to write a formula starting with = to calculate totals from cell references, and highlight built-in functions with over 475 options.

Learn how Excel functions are written: equal sign, function name, opening parenthesis, and comma-separated arguments. Optional arguments in brackets and a max function example illustrate the syntax of the function.

Master core Excel terms like value, range, contiguous range, non-contiguous range, and array, and see how they apply to functions such as if and vlookup.

Learn to use Excel functions such as sum, average, max, min, and count, and discover multiple ways to write them from the formulas tab to the insert function menu.

Explore absolute, mixed, and relative referencing in Excel, lock columns and rows with dollar signs, and prevent formula changes when copied across cells, using practical examples.

Master essential Excel shortcuts for functions: jump to last non empty cells with ctrl+arrow, select ranges with ctrl+shift+arrow, and cycle corners with ctrl+period, plus quick sums and pivot tables.

Explore how to locate data in Excel using vlookup for data to the right and each lookup for downward data, and learn how index match overcomes these limitations.

Master VLOOKUP to find data to the right in a table by using four arguments—lookup value, table array, column index, and exact match.

Learn how to find data downwards with hlookup, using horizontal lookups, the lookup value, table array, and row index number to perform exact matches.

Explore the key limitations of VLOOKUP and HLOOKUP, including direction constraints, column/row number reliance, and sensitivity to table structure, and learn how index match offers a robust alternative.

Discover how to perform a left lookup with index and match to pull employee data, such as employee ID and date of employment, by dynamically locating rows and headings.

Learn to perform actions in Excel based on conditions using the if function, including nesting ifs with and and or, plus if error and count ifs.

Understand how the if function in Excel uses a logical test to return a value when true and another when false, with pass/fail and yes or blank examples.

Explore key comparison operators—greater than, less than, equal to, greater than or equal to, less than or equal to, and not equal to—and learn how they drive logical functions like IF to evaluate conditions.

Use the if function to test whether sales are greater than or equal to 18,000, returning yes or no, and apply an absolute reference so the result copies down.

Master how to nest if functions in excel to test multiple conditions and return different results, such as tiered commissions for sales above 18,000 and 21,000, and understand evaluation order.

Explore the end function, returning true only when all conditions are met, and the all function returning true if any condition is met, illustrated with a bonus example.

Combine the if function with or and and to classify survey feedback on weight loss and energy into unsatisfactory, satisfactory, and standard met using logical tests and visual tick indicators.

Learn how the IFERROR function in Excel returns a chosen value when an error occurs, using value and value_if_error arguments, and use it with VLOOKUP to hide errors.

Learn to count specific values with the countifs function by applying multiple criteria ranges and criteria, locking ranges, and counting items based on color, product, or region.

Master text case in excel using upper, lower, and proper functions to convert strings to uppercase, lowercase, or proper case, and apply these tips to clean data efficiently.

Apply the trim function to remove leading and trailing spaces from text strings in Excel, preserving single spaces between words and fixing formatting in addresses and names.

Master joining text in Excel with the concatenate function (and the newer cat function), including adding a space between first and last names.

Learn to use Excel's len to count characters (spaces included) and the difference between search and find for locating text within strings, with a second-dash example using a start position.

Learn to extract job titles from dash-delimited strings using len, search, and switch with right, left, and mid in excel, and apply proper and lower for formatting.

Master conditional formatting in Excel by applying highlight cell rules, top bottom rules, data bars, color skills, and icon sets, then use formulas and manage rules to visualize data effectively.

Master conditional formatting in Excel. Access it from the home tab and apply highlight cell rules, top bottom rules, data bars, color skills, and icon sets.

Learn to apply conditional formatting with highlight cell rules to emphasize key data, using greater than and less than criteria and color formatting to mark high and low scores.

Learn to apply conditional formatting with highlight cell rules using text that contains, then switch to linking the target text to a cell for dynamic highlighting via a dropdown.

Apply conditional formatting with top/bottom rules to highlight mathematics scores above average, using green fill with dark green text or yellow fill with yellow text, and copy formatting across cells.

Apply conditional formatting to visualize data with data bars, color skills, and icon sets, using monthly sales, expenditures, and net profit to reveal performance patterns.

Apply conditional formatting with formulas to trigger formatting, using a rule like B2 >= 40,000, and lock the column while keeping rows relative for a range.

Demonstrates using a formula in conditional formatting to highlight employees who meet two criteria, such as over 100 doors and over 110 windows, by locking columns and applying a rule.

Use formulas in conditional formatting to highlight rules when employees exceed 100 doors or 110 windows, employing the all function to return true for any (or all) met conditions.

Learn to manage conditional formatting rules in Excel using the rules manager, create and edit rules, delete or duplicate them, and adjust the applies to range for precise highlighting.

Learn how macros automate repetitive tasks in Excel by recording and running macros, and explore absolute versus relative macro recording, plus an introduction to VBA and the Visual Basic Editor.

Discover how Excel macros let you record a sequence of actions with the macro recorder and execute them with a single click to automate routine tasks.

Learn to record your first Excel macro, set a name and shortcut, choose where to store it, describe its purpose, and run or edit it to automate formatting tasks.

Save your workbook as an excel macro enabled workbook (.xlsm) to preserve macros, avoiding a macro-free save so the macro remains functional.

Discover how VBA records every macro step in the Visual Basic Editor and how to read and modify that code to automate tasks in Excel.

Record macros in Excel using relative references to apply formatting to the sales report anywhere in the sheet, and absolute references to lock actions to the original data.

Launch and build pivot tables to summarize large data and extract key insights. Include totals and grand totals, adjust layout and styles, and filter using slices and timelines.

Discover how pivot tables in Excel summarize large data sets, build a table with fields, include totals and grand totals, and use slices and timelines to filter insights.

Familiarize yourself with the source data and ensure a square table with headings and avoid missing rows before building a pivot table to summarize and filter by country or brand.

Select a data cell, insert a pivot table, let Excel detect the source range, adjust if needed, and place the pivot table on a new or existing worksheet.

Build and customize a pivot table by dragging fields from the pivot table fields menu into filters, rows, columns, and values to shape layout and insights.

Create a duplicate pivot table attached to the same source data, copy and paste it, then use two independent pivot tables to compare brand, country, and customer breakdowns.

Learn to apply compact, outline, and tabular pivot table layouts in Excel by using the design tab's report layout options to toggle styles and arrange fields like country and brands.

Explore pivot table style options by using the design tab to apply styles, toggle row and column headers, and enable banded rows or columns to customize your pivot table.

learn to filter pivot table reports with slicers and timelines, selecting fields like customer or order date to filter by single or multiple values and date ranges.