Advanced Microsoft Excel - Take your skills to a new level!

Explore the course workbook as a gamified learning space, mastering formatting, keyboard shortcuts, and reference solutions, while navigating automatic and manual points and surprise buttons.

Color your spreadsheet inputs blue and keep formulas black to improve readability and reduce errors, placing all assumptions at the top and avoiding hard-coded numbers in formulas.

Master formatting best practices in Excel by applying consistent number formatting, mindful borders and fill colors, and clean, intuitive spreadsheets that enhance readability and usability.

Master essential keyboard shortcuts to boost Excel efficiency, focusing on the most useful shortcuts. Expect a slower start, but achieve a 10–15% gain and save 40 hours per year.

Master essential keyboard shortcuts such as ctrl+s, ctrl+c, ctrl+v, ctrl+z, and ctrl+y to save, copy, paste, undo, and redo across Excel, Word, PowerPoint, and more.

Master Excel keyboard shortcuts to insert and delete columns and rows. Use control+spacebar to select a column, shift+spacebar for a row, and control+shift++ to insert; control+- deletes.

Unlock powerful Excel shortcuts by using the Alt key to access the ribbon, toggle grid lines, adjust row height, and apply no fill color with quick keystrokes.

Customize the quick access toolbar by removing redundant items and adding frequent commands (no borders, bottom border, all borders, pivot table) and use Alt+numbers to create shortcuts.

Master efficient navigation and selection in large Excel datasets by using control and shift with the arrow keys to jump to data ends and select ranges.

Learn to master absolute cell references in Excel by locking rows, columns, or entire cells with F4, enabling dynamic formulas as you copy across the workbook.

Master absolute cell references by locking the row or column to build a salary formula from hourly rate and hours, then copy across and down, and explore index match.

Learn how naming ranges in Excel simplifies INDEX MATCH, improves formula readability, and enables quick cross-workbook references using the name box and descriptive names.

Name entire lookup tables and columns to simplify formulas like sumifs and countifs, then edit or delete named ranges with the name manager, preparing you to use index match.

Learn the index and match combination to overcome vlookup limitations, using index and match as two formulas, then combine them for a powerful and flexible Excel lookup.

Learn how index formula returns a value from a table using the array, row, and column; compare it to vlookup and see how match auto-supplies coordinates for tardis unit price.

Learn the match formula to return an item's relative position in a single row or column, use exact match 0, and combine it with index for dynamic lookups.

Master combining index and match in Excel to create a dynamic, exact-match lookup. Separate the formulas first, then merge them for a bulletproof, column- and row-insensitive solution.

Explains how to use index and match together in Excel to pull unit prices and categories from pricing table, with exact-match lookups and relative/absolute references, and includes a case study.

practice the index match formula to consolidate an income statement from multiple years, locking cells and dates to accurately pull revenue, cost of goods sold, and net income.

learn to use min and max formulas in excel to find the lowest and highest values, handle multiple ranges, and apply floor or ceiling concepts for dynamic spreadsheets.

Apply min and max with an if approach to compute taxes in a monthly income statement, ensuring zero tax when earnings before taxes are negative and accurate financial modeling.

Explore how to combine and and or statements with an if statement to power the Excel if formula, using CPA passing scores and house criteria as examples.

Learn to implement and/or logic inside if statements to evaluate multiple criteria, such as a passing score and fee payment, for pass/fail decisions.

Learn to combine the left and len formulas in Excel to dynamically extract names from a string that includes a five-character employee id, using length-based backtracking.

Learn to create a dropdown list in Excel using data validation and use the choose function to build dynamic revenue scenarios (upside, base, downside) for financial forecasting.

Explore the choose function for scenario analysis in Excel, using a drop-down to update revenue across cells for upside, base, and downside scenarios.

Combine min, max, and if to compute year-end bonuses using the three-account and one hundred fifty thousand dollars sales criteria, applying commission and a max of eighty-five hundred dollars.

Explore advanced pivot table techniques by creating a calculated field for gross profit margin (sales minus cost of goods sold, divided by sales) across months and regions.

Learn to build cumulative and running totals in pivot tables, use show values as running total, and display percent of column total, with currency formatting, and create pivot charts.

Create a pivot chart from data to build a dynamic dashboard, linked to a pivot table and using drag-and-drop fields like product, month, and sales; format the axis as currency.

Explore how pivot charts extend pivot tables with refresh, slicers, and dynamic dashboards, including creating combo charts with secondary axes to visualize the gross profit margin.

Record a macro to automate common spreadsheet tasks without writing VBA code. Save the workbook as a macro enabled file and enable the developer tab.

Explore the developer tab in Excel, click Visual Basic to view and edit macros, and see how recording a macro generates VBA code for your workbook.

Record a macro to automatically reformat an accounts receivable ledger from an unformatted Excel report, including text to columns, header setup, date and currency formatting, and a workbook-specific shortcut.

Record and run a macro to automate weekly formatting and shortcuts, then save the workbook and note macros are workbook-specific and cannot be undone.

Protect an entire Excel sheet with password-protected settings to prevent edits. Understand how locking cells, restricting selection, and hiding formulas keep data secure, while still allowing deletion of the sheet.

Learn to protect only specific cells in an Excel sheet by unlocking selected cells, then applying sheet protection to lock the rest, enabling targeted edits.

Protect the workbook to secure its structure, preventing adding, deleting, hiding, renaming, or moving worksheets, while still allowing viewing code and protecting individual sheets. Passwords can be cracked.

practice applying the learned formulas, especially index match, in your Excel spreadsheets to make these techniques second nature, and consider the VBA macros course to automate tasks.