Excel Automation Techniques Training Course

Participants can build formula-driven models that update reliably as data changes.

• Dynamic array functions FILTER, SORT, UNIQUE, and SEQUENCE generate live outputs
• LET function simplifies repeated calculations and improves formula readability
• XLOOKUP and XMATCH handle flexible lookup logic across structured tables
• SUMIFS, COUNTIFS, and AVERAGEIFS automate criteria-based summary calculations
• Spill ranges managed with tables, validation lists, and downstream references
• Formula auditing traces precedents, dependents, errors, and calculation bottlenecks

Participants can create repeatable Power Query processes for imported source data.

• Power Query imports CSV, Excel, folder, and database source data
• Applied Steps record transformations for inspection, editing, and refresh
• Data types assigned early to prevent dates, numbers, and text errors
• Column profiling identifies nulls, duplicates, outliers, and inconsistent values
• Merge queries joins related tables using defined matching keys
• Append queries combines monthly files into a single standardised dataset

Participants can automate recurring file preparation while controlling query failures.

• Folder connectors automate consolidation of recurring files with matching structures
• Parameters separate changing file locations and dates from query logic
• Custom columns apply conditional transformations using M expressions
• Pivot and unpivot operations reshape cross-tab reports into analysis-ready records
• Query dependencies reviewed through lineage, load settings, and refresh order
• Data quality checks flag missing fields, invalid codes, and unexpected row counts

Participants can build refreshable reporting models using related Excel tables.

• Data Model relationships connect fact tables with shared dimension tables
• Star schema design separates transactions, dates, products, customers, and regions
• DAX measures calculate totals, ratios, variances, and period comparisons
• CALCULATE modifies measure context using filters and relationship rules
• PivotTables present refreshable summaries from Data Model measures
• Slicers and timelines control report views without changing source data

Participants can write and run VBA procedures that control Excel objects.

• Visual Basic Editor modules, procedures, projects, and macro security settings
• Microsoft Excel object model: workbooks, worksheets, ranges, and collections
• Sub procedures automate sequenced actions without worksheet formulas
• Variables and data types store values, objects, counters, and dates
• With blocks simplify repeated references to workbook and worksheet objects
• Macro recorder output reviewed, cleaned, and converted into maintainable code

Participants can develop VBA macros that process variable data and handle exceptions.

• If, ElseIf, and Select Case control conditional processing decisions
• For Next loops process rows, columns, worksheets, and collection items
• For Each loops iterate through cells, files, and workbook objects
• Last-row detection handles variable-length datasets without fixed ranges
• On Error routines capture failures and restore application settings
• Debug.Print and breakpoints isolate logic errors during macro testing

Participants can automate common report production and controlled user inputs.

• Macro routines refresh queries, update PivotTables, and apply report formatting
• Workbook paths built safely using file names and folder variables
• SaveAs and ExportAsFixedFormat create controlled workbook and PDF outputs
• Data validation lists constrain user inputs before automated processing
• Worksheet buttons assign macros for repeatable user-triggered reporting tasks
• Workbook events trigger controlled actions when files open or values change

Participants can test, document and control automated Excel workbooks for business use.

• Test cases cover normal inputs, boundary values, blanks, and invalid data
• Reconciliation checks compare automated outputs against approved baseline reports
• Control totals verify row counts, amounts, dates, and source completeness
• VBA comments document assumptions, inputs, outputs, and maintenance requirements
• Workbook documentation records query sources, refresh steps, and macro purposes
• Version control conventions distinguish development, review, approved, and archived files

Participants can scope a controlled Excel automation initiative for their own work.

• Requirements define users, decisions, inputs, outputs, timing, and approval points
• Automation backlog ranks candidate processes by effort, benefit, and control risk
• Ownership matrix assigns data, workbook, review, and maintenance responsibilities
• Deployment checklist covers security, file locations, permissions, and refresh schedules
• User guidance specifies inputs, run steps, exceptions, and support contacts
• Personal application plan identifies one recurring process for staged automation