| In this series of Excel courses
(220-224) we will study Data Analysis. This course is the second
in the series. This course covers AutoFilter, Custom AutoFilter,
Advanced Filtering, Calculated Filters, Custom Views, and Subtotals.
We'll work with Filters a lot in this course.
Filters allow you to weed out data you don't want and show just a
specific set of data. For example, if you have a bunch stores in
different cities, and you want to see sales from just your Toronto
stores.
You can also use filters to show you the Top 10
items (or top 10%, or top 20 items, and so on).
You can use Custom Filters to show you, for
example, all stores that have sales between $100 and $900.
You can create Advanced Filters to specify
your own criteria row. This will let you create query-like structures in
your Excel spreadsheets and really get the data you want!
We've got a great side project in this
class where we take a long list of customers (some of whom are missing
data), filter that data correctly, sort and filter by ZIP code...
...and then create mailing labels using
Microsoft Word and a Mail Merge.
You will learn all about Filter Wildcard
characters.
You will learn about Calculated Filters.
You will learn how to create Custom Views
with your filter. This will allow you to set up special filters, such as
"show me all Buffalo stores with at least $500 in sales," and "show me
all Toronto stores with at least $300 in sales." You can then quickly
switch between them using the toolbar.
Next we'll learn about the SUBTOTAL
function, and how it's much better than SUM or AVERAGE when working with
Filters. Why do you need it? What's the benefit? You'll find out!
You'll also learn how to create automatic
Subtotals (not to be confused with the SUBTOTAL function). These
are automatic subtotals for groups of records - like "show me the sales
totals for each group of stores by month."
We'll talk a little bit about Grouping and
Outlining. We'll discuss it in great detail in the next class (Excel
222).
Learning how to use Filters properly is the
cornerstone for good, solid data analysis. Whether you've got a few
records to filter, or several thousand, the lessons in this course will
teach you how to manage that data.
|
