The Tyranny of the Spreadsheet Cell

Quote of the Day

Never give up on a dream just because of the time it will take to accomplish it. The time will pass anyway.

— Earl Nightingale.

Figure 1: The Concept of Cell is Both a Strength and a Weakness of Spreadsheets. (Source)

I had a conversation the other day with an engineer to whom I was expressing my frustrations with using Excel to process large data sets of complex numbers.  She also has processed large data sets with Excel and commented that she found the processing painful. I told her about how I was using Excel to work with my data sets, but my techniques all seemed contrived and overly complex. While you can use Excel to work with these data sets, it is a bit like trying to use a Swiss Army knife as a screwdriver. Yes, it can turn a screw but there are much better ways!

As we talked about our Excel frustrations, I decided that all my issues with the Excel have to do with its reliance on the concept of a cell.

I have grown weary of the tyranny of the spreadsheet cell. In a spreadsheet, you have to deal with data on a cell level. Even when I think of the data as an aggregation (ex. matrix, list, vector), Excel forces me to deal with each individual data item when the entire data items is better viewed as an aggregation of data. When I apply a function in Excel, I must explicitly apply the function to each individual data item. While Excel does support some forms of aggregation, like with matrices and named ranges, it almost seems like it was added as an afterthought.

My issues are not limited to the lack of data aggregations – Excel is clumsy to use with complex numbers. I recently had to deal with impedance calculations involving a massive number of complex number calculations in Excel – it worked, but it was extremely painful. The same calculations in Mathcad took a couple of simple statements – the brevity was because (1) Mathcad handles complex numbers as easily as real numbers, and (2) Mathcad handles data aggregations as easily as it does individual numbers.

However, Excel does have its strengths – it provides an excellent demonstration of the power of an integrated data analysis environment. The ability to gather data, process the data with automation (i.e. VBA), and to display the data makes for a powerful tool. The power of tool is limited, however, by the cell-based data model. I would argue that ability to deal with data aggregations makes the combination of Rstudio and ggplot2 a far superior data processing environment to that of Excel.

Understand that I work with Excel every day. I even occasionally compete in Excel competitions to keep my skills up. But I do not try to use it to solve every problem. Excel is great for small data sets of real numbers with a tabular structure – it also does well with lists of strings. For almost anything else, Mathcad, Mathematica, or R then become my tools of choice.

Save

Save

Save

Save

Save