Hello there. I know all of you are excited to get started with the course. I wanted to just start out by demonstrating some examples of what you guys are going to be capable of doing by the end of this course. So I've got several examples kind of show what you kind of going to be doing. Everything I'm showing here, you're going to be able to do by the end of part 1 of everyday Excel. You might think that you have a long way to go and by looking at these example, you might be a little bit intimidated, but we're going to take tiny little steps. Each little screen-cast is going to teach you something new and by the end of the course, all of this will seem pretty manageable. The first example I want to show you is how we can maybe have the name. So we've got Last Name, First Name. I'm going to show you some formulas that we can extract just the Last Name from Last Name, First Name. You can also do the same thing for First Name to sort of isolate or separate First Name and Last Name. Then I've also got another example here where we can concatenate or join. So you guys are going to learn all about these different formulas and functions. We can join First Name and Last Name into an e-mail address. You also notice here that I made those lowercase. So this is one example of what you'll be able to do in the course. You're also going to learn the basic financial functions in Excel. This example, you borrow 20,000 dollar to be repaid using 15 year payoff period, your loan charges, and annual interest rate. This question is asking for the monthly payments. So you're going to be able to use Excel formulas knowing the principle, the loan amount, the annual percentage rate and the number of years you'll be able to calculate monthly payments. If you wanted to determine how much money a savings account is going to earn, you can also use Excel functions to do that. For example, here, if you invest 5,000 dollar at two percent annual percentage rate over 10 years, then after that time you would have earned 1095 dollar. Then I've got another example here. If you want 20,000 dollar in five years and you can make payments of 200 dollar each month with a certain annual percentage rate compounded monthly. How much do you need to put down today? I will also show you how to calculate the answer to this in this course. In this example, I have applied a filter. So I've got a bunch of students, their student ID number and there section number. I'm going to show you how you can use filters. I can go here and I can select the section number is just going to show the students in that section. You can also apply a filter over here. We can, for example, just filter by section 104 and it brings those down to only the students that are in section 104. Here's another example of an Excel table with filters in all of the columns. You can filter by, for example, ship dates. You have lots of different options. For example, you can filter for dates between two different values. For example, batches or records between February 15th and August 15th of 2018. We can apply that filter. You can clear these filters and we can filter by the ship time. Maybe if we want to filter for ship times that are less than five days, we can do that. So that's just another example of how we can apply a filter, and you're going to learn all about filters in this course. You will also learn about data validation. Data validation as an example. Here we can validate these cells. We can make it such that it will only accept a certain set of names that are over here on the right. I've also set this up and you're going to learn how to do this. That in this column, in the right column, is going to look up the employee to the left over here in sort of our pay schedule to determine the rates. Then the user will put in the hours here, whether or not it's overtime. We also have data validation there. It's either over time or it's not. The spreadsheet's going to calculate the pay. Then we've got the total pay down here for all of our employees. So this actually uses quite a bit of functions you see up here in the formula bar, we have an if statement over here and the rate column. I also have something known as the VLookup function, and you're going to learn all about these functions and much, much more. Another example of how we might use a Lookup function is shown here. We have students, their student ID number, their scores on homework 1, homework 2, and exam 1, I've got data validation over here in this cell. I can drop down and choose the various students. It's going to look up their student ID number, their homework 1, homework 2, exam 1 scores. It's not that big of a deal for such a small data-set here. But you can imagine with many, many students and many, many assignments, this would be quite useful. We can also look by student ID. So I can select the student IDs here, and it's going to look up the name and then the assignment scores there. So you're going to learn all about how to use lookup functions in this course. You're going to learn all about how to visualize data and how to represent data in various tables and charts. Here, I have an example where I have cookies that are being sold by various sellers. We have buyers that are buying those cookies. We have the actual cookies, we have the quantities and so on and total sales. We can build these PivotTables. So I've inserted a PivotTable. Here I can drag down into this rows field, the seller. I can drag in the buyer over here to the columns. I can add in the quantities down here to the sum values field. What that does is it creates a PivotTable that kind of shows the relationship between sellers and buyers, tells us how many boxes of cookies each of the buyers has bought from the sellers. We can also put in cookies down here as a subset in rows and it's going to kind of itemize which of the cookies were sold to each of the buyers by each of the sellers. So there's lots of different things you can do with PivotTables. Finally, one of the last things you're going to learn is how to visualize data in graphical form. So here we have a scatter plot. I'm going to show you as an example, how you can make these dynamic plots where we can select or deselect various items and various series from the plot. So depending upon which of these four is selected, is just going to plot those series. Then we can check those two, add them to the plot. Again, I realized that some of this might seem very intimidating, but we're going to go through this fairly slowly. We are going to crawl, then walk, then run. We're going to start slow. Each little screen-casts, we're going to learn something new. By the end of five weeks, you're going to be amazed at what you can do in Excel. Have fun, and I wish all of you the best of luck.
