Data Analyst roadmap

Spreadsheet software like Excel or Google Sheets is where most data careers begin. It lets you organise information in rows and columns, use formulas to calculate values, and build quick charts. For example, you can calculate monthly sales totals using simple formulas and highlight high-performing regions using conditional formatting.

Learning this tool matters because nearly every workplace relies on spreadsheets for daily reporting and quick ad-hoc analysis. It helps you get comfortable handling numbers before moving to heavier programming languages.

• Use formulas like SUM and AVERAGE for quick math
• Apply conditional formatting to spot key trends
• Use VLOOKUP or XLOOKUP to combine separate tables

Pivot tables let you summarise thousands of rows of data in just a few clicks without writing complex formulas. They group information and calculate totals automatically, turning a messy list into a neat summary table. For instance, you can instantly see total sales broken down by product category and region.

This skill is vital for data analysts because raw business data is often huge and messy. Pivot tables let you explore data quickly, answer stakeholder questions on the spot, and spot high-level patterns without getting bogged down in details.

• Drag and drop fields to group data instantly
• Change summary metrics from sums to averages easily
• Add filters to focus on specific dates or regions

Data cleaning is the process of fixing errors, duplicates, and inconsistent formats before you start your analysis. Real-world data is rarely neat; names might have extra spaces, dates might use different formats, and cells might be blank. Imagine trying to count customer orders when some names are lowercase and others are capitalized.

Cleaning matters because bad data leads to wrong answers and poor business decisions. Taking the time to standardise your spreadsheet ensures your reports are accurate and trustworthy.

• Use TRIM to remove accidental extra spaces
• Find and remove exact duplicate rows
• Standardise date formats across every cell

SQL allows you to talk directly to databases to pull out the exact information you need. Instead of scrolling through millions of rows, you use commands like SELECT to choose columns, WHERE to filter by specific conditions, and LIMIT to keep results manageable. For example, you can pull only the orders placed in London over the last week.

Knowing how to query databases is essential because company data lives in secure servers, not spreadsheets. SQL lets you access this data independently and efficiently.

• Use SELECT to pick the columns you want
• Use WHERE to filter rows by specific criteria
• Use ORDER BY to sort your results neatly

Aggregate functions let you summarise large sets of database records into single numbers, like totals or averages. By combining commands like COUNT, SUM, and AVG with GROUP BY, you can crunch thousands of rows instantly. For example, you can calculate the total revenue generated by each product category.

This capability matters because businesses rarely want to see every individual transaction; they need high-level answers to monitor performance and spot trends over time.

• Use COUNT to find the number of rows
• Use SUM and AVG for totals and means
• Use GROUP BY to categorise your aggregated results

Databases store information across separate tables to keep things organised, such as keeping customer details in one place and orders in another. Joins let you connect these tables back together using a shared identifier, like a customer ID. For example, you can match a list of purchases with the actual names and email addresses of the buyers.

Joins are crucial because complete business answers usually require data from multiple sources. Being able to combine tables unlocks deeper insights.

• Use INNER JOIN to keep only matching records
• Use LEFT JOIN to keep all records from the left table
• Always join on a clear, unique identifier

Window functions perform calculations across a specific set of database rows that are related to the current row. Unlike regular aggregates that collapse your data, window functions keep individual rows while adding extra context. For example, you can calculate a running total of sales over time or rank products by revenue within their category.

These functions are powerful for advanced business analysis, helping you track cumulative progress and compare current performance against previous periods without losing detail.

• Use OVER to define the window of rows
• Use RANK or ROW_NUMBER to order items
• Calculate running totals without losing row details

Descriptive statistics give you a quick summary of your data using metrics like the mean, median, and spread. They help you understand what is typical and where things vary. For example, knowing the median household income gives a better picture than the mean if a few very wealthy people skew the results.

Understanding these measures matters because relying on a single number can be misleading. Knowing your data's spread and spotting outliers keeps your analysis honest and accurate.

• Use mean for balanced data without extreme values
• Use median to avoid the influence of outliers
• Check the spread to see how much data varies

Distributions show how often different values appear in your dataset, helping you see patterns like the classic bell curve. Sampling involves looking at a smaller group of data to make conclusions about a larger population. For example, testing product quality on a small batch of items rather than every single one made.

This knowledge helps you understand uncertainty. Knowing how samples vary prevents you from panicking over minor random dips in daily business metrics.

• Understand normal distribution and the bell curve
• Recognise skewed data patterns
• Know why random sampling reduces bias

An A/B test compares two versions of something to see which performs better. You might show half your website visitors a blue checkout button and the other half a green one, then measure which group clicks more. This helps you separate genuine improvements from random chance.

This matters because businesses make expensive changes every day. Using statistics to validate tests ensures you invest in updates that actually work rather than following gut feelings.

• Define your success metric before starting
• Run tests long enough to gather sufficient data
• Use statistical significance to confirm results

Choosing the right chart helps your audience grasp insights immediately without second-guessing. Bar charts compare categories, line charts show trends over time, and scatter plots reveal relationships between two variables. For example, use a line chart to show monthly signups growing over the past year rather than a confusing table.

Good visual choices matter because poor charts confuse stakeholders and hide key messages. Simple, clean visuals make your findings accessible and persuasive.

• Use bar charts to compare different categories
• Use line charts to track trends over time
• Use simple tables when exact numbers matter most

Business Intelligence tools like Power BI or Tableau let you build interactive dashboards that update automatically with live data. Instead of static PDF reports, stakeholders can click filters to explore regional performance or drill down into specific product lines. For example, a manager can filter a dashboard to view only last month's European sales.

These tools are essential for modern data teams because they empower business leaders to answer their own routine questions and track KPIs in real time.

• Connect dashboards directly to live data sources
• Add interactive filters for date ranges and regions
• Keep layouts clean and uncluttered

Data storytelling is the art of combining your charts, numbers, and plain English into a compelling narrative that drives action. Instead of walking through every spreadsheet tab, you lead with the main insight and your clear recommendation. For example, state upfront that website traffic dropped due to a broken link and suggest fixing it immediately.

This skill matters because data alone rarely changes minds. A clear story bridges the gap between raw numbers and business decisions.

• Lead with your most important insight first
• Keep explanations simple and jargon-free
• End with a clear, actionable recommendation

Python is a flexible programming language used widely by analysts to load, clean, and explore data. You do not need to be a software engineer; learning basic variables, loops, and functions is enough to get started. For example, you can write a short script to read a CSV file and count how many rows it contains.

Learning basic Python helps you handle datasets that are too large for spreadsheets while automating repetitive tasks to make your workflow faster and more reliable.

• Store data in variables and lists
• Write simple loops to repeat tasks
• Import libraries to add extra functionality

Pandas is the core Python library for data analysis, providing flexible data structures called dataframes. It lets you filter, group, and join large tables of data using code instead of mouse clicks. For example, you can load a file with a million rows and filter for orders over one hundred pounds in seconds.

Pandas matters because it bridges the gap between database queries and advanced modelling, making complex data manipulation repeatable and efficient.

• Load CSV and Excel files into dataframes
• Filter rows and select columns easily
• Group data to calculate summary statistics

A portfolio project involves taking a real public dataset, asking a meaningful question, and publishing your analysis for others to see. You might analyse local housing prices or public transport delays using the skills you have learned. For example, you can write up your findings in a clear report with charts.

Building a portfolio matters because it proves your practical abilities to potential employers better than any certificate, showing you can turn raw data into useful answers from scratch.

• Pick a topic you genuinely find interesting
• Document your cleaning and analysis steps clearly
• Publish your code and findings online