Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In Excel for Mac 2011 Essential Training, author Curt Frye gives a comprehensive overview of Excel, the full-featured spreadsheet software from Microsoft. The course covers key skills such as manipulating workbook and cell data, using functions, automating actions, printing worksheets, and collaborating with others. Exercise files accompany the course.
Excel worksheets typically contain a lot of data, usually much more than can be displayed on your monitor at one time. Sorting data helps group similar data points together, but Excel still displays your entire list. If you want to limit the data displayed in your worksheet, you can do so by creating a filter. So what allows you to filter data in a worksheet? Well, it helps if you have the data in a list. As an example here, I have three columns: Year, Season and the number of new customers for that season. I also have headers: Year, Season, and Customers. And you'll notice that those cells are formatted in bold, and are centered.
I do that so that these cells are set apart from the data, which you can see just has no boldface and is aligned for that data type. When I do that, Excel can identify the headers versus the data of the list. If you want to filter a data list, that is reduce the number values that are displayed, you can click any cell in the data list, and then on the toolbar, click the Filter button, which is here. When you do, Excel displays the Filter buttons here at the right edge of the header cells. Now I can set a filter for any one of these columns, or more than one at one time.
The first thing I'll do is teach you how to create what's called a selection filter. A selection filter allows you, as the name implies, to select only the data that you want to display. To do that, I will just show data for Fall; so Fall is a season. I'll click the Season column's filter arrow, and then here at the bottom of the Season pane, you see that I've Fall, Spring, Summer, and Winter, or if I want, I can Select All. To create a selection filter, the first thing I do is clear Select All.
And now because I want to display values from the Fall, I select Fall. When I do, you'll see that Excel filters the data list, so now the only data that's visible are those for 2008, 2009, during the Fall season. I should point out that the data isn't gone. It's just hidden. You can tell that rows have been hidden by looking over to left, where you see the blue lines between some of the row numbers. And also you can see that the row numbers aren't in sequence. That means the data is hidden, but it's not gone.
When I remove the filter in a few seconds, you'll see the data comes right back into the worksheet. If I want to get rid of that filter, I can either press Command+Z, which will undo it, or I can click the Clear Filter button, and the data comes back as normal. I will close that dialog box. You can also filter based on the values in more than one column at one time. So let's say, for example, that I wanted to recreate my Season filter, but this time, I'll change it to display Spring. So I clear Select All, click Spring, and you can see that Excel has limited the data just to Spring of 2008, 2009, 2010.
And you can also see that a filter has been applied based on the Season column, because the filter arrow has changed; instead of just being a downward-pointing black arrow, we have a filter icon that looks like a funnel. That indicates that a filter has been applied to that column. So now let's filter by Year. So for the Year, and in this case, I want to have any years that occurred after 2008, so greater than 2008. To the Filter section of the dialog, click Choose One and then click Greater Than.
I can then type the value that I want within this box here. So I want it to be Greater Than 2008, so I'll type 2008, and when I do, the filter is applied. I see the filter arrow has changed; it now has the funnel icon, indicating a filter is active in that column, and I see the values for 2009 and 2010 and Spring. If I want to clear the filter, I can just click Clear Filter, and that gets rid of the filter that I had for the Year column. If I want to clear the filter for the Season, I can click that filter arrow and click Clear Filter, and all of my data comes back.
Filtering a worksheet helps you focus on the data that's most important to you. By temporarily hiding the data you don't want to consider, you remove distractions standing between you and your decision.
Find answers to the most frequently asked questions about Excel for Mac 2011 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
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.