Skip to content
ArumSoft Learning
KO

    Advanced Excel

    Design workbooks that hold up: dynamic arrays and LET, advanced lookups and PivotTables, Power Query and the Data Model, what-if analysis, dashboards, error controls, performance and recorded macros.

    Advanced12 tutorialsabout 3 hoursStart the path →

    What's in this path

    1. Step 1: Workbook Architecture: Structuring Excel ModelsSplit a workbook into input, data, calculation and output sheets, name your assumption cells and document it so someone else can run it.11 min read
    2. Step 2: Dynamic Array Formulas in Excel: FILTER, SORT and UNIQUEWrite one formula that spills a whole list: unique values, sorted results, filtered rows, numbered sequences and top-N lists that update with the data.14 min read
    3. Step 3: Cleaner Formulas with LET in ExcelName the pieces of a long formula with LET so each is calculated once, the formula reads like a sentence, and errors are caught on purpose with IFNA.12 min read
    4. Step 4: Advanced Lookups in Excel: Multiple Criteria and Approximate MatchLook up a value that depends on two columns, read a row-and-column grid, and place numbers into tiers with approximate match.16 min read
    5. Step 5: Advanced PivotTables in ExcelGroup dates into quarters, show values as a percent of the total, add calculated fields, filter to the top N and keep a PivotTable's cache up to date.13 min read
    6. Step 6: Power Query Basics: Combine and Clean Data in ExcelImport a folder of monthly files with Power Query, clean them with applied steps, and refresh the result whenever a new file arrives.15 min read
    7. Step 7: Excel Data Model and Power Pivot BasicsRelate a sales table to a product table in the Data Model, build one PivotTable from both, and write your first DAX measures.14 min read
    8. Step 8: What-If Analysis in Excel: Goal Seek, Scenarios and Data TablesWork backward from a target with Goal Seek, save best and worst cases with Scenario Manager, and compare many inputs at once with data tables.15 min read
    9. Step 9: Designing an Excel DashboardPlan a dashboard around one audience and one decision, pick three to five KPIs, match each chart to its question and remove everything that doesn't help.13 min read
    10. Step 10: Error Checking and Controls in ExcelTrace and evaluate suspicious formulas, add reconciliation checks and an error summary, handle filtered rows correctly and protect a report before you send it.14 min read
    11. Step 11: Making Excel Workbooks Faster and Easier to MaintainFind what makes a workbook slow, replace volatile functions and oversized ranges, use manual calculation safely and leave notes so the next person can maintain it.13 min read
    12. Step 12: How to Record a Macro in ExcelRecord a repeated formatting job as a macro, read the VBA Excel writes, choose relative or absolute references and save and open macro files safely.13 min read