A Pattern Language for Spreadsheets

Context: a user is completing a spreadsheet.

Typically, a numeric information can be a simple number, percentage, currency, date, time, fraction or a boolean value. In all these cases spreadsheets usually store a floating point number and presenting its value with a data format.

Instead of storing a numeric value and formatting it, the formatted value is stored as text in the cell. As a consequence, the numeric value is lost, because the information is modelled with a sequence of characters.

A user stores numbers, currencies and date information in a spreadsheet. Because the person uses copy&paste to transfer information from one source to the spreadsheet, formatted text is copied, too. In these cases the spreadsheet does not automatically detect that a numeric value is intended, thus stores the information as text.

• Multiple Surface Forms

Context: a user would like to complete cells in a spreadsheet that represent an entity.

The user completes a cell to express that a certain entity has a property value. This property value could be a reference to another entity or a literal value.

Acronyms are usually shorter than written-out names. Thus, one saves time when typing just a short text. Similarly, a symbol string (in best case one symbol) can decode a certain property value.

A user would like to state that some books were already read. The person decides that an "x" symbol indicates that a book was read (a boolean "true" value). For the genre of the books the user writes acronyms to refer to them: PH means philosophy, F means fiction and NF means nonfiction.

• Multiple Surface Forms

Context: a user is completing a spreadsheet. Some entities where already mentioned in the sheet.

A surface form is a text which is used to refer to an entity. Some entities have multiple surface forms.

If entities have multiple surface forms, users have to pick a form each time they refer to them. When this is not done consistently, several surface forms of the same entity can be found in the sheet.

Several users list books and their authors. Every time a user refers to the author, another surface form is used. This happens because there is freedom in which of the author's names are used, how names are ordered and if names are abbreviated.

Context: a user has an already filled spreadsheet. Some ranges in the spreadsheet represent entities that have certain properties.

The entities represented by ranges share a property, but they have different values. These values can be different objects like entities or literal values.

In spreadsheets a user can color cells with a foreground or background color. The color makes it easy for the user to distinguish ranges very fast. With background knowledge a user directly sees that a certain range has a certain property value just by looking at its foreground or background color.

A user has a list of books. Some of them were not read by the person yet. Instead of adding another column for the read-property, the user colors the background of the cells representing the books which were not read yet in gray.

• Partial Formatting Indicates Relations

Context: a user is working with a filled spreadsheet. Some cells have multiple entities.

An entity is related to other entities which are recorded in the same cell. Although they are mentioned in the same cell, they have different relationships. The types of relations cannot be distinguished by looking at the entities or their enumeration order.

Spreadsheets allow to partially format texts with different styles and colors. A certain style and color combination encodes another relationship.

A user lists papers and their authors in a spreadsheet. Since authors are sorted by first name, it is not clear who the associated (main) author of the paper is. Therefore, the main author is colored in blue to indicate the associated-relationship between the paper and the author.

• Multiple Entities in one Cell
• Property Value as Color

Context: a user is working on a filled spreadsheet. Information is constantly changing and needs to be kept in sync with the spreadsheet.

Circumstances change over time which make certain entries in a sheet outdated. However, because of specific reasons the deprecated information must not be removed completely.

Spreadsheets allow to format text (e.g. cross out) which can symbolize that the information represented by the text became invalid. This way the reader can still see the information, but knows at the same time that it is deprecated. Note that applying a text style will turn the content automatically into text.

A user lists tasks and responsible persons in a spreadsheet. However, the responsibility changes before the task of tidying up the database was completed. To still identify the previous responsible person, Mr. Walls his name is crossed out instead of being removed.

• Multiple Entities in one Cell
• Partial Formatting Indicates Relations

Context: a user is completing a spreadsheet. Each cell refers to one information, but now more than one information has to be stored.

It happens to be that an entity refers to more than one information. This can be references to other entities or multiple literal values.

Because spreadsheet cells can always be textual, additinonal information can be added by extending the cell's text. Usually, a delimiter is chosen by the user to sparate information. If the cell was not textual before (e.g. a date was stored using a numeric value), it will automatically convert its content to text, so that the additional information can be stored.

A user would like to state the published date of books (left), however some books are already published in their second edition. As a solution, the published date of the second edition is added by an additional line in the same cell. The new line character \n serves as a delimiter. Note that this changed the cells numeric date (left) into textual dates (right).

• Numeric Information as Text
• Unordered Entities

Context: a user enters multiple entities in one cell.

An entity refers to more than one information. This can be references to other entities or multiple literal values.

Every time entities are listed, a different order is used. The entity's place does not tell what information it is.

A user lists pages and year of publication per book in one cell. Since humans can distinguish page numbers and years in this case, the information was added in the order it was discovered.

• Multiple Entities in one Cell
• Intra-Cell Additional Information

Context: a user is working on a filled spreadsheet. Additional information has to be recorded somewhere.

Cells contain already information, usually in an intended structured way. However, some additional information is highly related to that already stored information.

The information is usually recorded as informal text which can be arbitrarily positioned in the cell.

A user makes an investigation about books and learns that the book "Jurassic Park" is about dinosaurs. This information is added by the person as a short note in the Title-column.

• Distant Additional Information
• Multiple Entities in one Cell

Context: a user is working with a filled spreadsheet. Additional information has to be recorded somewhere.

Cells contain already information, usually in an intended structured way. However, some additional information is highly related to that already stored information.

The additional information is not in the vicinity of the original information. Instead, it can be far away or even on another sheet. Usually, there are some ways to refer to the additional information to show the relationship.

A user has a spreadsheet about area sizes of plants. Since a certain area has special plants, a footnote "a" is attached to that number. In a distant cell the special plants are listed using the same footnote key.

• Intra-Cell Additional Information
• Multiple Entities in one Cell

Context: a user has an already filled spreadsheet and collaborates with other users via this spreadsheet. The user feels that some cells need more information to understand their contents.

Sometimes cells only contains few words, a number or symbols. Other users might not understand their contents correcty. It is not desired that the cell's content is changed.

Spreadsheets allow to add a comment to a cell. This comment is shown, when a user hovers over the cell. The comment is usually a text message explaining the content of the cell in more detail.

A user collaborates with users having a native tongue other than English. The person feels that the word "Genre" meight not be understood by them. Instead of changing the cell's content, the user attaches a cell comment that explains that genre means "kind" or "sort".

• Intra-Cell Additional Information
• Distant Additional Information

Context: a user is working with a filled spreadsheet. There are entities of different types.

Although entities are of various types, it happens that they share same properties.

If entities have overlapping properties, columns representing these properties can be reused, although, this mixes up entities of different types. Empty cells for disjoined columns are accepted.

A user lists books and paintings in one spreadsheet, because books and paintings share the properties that both have a title and an author. In case of paintings the Genre-column is left empty.