# A Pattern Language for Spreadsheets

Spreadsheets are widely used, especially in the industrial sector. They can model complex workbooks containing multiple sheets with meta data rich cells (content and appearance). We observed several of such workbooks in industry projects and learned from their authors why they modeled and designed spreadsheets in certain ways. From that, we derived generation patterns which form a pattern language for spreadsheets. The structure of these patterns are heavily inspired by Alexander's patterns in the architectural domain, however with the difference that our patterns describe for given circumstances (situations, issues, facts) concrete possibilities how to model them using spreadsheets. Typically, such a pattern comes with a title and a context hinting to a specific issue. This circumstance is described in more detail followed by a solution how to store the containing facts in a spreadsheet. After that, an example illustrates with an image how the pattern could be applied. Some patterns provide links to related ones and they are grouped in categories.

Main Author: Markus Schröder 01.03.2021

Contents

## Modelling

Numeric Information as Text

Context: a user is completing a spreadsheet.

There is numeric information …

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.

… therefore, it can be also represented as text

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.

Examples

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.

Related Patterns
2021-03-01

Acronyms or Symbols

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

An entity refers to another entity or has a literal value …

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.

… therefore, to save time a rather short acronym or symbol string is used to refer to an 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.

Examples

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.

Related Patterns
2021-03-01

Multiple Surface Forms

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

Entities can be mentioned in various ways …

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

… therefore, different cells contain distinct surface forms of equal entities

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.

Examples

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.

2021-03-01

## Formatting

Property Value as Color

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

Some entities have different values for the same property …

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

… therefore, different colors that encode property values are chosen to color ranges

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.

Examples

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.

Related Patterns
2021-03-01

Partial Formatting Indicates Relations

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

Multiple entities in one cell have different relationships with another entity …

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.

… therefore, partial formatting is used to indicate their relations

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

Examples

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.

Related Patterns
2021-03-01

Outdated is Formatted

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

Information is not valid anymore, but must not be removed completely …

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.

… therefore, outdated information is formatted

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.

Examples

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.

Related Patterns
2021-03-01

## Extension

Multiple Entities in one Cell

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

Multiple entities have to be referred to …

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

… therefore, entities are listed in the same cell

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.

Examples

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).

Related Patterns
2021-03-01

Unordered Entities

Context: a user enters multiple entities in one cell.

Multiple entities have to be referred to …

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

… therefore, entities are listed for each cell, but in different orders

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

Examples

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.

Related Patterns
2021-03-22

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

Additional information is related to information that is already recorded in a cell …

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

… therefore, the additional information is recorded in the same cell

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

Examples

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.

Related Patterns
2021-03-01

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

Additional information is related to information that is already recorded in a cell …

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

… therefore, via a reference the additional information is recorded in a distant cell

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.

Examples

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.

Related Patterns
2021-03-01

Cell Comment Explains Content

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.

Some cell contents are obscure …

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.

… therefore, cell comments are attached to the cells to explain their contents in more detail

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.

Examples

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".

Related Patterns
2021-03-01

## Layout

Multiple Types in a Table

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

Some entities of different types share same properties …

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

… therefore, they are recorded in the same table

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.

Examples

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.

2021-03-01