
This video contains a basic introduction is all about and what is the benefit of using excel , as it is very useful tool for each individual
Fill series plays a vital role in reducing your time and effort to write the values when in series by yourself, you can even drag the formula with a double click
Discover how to create and import custom lists in Excel, store them in memory, and reuse them in new workbooks by using file options and advanced settings.
Master essential Excel shortcuts and the golden key: navigate with Ctrl+Shift+arrow, format with Ctrl B/U, copy with Ctrl R/D, and use Alt to access color fill, alignment, and borders.
Learn how fixed and variable references power Excel formulas, how to auto-fill across ranges, and leverage sum and other functions with tab completion for efficient data analysis.
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.
Generate random numbers in Excel using a formula within a bottom limit of 30 and a top limit of 90, then quickly fill down to populate results.
Copy the range containing formulas. Use paste special values to replace formulas with their current results, preserving intact values without recalculation.
Master mark sheet basics in Excel by using autosum to total marks and fill totals across subjects, then compute percentage by dividing obtained marks by total and formatting as percent.
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.
Learn to rank students using Excel's rank function, compare each score with all classmates, fix absolute references, and ensure compatibility with Excel 2007 and earlier.
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.
Master sort and filter workflows in Excel, including using format painter, inserting columns, and applying top ten items and between percent filters to analyze student batches.
Master subject-wise attendance and marks calculations in Excel using count, count blank, count if, max, min, and average; apply conditional counting, handle absent entries, and learn dragging and replace techniques.
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.
Identify fixed versus movable references in Excel using dollar signs to fix column or row, and see how dragging formulas changes results.
Learn to apply sumif with a mix of absolute and relative references to create dynamic ranges when dragging the formula, keeping criteria fixed while summing by salesperson.
Master sumif with external sheet references by linking data between bonus report and sales report, fixing absolute and relative references, and dragging formulas across sheets for accurate sales totals.
Learn to use Excel if conditions with multiple logics to calculate bonuses from units sold and customer reach, including 1500 units and 55 customer thresholds, and/or logic.
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.
Learn to arrange and clean Excel data by merging and centering cells, correcting headers, and using paste special skip blanks before aging analysis.
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.
Analyze aging of accounts receivable using Excel formulas to calculate month-by-month outstanding balances, extract month and year from dates, handle blanks and errors, and automate six-month aging reports.
Master how to use absolute and relative references in Excel, fix columns with dollar signs, and apply date value comparisons when dragging formulas across rows and columns.
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.
Learn to build a DSUM-based search box in Excel that updates the sum according to criteria across database headings, allowing instant, criteria-driven totals from your data.
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 pivot tables in Excel by building reports from raw data, selecting the data range, inserting a pivot table, and arranging regions, sales representatives, and values in rows and columns.
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.
Explore when to use line graphs, bar charts, and pie charts in pivot chart reporting, and learn to switch to stacked and 100 percent stacked columns for clear monthly analysis.
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.
This course contains the use of artificial intelligence.
Learn Microsoft Excel from the basics through advanced data analysis and reporting. Work through step-by-step demonstrations and downloadable practice workbooks so you can apply each technique to a spreadsheet of your own.
Build Excel Skills You Can Use at Work
Start with Fill Series, keyboard shortcuts, tables, sorting, filtering, and conditional formatting. Clean imported data with Flash Fill, Text to Columns, text functions, and duplicate removal. Practice relative and absolute cell references so your formulas behave as expected when you copy them.
Use IF, AND, OR, COUNTIF, SUMIF, and SUMIFS to calculate results based on business rules. Compare VLOOKUP, XLOOKUP, HLOOKUP, INDEX and MATCH, and DGET so you can choose a lookup method that fits your data. Use named ranges, data validation, and drop-down lists to make workbooks easier to maintain.
Combine files from a folder with Power Query. Use PivotTables, calculated fields, PivotCharts, and slicers to summarize data and build interactive Excel dashboards. Explore advanced filters and macros for repetitive tasks, and see how AI can help with selected Excel workflows.
Put the techniques to work on practical projects. Create a marksheet, prepare a sales and bonus report, reconcile bank book data, and analyze accounts receivable aging. Selected lessons include starter and finished files, so you can try the exercise yourself and compare your results with the demonstration.
This course is for beginners, students, accountants, finance and operations staff, and anyone who uses spreadsheets for reporting. By the end, you’ll be able to take raw data, clean and analyze it, and turn it into a report someone else can use. A recent desktop version of Microsoft Excel is recommended for newer functions such as XLOOKUP and TEXTSPLIT.