Advanced Excel with AI

The Advanced Excel with AI course at RexGalaxy Academy is a practical 5-month program for students, working professionals, MIS executives, data analysts, operations teams, finance support teams, HR analysts, reporting analysts, and anyone who wants to build strong spreadsheet and business reporting skills. The course starts with Excel discipline and moves into advanced formulas, lookups, dynamic arrays, data cleaning, tables, validation, pivots, dashboards, Power Query, Power Pivot, DAX basics, MIS analytics, macros, VBA awareness, AI-assisted productivity, visualization, storytelling, and capstone portfolio work.

This course is built around real business reporting practice. Students learn how to structure workbooks professionally, clean messy data, build reliable formulas, summarize large datasets, design dashboards, automate repeatable reporting tasks, and use AI responsibly for formulas, summaries, data cleaning ideas, report explanations, and productivity support. The emphasis is not only on using Excel features, but also on accuracy, documentation, audit-friendly workbooks, and professional reporting habits.

Key learning highlights:

• Build accurate and professional Excel workbooks with clean structure, formatting, formulas, references, and report-ready layouts.

• Master advanced formulas, logical analysis, lookup models, dynamic arrays, reconciliation reports, and error-safe calculations.

• Prepare clean datasets using Excel tables, validation rules, conditional formatting, duplicate handling, and data quality checks.

• Create pivot tables, pivot charts, slicers, dashboards, KPI reports, MIS packs, and presentation-ready business summaries.

• Use Power Query for repeatable data cleaning, imports, transformations, joins, refresh logic, and data pipeline documentation.

• Understand Power Pivot, data model basics, relationships, DAX measures, model-based reporting, and connected business analysis.

• Apply AI tools responsibly for formula support, data cleaning plans, dashboard ideas, insight writing, documentation, and learning support while verifying accuracy.

By the end of this course, students can prepare professional Excel reports, build dashboards, analyze business data, automate repetitive tasks, document reporting processes, and present a portfolio-ready Excel with AI capstone project confidently.

This module builds a strong base in workbook structure, spreadsheet accuracy, navigation, formatting, and professional data habits. Learners understand how to create clean Excel files that are easy to audit, update, print, and share.

Topics covered:

• Excel interface, ribbon, workbook structure, worksheets, cells, ranges, name box, formula bar, status bar, and quick access workflow.

• Workbook discipline including file naming, version control, sheet organization, tab colors, freeze panes, zoom, views, and safe save practices.

• Data entry standards with headings, consistent formats, clean rows, structured columns, no merged data tables, and audit-friendly layouts.

• Cell references including relative, absolute, mixed references, named ranges, formula copying, fill handle behavior, and common reference mistakes.

• Formatting essentials such as number formats, dates, currency, percentages, alignment, borders, styles, themes, and readable presentation habits.

• Page setup, print area, page breaks, headers, footers, scaling, PDF export, and professional report submission checks.

• Basic formulas including SUM, AVERAGE, COUNT, MIN, MAX, ROUND, TODAY, NOW, text joining, and arithmetic rules.

Practical outcome:

• Students can create accurate, readable, and professional Excel workbooks ready for advanced analysis and reporting.

This module develops formula confidence for business problem solving, decision-making, and accurate calculation models. Students learn how to combine functions properly and avoid formula errors in messy datasets.

Topics covered:

• Logical functions including IF, IFS, AND, OR, NOT, nested IF, SWITCH awareness, decision tables, and condition-based calculations.

• Conditional calculations using SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, criteria ranges, and multi-condition summaries.

• Text functions such as LEFT, RIGHT, MID, LEN, TRIM, CLEAN, UPPER, LOWER, PROPER, TEXTBEFORE, TEXTAFTER, and data cleanup logic.

• Date and time formulas including TODAY, EOMONTH, NETWORKDAYS, WORKDAY, YEAR, MONTH, DAY, DATEDIF awareness, and aging reports.

• Math and rounding functions including ROUND, ROUNDUP, ROUNDDOWN, INT, MOD, ABS, CEILING, FLOOR, and financial calculation support.

• Error handling using IFERROR, IFNA, ISBLANK, ISNUMBER, ISTEXT, ISERROR, and user-friendly formula outputs.

• Formula auditing with trace precedents, trace dependents, evaluate formula, watch window, show formulas, and calculation checks.

Practical outcome:

• Students can write reliable formulas that automate business logic and reduce repetitive spreadsheet work.

This module focuses on lookup formulas, matching logic, dynamic arrays, and reconciliation work used in MIS and analysis tasks. Students learn to connect information from multiple sheets and create dynamic reports.

Topics covered:

• Lookup basics including VLOOKUP, HLOOKUP awareness, exact match, approximate match, column index issues, and lookup table preparation.

• Modern lookups including XLOOKUP, XMATCH, INDEX MATCH, left lookup, two-way lookup, wildcard lookup, and fallback messages.

• Dynamic arrays including FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAY awareness, spill ranges, and dashboard-ready outputs.

• Data matching to compare two lists, find missing values, duplicate checks, reconciliation, mismatch reporting, and exception lists.

