| 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.
|
