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.

Improve workbook readability by applying cell styles to differentiate input cells, calculation cells, and heading cells, and customize styles to match your organization's palette.

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

Add navigation buttons and hyperlinks to an Excel workbook to enable quick jumps between worksheets, using text links, shapes, and icons, with screen tips and keyboard shortcuts for easy navigation.

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.

Explore how sumif, countif, and averageif use criteria to sum, count, and average data, with practical examples from a sales table.

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.

Use IFERROR and IFNA to replace spreadsheet errors with meaningful messages, such as no bonus, improving clarity when using IFS for bonus calculations.

Apply nested ifs or the ifs function to calculate prize money by medal type (bronze, silver, gold), then use countifs and sumifs for multi-criteria tallies across country, gender, and medal.

Discover how to use vlookup for exact matches to pull item descriptions and prices from a parts catalog, including table arrays, column indices, named ranges, and basic error handling.

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.

Explore the left-to-right limitation of traditional lookups and adopt index and match for flexible, column-independent results. Use named ranges to simplify formulas returning category, revenue, and profit.

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.

Discover how the offset function uses a reference cell and offset rows and columns to return single or multiple cells, and how it works with sum for dynamic monthly totals.

Explore the indirect function and how it references cells to return dynamic results, including using one R1C1 style referencing, concatenation, and count to build updating totals.

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.

Explore date and time functions in Excel to extract day, month, year, and weekday numbers, convert to names with text, and compose dates and times with date and time functions.

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.

Standardize text in Excel using proper, trim, and clean to fix case and spacing issues. Convert to range, paste values, and apply accounting formatting for pivot tables and clean data.

Master splitting data with text to columns in Excel, using delimited or fixed width methods to separate fields like employee name, job title, department, and salary.

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.

Convert data into Excel tables to streamline formatting, naming, and auto expansion; see how tables auto update charts and pivot tables as you add data.

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.

Select the analysis goal and drag pivot table fields into rows, columns, values, and filters to build flexible reports in Excel, with sums or counts as needed.

Learn to manage subtotals and grand totals in pivot tables, adjusting visibility and placement on rows or columns, and rename labels for clearer analysis.

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.

Format error values and empty cells in a pivot table using the pivot table options. Show a zero for empty cells to improve clarity in charts and reports.

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.

Learn to apply pivot table styles in Excel, use design tab to switch themes, customize headers and banded rows or columns, and create a custom style with a blue outline.

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.

Create a basic cluster column pivot chart showing total gross sales by country, then apply formatting with a chart title, data labels, and clean chart elements.

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.

Insert and customize a timeline slicer to filter date-based data, ensure the date column is formatted as a date, and adjust styling, headers, and layout for an interactive pivot view.

Learn to create a universal slicer that controls multiple pivot charts using report connections. Name pivot charts clearly and link the slicer to all of them to update together.

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.