Introduction

Welcome! Advanced Sorting & Ribbon Tips

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Excel Expert Level 9. In this course we will focus on advanced sorting, advanced filtering, and customizing the Excel interface, including the ribbon and Quick Access Toolbar. We will discuss topics such as creating custom lists, sorting by Roman numerals, and using advanced filter features. This course is designed for experienced users who have completed previous beginner and expert levels. You will also learn about the course structure, recommendations for getting the most out of the lessons, and how to participate in student forums to get help or ask questions.

Navigation

Prev: X09
Next: Lesson 01

Keywords

TechHelp Excel, advanced sorting, advanced filtering, customize Excel ribbon, Excel 2010, custom lists, Quick Access Toolbar, filter data, user interface, Roman numerals sorting, macros, user forms, templates, Visual Basic for Applications, integrate Offi

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Excel 2010 Expert Level 9 brought to you by ExcelLearningZone.com. I am your instructor, Richard Rost.

Today's class focuses on three main topics: advanced sorting, advanced filtering, and customizing the Excel interface, including the ribbon.

The last class was designed for use with Microsoft Excel 2010. The lessons on sorting and filtering are the same for pretty much all versions of Excel. Customizing the interface, especially the ribbon, is specific to Excel 2010, but that is only one lesson. If you are here for the sorting and filtering, you will be just fine, even if you are going all the way back to Excel 2000.

This is an expert level class for Microsoft Excel 2010. I strongly recommend that you take all of my beginner classes first, Levels 1 through 5, and the other expert classes, Levels 1 through 8, before taking this class.

I have covered basic sorting and filtering in some of the previous classes, and it would be very helpful if you had those classes first before getting into the advanced sorting and filtering.

My courses are broken up into four different groups: beginner, expert, advanced, and developer.

The beginner courses are for novice users with little or no experience with Excel.

The expert series, which is what you are watching right now, is designed for more experienced users who are already comfortable with Excel. Expert classes go into a lot more depth about each topic than the beginner classes did, and we will cover more functions, features, tips, and so on.

When you have mastered the expert classes, move up to the advanced lessons. You will learn how to build macros, build user forms, create your own templates, and many more advanced features that not everyone will use, but they really add enhanced functionality and professionalism to your spreadsheets.

Finally, my developer series is designed to teach you how to program in Visual Basic for Applications with Microsoft Excel. This will allow you to create Excel-based programs for your users, automate your spreadsheets, and integrate Excel tightly with the other Office applications.

Each of my series is broken down into different levels. For example, the beginner series contains five different levels, which you should have taken previously. This is Level 9 of the expert series. Each level teaches you new and different topics in Microsoft Excel, building on the lessons learned in the previous levels.

When you finish all of the expert classes, move up to the advanced series, and then finally the developer series.

Now let's take a more detailed look at exactly what we are going to learn in today's class.

In lesson one, we will begin by learning some advanced sorting techniques.

In lesson two, we are continuing with advanced sorting techniques, including creating custom lists and sorting based on things like Roman numerals.

In lesson three, we are going to learn more about customizing the Excel user interface. We are going to review customizing the Quick Access Toolbar, and I will show you a couple of new tricks with that. We are going to learn how to customize the ribbon by modifying the existing tabs and creating our own custom tabs.

In lesson four, we are going to take a closer look at filtering data in our spreadsheets.

In lesson five, we are continuing our look at filtering data.

In lesson six, we are going to take a look at advanced filtering, which will allow you to create filters right on your sheet and filter the data in place.

If you need help with the topics covered in today's lessons, please feel free to post your questions in the Excel Interactive Student Forums.

If you are watching this course using my custom video player software, or online at my Web Theater, you should see the student forum for each lesson appear in a small window next to the class videos if you have an active internet connection.

Here you will see all the questions that other students have asked as well as my responses to them and the comments that some of the other students may have made. I encourage you to read through these questions and answers as you start each lesson. Feel free to post your own questions and comments as well.

If you are not watching your lessons online, you can still visit the student forums later by visiting ExcelLearningZone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then replay the lesson from the beginning and follow along with my examples. Actually create the same spreadsheet that I make in the video. Build the spreadsheet with me step by step.

Do not try to apply what you are learning right now to other projects until you have mastered the sample spreadsheet. If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum.

Most importantly, keep an open mind. Excel might seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

Quiz

Q1. What are the three main topics covered in this class?
A. Advanced sorting, advanced filtering, and customizing the Excel interface
B. Basic data entry, chart creation, and mail merges
C. Conditional formatting, pivot tables, and protecting sheets
D. Formulas, macros, and printing options

Q2. Which lesson specifically deals with customizing the Excel user interface and ribbon?
A. Lesson one
B. Lesson three
C. Lesson five
D. Lesson six

