Excel 2010/2013
Excel 2007
Excel 2003
Tips & Tricks
Excel Forum
Course Index CIG Excel Book
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Advanced Level 2    dismiss
 
 

< Previous: Excel Expert 8

Next: Excel Expert 10 >

Excel 2010 Expert Level 9
Microsoft Excel Expert Tutorial - 1 Hour, 5 Minutes

 
 
This is the ninth class in our Excel 2010 Expert Series. It picks up where Expert Level 8 left off. This course covers several different topics, including:
 
 - Advanced Sorting Techniques
 - Creating Custom Lists
 - Customizing the Excel Interface
 - Modifying the Ribbon Menu
 - Advanced Data Filtering
 - More...

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first lesson of this course (free of charge), or scroll down for more information.




 

Excel 2010 Expert Level 9
Description: Excel 2010 Expert Level 9
Versions: Microsoft Office Excel 2010
Pre-Requisites: Excel 2010 Expert Level 8
Running Time: 1 Hour, 5 Minutes
Cost: $24.99


This course is for the expert user who has good experience with Microsoft Excel or has completed the five courses in our Beginner Series plus the previous eight Expert Level classes. This course covers several different Excel features.

We will begin by learning advanced sorting techniques. You will learn how to create a custom sort, change the sorted columns, add/delete sort levels, learn how to sort by color, icon sets, and more.

 

Next you'll learn how to sort based on a custom list (such as month names) and even how to create your own custom list. We'll create a custom list representing Roman Numerals and sort by that. You'll also learn about special alphanumeric sorting (such as a1, a2, ... a10, etc.)

 

You'll then learn about customizing the Excel interface. You'll see how to add and remove commands from the Quick Access Toolbar, how to hide tabs from the Ribbon menu, and how to create your own custom Ribbon tabs, groups, and buttons.

 

In the next lesson, we'll learn about data filtering. You'll see how to turn filtering on and off, filter on multiple columns, clear filtering criteria, and reapply the filter for new data.

 

Next we'll learn about AND and OR conditions in filters and exactly how they work. We'll learn about the different kinds of text, numeric, and date filters. We'll review wildcards and see how they work with filters. You'll see the various kinds of "top 10" filters (top 10 items, top 5% of items, bottom 3 items, etc.) We'll filter for blank data fields, and more.

 

Finally, you'll learn how to create an Advanced Filter using a region of your spreadsheet for the fields and criteria. This is a great way to set up a very powerful filter for other users who may not be Excel savvy.

 

That is what is covered in Excel Expert Level 9. Plus, of course, there are lots of little tips and tricks thrown about here and there in the lessons (too many to list here). If you have any questions about whether or not this class is for you, please contact me.
 

 

Complete Outline - Excel 2010 Expert Level 9

00. Intro (5:28)

01. Advanced Sorting 1 (7:31)
Custom Sort
My data has headers
Sort By, Sort On, Sort Order
Add Level, Delete Level
Copy Level
Sort by Color
Conditional Formatting
Icon Sets
Sort Options
Case Sensitive
Sort Left to Right
Sort Top to Bottom

02. Advanced Sorting 2 (8:49)
Sort on Custom List
Sort on Birth Month
Create a Custom List
Sort by Roman Numerals
Alphanumeric Sorts
McDonald, Mc Donald
a1, a10, a11, a2, a3

03. Customizing the Interface (11:11)
Customizing Quick Access Toolbar
Adding buttons
Removing buttons
Change order of buttons
Insert separator
Choose commands from
Show QAT Below the Ribbon
Reset customizations
Import/export customizations
Custom toolbars for specific workbooks
Customizing the Ribbon
Main Tabs v. Tool Tabs
Removing groups
Adding your own custom tab
Adding groups and commands
Hiding built-in tabs
04. Filtering Data 1 (5:58)
Download sample spreadsheet
Turn on filtering
Filter on one or more values
Filtering on multiple columns
Turn off filtering
Clearing the filter
Reapplying the filter

05. Filtering Data 2 (8:15)
AND and OR conditions
Text Filters
Numeric Filters
Date/Time Filters
Wildcard Characters
Top 10 Filters
Filter for blanks
Right-click to filter

06. Advanced Filters (13:15)
Create an Advanced Filter Row
List range
Criteria range
Copy to another location
Filter the list in place
Unique records only
AND across, OR down
Multiple rows, multiple columns
Set up nice formatted filter region

07. Review (4:18)

 


 
Keywords: microsoft excel tutorial, microsoft excel 2010 tutorial, microsoft office excel 2010 tutorial, microsoft excel 2010 training, advanced sorting and filtering, customizing the ribbon
 
 

Student Interaction: Excel 2010 Expert 9

Richard on 8/9/2011:  Excel Expert 9 covers several different features. Topics covered include: - Advanced Sorting Techniques - Creating Custom Lists - Customizing the Excel Interface - Modifying the Quick Access Toolbar - Creating Custom Ribbon Tabs - Advanced Data Filtering - Special Text, Numeric, Date Filters - Create a Custom Filtering Section - Lots More!
Michael Duran on 8/29/2011: Can I preper a spreadsheet with the aMike Duranutofill function already programmed in
Tiffany on 12/31/2011: How can I create a custom tab using Excel 2007?
Alex Hedley on 1/1/2012: Hi Tiffany,

If you click
File | Options
Customise Ribbon

You will now have a list of the current tabs in the Ribbon.

You can now add a New Tab or a New Group to a current tab.

Now add any controls you wish from the list.
[Don't forget to rename it/them].

Al

Laurie Jones on 7/30/2013: In Excel 2007, can you tell me how to customize the ribbon, or create my own tab?

Reply from Richard Rost:

Customization of the Ribbon in Excel 2007 was very limited. They made it MUCH easier in 2010 and 2013. If you need to customize the Ribbon in 2007 you have to use custom XML and a lot of strange stuff. In 2010 it's just click and drag. I honestly don't even have a machine with 2007 installed on it anymore, so I can't guide you through it.

Laurie Jones on 7/30/2013: At counter 07:40, it shows <=600, yet the filter brought up 625. Can you help me understand why it brought up a number higher than 600, when it was asked for less than 600? Thank you.
Laurie Jones on 7/30/2013: Never mind. I see the and or condition.


Reply from Richard Rost:

:)

Anonymous on 5/2/2014: Hi Richard, if you set up the Criteria Range above the List, with all the Headers, how come Excel doesn't return a blank filtered list instead, since across 1 row, it uses the AND condition. If you don't include a condition for every column, shouldn't it say Store=Buffalo for example AND no information in Profit) - but since we have information in every profit cell, there is no record that has no profit, i.e. there is no record that satisfies both of those conditions, for the filtered list?
 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP