Skip to content
ArumSoft Learning
KO

    Expert Excel

    Plan and build complete Excel solutions: LAMBDA, engineered Power Query, DAX measures, VBA and Office Scripts, Solver and forecasting, interactive dashboards, testing, scale, security and a capstone project.

    Expert12 tutorialsabout 3 hoursStart the path →

    What's in this path

    1. Step 1: Planning an Excel Solution: Requirements and DesignTurn a vague request into a design brief: the decision, the refresh cadence, a workbook map, an assumptions register, an owner and testable acceptance criteria.13 min read
    2. Step 2: Reusable Formulas with LAMBDA and Dynamic ArraysWrite a rule once as a named LAMBDA, apply it to a whole column with MAP, fold values with SCAN and REDUCE, and test it before you rely on it.17 min read
    3. Step 3: Power Query Engineering: Parameters, Staging and RefreshBuild a folder import that survives change: a path parameter, a custom function, a connection-only staging query, a Rejected Rows table and checks you run after every refresh.18 min read
    4. Step 4: Data Modeling and DAX Measures in ExcelDesign a star schema in the Data Model, keep measures in their own table, and write DAX with CALCULATE, DIVIDE and SAMEPERIODLASTYEAR.17 min read
    5. Step 5: Automating Excel with VBAWrite VBA macros by hand: loop over ranges without Select, handle errors, respond to events, and log every run.20 min read
    6. Step 6: Office Scripts and Power Automate for ExcelRecord and write Office Scripts, run them on a schedule with Power Automate, and choose the simplest automation that solves the job.16 min read
    7. Step 7: Solver, Forecasting and Statistics Tools in ExcelFind the best product mix with Solver, test how sensitive it is, project a time series with Forecast Sheet and handle outliers in a trend honestly.18 min read
    8. Step 8: Building Interactive Dashboards in ExcelBuild one dashboard with an executive view and an analyst view, clear controls, exceptions first, drill paths and a clean print layout.14 min read
    9. Step 9: Testing Excel Models and Keeping Audit TrailsAdd a QA sheet that proves no rows were lost, totals tie back to the source and problems are listed, and keep a change log for every release.15 min read
    10. Step 10: Excel Performance at ScaleFind out why a large workbook is slow, fix the costliest causes first, and recognize when the job has outgrown Excel.16 min read
    11. Step 11: Securing and Deploying Excel WorkbooksProtect formulas while inputs stay open, know what Excel passwords really protect, handle macros and external links safely, and release each version with a checklist and a rollback plan.14 min read
    12. Step 12: Excel Capstone Project: Build a Complete SolutionA guided project brief: build a refreshable operations model from five sources, with queries, a star schema, measures, a two-audience dashboard, QA checks, automation and a handoff.15 min read