This feels like a century old question, “can I use Google Sheet or Excel as a database?” The short answer is unfortunately no. The efforts of storing, transforming, and maintaining a spreadsheet so that it would work just like a database are beyond reasonable.
Spreadsheets by definition is a tabular form of application that stores data in a grid of cells. The origin of it can date back to the heydays of newspaper.
When should you use a spreadsheet? Spreadsheets are particularly useful for
1. Data modeling
You can ask a financial analyst how much they use Excel on daily basis. I used to be one of them. I spent probably 80% of my time working on a spreadsheet working with individual company’s financial data. To build a quick Pro Forma or DCF model, there’s really not a replacement to my knowledge. This however changes when you’re dealing with a larger quantify of data. If you’re extracting information from more than 1 million rows of data, a spreadsheet becomes much less capable.
2. Data entry
Accountants and people in operations enter data in spreadsheets for centuries. It is perhaps still the most flexible way of building a database till this day. Again, this changes when you have a thousand of sales people all entering sales numbers in one database. This is where a lot of proprietary software comes in and transform the manual data entry process.
3. Quick charting and data exploration
This is maybe the weakest spot to a spreadsheet’s prowess. There are thousands of charting tools on the market; some cater to a particular type of data feed, some are even designed for a certain type of data (time-series, text and even image). A typical spreadsheet program does not handle all types of data equally when it comes to data exploration and visualization. This is why you have hundreds of Business Intelligence software that charts your spreadsheet data in like a thousand ways. Nevertheless, they’re all built on tops of a spreadsheet!
Databases on the other hand are organized collections of data. They’re more useful in
1. Keeping records and data cross-referencing
Large number of data records need to be stored logically and systematically. Unlike spreadsheet, databases can be joined, and merged programmatically since the table schema is formatted and consistent.
2. Supporting application development and deployment
One key drawback about a spreadsheet is the fact that it cannot support an application that keeps requesting new data.
3. Storing big data tables from different sources
Another limitation to a spreadsheet is the fact that it cannot exceed a certain limit in rows or columns due the memory limit of our computers.