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 Developer Level 3   dismiss
 
 
Courses - Microsoft Excel 222
Description: Intermediate Microsoft Excel - Data Analysis 3
Running Time: 86 minutes
Pre-Requisites: Excel 221 strongly recommended
Versions:
We use Excel XP in this course, but the lessons are valid for all versions of Excel from 95 to 2003. There are cosmetic changes in Excel 2007. Order before 7/31/2017 to get a FREE upgrade to our 2007 version when released!
 

 Click here for the Teacher's Outline
 
In this series of Excel courses (220-224) we will study Data Analysis. This course is the third in the series. This course covers styles, grouping and outlining, auto-outlining, manual grouping of data, and data validation.

First, we'll learn about Styles. Styles can be used to store all kinds of information about a cell - font, format, colors, etc. One benefit of using styles is that when you change the style's settings, all of the cells in your sheet that have are formatted with that style all change. We'll learn how to use a lot of the existing styles, plus create our own. We'll also see how to merge (import) styles from another workbook.

 

Next we'll learn about Grouping and Outlining. You can use this feature to automatically group similar information together. For example, here's a sheet where we'll group all of the sales information together - then the expenses information.

 

You can expand and collapse the outline groups, add totals, and more.

 

We'll teach you how to group information together on multiple levels, including across the columns (notice the months grouped into quarters on this sheet).

 

I'm never one to rely on Excel to do everything for me (you just miss out on so much of the power!) so next we'll learn how to create manual grouping levels ourselves - with lots of cool tricks thrown in.

 

The next lesson is a whole bunch of Excel tips and tricks with respect to sorting, grouping, outlining, and lots more. For example, I'll teach you how to collapse a group (or two) and then select just the visible cells.

 

I'll show you how to put subtotals above a group so when you collapse the groups, things don't look wierd.

 

We'll make our very own sorting and grouping custom toolbar with all of the functions on it that's you're going to need (preventing you from having to dig in the menus every time).

 

Oh... and remember we worked with Styles earlier. You didn't believe me that they were useful. Here's an example. You've got a sheet with a Style defined for your headings (let's say in blue)...

 

Now make one little change to your style and your entire sheet is updated...

 

OK, now comes the cool lesson: Data Validation. Are people typing in what they're supposed to be typing in. I'll teach you how to make Excel yell at people when they don't fill in the right data into your sheet.

 

You can control the type of information that gets put into cells (text, numbers, etc.)

 

You can give custom prompts to the user...

 

...which show up right on your sheet.

 

Plus you can give custom error messages.

 

Plus here's a real cool one... I'll show you how to create a list that the user has to choose an option from!

 

We'll talk a little bit about data auditing just to see how it applies to data validation. We'll do a lot more with auditing in later lessons.

 

We'll learn some new text functions, like ISTEXT() and LEFT() and we'll see how they apply to checking data validation rules.

 

So once again we cover a whole bunch of new topics and new techniques to aid you in your data analysis tools. We're leading up to something big.... you'll find out what soon.... but you need to get these skills under your belt first. Don't miss this lesson.

 

 

 

 Click here for the Teacher's Outline
 
 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Excel 222

Richard on 1/1/2007:  In this series of Excel courses (220-224) we will study Data Analysis. This course is the third in the series. This course covers styles, grouping and outlining, auto-outlining, manual grouping of data, and data validation.
DonaldĀ Davison on 7/10/2009: Hi richard: Is there a way that budget figures can be develop within excel?
Richard Rost on 7/15/2009: Donald, sure you can use Excel to figure out your budget. Budgets are usually just a matter of math. Tell me more about what you're dealing with, and perhaps I could make a lesson out of it.
 

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