Tools of the Data Analyst

The Data Analyst's Toolkit

One of the first questions new analysts ask is: "What tools do I need to learn?" The answer depends on the industry and company size, but there is a core set of tools that appear in almost every data analytics role.

This lesson introduces the most important tools, what they are used for, and how they fit together in an analyst's workflow.

Spreadsheets: The Universal Starting Point

Microsoft Excel and Google Sheets are the most widely used analytical tools in the world. Even organisations with sophisticated data stacks often rely on spreadsheets for quick analysis, sharing results, and financial modelling.

What spreadsheets are used for:

• Organising and cleaning small to medium datasets
• Creating pivot tables to summarise data
• Writing formulas (VLOOKUP, SUMIF, COUNTIFS, IF statements)
• Building simple charts and graphs
• Sharing analysis with non-technical stakeholders

Key skills to develop:

• Pivot tables: Summarise large datasets by grouping, counting, and averaging
• VLOOKUP and INDEX/MATCH: Look up values across tables
• Conditional formatting: Highlight patterns visually
• Data validation: Ensure inputs are clean and consistent

When spreadsheets fall short:

• Datasets with more than 100,000 rows become slow and unreliable
• Complex transformations are hard to reproduce or audit
• No built-in way to connect to live databases

SQL: The Language of Data

SQL (Structured Query Language) is the single most important skill for a data analyst. SQL lets you query databases: filter rows, join tables, calculate aggregates, and extract exactly the data you need.

Almost all business data lives in databases. Learning SQL gives you direct access to it.

-- Find the top 5 products by revenue in the last 30 days
SELECT
  product_name,
  SUM(amount) AS total_revenue,
  COUNT(*) AS order_count
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY product_name
ORDER BY total_revenue DESC
LIMIT 5;

Core SQL concepts:

• SELECT: Choose which columns to return
• WHERE: Filter rows by condition
• GROUP BY: Aggregate data into groups
• JOIN: Combine data from multiple tables
• ORDER BY: Sort results
• HAVING: Filter after aggregation

Common databases you will query:

• PostgreSQL, MySQL: Relational databases used by most tech companies
• BigQuery: Google's cloud data warehouse, common in analytics teams
• Redshift: Amazon's data warehouse
• SQLite: Lightweight database for local analysis

Python for Data Analytics

While SQL is for querying, Python with the pandas library is for transforming, analysing, and visualising data programmatically.

Python is more powerful than SQL for:

• Complex data cleaning (regex, fuzzy matching, parsing)
• Statistical analysis
• Creating custom visualisations
• Automating repetitive tasks
• Working with unstructured data (text, JSON, APIs)

import pandas as pd

# Load a CSV file
df = pd.read_csv('sales_data.csv')

# Calculate monthly revenue by category
monthly_revenue = df.groupby(['month', 'category'])['revenue'].sum().reset_index()

# Find the top month
best_month = df.groupby('month')['revenue'].sum().idxmax()
print(f"Best month: {best_month}")

Key Python libraries for analysts:

• pandas: Data manipulation and analysis
• NumPy: Numerical computing
• Matplotlib / Seaborn: Data visualisation
• Plotly: Interactive charts
• openpyxl: Reading and writing Excel files

Business Intelligence and Visualisation Tools

Raw numbers are hard to act on. Visualisation tools help you turn data into dashboards that business teams can understand and use daily.

Tableau

One of the most popular BI (Business Intelligence) tools. Drag-and-drop interface for creating dashboards. No coding required. Used heavily in large enterprises.

Power BI

Microsoft's BI tool. Integrates deeply with Excel and Microsoft 365. Very common in corporate environments.

Looker / Metabase

Open-source and cloud-based options. Looker (owned by Google) is popular among tech companies. Metabase is a self-hosted open-source option.

Google Data Studio (Looker Studio)

Free, browser-based dashboarding tool that connects to Google Analytics, Google Sheets, BigQuery, and more. Great for getting started.

How These Tools Fit Together

Here is a typical analytics workflow showing where each tool fits:

Raw Data (Database)
      |
      v
SQL (Extract and query the data you need)
      |
      v
Python / Spreadsheets (Clean and transform)
      |
      v
Tableau / Power BI (Visualise and create dashboards)
      |
      v
Reports / Presentations (Communicate to stakeholders)

Which Tool Should You Learn First?

If you are starting from zero:

1. Start with spreadsheets: Build fluency with pivot tables and common formulas. You can do real analysis immediately.
2. Learn SQL next: This unlocks the vast majority of business data and is required in almost every analyst job.
3. Add Python when ready: Start with pandas and matplotlib. This makes you significantly more powerful.
4. Pick up a BI tool: Tableau Public (free) or Google Looker Studio (free) are good starting points.

You do not need to master all of these before getting a job. Strong SQL skills alone can get you into an entry-level analyst role.