Phillip Burton

Are you a seasoned Excel user? In this article, we’ll look at why you would want to learn SQL if you already are an Excel user.

I was once in the same place as you. I started programming in the late 1970s and early 1980s. In the 1990s, I first started creating spreadsheets and used Microsoft Access (another type of database). However, it was over a decade later before I began to investigate SQL Server. 

Isn’t that just for big businesses? Why should you learn it? Let’s find out.

70-461, 761: Querying Microsoft SQL Server with Transact-SQL

Last Updated September 2021

Bestseller
  • 280 lectures
  • Beginner Level
4.5 (8,163)

From Tables and SELECT queries to advanced SQL. SQL Server 2012, 2014, 2016, 2017, 2019, exams 70-461 and 70-761 | By Phillip Burton

Explore Course

Similarities between Excel and SQL Server

What do Excel and SQL Server have in common?

However, the similarities stop there.

Differences between Excel and SQL Server

Adding Data in Excel

Excel is excellent as a presenting tool, but the spreadsheet application does have its disadvantages when it comes to data entry. It holds data in an unstructured format, meaning that users can put information anywhere they want in the spreadsheet. 

In the image below, data analysis would only be accurate if you store the dates in column A. Because Excel has no limitations, the user could insert dates into columns A, B, and F without Excel raising any errors.

Data table with missing entries

Also, the data in a single column doesn’t need to be of the same type. For example, users could insert dates, text, and numbers in the same column, making analysis hard to do. You can also merge cells together, which may be suitable when creating presentations, but it makes analyzing data much harder. Of course, this is especially true if you are using more structured analysis techniques such as PivotTables.

Fortunately, you can use the data validation tool to stop this from happening. Data validation ensures that users are inputting the correct type of data in each column. You could also protect the spreadsheet to prevent users from making structural changes, such as Merge Cells. However, in my experience, these types of safeguards are never used.

Adding Data in SQL Server

SQL Server has strongly typed data storage. This means that each column has a single data type such as text, date, or number. It is not possible to put anything in a particular column which is not compatible with its type. For example, you could insert “1” into a text column because it can convert “1” into text. However, you cannot insert “hello” into a number column because it cannot be converted. If you attempt to do this, you will encounter an error. Additionally, SQL Server cannot merge individual cells in tables together.

Table with column name and data type
Invalid value

Your data is immediately validated in SQL, increasing your data integrity and allowing for more reliable analysis.

Adding functions into data in Excel

In Excel, you might have 100,000 rows of data, including FirstName and LastName columns, and you want to create a FullName column. That is a very easy formula to create using a formula like the following:

= A2 & ” ” & B2

However, you’ll have to create this formula for each data row. This increases the time required to calculate the values and increases the file size, as Excel has to store these formulas.

Data table

There are ways around this in Excel. You could:

However, the default is that you have to make a formula for each of these 100,000 rows.

Adding functions into data in SQL Server

In SQL Server, you only need to add one formula. SQL stands for “structured query language,” and you can use the programming language to add this formula. For example, you could add a computed column in your table or create a SELECT statement which concatenates these columns:

SELECT FirstName, LastName, FirstName + ' ' + LastName AS FullName
FROM myTable

You can save the results of these SQL queries as Views or Stored Procedures and regenerate the results whenever you want. You could also use it in more advanced fields like data science.

Security in Excel

Excel has not got many security features. This can be a concern if you have sensitive data, such as personal or salary data.

When saving your spreadsheet, you could password protect it. This means that you need that password to open the Excel file.

Password protection in Excel

However, someone can bypass this protection using password crackers, which are readily available on the Internet.

Another way of locking down Excel is by protecting locked cells in a spreadsheet. This could stop people from looking at a particular formula to see how it is calculated, or changing some of the styles or content (such as merging cells together).

Protect sheet in Excel

While useful, this again can be turned off using password crackers.

Security in SQL Server

As a Database Management System, SQL Server has security at its core.

Users and logins folders

This user account will also determine what sort of access you will have to the data. For instance, you may only be able to read a certain set of tables or queries. Alternatively, you may be able to only insert data without the ability to read it.

SQL Server is also good for version control. Suppose you added some data, altered it, then deleted it. In an Excel spreadsheet, you would only have the end result (namely, that nothing had changed). In SQL Server, you could have the entire history if you wanted to track it.

Can you use Excel and SQL Server together?

If you store data in SQL Server, can you use it in Excel? Absolutely, and it is very easy to do. In Excel, you can go to the Data tab and link data from SQL Server by going to Data – Get Data – From Database – From SQL Server Database.

Get Data in Excel

This can be the original data or processed data. In this way, SQL Server can process the data, and Excel can then make the visualizations you want from the analysis.

Excel can have up to around a million rows in a single spreadsheet, and tables in SQL Server can store enormous amounts of data. Therefore, if you are importing big data sets into Excel, you have two options:

The data can be refreshed by right-clicking on the table and choosing Refresh.

This means that:

You can’t do this easily in Excel. It is much simpler when you use SQL and Excel together.

How can I learn SQL?

If you are interested in learning how SQL Server works, why not look at my course “Database Fundamentals,” which is exclusive to Udemy.

In just over 8 hours, you will learn the key concepts of SQL Server:

For a more in-depth look at the SELECT statement and related objects, please go to my “Querying Microsoft SQL Server with Transact-SQL” course.

However, maybe you want to know more about Get, Transform, and Power Pivot? If so, please have a look at my “Analyzing and Visualizing Data with Microsoft Excel” course. It also shows how to connect Excel with SQL Server.

I hope that you enjoyed this article and that you found it useful. Maybe you will consider looking at learning SQL Server. You can then connect it to your Excel visualizations.If you’re ready to learn more about SQL or Excel, please continue on to Udemy’s other blog articles. “What is Excel and how it can work for you?” will give you an overview of how the spreadsheet software became an essential skill for professionals. “What is SQL?” and “Best way to learn SQL,” will give you a deeper understanding of the database language. In my next article I will explain how to use SQL in Excel in more detail.

Page Last Updated: May 2021

Top courses in SQL

Complete Microsoft SQL Server Database Administration Course
Imran Afzal (300,000+ students), Abbas Mehmood
4.8 (111)
Highest Rated
SQL for Beginners: The Easiest Way to Learn SQL Step by Step
Code Star Academy, Oracle Master Training • 100,000+ Students Worldwide
4.4 (1,685)
SQL Programming Basics
Global Academy
4.5 (571)
The Complete SQL Bootcamp 2021: Go from Zero to Hero
Jose Portilla
4.7 (110,050)
Bestseller
The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
Colt Steele, Ian Schoonover
4.6 (59,138)
Bestseller

More SQL Courses

SQL students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo