Excel in Real-World Data Analysis: Still the Workhorse

Anthony Kibet — Wed, 19 Aug 2026 08:23:18 +0000

Ask any data analyst what tool they touched first, and there's a good chance the answer is Excel. Despite the rise of Python, R, and cloud-based BI platforms, Excel hasn't gone anywhere. It's still sitting at the center of how businesses actually handle data day to day, and understanding why says a lot about what data analysis really looks like outside of a classroom.

Why Excel Still Matters

Part of it is accessibility. Nearly every business, from a five-person startup to a multinational corporation, has Excel installed somewhere. It doesn't need a server, a coding environment, or a dedicated data team to get value out of it. Someone in finance, marketing, or operations can open a spreadsheet, drop in some numbers, and start finding answers within minutes.

The other part is flexibility. Excel isn't just a place to store data, it's a full workspace for cleaning, exploring, calculating, visualizing, and presenting it, all in one file. That combination is hard to beat for quick, everyday analysis, even in companies that also use heavier tools for larger-scale work.

Real-World Scenarios Where Excel Shows Up

Financial reporting and budgeting. Finance teams build monthly and quarterly reports in Excel constantly, tracking revenue, expenses, and variances against budget. Formulas handle the math, formatting highlights what needs attention, and the same template gets reused month after month.

Sales and performance tracking. Sales teams use Excel to monitor pipeline, commissions, and quota attainment. A regional manager might pull a report from a CRM, drop it into Excel, and use it to break down performance by rep, product line, or region.

Inventory and operations. Small and mid-sized businesses often manage stock levels, reorder points, and supplier data in Excel, especially before they've invested in dedicated inventory software. It's common to see formulas flagging low-stock items automatically.

Data cleaning before deeper analysis. Even in companies that eventually move data into Python or a database, Excel is frequently the first stop. Analysts use it to scan for obvious errors, missing values, and formatting issues before anything gets pushed downstream.

Ad hoc analysis and one-off questions. Not every question needs a full pipeline. When a manager asks "how did this product perform last quarter compared to the one before it," Excel is often the fastest way to get an answer, since the data's usually already sitting in a spreadsheet somewhere.

Formulas and Features That Do the Heavy Lifting

VLOOKUP / XLOOKUP. These are the classic tools for pulling matching data from another table, like grabbing a customer's region based on their ID. XLOOKUP is the newer, more flexible version, handling both directions of lookup and dealing with errors more gracefully than VLOOKUP ever did.

Pivot Tables. Arguably Excel's most powerful feature for analysis. Pivot tables let you summarize thousands of rows into a digestible table in seconds, grouping data by category, calculating totals or averages, and reshaping it without touching a single formula. A sales dataset with 10,000 transactions can become a clean summary of revenue by month and region in just a few clicks.

IF statements and nested logic. Conditional formulas like IF, IFS, and SUMIF/SUMIFS let analysts apply business logic directly into the data. For example, flagging orders as "Late" or "On Time" based on a shipping date, or summing revenue only for a specific product category.

COUNTIF / COUNTIFS. These handle quick counting tasks, like figuring out how many transactions fall into a certain category or how many days a metric stayed above a threshold, without needing a full pivot table.

Conditional Formatting. This turns raw numbers into visual signals, coloring cells red for underperformance or green for hitting a target. It's a small feature, but it dramatically speeds up how quickly someone can scan a report and spot what matters.

Data Validation. Used to control what gets entered into a spreadsheet in the first place, like restricting a column to a dropdown list of approved categories. This matters more than it sounds, since messy input at the source is one of the biggest causes of bad analysis down the line.

Charts and sparklines. Built-in charting tools let analysts turn a table of numbers into a trend line or bar chart without leaving the spreadsheet, which is often enough for a quick internal report or presentation.

How It All Comes Together

