|
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
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) |
|
