Excel is a great tool for working with data. Sorting and filtering operations are easy and intuitive, and aggregating data by getting subtotals and averages can be done at the touch of a button. If you need to manipulate the data in any way, there’s a ribbon with all the functions categorized and available through drop-downs. Working with data is easy in Excel. But depending on where the data comes from and how you need to display it, there might be better options.
Excel, like many other popular and widely used applications, can get the data you need to work with from a database. Again, there’s an easy to use button on the Data ribbon. Work your way through a wizard, selecting a table or two, and the data appears, with each table on its own worksheet. Then you can get to sorting, filtering, and aggregating. Despite the ease of Excel, there are downsides to this approach. First, each table opens in a separate worksheet. If the data is related, you need to create references or formulas and maybe even use a cool add-in called Power Query to merge the data together. If you don’t want all the data from all of the tables, it can take some time to get just the rows and columns you want displayed properly. Second, Excel cannot handle an unlimited amount of data. As storage becomes ever cheaper, it has become a popular strategy to collect as much data as possible and then to work with it later. Excel limits a worksheet to a little over a million rows and each column to 255 characters. Databases have no such limitations. So it’s possible a database table won’t fit in Excel.
Databases can come in a variety of shapes and sizes. But virtually all support the ability to write statements to query the data, known as Structured Query Language (SQL) queries. Writing a SQL query allows you to specify exactly what columns and rows you want, including columns and rows from two, three, or even dozens of tables at a time. The rows returned are displayed as a single result set. You can sort and filter and aggregate right in the statement, and with a bit of practice may even be faster for you to type out than to import and then manipulate the data in Excel. Not to mention potentially faster to execute, since your computer only has to display the data you want displayed, not all of the rows you filtered out or used in calculations.
SQL queries can be written in a number of programs, and usually even saved if there are some you’d like to reuse. SQL queries are often the basis for reports you view, or do the behind-the-scenes work in other popular technologies, like SharePoint. So if you work with data regularly, or are looking to start or change careers in information technology, learning how to write SQL queries can be a great help.