How to speak to Data

Using SQL with Microsoft Excel

Published on 11/03/2021 by Donata Petrelli

Reading Time: 4 minutes

Who among us doesn’t ask Google where the best pizza in town is when we find ourselves in a new location?

Or who doesn’t ask Alexa what the weather will be like over the weekend before deciding to leave?

If we’re used to talking to data on the web … then why should we be surprised that we can also talk to our data that is, much more simply, within an Excel spreadsheet?

Don’t worry … I haven’t lost my mind over algorithms J. It’s that there really is a possibility to talk to our data and ask it to find the answer to our needs.

In this case, instead of asking a vocal question, we use a written language, simple and very fast, to ask the question to our Excel sheet which, as an answer, will show the result!

This language is SQL. Many people will have heard it before or even used it. It is often associated with operations on sophisticated Databases, for the management, interrogation and manipulation of data. In reality it is possible to use it, easily and by all, experts or not, within Excel.

Do you think this is not possible?

Okay, so I want to give you an example.

“Excel: what shoes am I wearing today?”

You all know by now how much I love shoes. But you don’t know what is the criteria with which I choose daily the pair to wear.

For my convenience I have cataloged all my shoes within an Excel spreadsheet like this one in Figure 1

This is the starting point for my daily decision. Of course every question I ask starts with the fields in my Excel sheet.

So I think about where I’ll need to go and what outfit I’ll be wearing, to evaluate the type of shoe. Then I evaluate my mood … high heels always help cheer me up! Finally the weather and temperature outside, if it’s nice weather well open too … otherwise I would avoid.

Let’s assume that I have to go out for a social gathering and I decide to wear a black dress. It is evening and the weather is clear.

After my needs analysis, I can ask my excel sheet this question: “Select shoes from my file that are decoltè, the shape is pointed, the color is black.”

There, I’ve just spoken to my data. But now I have to translate this question into a language that Excel can understand: SQL! Used precisely for queries in databases, it is very simple to the point that it resembles the English language!

So, in SQL syntax, my question becomes:

SELECT * FROM [List_Shoes$] WHERE [CATEGORY] = ‘Decolletés’ AND [POINT_SHAPE] = ‘Pointed’ AND [COLOR] = ‘Black’

Simple, right? Told you so 🙂

Now let’s see in detail the steps in Excel.

Using SQL with Excel in practice

First let’s start from an empty Excel sheet and select the first cell A1.

Then click on “Data/Get External Data/Existing Connections” as in Figure 2

From the “Existing Connections” window click on the “Browse for More…” button to select the DB_Shoes.xlsx file. Then select the table to query as shown in Figure 3

Set options: in a table and starting from cell A1, as shown in Figure 4

Once the data has been imported, click on the “Properties” button from the “External Table Data” menu

From the “Properties” window, click on the button to the right of the sheet name to see the “Connection Proprieties” window appear as in figure 6.

In the “Definition”, write the query:

SELECT * FROM [List_Shoes$] WHERE [CATEGORY] = ‘Decolletés’ AND [POINT_SHAPE] = ‘Pointed’ AND [COLOR] = ‘Black’

within “Command text:” as shown in Figure 7

Click on OK to get the result shown in Figure 8

The answer is in!

Conclusion

Thanks to this simple question executed with the SQL language I immediately found the answer to my doubt about which shoe to wear for the meeting … now all that remains is to choose between Plateau or not 🙂

For those who want to deepen the topic on the use of the plateau can read my article “The invention of the Plateau“. For those who want to deepen the use of SQL and its syntax there are many resources on the net.

The purpose of this article is certainly not to provide a guide on SQL language but to show how thanks to it is actually possible, simple and fast to talk to our data 🙂

I hope you found it inspiring for your own data analysis. I also invite you to sign up for my free newsletter to stay updated and continue to follow my posts.

Subscribe Free Newsletter