Handle Missing Values in Excel with Python

Counting missing values sounds like one of the easiest parts of data cleaning. You load a spreadsheet, ask Python how many empty cells there are in each column, and move on.

The problem is that a spreadsheet can represent “we don't know” in several different ways. One person leaves a cell empty, another types N/A, and someone else puts a dash. To us, these values may all mean the same thing. To Python, they do not necessarily mean the same thing.

That can give you a missing-value count that looks completely reasonable but is still wrong.

In this tutorial, we will use Python in MLJAR Studio to count missing values in a customer spreadsheet, find the values the first check missed, convert them into proper nulls, and make a numeric column usable again.

You can open the full MLJAR Studio conversation to inspect every prompt, generated Python cell, and output used in this tutorial.

Get the data

Download the demo file from datasets-for-start and put it in:

~/Documents/customers/
~/Documents/customers/

The file is called customers.xlsx.

It is a sample customer list with several deliberately messy parts. In this tutorial, we will focus only on missing values. The same file is used in the full data-cleaning tutorial, where we also handle duplicates, mixed date formats, and inconsistent country names.

Open the Excel file

Start MLJAR Studio and choose Start a New AI Conversation.

Use the Read a file button at the bottom, choose Read file from computer, and select customers.xlsx. You do not need to type the path yourself.

Reading is not uploading. MLJAR Studio opens customers.xlsx directly from your disk. The generated Python loads the workbook and performs the missing-value analysis locally on your computer.

Studio loads the workbook and shows a preview.

The generated Python looks like this:

import pandas as pd

df = pd.read_excel("/home/piotr/Documents/customers/customers.xlsx")

print(f"Shape: {df.shape}")
columns = df.columns.tolist()
print(f"Columns ({len(columns)}):", columns if len(columns) <= 100 else columns[:100] + ["..."])
df.head()
import pandas as pd

df = pd.read_excel("/home/piotr/Documents/customers/customers.xlsx")

print(f"Shape: {df.shape}")
columns = df.columns.tolist()
print(f"Columns ({len(columns)}):", columns if len(columns) <= 100 else columns[:100] + ["..."])
df.head()

The result tells us:

Shape: (530, 7)
Columns (7): ['customer_id', 'full_name', 'email', 'country', 'signup_date', 'plan', 'monthly_spend']
Shape: (530, 7)
Columns (7): ['customer_id', 'full_name', 'email', 'country', 'signup_date', 'plan', 'monthly_spend']

We have 530 rows and seven columns. Now we can start looking for missing data.

Count the missing values

Begin with the obvious question:

count missing values per column
count missing values per column

Studio generates:

missing_by_column = df.isna().sum().sort_values(ascending=False)
missing_by_column
missing_by_column = df.isna().sum().sort_values(ascending=False)
missing_by_column

The result is:

full_name        14
email            14
monthly_spend    12
customer_id       0
country           0
signup_date       0
plan              0
full_name        14
email            14
monthly_spend    12
customer_id       0
country           0
signup_date       0
plan              0

According to this first check, the file contains 40 missing values: 14 missing names, 14 missing email addresses, and 12 missing monthly spend values.

Nothing about those numbers looks suspicious. They are small enough to feel believable, and the gaps appear in columns where missing information is possible. But the answer is incomplete.

Count them again

The spreadsheet uses more than one way to represent missing information. Tell Python about the other forms we expect:

count missing values per column, treating N/A and - as missing
count missing values per column, treating N/A and - as missing

Studio generates:

missing_tokens = ["N/A", "-"]

missing_by_column = (
    df.isna()
    | df.astype("string").apply(
        lambda col: col.str.strip().str.upper().isin(
            [x.upper() for x in missing_tokens]
        )
    )
).sum().sort_values(ascending=False)

missing_by_column
missing_tokens = ["N/A", "-"]

missing_by_column = (
    df.isna()
    | df.astype("string").apply(
        lambda col: col.str.strip().str.upper().isin(
            [x.upper() for x in missing_tokens]
        )
    )
).sum().sort_values(ascending=False)

missing_by_column

Now the result is:

We now have 60 missing values, not 40. Twenty missing values were present in the spreadsheet but were not included in the first count.

This is why missing-value checks are worth treating as a data problem rather than just a pandas command. Python can count what it recognizes as missing, but it cannot automatically know what every placeholder in your spreadsheet means. A human still needs to inspect the values and decide which tokens actually mean “unknown.”

Why the first count was incomplete

This file represents missing information in three ways. Some cells are empty, some contain N/A, and others contain a single dash.

When pandas reads an Excel file, it recognizes empty cells and several common markers such as N/A as nulls. A dash is different. As far as pandas is concerned, - is simply text, so df.isna() does not count it.

In this file, the difference looks like this:

Those dashes account for 20 missing values, which is one third of all the missing data in the file.

The important lesson is not that a dash is special. Your spreadsheet may use NULL, none, unknown, --, TBD, or another placeholder. Inspecting the values in important columns helps you build a list that reflects the meaning of your own data.

