Dealing with Too Many Rows in Spreadsheets

# 8 on our "Top 10 Dysfunctions of #Spreadsheets” … drum roll please … #TooManyRows. Have you run into this brick wall before?! 👇

We’ve seen this many times — it makes the phone ring here at Kiza. “Our Excel doc has run out of rows so it is time for us to get a more professional system.” ⛔️

#GoogleSheets and #Excel will walk you through their specific limitations — roughly about 1 million rows, depending upon the number of columns.

I had a little fun and copy / pasted a chunk of sample data into an Excel doc. I did that a few times, selecting all each time so that my clipboard grew (exponentially), and it didn’t take me long at all to max it out.

A common use case is when a #workplaceInnovator like you hooks some third party input or #API into a #spreadsheet as the back end database, or they have an admin do some bulk imports of CSVs. We’ve seen change logs, or website orders or survey results feeding in dynamically that goes a bit wild over time. 📈

#CaseStudy — We worked with a custom furniture builder who basically went viral. Good for them! 🪑🔨

They brought orders in from #Shopify every day, automatically bringing data into a centralized Google Sheet for inventory tracking and fulfillment. It worked for a time … and then one day it broke. Ack!

It would probably be harder to reach that 1M limit with only manual data entry, though we’ve seen that too.

What to do?

1. In a #spreadsheet, before you start hooking in a batch loader or asking your teammate to paste in a CSV every day, be sure to do a little math about how your data might grow.

#Model it out. That’ll be easy for you to do … in a spreadsheet, ha!

Think not only about current volumes, but what if your volumes of logs or responses continues to grow 2x or 4x … how many months would it be until you hit that #limit?

Try to also keep your #columns narrow, ie. few. If you do automate bringing in orders from a web system, for example, see if you can select only the columns that you really need, rather than bulking it up with a bunch of extra data.

Lastly, set yourself a monthly calendar reminder to check. If you see the slow train coming, you’ll be able to adjust (or call us) in time before the day comes, avoiding any emergency fix. 🗓️


2. In a #RapidApp development environment such as #Claris #FileMaker, you’ll be limited only by the drive size and the file size, which if hosted out on Amazon Web Services (#AWS), will be pretty darn big! ☁️

You’ll also be able to architect scalable solutions to automatically archive data periodically, be that as a part of a nightly or more periodic process. 🗑️

Monitoring will also be a natural part of the process of a solid data solution, so you’ll avoid that dreaded emergency moment and keep smooth sailing as you grow your operation.

#NoLimits! Dream big.