The good, the bad and the ugly of Spreadsheets

As you can tell by our name, we love spreadsheets in general. However, there are still things about them we don’t particularly get excited about.

So, we are here to set the right expectations about spreadsheets because as our favorite blogger Tim Urban says Happiness = Reality - Expectations.

The Good

People often forget that spreadsheet software, VisiCalc, was the first “killer app” ever. It propelled a huge number of jobs and industries that didn’t exist before.

The main advantage of spreadsheets is how they store data, particularly as rows and columns. This structure makes it great to store any format of relational data.

Storing data can be done in a lot of different ways such as a database, but for most people’s data needs, a database can be an overkill. Spreadsheets make it easy to store data. (shameless plug: if you need to access spreadsheets as a database, you can check out our product APIspreadsheets)

After you have stored your data, spreadsheets also make it super easy to share data. Other data storage formats are not adept at sharing.

Think about the last time someone needed to view a table from your database. You most likely used a spreadsheet or csv file to send them the data.

We, at love spreadsheets, love these 2 qualities of spreadsheets: easily storing and sharing data.

The Bad

Okay, so although we love storing data in spreadsheets, if your data gets too big, then spreadsheets get unwieldy.

Even though Excel supports up to 1,048,576 rows, we have found that any amount of data even close to that uses too much memory and freezes Excel.

No matter the amount of data you have, data cleaning is a huge part of data analysis. Writing macros in VBA can help you do more advanced data cleaning but we believe that it shouldn’t be this complicated.

Things like filling in missing values, removing fuzzy duplicates, converting dates easily still require some effort and use of programming. Not to mention, it is super hard to share what you have done with others. (shameless plug: we write articles on how to do these things in Python, follow us on Medium or subscribe to our newsletter at www.lovespreadsheets.com).

As data gets more and more complex, spreadsheets are not able to handle the complex cleaning and transformations that modern day data requires. A big drawback to this great data format.

The Ugly

We are pretty fluent in using spreadsheets. However, there are two things we still find very ugly:

  1. Pivot Tables
  2. Visualizations

There’s nothing particularly wrong or bad about these features. In fact, being able to quickly transform your data using pivot tables or visualize it in a quick graph is a big plus.

But no matter how many times we do it, there’s still clunkiness about it. Pivot tables inexplicably generate 30 more rows. Visualizations don’t show what we want until we experiment with selecting data a 100 different ways.

Not to mention, depending on the data, Pivot tables and visualizations often look like an eye sore compared to the beautiful tables and graphs we see on the web today.

This is interesting…but what does it mean for us Spreadsheet aficionados?

Look, Spreadsheets and MS Excel are super powerful tools. You can do almost anything in them, especially with Macros and VBA.

However, we are all about using the best tools for a job and there are many great tools out much better suited for particular jobs. Such as Tableau for Visualizing, using libraries in R and Python for data cleaning, Weka for Machine Learning, SQL to get data etc.

As we move into and adjust to this big data world, the capabilities of spreadsheets start to hit their limit. If you want to excel (pun intended) even further at work, we recommend picking up these additional skills while still using spreadsheets as a great data storage and sharing tool!

Written by

Products and resources that magically simplify hard spreadsheet tasks. Check us out at www.lovespreadsheets.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store