Microsoft Excel 2021/365: Intermediate Level Training

Adopt a standard design approach for spreadsheets by enforcing consistent formatting, naming, and audience-aware layouts, using tables, color coding, data validation, and protection to separate data, calculations, and analysis.

Set up a data validation dropdown to control input and prevent errors, then protect the sheet to lock critical formulas while allowing dropdown selections.

Explore how to use Excel logical functions and, or, and if to make decisions with real-world examples like approvals, pass/fail, and shipping fees, including nested and multiple tests.

Explore the if function with the formulas dialog box and f x icon, and apply it to a shipping calculation using totals over 1500, cell references, and F4 locking.

Explore how to build nested if statements in Excel to assign end-of-year bonuses based on job rating, including constructing, troubleshooting, and comparing alternative approaches like the ifs function.

Explore how the IFS function improves building complex logical tests in Excel compared to nested IF statements. Learn practical examples, including day-of-week mapping and error handling for invalid inputs.

Learn to use sumifs, countifs, and averageifs for multiple criteria. Apply criteria like company, job title, and total sales, with examples using greater than or less than thresholds.

Master VLOOKUP with approximate match to retrieve tax rates from salary brackets, using a true argument and a named range to return the correct marginal rate.

Learn to use hlookup on horizontally oriented data by transposing with paste special, creating a named range movie list, and retrieving year, rating, and genre with exact match.

Discover how xlookup and xmatch in Excel 2021 simplify lookups, replacing index/match with easier syntax, including not found, exact match, and search mode options, plus table references.

Practice creating a data validation dropdown in Excel to list athletes and retrieve their bib number, route, and position using index-match and vlookup, with iferror for error handling.

Master sorts on multiple columns in Excel 2021/365 using the sort dialog, adding levels, and sorting by last name, city, and town with colors and icons.

Define a custom list in Excel, import or create the order, and apply it with the custom sort to arrange data in a specific sequence, such as Mr., Miss, Mrs.

Discover Excel 2021 dynamic array functions SORT and SORTBY to sort data by single or multiple columns, with sort index and order, and auto-updating results via tables.

Master Excel's advanced filter to extract a unique list, filter by complex criteria, and copy results to a new location using and/or conditions.

Explore how to extract unique values in Excel 2021/365 using the unique function, including sales reps, compare it with the advanced filter, and leverage dynamic arrays and tables for updates.

Learn to use the filter function in Excel 2021 to extract records with one or more criteria and have results update dynamically. Combine with sort to output multi-criteria, sorted list.

Master sorting and filtering in Excel by creating unique regions list, using a data validation dropdown, applying a dynamic filter with a no-records message, and sorting by root and position.

Understand how Excel stores dates as day counts from the first of January 9500 and times as fractions of a day, then apply short date, long date, or time formatting.

Master custom date formats in Excel, using today and now functions and shortcuts like control semicolon to hard-code dates and times.

Use the work day function and the work days int function in Excel 2021/365 to compute finish dates from a start date by adding work days, excluding weekends and holidays.

Calculate workdays between start and end dates with networkdays and networkdays.int, excluding weekends and holidays, and apply cell-locking and formatting tips for accurate results.

Learn to calculate age and date differences with the datedif function using date of birth and today’s date, and format results as years, months, or days.

Master end-of-month calculations with eomonth and edate to determine the last day of each month and shift dates by months, including leap year handling.

Extract month, day, year, weekday number, weekday name, and month name from a date in column A using Excel functions, and check if it is Friday with an if formula.

Import and combine data from a folder and text files in Excel using Get Data and Power Query, then clean the dataset by removing blanks, standardizing case, and removing duplicates.

Remove blank rows and blank cells, fill empty cells with zero, and delete duplicates to clean your data in Excel 2021/365. Prepare datasets for analysis and pivot tables efficiently.

Split data with text functions in Excel, using left, mid, right, and find to extract names and codes; see when simple formulas suffice and how the easier method follows.

Master flash fill in Microsoft Excel 2021/365 to split and combine data quickly, using three invocation methods, with caveats when data has blank columns.

Discover how to join text strings in Excel using the ampersand operator and the CONCAT function, including adding spaces, dashes, and brackets to combine names and job titles.

Learn to clean data by removing blanks and duplicates, format numbers, create a region code by combining store code and country with a dash, and name the table store_sales.

Master pivot tables to analyze data dynamically, switching fields to summarize gross sales by country or by product, and format, sort, and filter results for clear insights.

Create a pivot table from a named data table, such as product sales, and use the fields pane to analyze profit by country on a new worksheet.

Apply currency formatting with zero decimals to pivot table data via value field settings. Display negatives in brackets and preserve formatting as the pivot table grows.

Learn to use show values as and summarize values by in pivot tables, applying sums, counts, averages, running totals, and the percentage of the grand total to analyze sales data.

Group data in pivot tables by using date fields to automatically split into years, quarters, and months; create custom groups and adjust show at top of group for clarity.

Explore pivot table report layouts, including compact form, outline form, and tabular form, using the design ribbon, and learn to adjust column widths and insert a blank line for clarity.

Create a pivot table from stool_sales; category in columns, town in rows; show sales, format currency with zero decimals, apply top five by sales, name it sales by town cats.

Learn to transform pivot tables into clear pivot charts by selecting appropriate chart types, refining data, and applying filters such as top five items to visualize key trends.

Master pivot chart formatting in Excel by adjusting grid lines, applying chart styles, and customizing data labels and legends for flexible analysis.

Create map charts from pivot table data by removing the data, building the map chart, and then pointing the chart back to the pivot table to stay dynamic.

Create and format a pivot chart in Excel by filtering for laptops, mobile phones, and PCs, using a bar chart, and titling it top five towns by category.

Learn to add visual filters to pivot tables and charts using slicers, customize their style and layout, and apply multiple slicers for interactive data exploration.

Update pivot table data quickly by placing source data in an Excel table that automatically expands; refresh pivot tables and charts with Alt+F5 to include 2020 data and slicers.

Add interactivity with a manager slicer and a date timeline slicer, customize styles, then update the data with July entries and refresh the pivot tables and charts.