Advance Excel Mastery Beginner to Advanced Skills in Excel

Learn how to implement a nested if formula in Excel to automate grades from a marking scheme, handling multiple thresholds and bracket closures, with debugging tips.

Freeze panes to lock header rows and the first column at their intersection, keeping headings and names visible as you scroll, using the View tab and your current selection.

Create a sales and bonus report in Excel by calculating profit from units sold at 275, then total by month (February and March) and grand total using manual totalling.

Master advanced Excel techniques by hiding and unhiding rows, grouping and collapsing data with plus minus signs, and applying subtotals and auto totals for clear, expandable summaries.

Remove subtotals and duplicates in one step, then build a unique salespeople list and sum units sold, sales amount, and profit with a sum if function across three months.

Learn to perform aged debtors analysis in Excel by calculating due dates from credit terms and aging receivables into periods, including handling outstanding balances and practical reporting.

Format a sheet quickly by applying brown color, bold text, and size 10; highlight headers with borders, remove underline, and use the format painter to copy formats across headings.

apply conditional formatting in Excel to highlight entire rows by status, turning green for cleared and red for unclear, using formulas with fixed columns and heading references.

Build a live aging report in Excel that automatically categorizes open balances by days past (0–30, 31–60, 61–90, 91+) using system date and an if with and, plus absolute/relative references.

Master vlookup to search the leftmost column of a table and return related data from a specified column, with exact and approximate match options.

Name ranges to simplify vlookup formulas and return blanks with if error when lookups fail. Use named ranges for lookup value and table to keep references stable.

Apply data validation in Excel to restrict inputs to a named list, enable a dropdown from the list, and prevent spelling mistakes, making car parts data entry faster and error-free.

Discover how to use the hlookup function by flipping tables with transpose, applying exact-match range lookups to extract part numbers, and building dynamic references in Excel.

Master Excel lookups by using the lookup value, lookup vector, and result vector to retrieve data from the correct columns, and understand left-side constraints and sorting.

Master index and match to identify the lowest bid and return the corresponding vendor name, using exact match and dynamic lookup to overcome traditional lookup limitations.

Master VLOOKUP and INDEX/MATCH to build an invoicing system for Remington Pharmaceuticals, dynamically extracting medicine data from a table, using named headings, and applying approximate matching for discounts.

Master sumif and ifs in excel to calculate total sales by product and sales representative, and explore criteria ranges, duplicates, and the drawbacks of sumif with multiple conditions.

Master advanced filters and macros in Excel by naming databases, applying the copy to another location option, and automating refresh with recorded macros and VBA, including debugging and prompts.

Extract all file names from a folder in Excel with a click using a ChatGPT generated macro; enable the developer tab and run it to list names in a worksheet.