Viewers: in countries Watching now:
Whether you're a novice or an expert wanting to refresh your skillset with Microsoft Excel, this course covers all the basics you need to start entering your data and building organized workbooks. Author Dennis Taylor teaches you how to enter and organize data, perform calculations with simple functions, work with multiple worksheets, format the appearance of your data, and build charts and PivotTables. Other lessons cover the powerful IF, VLOOKUP, and COUNTIF family of functions; the Goal Seek, Solver, and other data analysis tools; and how to automate many of these tasks with macros.
When you're working with large amounts of data as in the list here which has over 700 rows, you don't always want to see all of the data, we might want to see just the Full Time people, or just the people in certain departments, or just those who have been here so many years or have a certain Job Rating. The filtering capability of Excel works smoothly and easily. We're on the Filter worksheet in the workbook called 12-DatabaseFeatures. You can start filtering from the HOME tab by choosing the second button from the right and then choosing Filter or on the DATA tab in the Ribbon, simply click Filter.
And that gives us Filter arrows in the top row of our list. If we want to see just the Full Time people, we'll click the arrow for Status and this gives us a complete list of all possible entries within the column. And we can begin here by un-selecting all of them and simply choosing Full Time and then OK. We are only viewing the Full Time people. In the Status bar on the lower left-hand corner, it reminds us how many records we're seeing, 393 of 741 records found.
Recognize that when a field is being used for filtering purposes, the icon there is not simply an arrow but an arrow with a filter. And while we're looking at this list, if we only want to see the Full Time people who have a Job Rating of 5, our highest level, we'll go to the Job Rating column, click the arrow here,un-select all the entries and then simply click on 5. And now we're seeing a much smaller list, it's 102 out of 741 records. We're only seeing the people who have a Job Rating of 5 and the Status Full Time.
And if we want to narrow this further, we could, maybe we want to see people in certain Departments and it can be more than one. We'll un-select them all, maybe we want to see those people who have a Full Time status and Job Rating of 5, but only those in certain Departments, maybe the Environmental Health & Safety, Human Resources and possibly a couple of others here. It's your call, it's your choice; you click OK. And now instead of 102 records we're down to 14 records. And once again, recognize that the icon changes when that particular field is being used as the source of filtering.
If we want to bring back all the records, we could either click Filter twice or perhaps more directly, simply click the Clear button, the Filter arrows stay there but we are no longer using any column for filtering purposes. Sometimes you want to simply look for data. If we click in column A and we're looking for somebody named Sam, you might start by just clicking in the panel right here typing s. Now, although it's not obvious, all the entries below contain the letter s. Now they contain s-a; now s-a-m and there are no matches there. How about James? We know there is at least one there, we can see it down below and it's not case sensitive.
And each time as I add a letter here, the list below, although we can't see it all, is getting shorter and shorter. So if we were to click OK now, we would only see the word James as it appears there multiple times. Let's click Clear and bring back all of our data. Excel does recognize what kind of field you're dealing with. If we click the drop arrow for Department, recognize that we see the term Text Filters. So for example, we could be looking here for only those Departments that contain a certain word. The options that we see here are related to Text Types Entries.
Column H has salaries; we might want to see the salaries in a certain range. Because this is a numeric field, we see the term Number Filters and a different set of choices out here. So we could look at all of the salaries between a certain range, for example all of those that are greater than or equal to 40,000 and less than 50,000, example here less than or equal to or maybe just less than, we've got some variation there. So now we're about to see just the salaries within that range.
How many people are within that range; 134 out of our 741 records and here too we could be looking at other fields at the same time. To bring that back again, simply click here, we could also simply remove the Filter here this way and how about a Date Field here, drop arrow, Date Filters, this gives us some amazing capability. I think for hire dates some of these wouldn't be very obvious choices, but if these were sales dates think of how handy this would be if we were dealing with transaction data to just see the data from Last Month or Yesterday or Last Year and furthermore, if we looked farther down All Dates in the Period.
We could isolate the dates by month, no matter what the year is, let's get all the February entries here and we see all the people hired in February in different years. May be we'll use this to create the anniversary list. Drop arrow again, Date Filters and also don't overlook All Dates in the Period where we see it by Quarters as well. And so there are some very strong capabilities here in Date Type columns. Once again we could Clear Filter from Hire Date. Filtering is fast and easy. Recognize too that when you do have a filtered list, let's create another one maybe this time we'll choose just the Hourly people who have a good Job Rating.
We'll choose Hourly only by Unselecting all, then choosing Hourly, click OK, 54 of those, Job Rating 5, Unselect all, choose 5, click OK and now we're down to just 11 of these. If we copy this list, if we simply highlight and copy, Right-Click and Copy, automatically we are only getting this visible data. If I click plus at the bottom to create a new sheet and I paste this list out here, Right-Click Paste or possibly Ctrl+V, we've got just the visible data.
In a filtered list you never worry about that, we can of course print this too. Keep in mind too that whenever you're using filtering you'll see blue rows to indicate that we're not seeing all the rows. So this is a feature that allows us to quickly view just the data we want and to easily get back all the data simply by choosing Clear.
There are currently no FAQs about Excel 2013 Essential Training.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.