Microsoft 365 Excel - Advanced

Customize the Excel ribbon by creating or removing tabs and groups, adding commands such as autosum and formula auditing tools like trace precedents and remove arrows.

Customize the quick access toolbar by adding icon commands, rearranging items, and using more commands for features like charts and pivot tables, with reset or import/export across all workbooks.

Navigate to file, open options, and customize general and formulas settings; configure mini toolbar, live previews, fonts, views, sheets, and default themes, plus manual versus automatic calculation and error checks.

Explore how to customize Excel autocorrect options to automatically fix errors, capitalize sentences and days, and replace common misspellings, including adding personalized replacements in the autocorrect dictionary.

Explore customizing save defaults in Excel, including auto save for cloud workbooks, local save control, default save format, auto recover timing and location, and default file paths.

Explore advanced Excel options by adjusting the Excel options dialog, including default input direction and decimal places. Add custom lists and use the fill tool to tailor how data fills.

Explore the most common logical functions in Microsoft 365 Excel, learn to build logical tests, interpret true or false results, and use operators for robust data analysis.

Explore how to use sumif and sumifs to total values based on criteria, using range, criteria, and optional sum range, with named ranges and practical examples.

Learn how the average if function computes the mean for cells meeting criteria, including last year production, with average ifs for multiple conditions, enabling pivot table like analysis in cells.

Learn to use countif to count cells within a range that meet a condition, and apply countifs for multiple criteria in Excel data analysis.

Explore the if function in Excel by learning how to set a logical test, define the true and false results, and apply it to commission scenarios based on sales thresholds.

Explore how the and function in Microsoft 365 Excel evaluates multiple logical tests and returns true only when all conditions are met, for example sales and continuing education credits.

Explore the or function in Excel, a less restrictive alternative to and that returns true when any condition is met, and learn its use with if for bonuses.

Master nested if functions in Excel by nesting if statements and evaluating multiple logical tests in order, using greater than or less than thresholds shown in commission examples.

Master the IFS function to replace nested if statements with a cleaner, readable approach that tests multiple conditions, accounts for every eventuality, and returns the first true result.

Learn to nest and or inside an if function in Excel to evaluate multiple conditions. Apply these techniques to determine an education bonus from sales and cue credits.

Explore maxifs and minifs to extract max and min values under multiple criteria, using max range and min range with criteria ranges, and compare with pivot tables.

Learn to use the Iferror function to control outputs when formulas encounter non critical errors, returning friendly results like not found or no data.

Learn how lookup functions use a unique identifier to retrieve related data from another dataset, with horizontal, vertical, and xlookup options, plus index and match as related techniques.

Master vlookup and hlookup syntax and arguments for reliable data retrieval. Learn how the table array must start with lookup values, use absolute references, and apply exact matches.

Apply hlookup to a health plan data matrix to determine dental coverage with an exact match, returning row-based results; note hlookup is like vlookup but horizontal.

Explore how to use vlookup with true to find approximate matches in numerical ranges, such as varying commission rates based on sales.

Demonstrates using hlookup with an approximate match to map customer ratings to grades from a grading scale, showing how to handle missing exact percentages.

Explore how index retrieves the value at the intersection of a row and column, unlike vlookup's left-to-right constraint. See why index alone is limited and how match enables dynamic lookups.

Use the match function to find a value's position in a range, returning a number for dynamic lookups with index. Learn exact match (0) and practical last-name examples.

Combine index and match to pull data from any table, replacing vlookup and hlookup limitations by matching headers and column names, even when data shifts or columns are inserted.

Compare two lists with vlookup to identify new 2023 products not present in 2022, using exact match and #N/A signals, with optional iferror handling.

Use vlookup to compare two lists and detect missing items by applying isna to return a descriptive result, avoiding iferror and enabling a dynamic yes or no answer.

Explore how text functions in Excel clean, split, and merge data imported from different sources, fix capitalization, trim unwanted characters, and prepare data for use without manual edits.

Master text merging in Excel with concat, concatenate, and textjoin, learn when to use ranges or individual strings, add delimiters, ignore empty cells, and manage spaces.

Learn to split a single column into multiple columns with Excel's text to columns tool, using comma delimiters for CSV files and previewing results.

Master left, right, and mid functions to extract specific characters from text strings, such as isolating an id, a middle part number, and a rightmost category code.

Learn how to standardize text capitalization in Excel using the UPPER, LOWER, and PROPER functions. Clean up imported data and apply uniform capitalization to names and locations.

Learn to use the Len function to count characters in a text string, verify the length of IDs, and analyze data with conditional formatting and data bars.

learn how the trim function cleans up extraneous spaces in text strings imported from various sources, leaving only single spaces between words.

Explore the substitute function as a formula-based find-and-replace tool that targets text within a string and replaces it with new text, including specific instances like replacing 2013 with 2016.

Explore date and time functions in Excel to perform date and time calculations, such as days between dates and current time, updated on workbook open, plus working days with holidays.

Master the TODAY, NOW, and DAYS functions to show current date and time, convert dates to serial numbers, and calculate days since a given date for dynamic workbooks.

Explore networkdays and yearfrac to calculate remaining work days between current date and year-end, accounting for holidays, and compute years of service from hire date to today.

Use the date value function to convert text dates into serial numbers in Excel. Reference a text-formatted cell or type the date directly, then reuse the serial in other formulas.

Discover how to show formulas in any workbook by using the formulas tab to toggle show formulas or pressing Ctrl+tilde, then view formulas in cells or the formula bar.

Master tracing precedents and trace dependence in Excel to identify cells and formulas feeding totals, and to understand how changes ripple across the workbook.

Use the watch window in Excel to monitor current values of cells across sheets, dockable or floating, and track changes in named totals like Q1, Q4, and grand total.

Use Excel's error checking to locate and fix errors across worksheets, tracing error sources and precedence. Update formulas, copy from above, and ignore non-errors to restore consistency.

Learn to use the scenario manager for what-if analysis to store and switch value sets on a worksheet, back up originals, and generate a scenario summary or pivot table report.

discover how to use goal seek to set a formula’s result to a target by changing one cell, illustrated with loan payment calculations via the pmt function.

Create a data table for what-if analysis to explore loan amount and interest rate, showing monthly payments and updating when years change by row input cell and column input cell.

Explore layered protection in Excel, including cell level locks and hidden values, worksheet protections, workbook structure locks, and file encryption with passwords to control access and edits.

Learn how to encrypt an Excel workbook with a password to open or modify, use the save as tools options to set separate passwords, and manage read-only access.

Protect worksheets to control actions by locking cells and allowing only unlocked cells to be selected. Set editable ranges with passwords so authorized users can modify quarterly values.

Lock and unlock specific cells, hide formulas, and apply sheet protection in Excel using the Format Cells dialog, the Protection tab, and the Ctrl+1 shortcut.

Explore additional protection features in the workbook: mark as final, a digital signature, and domain-based access restrictions to read, change, or print with specific user permissions.

Protect the structure of a workbook to prevent changes to worksheet order and names, using the protect workbook feature with a password; this does not protect sheet content.

Explore macros as the bridge between Excel knowledge and VBA, recording actions to generate VBA code for automation. Learn macro naming rules and how recording yields reusable, replayable automation.

Enable the developer tab to access macro tools, VBA, and security settings; learn the default 'disable VBA macros with notification' option and saving workbooks in xlm format.

Learn to create a basic formatting macro in Excel by recording steps to standardize district reports, name and store the macro (including personal macro workbook), and run it across sheets.

Learn how to run macros in Excel, see how a macro duplicates formatting across worksheets, and understand limitations like no undo, plus how relative referencing and VBA editing affect results.

Assign macros to the quick access toolbar for cross-workbook use via the personal macro workbook, or add a worksheet button to run a macro on that sheet.

learn to create complex macros by recording actions with macro recorder to generate pivot tables and charts, then use relative referencing with offsets starting from cursor in developer tab.

Explore how to view and edit VBA macro code in Excel, navigate the Visual Basic editor, and modify modules in the personal macro workbook, with practical examples.