How to Add Machine Learning Predictions to Excel with AutoML

Excel is great for working with data you already have. But what if you want to fill a new column with values that cannot be calculated with a simple formula?

Imagine that you work with insurance data. You have information about previous customers, including their age, BMI, smoking status, and the medical charges they generated. Now you receive a spreadsheet with 100 new customers. You have the same information about them, but you do not know their future charges.

In this tutorial, we will use historical Excel data to train a machine learning model. Then we will use the model to estimate charges for new customers and save those predictions as a new column in an Excel file.

We will do everything in MLJAR Studio using the AI Data Analyst. The AI assistant prepares and runs visible Python code, while MLJAR AutoML trains and compares the machine learning models. We will inspect the model report before using its predictions—the human remains in the analysis loop.

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

The business problem

We start with an insurance dataset containing these customer fields:

• age
• sex
• bmi
• children
• smoker
• region
• charges

The charges column is the value we want to predict.

For this tutorial, the data is divided into two Excel files. insurance_historical.xlsx contains 1,238 previous customers whose charges are known. insurance_new_customers.xlsx contains another 100 customers, but its charges column has been removed to represent new records whose outcome is not known yet.

The workflow is:

historical customers → train a model → review performance → new customers → predicted charges
historical customers → train a model → review performance → new customers → predicted charges

This is a regression problem because the target is a numerical amount rather than a category.

Get the data

Download insurance_historical.xlsx and insurance_new_customers.xlsx from datasets-for-start and save them in:

~/Documents/charges/
~/Documents/charges/

The historical workbook supplies examples for learning. The new-customer workbook supplies rows that need predictions. Keeping the two roles separate makes it harder to accidentally train on records that should be treated as new.

Local files and AI models

MLJAR Studio is a desktop application. Python reads these workbooks directly from your disk, and model training, prediction, and Excel export run on your computer. Reading a file is not uploading it to a separate data-analysis service.

The AI model that writes code is a separate part of the workflow. With a cloud provider, relevant context such as your prompt, column names, dataframe shape, and preview values may be sent to that provider. If the data is confidential, connect Studio to a local LLM through Ollama, vLLM, LM Studio, Jan, or llama.cpp instead. The local versus cloud LLM guide explains this distinction in more detail.

The charge predictions do not come from the LLM. They come from the regression model trained locally by MLJAR AutoML.

Load the historical Excel file

Open MLJAR Studio and start a new conversation with the AI Data Analyst. Use Read a File or ask:

Read a file "/home/piotr/Documents/charges/insurance_historical.xlsx"
Read a file "/home/piotr/Documents/charges/insurance_historical.xlsx"

Studio uses pandas to read the workbook and reports:

Shape: (1238, 7)
Columns (7): ['age', 'sex', 'bmi', 'children', 'smoker', 'region', 'charges']
Shape: (1238, 7)
Columns (7): ['age', 'sex', 'bmi', 'children', 'smoker', 'region', 'charges']

Six columns describe each customer. charges is the known outcome that the model will learn to predict.

You do not need to manually replace values such as female, yes, or northwest with numbers. AutoML prepares categorical columns as part of its training workflow.

Train a machine learning model

Now tell the AI Data Analyst what you want to predict:

Train an AutoML model to predict charges
Train an AutoML model to predict charges

Because charges is continuous, Studio correctly identifies the task as regression. MLJAR AutoML prepares the features and evaluates a baseline, Linear model, Decision Tree, Random Forest, XGBoost, Neural Network, and an Ensemble.

The Ensemble produces the best validation result in this run. You do not have to select one algorithm before training; AutoML runs the standard experiments and compares them under the same evaluation setup.

That automation does not remove the need for review. Before using predictions, check the metric, compare the winner with the baseline, consider whether the training data represents the population you will predict, and decide whether the remaining error is acceptable for the intended use.

Check how good the model is

Ask:

Explain the model performance in simple terms
Explain the model performance in simple terms

The report gives us three useful numbers:

