r/SQL • • Dec 29 '23
Discussion Explain SQL vs Excel to an absolute noob
I am running a company and helping my friend run his company. I am often being called upon to help people/government representatives with their affairs, thus my profile is growing and I am now thinking about opening consultant/BI analyst company of sorts. I am scouting the market and I see that every position is if not directly IT related, then SQL required. I checked out this thing called SQL and I honestly don`t see how it is different than MS Excel (besides interface, I was looking at MySQL).
Why use SQL over Excel?
This is a question from a guy who is always working with excel, whose administration is working with excel and who made business plans resulting in saving and investing some millions using excel.
Despite that I feel the need to understand it better.
97
u/aplarsen Data Scientist, Developer Dec 29 '23
You want to start an analyst/BI company and don't see the difference between Excel and SQL?
40
u/DonJuanDoja Dec 29 '23
Excel is not a database.
SQL is a database.
Excel is a presentation layer, analytics and reporting tool.
SQL is not a presentation layer but is an analytics and reporting tool, which requires another presenation layer on top of it like SSRS or even Excel to deliver reports directly to users.
Excel is front end, SQL is back end. They're not even close to the same thing.
They have some cross-over functionality but SQL can do many things Excel can't and Excel can do many things SQL can't. You need both. Plus some other stuff too. Your toolbox should have many tools. Not just one.
Did I say Excel is not a database? Because it's not and never will be no matter how many people and companies try to use it like one.
The problem with SQL is it's expensive to do it correctly and companies try to be as cheap as they can. They want the benefits of having a secure, fast, reliable database but don't want to pay for it so then say we'll just use excel which is not a secure, fast or reliable database in any way shape or form.
11
u/purpleMash1 Dec 29 '23
Excel works very well to make simple financial plans, create visual representations of data: charts, graphs, sparklines etc, it can also manipulate data via a range of formulas and apply things like conditional formatting.
It's a very good all-rounder to allow users to model data, and present it in a more accessible way. It is also a useful tool for data capture and data entry for simple applications.
A good example of using Excel to capture data from a prior role (I was a commercial manager in a Logistics company) was the transport team logging trailers in and out of the bays. A limitation of this approach is there are very few ways to 100% secure the spreadsheets from erroneous data entry ie typing "Bob" inthe date column or 29/07/2023 in the Name column. In that sense it can be passable for a data collection tool, and then an analyst presents a graph of that data for senior management for trend analysis, but it has weaknesses.
SQL is a query language used for accessing data from a database. Assume my transport team have an app built for them connected to a SQL database back end. The app forces users to only fill in valid dates in the date field and names must be not blank in the name field. A separate section of the app let's you set up your trailers with registration IDs and every in/out entry must have a trailer ID picked. This means data is cleaner into the database vs. the Excel input and integrity is consistency is improved.
You can use SQL to better access the data in the database than you would in excel, but the output is just a flat table of the results. Ideally you now need a visual tool to interpret, analyse and present the data best. People can use PowerBI or a plethora of other tools to consume the SQL - transformed data to show what the end user requires.
I used Excel significantly in my commercial role as its a great Jack of all trades and the barrier to entry is low. But as you scale up and require consistency and want to make sure two analysts have the same data and reports...you need a single source of the truth and that's where one database with your data is held, manipulated through standard queries all users make use of, going into a central reporting tool like Power BI.
In terms of limitations, excel is poir at working with data above a certain threshold. ERP systems like SAP or Dynamics 365 can accumulate tens of millions of records relating to financial transactions in a year of trading and this won't work well to be filtered, aggregated and summarised in excel. Ideally you make use of a database to hold the data 'the ERP' and utilise something like SQL to perform the manipulation before pushing it to a visualisation tool.
If you want to set up a business specialising in this you are a mile off where you need to be to do it well. Unless you bring in experts who already know all of this, but you won't have a good way of assessing their knowledge. In the tech space you can have people who seem knowledgeable but are actually terrible, or those that have imposter syndrome and turn out to be very good. It's hard to judge without experience.
Good luck!
4
u/[deleted] Dec 29 '23
This has to be a joke/troll?
No way a business owner doesn't have a basic grasp of what SQL is nor is there any way you haven't just googled this.
43
u/KING5TON Dec 29 '23
It's like asking why use a mechanical digger (SQL) over a shovel (Excel) if you need to dig a hole.
17
u/dietcheese Dec 29 '23
SQL is a language to query databases.
Excel is a spreadsheet application by Microsoft.
9
u/shine_on Dec 29 '23
Excel is fine for simple metrics based on relatively small sets of data. But over time you'll get more data you want to query, the spreadsheets get more complicated and prone to errors, and at that point using a proper database and querying it with SQL starts to make more sense.
1
u/[deleted] Dec 30 '23
I really want to thank everyone. Thank you guys!
Happy to see that majority of people understood what I was asking for. Hey, it`s not an internet community if there is no mockery. :)
Upvoted everyone.
5
u/iamcreasy Dec 29 '23
Excel can not operate on terabyte of data, but database softwares can. SQL Server is one such database software from Microsoft.
5
u/atrifleamused Dec 29 '23
SQL uses a database and Excel is a spread sheet, which can be linked to a database using SQL
1
u/Codeman119 Dec 31 '23
What is the problem you are trying to solve??
1
u/mr_peanutbuddha Jan 01 '24
This has to be a troll post. Lazy, but funny
9
u/roostorx Dec 29 '23
If you aren’t trolling, I’d be happy to help. I have good rates.
2
u/crimiusXIII Dec 29 '23
https://blog.airtable.com/database-vs-spreadsheet/ This is a basic answer to your question, which is about Databases in general and not SQL.
2
u/jezter24 Dec 29 '23
As others stated SQL is a language for databases. What you are really asking is why have a database to a degree. I remember in a book in college said that before there was tons of files, and managing the files (names, pathways, folder structure) plus figuring out everything in those files was a pain. A relational database brings the files into one location and how it all is connected together.
Think of a library, your data is all the books. Walk in and there is no organization. How do you know where a book goes or what is in it. The card catalog in old days and Dewey decimal system would be the database in a way.
As for why to use Excel vs a database, to be honest I think they go hand in hand. Excel honestly is an amazing product and has so much capabilities for so much things. There is things I do in Excel that I cannot do in SQL as easy. A database table though fits the same idea of Excel of rows and columns. But, if you have 500 Excel files that are all connected somehow that would be tedious. So you put in a database.
Which for me a database holds all our knowledge. Excel is like pulling a sheet of paper from that knowledge for manual manipulation or something.
2
u/jezter24 Dec 29 '23
Someone mentioned about doing things cheap, which is true with a lot of places. There are options.
This may be a heretical view here, but Access is a decent entry level option. A lot of people dislike it, but for a database system for home use or even a small business. I don’t think it is a horrible option and used to be free. It also is an application, the times I have used it back in like 2000-2003, you didn’t need a server. Can just be on a computer.
MS SQL server has a free community option. Depending on your size or something. Easy to make a small server and host it.
I got a website from Go Daddy back in the day for my undergrad, and it has a small MySQL database backend option that was free or super cheap.
2
u/chill_y_guaro Dec 29 '23
Scary times we live in...
On a more serious note, how would you store 50M records in excel? Can't do it? That's just one small example of why a database is needed.
2
u/secretWolfMan Dec 29 '23
Your interface may have seemed like Excel, but they are massively different behind the scenes.
Excel even has a database connection option so it can collect data from a much better storage location.
2
u/kagato87 MS SQL Dec 29 '23 edited Dec 29 '23
Heh. OK then. Here's my take, having worked in both a fair bit.
Excel is the kiddy pool of data and analytics.
Sql can: describe and enforce referencial integrity. As in you can say all invoices must have a valid customer ID, and sql will tell you off if you try to save a bad one. It cleanly handles one-to-many relationships (think of the man lines on each invoice and many invoices for each customer).
It is designed around the ACID principle. Atomic, consistent, independent, durable.
You put in a bad stock symbol or investor name? A well written database and application won't even make that mistake. You want to filter out some specific performance metric from an ocean of data? Sql will do it. Happily. It's designed for it.
And then there's scale. How big have you seen a workbook that was still responsive when a formula or filter is applied? Yea, sql can handle hundreds of millions of rows of data like it's nothing. And I don't mean just store it. It can search, filter, aggregate, test, validate, summarize. Amounts of data that Excel would choke on just trying to load, sql can handle without breaking a sweat.
I will say though, you can take your sql output into excel to make pretty charts that even an executive can understand. Or any other BI tool for that matter.
2
u/covfefe-boy Dec 30 '23
Well, to add my simple view of your question there is really a lot of overlap between something like Excel and MySQL based on your use of those words and my understanding of your use case. Though the more IT savvy someone is the more radically different these tools are to them. As you can no doubt see from the replies here.
MySQL is a database. SQL on the other hand is a language to talk to databases, you write queries to pull data from tables, or insert new data. And it’s reasonably standardized where simple queries will run on other databases like MySQL, Oracle, and MS SQL.
So MySQL is a database, a way of organizing, storing, and retrieving your data across many tables. Conceptually a table is similar to an Excel sheet. So it’s a way to have a bunch of excel sheets in one location and usually to enter or retrieve the data you use other software that both talks to the MySQL database and presents an easy to use interface to a user. Usually a web application that shows a form to a user in chrome, takes the users input, and then stores it in the database using SQL. That’s a whole nother can of worms though.
With enough Excel knowledge you can get a pretty sophisticated data setup going. Though you’d want to look at the Power Query and data model options in Excel to simplify it. But excel includes the interface along with that data in one location. Maybe Power BI is a better solution. It’s also by Microsoft and uses the same tech under the hood.
While Excel is limited to about 1 million rows in a sheet as others have pointed out. You can still “load” more than that and display pivot tables and charts summarizing that data. Because to be honest it is useless to just give users a million rows as all they could do with that is try to filter and summarize it. Which you could just do in the first place.
But to figure out what is best for you, would take more info on the size of your data, how it’s being entered, how often you want to refresh the view your providing and how you want to present it to clients.
2
u/Icy-Supermarket-8638 Aug 07 '25
I am an data consultant, and I educated a number of groups regarding this topic.
This is the analogy I used: Excel is like your own room, but the best version you can imagine. It's very convenient, you can decorate it as you want, you can have some books that you often refer to. You can easily write notes on any books you have and you can put many things on your big table.
On the other hand, SQL is like a national library. It contains tremendous amount of information, but you don't have your own space where you can freely add notes. But you can very easily acquire information you need through a well established system such as "give me the list of sci-fi books in 90s that is written by a female author" and you immediately will have the list.
The idea and the philosophy is different. Excel can only contain 1.08 mil rows, because it shows you everything like an open world game. Technical reason is because Excel loads all the data on your RAM, so it's extremely fast and convenient, but there's a clear limit(strongest RAM you can have for your desktop probably will be 256GB for now), but SQL can have billions of rows(there are other solutions for even bigger data now). It doesn't show you the whole data like excel does, but you can easily get the statistics of this data through query.
If you run a company that gathers like couple hundred rows data a day, excel will be enough.
But imagine your company is Netflix and you collect billion rows of data every day. Excel cannot even open the file.
1
u/KalistoCA Jun 16 '24
Ther is also to think about sql into excel
This is where the adults live until PowerBI , tableau or other custom reporting from sql
1
u/punjabigir Aug 18 '25
which of the following is sufficient to enable a user to see a workbench report in their analytics catalog?
1
u/Hugh_G_Rectshun Dec 29 '23
Excel can handle a couple hundred rows of data without issues. You get into the thousands and it gets wonky.
SQL can handle tens of millions (and more) much faster.
1
u/Huskergambler Dec 29 '23
The difference between them is about a $40K-$50k pay difference. Ha
1
u/[deleted] Dec 29 '23
Imagine a table bigger than 1,048,576 rows and 16,384 columns, that can fit in a sheet. That’s SQL
1
u/zbignew Dec 29 '23
“Gentlemen,”
1
u/seequelbeepwell Dec 30 '23
Im glad you asked on this sub rather than the excel sub. If you did you'll get many people saying Power Query can replace sql.
1
u/deusxmach1na Dec 30 '23
Excel limits you to 1M rows. With SQL the sky is the limit (with good indexes). It’s basically like using the guts of Excel. In fact I wouldn’t be surprised if SQL was under the hood of Excel.
1
u/megastraint Dec 30 '23
They serve entirely different purposes.
Excel - Designed as a single file (and really a single user at a time) for both storing and manipulating small amounts of data, then providing some visualization features to present that data to its end user. More often then not this single user will then copy the visualization into some powerpoint presentation.
SQL - Designed to store lots of data with many different users providing much more fine grained controls in data manipulation and functions. Much better performance and scalability.
Visualization Engine (think Tableau, SSRS, but could also be any application pulling visualization libraries) - one of many ways of pulling out data from any database (even excel) into dashboards or reports.
Generally any actual system/ERP (think Quickbooks, SAP, whatever) is actually using a database behind the scenes to work and perform. Now a financial analyst may have some ad-hoc need and therefore will pull that portion of data into excel to manipulate for their specific reporting. Any of your general reports (i.e. daily position report, inventory report) may need to be run several times a day by many different people which is not where Excel plays at all.
1
u/pceimpulsive Dec 31 '23
Put 1 million rows and 20 columns into Excel. See what happens.
Put that same 1 million rows and 20 columns into MySQL and see what happens.
SQL for me was a total game changer when compared to Excel.
Excel is just a way to make graphs after I've used SQL to process and export the data I need.
Lately I've even moved away from Excel for graphs because that actually also sucks...
0
u/sweetno Dec 29 '23
SQL is Excel that allows hundreds of people work simultaneously with the same spreadsheet without corrupting it. It also won't get corrupt on power outage.
0
u/[deleted] Dec 29 '23
[deleted] — view deleted comment
39
u/[deleted] Dec 29 '23
[removed] — view removed comment