Data Analytics Using Excel and Power BI Training Course

Participants can build auditable Excel calculations and analysis tables from structured data.

• Relative, absolute and mixed references applied to reusable calculation patterns
• XLOOKUP and INDEX-MATCH retrieve attributes from controlled reference tables
• SUMIFS, COUNTIFS and AVERAGEIFS calculate filtered business measures
• IF, IFS and IFERROR handle classification rules and exceptions
• Construct calculated fields for margin, variance, growth and attainment
• Audit formulas with trace precedents, error checks and reconciliation totals

Participants can summarise, compare and communicate Excel findings using PivotTables and charts.

• PivotTables summarise transactions by period, product, region and customer
• Group date fields into months, quarters and financial reporting periods
• Calculated fields and Show Values As reveal contribution and variance
• Slicers and timelines control management views without changing source records
• Select charts according to comparison, trend, composition and distribution questions
• Forecast Sheet and moving averages estimate short-term demand patterns

Participants can create documented Power Query transformations that replace manual preparation steps.

• Connect Power Query to Excel workbooks, CSV files and folder sources
• Set data types before filtering, merging and calculation steps
• Remove errors, null rows and duplicate records using explicit criteria
• Split, extract and replace text values through transformation commands
• Merge queries using keys and verify matched and unmatched records
• Append monthly files into a consistent transaction table
• Document query steps, source locations and refresh dependencies

Participants can design Power BI data models that support accurate, maintainable reporting.

• Fact and dimension tables separated according to star-schema principles
• Define table grain before creating relationships and calculated measures
• Create one-to-many relationships using unique dimension business keys
• Date tables built for calendar, fiscal period and time-intelligence analysis
• Role-playing dates managed for order, shipment and invoice reporting
• Avoid bidirectional filtering unless a defined reporting requirement justifies it
• Model diagrams reviewed for ambiguous paths and inactive relationships

Participants can write DAX measures that calculate and explain business performance indicators.

• Measures distinguished from calculated columns by evaluation and storage behavior
• SUM, DISTINCTCOUNT and DIVIDE create core performance measures safely
• CALCULATE modifies filter context for controlled business logic
• Use FILTER and ALL to create ratio, share and comparison measures
• Year-to-date, prior-year and rolling-period measures use date-table functions
• Variables improve DAX readability, testing and repeated calculation logic
• Validate measure results against Excel reconciliations and source totals

Participants can design interactive Power BI reports that answer defined management questions.

• Report pages structured around decisions, audiences and priority performance measures
• Choose bar, line, matrix and scatter visuals for analytical purpose
• Cards, KPIs and conditional formatting highlight material exceptions and trends
• Slicers configured for relevant filtering without excessive report complexity
• Drill-through pages support investigation from summary measures to detail
• Tooltips and report-page navigation provide supporting context for readers
• Apply accessible labels, titles and colour choices to visual communication

Participants can test report accuracy and interpret patterns without overstating conclusions.

• Reconcile report totals to source extracts using documented control points
• Compare record counts, distinct keys and null rates after transformations
• Exception reports identify unusual values, missing categories and broken relationships
• Variance analysis separates price, volume, mix and timing effects
• Correlation interpreted without claiming unsupported causal relationships
• State metric definitions, exclusions, refresh dates and known limitations
• Record validation evidence for peer review and reporting sign-off

Participants can prepare Power BI reports for controlled publishing, refresh and user access.

• Power BI Service workspaces organised by development, review and publication status
• Dataset refresh requirements assessed for source access and ownership
• Scheduled refreshes planned around data availability and reporting deadlines
• Row-level security concepts applied to restricted regional or departmental views
• Define report owners, data owners and metric approval responsibilities
• Version control conventions distinguish draft, approved and retired reports
• Usage feedback informs report maintenance and backlog prioritisation

Participants can specify a practical reporting solution for a defined workplace requirement.

• Frame a reporting question using decision, audience, measure and frequency
• Map source files, owners, refresh cycles and data-quality risks
• Draft a star schema with facts, dimensions and relationship assumptions
• Specify KPI formulas, business definitions and validation control totals
• Sketch report pages, visual choices, filters and drill-through paths
• Prioritise quick wins, dependencies, review points and implementation actions
• Present an application plan for trainer and peer challenge