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  
 
 
 

< Previous: Excel Expert 9

Next: Excel Expert 11 >

Excel 2010 Expert Level 10
Microsoft Excel Expert Tutorial - 1 Hour, 16 Minutes

 
 
This is the tenth class in our Excel 2010 Expert Series. It picks up where Expert Level 9 left off. This course covers several different topics, including:
 
 - Working with Custom Views
 - Using the SUBTOTAL Function
 - Creating Data Sheet Outlines
 - Control Input with Validation
 - Select Values in Dropdown Lists
 - And Lots 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 10
Description: Excel 2010 Expert Level 10
Versions: Microsoft Office Excel 2010
Pre-Requisites: Excel 2010 Expert Level 9
Running Time: 1 Hour, 16 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 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 2010 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)
C
ustom 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)
 

 


 
Keywords: microsoft excel tutorial, microsoft excel 2010 tutorial, microsoft office excel 2010 tutorial, microsoft excel 2010 training, custom views, subtotal, outline, data validation, drop down, dropdown, lists.
 
 

Student Interaction: Excel 2010 Expert 10

Richard on 10/1/2011:  Excel Expert 10 covers several different features. Topics covered include: - Working with Custom Views - Using the SUBTOTAL Function - Creating Data Sheet Outlines - Expand & Collapse Data Groups - Control Input with Validation - Select Values in Dropdown Lists - Multiple Cascading Drowndown Lists - Lookups with the DGETS Function - Lots More!
Richard on 10/1/2011: Yes, yes. I'm aware that the sort order of the Excel Expert courses goes 1, 10, 2, 3, etc. I have to fix it. I wasn't planning on having more than 9 courses in one level. :)
Christine M on 4/10/2012: This program sounds great. I think it might be my next order. But can you do an excel lesson with a workbook summary? I have worksheets that I would like to summarize or list all of the data on one page. I want that summary page to update when I update the individual worksheet. Thanks! Love the lessons!

Reply from Richard Rost:

Yep. In Excel Expert 1 you learn how to refer to values on other sheets.

 Mrk on 6/2/2012: 1:09 Excel Course Levels... I was looking to purchase Advance & Developer Levels - Since I was not able to find them in Theater Home Memu -May I assume you're working on them? B.T.W... Great video's and hell of a great teacher too.
John Reneau on 9/17/2012: What is the number of excel 2010 expert for the learning cd on advanced charts

Reply from Richard Rost:

I covered basic charts in Beginner 4, and PivotCharts in Expert 7. I haven't gotten to advanced charts yet. Coming soon. :)

Joe Beniacar on 5/3/2014: Hey Richard, how come the Grand Total and Region Totals use a SUBTOTAL(9,...) function, rather than a SUBTOTAL (109,...) function? Isn't the collapse feature more closely related to "Hide" than "Filter"? Thanks!
Anonymous on 5/4/2014: Hi Richard,
What does the VALUE function do? i.e. Why can't you just use: ISNUMBER(LEFT(C1,3)), without the VALUE part, to check if the first 3 #'s together are a number? Thanks!

 

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