Month 1 – Data Analytics Foundations (Advanced)
Advanced track: Month 1 – Data Analytics Foundations (Advanced)
4 lessonsData Analytics (Advanced Track)Free to enrol
Lessons
01
Week 1: What is Data Analytics?
Video: enrol to watchData analytics is the practice of turning raw records into decisions. The job is less about tools than about asking a question precisely enough that data can answer it.
The four kinds of analysis
- Descriptive: what happened? (revenue fell 8% last quarter)
- Diagnostic: why did it happen? (one region drove the entire drop)
- Predictive: what is likely next? (at this rate we miss target by March)
- Prescriptive: what should we do? (shift budget to the underperforming region)
Most working analysts spend their time on the first two, and that is genuinely valuable.
The typical workflow
1. Define the question with whoever is asking it
2. Collect the data
3. Clean it, which reliably takes longer than expected
4. Analyse
5. Communicate the result
Step 5 fails more often than step 4. A correct answer nobody understands changes nothing.
Tools and where they fit
- Excel for quick analysis and small datasets
- SQL for pulling data out of databases
- Power BI or Tableau for dashboards people revisit
- Python or R once the work outgrows the others
You do not need all of them. Excel plus SQL covers a large share of real analyst work.
A caution worth learning early
Correlation is not causation. Ice-cream sales and drowning rates rise together because both rise in summer, not because one causes the other. Before reporting a relationship, ask what else could explain it.
Practice
Write down a question about data you already have access to, phrased specifically enough that a number could answer it. Then list exactly which fields you would need.
02
Week 2: Excel for Data Analysis
Video: enrol to watchExcel remains the fastest way to interrogate a few thousand rows. These are the functions that do the heavy lifting.
Lookups
VLOOKUP searches the first column of a range and returns a value from another column:
=VLOOKUP(A2, Products!A:C, 3, FALSE)
The FALSE matters - it demands an exact match. Leave it out and Excel does an approximate match and may return something wrong without warning.
XLOOKUP, in newer versions, is cleaner and can look left as well as right:
=XLOOKUP(A2, Products!A:A, Products!C:C)
Conditional calculations
=SUMIF(B:B, "Dubai", C:C) totals column C only for rows where B is Dubai.
=COUNTIF(B:B, "Dubai") counts them.
=SUMIFS and =COUNTIFS take several conditions at once.
PivotTables
Insert > PivotTable. Drag a field to Rows, another to Values, and Excel summarises thousands of rows instantly. Change the Values setting from Sum to Average or Count to re-frame the same data. For exploratory work this beats writing formulas.
Handling errors
=IFERROR(VLOOKUP(...), "not found") replaces ugly #N/A results with something readable. Use it to tidy output, but check the errors first - sometimes an error is telling you the data is wrong.
Sorting and filtering
Turn your range into a Table with Ctrl + T. You get filter buttons, formulas that auto-extend to new rows, and named references that are far easier to read.
Practice
Using a sales sheet, build a PivotTable of revenue by region and month, then write a SUMIFS formula that reproduces one of its figures. They should agree exactly.
03
Week 3: Data Cleaning Techniques
Video: enrol to watchAnalysts routinely spend more time cleaning data than analysing it. Doing it systematically is what separates a reliable result from a plausible-looking one.
Start by profiling
Before fixing anything, understand what is wrong. For each column ask: how many rows are blank? How many distinct values are there? Do the minimum and maximum make sense? A COUNTIF of blanks and a quick PivotTable of distinct values answers all three in minutes.
The common problems
- Duplicates: the same record entered twice, inflating every total
- Inconsistent categories: "UAE", "U.A.E." and "United Arab Emirates" counted as three regions
- Mixed types: numbers stored as text, which refuse to sum
- Stray whitespace: invisible, and it breaks every lookup
- Impossible values: a negative age, a date in 2099
- Missing values, which need a decision rather than a default
Deciding about missing data
There is no universal rule. Dropping rows loses information and can bias the result if the missing data is not random. Filling with an average hides uncertainty. Whichever you choose, write down what you did - an analysis nobody can reconstruct is not trustworthy.
Keep the original
Always clean a copy. Record each transformation, either as Power Query steps or as notes. If someone later asks why your total differs from theirs, the answer lives in those steps.
Validate afterwards
Re-run your profile. Does the row count match expectations? Does the total still roughly match the source? Cleaning that silently drops 40% of rows is worse than no cleaning at all.
Practice
Take a messy export and produce a cleaning log: every problem you found, what you did about it, and the row count before and after.
04
Week 4: Visualisation Basics
Video: enrol to watchA chart's job is to make a number easier to understand, not to decorate a page.
Match the chart to the question
- Comparing categories: horizontal bar chart. Bars beat pies because people compare lengths far more accurately than angles.
- Change over time: line chart, with time running left to right.
- Relationship between two numbers: scatter plot.
- Single headline figure: just show the number, large.
- Parts of a whole: stacked bar, or a pie only if there are two or three slices.
Rules that prevent misleading readers
Start bar chart axes at zero. A bar chart truncated at 90 can make a 2% difference look enormous, and this is the most common way charts mislead, deliberately or not. Line charts measuring change may start elsewhere, but say so clearly.
Sort bars by value rather than alphabetically, unless the categories have a natural order. Sorted bars let the eye find the biggest and smallest instantly.
Label directly where you can, instead of making readers travel to a legend and back.
Reduce the noise
Remove heavy gridlines, 3D effects and background fills. Every pixel that is not data is competing with the data.
Colour carries meaning
Use one colour for everything, and a second colour only to highlight the point you are making. A rainbow chart tells the reader that everything is equally important, which is never true.
Give it a title that states the finding
"Revenue fell 12% in Q3" is more useful than "Revenue by Quarter". The title is your one guaranteed chance to say what the chart shows.
Practice
Take a chart you have seen recently and list three things you would remove from it.