Advance Excel Mastery Beginner to Advanced Skills in Excel

Create a mark sheet in Excel for five subjects and sixty students, name the sheet mark sheet, auto fill student numbers, and complete a one-minute mock assignment with marks 30–90.

Copy the range containing formulas. Use paste special values to replace formulas with their current results, preserving intact values without recalculation.

Learn to use Excel's if-then logic to classify scores as boss or fail with a 50 percent threshold, and copy the formula across cells.

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.

Apply conditional formatting to color code outcomes, green for pass and red, for fail, using highlight cell rules like equals to pass or fail. Use data bars to represent percentages.

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.

Learn to format data with Excel's table styles, convert ranges to tables, manage headers, apply predefined styles, and switch between table and range formatting for clean presentation.

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 grouping and auto totalling in Excel to create expandable summaries with subtotals and a grand total, using the Data tab, and expand-collapse controls for monthly totals.

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.

Automate data arrangement in Excel by recording a macro to capture repetitive steps, then attach it to a form control button for instant execution on new data.

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.

Apply advanced formatting tricks in Excel by using conditional formatting to highlight headings, name ranges, and craft formula-based rules that color-code headings and sections for clearer data visualization.

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.

Master Excel autocomplete with combo boxes using the ActiveX control, linking to a cell, and configuring the list range in design mode for seamless data entry.

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.

Develop an automatic Excel system that retrieves medicine details, quantities, and prices, applies discount bands from the data tab, and calculates net amounts using vlookup or index match.

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.

Master grouping pivot tables by month and region to produce a month-by-month sales report, with drill-down details and formatting options.

Learn to create and customize pivot tables, add calculated fields like gross profit, refresh data sources, and display sales, cost of goods sold, and profit per product.

Create dynamic dashboards in Excel by combining pivot tables and charts, using timelines, slicers, and report connections to analyze sales by month, region, and product.

Learn to create hyperlinks to external files or web pages for data mapping in Excel, save time, and manage linked references with Format Painter.

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.