esc
  • WorkflowsPages
  • Case studiesPages
  • Machine Learning FoundationsLearn
  • AI EngineeringLearn
  • AI Guardrails & Red TeamingLearn
  • UI/UXLearn
  • Design SystemsLearn
  • Production System DesignLearn
  • Book a callContact
  • Download ResumeContact
Let’s talk

I’ll build the agent for it.

Machine Learning Foundations / Beginner / 01.48

Excel files as data

Spreadsheets are everywhere, and a lot of real data arrives in one. Learn what is inside an Excel file, what can go wrong, and how to get a clean table out.

15 min readFree

Why spreadsheets matter

Picture a small shop owner. She tracks every sale in a spreadsheet, adds a few notes in the corners, and colours the late orders red. Then she asks you: can machine learning predict next month's sales? You say yes, and she sends you the file. It is an Excel file.

This happens all the time. People who are not programmers keep their data in spreadsheets, because a spreadsheet is easy to open, type into and share. Wikipedia describes a spreadsheet as 'a computer application for computation, organization, analysis and storage of data in tabular form'. Tabular form means rows and columns, which is also the shape most machine learning tools want.

Key words

Spreadsheet
A program, and also the document it makes, that holds data in a grid of cells.
Workbook
One Excel file. It holds one or more sheets.
Sheet
One page of a workbook: a grid of rows and columns. Also called a worksheet.
Cell
One box in the grid, such as C10: column C, row 10.
Formula
A rule in a cell, such as a sum, that works out its value from other cells.
Serial number
A plain number that counts days. Excel stores dates this way.
Tidy table
One header row on top, then one row per example and one column per feature, with nothing else on the sheet.

What an .xlsx file is

A file ending in .xlsx looks like one lump, but it is not. Wikipedia says that Office Open XML 'is a zipped, XML-based file format developed by Microsoft for representing spreadsheets, charts, presentations and word processing documents'. It is a standard: Ecma International standardized the first version as ECMA-376, and ISO and IEC standardized later versions as ISO/IEC 29500.

Two words in that sentence need explaining. ZIP is the same kind of file you get when you compress a folder: several files packed into one. XML is a way of writing data as plain text with labels in angle brackets, like <v>15</v>. So an .xlsx file is a little ZIP folder of text files that follow the standard.

The standard has its own words for this. On Wikipedia's page about the packaging rules, the term package 'corresponds to a ZIP archive' and the term part 'corresponds to a file stored within the ZIP'. Wikipedia adds that ZIP libraries can be used to open these files, as long as the right files are included and the conventions are followed.

Tip

This is why a program can read an .xlsx file without opening Excel. The data is not locked inside a secret format. It is text files in a ZIP, written by a public standard.

A peek inside

Microsoft's guide says: 'The document structure of a SpreadsheetML document consists of the <workbook/> element that contains <sheets/> and <sheet/> elements that reference the worksheets in the workbook. A separate XML file is created for each worksheet.' SpreadsheetML is the name for the spreadsheet part of the standard.

We will not unpack a real Excel file here, because the exact list of parts is long. Instead, here is a tiny stand-in that shows the idea. We write a worksheet in the form Microsoft's own example uses: five cells holding 1 to 5, and a sixth cell holding a sum. We put it in a ZIP kept in memory with Python's zipfile module, then open the ZIP and read the cells back. The file name sheet1.xml is our own choice, and this is not a real Excel file.

peek.py
import io
import zipfile
import xml.etree.ElementTree as ET

# A sheet written the way Microsoft's docs show it: A1 to A5 hold 1 to 5, A6 holds a formula.
sheet = (
    '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData>'
    '<row r="1"><c r="A1"><v>1</v></c></row>'
    '<row r="2"><c r="A2"><v>2</v></c></row>'
    '<row r="3"><c r="A3"><v>3</v></c></row>'
    '<row r="4"><c r="A4"><v>4</v></c></row>'
    '<row r="5"><c r="A5"><v>5</v></c></row>'
    '<row r="6"><c r="A6"><f>SUM(A1:A5)</f><v>15</v></c></row>'
    '</sheetData></worksheet>'
)

# Put it in a ZIP file, kept in memory. The part name sheet1.xml is our own choice.
buffer = io.BytesIO()
with zipfile.ZipFile(buffer, "w", zipfile.ZIP_DEFLATED) as z:
    z.writestr("sheet1.xml", sheet)

# Open the ZIP again, as a program reading a workbook would.
with zipfile.ZipFile(buffer) as z:
    print("files in the ZIP:", z.namelist())
    root = ET.fromstring(z.read("sheet1.xml"))

