I was reading about one of the new functions that Microsoft has added to Excel, XLOOKUP(), and I started wondering where the different LOOKUP() functions came from. This little rabbit hole turned out to be quite revealing in terms of the way that Excel looks and works – the search took me to some history of VisiCalc and Lotus 1-2-3 which we will look at here.
The story starts with Dan Bricklin and Bob Frankston in 1978 and 1979. They are the authors of a spreadsheet program called VisiCalc. Dan Bricklin has a website with quite a bit of interesting history and a page devoted to VisiCalc. There is even a page where you can download a copy of VisiCalc that will run on 32-bit Windows. I run it on 32-bit Windows 7 with VirtualBox, and it seems to work fine for simple tasks and taking screenshots. There is even a VisiCalc reference card for the Apple II computer that provides lots of details about how to use the software.
So, how is this cool? Fig. 1 shows VisiCalc downloaded from brickin.com running on my Windows 7 virtual machine.
The details of what VisiCalc can do may be interesting, but for our current purpose, I just want to look at the structure of the interface. Those of us familiar with Microsoft Excel will be comfortable with the design of what we see here. In particular, the grid layout with row numbers down the left side, beginning at the top, and alphabetic column labels above row number 1, and moving left to right. In VisiCalc, the number of rows was limited to 254 and the maximum number of columns was 63. Since there are only 26 letters in the English alphabet, the 27th column was labelled with AA, and the 28th was AB, and so on. In VisiCalc the maximum column label was then BK. The address of a cell is given by combining the column letter and the row number as C1. In Fig. 1, the cell C1 is currently selected/active, as we can see from the fact that it is the only light-colored cell. The address of the currently selected cell is also shown at the left end of the Entry Contents Line above the column headers.
Fig. 2 shows a late version of Lotus 1-2-3 downloaded from the WinWorld site which archives older software including various versions of Lotus 1-2-3. This is the way Lotus 1-2-3 looked in the year 2000, 20 years after VisiCalc was released.
Fig. 3 shows the version of Excel (version 2101) that I am currently running on my 64-bit Windows 10 computer.
The active cell in Lotus 1-2-3 and Microsoft Excel are both indicated with a heavy line around the cell, and that cell’s address in the box to the left of the formula bar above the work area: “A:C1” in Lotus’ Selection Area, and “C1” in Excel’s Name Box. Figures 1, 2 and 3 all show cell C1 active, and the contents are a calculation of 96 divided by 18 – the result is shown in the cell.
The contents of column A in Fig. 1 is text, and is aligned to the left side of the cell, while the contents of column B are numbers, and these are aligned to the right side of the cells. This behaviour is also seen in Lotus 1-2-3 and Microsoft Excel, and provides a useful visual cue because sometimes we get spreadsheets in which someone has formated numbers as text.
Dan Bricklin has a very interesting TED talk entitled Meet the inventor of the electronic spreadsheet that is definitely worth watching if you are interested in a bit of history.
Figures 4, 5 and 6 show the last cell accessible in the different software – this is the largest column label and row number available in the software
In Fig. 7 we see the change in the maximum number of rows and columns in the spreadsheet software we have been discussing – note that this change is over a period of 42 years. VisiCalc was first released on the Apple II, which was an 8-bit computer, meaning that memory addresses and integers were limited to values that could be stored in a single byte of 8 bits. Initially Lotus 1-2-3 was released for 16-bit computers, and the last versions were able to use 32-bit integers and memory addresses. Currently Microsoft Excel is available for both 32-bit and 64-bit operating systems. The result of all this can be seen in Fig. 7, where the maximum number of cells in a worksheet is shown in the second column from the right. Every 21 years, the maximum number of cells in a worksheed has increased by a factor of 1000. This has been made possible by advances in computer architecture and the need to analyze ever larger data sets.
Practically speaking, the amount of data we can store and work with in our workbooks is limited by the amount of physical memory (RAM) we have in our computers.
The overall structure of the interfaces hasn’t changed much. VisiCalc in Fig. 1 was from about 1979, Lotus 1-2-3 in Fig. 2 was from 2000 and Excel is shown as it is in 2021. Over a period of 40 years, the design decisions made for VisiCalc have stood up well. Dan Bricklin and Bob Frankston gave us a computer version of the paper spreadsheet and the decisions they made in 1978-1979 were insightful enough that we still find their way of working with numbers useful. Thank you, gentlemen!