To learn data analysis with Excel and Power BI, start with clean spreadsheets, core formulas and pivot tables in Excel, then learn Power Query to clean and combine data, and finally move to Power BI for data models, DAX measures and interactive dashboards. Build two or three real projects along the way so you can show your work.
Quick answer: Excel basics → lookups and pivot tables → Power Query → Power BI data modelling → DAX → dashboards → SQL. Many learners reach a portfolio-ready level within a few months of regular practice.
Why Excel and Power BI?
Spreadsheets are still everywhere in business, and Power BI is widely used for reporting. Together they cover a large share of what junior analysts do day to day: cleaning data, answering questions and presenting results. They are also a gentle entry point before programming languages. If you are planning a career move, read how to become a data analyst in Canada.
The roadmap
| Step | Skills | Practice idea |
|---|---|---|
| 1. Excel foundations | Tables, sorting, filtering, cell references, basic functions | Clean a messy sales list |
| 2. Analysis formulas | IF, SUMIFS, COUNTIFS, XLOOKUP, text and date functions | Monthly summary by region |
| 3. Pivot tables and charts | Grouping, slicers, pivot charts | Interactive sales report |
| 4. Power Query | Import, clean, merge and append data | Combine several monthly files |
| 5. Power BI modelling | Star schema, relationships, date tables | Model sales, products and customers |
| 6. DAX | Measures, CALCULATE, time intelligence | Year-over-year growth measures |
| 7. Dashboards | Layout, visuals, filters, storytelling | One-page executive dashboard |
| 8. SQL | SELECT, JOIN, GROUP BY | Query the same data from a database |
Step 1 to 3: Excel skills that matter
Start by converting ranges into proper tables, which makes formulas and pivots easier. Learn conditional sums and counts, lookups and date functions. Pivot tables are the single most useful analysis tool in Excel: master grouping, calculated fields and slicers.
Step 4: Power Query, the hidden superpower
Power Query lets you record cleaning steps once and refresh them every month. It exists in both Excel and Power BI, so the time you invest pays off twice. Practise removing duplicates, splitting columns, unpivoting and merging tables.
Step 5 to 7: Power BI
In Power BI, the data model is more important than the visuals. Learn how fact and dimension tables relate, why a dedicated date table matters and how filters flow through relationships. Then write DAX measures for totals, ratios and comparisons over time. Finally, design dashboards that answer specific questions instead of displaying every chart possible.
Step 8: add SQL
Most analyst job postings mention SQL. Once you are comfortable with Power Query and modelling, SQL will feel familiar. Follow our SQL beginner’s roadmap, and consider Python later; see learn Python online.
Beginner portfolio projects
- Personal budget dashboard: categorise a year of expenses and visualise trends
- Public data report: use open data from a Canadian government portal and build a dashboard
- Sales performance model: a star schema with products, customers and dates
- HR or survey analysis: clean responses with Power Query and summarise results
For each project, write a short summary of the question, the steps and the insight.
How long does it take?
- Excel foundations and pivots: a few weeks of regular practice
- Power Query and Power BI basics: several more weeks
- DAX and a solid portfolio: a few months in total
These are rough ranges; your pace depends on weekly hours and prior experience.
Self-paced or structured?
Free tutorials can take you far with Excel. Structured courses help when you want feedback on dashboards and a clear path to job-ready projects. Compare formats in bootcamp vs self-paced learning.
Learning checklist
- Can build a clean Excel table and use XLOOKUP and SUMIFS
- Can create pivot tables with slicers
- Can clean and combine files in Power Query
- Can build a star schema model in Power BI
- Can write basic DAX measures, including year-over-year
- Have two or three published projects with write-ups
- Know enough SQL to join and aggregate tables
FAQ
Do I need Excel before Power BI?
It helps. Many Power BI concepts, like Power Query and data types, are easier after Excel experience.
Is Power BI free?
Microsoft offers a free desktop application for building reports. Sharing and collaboration features usually require a paid licence. Check Microsoft’s current plans.
Can I get a job with only Excel and Power BI?
Some reporting and junior analyst roles focus on these tools, but adding SQL widens your options significantly.
Mac or Windows?
Power BI Desktop runs on Windows. Mac users often use a virtual machine or a cloud-based option.
Conclusion
Excel and Power BI are a practical, fast route into data analysis. Learn them in order, focus on clean data and good models, and publish a few projects. Then add SQL to become a well-rounded junior analyst.
