Office and collaborative tools¶

Exercise office-and-collaborative-tools-annex.pdf · PDF

You have written the inventory of the Tramuntana office in a neat Markdown table: laptops, monitors, licences, prices. Then the manager walks in: "And if we hire two more designers?" In Markdown, you get out the calculator and redo every total by hand. Is there a tool that recalculates by itself?

There is, and it is one of the most used programs on Earth. Every office, from a two-person shop to a hospital, keeps a budget, a list or a timetable in one. Today you will build the inventory of Tramuntana Apps in it, share a sheet with two classmates and edit it at the same time, and find out why your Git repository prefers some files to others.

What is an office suite?¶

An office suite is the set of general-purpose programs every office uses: a word processor (letters, reports), a spreadsheet (tables that calculate) and a presentation program (slides).

You can get one in three ways. They are the same three rows you used in the selection matrix:

Two formats, one trick¶

There are two big families of office files:

• ODF (Open Document Format), the native format of LibreOffice. International standard ISO/IEC 26300, from 2006.
• OOXML (Office Open XML), the native format of Microsoft Office. International standard ISO/IEC 29500, from 2008.

They look very different, but inside they use the same trick: the file is a ZIP archive (a box of compressed files) full of XML files (plain text with tags, a cousin of HTML). You can open the box with unzip:

$ unzip -l data/inventory.ods
  Length      Date    Time    Name
       46  2026-10-05 20:58   mimetype
    13543  2026-10-05 20:58   content.xml
    14614  2026-10-05 20:58   styles.xml
     [...]                    9 files

content.xml holds your cells, styles.xml the fonts and colours. Remember this box: it will matter when the spreadsheet meets Git.

The program people bought the computer for¶

Before 1979, an accountant kept the budget on big sheets of squared paper. Change one number, and every total that depended on it had to be rubbed out with an eraser and recalculated.

Then, on 17 October 1979, Dan Bricklin and Bob Frankston released VisiCalc for the Apple II. You changed one number and every result that depended on it changed at once. It is remembered as the program people bought the computer for.

That is the analogy for this page: a spreadsheet is squared paper where every square can be a calculator, and the calculators are wired to each other.

Anatomy of a spreadsheet¶

Change a number, and every square wired to it recalculates. Never type a result (6600) where a formula (=D2*E2) should be: the typed result is wrong the moment a price changes.

flowchart LR
    D2["<b>D2</b><br>quantity: 6"] -->|"=D2*E2"| G2["<b>G2</b><br>total: 6600"]
    E2["<b>E2</b><br>unit cost: 1100"] -->|"=D2*E2"| G2
    G2 -->|"=IF(...)"| H2["<b>H2</b><br>first year: 6600"]
    H2 -->|"=SUM(H2:H11)"| H13["<b>H13</b><br>TOTAL: 21154"]

Fill down: relative references¶

Type =D2*E2 in G2, then fill down: drag the small square at the corner of the cell down to G11. Click G5 and read its formula: =D5*E5. The references moved with the row. They are relative references: "the cell two to my left", not "cell D2".

Functions that ask a question¶

Text inside a formula goes between quotes. monthly without quotes gives #NAME?; a missing bracket gives Err:508 or Err:509.

The dollar sign: absolute references¶

Put the VAT rate 0.21 in cell L1. In I2 type =H2*(1+L1) and fill down. Now click I3: it says =H3*(1+L2). L2 is empty, so the "price with VAT" equals the price without it.

The reference to the VAT rate must not move. Write it as $L$1: an absolute reference. The dollar sign pins the column ($L), the row ($1), or both. In Calc, the F4 key adds the dollars to the reference under the cursor.

A spreadsheet never tells you it is wrong¶

England, autumn 2020. Positive COVID tests arrived as text files and were loaded into a spreadsheet in the old .xls format, which holds at most 65,536 rows. When the file was full, the rest was silently left out. Between 25 September and 2 October, 15,841 positive cases were not passed on to the people who trace contacts.

Nobody typed a wrong formula. Counting the rows that went in against the rows that came out would have caught it.

So: reach every important total by two roads. In the inventory, hardware plus software must equal the grand total. Write it as a formula in a free cell:

=H14+H15-H13

If it does not give 0, something is wrong. The computer calculated exactly what you asked; it is your job to check you asked the right thing.

Two ways of working together¶

When you and a partner changed the same line in Git, Git stopped and asked you to decide. In a shared online document, you would have seen each other typing.

Shared editing in a browser became ordinary after 2006, when Google bought the small company behind the online word processor Writely and launched Google Docs that October.

Two features make live editing safe:

• Comments: a remark attached to a cell or a sentence, which others can answer without changing the content.
• Version history: the service remembers every change and who made it, so you can see who typed cell C3 and go back to an older version.

Sharing is giving permission¶

Almost every service has three roles:

The principle of least privilege from Unit 5 applies here too: give each person the smallest role they need. The accountant comments, the manager views, the team edits. "Anyone with the link can edit" is the chmod 777 of documents: a link has no name on it.

SUMPRODUCT: a matrix in one formula¶

In the Exercise sheet you rebuild the selection matrix with your team, each person typing one candidate column at the same time. The total of each candidate uses one function:

=SUMPRODUCT($B$2:$B$6;C2:C6)

SUMPRODUCT multiplies the weights by the scores, pair by pair, and adds the results. Fill it to the right: $B$2:$B$6 (the weights) stays, C2:C6 becomes D2:D6. The dollar sign, at work.

When the spreadsheet meets Git¶

A CSV file (Comma-Separated Values) is plain text: one line per row, values separated by commas. It stores values only: no formulas, no colours, no charts. An .ods stores everything, but it is a ZIP box.

Put the inventory in your repository twice, as .csv and as .ods, change the number of monitors in both, and ask Git what changed:

$ git diff
--- a/data/inventory.csv
+++ b/data/inventory.csv
@@ -2,7 +2,7 @@ item,category,department,quantity,unit_cost,billing
-Monitor,hardware,all,12,180,once
+Monitor,hardware,all,14,180,once
diff --git a/data/inventory.ods b/data/inventory.ods
Binary files a/data/inventory.ods and b/data/inventory.ods differ

For the .csv, Git shows the exact line and the number that changed. For the .ods, it only knows that something is different: a ZIP is not lines of text. That is the whole argument for plain text in a repository, in six lines of output.

Bonus: Pandoc turns the CSV into the Markdown table of your documentation, without typing it:

$ pandoc -f csv -t gfm data/inventory.csv
| item                    | category | department | quantity | unit_cost | billing |
|-------------------------|----------|------------|----------|-----------|---------|
| Laptop dev              | hardware | dev        | 6        | 1100      | once    |

Key takeaways¶

• An office suite is word processor + spreadsheet + presentations; you get it as installed free software (LibreOffice), installed proprietary (Microsoft 365) or as SaaS in the browser.
• ODF (.odt .ods) and OOXML (.docx .xlsx) are both ZIP archives of XML.
• A formula starts with = and points at cells and ranges; references are relative and move when copied, unless you pin them with $ as absolute references.
• IF, SUMIF, COUNTIF and SUMPRODUCT ask questions of your data; check every total by two roads, because a spreadsheet never tells you it is wrong.
• Shared online documents are edited at the same time; give each person the least role (viewer, commenter, editor).
• Keep data in the repository as CSV: plain text, so git diff shows exactly what changed.

Quick check¶

Next: the oldest collaborative tool still on every desk, and how a message really travels — Email and messaging.