ns = {"m": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"}
for cell in root.iterfind(".//m:c", ns):
    formula = cell.find("m:f", ns)
    value = cell.find("m:v", ns)
    print(cell.get("r"), "formula:", None if formula is None else formula.text, "| stored value:", value.text)

Output

files in the ZIP: ['sheet1.xml']
A1 formula: None | stored value: 1
A2 formula: None | stored value: 2
A3 formula: None | stored value: 3
A4 formula: None | stored value: 4
A5 formula: None | stored value: 5
A6 formula: SUM(A1:A5) | stored value: 15

Read the last line closely. Cell A6 keeps two things: the formula, SUM(A1:A5), and a value, 15. That matters, and the next part explains why.

Workbooks, sheets and cells

Wikipedia explains that 'several spreadsheets, often known as "worksheets" or simply "sheets", are gathered together to form a "workbook"'. It also says a spreadsheet 'consists of a table of cells arranged into rows and columns', where columns are normally letters and rows are numbers, so one cell can be addressed as 'C10'.

Wikipedia makes one more point that is worth keeping in mind: 'A spreadsheet is essentially just one table, whereas a database is a collection of many tables with machine-readable semantic relationships.' A workbook with three sheets holds three tables, but it does not link them the way a database does. Each sheet is on its own, and any link between them is something you must work out yourself.

Formulas and stored values

A formula is a rule, not an answer. Cell A6 above holds the rule SUM(A1:A5). But a file also needs to remember what the rule gave last time. Microsoft's guide says that the 'CellValue (<v/>) element, stores the cached formula value based on the last time the formula was calculated.' Cached means saved from before, so it can be shown fast.

Python's openpyxl library, which reads and writes .xlsx files, has a switch for this. Its tutorial says that 'data_only controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet.' So the same cell can give you either the rule or the saved answer, depending on one setting.

Tip

For machine learning you almost always want the values, not the formulas. A model cannot learn from the text SUM(A1:A5). It needs the number 15.

Numbers, dates and text

Dates are the biggest surprise. Excel does not store a date as day, month and year. Microsoft says: 'In the 1900 date system, dates are calculated by using January 1, 1900, as a starting point. When you enter a date, it is converted into a serial number that represents the number of days elapsed since January 1, 1900.' It gives an example: 'if you enter July 5, 2011, Excel converts the date to the serial number 40729.'

There is also a second system. In the 1904 date system, the count starts on January 1, 1904, and the same July 5, 2011 becomes 39267. Microsoft says the 1900 system is the default in Excel for Windows, and that earlier versions of Excel for Mac used 1904. Microsoft warns that dates pasted between the two systems without conversion 'vary from the dates that you copied'.

There is one more oddity. Microsoft's page says: 'When Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year, even though it actually was not a leap year.' Excel kept this so that its serial numbers stay compatible, and the page says that the WEEKDAY function returns incorrect values for dates before March 1, 1900. Since most data is newer than that, you rarely meet it.

Here is how to turn a serial number back into a date with Python's datetime module. For dates from 1 March 1900 onward, counting from 30 December 1899 gives the right answer in the 1900 system, which absorbs the leap-year quirk. We check it against Microsoft's two examples, which should both give 5 July 2011.

serial.py
from datetime import date, timedelta

def from_serial(n, system=1900):
    # The 1900 system counts from 30 December 1899 (valid from serial 61, 1 March 1900, onward).
    start = date(1899, 12, 30) if system == 1900 else date(1904, 1, 1)
    return start + timedelta(days=n)

print(from_serial(40729))
print(from_serial(39267, system=1904))

# The same number, read as a date, gives the date. Read as text, it is just a number.
raw = "40729"
print(raw, "->", from_serial(int(raw)).isoformat())

# A code that starts with zeros loses them when it is read as a number.
code = "00123"
print(int(code), repr(code))

Output

2011-07-05
2011-07-05
40729 -> 2011-07-05
123 '00123'

Both lines print 2011-07-05, which matches Microsoft's examples. The last line shows a second trap: leading zeros. A code such as 00123 is really text, a label. If it is read as a number, it becomes 123 and the zeros are gone. Microsoft's help page on importing text files mentions this need too: you may want 'to convert a column of numbers that contains leading zeros to text so you can preserve the leading zeros'.

Quick check

You read a date column from an Excel file and see the number 40729. What most likely happened?

Common traps

Spreadsheets are built for people, and people add things that help the eye but confuse a program. Here are the three layout habits that cause the most trouble.

  • Merged cells

    Merging joins several cells into one big box, often for a title. In the file it is stored as a range. Microsoft's reference describes a mergeCell as 'a single merged cell' with a 'ref' that is the 'range defined by merge cell'. A table whose header or labels are merged no longer has one clear value per cell, so unmerge before you use it.

  • Several tables on one sheet

    Two small tables side by side, or one above the other, feel tidy to a person. A program sees one big grid, with blank gaps in the middle. Wikipedia says a spreadsheet is essentially just one table. Treat each sheet as holding one table, and split the rest onto their own sheets.

  • Notes above the header

    A title, a date or a 'prepared by' line above the column names pushes the real header down. The program then takes the wrong row as the names. Tools let you skip rows or choose the header row, as the last section shows, but the cleanest fix is to delete the notes from the table sheet.

Three more traps are quieter.

Key words

Hidden rows
A row can be hidden on screen yet still be in the file. Microsoft's reference lists 'hidden' as an attribute of a row, so it is a flag, not a deletion. Do not trust a row count from the screen.
Colour as data
Red for late and green for paid is data that lives only in the colour. Formatting, such as 'conditionalFormatting' in Microsoft's table, is described by its own parts, apart from the cell values. Add a real column, such as status, so the meaning is in the values.
Auto-converted values
Excel may change what you type. The next part has a famous example.

That famous example is about genes. A 2016 paper in the journal Genome Biology reports that 'gene symbols such as SEPT2 (Septin 2) and MARCH1 [Membrane-Associated Ring Finger (C3HC4) 1, E3 Ubiquitin Protein Ligase] are converted by default to ‘2-Sep’ and ‘1-Mar’, respectively.' The authors scanned leading genomics journals and found that 'approximately one-fifth of papers with supplementary Excel gene lists contain erroneous gene name conversions.' They say Excel does this 'when used with default settings'.

Tip

A quick safety check: sort the column or look at its unique values. Strange dates in a column of names, or numbers like 2.31E+13 in a column of IDs, show that something was converted.

Getting a clean table out

Now the practical part. Most of the time you do not try to make a model read a messy sheet. You clean the sheet first, then pass on a plain table. Here is a simple routine.

  1. 1

    1. Make a copy

    Never clean your only copy. Work on a copy of the workbook, so the original stays as the owner made it.

  2. 2

    2. One table per sheet

    Move each table onto its own sheet. Delete titles, notes, totals rows and empty gaps, so the sheet holds the table and nothing else.

  3. 3

    3. Unmerge and name the columns

    Unmerge any merged cells. Put a short, plain name at the top of every column, in one header row, with no blank names.

  4. 4

    4. Fix the types

    Make sure each column holds one kind of thing: all numbers, all dates, or all text. Turn colour meanings into real columns. Keep codes with leading zeros as text.

  5. 5

    5. Export to CSV

    Use Save As and choose the CSV type. Microsoft's help page says: 'To export data from Excel to a text file, use the Save As command and change the file type from the drop-down menu.' CSV, the plain comma-separated format from the last lesson, is where the clean table lands.

  6. 6

    6. Check the result

    Open the CSV in a plain text editor. Check that the first line is the header, that dates look like dates and that codes kept their zeros.

Two warnings come from the same Microsoft page. First, a dialog box 'reminding you that only the current worksheet will be saved to the new file'. A CSV holds one table, so a workbook with four sheets needs four exports: 'You can save other worksheets as separate text files by repeating this procedure for each worksheet.' Second, you may see an alert that 'some features might be lost if you save the workbook in a CSV format'. That is expected: formulas, colours and merges do not fit in a plain text file, which is exactly why we cleaned them out first.

Quick check

A workbook has four sheets, and you want all four as CSV files. What do you do?

How Python reads Excel files

Sometimes you cannot or do not want to export by hand. Then a program reads the .xlsx file directly. In Python, the usual tool is the pandas function read_excel. Its documentation says it will 'Read an Excel file into a DataFrame', and that it supports 'xls, xlsx, xlsm, xlsb, odf, ods and odt file extensions read from a local filesystem or URL'. A DataFrame is pandas' word for a table, as the pandas lesson showed.

The documentation adds: 'This function requires an external library depending on the file format.' For .xlsx files that library is openpyxl. The pandas docs list it as an engine, saying 'openpyxl supports newer Excel file formats', and 'Otherwise openpyxl will be used'. Those docs also carry a note that the default for xlsx files will change from openpyxl to calamine in a future version, so pass the engine yourself if you want it fixed. Neither library comes with this lesson's setup, so we do not run them here. They are installed separately, and you only need the ideas.

The settings on read_excel line up with the traps above. Here are three that matter most.

  • sheet_name

    Picks the sheet. The documentation says strings are used for sheet names and integers are zero-indexed sheet positions. Set it to None and you get 'a dictionary containing DataFrames for each sheet', a handy way to meet one table per sheet.

  • header and skiprows

    header is the 'Row (0-indexed) to use for the column labels', and skiprows is the number of lines to skip at the start. Together they handle notes above the header, without changing the file.

  • dtype and parse_dates

    dtype sets the type of each column, and parse_dates asks for date parsing. The docs also say that if you do not want to parse some cells as dates, 'just change their type in Excel to "Text"'. These give you control over dates and over codes with leading zeros.

Tip

Reading the file directly saves a step, but it does not fix the mess. A merged cell is still merged, and a column of mixed types is still mixed. Clean in the sheet first when you can, and read the result second.

Quick check

A sheet has a title in row 1 and a note in row 2, and the real header is in row 3. Which pandas settings handle this without touching the file?

What to remember

Next: JSON. Spreadsheets are for grids, but a lot of data comes in a shape that is not flat, with items inside items. JSON is the format built for that, and it is the next lesson.

Sources

How is this lesson?

Last updated on 16 November 2026

Learn with me

Every lesson is free. I add new ones as I learn. Have a question, or want help with your own work? Book a short call.