• Reference functions including ROW, COLUMN, CHOOSE, OFFSET awareness, INDIRECT awareness, and when to avoid volatile formulas.

• Structured references with Excel Tables, readable formulas, automatic expansion, table names, and formula maintenance benefits.

• Multi-condition lookups using combined criteria, helper columns, nested formulas, and clean formula design for complex reports.

Practical outcome:

• Students can match, filter, and summarize data accurately using modern lookup and dynamic array techniques.

This module teaches students how to prepare clean, reliable, and analysis-ready datasets. Learners practice removing errors, standardizing columns, controlling inputs, and building structured Excel tables.

Topics covered:

• Data cleaning process: inspect raw data, remove noise, standardize columns, identify blanks, check duplicates, and document assumptions.

• Excel Tables, table names, filter buttons, total row, structured references, auto expansion, and clean dataset rules.

• Sort and filter, multi-level sorting, custom sorting, text filters, date filters, number filters, and filtered report extraction.

• Remove duplicates with key column selection, duplicate review, safe backup copy, and business risk of deleting records blindly.

• Data validation including dropdown lists, number rules, date rules, custom formulas, input messages, error alerts, and controlled entry forms.

• Conditional formatting for duplicates, blanks, top values, aging, thresholds, icon sets, data bars, and rule management.

• Text cleanup using TRIM, CLEAN, SUBSTITUTE, VALUE, TEXT, flash fill, find and replace, delimiters, and standard naming formats.

Practical outcome:

• Students can transform messy spreadsheets into consistent, reliable, and analysis-ready Excel datasets.

This module teaches fast summarization and interactive reporting using pivot tables, pivot charts, slicers, timelines, and dashboard layouts. Students learn how to convert raw data into management-friendly summaries.

Topics covered:

• Pivot table fundamentals including source data rules, refresh behavior, row labels, column labels, values, filters, and field list workflow.

• Value settings such as sum, count, average, percentage of total, running total, difference, number formatting, and correct aggregation choices.

• Grouping dates by month and quarter, number ranges, custom groups, report categories, and avoiding grouping errors.

• Slicers and timelines for interactive filtering, connecting slicers to multiple pivots, clear filters, and dashboard navigation.

• Pivot charts including column, bar, line, combo, pie awareness, chart filters, labels, legends, and readable visualization choices.

• Dashboard layout with KPI cards, trend charts, category breakdowns, filters, titles, color consistency, and user-friendly design.

• Refresh and source control, expanding data, table-based sources, refresh all, broken sources, and report maintenance habits.

Practical outcome:

• Students can create interactive Excel dashboards that summarize large data clearly and support business decisions.

This module introduces Power Query for repeatable cleaning, combining, and refreshable reporting. Students learn how to reduce manual copy-paste work and create documented data transformation pipelines.

Topics covered:

• Power Query purpose: repeatable cleaning, refreshable reports, step-based transformations, and reducing manual copy-paste work.

• Data imports from Excel files, CSV, text files, folders, web awareness, database connection awareness, and source selection rules.

• Query Editor workflow including applied steps, preview grid, data types, column profiling, rename steps, and transformation planning.

• Cleaning actions including remove rows, remove columns, split columns, merge columns, trim, clean, replace values, fill down, and standardize text.

• Combining data using append queries, merge queries, join types, matching keys, lookup tables, and handling unmatched records.

• Transform columns with extract text, parse dates, unpivot data, pivot data, group by, conditional columns, and custom columns.

• Refresh logic including refresh all, query dependencies, source path issues, privacy levels awareness, and refresh troubleshooting.

Practical outcome:

• Students can build repeatable Excel data pipelines using Power Query for clean and refreshable reporting.

This module introduces model-based reporting for learners who need to connect multiple tables and create more powerful reports. Students understand relationships, measures, and basic DAX thinking.

Topics covered:

• Data model concepts including tables, relationships, primary keys, foreign keys, dimension tables, fact tables, and model-based reporting.

• Relationship basics including one-to-many, filter direction awareness, unique keys, lookup tables, and avoiding many-to-many confusion.

• Power Pivot interface, managing the data model, diagram view, data view, calculated columns, measures, and field organization.

• DAX introduction including calculated columns versus measures, evaluation context awareness, aggregation logic, and reusable business metrics.

• Core DAX functions such as SUM, COUNT, DISTINCTCOUNT, AVERAGE, DIVIDE, CALCULATE awareness, FILTER awareness, and basic KPI creation.

• Time intelligence awareness including monthly trends, year-to-date thinking, date table concept, calendar tables, and period comparison basics.

• Model reporting using pivot tables from data model, slicers across tables, category analysis, customer analysis, and product performance.

Practical outcome:

• Students can analyze connected datasets with Power Pivot and build reports beyond single-sheet Excel summaries.

This module focuses on professional MIS reporting, KPI summaries, variance analysis, and management-ready outputs. Students learn how to explain performance through numbers and structured reports.

Topics covered:

• MIS reporting process: understand requirement, define metrics, collect data, clean data, calculate KPIs, review accuracy, and deliver reports.