A typical real-world Excel workflow might look like this: pull raw data from an internal system, clean it up using IF and data validation to catch inconsistencies, use XLOOKUP to merge in reference data from another sheet, build a pivot table to summarize the numbers by category, apply conditional formatting to highlight outliers, and finish with a chart summarizing the trend for a report or meeting. None of these steps requires advanced technical skill on their own, but combined, they cover a huge share of what "data analysis" actually means in most workplaces.

The Takeaway

Excel doesn't get the same spotlight as newer data tools, but it's still where a massive amount of real-world analysis actually happens. It's not about being the most powerful tool available, it's about being fast, familiar, and flexible enough to handle the everyday questions businesses need answered. Learning it well, formulas, pivot tables, and all, isn't just a stepping stone to "real" data analysis. For a lot of roles, it is the real analysis.

Understanding Data Modelling in Power BI: Joins, Relationships, and Schemas Explained

Anthony Kibet — Mon, 30 Mar 2026 09:19:34 +0000

Data modeling is like building a well-organized library 📚. It helps us connect different pieces of information in a way that makes analysis easy and meaningful. In Power BI, data modeling is the process of defining how tables relate to each other, making sure data flows smoothly for reporting and insights. It involves creating relationships, understanding joins, and designing schemas — all to turn raw data into useful stories.

What is Data Modeling?

Imagine collecting data from different sources: sales data, customer info, products, and more. Data modeling arranges these data pieces in a logical way. Instead of having all data jumbled together, it structures it so you can analyze relationships: who bought what, when, and why? In Power BI, this is achieved through establishing relationships between tables, which allows you to filter, aggregate, and drill down into your data efficiently.

SQL Joins: (Data Combination)

SQL joins are ways to combine data from two or more tables based on related columns. Let's explore the common joins with simple examples:

1. INNER JOIN

What it does: Only shows rows with matching values in both tables.
Example: Find customers who made purchases. Only customers with sales records appear. -

2. LEFT JOIN (or LEFT OUTER JOIN)

What it does: Shows all rows from the left table, plus matches from the right table. If there's no match, you still see the left table row, but with blank info on the right.
Example: List all customers, even those who didn't buy anything (show blank for sales).

3. RIGHT JOIN (or RIGHT OUTER JOIN)

What it does: Opposite of LEFT JOIN; shows all rows from the right table plus matched rows from the left.

4. FULL OUTER JOIN

What it does: Combines everything from both tables; shows all matches and non-matches from both sides.

5. LEFT ANTI JOIN

What it does: Shows rows from the left table that do not have a match in the right.
Example: Customers who haven't made any purchase.

6. RIGHT ANTI JOIN

What it does: Rows from the right table with no match in the left.
Example: Sales records with no customer info.

Power BI Relationships: Building Connections 🌐

Unlike SQL joins, Power BI relationships are persistent links between tables that don't change unless you edit them. Here’s what you need to know:

Cardinality: How many related records exist:
- 1:M (One-to-Many): One record in the first table relates to many in the second. (e.g., one customer → many orders)
- M:M (Many-to-Many): Multiple records related to multiple. Requires bridging tables.
- 1:1 (One-to-One): One record relates to only one record.
Active vs Inactive Relationships: Active relationships are used by default when you analyze data, while inactive ones are stored but not automatically used. You can activate an inactive link when needed.
Cross-Filter Direction: How filters flow:
- Single direction: filters go one way.
- Both directions: filters flow both ways, useful for complex models.

Fact and Dimension Tables: The Building Blocks

Fact Tables: Store measurable data, like sales amount, quantity sold. Usually large.
Dimension Tables: Store descriptive info, like customer names, product categories. Usually smaller.

Schemas:

Star Schema: Fact table connected directly to dimension tables. Used for simplicity and fast querying.
Snowflake Schema: Dimension tables normalized into sub-tables, reducing data redundancy.
Flat Table (DLat): All data in one big table, but less efficient and harder to maintain.

Role-Playing Dimensions: Multiple Uses of a Single Dimension

Some dimensions, like Date, can be used to filter sales date, ship date, or invoice date. In Power BI, you relate the same Date table to multiple date fields to reuse.

DEV Community: Anthony Kibet