Microsoft Excel 365 Intermediate

Adopt a standard, implement consistent formatting and Excel tables to design better spreadsheets. Separate source data, calculations, and analysis; use cell styles, color coding, and data validation to control input.

Create and customize cell styles, including duplicates, to visually indicate formulas and input cells, improve readability, and use a legend to explain color meanings.

Learn to control data input in Excel by using data validation dropdown lists and protecting the sheet to prevent spelling errors and keep key formula cells accurate.

Learn to navigate large Excel workbooks quickly by jumping between worksheets, creating hyperlinks and buttons, and using shapes and icons with screen tips to reach the right worksheet fast.

Create a summary sheet at the workbook’s start to guide usage, show tab instructions, cell style meanings, save versions, and track changes for clarity.

Learn to accelerate data entry in Excel by using forms to input data quickly, add new records, and manage invoice data within a table via the Quick Access toolbar.

Format data as an Excel table, designate input cells B, C, D, F, compute sales tax in column E at 20%, and add records via Quick Access toolbar forms button.

Explore Microsoft Excel 365 intermediate: recap of logical functions, using the if formula to test data and output yes or no for expenses above 1000, using absolute references with F4.

Use and and or with if statements to evaluate multiple conditions, such as test score pass/fail thresholds and a 20% shipping fee for weight above 30 kg.

Explore how nested if statements in Excel map job ratings to bonuses, lock references with F4, and compare with IFS, XLOOKUP, and VLOOKUP.

Learn to use the IFS function as a shorter alternative to nested ifs in Excel 365, intermediate course, for calculating employee bonuses by job rating and comparing methods.

Master conditional ifs with countifs, sumifs, and averageifs, using multiple criteria and criteria ranges. Apply these functions to sum, count, and average sales by company and job title.

Learn how to use ifna and iferror to handle errors in Excel formulas, replace na errors with blanks, and understand the difference between specific na errors and general errors.

Combine logical formulas with conditional formatting to highlight a selected student from a data validation dropdown within a dynamic table that expands as new records are added.

Practice excel formulas by filling prize money in column e with nested ifs or the ifs function, then count usa, australian females, uk male silver, and total usa prize money.

Master vlookup exact and approximate match using a parts catalog to fetch descriptions and prices, and compare it with xlookup while handling not found errors.

Learn how to use HLOOKUP for horizontally organized data, compare it with VLOOKUP, transpose data, create a named range, and retrieve year, rating, and genre with row index numbers.

Learn the limitations of vlookup and use index and match for flexible lookups across categories, apps, revenue, and profit with a data validation dropdown.

Explore Xlookup and Xmatch to simplify lookups beyond Vlookup and index and match, using optional arguments and exact or bottom-up searches.

Learn how to handle duplicate lookups in Excel 365 using xlookup and xmatch, and create a unique key by combining name and department to correctly return a country with vlookup.

Explore how to use the choose function for lookups by mapping a job rating to a class and by combining choose with randbetween to randomly assign teams.

Learn how the switch function replaces vlookup or xlookup by defining cases inside the formula. See a practical IMDb score rating example showing switch without an external table.

Practice using lookup functions with a data validation drop-down in cell H3 to retrieve an athlete's bib number, route, and finish position, with error checking for NA errors.

Master multi-column sorting in Excel 365 by sorting department, business unit, and country using the sort dialog, add levels, and sort by cell or font colors.

Use the unique function to extract departments, then create and import a custom list to sort records in Excel by that order via the data tab.

Learn to sort data with dynamic array formulas in Excel 365 using sort and sort by, place results anywhere, and sort by multiple columns with curly braces.

Learn to use the filter function as a dynamic array that spills results, apply multi-criteria filters with and/or logic, and output or sort results anywhere in the workbook.

Learn to extract a unique list from a range with the unique function, then refine results using sort, drop, and array to text for dynamic arrays and data validation.

Practice sorting and filtering data by creating a unique regions list, building a data validation drop-down in cell f4, filtering by region, and sorting results by country.

Find duplicates with conditional formatting and highlight unique values to identify attendees who didn't attend both webinars. Use go to special row differences to compare two data lists.

Discover how to find duplicates across two attendee lists in Excel using filter and countif to extract names of people who attended both webinars, enabling targeted follow-up emails.

Practice finding duplicates by adding a formula to compare two country lists in columns b and c, identifying the countries that appear in both lists in column d.

Learn how to use large and small functions in Excel to extract top or bottom values from a data set, including using k, arrays, and xlookup and if for criteria.

Learn to rank data in Excel 365 with rank.eq and rank average, and compare to the legacy rank function; see how ties and order affect results.

Explore Excel's subtotal and aggregate functions, learn how to sum, count, and ignore hidden rows or errors, and use color-based filtering to sum or count highlighted data.

Practice statistical functions in Excel by calculating the average score, the median score, and the scores that appear most often, then rank players in ascending order by their scores.

Explore rounding values in Excel using round, round up, and round down, with practical examples of price plus tax, decimal formatting, and how rounding affects finance calculations.

Master specialized rounding in Excel with MROUND to a chosen multiple, and compare ceiling dot math and floor dot math for rounding up or down to the nearest multiple.

Practice rounding values by calculating a 1.2% bonus and the new salary in Excel 365 intermediate. Ensure the total salary is rounded to the nearest 100.

Master custom formatting in Excel by using four-part, semicolon-delimited rules to define positive, negative, zero, and text displays, including placeholders and currency formatting.

Calculate variance as actual minus target and visualize it with color-coded formatting in Excel. Apply symbols for above and below target, and special formatting for zero values to improve readability.

Master advanced conditional formatting in Excel 365 with data validation driven row highlighting and formula rules, and learn to insert checkboxes, track completion as done, and apply strikethrough.

Practice calculating the difference between test scores and apply custom formatting to show positives in green with an up arrow and negatives in red with a down arrow.

Understand how Excel stores dates as serial numbers, how formatting displays short or long dates in US and UK formats, and how times are fractions of a day.

Master date and time formulas, today and now, and learn custom formats and keyboard shortcuts to insert hard coded dates in Excel 365.

Explore common date and time functions in Excel 365, extracting day, month, year, and weekday, formatting results, and building dates or times from separate components.

Explore workday and networkdays functions in Excel 365, customize weekends with the international version, include holidays, and calculate finish dates and total working days for projects.

Master end-of-month and edate functions in Excel 365 to build loan amortization schedules, calculate dates, and explore future and past dates including leap years.

Practice date and time functions by calculating project durations for Olivia’s working days and setting today’s date in C4 to generate invoices on the last day of the finish month.