• KPI design for sales, revenue, profit, margin, attendance, productivity, inventory, collection, conversion, and operational performance metrics.

• Variance analysis including actual versus target, month-over-month change, year-over-year awareness, percentage change, and exception explanation.

• Trend analysis with moving average awareness, seasonal view, growth patterns, decline indicators, and simple forecasting awareness.

• Segmentation by region, branch, product, customer, channel, employee, department, and category-wise analysis.

• Report layouts including summary page, detailed sheets, charts page, raw data sheet, assumptions sheet, and action notes.

• Executive summaries with key insights, top issues, improvement areas, risks, opportunities, and recommended next steps.

Practical outcome:

• Students can deliver structured MIS reports that explain business performance clearly and professionally.

This module introduces Excel automation possibilities using macros, recorder workflow, and safe VBA concepts. Students learn how automation can reduce repetitive reporting work while still being tested carefully.

Topics covered:

• Automation mindset: identify repetitive work, define inputs and outputs, reduce manual steps, document logic, and test carefully.

• Macro recorder workflow, recorded actions, generated code inspection, assigning buttons, limitations, and avoiding unreliable recorded steps.

• VBA awareness including modules, procedures, variables, ranges, worksheets, loops, conditions, message boxes, and simple code reading.

• Workbook events awareness such as open, change, button click, form controls, and when automated actions should or should not run.

• Automation use cases including formatting reports, cleaning sheets, exporting PDFs, splitting files, refreshing reports, and preparing email drafts awareness.

• Form controls including buttons, dropdown controls, checkboxes awareness, simple input forms, and controlled user interaction.

• Error handling awareness with safe backups, tested macros, comments, limited scope, and recovery from failed automation.

Practical outcome:

• Students understand Excel automation options and can safely build or explain basic macro-based productivity workflows.

This module teaches how to use AI responsibly for Excel productivity, formula support, cleaning plans, summaries, dashboard ideas, and documentation. Students learn to use AI as an assistant while keeping control of accuracy.

Topics covered:

• AI workflow: define the business question, describe the dataset, ask for logic, verify results, and document assumptions clearly.

• Prompting for formulas by explaining columns, sample values, expected output, edge cases, and requesting readable formula alternatives.

• AI for data cleaning to identify messy patterns, generate cleanup rules, suggest Power Query steps, and create validation checklists.

• AI for analysis including KPI ideas, segment suggestions, anomaly checks, trend questions, and possible business interpretations.

• AI for dashboards including layout ideas, chart selection suggestions, KPI card structure, and executive summary templates.

• AI for documentation such as README notes, process steps, report explanations, data dictionary drafts, and presentation talking points.

• Accuracy discipline: verify formulas manually, test sample rows, compare totals, review hallucinations, and never trust outputs blindly.

Practical outcome:

• Students can use AI as a productivity assistant while maintaining strong Excel accuracy and professional responsibility.

This module trains students to convert numbers into clear visual stories using charts, dashboards, summaries, and presentation-ready reporting. The focus is on communication, not just decoration.

Topics covered:

• Visualization principles: choose the right chart, reduce clutter, show comparisons, focus on message, and guide the viewer.

• Chart types including column, bar, line, area, combo, scatter awareness, pie caution, waterfall awareness, and when each chart is useful.

• Chart formatting with titles, labels, axis settings, legends, colors, gridlines, number formats, and consistent dashboard style.

• KPI cards with current value, target, variance, trend arrows awareness, status labels, and management-friendly summary boxes.

• Conditional visuals such as heatmaps, icon indicators, progress bars, threshold highlighting, and exception-focused reporting.

• Story flow including business context, key numbers, trend, breakdown, exceptions, reasons, and recommended action points.

• Dashboard usability: filter placement, slicer naming, spacing, alignment, instructions, refresh note, and print-friendly layout.

Practical outcome:

• Students can communicate data insights clearly using charts, dashboards, and business storytelling techniques.

The final module brings the entire Advanced Excel with AI journey together through a portfolio-grade project. Students prepare a workbook and presentation that can be shown in interviews, demos, counselling sessions, or portfolio reviews.

Topics covered:

• Capstone planning: choose a business case, define users, list requirements, identify data sources, and create a delivery plan.

• Project options such as sales dashboard, HR analytics, inventory tracker, finance MIS, marketing report, operations dashboard, or billing analysis.

• Workbook architecture including raw data, cleaned data, lookup tables, calculations, pivots, dashboard, documentation, and final summary sheets.

• Feature completeness with formulas, validation, tables, Power Query, pivots, charts, slicers, AI notes, and executive insights.

• Quality review including formula checks, totals reconciliation, refresh testing, spelling check, layout alignment, and data protection review.

• Portfolio assets such as final workbook, PDF dashboard, screenshots, data dictionary, process note, insight summary, and project explanation.

• Interview topics covering formulas, lookups, pivots, Power Query, dashboards, data cleaning, MIS reporting, automation, and AI usage discipline.

Practical outcome:

• Students deliver an interview-ready Excel with AI capstone and confidently explain data, logic, dashboard, and business value.