Pivot Tables With Web Data in Excel

How would you import online data to your spreadsheet?

This may look like a trivial question, but it's not at all. I bet the easy and most common approach would be directly copy/pasting the data to your spreadsheet. But, please let me ask you one question: What if the data gets updated? 

In this post, I will be sharing with you one different approach to get data from Internet. This is done by executing a query direct from your spreadsheet. By this way, every time your file will be opened, the data will  be refreshed and your information will be always up to date.

I used this Excel feature several times and created really powerful tools. I hope you'll find it useful too. After this brief introduction let me jump direct to the step by step process.

Spoiler alert!! You can download the file I used for this exercise direct from this link.

Let's go with the step by step

We will follow a 3 step approach:

• First we need to identify the source with the data we want to load into our spreadsheet.
• Second we have to set up the query to load the data.
• Finally we can start operating with the data we got.

With these comments in mind, let's go with the explanation. 

Step by step process for loading and operate with web data in our Excel spreadsheet

• We need first to identify the source with the data we want to load. In this case we will load the chart with the top 250 movies from IMDB. Link here.

• Then we open a new Excel spreadsheet and go to Get and Transform data From Web.

• A new window will appear asking for the URL we want to use for loading our data. We can keep the Basic configuration.

• Once we click OK, a new window will appear with the tables identified in the selected URL. In this case we can see 2 tables listed (Document and Table 0). The one we need is Table 0, so we select it and click Load. We could also check the Web View if we want.

• The data will be loaded to our spreadsheet and will see the films listed as they were online. We can appreciate that on the right side we have the query we created. 

• Before moving forward, we need to configure the query to be refreshed every time we open the Excel file. To achieve it, we right click on the query and will see the Properties. We must select the "Refresh data when opening the file" option.

• At this point we can start working with the data, but first we will rename the table so will be easier to work with later. We will name it IMDB.

• Next step is to create the Pivot Table. We go to a new Sheet and just insert a new Pivot Table.

• In Table/Range we must insert the Table Name we created (IMDB).

• For this Exercise we want to assess how many films are grouped by rank. So, we will work with IMDB Rating in the Rows and Count of Title in the Columns.

• We will get the data grouped by IMDB Rating like listed below.

• But, as we pretend to present a report with this data, we need it to be more concise. So, right click on any row and select Group. Then in the Group set up, we group by 0.5.

• Now we have the data ready to be used for our report. Let's create a Pie Chart. We just select the data from the Pivot Table and Insert a 3D Pie Chart (this could be adapted to your requirements).

• Last step of our exercise is just closing the file and reopening to confirm the data refresh is taking place. Few questions will arise asking you to grant access:

• After allowing the required accesses you can check that the query is being executed every time you open the file.

You can download the file I used for this exercise direct from this link.

So, what about you? Did you know about this Excel feature? Do you think this method would improve your productivity? Please, feel free to share with me your thoughts about this topic by adding a comment to this post.

Video explanation