2 days ago: New Feature: you can now upload your Profile Picture to the site. Check it out!  Dismiss
 
Excel 2010-2019
Excel 2007
Excel 2003
Tips & Tricks
Excel Forum
Course Index CIG Excel Book
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
 
 

< Previous: Excel Expert 9

Next: Excel Expert 11 >

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

 
 
This is the tenth class in our Excel 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 Expert Level 10
Description: Excel Expert Level 10
Versions: Recorded with Microsoft Excel 2010. Valid for 2007 to 2021.
Pre-Requisites: Excel Expert Level 9
Running Time: 1 Hour, 16 Minutes
Cost: $12.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 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 tutorial, microsoft office excel tutorial, microsoft excel training, custom views, subtotal, outline, data validation, drop down, dropdown, lists.
 
 

 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 3/19/2024 6:08:05 AM. PLT: 0s