The dash causes another problem

The hidden missing values do more than make our count wrong.

monthly_spend should be numeric, with values such as 0, 19, 49, and 199. But the column also contains six dashes. Text mixed with numbers prevents pandas from treating the entire column as a normal numeric series.

The same six dashes therefore create two problems: they are not counted as missing, and they make arithmetic on monthly_spend unreliable. Let us fix both.

Convert the dashes to nulls

Ask Studio:

convert the - values to nulls and make monthly_spend numeric, then show the total spend
convert the - values to nulls and make monthly_spend numeric, then show the total spend

Studio generates:

df = df.replace(r"^\s*-\s*$", pd.NA, regex=True)

df["monthly_spend"] = pd.to_numeric(
    df["monthly_spend"],
    errors="coerce",
)

total_spend = df["monthly_spend"].sum()
total_spend
df = df.replace(r"^\s*-\s*$", pd.NA, regex=True)

df["monthly_spend"] = pd.to_numeric(
    df["monthly_spend"],
    errors="coerce",
)

total_spend = df["monthly_spend"].sum()
total_spend

The result is:

21013.0
21013.0

First, Python replaces cells containing only a dash with a proper null. The regular expression ^\s*-\s*$ matches a value made up of one dash with optional spaces around it.

That precision matters. We do not want to remove every dash from the spreadsheet: names, email addresses, and other legitimate text can contain hyphens.

Then pd.to_numeric() converts monthly_spend into a numeric column. With errors="coerce", anything that still cannot be interpreted as a number becomes null instead of causing the conversion to fail. The column can now be summed, averaged, grouped, and plotted.

About the total spend

The result is 21013.0, but it is not the final correct total for the complete dataset.

The reason has nothing to do with missing values. The file also contains 30 exact duplicate rows, which we have not removed in this focused tutorial. After removing those duplicates, the total monthly spend is 19709, a difference of 1304.

This is a useful boundary to state clearly: the missing-value representation is now fixed, but the entire dataset is not yet clean. A plausible calculation should be reviewed in the context of the other known data-quality issues before anyone uses it in a report.

For the complete workflow, continue with Clean Messy Excel Data with Python and AI.

Should we fill the missing values?

Now that the placeholders are proper nulls, the next decision is what to do with them. In many cases, the best answer is to leave them alone.

A null says that we do not know the value. Replacing a missing monthly_spend with zero says something different: that the customer pays nothing. Adding a fake email address does not give us a real email address, and dropping every row with one gap can discard otherwise useful customer information.

There may be evidence that supports filling a value. In this spreadsheet, monthly_spend appears linked to plan: Free is 0, Basic is 19, Pro is 49, and Enterprise is 199. We might reconstruct missing spend values from that relationship—but only after checking that the rule holds for every known record and accounting for discounts, legacy prices, or custom contracts.

This is where the human stays in the analysis loop. AI can generate the code and summarize patterns, but you decide whether the business rule is trustworthy enough to change the data. For this tutorial, we keep the 60 missing values as nulls rather than inventing values we cannot verify.

Reuse the notebook

The conversation and generated code are stored in a normal Jupyter notebook. The missing-value checks therefore remain available after you close MLJAR Studio.

When a similar spreadsheet arrives, load the new file and rerun the same checks. If the source system starts using a placeholder such as TBD or --, add it to missing_tokens, review what it means, and run the notebook again.

You can also extend the workflow with duplicate removal, date parsing, category standardization, or summary statistics. Keeping the checks and transformations together makes the process repeatable and reviewable.

A note on privacy

MLJAR Studio works with files directly on your computer. The generated Python reads customers.xlsx from your disk and performs the missing-value checks locally, so you do not need to upload the workbook before Python can work with it.

There is a difference between where Python runs and where the AI model runs. With a cloud AI provider, the model may receive context such as your prompt, column names, dataframe shape, and preview values. For a real customer file containing names or email addresses, consider whether that context is confidential.

If you want the AI part to stay local too, MLJAR Studio can connect to local LLMs through Ollama, vLLM, LM Studio, Jan, and llama.cpp. In that setup, prompts, data context, generated code, and results do not need to be sent to a cloud AI provider.

Summary

We started with a customer spreadsheet where the first missing-value check found 40 gaps. After inspecting the representation more carefully, we discovered dashes used as placeholders, bringing the real total to 60.

We converted those dashes into proper null values and turned monthly_spend into a numeric column that can be analyzed normally. We also kept the resulting total in context: missing-value handling is fixed, but duplicate rows still need their own review.

The most important part was recognizing that “missing” is meaning in the data, not merely an empty Excel cell. Python performs the transformation, while a person reviews what each placeholder means and whether an unknown value should remain unknown.

Next steps

Continue with Remove Duplicate Rows from Excel with Python to address the duplicate records, or follow Clean Messy Excel Data with Python and AI for the complete cleaning workflow.