A brief overview of Excel

- [Instructor] When you think about database tools, you don't always think of Microsoft Excel. And that's too bad, because for better or worse, Excel is one of the most popular database applications in the world. In this movie, I'll give you a quick overview of Excel, and why it's relevant to this problem. To start out, Excel is a spreadsheet program. And that means that its sheets are laid out in a grid pattern. You have your columns that are labeled with letters, and rows which are labeled with numbers. Starting with Excel 2007, Excel worksheets can have up to 1,048,576 rows.

Once you get your data into your worksheet, you can create a query to bring data from an external source into Excel by going to the Data tab. And then clicking New Query, and selecting your source. Once your data is into Excel, it can be used as either a regular range, or as an Excel table. Excel tables are wonderful, because they allow you to work with your data by reference to the table, as opposed to the exact cells that they contain. So if you add a row of data, the Excel table definition updates, and you don't have to change any of your formulas or other definitions.

This is one example of some data that I downloaded through a query. So I have codes for the type of vehicle that was involved in an accident. And you see here that number one is a pedal cycle, number two is a motorcycle with an engine of 50cc's or under, and so on. I can combine this data using formulas. So if I go to Sheet3, you'll see that I have another table that combines Accident_Index data, and that's on Sheet4, along with the Vehicle_Type, Vehicle_Label, which is in the LOOKUP table I showed you a second ago, as well as Accident_Severity which is here on Sheet4.

When I bring all of that data together using LOOKUPs, and I did the same thing here for Accident_Severity looking up in the Accidents table, I can combine everything into a pivot table. My pivot table's on Sheet1, and you see here that I have found the average of the accident severity for each of the different types of motorcycles, as well as the count of the number of accidents. Pivot tables allow you to rearrange your data. So for example, if I wanted to get rid of the count from the Values area, I could drag it to the Field list, and that would remove it from the table.

If I wanted to add it back, I could drag Accident_Severity down below the existing field in the Values area. And then I have the Sum of Accident_Severity, which isn't really useful. So I can right-click any cell on that area, point to Summarize Values By, and click Count. And that gives me the number of accidents right next to the average of the accident severity. So as you can see, Excel is an extremely powerful and versatile program. Is it great for large data sets? Yes, if certain conditions are met, but if you're working with a smaller data set, of say, under about 500,000 rows, then it is an ideal tool.