Q3. According to the instructor, before taking this expert class, students should:
A. Complete all beginner and previous expert level classes
B. Be able to type at least 40 words per minute
C. Have experience with advanced Excel macros
D. Have completed the developer series

Q4. Advanced sorting techniques in this class include:
A. Creating custom lists and sorting Roman numerals
B. Using only alphabetical sorting
C. Sorting by color only
D. Sorting by file size

Q5. What should students do right after watching each lesson for the first time?
A. Skip to the end and take the quiz
B. Immediately apply lessons to their personal projects
C. Replay the lesson and follow along with the examples
D. Ask for a refund

Q6. What is the recommendation if a student gets stuck or does not understand something?
A. Give up on the course
B. Watch the video again or ask for help in the student forum
C. Skip to the advanced courses
D. Contact Microsoft support directly

Q7. Which version-specific lesson is mentioned as unique to one version of Excel?
A. Sorting data by multiple columns
B. Creating and customizing the ribbon
C. Using filter drop-down menus
D. Performing basic math operations

Q8. Where can students post their questions related to the course?
A. Excel Interactive Student Forums
B. Only through email to support
C. In the comment section on YouTube
D. No place is available for questions

Q9. What order should students progress through the ExcelLearningZone.com courses?
A. Start with advanced, then developer, then beginner, then expert
B. Start with beginner, move to expert, then advanced, then developer
C. Start with expert, skip advanced, and move to developer
D. Only take the developer series

Q10. What is NOT recommended when learning from this course?
A. Creating the sample spreadsheet alongside the lesson
B. Trying to apply what you learn to other projects immediately
C. Watching each lesson through once before practicing
D. Using the student forum for questions

Answers: 1-A; 2-B; 3-A; 4-A; 5-C; 6-B; 7-B; 8-A; 9-B; 10-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Excel Learning Zone focuses on expert-level techniques in Microsoft Excel 2010, specifically in Level 9 of my training series. In this class, we're going to work on advanced sorting, complex filtering, and learning how to customize the Excel interface, including personalizing the ribbon.

Just to clarify, most of what we'll cover in sorting and filtering applies to almost every version of Excel, even versions as old as Excel 2000. Customizing the ribbon, though, is specific to Excel 2010, but it's only a small part of this class. If you're mainly interested in advanced sorting and filtering, you're in the right place regardless of your Excel version.

This course is intended for users who already have some experience with Excel. If you haven't already taken my beginner levels 1 through 5, and expert levels 1 through 8, I highly suggest starting there because we build on concepts covered previously. Basic sorting and filtering were introduced earlier, and it will really help if you are comfortable with those before moving onto the more advanced material here.

Let me explain how my curriculum is structured. The courses are broken down into four groups: beginner, expert, advanced, and developer. The beginner series is for people who are brand new to Excel. The expert classes, like this one, are for users who already know their way around the basics and are ready for more in-depth topics, more detailed functions, features, and techniques.

Once you are confident with the expert material, you'll be ready to advance further. In the advanced series, you will learn about creating macros, building user forms, designing custom templates, and lots of other powerful features that let you take Excel to a whole new level. For those interested in programming, the developer series covers using Visual Basic for Applications, or VBA, in Excel. This will show you how to automate tasks, develop user-oriented applications, and integrate Excel with other software in the Microsoft Office suite.

Each course level is progressive. For example, the beginner series has five different levels, which should be completed before tackling the expert series. Right now, we're working with Level 9 of the expert classes, with each level introducing new skills that build on everything you've learned so far.

Once you've mastered everything in the expert series, the next step would be to move on to the advanced topics, and finally to the developer classes if you wish.

Now let's talk about what this particular class covers. In the first lesson, we'll start with advanced sorting techniques. The second lesson keeps going with this topic, looking at how to sort using custom lists and even how to sort items written in Roman numerals. Lesson three is all about making Excel work for you by customizing the user interface. We'll explore tweaks to the Quick Access Toolbar and look at modifying and creating your own ribbon tabs.

Then we move on to filtering. Lessons four and five are devoted to filtering data in detail. In lesson six, we'll introduce advanced filtering, which lets you set up and apply filters right on your worksheet, so you can work with your data where it sits.

If you need any extra help with today's topics, remember that you can post your questions in the Excel Interactive Student Forums. If you're viewing this course on my custom video player or through my Web Theater online, you'll see a forum window next to each lesson as long as you have an internet connection. There, you can read questions from other students, my answers to them, and comments from the rest of the community.

If you're watching these lessons offline, you can always visit the forums later by going to ExcelLearningZone.com/forums.

For the best results with this class, my advice is to watch each lesson from start to finish before trying anything in Excel. Then, watch the lesson again and follow along with me step by step, building the sample spreadsheet as I do. I recommend focusing on learning the sample first before trying to apply these new skills to your actual work projects. If you run into anything confusing or get stuck, don't hesitate to watch the lesson again or ask for help in the forums.

Above all, stay open minded. Excel can seem a bit daunting at first, but once you understand how things work, you'll realize just how manageable and useful it can be.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Advanced sorting techniques
Creating custom lists for sorting
Sorting based on Roman numerals
Customizing the Quick Access Toolbar
Customizing the ribbon
Modifying existing ribbon tabs
Creating custom ribbon tabs
Filtering data in spreadsheets
Advanced filtering in place

Article

Welcome to this expert-level tutorial on advanced sorting, filtering, and customizing the Excel interface. This lesson is designed for users who are already comfortable with the basics of Excel and are looking to expand their skills even further. If you are new to Excel, it is strongly recommended to start with beginner material to build a solid foundation before jumping into expert features.

Let us begin by diving into advanced sorting. Sorting data is one of the most powerful tools you have for organizing information in a spreadsheet. You may already know how to sort by a single column in ascending or descending order, but sometimes you will need to sort by multiple columns or in a custom order. For example, consider a sales report where you want to first sort by region, then by sales rep name. To do this, select your data, go to the Sort dialog, and add multiple levels. Set the first level to Region and the next to Sales Rep. Excel will sort by region first, then by the sales rep within each region.

In addition to multi-level sorting, you might encounter cases where you want to define your own order instead of relying on Excel's built-in options. Imagine you have a list of project phases: Planning, Design, Development, Testing, and Deployment. If you sort these alphabetically, it will not reflect the actual workflow. To address this, create a custom list. Go to the Excel options and find the Custom Lists section. Enter your phases in the correct order, save the list, and then use it as a sort order. Now, whenever you sort that column, Excel will follow the order specified in your custom list.

Another interesting scenario involves sorting non-standard data, such as Roman numerals. If you have a column with values like I, II, III, IV, and so on, Excel will sort them alphabetically by default, which is not what you want. To sort Roman numerals correctly, you can create a helper column. In this column, use a formula or a lookup table to translate the Roman numerals into their numeric equivalents. For instance, if A2 contains a Roman numeral, write a formula that converts it, and then sort your data by this helper column. Once sorted, you can hide the helper column if you do not need to see it.

Moving on to customizing the Excel interface, many users want to streamline their workflow by modifying the Quick Access Toolbar and the ribbon. The Quick Access Toolbar is the row of icons at the top, and you can add your most-used commands there for quick access. Simply click the small dropdown arrow next to the toolbar, select More Commands, and add the features you use most often. You can also rearrange them in the order you prefer.

Customizing the ribbon allows you to tailor Excel even further. You can add new tabs or groups, move commands around, or even create a tab specifically for a project or department. To do this, right-click the ribbon and choose Customize the Ribbon. In the customization window, you will see all the existing tabs and groups. You can add a new group to any tab, then add the specific commands you want within that group. You can also create a completely new tab and fill it with the commands you find most useful for your workflow. This makes common tasks just a click away.

Now let us focus on filtering data. Filtering helps you temporarily hide information that you are not interested in so you can focus on the data that matters. Start by selecting your data and turning on filtering from the menu. You will see small dropdown arrows at the top of each column. Click these to filter by values, by text, or by criteria such as numbers greater than a certain amount. You can also combine filters on multiple columns to narrow your results even further.

For advanced filtering needs, Excel offers an Advanced Filter feature, which lets you define complex criteria and filter your data in place or copy it elsewhere. To use advanced filtering, first set up your criteria range somewhere on the sheet. For example, if you want to filter for sales over 1000 in the East region, your criteria range will include the headings and your specific criteria just below them, such as Region = East and Sales > 1000. Go to the Advanced Filter command, specify your data range and your criteria range, and choose whether you want to filter the list in place or copy the results to a new location. The filtered results will match only the records that meet all the conditions specified.

If you are interested in automating these tasks or creating custom solutions, you might consider learning Visual Basic for Applications, or VBA. With VBA, you can write your own code to sort, filter, and manipulate data programmatically. For example, to sort data with VBA, you might use code like this:

Sub SortData()
Range("A1:D100").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
End Sub

This simple macro will sort the range A1 through D100 by the second column in ascending order, assuming your data includes headers in the first row.

To get the most out of this material, it is a good idea to first read through the explanations and examples, then try them yourself with a real spreadsheet. Practice sorting and filtering data using the techniques described here. When working on your own projects, always start with simple sample data to make sure you understand how the feature works, and then apply it to your real work. If you run into any difficulties, revisit the examples or ask for help from a community or forum dedicated to Excel.

Advanced features like multi-level sorting, custom lists, advanced filtering, and interface customization are powerful tools that can help you harness the full potential of Excel. With a bit of practice, these skills will make your spreadsheets more organized, efficient, and easier to use. Remember to keep an open mind and experiment with new features, and soon you will find that working with Excel becomes faster and more intuitive.