Why can't we use MS Excel as database to store data? I mean it is also columns and rows with so many inbuilt functions.

Question

I understand there's limitations like number of rows and columns, can't we use multiple sheets in same workbook? Also, online version allows simultaneous editing. I can't understand why we can't use this and why to learn SQL. Is it due to copyright issues? Please clarify.

Answer 1

Have you ever tried to group an Excel table with 10,000 rows by the values of column B, sort the results simultaneously by column C and get the sum of the top 100 results in column A that are between value X and value Y, but only for rows that have a certain value in column D? Excel isn't made for that. If you need to combine multiple tables because one table isn't enough to hold all of your data, things are only getting worse. Compared to SQL, Excel would be terribly slow and difficult to maintain, probably can't be accessed by multiple users/instances at once etc.

Answer 2

the tougher part is going from one single table in Excel to a properly defined relational database with one to many relationships between the tables....but that effort is important because a properly defined relational database is how you minimize redundant data in your system which in turn provides high data integrity, consistency, and reliability

Answer 3

I think it's important to note that SQL is a query language to modify and select data from a relational database management system, not a relational database management system itself where the data is actually stored. SQL can select data from Access or SQLserver, Microsoft's rdbms products. SQLserver has much larger storage capabilities and excellent security capabilities. Access works for a handful of trusted users.

Answer 4

Akshay You can use Ms Access instead of MS Excel for a simple database with lesser data amount capability then SQL. But if you make a complex database with bunch of data, I suggest SQL (some people will suggest NOSQL). I agree with Anna for the arguments.

Later, when you need to analyze with spreadsheet, you can export it to appropriate program, at least CSV.

Answer 5

other rdbms such as Oracle and Postgress and Mysql all use SQL to select and modify data. the dialects can be slightly different but only slightly

Answer 6

Also if you're not sure which product to use to store your data you can start with a single Excel table and import that data into an Access relational database as you run into performance and data integrity issues. you can import Access tables into SQLServer if Access is not enough.

Answer 7

While agreeing emphatically with all prior posts...

Technically, you really can use MS Excel as a database. Many people do. I worked for a large company for a couple of years that did this. There was very little data integrity being preserved -- many costly errors, lost work, and repeated work. It was incredibly inefficient, frustrating, and had a huge impact on the bottom line.

I remember one particularly annoying performance issue where I felt the task should have been relatively painless (like find a value), but because something could be anywhere in the sheet, MS Excel examines the contents of each cell individually -- which takes so long that I thought my computer froze, whereby I would get up to grab coffee and come back to see (to my surprise) that it had done something (not the whole thing, but something).

In addition, MS Excel has character limits per cell, which means data and functions contained in each cell would be truncated or broken.

Really, there are many reasons to use SQL over Excel.

Answer 8

Akshay all rdbms systems that I have used have a mechanism for viewing table data and an SQL window where you can write SQL to select specific data.

Answer 9

you can use anything as anything you want
shoes like hat, excel as db, or else
in excel don't have queries which is main part of sql interface and command langue
in excel don't have fine visualization for 100.000.000 rows as in db managers, in excel you don't have provider to your base for two or more users at once, what is need for write sites or office tools, by the way, web hosts working only with sql and nosql db, no one will let you use excel on host, all world use sql, and you will need solve all this problem to use excel instead of sql. But it's still really, as wear shoe instead of hat. and if you writing a small table with data and using excel to code, i think for a short time while your table not grow, you can strorage it in excel sheet for one user only

Answer 10

I could say more on DB's, but here are some dev hints at / beyond Excel...

You *can* use DDE to do all sorts of crazy cool parallel things with Windows apps:
https://docs.microsoft.com/en-us/windows/desktop/dataxchg/about-dynamic-data-exchange

Or OLE automation via VBA...
https://docs.microsoft.com/en-us/office/vba/api/overview/excel

But this is Excel's file format spec (ok to skip this reference, just keep going for my point*)
https://www.loc.gov/preservation/digital/formats/fdd/fdd000398.shtml

Excel documents aren't databases with clustering, granular locking / transactional mechanisms integrated with the OS.

They're ZIP archives.

* "[documents] can be explored...typically by changing the file extension to .zip."

Answer 11

Akshay, I think you should try MSAccess first for understanding how SQL works for making databases and all of their relationships, I did it before I knew what is SQL.
There you can also see the data of the table easier as if you saw excel or other spreadsheet apps.
You can also program the GUI based on VB that is already attached in MSAccess.

SQL is used for storing data which you can access it with other programming language such as PHP, Python, Java, C, CPP, etc.
When you program it well, you can see data as if you are using spreadsheet apps. PHPMyAdmin will help you organize it which is already packetted by XAMP etc.
Also you can export it to excell or csv format.

I remember the old time when I used dot DAT "*.DAT" extension format for saving datas.

Answer 12

Thank you everyone for taking time and answering my question. After I read through your answers I'm bit clear on why excel is a best solution for database uses.

Answer 13

I've another question if it's ok, What I understand is that almost all apps that store data like note taking, expenses management, contacts, file explorer etc.. has database behind it. Am I correct?

Answer 14

Seems right. Data can be stored locally without a database, but a DB can be layered on top of it.

Answer 15

MS ACCESS is used to store the database

Answer 16

Excel is also a small DB. But there are limitations when working with complex projects

Answer 17

I usually use excell (now I prefer google sheet) for pre and post dB making.

'Pre' means for designing tables I want to look like and what kind of data in it, also how the data to be displayed.
'Post' means I analyze data I gathered from the dB.

And FYI, google sheet currently support SQL instruction too.

But still, if I want to make a complex dB, I prefer using MS-Access because it is simple and can do inter-relationship between tables; if the datas are no more than 1000's, but if it is too big then I choose mySQL for it is can handle intrer-relationships between tables too.

Lately I read about NOSQL with its advantages and disadvantages, but I still learn it deeply. Google uses both of them. You can watch it on "Google SQL vs No SQL" on YouTube.

Answer 18

Alfa Riansyah Anna Lisa F so isn't there a database that's having a GUI interface so that we can see the tables like we do in Excel etc.
Also, can't we make such GUI interface software to run all SQL commands using buttons etc.

Answer 19

what is the importance of<font color="#ff0000"> SQL<\font>?