RMSE means Root Mean Squared Error. It measures prediction error in the same unit as the target—in this case, US dollars—and gives larger mistakes more weight. The ensemble reduces RMSE by about 62% compared with the simple baseline.

An RMSE of roughly 4,513 USD does not mean every prediction will be wrong by exactly that amount. Some errors will be smaller and some may be much larger. It is a summary of validation performance, not a guarantee for an individual customer.

This is the key difference from an Excel formula. A formula applies rules we define and returns their exact mathematical result. A machine learning model learns statistical patterns from historical examples and returns an estimate.

For real insurance work, predictions should support qualified human review rather than determine pricing, coverage, or customer treatment automatically. A good validation score is only one part of deciding whether a model is appropriate.

Load the new customers

Now read the workbook containing the 100 new customers:

Read a file "/home/piotr/Documents/charges/insurance_new_customers.xlsx"
Read a file "/home/piotr/Documents/charges/insurance_new_customers.xlsx"

Studio reports:

Shape: (100, 6)
Columns (6): ['age', 'sex', 'bmi', 'children', 'smoker', 'region']
Shape: (100, 6)
Columns (6): ['age', 'sex', 'bmi', 'children', 'smoker', 'region']

There is no charges column. That is expected: these are the rows for which we need estimates.

Before predicting, verify that the six input columns match the training features. Column names and meanings matter. A model trained with bmi cannot safely interpret a differently defined column merely because it also contains numbers.

Predict charges for the new customers

Ask:

Use the trained AutoML model to predict charges for the new customers
Use the trained AutoML model to predict charges for the new customers

The model generates one prediction for each row and adds it as predicted_charges. The essential Python operation is:

predicted_customers["predicted_charges"] = automl.predict(new_customers)
predicted_customers["predicted_charges"] = automl.predict(new_customers)

The first predictions include:

The values come from the trained machine learning model—not from an LLM guessing a plausible number. Still, they remain model estimates and should be interpreted together with the validation error and the limits of the training data.

Save the predictions to Excel

Ask:

Save the dataframe to insurance_new_customers_predictions.xlsx
Save the dataframe to insurance_new_customers_predictions.xlsx

Studio writes the 100 customer records, their original six fields, and the new predicted_charges column to:

~/Documents/charges/insurance_new_customers_predictions.xlsx
~/Documents/charges/insurance_new_customers_predictions.xlsx

The two input workbooks remain unchanged. Saving to a new filename preserves the source data and makes the prediction output easy to identify.

Bonus: Compare predicted charges with actual charges

In a real business situation, we would need to wait until the actual charges became available before checking our predictions. For this tutorial, however, we have a small advantage.

The 100 new customers were taken from the end of the original insurance dataset. We removed their charges column before making predictions, but the original values still exist in the source data. This lets us reveal the outcomes only after predicting and measure how well the model performed on those 100 customers.

Download the original insurance.csv file if you do not already have it. In the recorded conversation, it is available at this local path:

/home/piotr/sandbox/datasets-for-start/insurance/insurance.csv
/home/piotr/sandbox/datasets-for-start/insurance/insurance.csv

Ask Studio:

Read a file "/home/piotr/sandbox/datasets-for-start/insurance/insurance.csv"
Read a file "/home/piotr/sandbox/datasets-for-start/insurance/insurance.csv"

The file contains all 1,338 records. The first 1,238 rows supplied the historical training data; the final 100 rows are the customers we predicted earlier.

Now ask:

The last 100 rows from the latest file have the real charges for our new
customers. Compare them with the predicted charges and make a visualization.
The last 100 rows from the latest file have the real charges for our new
customers. Compare them with the predicted charges and make a visualization.

Studio extracts the final 100 actual values, predicts from the six feature columns, calculates evaluation metrics, and creates a predicted-versus-actual chart.

The essential comparison starts with:

from sklearn.metrics import mean_absolute_error, r2_score

actual_new_customers = df.tail(100).reset_index(drop=True)
comparison = actual_new_customers[["charges"]].copy()
comparison["predicted_charges"] = automl.predict(
    actual_new_customers.drop(columns="charges")
)

mae = mean_absolute_error(
    comparison["charges"],
    comparison["predicted_charges"],
)
r2 = r2_score(
    comparison["charges"],
    comparison["predicted_charges"],
)
from sklearn.metrics import mean_absolute_error, r2_score

actual_new_customers = df.tail(100).reset_index(drop=True)
comparison = actual_new_customers[["charges"]].copy()
comparison["predicted_charges"] = automl.predict(
    actual_new_customers.drop(columns="charges")
)

mae = mean_absolute_error(
    comparison["charges"],
    comparison["predicted_charges"],
)
r2 = r2_score(
    comparison["charges"],
    comparison["predicted_charges"],
)

Each point represents one customer. The horizontal axis shows the actual charge, while the vertical axis shows the predicted charge. The dashed diagonal is a perfect prediction. A point closer to that line has a smaller prediction error.

For these 100 held-out customers, the model achieved:

• MAE: 2,245.14 USD
• R²: 0.907

MAE, or Mean Absolute Error, tells us that a prediction differs from the real charge by about 2,245 USD on average across this test set. Unlike RMSE, MAE gives each absolute error equal weight.

An R² of 0.907 means the model explains about 90.7% of the variation in charges within these 100 records. It does not mean that every individual prediction is 90.7% accurate.

We can also inspect individual predictions:

The predictions are not exact, and some customers have larger errors than others. This is expected: the model learned patterns from historical examples and applied those patterns to records it did not see during training.

The separation is important. The model was trained on the first 1,238 customers, while these 100 customers remained outside the training data. Their real charges were revealed only after predictions had been created. If we evaluated the model on rows it had already learned from, the result would look better than its true performance on new data.

This mirrors a real monitoring workflow:

historical data → train model → predict new data → observe real outcomes → compare predictions
historical data → train model → predict new data → observe real outcomes → compare predictions

As new outcomes arrive, repeating this comparison helps a human reviewer see whether the model continues to perform well or needs investigation and retraining.

AI helps build the workflow; Python makes it reproducible

There are two different types of AI involved here.

The AI assistant interprets our requests and writes the Python cells needed to read files, train the model, make predictions, and export the result. MLJAR AutoML then trains conventional machine learning models on the historical rows and selects the best validation result.

Every generated cell stays visible in a normal Jupyter notebook. You can inspect it, edit it, and rerun it when another customer workbook arrives. The conversation can therefore become reusable Python code instead of a one-time chat answer.

The same pattern applies to other numerical prediction tasks:

• house prices,
• sales or demand,
• delivery times,
• operating costs,
• energy use,
• other values for which historical examples and known outcomes are available.

The critical requirement is not merely having a spreadsheet. You need historical rows whose target is known, new rows with compatible input features, and a review process that decides whether the model is accurate and appropriate enough for the business use.

What we did

We loaded 1,238 historical insurance records and used charges as the regression target. MLJAR AutoML trained several models, selected an Ensemble with a validation RMSE of 4,512.61 USD, and showed that it substantially outperformed the baseline.

After reviewing that result, we loaded 100 new customers, confirmed that their six input columns matched the training features, and generated one predicted_charges value per row. Finally, we saved the original fields and predictions to a new Excel workbook.

In the bonus evaluation, we revealed the real outcomes for those 100 held-out customers. The model achieved an MAE of 2,245.14 USD and an R² of 0.907, and the predicted-versus-actual chart made the individual errors visible for human review.

The result is more than a filled spreadsheet. The notebook records how the model was trained, evaluated, applied, and exported, so the workflow can be reviewed and repeated.

Next steps

Read AutoML in Python for beginners for a code-first introduction to regression and model reports. If you are moving from spreadsheets to pandas, Python for Excel users covers filtering, sorting, calculated columns, and Excel export.

You can also turn an Excel workflow into a web app when colleagues need to run a prediction or analysis workflow without editing the notebook directly.

Browse the spreadsheet workflow hub for more guides to cleaning, merging, analyzing, automating, and sharing Excel data.