I still remember I could not distinguish between a spreadsheet application and a database application when I saw their interfaces for the first time – they both have a table with rows and columns – they look alike! Yet, it is essential to understand that they serve different purposes.
Figure 1 The interfaces of a database application (left) and a spreadsheet (right) resemble each other.
Microsoft Excel is famous worldwide as a spreadsheet application that may not need further introduction. As you learn more about it, you should agree that it is an application designed for calculation, and MS Excel excels (pun intended) at it by having hundreds of functions. Yet, instead of calculating, if you misuse a spreadsheet application to manage data, even sophisticated applications like MS Excel will fall short too. One of the reasons is that MS Excel can only accept a limited number of rows. What exactly is the limit? Press Ctrl+Down Arrow in MS Excel to find out!
It turns out that the “end” of a standard MS Excel worksheet stops at cell XFD1048576 (the last cell reference), i.e., there are 1,048,576 rows and 16,384 columns (Learn more about the limits in MS Excel here). Although the total number of rows you could use is enormous (more than a million), efficient data storage would only employ a row partially in principle. We usually use one row for each observation/record regardless of the number of variables (columns). That means MS Excel can, at most, store 1,048,576 observations/records only. One million observations are not a big deal, especially in the digital age. Any smart sensors in a hotel guest room can generate data in such level. Assume that there is a hotel property with 1,000 guest rooms. Whether a guest room is occupied or vacant, the front office manager would have 1,000 records to trace the status of each room daily. Suppose the hotel has been operating for ten years. There would be 3,650,000 (1,000 rooms by 3,650 days) observations to maintain. Regardless of variables (columns), this scenario has reached the row limit of MS Excel threefold readily. Indeed, it is easy to spot a hotel with 1,000+ rooms, ten years of history, and willing to analyze its business data. Some students may suggest having multiple MS Excel workbooks/worksheets to bypass the limitation. However, many critical features in MS Excel would become less flexible in such an arrangement. For example, Pivot Table does not work across multiple files directly. Mistakes tend to occur more frequently in your formula under such complicated circumstances too.
On the contrary, database applications are usually free from those limitations. It is beneficial for long-term data management strategy. Microsoft Access, a desktop-class database application, has a 2GB file size limitation. This limitation, however, is fair for a database product designed for desktop-class users (Learn more about the limits in MS Access here). Server-class database applications/systems such as MySQL, MS SQL, and other database products would provide a much bigger space for data storage (e.g., MySQL’s default size limit of each table is 256TB, i.e., 256,000GB).
Nevertheless, take into account the importance of MS Access. For learning database principles and delivering desktop-class data management projects in small-medium enterprises, MS Access serves this sector well. Furthermore, upgrading to a server-class database format is available in MS Access (technically, this process is called migration).
Before undertaking your next data management project, choose wisely for future scalability. MS Excel is suitable for both calculations and data analysis. Yet, it is always better to learn to maintain a database if your data management project is long-term and potentially large in scope. Using a database application to manage your data is far more sensible than simply inputting all data into a spreadsheet – choose the right tool for your project.
Students who enrolled in ICT1002 Digital Competence I will learn more about the principle and technical skills in spreadsheet and database applications.
By IFTM Faculty Member Mr. Joe Lam
