Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
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.
Most sorting operations arrange data into numerical or alphabetical order. Well, what do you do if your data doesn't make sense if it's sorted into alphabetical order? In that case, you can create a custom list, which you can use to sort your data. In this case, I have a data list that shows my number of customers for each season of the years 2008 through 2010. If I want to sorts the data by the values in the Customers column, I can do that by clicking the down arrow and then clicking Descending, which will put the highest value on top. If I want to undo the sort, I can press Command+Z, but in this case let's say that for whatever reason I am not able to do that.
Maybe the worksheet got saved in the state and then closed and then reopened, and there is no way to undo the operation. In this case, if I want to sort by Year and then by Season, I can create a multilevel sort. Going up to the Sort button, clicking Custom Sort, deleting the rule that I have here right now by clicking the Remove Level button, and then adding a sort that sorts by the Year, Smallest to Largest, which is correct, and then a second rule where I sort by the Season, and I'll just leave it on A to Z for now.
When I click OK to sort, you'll see that I have 2008, but the seasons are out of order. This year we're going from Fall, to Spring, to Summer, to Winter, which doesn't make any sense. If I want to be able to sort by season, or any other list, then what I need to be able to do is create a custom list. In other words, I need to put these four values into a definite order that Excel can identify, and that I can use in my sorting. To do that, you open the Excel Preferences dialog. So I will press Command+Comma. I could also go through the Excel menu and click Preferences, but once I'm here in the Preferences dialog, I can click Custom Lists, and from here, I can create a new list.
In this case, I have a very short list of entries, so I will just type them in. I have the order of the seasons, which is Spring, Summer - and I am just pressing the Return key every time I put in the list item - Fall, and Winter. Just verify my spelling quickly, make sure everything is in order, and everything is correct. That looks good, so I will click Add, and when I do, the list appears here in the Custom list. You'll notice that the existing lists are January, February, all the way through December, so those are the months.
You have the abbreviations for the months, then you have the days, Sunday through Saturday, and then Sunday, Monday, and so on, in three-letter abbreviated form. Everything looks good, so I'll click OK, and now when I sort my data, I can use that custom list. So I will click the Sort button's down arrow again, click Custom Sort, click the Season rule, so I'll sort by season, but now instead of sorting from A to Z, I'll click the arrow and select my custom List.
That's here at the bottom: Spring, Summer, Fall, Winter. Click OK. So my rules look correct, by year and then by season. Click OK again and Excel sorts my list into proper order. One other item that I want to mention is that any time that you have a custom list, you can use that to drag the fill handle to extend the series. So let's say, for example, in cell E3 I type Spring, and I want to have the sequence of cells that I have the different seasons in order, Spring, Summer, Fall, Winter, and so on. If I click this cell, move the mouse pointer until it turns into a black arrow - which you do by hovering over the bottom right corner of the cell and then clicking and dragging - I get Spring, Summer, Fall, and Winter, the sequence that I'd identified before.
Creating a custom list let's you sort your data using orders that are specific to your business. Creating a list of seasons in the order Spring, Summer, Fall, and Winter helps you sort data by time, with the added benefit of being able to extend the list using Auto Fill.
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.