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 how to create custom views. This way, you can
filter multiple sets of data and quickly switch between them using the
custom views dropdown menu.

Next we'll learn about the SUBTOTAL
function so that our calculations work properly with filtered or hidden
data.

We'll learn how to group the data in our
sheets together using Outlines.


Outlines are great for manually or
automatically grouping and summarizing the data in your sheets,
especially if you don't feel like building a pivot table.

Next we'll spend a lot of time learning
about Data Validation. This allows you to control user input and
force them to enter exactly what kind of data you want. You can create
custom prompts and error messages for the data as it's being
entered.

One excellent way to control user data
entry is to use Dropdown Lists. These force the user to select a
value from a list of options.


You'll learn many different kinds of
advanced data validation. You'll see how to force a date to fall
between two specific days (or within 30 days of today). You'll learn how
to work with different functions inside of a validation rule to
customize the criteria. For example, we'll create a special validation
formula to make sure a Social Security Number is entered in the right
format.

Finally, in one of my favorite examples,
we'll create Cascading Dropdown Lists where we can have one list
limit the choices in a second list. For example, pick a car make and
then the list of models is filtered for that manufacturer. Then we'll go
one step further... enter in the year, and we'll use the DGET
function to pull up the price of the car based on those 3 criteria. This
is really powerful stuff.

That is what is covered in Excel
Expert Level 10. 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 10
00.
Intro (5:33)
01. Custom Views (6:06)
Turn on filtering
Create a Custom View
Add Dropdown to Toolbar
Add View with Dropdown
02. SUBTOTAL Function (7:12)
SUM calculates ALL cells
SUBTOTAL function know filters
Select a subtotal type
SUM, AVERAGE, etc.
Just HIDING a row
Only works in columns, not rows
AutoSum uses SUBTOTAL for SUM only
03. Outline Subtotals (7:33)
Create a Subtotal
Expand Collapse Buttons
Multiple Subtotal Groups
04. Outlines (7:45)
Creating a Manual Outline
Group, Ungroup Buttons
Clear Outline
Auto Outline
05. Data Validation 1 (10:05)
Validation Rules
Text Length
Circle Invalid Data
Input Message
Error Alert
Stop, Warning, Information |
06.
Data Validation 2 (9:30)
Date Values
TODAY() function
Appts in 30 days
Range Formulas for Validation
Ignore First Row Error
Times
Whole Numbers
List
Dropdown List Values
Conditional Formatting
07. Data Validation 3 (8:43)
Custom Rules
ISTEXT()
AND()
MID()
LEN()
Exceeding a Budget
08. Data Validation 4 (8:46)
Cascading Lists
Pick a Car Make
Show Models for that Company
DGET Function
Lookup with Multiple Criteria
09. Review (4:36)
|

|