Excel

In short: Microsoft’s spreadsheet program — besides manual data entry, also relevant for developers, since many systems have to support Excel import/export (.xlsx).

In more detail: Besides formulas and charts, Excel also offers its own scripting language (VBA) for automation. In software development, Excel frequently appears as a data source or destination (e.g. customer data import, reporting export), which is why there are libraries for programmatically reading/writing .xlsx files in almost every programming language.

In Depth

Internal structure

Internally, a modern .xlsx file is actually a ZIP archive of several XML files (cell contents, formatting, formulas each stored separately) — which is why xlsx files can also be opened and edited without Excel itself, as long as a library understands the format (e.g. openpyxl in Python, ExcelJS in Node.js/JS, or Apache POI in Java).

import openpyxl
 
wb = openpyxl.load_workbook("sales.xlsx")
sheet = wb.active
for row in sheet.iter_rows(min_row=2, values_only=True):
    print(row)

Excel as an unavoidable integration interface

For developers, Excel is often an unavoidable integration interface: many business departments deliver or expect data as an Excel spreadsheet, even when databases have long been used internally — for non-technical people, Excel often remains the most familiar tool for viewing and editing data. Typical pitfalls with programmatic import:

• Inconsistent date formats: depending on regional settings, Excel interprets dates differently (day/month/year vs. month/day/year).
• Leading zeros: Excel removes them by default for numeric-looking values (e.g. for postcodes, 01234 → 1234), unless the cell is explicitly formatted as text.
• Formulas instead of raw values: a cell can contain a formula whose computed result has to be read separately — depending on the library and read mode, you get back either the formula itself or the last computed value.
• Merged cells: considerably complicate automated parsing, since the logical table structure is no longer unambiguous.

VBA and modern alternatives

VBA (Visual Basic for Applications) allows workflows to be automated directly within Excel (macros) — historically the standard way for power users to automate repetitive tasks without learning a “real” programming language. VBA is now increasingly being replaced by Power Query (declarative data preparation without classic code) or external scripts (Python, R), which are more robust, easier to version (checkable into Git) and better testable than macros embedded in the Excel file itself.

Excel as an improvised database

In practice, Excel is often repurposed for things it wasn’t built for — as an improvised mini-database for inventory lists, customer data or project plans, maintained by several people emailing the file back and forth. This works for a while, but quickly hits limits: no real concurrency control (two people editing at once, one version overwrites the other), no version history in the sense of version control, and no way to cleanly represent more complex relationships between records, as a relational SQL database would. Many software projects arise exactly from this pain point — a “real” application is meant to replace what was previously laboriously managed in Excel.

See also: Word, XML, PowerPoint