Skip to content
ArumSoft Learning
KO

    Intermediate Excel

    Clean and structure real data, lock references, combine logic and lookup functions, highlight what matters with conditional formatting, and summarize data with PivotTables and simple dashboards.

    Intermediate12 tutorialsabout 3 hoursStart the path →

    What's in this path

    1. Step 1: Turn Messy Data into a Clean Excel TableGet a range table-ready, convert it to a named Excel Table, add a calculated column and switch on a Total Row.12 min read
    2. Step 2: Relative, Absolute and Mixed References in ExcelControl what changes when you copy a formula: keep row references moving, lock a shared rate with $, and build a grid with mixed references.12 min read
    3. Step 3: Logic Formulas in Excel: IF with AND, OR and IFSTurn business rules into formulas: sort values into tiers with IFS, test several conditions with AND and OR, and catch errors with IFERROR.13 min read
    4. Step 4: Lookup Formulas in Excel: XLOOKUP, VLOOKUP and INDEX/MATCHPull a price or category from a reference table with XLOOKUP, read and write VLOOKUP and INDEX/MATCH, and handle values that aren't in the list.14 min read
    5. Step 5: Text and Date Functions in ExcelClean up names with TRIM, pull parts out of codes, join values into labels, and count the working days left before a deadline.14 min read
    6. Step 6: How to Use Conditional Formatting in ExcelColor cells automatically by rule: flag values over a limit, catch duplicates, highlight top values, add data bars and write your own formula rules.13 min read
    7. Step 7: How to Use Data Validation in ExcelAdd drop-down lists, number and date rules, input messages and error alerts, then protect formula cells so only the inputs can change.13 min read
    8. Step 8: How to Create a PivotTable in ExcelSummarize a list by category, month or region in a few clicks with a PivotTable, then change the calculation, format the numbers and refresh it.13 min read
    9. Step 9: PivotCharts, Slicers and Simple Dashboards in ExcelTurn a PivotTable into a PivotChart, add slicers and a timeline anyone can click to filter, and arrange them into a clean one-page dashboard.13 min read
    10. Step 10: Importing and Cleaning Data in ExcelImport a CSV file without losing data, check that numbers are really numbers, then split, fill, standardize and de-duplicate an export.13 min read
    11. Step 11: Organize and Protect an Excel WorkbookGive each sheet a clear job, freeze headers, name ranges, add notes and comments, and protect formulas while inputs stay editable.12 min read
    12. Step 12: Productivity and Formula Auditing in ExcelSelect blanks and formulas in one step, paste values and formats, copy styles, and trace and evaluate formulas to find out why a number is wrong.13 min read