FormulaCamp
Completed
← All paths
Learning paths

Excel for Data Analysts

Excel for data analysts: clean raw data, join tables with XLOOKUP, use PivotTables, Power Query and DAX, and back your findings with statistics.

29 lessons · about 9.2 h · 13 lessons are free

0 of 29 lessons done

Start the path

Who it is for

Junior analysts, BI-curious professionals and anyone who answers business questions from raw data before it reaches a BI tool.

What you will be able to do

  • Clean and reshape messy data with formulas and Power Query, and refresh it with one click
  • Join tables with XLOOKUP and INDEX + MATCH, and use dynamic arrays like FILTER and UNIQUE
  • Summarize with PivotTables and DAX measures, then pick the right chart for the story
  • Use percentiles, correlation and significance tests so your conclusions hold up

Lessons in this path

  1. 1Filter and SUBTOTALFreelesson15 min
  2. 2Cleaning Datalesson15 min
  3. 3Numbers Stored as Text and Dates from CSVlesson20 min
  4. 4Duplicates and Text to ColumnsFreelesson15 min
  5. 5The VLOOKUP FunctionFreelesson15 min
  6. 6XLOOKUPFreelesson15 min
  7. 7INDEX + MATCHFreelesson15 min
  8. 8Multiple Conditionslesson15 min
  9. 9UNIQUE and SORTlesson15 min
  10. 10FILTERlesson15 min
  11. 11LET, TEXTBEFORE, TEXTAFTERlesson15 min
  12. 12GROUPBY and PIVOTBYlesson25 min
  13. 13PivotTableFreelesson15 min
  14. 14PivotTable with Rows and ColumnsFreelesson15 min
  15. 15PivotTable: Grouping Dates and % of Grand Totallesson20 min
  16. 16Calculated Fields and Report Filterslesson25 min
  17. 17Power Query: What It Is and Why Use ItFreetheory and quiz15 min
  18. 18Power Query: Cleaning an ExportFreelesson25 min
  19. 19Power Query: Append, Merge, and Group Bylesson25 min
  20. 20Power Query: Unpivot and Totalslesson20 min
  21. 21Power Pivot and DAX: The Data ModelFreetheory and quiz15 min
  22. 22First Measures: SUM, COUNTROWS, DIVIDEFreelesson25 min
  23. 23Choosing the Right ChartFreetheory and quiz15 min
  24. 24Scatter, Doughnut and Combo Chartslesson20 min
  25. 25MEDIAN, LARGE, RANKFreelesson15 min
  26. 26Percentiles, Quartiles, and Outlierslesson25 min
  27. 27Spread, Correlation, and Forecastinglesson15 min
  28. 28A/B Tests: Conversion and Significancelesson25 min
  29. 29Project P06: Sales Dashboardproject40 min

Interview-style questions

Typical tasks and questions for this role. Each one is taught in a lesson of the path.

  1. Why would you use XLOOKUP instead of VLOOKUP? Give two concrete reasons.

    Learn it in: XLOOKUP

  2. You get the same messy export every Monday. How do you automate the cleaning?

    Learn it in: Power Query: Cleaning an Export

  3. How do you add a margin percentage to a PivotTable that stays correct when you regroup the data?

    Learn it in: Calculated Fields and Report Filters

  4. How do you find outliers in a column of order values?

    Learn it in: Percentiles, Quartiles, and Outliers

  5. Version B converts 5.2% against 4.8% for A. How do you decide whether the difference is real?

    Learn it in: A/B Tests: Conversion and Significance

Practice

Other paths