Databases vs spreadsheets

365 Careers
A free video tutorial from 365 Careers
Creating opportunities for Business & Finance students
4.5 instructor rating • 57 courses • 969,020 students

Lecture description

In this lecture, we will focus on the differences between spreadsheets and databases. This exercise will be relevant not only for current Excel users. Those of you who do not use Excel regularly will still have the chance to understand the advantages and the disadvantages of using databases or spreadsheets. 

Learn more from the full course

SQL - MySQL for Data Analytics and Business Intelligence

SQL that will get you hired – SQL for Business Analysis, Marketing, and Data Management

09:20:17 of on-demand video • Updated May 2020

  • Become an expert in SQL
  • Learn how to code in SQL
  • Boost your resume by learning an in-demand skill
  • Create, design, and operate with SQL databases
  • Start using MySQL – the #1 Database Management System
  • Prepare for SQL developer, Database administrator, Business Analyst, and Business Intelligence job opportunities
  • Adopt professionally tested SQL best practices
  • Gain theoretical insights about relational databases
  • Work with a sophisticated real-life database throughout the course
  • Get maximum preparation for real-life database management
  • Add data analytical tools to your skillset
  • Develop business intuition while solving tasks with big data
  • Study relational database management theory that you will need in your workplace every day
  • Learn how to create a database from scratch
  • The ability to take control of your dataset – insert, update, and delete records from your database
  • Be confident while working with constraints and relating data tables
  • Become a proficient MySQL Workbench user
  • Acquire top-notch coding techniques and best practices
  • Know how to answer specific business questions by using SQL’s aggregate functions
  • Handle complex SQL joins with ease
  • Approach more advanced topics in programming like SQL’s triggers, sequences, local and global variables, indexes, and more
  • Merge coding skills and business acumen to solve complex analytical problems
  • Become a proficient SQL user by writing flawless and efficient queries
  • Tons of exercises that will solidify your knowledge
  • The freedom to query anything you like from a database
English [Auto] Welcome back to our school course in this lesson. We will talk about the differences between databases and spreadsheets. But first I'd like to ask you a favor. I would be really grateful if you could go to the course dashboard that's easy to do. All you have to do is click here and leave a rating for the course. This will mean a lot to me and will help other students know that this course is worth taking. The reason I am asking you to do this now is that this is a large course and most people do not complete all the lessons in one sitting eventually forgetting to leave orating so please do it now. It should only take a couple of seconds and it will really validate our efforts. Thank you so much for this OK. Now having said that let's continue with the comparison between databases and spreadsheets OK. Great. Now we understand why people use databases. However I would imagine some of your Excel users and still imagine an Excel spreadsheet. When we talk about tabular data please don't do that. Data tables databases and Excel spreadsheets are different things. In this lecture we will focus on the differences between spreadsheets and databases. This exercise will be relevant not only for current Excel users those of you who do not use Excel regularly will still have the chance to understand the advantages and disadvantages of using databases or spreadsheets. OK let's start with the definition. What is a spreadsheet. It is an electronic Ledger an electronic version of paper accounting worksheets. It was created to facilitate people who needed to store their accounting information in tabular form digitally so it is possible to create tables in a spreadsheet. This is one reason some people believe spreadsheets and databases are interchangeable while in reality they aren't there are similarities between the two. Both can contain a large amount of tabular data and can use existing data to make calculations. Third neither spreadsheets nor databases are typically used by a single person. So many users will work with the data. The differences between the two forms of data storage lie in the way these three characteristics are implemented. OK imagine a spreadsheet. Every cell is treated as a unique entity. It can store any type of information a date an integer value a string name and then not only can we have different types of values in various cells but we can also apply a specific format to these cells. This is not inherent to databases. They only contain raw data. Each cell is a container of a single data value. It is the smallest piece of information there is. You must preset the type of data contained in a certain field. This feature prevents inadvertent mistakes for example in a field containing date values should the user try to insert a string. The software will show an error and she will have the chance to correct herself. This won't happen in Excel if you insert a string in the column with the date values. You wouldn't obtain an error message in Excel will store the string value in a spreadsheet. Data can be stored in a cell while on a database. Data is stored in a record of a table meaning you must count the records in a table to express how long the data table is not the number of the cells and that is it. You cannot pick a font color or size. All you care about is the information being stored. You don't care about formatting. Our main goal is to save the numbers. Another substantial difference is that in a spreadsheet different cells can contain calculations such as functions and formulas. This means if you want to combine two integers the result will be stored in another cell in a database. All calculations and operations are based on the existing data and are done after it's retrieval. There is a specific feature called views similar to the tables in which you can do a calculation. These objects also contain columns that can mean normal columns like the ones in the tables or could contain a certain type of calculation. There is no way you can mistake a record of data with a calculation. The database features mentioned so far improved data integrity. You can store different types of data in the same field and it is unlikely someone will mistake a data value for an outcome of a calculation especially in large data sets. Data integrity is a strong advantage when working with databases. Naturally you might think a spreadsheet can contain multiple worksheets so one can create tables in the worksheets and then use the worksheets to create relations between the tables. Why bother using relational databases. Well in a spreadsheet such relations will be logically limited. Instead of setting up spreadsheets or worksheets one can set up relations between the tables and this will boost the performance of operations. Increasing the speed with which you could manipulate your data set albeit powerful for many circumstances. Spreadsheets have their limitations. Excel isn't capable of handling over 1 million rows of data. This immediately induces us to look for a solution. Usually the fix is to use databases. We're having 2 5 or 10 million records is not a problem referring to the multi-user property. Spreadsheets are lagging. Essentially every person must update their own spreadsheet with new data. For instance if there is a new purchase to register or a last name in the customer's table to correct every user must make these changes manually. You would justifiably think Google Docs in the latest version of Office solve this issue but they do so only partially in Google Docs. You might have trouble finding out who changed or deleted information incorrectly which often leads to a cumbersome situation where people have a hard time organizing their tasks as opposed to that you saw in the data control language lecture that databases provide a stable structure controlling access permissions and user restrictions. One person can make a change that is visible to everybody instantly. This feature increases efficiency and data consistency when using databases. Considering data integrity and data consistency using databases eliminates duplicate information which is another way to save space and increase efficiency. Look at the customers table. You know a certain first and last name corresponds to a unique email address. So if you know John McKinley has changed his email and you are using a spreadsheet flooded with data you may change the email address once and accidently miss updating the same address in another record. This may lead to inadvertent mistakes. They can be avoided when using a relational database. An accredited user only needs to access the customer's table and change. John McKinley's email address there. Just once not only will this operation save time but it will also anticipate in consistency's. So what we discussed in this lesson are highlights why databases are a better environment for storing and keeping track of data when working with multiple dimensions and large amounts of data. Spreadsheets have their advantages as well. They are an excellent tool that allows us to carry out extensive analysis but for the easier retrieval and updating of data efficiency data consistency data integrity speed and security relational databases are definitely the structure to opt for they can store lots of raw data and are excellent when separating the data from the way it is displayed for analysis. As you saw it would be a good idea to stop trying to visualize data tables in the form of spreadsheets. They are different. Stay tuned for the next lecture where we will offer more database terminology. Thank you for watching.