If you already work with Excel, learning Python does not mean forgetting everything you know and starting again. You already know what you want to do with data: look at the last rows, keep only a few columns, apply filters, sort from highest to lowest, add a calculated column, and save the result as a new workbook.
Python can do the same things. The main difference is how you tell the computer what you want.
In this tutorial, we will open an Excel file in MLJAR Studio and work with it using simple prompts. We will not ask AI to transform the whole spreadsheet in one instruction. Instead, we will work step by step, inspect the result after every operation, and look at the Python that was generated.
This maps each step to something you probably already do in Excel. Scrolling to the bottom becomes tail(). Hiding columns becomes selecting columns by name. AutoFilter becomes a condition. A formula filled down 503 rows becomes one operation on an entire column.
By the end, we will start with 2025-01.xlsx, add a new column with 23% VAT, and save the result as 2025-01-with-vat.xlsx.
You can open the full MLJAR Studio conversation to review every prompt, generated Python cell, and output used in this tutorial.
Get the data
Download the sales data from datasets-for-start and put 2025-01.xlsx in:
~/Documents/sales/
The file contains January 2025 sales data with 503 orders and seven columns:
order_id order_date region product units unit_price revenue
We will use the same dataframe throughout the tutorial and gradually change what we display or add to it.
Start a new AI conversation
Open MLJAR Studio and choose Start a New AI Conversation.
This opens an AI-assisted notebook. You can describe what you want to do in normal language, and Studio generates Python code and runs it.
The conversation is also a real Jupyter notebook. Your prompts are stored as markdown cells, the generated Python is stored as code cells, and the outputs stay with them. You can inspect the code at any time instead of treating the AI response as a black box.
Read the Excel file
Use Read a File, select the Excel workbook, and choose:
/home/piotr/Documents/sales/2025-01.xlsx
Reading is not uploading. MLJAR Studio opens the workbook directly from your disk. The generated Python reads the spreadsheet and performs the computations locally on your computer.
Studio generates Python to read the spreadsheet:
import pandas as pd df = pd.read_excel("/home/piotr/Documents/sales/2025-01.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 first thing we learn is the shape of the data:
Shape: (503, 7) Columns (7): ['order_id', 'order_date', 'region', 'product', 'units', 'unit_price', 'revenue']
In Excel, you would see a worksheet with 503 data rows and seven columns. In pandas, the table is stored in a DataFrame. Here, our dataframe is called df. You can think of it as the table we are currently working with.
Look at the last rows
In Excel, if you want to know what is at the bottom of a table, you scroll down or jump to the last row.
Here we can simply ask:
show the last 5 rows
Studio uses:
df.tail()
The last five rows are orders 499 through 503, all from January 31.
This is a small example, but it introduces an important idea. You do not need to remember tail() before you start. You can describe what you want, see the generated code, and gradually learn the pandas vocabulary while doing real work.
After you have seen df.tail() a few times, it becomes as familiar as scrolling to the bottom of a worksheet.
Show only the columns you need
A spreadsheet can have many columns, but often you only need a few of them for the task you are working on.
Ask:
show only the order_date, product and revenue columns
Studio generates:
df[["order_date", "product", "revenue"]]
The result still has all 503 rows, but only the three requested columns are displayed.
In Excel, you might hide the other columns or copy the three columns you need into another sheet. In pandas, you can select them directly by name.
We have not deleted anything from df. We only asked Python to show a particular view of the data. The original dataframe still contains all seven columns.
Filter rows with two conditions
Now let us do something you would normally use Excel's filter menu for. We want orders from the North region where revenue is greater than 1,000 USD.
Ask:
show orders from the North region with revenue over 1000
Studio generates:
df.loc[ (df["region"] == "North") & (df["revenue"] > 1000) ]
The original dataframe contains 503 rows. This filter returns 44 rows.
The first condition:
df["region"] == "North"
means the same thing as selecting North in Excel's Region filter. The second condition:
df["revenue"] > 1000
means the same thing as choosing Greater Than 1000 for revenue. The & means both conditions need to be true.
Although the Python may look unfamiliar at first, the logic is the same as the filter dialog you already know. We can also inspect the 44 matching rows before moving on.
Sort the filtered rows
Now ask:
sort by revenue, highest first
Studio keeps the previous filter and adds sorting:
df.loc[ (df["region"] == "North") & (df["revenue"] > 1000) ].sort_values( "revenue", ascending=False )
The largest matching orders are now at the top. Two Standing Desk orders have revenue of 2,694.00 USD, and another two have revenue of 2,245.00 USD. The next order is a Monitor Arm sale worth 1,757.80 USD.
These are separate orders with different order_id values. They happen to have the same revenue because the same product and quantity can produce the same order value.
In Excel, you might filter the table and then choose Largest to Smallest. In pandas, that becomes:
.sort_values("revenue", ascending=False)
The short prompt follows the filtering step in the conversation, so the generated code keeps the North-region and revenue conditions and sorts that result. Because the notebook stores the conversation and Python together, you can inspect exactly what was sorted.
Add a calculated column
Now we will do something that should feel familiar to an Excel user. Suppose we want another revenue column that includes 23% VAT.
In Excel, you might enter:
=G2*1.23
and fill that formula down through the entire column.
Here, ask:
add a column called revenue_with_vat that is revenue plus 23% VAT
Studio generates:
df["revenue_with_vat"] = df["revenue"] * 1.23 df.head()
The dataframe now has eight columns instead of seven. For example:
209.30 USD -> 257.439 USD 2694.00 USD -> 3313.620 USD
This is one of the biggest mental shifts when moving from Excel to pandas. In Excel, the formula usually lives in one cell first, and you copy it down the column. In pandas, one statement works on the entire column at once.
There is nothing to drag, no risk that the formula stops halfway through the dataset, and no individual row with an accidentally different formula. You describe the relationship between the columns, and pandas applies it to every row.
The original total revenue is 363,879.30 USD. After adding 23% VAT, the corresponding total is 447,571.54 USD.
Inspect the dataframe like a spreadsheet
You do not have to inspect data only through notebook outputs. MLJAR Studio has a dataframe viewer that opens the current dataframe in a spreadsheet-like table.
Here we can see revenue_with_vat next to the original revenue column and scroll through the full dataset.
This is useful if you are coming from Excel because you can still inspect the data visually. Python performs the transformation, but you do not lose the familiar table view. The difference is that the operations which produced the table are written down in the notebook.
Save the result as a new Excel file
We have changed the dataframe in memory, but the original 2025-01.xlsx file has not been modified.
To create a new workbook, ask:
save the result as 2025-01-with-vat.xlsx
Studio generates:
df.to_excel( "/home/piotr/Documents/sales/2025-01-with-vat.xlsx", index=False )
index=False prevents pandas from adding its own dataframe row numbers as an extra Excel column.
The result is saved as:
/home/piotr/Documents/sales/2025-01-with-vat.xlsx
The original file remains untouched.
Check the new file
Open the sales directory and you can see both workbooks:
We started with 2025-01.xlsx and ended with 2025-01-with-vat.xlsx. The new workbook contains the original seven columns plus revenue_with_vat.
This is similar to Save As in Excel, but the complete transformation is also stored in the notebook. If you need to repeat it next month, you do not have to remember which filters you clicked or which formula you filled down. You can rerun the same Python steps.
Excel actions and their Python equivalents
By this point, we have translated several everyday Excel actions into pandas:
| What you do in Excel | Python |
|---|---|
| Scroll to the bottom | df.tail() |
| Show only a few columns | df[["order_date", "product", "revenue"]] |
| Filter Region = North | df["region"] == "North" |
| Filter Revenue > 1000 | df["revenue"] > 1000 |
| Use two filters together | condition1 & condition2 |
| Sort largest to smallest | .sort_values("revenue", ascending=False) |
Enter =G2*1.23 and fill down | df["revenue_with_vat"] = df["revenue"] * 1.23 |
| Save As | df.to_excel(...) |
You do not need to memorize this table before working with Python. You can start with a task you already understand from Excel, describe it in plain English, and inspect the Python that performs it. Over time, expressions such as df.tail() and sort_values() become familiar.
Why use Python if I already know Excel?
For a small spreadsheet and a one-time change, Excel may be faster. If you only want to sort a table once, opening the Sort dialog is perfectly reasonable.
Python becomes more interesting when the work needs to be repeated.
Imagine receiving a new sales workbook every month. In Excel, you might open the file, apply the same filters, add the same formula, fill it down, check the result, and save another workbook.
In the notebook, those steps are already recorded. Change the input filename and rerun the cells. The same filtering logic, sorting, calculated columns, and export can be applied again without rebuilding the process manually.
That is also why we did not perform the whole task in one AI prompt. The notebook is more useful when the workflow is made of understandable steps. You can inspect each result, change one piece, rerun it, and see exactly how the final spreadsheet was created.
The notebook keeps the conversation and the code
There is another difference from editing a spreadsheet directly. The notebook contains both the question:
show orders from the North region with revenue over 1000
and the Python that implements it:
df.loc[ (df["region"] == "North") & (df["revenue"] > 1000) ]
A month later, you do not have to reverse-engineer a finished workbook to remember what you were trying to do. The question, code, and result are stored together.
You can also edit the generated Python directly. AI helps write the code, but the code is there for you to inspect and change. That makes the notebook useful both for getting an answer and as a record of the analysis.
A note on privacy
MLJAR Studio works with files directly on your computer. The generated Python reads 2025-01.xlsx from your disk and performs these operations locally, so you do not need to upload the workbook somewhere before Python can work with it.
There is a difference between where Python runs and where the AI model runs. If you use a cloud AI provider, the model needs some context to help with the data. Depending on the task, this can include your prompt, column names, dataframe shape, and preview values.
If you want the AI part to stay local as well, MLJAR Studio can connect to local LLMs through Ollama, vLLM, LM Studio, Jan, and llama.cpp. In this setup, you can run the AI model on your own computer or infrastructure, so your prompts, data context, generated code, and results do not need to be sent to a cloud AI provider.
What we did
We started with an Excel workbook containing 503 sales records.
Instead of learning pandas from abstract examples, we used operations familiar to an Excel user. We looked at the last rows, selected columns, filtered the data with two conditions, sorted the result, added a calculated VAT column, and saved everything to a new Excel workbook.
The biggest change in the mental model was the calculated column. In Excel, you would normally write =G2*1.23 and fill the formula down. In pandas, one line applies the calculation to the whole column:
df["revenue_with_vat"] = df["revenue"] * 1.23
That is a useful way to start thinking about Python for spreadsheet work. Instead of editing individual cells, you describe what should happen to the data as a whole.
Because we worked step by step, the notebook shows exactly how we got from the original file to the final one.
Next steps
Now that the basic dataframe operations make sense, continue with Excel formulas in Python. It translates familiar SUM, SUMIF, and COUNTIF formulas using the same sales data.
Try Merge all Excel files in a folder when you are ready to work with all twelve monthly files instead of one.
If you want to go from analysis to something other people can use, see Turn an Excel spreadsheet into a web app.
If the workbook needs preparation before analysis, follow Clean messy Excel data to remove duplicates, handle missing values, parse dates, and standardize categories. You can also browse the spreadsheet workflow hub for concise guides to analysis, transformation, automation, and sharing.