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  
 
 
 
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 3/22/2024 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

 
 
 

 

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:38:12 AM. PLT: 0s