MS Excel Complete Basic to Advance Training Course

Explore creating a mock marks sheet in Excel: add a sheet, auto generate 1–50 student IDs, set five subjects, auto fit columns, and assign 30–90 scores in one minute.

Generate random numbers in Excel using the rand between formula within a defined range, e.g., 30 to 90, then fill down quickly by dragging or double-clicking the fill handle.

Learn how to preserve values while removing formulas in Excel by using copy, paste special values, and understanding formula refresh during cell edits.

Learn to calculate total marks and percentage in Excel using auto sum, range selection, and autofill across five subjects, with total 500 and decimal formatting.

Explore how to build if-then criteria in Excel to classify students as pass or fail based on a 50 percent threshold, and modify formulas accordingly.

Master building a complete grade calculation in Excel by using nested if statements to cover all percentage ranges, including proper bracket closure and error checking, for automatic scoring.

Apply the rank function to assign positions by comparing each score with the full class, and fix absolute and relative references (F4) when dragging across all 50 students.

Use conditional formatting in Excel to automatically highlight pass in green and fail in red, with data bars to visually compare student outcomes.

Use freeze panes to keep headings and student names visible while scrolling by selecting the intersection point and choosing top rows or first column from the view tab.

Learn how to convert a data range into a formatted excel table, apply a predefined style, specify headings, adjust colors, and convert the table back to a range.

Learn to use sort and filter in Excel to view morning batches, copy formatting with format painter, insert columns, and filter by percentages with between and top 10 percent.

Explore subject-wise attendance and marks in Excel using count, count blank, count if, max, min, and average. Learn efficient range selection, dragging formulas, and find and replace for absent marks.

Learn to prepare a three-month sales and bonus report by calculating sales amount and profit with absolute references, sorting by month, and inserting monthly and grand totals.

Group data and apply subtotals to generate a month-wise summary with grand totals, using the data tab to sort months and expand or collapse details.

Learn how to hide and unhide rows, group and collapse data with plus minus controls, and apply subtotals for automatic totals in Excel for readable summaries.

Remove duplicates and build a quarterly sales summary in Excel using subtotal and sum if to total units, sales amount, and profit by each salesperson.

Learn how to use absolute and relative cell references in Excel, using dollar signs to fix columns or rows, and practice dragging formulas to see how references change.

Apply sumif with mixed absolute and relative references to fix ranges and criteria, enabling dynamic totals for salesperson records as you drag across columns and down rows.

Master sumif with external sheet references across bonus report and sales report, combining absolute and relative references, using the comma to switch sheets and F4 to fix ranges.

Apply if conditions with multiple logics to calculate sales bonuses in excel. Explore using and, or, and fixed references for criteria based on units sold, customer reach, and bonus percentage.

Learn to perform aged debtors analysis in Excel by classifying outstanding invoices into aging periods, based on credit terms and due dates, and apply a practical example with client data.

Enable the developer tab and record a macro to automate data arrangement in Excel, so you can repeat merging and arranging steps and auto arrange data with a button.

Master quick Excel formatting: apply brown bold size 10 to the first column, highlight the second row, add borders, and use format painter to copy formats.

Learn advanced conditional formatting in Excel by building formulas to highlight payment status across rows: green for cleared and red for unclear, using absolute and relative references.

Apply advanced formatting tricks in Excel by using conditional formatting on headings, naming ranges, and crafting formula-based rules to highlight entire rows efficiently.

Learn to perform aging analysis in Excel by extracting month and year from due dates, and map outstanding balances to monthly aging while handling blanks and errors with formulas.

Learn to use absolute and relative references in Excel, fixing columns while rows move, and build a date-based aging analysis using proper date value handling.

Master vlookup to search a vertical list and return fields like part number, location, warranty, and price using the leftmost column, table array, range lookup, and exact match.

Learn to use named ranges to simplify vlookup with iferror, making blanks for errors, and understand absolute references, plus naming rules and underscores for lookup values and tables.

Master data validation by linking a named range to a dropdown list, restricting entries to valid items, and preventing spelling errors in answers.

Use the developer tab to insert an ActiveX combo box with autocomplete, linked to a list, configure list range and linked cell, then turn off design mode to update formulas.

Learn to use HLOOKUP by flipping a table with transpose, perform horizontal lookups, and retrieve prices, bar numbers, and product names, while automating with fill series and exact-match options.

Learn how to use Excel lookup functions to retrieve data, explain why vlookup requires the data to be on the right, and define fixed lookup and movable result vectors.

Learn to build an invoicing system in Excel using vlookup and index match, with dynamic column indices, named headings, and approximate lookups for discounts and totals.

Demonstrate using sum if, sumifs, and IFS to calculate total sales by product and by sales representative from a dataset, and illustrate removing duplicates for a clean summary.

Create a dynamic DSUM search box to sum sales data by criteria, selecting the database and field by headings, so the total updates instantly as you filter.

Learn to use advanced filters to extract and copy results from a named database, create a search box to filter by criteria, and automate refresh with macros and a button.

Learn to create and customize pivot tables in Excel, turning raw data into a virtual summary report by dragging fields such as regions, salespersons, and sales amounts.

Learn to build month-by-region sales reports in Excel with pivot tables, grouping by month and year and using design options for clear, actionable summaries.

Create calculated fields in pivot tables to compute profit per product, by subtracting cost of goods sold from sales, then refresh and adjust the data source.

Compare line, bar, and pie charts to visualize trends, comparisons, and contributions, then apply pivot charts to switch between clustered, stacked, and 100 percent stacked layouts for clear monthly analysis.

Master dashboard reporting in Excel by combining pivot tables and charts to analyze sales by month and by sales rep, with a timeline and slicers for regional and product filtering.

Automate cheque printing by linking Excel data with Word templates via mail merge, macros, and Visual Basic, to streamline payroll and reduce errors.

Explore how to create and use hyperlinks in Excel to connect to external sites, other files, or internal sheet references, including defining names for ranges and formatting hyperlinks.

Learn to create and manage hyperlinks to external files or web pages in Excel, linking to items like bankbook reconciliation and last month's salaries, while keeping file paths stable.

Split a list of full names into first and last names using text to columns with space as delimiter, then concatenate with a space and paste values.

Use Excel Flash Fill to merge first and last names into a full name, with auto-suggested fills and autofill options that save a lot of time.