Referencing Cells

Referencing a Single Cell

In a verb-noun approach, which is the default in spreadsheet apps right now, the user selects the action (verb) first, that is referencing the cell by typing an '=' symbol and moving into a reference/formula mode and then selects the cell (noun) that needs to be referred.

In contrast, in a noun-verb paradigm, the order is flipped. In the solution we went for, the user selects the data (noun) first, that is, the cell that you need referred to and drags it to the target cell. There is no 'reference mode'. Instead of shifting attention from the data to the target cell and then returning to the data that needs to be linked, this allows the user a natural way to link a cell to its target cell with at least one less attention shift. This makes it natural to the way a beginner user might think and help reduce errors the modes might bring in, as Raskin argues in his book, The Humane Interface.

Referencing Multiple Cells

Extending the idea further, here we look at referencing multiple cells. The same drag-and-drop interaction is used to reference multiple cells. Additionally, upon dropping the selected cells on the target cell, the user is presented with a list of commonly used functions based on the data types the cells hold. Here, three numeric cells are referenced, so a list of common numeric functions is provided to quickly complete the reference action.

Again, in the verb-noun construction we see in apps today, the user has to shift the focus from the data to go into a 'formula mode', typing an '=', then type in the function to come back to select the cells that were in the locus of their attention before they did all these actions. The noun-verb construction naturally maps to how most users intuitively think about approaching the task and reduces the surface area for errors by eliminating the mode.      

Ways to Look Up Data

Nilenso suggested us explore a few tricky cases at the onset of the project and one of them was cross table lookups. There are multiple ways to to do lookups on spreadsheets, but most of them have a learning curve that involves the formula language and additionaly, the lack of a table schema also makes it hard for a novice user to wrap their head around most of the current solutions. Let's take a look at some of them:

INDEX and MATCH

This solution uses two functions INDEX and MATCH. MATCH function finds a given value from a given range and returns the position which INDEX uses as argument to lookup the desired value.

VLOOKUP

VLOOKUP is more straightforward since it's tailor-made for our task here. It takes three arguments, first the value you want to find, second, the range of cells in which to search for the value you want to find and a third argument for the column index that contains the value that you are trying to find in the range of cells.

Relation column

Some apps have built-in methods like relation columns that can be used to do relations like lookups.

We explored two interface solutions to make lookup natural and friendlier. One of them involves making the process easier with live instructions and the other brings in a table primitive that allows relational database operations.

Cursor Instructions

As we saw in the solutions above, the formula language can be tricky to manipulate. The details of the function provided for help in the popover reads like a technical documentation that is meant for software developers than normal users.

The solution we devised is a more general approach that applies to most spreadsheet functions, not just lookups. It provides contextual, onscreen instructions based on the function the user is providing arguments for, and is evaluated live for tight feedback loops. This augments the direct-manipulation interface, spreadsheets already have for providing arguments with a more friendlier step-by-step function documentation that helps users make sense of the function by directly interacting with the data.

We also explored a way to apply functions and formulas in a modeless way by creating smart suggestions based on user selections. Instead of going into a mode to type in the function then select the data, the user can select all the data involved in the function first and then complete the action with a given list of functions that fits the data they selected.

Table Joins

The second solution is a natural extension of the collection/table primitive Nilenso suggested as a core feature of Bean from the get-go. It brings in relational operations to the spreadsheet by introducing a table primitive. With the new table primitive in place, a user can use the natural join operation to create an equivalent output as that of the lookup.