Data Analysis with Excel, Python & AI
Go from a vague question to a clear answer: clean and pivot data in a spreadsheet, repeat the analysis in Python with pandas, check an AI assistant's work and write a one-page answer people act on.
- 7lessons
- 4.5 hto read and do
- Beginnerlevel
- 7podcast episodes
- 11checked exercises
Tools you'll use in this course
The lessons open these with a template ready. They work for your own job too, no course needed.
About this course
A practical course for anyone who has to answer questions with data: new analysts, product managers, operations staff and founders. You start with the hardest and most skipped step, turning a vague request into a question, a metric with an exact definition and a dataset. Then you clean a messy export in a spreadsheet, summarise it with pivot tables, chart it so the answer jumps out, and repeat the same analysis in a Python notebook with pandas so it can run again next week.
Every lesson follows one case at Mangrove Market, a fictional grocery chain with 320 stores. The head of stores writes "East city sales are down. Why?" With Tomás, the data analyst, you separate the calendar effect from the real drop, find the nine stores and the delivery change behind it, and catch an AI assistant's confident wrong answer before it reaches a meeting. You finish by writing the one-page answer, with a recommendation, its cost and how you will know it worked.
No SQL or Python is needed to start: every line of code is explained, and the auto-checked exercises run in your browser. Each lesson has a podcast episode, quick checks, flashcards and a practice task with a model answer, and the capstone gives you a portfolio piece: a real question answered end to end.
What you will be able to do
- Turn a vague request into a decision, a precise question, a metric with an exact definition and a plan for the data
- Clean a messy export in a spreadsheet: fix types, remove duplicates, handle blanks, add fields with lookups and log every change
- Summarise data with pivot tables and find which stores, products or days explain a change
- Choose the chart that answers the question, title it with the finding and avoid the charts that mislead
- Repeat the analysis in a Python notebook with pandas, and use an AI assistant to write analysis code while checking every number it gives you
- Write a one-page answer that leads with the conclusion and ends with a recommendation, an owner and a way to measure it
Syllabus
7 stops, about 4.5 h in total. Each stop fills in when you complete the lesson.
Asking a question data can answer
Most analysis that disappoints started with a vague question. This lesson takes a real message, "East city sales are down. Why?", and turns it into a decision, a precise question, a metric with an exact definition, a fair comparison and a dataset, so the work that follows answers what was asked.
Asking data questionsMetric definitionsAnalysis planning
Cleaning data in a spreadsheet
An export almost never arrives ready to analyse. This lesson takes the messy East city sales export from Mangrove Market and turns it into a table you can trust: the raw file kept untouched, every change written down, and the row counts and totals checked before anyone reads a number.
Data cleaningSpreadsheets
Pivot tables and summaries
A clean table of 24,000 rows answers nothing until you summarise it. This lesson teaches you to build pivot tables and SUMIFS summaries, to pick the right summary (sum, count or average), to compare periods fairly, and to find which stores and categories make up a change. In the course story, this is where East city's drop stops being one number and becomes 9 stores and two aisles.
Pivot tablesSpreadsheetsAsking data questions
Charts that answer the question
A good chart answers one question so clearly that a busy person gets it in five seconds. This lesson shows you how to pick the chart from the type of question, write a title that states the finding, make the one line that matters stand out, index groups of different sizes to 100, and spot the tricks that make a chart lie.
ChartsData storytellingSpreadsheetsAsking data questions
The same analysis in Python
A spreadsheet answers a question once. A notebook answers it every week, and anyone can read how. This lesson rebuilds the East city analysis from the last three lessons in Python with pandas, line by line, so you can rerun it on Monday's new file in under a minute.
PythonpandasData cleaningCharts
Analysing with an AI assistant
An AI assistant can read your export, write the pandas code and draw the chart in two minutes. It can also join two files wrongly, average the wrong thing and write a confident paragraph about it. This lesson shows you how to brief an assistant, read what it did, and check its answer before anyone acts on it.
AI-assisted analysisPythonpandasData cleaningAsking data questions
Telling the story
You found the answer. Now one busy person has five minutes to read it and decide. This lesson shows you how to write the one-page answer: the answer first, the evidence under it, and a recommendation with its cost, expected impact, owner and date, plus the caveats and the follow-up that make people trust it.
Data storytellingAsking data questionsCharts
Capstone: Find the stores that lose the most sales to empty shelves
A realistic project with a rubric and a model answer.
Final exam and certificate
10 questions drawn at random, 70% to pass.
Capstone project
Find the stores that lose the most sales to empty shelves
Mangrove Market's head of stores wants to know which stores lose the most sales to out-of-stock items, why, and what to do first. Build a dataset of at least 2,000 rows with one row per store, article and day: store, city, article, category, units sold, price, and whether the item was out of stock at the morning count. You can adapt a public retail dataset or generate one (an AI assistant can help, as in the AI lesson), but plant at least four quality problems in the raw file on purpose: duplicated rows, numbers stored as text, inconsistent store names and blank values that are not zeros. Turn the request into a question, a metric and a plan; clean the data in a spreadsheet with a cleaning log; estimate lost sales (for example the item's normal daily sales on the days it was out of stock) and summarise them with pivot tables; repeat the cleaning and the summary in a pandas notebook and check the two give the same totals; ask an AI assistant for one part of the analysis and record what you checked and what it got wrong; then write the one-page answer for the head of stores with two charts at most.
You will hand in
- An analysis plan: the decision, the question in one sentence, the metric with its exact definition, the comparison and the data checks
- The raw file, the cleaned spreadsheet with pivot tables, and a cleaning log with row counts before and after each step
- A pandas notebook that repeats the cleaning and summary from the raw file and matches the spreadsheet's totals
- A short AI log: the prompt you gave, the code or answer it returned, the checks you ran and what you corrected
- A one-page answer with the conclusion first, at most two charts, the recommendation, its cost and expected impact, and how it will be measured
Check what you know
Already know some of this? Answer 7 quick questions, one from each lesson, and we will mark the lessons you can skim. It takes about 4 minutes and nothing is graded.
Questions
Do I need to know Python or SQL?
No. The course starts in a spreadsheet and introduces Python step by step, explaining every line. The auto-checked exercises use small SQL or JavaScript snippets with most of the code already written, and they run in your browser.
Do I need Excel?
No. Everything in the spreadsheet lessons works in Excel or in Google Sheets, which is free. Where the two differ, the lesson says so.
What do I need for the Python lesson?
A free notebook in your browser, such as Google Colab, or Jupyter installed on your computer. Nothing else.
Can I use AI tools in the course?
Yes, and one lesson teaches how. You will use an assistant to write analysis code and learn the checks that catch its mistakes. Never paste customer personal data into a tool your company has not approved.
Is it free, and is there a certificate?
Yes. Like every Kolom course, the lessons, exercises, exam and certificate are free. The certificate needs the final exam; the capstone project adds a reviewed portfolio piece.
Is it really free?
Yes. Every lesson, podcast episode, exercise, quiz, final exam and certificate is free. The labs and their templates for real work are free too. Plus and Premium add every lab challenge, weekly extras and a mentor on top, and they never lock the tools or the courses.
How long does it take?
Each lesson shows an honest time for reading and doing. Most people finish a course in two to four weeks at three or four hours a week.