DEV Community

Cover image for How Excel is Used in Real-World Data Analysis
Tom Chege
Tom Chege

Posted on

How Excel is Used in Real-World Data Analysis

#learning #data

Working with real-world data is rarely clean. Unlike textbook examples, datasets often contain inconsistencies like text mixed with numbers, duplicate values, missing fields, and ambiguous identifiers.

Over the past week, I’ve been exploring key Excel concepts such as error handling, aggregation, lookup functions, and data cleaning. Each of these plays a critical role in transforming raw, messy data into reliable insights.

In this article, I’ll focus specifically on error handling which is a foundational skill that helps ensure your formulas remain robust even when your data isn’t.

What are Excel errors and why do they matter?

Excel errors are often seen as problems to eliminate, but they are actually signals. Each error tells you something specific about your data or logic:

  • #DIV/0! → You’re dividing by zero or an empty cell
  • #N/A → A lookup couldn’t find a match
  • #VALUE! → The data type is incorrect (e.g., text instead of a number)
  • #NAME? → Excel doesn’t recognize part of your formula

Ignoring these errors or masking them without understanding their cause can lead to misleading results. Instead, they should be treated as indicators of underlying data issues.

The role of IFERROR

One of the most commonly used tools for handling errors in Excel is the IFERROR function. It allows you to return an alternative result when a formula fails.

Example:

=IFERROR(Bonus/Salary, 0)
Enter fullscreen mode Exit fullscreen mode

This ensures that if Salary is missing or zero, the formula does not break but instead returns 0.

⚠️ While this improves usability, it introduces a new responsibility: ensuring that the fall back value does not distort the meaning of your analysis.

The risk of hiding errors

A common mistake is using IFERROR to suppress errors without considering their implications.

=IFERROR(Salary * 0.1, 0)
Enter fullscreen mode Exit fullscreen mode

If Salary is missing, this formula returns 0, which may incorrectly suggest that the calculated value is genuinely zero.

A more transparent approach would be:

=IF(ISBLANK(Salary), "Missing Salary", Salary * 0.1)
Enter fullscreen mode Exit fullscreen mode

This makes the issue visible and preserves the integrity of your data.

Handling specific errors more intelligently

Not all errors should be treated the same. In many cases, it is better to target specific error types.

For example, when working with lookups:

=IF(ISNA(VLOOKUP(A2, A:B, 2, FALSE)), "Not Found", VLOOKUP(A2, A:B, 2, FALSE))
Enter fullscreen mode Exit fullscreen mode

This distinguishes between a missing value (#N/A) and other potential issues, allowing for more precise handling.

Combining logic and error handling

Robust formulas often combine conditional logic with error handling to cover multiple scenarios.

=IFERROR(
  IF(ISBLANK(A2), "No Input", XLOOKUP(A2, A:A, B:B)),
  "Not Found"
)
Enter fullscreen mode Exit fullscreen mode

This structure:

  • Checks for empty input -- ISBLANK
  • Performs a lookup when valid -- XLOOKUP
  • Returns a clear message if the lookup fails -- "No Input"

Such layered logic makes your formulas more resilient and easier to interpret.

What I have learnt about error handling

  • Treat errors as signals, not just problems
  • Avoid blindly replacing errors with default/placeholder values
  • Use targeted checks such as ISNA or ISBLANK where appropriate
  • Ensure default/placeholder values do not distort analysis
  • Design formulas with real-world data imperfections in mind

Final takeaway:

Errors are feedback. By understanding the types of errors, applying functions like IFERROR thoughtfully, and combining them with logical checks, you can build Excel models that are both reliable and transparent.

Top comments (0)