Bureau of Transportation Statistics - Microsoft Excel Tutorial

- [Instructor] Even though the United States economy has shifted to information technologies and away from manufacturing, transportation still plays a significant part of our economic activity. The Bureau of Transportation Statistics, and which is part of the Department of Transportation, gathers data on highway, water, rail, and intermodal transportation in the US. If you work in manufacturing or need a baseline for national and international transportation trends, this website provides the data you need to make good decisions. You can find the Bureau of Transportation Statistics at bts.gov. The website is organized in an interesting way in that it gives you several different ways to get to your information. So for example, you could go by topics and geography, airlines and airports, or you could go international or by state. You can also go by mode, aviation, highway, rail, marine, and so on. You can also look at specific statistical products and data by moving over that header. So you have an A to Z Index, Airline Information for Download, and so on. Let's say that I want to look at TransBorder Freight Data, such as between the US and Canada, or the US and Mexico. I'll go over and click TransBorder Freight Data. And you can see here that the data is broken down by port and commodity. And in this case, port can also mean a crossover. So for example, here, at the northern part of western Montana, northwestern Montana at Sweetgrass and just down form Lethbridge in Alberta, you'll find that crossover. And you can also find information broken down by state and commodity or by state and port. So there are quite a few different ways that you can look at your data. I'll scroll down and take a look at Port and Commodity with the dashboard. The data's come up and you can see that we have quite a few different indicators in blue. There is an increase or growth in trade. And yellow or orange indicates a decline. So if I scroll in and look at Sweetgrass, Montana, and scroll in, wait for the map to adjust, you can see that there has been a decline in trade and also that this is a fairly small area. On the other hand, if I scroll back out, I can look over in the Chicago area or the Detroit area, rather, I can see that there has been a fairly large decrease in trade between the US and Canada. Also note, over on the side, that we have a start date of February 2019 and an end date of February 2019, and we're comparing to the previous year. If I click that control, I can look at the previous quarter. And the map will recalculate and we see how the changes have had an effect. If I want to break down the data by commodity, I can go to Ports by Commodities, and I will see a graph that summarizes my data there. So you can see that the vast majority of freight moves by truck. About 1/5 of that amount moves by rail, and so on. One final note, if I want to download the data from a particular view, I can go to the bottom right corner of the screen, click Download, from here I can select from my different file formats. In this case, I'll click Data. Depending on where you access the data from, it's possible not all of these options will be available to you. Now, I can either download the full data set, which would take a long time to accumulate because this is a fairly large summary, or I can click Download all rows as a text file, and it downloaded just the data that we saw to an Excel file. And if I go down to my Downloads bar, click the up arrow and click Open, we'll see the final in Microsoft Excel, including in this case, conditional formatting and indicators whether there is a negative trend, or a positive trend, or no trend. So as you can see, there is a lot of data available to you. If you have time to add a quick internet connection, you can download large data sources, otherwise I recommend that you make due with a summary.