IntroductionWelcome| 00:04 | Hi! I'm Dennis Taylor and I'm pleased to
present Managing and Analyzing Data in Excel.
| | 00:09 | When you work with large lists and
database-like structures, you need tools to
| | 00:13 | manage your data and get the
most information out of it.
| | 00:16 | Excel has a variety of features
specifically designed for these purposes and we
| | 00:21 | will cover them in detail in this course.
| | 00:23 | We'll start by showing you a variety
of ways to use the most important data
| | 00:27 | management tool, sorting, and then
show you how to automatically insert
| | 00:31 | subtotals into sorted lists.
| | 00:33 | We will also show you how you can see
just the data you need using filtering,
| | 00:39 | how to identify and delete
duplicate data, and how to gather valuable
| | 00:42 | statistical information via the SUMIF
family of functions and the database
| | 00:47 | statistical functions.
| | 00:49 | So let's get started with
Managing and Analyzing Data in Excel.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you're a Premium member of the
lynda.com Online Training Library, or if
| | 00:06 | you're watching this tutorial on a DVD
-ROM, you have access to the exercise
| | 00:10 | files used throughout this course.
| | 00:13 | As you open a file, as I am now, you will
see that there are a number of sheet tabs.
| | 00:20 | Many of these will be
referred to in the training.
| | 00:23 | Here and there you will see a
tab that is not used at all.
| | 00:26 | If you're a monthly subscriber or annual
subscriber to lynda.com, you don't have
| | 00:31 | access to the exercise files.
| | 00:33 | But you can follow along from
scratch with your own files.
| | 00:36 | Let's get started!
| | Collapse this transcript |
|
|
1. Sorting DataSorting from the Sort menu| 00:01 | Whenever you work with large amounts of
data, whether it's an HR list like we see
| | 00:05 | here on the sheet or on the next sheet,
scientific type data, the need to sort
| | 00:10 | the data, rearrange the order
of the rows, is a common need.
| | 00:14 | And let's say that sorting is something
that is probably the key action that we
| | 00:19 | take to manage our data.
| | 00:21 | We need to see at them this particular order.
| | 00:23 | Often we'll print this.
| | 00:25 | Before actually working with
data, a couple of quick tips here.
| | 00:29 | Let's imagine you have never seen this
worksheet or maybe you've only seen it once or twice.
| | 00:34 | You forget how big it is.
| | 00:35 | It's something you'd want to know.
| | 00:37 | Get in a habit of pressing Ctrl+End.
| | 00:39 | This is not a perfect keystroke shortcut,
but many times it gives you that quick read.
| | 00:44 | Notice that the active
cell has moved to cell O742.
| | 00:48 | What can we say about that?
| | 00:50 | In this particular worksheet, remember
we just pressed Ctrl+End, we can be sure
| | 00:54 | there is no data anywhere below row
742 nor is there any data to the right of
| | 01:00 | column O. Press Ctrl+Home to go
back to the upper left-hand corner.
| | 01:04 | Before doing sorting or filtering or
using some of the other database management tools,
| | 01:09 | it's a good idea also to make
sure that your data is in one solid
| | 01:14 | cluster, meaning that we don't
have any empty rows in the data.
| | 01:18 | Do we want to go scrolling through this?
| | 01:20 | What if it's 7,000 rows?
| | 01:22 | What if it's 700,000 rows?
| | 01:24 | So another quick tip here is with
the active cell anywhere in your data,
| | 01:29 | press Ctrl+A. Now if this were small amount,
we'd probably see the edges of all of this data.
| | 01:35 | We might even be able to zoom backward
with the Ctrl key and the mouse wheel.
| | 01:39 | But we've got over 700 rows here.
| | 01:41 | You saw that earlier.
| | 01:42 | So how do we know if there
are any empty rows in here?
| | 01:46 | Take the unlikely step of
pressing Ctrl+Period a few times.
| | 01:50 | As I'm doing here. The active cell is now an O1.
| | 01:54 | Ctrl+Period simply moves the
active cell around my corners.
| | 01:57 | So whether you believe that or not,
it does prove that there are no empty
| | 02:00 | rows within the data.
| | 02:02 | And that's the key idea, because
any time we want to do sorting and
| | 02:06 | filtering and use other database
management tools, instead of having to
| | 02:10 | constantly highlight all of our data, we need
to only click within our data on a single cell.
| | 02:16 | And if we only do sorting occasionally,
it's always best to go to the Data tab
| | 02:22 | and use the actual Data > Sort command.
| | 02:25 | And even if you do sorting a lot, this
is the place to go to for some of the
| | 02:29 | many options that are now available in sorting.
| | 02:32 | We will talk in later movies about the
AZ and the ZA buttons and some other
| | 02:36 | issues, but this is, you
might say, ground zero for sorting.
| | 02:41 | So we'll click the Sort button.
| | 02:43 | Again, it's on the Data tab, and
although we can sort in a variety of different
| | 02:47 | ways, first time around let's just
simply look at what we have here.
| | 02:51 | Sorting for about 95% of the people most of
the time means rearrange the order of the rows.
| | 02:59 | And the first step in here is to
choose the field that we want to sort on.
| | 03:02 | Let's imagine in this particular example
that we would like to rearrange all this
| | 03:06 | data in alphabetical order by department.
| | 03:09 | Here is a list of the fields. They
match up with what we see in row 1. There's
| | 03:16 | department, let's click it.
| | 03:17 | Notice that Sort On says Values, that's
its default setting, but you will see,
| | 03:23 | you can sort by Cell Color,
Font Color, Cell Icon.
| | 03:27 | The order often will automatically be
A to Z. Now possibly it wasn't here
| | 03:32 | because the last sorting that was
done might've been in reverse order.
| | 03:36 | But with text fields, A to Z is
by far the most common choice.
| | 03:40 | In other words, alphabetically.
| | 03:41 | And even though there are other
options, we will ignore those right now.
| | 03:45 | Let's just click OK and almost
instantly we see that the list has been
| | 03:50 | rearranged by department.
| | 03:53 | Now there are certainly other ways to
sort that are faster and we talked as
| | 03:57 | we did this too, but that's where we want to go,
this actual Sort command that's on the Data tab.
| | 04:02 | If you use this feature a lot, you might
consider right-clicking and add this to
| | 04:07 | the Quick Access Toolbar.
| | 04:09 | And so the next time you sort, perhaps
you have been using or had been using the
| | 04:14 | Home tab, you want to start the sorting again,
| | 04:17 | there is the button right there. You
don't have to go over to the Data tab.
| | 04:20 | And of course, that's a tip you can
use with other features as well in Excel.
| | 04:24 | So using the Sort command, generally
straightforward and fast. I'm using it
| | 04:29 | from the menu, the actual Sort command.
The best way, the most reliable way to
| | 04:34 | use sorting in Excel.
| | Collapse this transcript |
| Sorting from the toolbar| 00:01 | As you become more comfortable with
sorting, there are many times when you want
| | 00:04 | to sort your data quickly and easily.
| | 00:07 | And if your data is all together here,
no empty rows within this data, if
| | 00:11 | you'd like to rearrange the entire
list by Employee Name, click on a single
| | 00:15 | cell in column A. It can be cell A1
or any of the others. Do not click the
| | 00:20 | column letter itself.
| | 00:22 | Unfortunately, it's pretty difficult
to rearrange only the data in column A.
| | 00:26 | That was a major shortcoming
of some older versions of Excel.
| | 00:30 | So, click on a single cell. Imagine
that we would like to rearrange this entire
| | 00:35 | list, some 740 names, alphabetically by name.
| | 00:39 | Go to the Data tab. We
probably want this alphabetical.
| | 00:42 | There's an AZ button.
| | 00:44 | Click it, the deed is done.
| | 00:46 | And furthermore what if after printing
this or reviewing it or sending it onto
| | 00:51 | others, we now change our minds because
we want to see the list in alphabetical
| | 00:55 | order by department?
| | 00:56 | We'll click in column C, click the AZ
button, and recognize that the order
| | 01:02 | is now by department.
| | 01:03 | But almost as important as that
recognition is that if you're looking at
| | 01:07 | records within a single department, for
example, this ADC department, recognize
| | 01:12 | that the order of those records there is based
on the sort that we had just done previously.
| | 01:18 | In other words, they're in order by
employee name and you can see that even more
| | 01:22 | clearly perhaps in the
Admin Training group right here.
| | 01:25 | So those names are in alphabetical
order because that was the sort that we had
| | 01:28 | done just previously to sorting by department.
| | 01:33 | And let's try this a third time.
| | 01:34 | I'm going to sort based on the
data in column F, the Status.
| | 01:39 | Click in column F, I'll click AZ again,
ascending order, and now we have all the
| | 01:44 | Contract people together.
| | 01:46 | And how are those Contract people
grouped? And they're quite a few of them, close
| | 01:50 | to 200 or so. They are in
alphabetical order by department.
| | 01:54 | And for any given department, say
Logistics right here, here are all
| | 01:58 | the Contract people.
| | 02:00 | What order are they in, the order of the
sort that preceded the department's sort.
| | 02:05 | So you can think of using these
buttons in kind of a cumulative way.
| | 02:10 | The last order or the last column you
chose to sort on overrides the others.
| | 02:16 | Now if someone says I'd like to see a
list of everybody by department based
| | 02:20 | on their salaries descending, if
Department is the major grouping here and
| | 02:26 | Salary is secondary, we'll go to the
Salary column, column K. This time click
| | 02:31 | ZA, descending order. There we go!
| | 02:34 | And for the moment, that's our list of
all salaries in the entire organization
| | 02:39 | here, descending order by salary.
| | 02:42 | Let's now click somewhere in column C,
we'll click AZ, and within any given
| | 02:48 | department, once again, here
are the Admin Training people.
| | 02:51 | You see the order that those records are in.
| | 02:53 | It's in descending order by the
salaries in column K. And if you haven't done
| | 02:58 | sorting by date, you'll
quickly learn and see how that works.
| | 03:02 | If we click in column G and do an AZ sort,
the dates are in ascending order from
| | 03:08 | the oldest to the newest.
| | 03:10 | The first person hired in this
organization, July 17th, 1990.
| | 03:13 | I'm going to reverse the order.
| | 03:16 | descending order puts the
latest dates at the top.
| | 03:19 | Remember that all rows are being sorted
here, so the information in one row is
| | 03:24 | going to be shuffled around. As we now
see, our most recent hire is listed on top.
| | 03:28 | So you quickly get the hang of how that works.
| | 03:32 | So as you become more comfortable with
sorting, using the ZA and AZ buttons, they are
| | 03:37 | fast and easy, remember that
they are cumulative in nature.
| | 03:41 | And here too, if you start to use
sorting a lot, why not right-click on each one
| | 03:46 | of these, add it to your Quick Access Toolbar?
| | 03:49 | Over time you may decide to
take it off in the same way.
| | 03:51 | Simply right-click on it, if
you don't use it that often.
| | 03:54 | So you can easily make
those adjustments as well.
| | 03:57 | So, sorting, fast and easy with
the Sort buttons in the Data tab.
| | Collapse this transcript |
| Multi-key sorting| 00:01 | Using the Sort command, you can sort
on multiple columns at the same time and
| | 00:05 | Excel has expanded this capability
substantially in versions 2007 and in 2010.
| | 00:11 | As we look at this data, it may be
important to you to see the data in
| | 00:15 | order possibly by department, but in some of
these departments, we have over 100 people.
| | 00:20 | And so we'd like to be able to look at
that portion of the list and see it in
| | 00:24 | order perhaps by Status, maybe by
Employee Name, maybe by Salary, or maybe a
| | 00:29 | combination of those.
| | 00:31 | If your needs are to sort on more than
one column and if you have not used the
| | 00:35 | Sort buttons, which have their
limitations and yet could work if you are only
| | 00:40 | thinking about two columns or
possibly three, go to the Sort command.
| | 00:44 | Now, once again, click on a single cell
within the data. Go to the Sort command
| | 00:49 | button on the Data tab.
| | 00:52 | Let's imagine that our major grouping
that we're looking for here, in other
| | 00:56 | words the very first column, the dominant
sorting arrangement here, is to be by department.
| | 01:02 | As we suggested because in some
departments there are many, many people, and we
| | 01:06 | would want that to be alphabetical,
make it be A to Z. Let's add another level
| | 01:11 | and this, we want to put in by Status.
| | 01:13 | Half Time, Full Time,
etcetera. Add another level.
| | 01:17 | We want those people perhaps to be in
order by their salaries, and maybe we want
| | 01:23 | to see the highest salaries first.
| | 01:25 | So we'll choose Largest to Smallest.
| | 01:27 | Now, it's possible that we still might
have within certain organizations here
| | 01:31 | because there are a lot of people,
a lot of people with the same salary.
| | 01:35 | So let's add a fourth level.
| | 01:37 | Now it's important to note here too
that in all versions prior to Excel 2007,
| | 01:42 | you could only sort on three fields at
once. We're about to use a fourth right
| | 01:47 | here and it will be the Employee Name.
| | 01:49 | And it's unlike that they will have people
with the same name within the organization.
| | 01:53 | It's certainly possible but we don't
need another level here, although certainly
| | 01:56 | with other kinds of data here and there
you will, and the response to a lot of
| | 02:00 | complaints about being only able to sort
on three fields at once, this limit has
| | 02:06 | now been raised to believe it or not 64 levels.
| | 02:09 | Here we're using four levels:
| | 02:11 | Department, Status, Salary, Employee Name.
| | 02:15 | Click OK, there we are!
| | 02:16 | And there are all the people in the same
department and here are all the people
| | 02:21 | who are full-time within this
department. And how are they ordered?
| | 02:24 | They are in descending order by the salaries.
| | 02:26 | It looks like no two salaries there
are alike, so the fourth order, by Name,
| | 02:32 | really doesn't have a role to play here,
although it certainly could in some situations.
| | 02:37 | So being able to sort on multiple fields is
probably best handled by way of the Sort command.
| | 02:42 | If you're pretty adept with the AZ and
ZA buttons, you could have achieve the
| | 02:46 | same objective, although it probably
would have taken longer. In those cases,
| | 02:49 | you probably refer to the
previous movie to see how that's done.
| | 02:52 | So multiple key sorting, big expansion in terms
of capability in Excel 2007 and in Excel 2010.
| | 03:00 | Best way to do that is to use
the Sort command as we just saw.
| | Collapse this transcript |
| Sorting based on the order of data in custom lists| 00:01 | Sometimes you need to sort data based
not on alphabetical or numerical order,
| | 00:06 | but on the order of data
as it appears in a list.
| | 00:09 | And it could be one of Excel's built-in
lists or it could be a list that you've created.
| | 00:14 | As we look at this particular worksheet,
we might want to sort the data based on
| | 00:18 | the information in column H.
Column H contains formulas.
| | 00:22 | Take a look at cell H2.
| | 00:24 | So I double-click it.
| | 00:26 | It looks like it's a rather complex
formula but that's actually pretty easy.
| | 00:29 | It simply pulls out the month
of each of these Hire Dates.
| | 00:33 | And perhaps what we're aiming for here
is a list that's sorted alphabetically by
| | 00:37 | month so we can just know when someone
is having an anniversary month we want
| | 00:40 | to print that data out.
| | 00:42 | So we'd like to sort based on the data
in column H, and if we're in a hurry we
| | 00:47 | can certainly use the AZ button
as we see it on the Data tab here.
| | 00:51 | It's going to sort the data based on
the data in column H. And there we are
| | 00:55 | and April comes first.
| | 00:56 | But wait a minute here!
| | 00:57 | Aren't we thinking, shouldn't January be first?
| | 01:00 | Well, no, this is an alphabetic sort.
| | 01:03 | We see April, then we see August, then
December. We didn't do anything special
| | 01:08 | to indicate to Excel that there is
anything unusual here, but we do have at our
| | 01:12 | fingertips a way to sort
this data chronologically.
| | 01:16 | We must use the Sort command.
| | 01:17 | It's on the Data tab just to
the right of the AZ, ZA buttons.
| | 01:21 | Let's click this again.
| | 01:22 | We do want to sort by Month, we want
to sort on Values, but not that A to Z
| | 01:27 | order, because built into Excel is a
custom list and we do see the months
| | 01:35 | abbreviated and full spellings.
| | 01:36 | We have full spellings here.
| | 01:38 | This is the one we'll use.
| | 01:39 | We want to sort chronologically, not
alphabetically, and by making this choice
| | 01:44 | and clicking OK, that's
exactly what happens. Easy and fast.
| | 01:49 | So anytime you need to sort on a
column that's got monthly names, either
| | 01:53 | abbreviations or full spellings or
possibly day of the week, you probably want
| | 01:57 | to chronological sort in both cases.
| | 01:59 | That's what we've done
here with these custom lists.
| | 02:02 | Now, there are other situations too where
you might need to sort in a particular order.
| | 02:07 | I'm going to sort this data based on
what we see in column F. A quick AZ sort
| | 02:12 | again from the Ribbon on the Data
tab and the Contract people come first.
| | 02:17 | Remember, there are also, as you
see this here on the screen, Full
| | 02:21 | Time, Half-Time, Hourly.
| | 02:24 | Well, I think a lot of us will want to say,
well, let's put the full time people first.
| | 02:28 | They run the company and I
want to see them first here.
| | 02:31 | If we did a reverse sort, ZA,
that's not going to help either.
| | 02:35 | We'd like to sort in a particular order.
| | 02:38 | On another sheet, and you don't
necessarily have to have the data be displayed here,
| | 02:42 | I've got the actual statuses
listed here in the order that I would like
| | 02:46 | to have them appear.
| | 02:47 | I'm thinking ahead too that I
might need this in the future.
| | 02:50 | So what I'm about to do is to take
this data and make it be an entry in a
| | 02:55 | custom list along with the days of
the week and the months of the year that
| | 02:59 | we've already seen.
| | 03:00 | So with this data highlighted and you
don't really have to do it this way and
| | 03:03 | you might be prepared to type this
yourself. And if you're using Excel 2010,
| | 03:08 | click the File tab in the
ribbon and then choose Options.
| | 03:12 | If you're using Excel 2007, click
the Office button, and then click Excel
| | 03:16 | Options, and in 2007 you'll see a
choice right about here on the screen that
| | 03:21 | indicates Custom Lists.
| | 03:23 | 2010, we need to go to the Advanced tab
and scroll way down to the bottom of
| | 03:28 | this and you'll see this box, Edit Custom Lists.
| | 03:32 | Let's click it and here are those
built-in lists that we saw earlier.
| | 03:37 | Let's now add by importing
the data that we've highlighted.
| | 03:42 | If you didn't have those items on a
worksheet like we have here, you would type
| | 03:47 | them right here where it says List Entries.
| | 03:49 | In other words, type Full Time, press
Enter, Half-Time, and Enter and so on.
| | 03:53 | Now we can just import them.
| | 03:55 | Now, this list will stay here indefinitely.
| | 03:58 | I'm going to click OK.
| | 04:00 | If the next exit that I do from Excel
is a normal one, we won't worry about it.
| | 04:04 | That list is around forever.
| | 04:06 | What it means is when we want to sort
data in a particular way, like here, we
| | 04:11 | will use the Sort command.
| | 04:13 | We do want to sort by Status, we want to
sort by Values, but not that A to Z order.
| | 04:19 | Click the drop-arrow, go to Custom List,
and that latest entry in our Custom
| | 04:23 | Lists, the one we put in, Full-
Time, Half-Time, Hourly, Contract.
| | 04:28 | That's the order I want.
| | 04:30 | Click OK, click OK, and the
full-time people come first.
| | 04:35 | So whenever necessary, you can
override standard sorting order by creating a
| | 04:39 | custom list or referring to one of
the built-in custom lists in Excel.
| | Collapse this transcript |
| Sorting by color font, color background, or icon| 00:00 | A new feature in Excel 2007 is the
ability to sort data on the basis of cell
| | 00:06 | color or font color or even cell icon.
| | 00:10 | In this particular worksheet, as I
start to scroll down, we would recognize
| | 00:14 | there is a pink row in Row 16.
| | 00:15 | There is 21 and 26 have green,
green color, so does Row 37.
| | 00:20 | Perhaps there are some colors there too.
| | 00:22 | And the colors might have been put in
there for a variety of reasons, but we do
| | 00:26 | want to rearrange our data.
| | 00:27 | We want to sort the rows here
by putting the colors together.
| | 00:31 | Maybe we want all the greens
to be at the top of the list.
| | 00:33 | You also notice in this worksheet as
I scroll to the right that in Column J
| | 00:39 | there are icons placed here based on the salary.
| | 00:42 | Now if we wanted all the green arrows
together, we could easily sort by the
| | 00:46 | values themselves, but
also what we could do here is not be
| | 00:52 | showing the actual salary number.
In other words, just the icon.
| | 00:55 | So at different times, you
might want to sort by icon as well.
| | 00:59 | So let's tackle first the issue of
sorting by color, color background here.
| | 01:04 | The active cell can be anywhere in our
list and we need to go to the Data tab
| | 01:08 | and use the Sort command and we could
pick practically any field here because
| | 01:14 | the color extends across all the cells
in columns A through K. Let me just use
| | 01:18 | Department, there we go.
| | 01:20 | Sort on Cell Color.
| | 01:22 | Now that does mean cell background, and
the first color we want, let's say the green.
| | 01:28 | Put all the green rows first.
| | 01:30 | We need to Add Level, two more at least.
| | 01:35 | We do these all at once or one by one.
| | 01:37 | Use Department there, Department there.
| | 01:39 | Cell Color and for the second cell
color, even though we didn't see, let's
| | 01:46 | put the yellows next, and third cell color,
the pinks. All the others will fall behind that.
| | 01:51 | So we're going to greens, yellows
and pinks at the top of our list as we
| | 01:55 | sort, and there they are.
| | 01:58 | Now at a different time whether we
had sorted or not is not the issue.
| | 02:02 | Let's take a look at the data in Column J
and I did hold out the possibility that
| | 02:06 | sometimes you might not want to show
the salaries. Maybe someone needs to
| | 02:10 | display a list like this or a portion
of it, and only give a rough estimate of
| | 02:15 | where the salary falls into place.
| | 02:17 | Is it in one of four quartiles?
| | 02:19 | So the green arrows are in the top quartile.
| | 02:22 | So let's change the display here using
conditional formatting, clicking Column
| | 02:26 | J, going to the Home tab in the Ribbon,
and then under Conditional Formatting,
| | 02:31 | under Icon Sets here, I guess we could've
divided this into thirds or into fifths,
| | 02:36 | but let's Manage the Rules as it's
called and make a change to this rule.
| | 02:41 | Edit the rule and show the Icon Only, and
now the idea of sorting by icon make sense.
| | 02:49 | We want all the greens to be on top.
| | 02:51 | So we want to sort this list by Salary.
| | 02:55 | Once again, back to the Data tab,
the Sort command and Salary and we've
| | 03:01 | four arrows, but we need only sort
on three. The leftovers will fall into
| | 03:05 | place automatically.
| | 03:06 | So three Salary sorts. The dominant one
is the first one and we're sorting by
| | 03:11 | Cell Icon in each of these cases here.
| | 03:16 | But we want the greens to appear
first. Those are the highest salaries.
| | 03:20 | We noticed two kinds of yellows, those
that point upward. We want that one first.
| | 03:24 | Then this one, these are in the third
quartile, and the red arrow is the fourth quartile.
| | 03:28 | They will fall automatically at the bottom.
| | 03:30 | So as we click OK, we've
sorted the list this way.
| | 03:33 | So without necessarily knowing what
these salaries are, we only know that the
| | 03:37 | green salaries are in the top quartile.
| | 03:40 | So as we view this list now and we
haven't sorted it by name too, we could have
| | 03:44 | possibly included that, but all the
people in the top quartile are first.
| | 03:49 | We sorted by Cell Icon and prior to
that in this movie we sorted by color.
| | 03:53 | New features available starting in Excel 2007.
| | Collapse this transcript |
| Sorting columns| 00:01 | Although not frequently needed, you
can sort by rearranging the left or right
| | 00:05 | order of columns, instead of the much more
common rearranging of rows from top to bottom.
| | 00:11 | As we look at this worksheet here, I think
you could begin to see what we're headed for.
| | 00:16 | We might want to rearrange this
data in such a way that our cities are
| | 00:20 | alphabetized from left to right.
| | 00:22 | Now, unlike the shortcuts we have seen
with prior examples of sorting, we must
| | 00:27 | highlight all of the data in question here.
| | 00:29 | What we're about to do as I zoom back
here is to select this data specifically.
| | 00:36 | This is what we want to rearrange.
| | 00:38 | We do not want to touch
the data in column A at all.
| | 00:41 | We simply want to rearrange
the column data as we see it.
| | 00:45 | We want Albuquerque to be on the left-
hand side, then Atlanta and so on.
| | 00:48 | We want these alphabetized.
| | 00:50 | So we must specifically highlight
all the data and we must use the Sort
| | 00:55 | command on the Data tab.
| | 00:58 | What's different here?
| | 01:00 | The Options allow us to sort in the
unusual sort left to right, not top to bottom.
| | 01:07 | What do we want to sort on,
based on what, what are we seeing?
| | 01:11 | It's the data in Row 3.
| | 01:12 | Now, notice there are options now since
we made the choice to sort left to right.
| | 01:18 | in the Sort by options we're not going
to be seeing field names; we're going to
| | 01:21 | be seeing row numbers.
| | 01:23 | We want to sort based on what we see in Row 3.
| | 01:26 | Of course the big concern right away
is thinking of how this might look.
| | 01:30 | Are the numbers below
this going to travel with it?
| | 01:32 | Well, yes, they will.
| | 01:34 | Keep an eye on Albuquerque then.
Number right below that is all 2, so
| | 01:38 | that's easy to remember.
| | 01:39 | So as I click OK here, we will be
rearranging the data as we see the data in
| | 01:44 | columns B through M. We expect to see
Albuquerque over there in column B and
| | 01:48 | all of its data with it, and then Atlanta and
probably Boston, and so on. Click OK, there we go!
| | 01:55 | And that rearranged the order
of the columns left to right.
| | 01:58 | So as we've seen here the
rearranging of the columns here is pretty fast
| | 02:02 | and straightforward.
| | 02:03 | You do have to highlight all of the data,
but when the need arises, sorting by
| | 02:07 | rearranging the columns is an easy task.
| | Collapse this transcript |
| Sorting data in random order| 00:01 | You may have the occasional
need to rearrange data randomly.
| | 00:04 | That is, sort this list in a random order.
| | 00:07 | Now, why might you want
to do something like that?
| | 00:09 | Maybe you want to review the accuracy
of your record keeping and you'd like to
| | 00:13 | extract from here, copy from here, 8 or
10 records, paste them somewhere else,
| | 00:18 | and review the data at hand.
| | 00:20 | And rather than just doing it in a
freeform way, let's actually rearrange this
| | 00:24 | data in a random order.
| | 00:26 | I think you could also imagine this on
a sales environment. You're trying to
| | 00:29 | analyze orders, inventory,
processing, shipments, those kinds of things.
| | 00:34 | Maybe you don't have a formal Quality
Control department, but you just want to
| | 00:38 | pick random records at times.
| | 00:40 | Let's randomize this list.
| | 00:42 | Now, we can either add a new column
over to left of column A, or possibly off
| | 00:46 | to the right here. Maybe we
haven't put in the new salaries.
| | 00:49 | If we did, we'd create a new set of data in
column N, but we'll just use this right here.
| | 00:53 | What we're about to do is to simply use a
function that perhaps a lot of you have not used.
| | 00:58 | It's a mathematical function, =rand,
and its sole purpose-- and you need to only
| | 01:03 | type =rand( and then Enter.
| | 01:05 | Its sole purpose is to put in a random
number between 0 and 1. And to fill in
| | 01:12 | this particular function throughout
the depth of the data here, simply
| | 01:16 | double-click the lower right-hand corner.
| | 01:18 | So here are a series of random
numbers and this function has an unusual
| | 01:22 | characteristic about it too, is if we
manipulate data elsewhere or make a change
| | 01:27 | in other parts of the worksheet,
all of these numbers get regenerated.
| | 01:31 | But our purpose to put it in here is
simply to use this data as a basis for sorting.
| | 01:37 | So we'll simply click on one of
the cells in Column M and click AZ.
| | 01:41 | Now, although we won't see what's
actually about to happen here, we will
| | 01:46 | rearrange the data based on these numbers, but
in the process the numbers were all changed.
| | 01:51 | So it's like destroying our paper trail.
I am going to click AZ and scrolling
| | 01:56 | left towards here, I think you can
see this data is in no discernible order
| | 02:00 | whatsoever, certainly not in order by
Employee Name or Department or Building
| | 02:04 | and or any other field that you look at.
| | 02:06 | This has been randomized.
| | 02:08 | It's in no particular order.
| | 02:09 | And meanwhile, those random
numbers got regenerated, and changed.
| | 02:13 | We don't need them anymore.
| | 02:15 | We can either delete the whole column
or if we want to leave the label there.
| | 02:18 | Just click here. We could double-
click to copy down the column again and
| | 02:21 | simply press Delete.
| | 02:23 | But the main idea is we've randomized
the list and now perhaps we'll copy the
| | 02:28 | first 10 or 15 elsewhere.
| | 02:30 | Review the accuracy of our data.
| | 02:31 | That was the whole purpose.
| | 02:33 | So using the rand function
temporarily in a list for sorting purposes is
| | 02:38 | something you'll need occasionally
and it's easy to set up as you just saw.
| | Collapse this transcript |
|
|
2. Filtering DataUsing single- and multiple-column text filtering| 00:01 | When you have a list of data, large or
small, there certainly are going to be
| | 00:04 | times when you say to yourself,
"I don't want to see all of this data; I just
| | 00:09 | want to see certain portions of it."
| | 00:11 | For example, in this list here, maybe
you just want to view the people that
| | 00:13 | are in the Quality Control
department or maybe you want to focus on the
| | 00:17 | manufacturing people, or maybe there are two or
three departments whose names you'd like to see.
| | 00:22 | Along with simple filtering, we also
can include different columns in play.
| | 00:28 | We maybe only want to
focus on the full-time people.
| | 00:31 | Now possibly, you might have
converted data into a table.
| | 00:34 | I have taken a copy of this data
and displayed it as a table.
| | 00:38 | It's in the worksheet to the
right called Employees-Table.
| | 00:41 | If you have used this feature, and
more and more people are starting to use
| | 00:45 | this because they like the easy
color applications you can use and other
| | 00:49 | features, it automatically applies or
gives you filter arrows as we see here.
| | 00:53 | I'm going to be using filtering on
another table, the one we first saw, to start
| | 00:58 | the process from the beginning.
| | 00:59 | But everything we do with a filter
there, certainly can be done in a table
| | 01:03 | like this as we'll see as we
go back to Employees Sheet.
| | 01:07 | The data doesn't have to
be in any particular order.
| | 01:10 | We do want to make sure or let's say
that we have made sure in this case, there
| | 01:13 | are no empty rows within the data and
as I scroll to the right here, no empty
| | 01:17 | columns within columns A through L.
So we want some control over this to see
| | 01:22 | just the data we want.
| | 01:24 | The active cell can be anywhere.
| | 01:26 | Go to the Data tab, click the
Filter button, and immediately we see
| | 01:31 | drop rrows for every field.
| | 01:33 | Let's focus on department.
| | 01:34 | Click the drop arrow here.
| | 01:36 | If it's only one department we need
to see here or want to see at any given
| | 01:40 | time, first step unselect all of these.
| | 01:43 | Now if you have worked with this
feature in the past, you'll notice this is a
| | 01:47 | huge improvement introduced in Excel 2007.
| | 01:50 | In prior versions trying to see
more than two departments was not
| | 01:55 | easily handled. Now it is.
| | 01:57 | We might want to focus on just those
people in training type departments.
| | 02:01 | here's Admin Training, here's
Executive Education. We have another one down
| | 02:05 | here, Professional Training Group.
Maybe there are some more, but there's three
| | 02:09 | of them. Let's click OK and we are
only seeing people in these departments.
| | 02:14 | Now, notice two big
visual changes on the screen.
| | 02:17 | The row numbers are blue, and of
course they're not all there because not
| | 02:22 | everybody is in one of these three departments.
| | 02:24 | Also, in the lower left-hand corner it
tells us that there were 52 of the 741
| | 02:30 | records that meet these criteria.
| | 02:33 | It's either Admin Training, Executive
Education, or Professional Training Group.
| | 02:37 | Notice also, the filter arrows that we
see on the other columns are still there,
| | 02:42 | but the filter arrow for this
particular column now actually has a filter icon
| | 02:47 | right there to remind us that this
particular column is at play in filtering.
| | 02:53 | And we can certainly apply filters
to other columns at the same time.
| | 02:57 | Let's suppose that now we also want
to narrow the list by choosing just the
| | 03:02 | full-time people in these departments.
| | 03:04 | So the arrow for Status, let's unselect
them all and choose Full Time. Click OK.
| | 03:10 | And instead of 52 records, we're now
seeing, as we look in the lower left-hand
| | 03:14 | corner, 29 records and we might want to
narrow it even further by just focusing
| | 03:20 | on the top performers, those whose Job
Rating-- and let's click the arrow for that.
| | 03:25 | Unselect them all. Job Ratings
4 and 5, the two top in our categories here.
| | 03:30 | Click OK and instead of
29 records, we're now seeing 16.
| | 03:35 | We might want to print
this data. Before doing so,
| | 03:38 | maybe it would make sense to sort.
| | 03:40 | You can use regular sorting tools, if
you're familiar with the AZ and the ZA
| | 03:44 | buttons. You can certainly do that.
| | 03:46 | But if we click the drop-arrow for
department, notice here's Sorting here too.
| | 03:50 | Perhaps it's a little more pertinent.
| | 03:52 | What we're about to do here is sort this
perhaps for printing or just visual reasons.
| | 03:56 | Only the data in the visible rows is
going to be sorted as we click this option.
| | 04:02 | You saw how we used different fields here.
| | 04:04 | If we change our minds again, we
might go in two different directions.
| | 04:07 | One might be that we want to hold on to some
of our filtering criteria and get rid of others.
| | 04:12 | So if that's the case, we possibly
would go back for example to Status and
| | 04:16 | select them all, so
that's no longer a criterion.
| | 04:20 | But if we want to get rid of all the
filters, we've two other options as well.
| | 04:24 | If we'd like to keep the filter
arrows here, we can simply click the Clear
| | 04:29 | button and that will bring back all of the
data while keeping the filter arrows in place.
| | 04:35 | You can see how that looks.
| | 04:36 | I am going to press Ctrl+Z to undo, to go back.
| | 04:40 | Suppose we like to get rid of all
filtering for the moment, including the filter
| | 04:44 | arrows, then we'll just click the
Filter button. All the data will be returned
| | 04:49 | and the filter arrows are gone.
| | 04:51 | So it's a very easy for the most
part feature to use, and in its simplest
| | 04:55 | form as we saw it here on a single column
or even expanding it into multiple columns,
| | 05:00 | it's a great tool for paring down the
data so you can focus on just what's
| | 05:05 | important to you at the moment.
| | Collapse this transcript |
| Taking a look at special numeric filters| 00:01 | When you work with Excel's filtering
capability, you should be aware of the fact
| | 00:04 | that Excel does recognize the kind
of field data you are working with.
| | 00:09 | Let's suppose that in this particular
worksheet we want to Fflter and maybe we
| | 00:13 | want to filter by Date or Status.
Sometimes we're not even sure.
| | 00:16 | Let's apply the Filter.
| | 00:18 | If we click the arrow for Status, we do
see that Excel recognizes that as a text
| | 00:23 | field and gives us somefilter possibilities.
| | 00:27 | If we were interested in looking at
Column G and applying a filter, that's a Date
| | 00:32 | column, we see Date filters, a
totally different kind of list there.
| | 00:38 | But let's suppose we're interested in
the Salary column, Number filters, and we
| | 00:43 | see some choices here and this is an
expansion on prior capability in Excel.
| | 00:48 | We simply want to find, for example,
those records where people's salaries are
| | 00:52 | within a certain range.
| | 00:54 | So we'll choose Between.
| | 00:56 | We don't have to worry
about setting up the logic here.
| | 00:58 | It's all done for us.
| | 01:00 | We're interested, for example, in
seeing all those records where the salaries
| | 01:04 | are between 60,000 and 70,000. Click OK.
| | 01:09 | And there they are.
| | 01:10 | We could sort those
Descending, Ascending, as necessary.
| | 01:14 | If on the other hand we say let's
find all of those salaries above average.
| | 01:19 | Once again, something new, easy to get
to. We don't have to worry about setting
| | 01:23 | up a formula. We don't have to refer to
some cell that has that average in it.
| | 01:27 | Above Average, and of course
this is a moving target too.
| | 01:31 | What are we seeing now?
| | 01:32 | All the salaries that are above average.
| | 01:34 | Lower left-hand corner reminds us
that's roughly half the records.
| | 01:38 | So the ability to get to numeric
filtering is simpler and has been expanded.
| | 01:44 | It's a lot easier to use than prior versions.
| | 01:46 | And once again, because Excel
recognizes the kind of data that you have in a
| | 01:50 | field, it makes filtering much more efficient.
| | Collapse this transcript |
| Harnessing special date filters| 00:01 | In this worksheet called ApplianceSales,
there are about 900 records and it
| | 00:06 | covers data over a two-year period.
| | 00:08 | And let's imagine that our filtering
needs revolve around Column E, what's
| | 00:13 | happening in the Date column.
| | 00:14 | Let's apply the filter by going
to the Data tab, clicking Filter.
| | 00:18 | If you have worked with date filtering
in the past, it's been one of the more
| | 00:22 | cumbersome capabilities of Excel in
the filter arena and very often in prior
| | 00:27 | versions you needed to go into the so-
called Advanced Filter to get these done.
| | 00:31 | I am going to click the drop arrow
here and you will of course see date
| | 00:36 | filters, just as we might have seen numeric
filters or text filters, but huge expansions here.
| | 00:42 | And imagine, perhaps thinking of your
data, how you might use some of these
| | 00:46 | filters to narrow down the data from
certain months or weeks or quarters, and
| | 00:51 | don't overlook the second to last choice here.
| | 00:54 | All Dates in the Period.
| | 00:55 | Now, I remember distinctly how in prior
versions if you wanted to get data just
| | 00:59 | from July and the data might have covered
multiple years, this was not easy. Now it is.
| | 01:06 | I'm going to click July
and we're seeing July data.
| | 01:09 | There it is for 2011, there it is for 2010.
| | 01:12 | It isn't necessarily sorted,. Maybe
that has to do with how the data appeared
| | 01:16 | ahead of time, but it's so easy to get to.
| | 01:18 | And let's not overlook, also as we
click this drop arrow, choose Date Filters,
| | 01:23 | maybe we want to see the data from a
certain quarter, and possibly next quarter,
| | 01:28 | this quarter, or last quarter, but
once again, going down to the second last
| | 01:32 | choice here, how about just the data
for Quarter 3 over the two-year period?
| | 01:37 | And here too we might or might not want
to sort the data, but we're seeing the
| | 01:40 | data there for September and then
August and July and so on and so on.
| | 01:44 | Maybe we want to sort on opposite
order, but you get the idea here.
| | 01:47 | Out of all our records here, 229 out of
the 909 are from the third quarter, and
| | 01:53 | it's so easy to get to with
this new enhanced datefilter.
| | 01:58 | Anybody who works with date type data
will really appreciate the huge advances
| | 02:02 | here with this capability.
| | Collapse this transcript |
| Creating a top-ten list by value or percent| 00:01 | When you want to show just the highest
or the lowest values in different fields
| | 00:05 | of a list, you can use a feature called Top 10.
| | 00:09 | Now immediately, we want to suggest that
that's just you might say a marketing term.
| | 00:13 | Top 10 could mean Top 5,
Top 8, Top 40, whatever.
| | 00:17 | It could just as easily mean Bottom 10,
Bottom 40, whatever. And the number 10
| | 00:23 | could be an actual number
or it could be a percent.
| | 00:26 | So it's a lot more flexible
than the actual name itself.
| | 00:30 | Let's click anywhere in this data and
apply a filter. And maybe we want to see
| | 00:34 | the top ten salaries within the company.
| | 00:37 | The records associated with those salaries.
| | 00:39 | Click the drop arrow for Salary > Number
Filter s> Top 10, and here's where we see
| | 00:45 | the variation coming in if we wish to choose it.
| | 00:48 | Top could be Bottom.
| | 00:50 | Let's leave it at Top for now.
| | 00:51 | It could be 10 or any number we want.
| | 00:54 | Maybe it's the Top 15. Change it.
| | 00:57 | And we simply want to see the Top 15
records, keep it at Items if we want to see
| | 01:01 | the top 15%, we'll choose that.
| | 01:04 | So you can quickly figure out
how to make this work for you.
| | 01:07 | We want to see the top 15 items, the
five 15 salaries, and the records associated
| | 01:12 | with them, there they are. Here too.
| | 01:14 | If we want to sort these, that's fine.
| | 01:16 | Click the drop arrow,
descending order, there we go.
| | 01:19 | Top 15% would probably be
a lot more in this example.
| | 01:22 | We've got about 700 records.
| | 01:23 | So we'd be seeing about a hundred of these.
| | 01:26 | So we did Top 15%. Top 10%, about 75 or so.
| | 01:30 | So you can easily make these work for you.
| | 01:33 | I do want to show you an idea that's
going to be a little bit confusing though.
| | 01:37 | Let's remove this filter, just this
filter for here, and remember if it's only a
| | 01:41 | single column, you might remove
the filter here by clearing it.
| | 01:45 | We could just as easily have cleared
the filter from the Ribbon, because we
| | 01:49 | don't want filtering anywhere for the moment.
| | 01:51 | So either way it works.
| | 01:52 | Now let's imagine that we were
focusing on just the people in one department.
| | 01:56 | So I am going to use the filter here on
the Department field, unselect them all,
| | 02:00 | and just focus on Manufacturing people.
| | 02:03 | It is the biggest department here,
click OK, we've got 151 records.
| | 02:08 | So you might be saying "Well, I want to
see the top 10 salaries in Manufacturing."
| | 02:13 | Click the drop arrow here,
Number Filters > Top 10 items. OK.
| | 02:17 | But we are only seeing 3.
| | 02:18 | So what is that telling us?
| | 02:20 | Out of all the top 10 salaries in
the entire list, only three of them are
| | 02:25 | coming from Manufacturing.
| | 02:27 | And so, if you really do want to see
the top 10 within Manufacturing, let's
| | 02:31 | remove this last filter, the top 10
based on Salary, let's clear the filter from
| | 02:38 | there only and then here's our
list of all the Manufacturing people.
| | 02:42 | Let's simply sort that list in descending order.
| | 02:46 | Largest to smallest.
| | 02:47 | Here are the top 10 salaries here as
we look at the list and you just have
| | 02:52 | to see it that way.
| | 02:54 | But don't confuse as we can see how
easily that could happen if you choose Top
| | 02:58 | 10 after already having a filter in place.
| | 03:01 | It could be misleading.
| | 03:02 | But there are some real advantages to
the capability, and again, as long as
| | 03:05 | you remember that the 10 can be any
number you want, you can really make it
| | 03:09 | work to your benefit.
| | Collapse this transcript |
| Creating custom filters| 00:01 | In this worksheet consisting of
scientific data, in Column B we see codes.
| | 00:06 | If you work with coding structures you
know how strange they can be sometimes.
| | 00:09 | It just so happens that based on the
codes that we're seeing here, we need to
| | 00:13 | find those that have B in the second position,
like this one, and then later the letter Q.
| | 00:19 | Now, here is a Q right behind it.
| | 00:21 | There might be some others.
| | 00:22 | Here is a Q that's a few characters removed.
| | 00:24 | We want to find all situations where B is
the second letter and then later there's a Q.
| | 00:29 | So let's apply a filter to this data.
And click the drop arrow for Code, Text
| | 00:35 | Filters > Custom Filter.
| | 00:39 | Not equals, but begins with, anything,
we don't care what it begins with, and
| | 00:46 | like in our previous example,
question mark, then B is the second character.
| | 00:51 | The asterisk symbol means 0, 1, or many characters.
| | 00:56 | So after the B, either immediately or
one or two or three or four characters
| | 01:01 | later, a Q is going to
cover all those situations.
| | 01:04 | I'll click OK and in our results we see
B in the second position in all of these.
| | 01:12 | Here the Q occurs immediately after the B.
Here it's about four characters away.
| | 01:17 | Here it's immediately after.
| | 01:18 | Here it's two characters away. So in all cases.
| | 01:22 | So just seeing one example of how you
might use question mark and asterisk and
| | 01:27 | sometimes together like we saw here,
you see some very unusual constructions.
| | 01:32 | So you can imagine the occasional need,
particularly with code numbers, to pull
| | 01:36 | out some very unusual data by way of a filter.
| | Collapse this transcript |
| Copying and sorting filtered lists| 00:01 | It's not uncommon when viewing a filtered
list to want to copy the data elsewhere.
| | 00:05 | There are special rules that allow
us this to proceed pretty quickly.
| | 00:09 | You don't have to worry about the hidden data.
| | 00:11 | You also have the option of pasting the
data as values or actually pasting it to
| | 00:16 | include formulas where present.
| | 00:18 | Let's imagine in this particular list
here, we want to apply a filter and maybe
| | 00:23 | we just wanted to see
people from certain departments.
| | 00:25 | So clicking the arrow for Department
will unselect all of these and choose just
| | 00:30 | the people in the ADC group and in
the Audit Services group. Click OK.
| | 00:35 | And we may or may not want to sort these,
but if we do, we could do that pretty
| | 00:39 | quickly here, sort A to
Z. We've isolated the two.
| | 00:42 | Let's simply highlight the data.
| | 00:44 | We might want to include the titles as well.
| | 00:46 | There is a concern though as we
consider highlighting the data.
| | 00:50 | What about formulas that might be in here?
| | 00:53 | Here is a formula here, rather long,
that easy formula that extracts the month.
| | 00:59 | There is another formula right there
that calculates the number of years a
| | 01:03 | person has been here.
| | 01:05 | There is some concern about what's
going to happen if we copy/paste data like
| | 01:09 | this and the other concern is if we
highlight all of this data, what about the
| | 01:13 | data in the hidden rows?
| | 01:15 | Well, if we manually select this data,
just drag across and copy it, you might
| | 01:19 | right-click and Copy and possibly
Ctrl+C. Go to a different worksheet.
| | 01:23 | Now if you don't have a
blank one handy, nice shortcut.
| | 01:26 | Shift+F11 will create a
brand-new sheet just to left.
| | 01:29 | Let's jump out here, right-
click and paste. There we go!
| | 01:34 | Re-adjust the column widths,
double-clicking any column boundary.
| | 01:38 | We've got all of our data
and just the visible data.
| | 01:40 | We didn't have to worry about that.
| | 01:41 | How about the formulas in
the Monthly column here?
| | 01:45 | Well, those are no longer formulas.
| | 01:46 | Well, based upon what you want to do
with this data, maybe this is just fine.
| | 01:50 | Years of service, it's accurate right
now, but it won't be within a day or so.
| | 01:55 | Even tomorrow that may or may not be all right.
| | 01:58 | So let's reconsider how
we might want to do this.
| | 02:01 | Many times what we just did is just fine.
| | 02:03 | Key idea, of course, was we didn't worry
about the hidden data. None of that got picked up.
| | 02:08 | Go back to the list where the filter was.
| | 02:11 | Data is still highlighted. Let's
copy this again, maybe a Ctrl+C. Those
| | 02:15 | so-called marquee lights are a reminder
that you're just picking up the visible data.
| | 02:21 | So once again, we will go to that other sheet.
| | 02:22 | It's called Sheet1.
| | 02:24 | This time, put the data below here.
Right-click, how about a Paste Special, and
| | 02:30 | this time formulas or possibly
formulas and number formats maybe.
| | 02:36 | We do want to keep those formulas,
click OK ,and we want the formulas to relate
| | 02:41 | to the data that we see here.
| | 02:43 | So here's one of them right here and
there's that big long formula and over
| | 02:48 | here's the one for Years.
We see that as well too.
| | 02:50 | So based on what it is you're trying to
do at different times, you may want to
| | 02:55 | as you copy and paste consider
pasting them as values, which is what happens
| | 03:00 | when you do a simple paste, or paste them
as actual formulas using Paste Special.
| | Collapse this transcript |
| Recognizing the limitations of standard filtering| 00:01 | Despite the power and capability of
the Filtering tool, there are some
| | 00:04 | situations that you just cannot handle.
| | 00:07 | Now, there have been huge improvements
in filtering and in prior versions, by
| | 00:11 | the way, filter was often referred
to as auto filter, but we have another
| | 00:15 | category called Advanced Filters.
| | 00:17 | Not to be covered in this particular movie.
| | 00:19 | Let's talk about those
situations that filter does not cover.
| | 00:22 | Let's apply the filter to this list here and
imagine that the following request came up.
| | 00:28 | We'd like to create a list here
consisting of those people whose salaries are
| | 00:32 | above 80,000 or whose years
of service are 20 or more.
| | 00:38 | Now if we start with salary here and
choose a number filter for all those that
| | 00:43 | are Greater Than Or
Equal to 80,000, there we go.
| | 00:46 | If we now click the Year Filter and
choose Number Filters and here we are
| | 00:51 | interested in those who have
been here 20 or more years.
| | 00:54 | I think if you haven't tried this, you're
already thinking what's going to happen here.
| | 00:57 | We are going to get results here for
people who meet both criteria at the same time,
| | 01:04 | and the original statement was
if the salary is greater than 80,000 or
| | 01:09 | the years are 20. There's no way that we
can handle this with a standard filter.
| | 01:15 | We must use an advanced dilter.
| | 01:17 | Thinking along similar lines, imagine
if you wanted to-- and I'll pull back all
| | 01:21 | of the data by Clearing-- you wanted to
come up with a list consisting of those
| | 01:26 | manufacturing people who are full-
time and at the same time, those quality
| | 01:30 | control people who are contract,
admittedly an unusual combination, but trying
| | 01:35 | to pull together disparate fields using
the Or as a joining word, that's one way
| | 01:40 | to describe it, is not going
to work with regular filter.
| | 01:43 | I think for most people, filter is
going to handle 95% to 100% of what you need
| | 01:47 | most of the time, but there is an
advanced filter to handle these kind of
| | 01:50 | unusual situations typically
involving the word Or in different columns.
| | Collapse this transcript |
|
|
3. Creating Automatic Subtotals in Sorted ListsSetting up subtotals| 00:01 | After sorting a list of data, perhaps
like the current list which is sorted by
| | 00:04 | department, you might want to
insert automatic subtotals at appropriate
| | 00:08 | breakpoints in the list.
| | 00:11 | Once you do this too there will be some
automatic expansion and collapsing tools
| | 00:16 | called Outlining Symbols, which you
can create using other features in Excel.
| | 00:20 | So this particular list here is
ready to have some subtotals inserted and
| | 00:24 | rather than that manually--
| | 00:25 | and it would take a lot time, there are
about 23 departments here, but imagine
| | 00:29 | manually putting a new row
in front of each one of these--
| | 00:31 | a prerequisite also is that your data
has not been converted into a table.
| | 00:36 | The next sheet to the right here is a table.
| | 00:38 | I'll click on this and if we were about to
use the Subtotals feature it's on the Data tab.
| | 00:44 | Recognize that Subtotal out here is grayed out.
| | 00:47 | You cannot use this on the data we first saw.
| | 00:50 | I am going to back to that first sheet.
We are ready to insert Subtotals here
| | 00:54 | and on the Data tab the
Subtotal command is available.
| | 00:57 | The active cell is anywhere in data.
We don't any have any empty rows or
| | 01:01 | columns within our data.
| | 01:02 | Let's click the Subtotal button and
using this feature over the years this is
| | 01:06 | this thing that I often forget.
| | 01:08 | Every time you come into this
command do check that first entry.
| | 01:11 | If you haven't been using it
sometimes it's got a different field there.
| | 01:14 | Make sure it's the field that you're
interested in that's going to be break
| | 01:17 | point, in this case Department.
| | 01:19 | The function you're going to use
most of the time is likely to be Sum.
| | 01:23 | There are some other variations there.
| | 01:25 | In this particular list we can add
Years, we could add Salary. Would we really
| | 01:32 | want to add Job Rating?
| | 01:33 | That probably wouldn't make a lot of sense.
| | 01:35 | Most of the other fields are text fields.
| | 01:37 | Now possibly we could've done Account,
but counting only one of these made
| | 01:41 | sense. Probably not all of them.
| | 01:42 | So you might debate that or
explore that a little bit.
| | 01:45 | You could only use one of these at
the same time on a given row here for--
| | 01:49 | These are going to appear in the Subtotal rows.
| | 01:51 | Let's click OK and we see what's happened here.
| | 01:54 | At the end of the ADC department let
me zoom back a little bit so we can
| | 01:57 | see some more of these.
| | 01:59 | We see Totals there for Years and
Salary, and we also see this for Admin
| | 02:05 | Training and of course for every department.
| | 02:07 | Now imagine what would have happened here
if we didn't have the data sorted and we
| | 02:11 | said introduce these subtotals
every time the department changes.
| | 02:15 | So that's, if not a formal prerequisite,
a pretty obvious prerequisite in terms
| | 02:19 | of an effective use of this.
| | 02:20 | Now just as important perhaps is seeing
all this is to recognize those unusual
| | 02:26 | characters that we see down
the left-hand side of the screen.
| | 02:29 | You'll see the numbers 1, 2, and 3.
| | 02:31 | 3 is you could say the current one that's set.
| | 02:34 | It shows us all the detail as
well as subtotals and grand total.
| | 02:38 | So I am going to click the
number 2 and watch this list collapse.
| | 02:43 | Now if you were about to print this,
because we see the word Total in column B
| | 02:47 | it might be a good idea to
make column B a bit wider.
| | 02:49 | We will just double-click
up there, possibly that idea.
| | 02:52 | Here and there check out your other
columns to make sure they're wide enough.
| | 02:55 | So conceivably you might want to
print this, you might want to hide these
| | 02:58 | columns in the meantime,
but we are seeing the detail.
| | 03:01 | Clicking the one over here is if
not worthless pretty close to it.
| | 03:04 | It simply shows the Grand Total only.
| | 03:07 | 2 shows again just the Subtotals and 3
shows the detail as well as the Subtotals.
| | 03:14 | Now if you were making a presentation on
this data and talking about the various totals,
| | 03:19 | if for example temporarily if someone
said, "Well, give me some more detail on
| | 03:22 | the compliance group here,"
notice to left there's plus.
| | 03:25 | So I click the plus; that group expands.
| | 03:29 | So we are seeing the detail for just that group.
| | 03:32 | And you do see of course
there is a minus over here.
| | 03:34 | We might want to collapse this.
| | 03:36 | So here and there possibly for display
reasons, possibly even for printing, you
| | 03:41 | might want to display the detail
for some of these, but not others.
| | 03:45 | So we have this quick ability to expand
and collapse the data. And possibly you
| | 03:52 | could leave your data in this form.
It all depends upon how frequently do
| | 03:56 | you do sorting, do you do filtering in
subtotals, because these totals here are in the way.
| | 04:02 | If you need to get rid of these, it's
simply go back to the Subtotal command and
| | 04:08 | then Remove All, and we
are back to our normal view.
| | 04:12 | So using the Subtotal feature is a
quick way to insert automatic subtotals at
| | 04:17 | various points in your list.
| | Collapse this transcript |
| Creating multiple levels and copying subtotals| 00:01 | You can introduce additional layers of
subtotals. In other words in the lists
| | 00:05 | we are looking at here we might have
major subtotals by department and have
| | 00:09 | minor totals by status.
| | 00:11 | If you look at the data for example for
Admin Training here, recognize that this
| | 00:15 | has been sorted secondarily by status.
| | 00:18 | So let's introduce subtotals again,
using the Subtotal command. Every time
| | 00:23 | there's a change in Department
Years and Salary Totals. Click OK.
| | 00:28 | Our major levels are totally in place.
| | 00:30 | Let's now introduce another layer.
| | 00:33 | Back to the Subtotal command and now we
would like to say every time there's a
| | 00:37 | change in the status. So we are going to
have a lot more subtotal lines and let's
| | 00:43 | continue to add Years and Salaries.
| | 00:45 | We do not want to replace current
subtotals, so uncheck that box. Click OK.
| | 00:52 | And now not only are we seeing
multiple levels, for example here's the ADC
| | 00:57 | department. Now because it's so small
those totals don't quite stand out maybe
| | 01:01 | as smoothly as we like, but it is
accurate. We had a Contract total, a Full Time
| | 01:06 | total of two people, half
time for one, hourly for one.
| | 01:10 | A little more obvious here perhaps or
little better looking maybe, as we look
| | 01:13 | at the Admin Training group.
| | 01:15 | There is the entire group there and
we see initial subtotal in row 14 for
| | 01:21 | contract people, another subtotal
in row 25 for full-time, and so on.
| | 01:26 | And notice also the outlining
symbols on the left-hand side.
| | 01:29 | We now have four of them.
| | 01:31 | If I click for three, we now see both layers
of Subtotals and the Grand Total at the bottom.
| | 01:39 | We are not seeing any detail.
Clicking the two will show us the major level of
| | 01:45 | subtotals and if we had only one
that's the way it would have looked anyway, one
| | 01:48 | layer of subtotals.
| | 01:49 | And clicking the one again
which show just the Grand Totals.
| | 01:52 | So have got this ability again
to expand and collapse at will.
| | 01:56 | And a common issue here is
sometimes people want to copy these totals.
| | 02:02 | Now I am going to insert a new worksheet
by pressing Shift+F11 just to the left here.
| | 02:07 | It's a quick way to insert a
new worksheet. Back to our data.
| | 02:10 | What happens if we say let's copy this
data? I'm going to highlight this data
| | 02:15 | here. Possibly I could have
hidden some rows ahead of time.
| | 02:18 | I would like to copy this data just this.
| | 02:20 | So let's select it. I will right
click and copy or Ctrl+C, either way.
| | 02:25 | Go over to this empty sheet, right-
click and paste, and what happens?
| | 02:30 | All the hidden data, different rows. I
am not going to worry about the column.
| | 02:34 | Unless we have got way too much data
here. That's not what you want probably.
| | 02:37 | So there is a workaround on this.
| | 02:39 | It does involve first of all possibly a
keystroke shortcut, if you know it, or
| | 02:43 | otherwise a rather obscure command.
| | 02:46 | And let's imagine we are back here
again. Let's say we are about to highlight
| | 02:50 | the data and we do that.
| | 02:53 | Now what we'd like to do here is to
copy just the data we are seeing and as I
| | 02:59 | suggested bury it in the Excel
command system on the Home tab and then the
| | 03:05 | extreme right button, Find & Select.
Nothing pops up out there, but there's a
| | 03:11 | feature called Go To Special and in
here's a choice called Visible cells only.
| | 03:18 | Could you found that one? Click OK.
| | 03:21 | And a slight screen difference on the
display here. We are only highlighting
| | 03:26 | right now the visible cells. If we use
Copy now, right-click, Copy, Ctrl+C or
| | 03:31 | Copy command, any of those,
| | 03:35 | then perhaps the picture is a little clearer.
| | 03:37 | Typically when you copy a range you
see these so-called marquee lights, some
| | 03:41 | people call them marching ants, around
the perimeter of a range, but in effect we
| | 03:46 | have got a whole bunch of different
ranges here that we are about the copy.
| | 03:50 | We are not copying the hidden cells.
| | 03:52 | So go over to the Sheet2 from before.
I want to paste just to the right of the
| | 03:58 | existing data and no paste special
here. Just a regular paste, there we go.
| | 04:02 | And we do not see the hidden rows
here. We see just we wanted to see,
| | 04:06 | the visible data only.
| | 04:08 | Now the keystroke shortcut and you
might get used to it if you use this feature
| | 04:11 | a lot, once again come back here and
let's imagine we have just highlighted the
| | 04:15 | data here, we are ready to copy it, but
we want to make sure we are choosing the
| | 04:18 | Visible cells only, keystroke shortcut Alt+Semicolon.
| | 04:22 | Selects the visible cells only, instead
of going to the Find & Select button and
| | 04:28 | Go To Special and Visible Cells and all of that.
| | 04:30 | Then we can do Ctrl+C for copy and
go to the other location and paste.
| | 04:34 | So its going to be handy at times.
You don't need to worry about this in a
| | 04:37 | filtered list, but in many-many
other situations in Excel, anytime you're
| | 04:41 | copying data and you've got hidden rows
or hidden columns and you don't want to
| | 04:46 | select that data, after first highlighting
everything that you want to copy, press Alt+Semicolon
| | 04:53 | before doing the copy.
| | 04:54 | So that situation will arise.
| | 04:56 | There is another issue here. Let's go
back to the top of the list and you might
| | 05:01 | as you look at the data here. I want to
go back and show the detail by clicking
| | 05:05 | the 4 here. You might want to include a
different account of information or how
| | 05:10 | many people in each department.
| | 05:12 | And this probably would show up a
little bit better if we had only one level,
| | 05:15 | but it should work here as well too and maybe
we'd like to show a total here of employees.
| | 05:20 | It's going to be more meaningful
for example in a list like this.
| | 05:23 | Now, there is no direct way to do this
other than what I'm doing right now.
| | 05:27 | Go back to the Data tab, go to Subtotal.
Every time there is a change in status.
| | 05:32 | This time let's add another line
called Count and we don't want to count the
| | 05:37 | years of the salary. We want to count
the employee name. We do not want to
| | 05:42 | replace current subtotals, so we click OK.
| | 05:47 | And there is Count setting out there
sort of all by itself and here's the
| | 05:50 | term Contract Count.
| | 05:51 | I think for most people this is not a
very appealing picture, but nevertheless
| | 05:56 | we do have a total out there.
| | 05:59 | Now a rather complex workaround that
you could use. I think that's the stuff
| | 06:03 | perhaps of another course, but when
you are trying to get different kinds of
| | 06:07 | totals like a Count and a Sum, that's
what total means here, trying to put
| | 06:11 | together at the same time
probably doesn't look real great.
| | 06:13 | But nevertheless at least we were able to
do that here, just by inserting a new row.
| | 06:18 | In other words a new level of subtotals.
| | 06:21 | So multiple level subtotals are
definitely a need. I think what we just did here
| | 06:25 | in the last example here may be not so
commonly used, but a great feature when
| | 06:30 | you no longer need the subtotals. Simply
remove them, back to Subtotals, and Remove All.
| | 06:36 | Once you get familiar with working
with subtotals the process of creating
| | 06:40 | becomes pretty fast and pretty
routine and so the idea of "well every time I
| | 06:45 | use my data, do I have to create subtotals," well
of course you don't, but it's an easy process.
| | 06:49 | It doesn't take very long, it can be
very fast and you can certainly do it with
| | 06:53 | multiple layers pretty fast as well.
| | Collapse this transcript |
|
|
4. Using Advanced FiltersUsing the Advanced Filter for complex OR criteria| 00:01 | Excel's filtering feature covers
lots of ground and it works in most
| | 00:05 | situations for most filtering.
| | 00:07 | But there is another tool
called an Advanced Filter.
| | 00:10 | Let's take a look at why we
might need this kind of a filter.
| | 00:13 | This is a little involved, a little
more cumbersome setting up, and it does
| | 00:18 | involve something called a criteria range.
| | 00:20 | What I am about to do is not at all
obvious, so now I'll have to explain a
| | 00:24 | little bit ahead of time and a little bit later.
| | 00:26 | When you see a criteria range, it is
usually in most examples that you might
| | 00:31 | see in an Excel book on
Advanced Filtering above the data.
| | 00:34 | So what I am about to do here with this
data is to highlight the first six rows
| | 00:39 | and then right-click and Insert, and
I am going to be copying the title row
| | 00:43 | here, simply by dragging across these
titles and I am going to drag this upward
| | 00:48 | with the Ctrl key held
down, put these into row 1.
| | 00:51 | You might see in some examples a
criteria range located to the right of the data.
| | 00:56 | It could be there.
| | 00:56 | It could be under the data, but there
are convenient reasons for putting it here.
| | 01:01 | Again, not all of this should fall
into place immediately. Here's an idea.
| | 01:05 | Suppose looking at this list
below here we need to pull out a list
| | 01:09 | consisting of those records where
either the years of service is 20 or more
| | 01:15 | or the salary is 80 or more.
| | 01:18 | You cannot do that with the
regular standard filter in Excel.
| | 01:22 | We can do this with an Advanced Filter.
| | 01:24 | What I am about to do now isn't exactly
obvious, but in cell I2 here, I am going
| | 01:30 | to put-in the formula >=20 and then and
certainly not obvious, in column K but
| | 01:38 | in one row below this,
I'm going to put in >=80000.
| | 01:45 | When we start to use the Advanced
Filter, it will prompt us for a criteria
| | 01:49 | range and this will be the criteria range.
| | 01:53 | If you know Access, you maybe familiar
with what's called a Query Grid and you
| | 01:58 | would have some understanding of this layout.
| | 02:01 | In no way is it intuitive but what it does
mean is years of service >=20 or salary >=80000.
| | 02:10 | So the active cell will be within the
data, and we're going to click on the
| | 02:15 | Advanced Filter located on the Data tab.
| | 02:19 | And in this dialog box, nearly every time
the list range Excel figures out just perfectly.
| | 02:24 | That's the entire list of data here.
| | 02:27 | You do have the option of
copying the results elsewhere.
| | 02:29 | We don't need to do that here.
| | 02:31 | Here is the indicator for criteria range,
and rather than collapsing the dialog
| | 02:36 | box here, let's simply scroll over to the
cells in question and highlight these cells here.
| | 02:42 | This is going to be the criteria range.
| | 02:44 | There is a Unique records option
only, which we won't use yet and we won't
| | 02:49 | need it in this example.
| | 02:50 | But as we click OK, watch the list
below row 7 change. And the indicator in the
| | 02:56 | lower left-hand corner says 125
of the records meet our criteria.
| | 03:01 | So what are we seeing here?
| | 03:02 | Here is a situation where
the years are 20 or more.
| | 03:06 | Next record, high salary, next
record, high years, and so on.
| | 03:10 | In some cases potentially both
criteria will be met, but based on the criteria range,
| | 03:16 | if either one of these is true,
we will see it appear in the list.
| | 03:23 | And there it is, there is the entire list.
| | 03:24 | We might make more sense out of this a
little more readily if we sort on one of
| | 03:28 | the two fields. We could easily do that
with the active cell in either location.
| | 03:33 | Maybe we'll do a ZA sort of the
toolbar this way. We see the data here.
| | 03:37 | Here are all the people
who make more than 80,000.
| | 03:40 | Some of them do have 20 years,
most of them don't, and so on.
| | 03:45 | There is the entire list.
| | 03:47 | an unusual construction, an
unusual way to set this up.
| | 03:50 | Now, could this criteria range
have been to the right of the data?
| | 03:54 | Yes, it could have been, but in
performing the filter, because rows are
| | 03:58 | hidden, we can see that row 8 is
hidden and row 10 is hidden, then we
| | 04:03 | couldn't quickly review and look at the
criteria range to make sure it was working properly.
| | 04:08 | That's why more often than not when you
see a depiction of an advanced filter in
| | 04:13 | an Excel book, it's likely to have
been setup above the data like this.
| | 04:18 | So there are other situations as well,
and it is unusual. The Advanced Filter
| | 04:23 | usually covers situations where you
just cannot get to these using the
| | 04:27 | standard filter.
| | Collapse this transcript |
| Using the Advanced Filter for complex multiple-field criteria| 00:01 | When you're trying to use filtering
on multiple columns involving multiple
| | 00:04 | criteria, the standard filter
just doesn't work some of the time.
| | 00:09 | We need to use an advanced filter.
| | 00:11 | Suppose in the data here comprised of
about 700 rows, we need to see people
| | 00:16 | from a certain department who are full
time and we also want to see people from
| | 00:20 | another department who are half-
time. And you can imagine some other
| | 00:24 | combinations like that.
| | 00:25 | What we need to do is set up a criteria
range, so let's highlight the first six rows,
| | 00:31 | put this above the data, insert,
and simply copy the top row here, holding
| | 00:37 | down the Control key drag this upward.
| | 00:39 | Let's just pick some departments here,
for example ADC. Rather than typing this,
| | 00:44 | I am just going to copy this up here.
| | 00:46 | We would like to see out of the ADC
department, the full time people. There are
| | 00:50 | two of them. Once again to save myself
some typing here, something with the Ctrl
| | 00:54 | key I am going to drag this here.
| | 00:56 | We are also interested in
getting and seeing people from the
| | 01:00 | Engineering/ Maintenance group.
| | 01:01 | So once again saving ourselves some
typing, holding down the Ctrl key. This.
| | 01:07 | And here we are interested in seeing
from that group the half-time people, so
| | 01:11 | I will just copy this.
| | 01:13 | As a criteria range, this list here
first of all does ignore columns D and E.
| | 01:20 | If we use the Advanced Filter on the data,
starting at row 7, what we are saying in
| | 01:24 | English is let's see all records where
the department is ADC and Full Time, in
| | 01:31 | other words that combination
together, or if we have got a combination of
| | 01:36 | Engineering/Maintenance and Half-Time.
That's what this criteria range means.
| | 01:42 | So clicking anywhere in the data,
using the Advanced Filter, here is our list
| | 01:47 | range, totally accurate, criteria
range, click here, scroll upward, select
| | 01:54 | just these cells here.
| | 01:56 | So the ADC Full-Time people, Engineering/
Maintenance Half-Time people. Click OK.
| | 02:03 | Any record we see here meets the two criteria,
either this combination or this combination.
| | 02:11 | So another way to read this is as we
read left to right we are using the word
| | 02:16 | And, those who were in the Department
ADC and whose status is Full-Time, or those
| | 02:23 | people in Engineering/
Maintenance who are Half-Time.
| | 02:27 | And you can imagine different combinations here.
| | 02:30 | And you can come up with almost any
kind of combination imaginable, the more
| | 02:33 | you work with these.
| | 02:34 | Now, if were to put Engineering/
Maintenance here and this were the criteria
| | 02:39 | range, that would be very unusual and
highly unlikely what it would mean is
| | 02:44 | let's see all records where we either
have a combination of these two criteria
| | 02:50 | or anybody who's Half-Time or
anybody who's Engineering/Maintenance.
| | 02:55 | So even it wouldn't do that probably,
it's doable and if you're familiar with
| | 02:59 | the Access query grid,
| | 03:00 | this would help because you
understand in a same kind of way that's done.
| | 03:04 | But there's another use for an
advanced filter to not quite so obvious.
| | 03:09 | And that's, for example, if we wanted to
use a function on one of our fields.
| | 03:14 | For example suppose we wanted to
find all the people hired on Monday.
| | 03:18 | I think if I that were a sale day
perhaps it would be more pertinent. How would we
| | 03:21 | come up with this kind of construction?
| | 03:24 | And there's no standard
way to do this otherwise.
| | 03:27 | The following formula is our starting
point. Equal and I'm going to type in here g8.
| | 03:32 | What does that mean?
| | 03:35 | That's the first cell in the list
that has a higher date in it. We are not
| | 03:40 | seeing it currently because the other
filter is in place unless we use it here. =g8.
| | 03:46 | Now we want to find out if these people
were hired on Monday. You would need to
| | 03:50 | know that here's a function called
weekday and if the weekday of g8, meaning the
| | 03:57 | first cell that has a date, =2. If you
are familiar with the weekday function, 1
| | 04:04 | is Sunday, 2 is Monday etc.
| | 04:07 | Now, to make this a little more
readable let's go into our data and clear the
| | 04:11 | filter, bring back all the
data. So here is the data.
| | 04:14 | And the reason this says false right
here is the literally that very first entry
| | 04:18 | there is not a Monday.
| | 04:20 | I don't care what it is
really, but it's not a Monday.
| | 04:23 | Now here is what we are about to do.
| | 04:24 | We are going to use the Advanced Filter and
use these two cells as the criteria range.
| | 04:31 | Now without any knowledge of why this
doesn't work, I'm going to remove this
| | 04:36 | word here and we must do
this, this phrase, Hire Date.
| | 04:39 | This will be the criteria range and we
will see just the people hired on Mondays.
| | 04:47 | So with the active cell within our
data, we use the Advanced Filter.
| | 04:52 | The criteria range is not what it had
been. It is now going to be just these two
| | 04:56 | cells and this will not work if you
leave data in cell G1, so that has to go.
| | 05:02 | And here is the criterion that
we put in earlier and click OK.
| | 05:07 | And there are 112 of these records.
| | 05:09 | These are all Mondays. You could do a
quick test on one of these simply by
| | 05:13 | typing equal weekday, left parenthesis.
| | 05:17 | Click on one of these dates.
| | 05:18 | The answer will be 2, because its a
Monday and that's a Monday also and so on.
| | 05:24 | So pretty far away from being intuitive.
I have never even come close to calling
| | 05:28 | at that, but another use of the Advanced
Filter for unusual situations which you
| | 05:34 | cannot get to with the regular filter.
| | Collapse this transcript |
| Using the Advanced Filter to create unique lists from repeating field data| 00:01 | A not so obvious capability of
using the Advanced Filter is the idea of
| | 00:05 | creating a unique list.
| | 00:07 | Now sometimes you might do this even
for a single column. There are about 700 or
| | 00:11 | so here. We might want a
unique list of the departments.
| | 00:15 | Obviously we see recurring
entries here. We might have that need.
| | 00:20 | More expansive is the idea that
maybe you got duplicate records in here.
| | 00:24 | There are a couple right
here. Do we have others?
| | 00:28 | No we don't know necessarily and if so,
where are they? Let's get rid of them.
| | 00:31 | We are not going to be using the new
feature called Remove Duplicates, although
| | 00:35 | that's certainly an option.
| | 00:37 | But let's talk about the idea that we
can use the Advanced Filter to create a
| | 00:41 | new list that does not have duplicates.
| | 00:44 | How many records are here?
| | 00:45 | I am going to double-click
the bottom edge of this cell.
| | 00:48 | We see that it goes down the row 762.
| | 00:51 | Let's create a new list
using the Advanced Filter.
| | 00:54 | We are going to put that
list off to the right here.
| | 00:57 | We want to compare its
results with what we now have.
| | 01:01 | So the active cell is within our data.
Let's go to the Advanced Filter on the
| | 01:05 | data tab and we want to copy
the results to another location.
| | 01:11 | The list range is right here.
No criteria range is required.
| | 01:16 | We want unique records only.
| | 01:19 | What's the upper left-hand corner cell?
Well we are going to copy these two,
| | 01:22 | let's just scroll over to the right
there, and for example M1. That's going to be
| | 01:29 | the upper left hand
corner of the receiving area.
| | 01:31 | So we are not in any way altering the
existing list; we're about to create a new
| | 01:36 | list consisting of unique records only.
| | 01:39 | Let's click OK and that list is their
presumably. Let's go find it. Let's go to
| | 01:44 | the bottom of it too.
Double-click the bottom edge.
| | 01:48 | And this goes down to row 742.
| | 01:51 | So there were some other
duplicates in there as well.
| | 01:53 | And you might have seen at the top of
the list, there is one Juan Bishop, but
| | 01:57 | Juan Bishop below or above that record.
| | 02:00 | There we see what's happened.
| | 02:02 | So that capability works on the entire list.
| | 02:05 | Now also suggested was this idea.
| | 02:09 | We have got a list of departments here.
We just don't happen to have a list of
| | 02:12 | all of our department in one nice place.
| | 02:15 | Let's collect it real fast.
| | 02:17 | This time we are going to use column
C only and use the Advanced Filter for
| | 02:22 | just column C. Advanced Filter, adjust
this list range to be just column C,
| | 02:30 | copy to another location, no
criteria range, Copy to, let's scroll to the
| | 02:36 | right into an empty column.
| | 02:38 | Simply use Y1 here.
| | 02:40 | Unique records only. Click OK and over in
cell Y1 there is that list even with the title.
| | 02:49 | That's a unique list of our departments.
| | 02:51 | No repeats in there.
| | 02:52 | It's like we have got 23 different
departments and there they are. You probably
| | 02:56 | want to sort that, but nevertheless
there it is, quickly and easily.
| | 02:59 | So the Advanced Filter can be used
to create a unique list. In a latter
| | 03:04 | example, simply a unique list based on
column entries. Prior to that we saw how
| | 03:08 | it could be used to create a new
cleaned up list of this existing data, with no
| | 03:13 | duplicate records.
| | Collapse this transcript |
|
|
5. Eliminating Duplicate DataUsing the Remove Duplicates command| 00:00 | Getting rid of duplicate records in a
database, or a large list, addresses a
| | 00:04 | problem that occurs
frequently in large lists of data.
| | 00:08 | Finally, as of Excel 2007, a new
command called Remove Duplicates is now
| | 00:14 | available on the Data tab.
| | 00:16 | In large lists sometimes you
don't see duplicates that easily.
| | 00:20 | I happen to see one here in
Row 5. That's Juan Bishop.
| | 00:23 | Here is another Juan Bishop here in Row 10.
| | 00:25 | Now, the names being the same
| | 00:28 | alone isn't enough to indicate that
the record is duplicated, but a quick
| | 00:31 | look here, I think you can see pretty
clearly with Social Security number's and
| | 00:34 | phone number's duplicated.
That's a duplicate record.
| | 00:38 | Now, in prior versions of Excel, you needed
to use something called the Advanced Filter.
| | 00:43 | But with this new capability-- and by
the way, the data does not have to be in
| | 00:47 | any particular order.
| | 00:48 | It doesn't have to be sorted here. We
can remove duplicates relatively quickly.
| | 00:54 | In this list here, I am going to double-
click the bottom edge of a cell first of
| | 00:57 | all just to see how far down it goes.
| | 01:00 | It goes to Row 762. Double-
click the top edge down here.
| | 01:04 | Let's proceed with Remove Duplicates.
| | 01:07 | As with other commands in the Data
tab, as always before proceeding with
| | 01:12 | commands, if you know that your data
is altogether with no empty rows or no
| | 01:16 | empty columns, we can simply click on a
single cell on our data and start the process.
| | 01:22 | Remove Duplicates.
| | 01:24 | Now we possibly don't need
to check every single column.
| | 01:28 | In fact, you could say here well, as
long as the Social Security number and the
| | 01:33 | phone numbers are the same,
we know we've got a duplicate.
| | 01:36 | But on the other hand, these are all
checked and it's not going to hurt anything
| | 01:39 | here. If one of those is different
though, will not have a duplicate record.
| | 01:43 | It will not be deleted.
| | 01:45 | So we had 762 rows.
| | 01:47 | Let's proceed here using
all these columns. Click OK.
| | 01:50 | 20 duplicate values.
| | 01:53 | Now, I would quibble a little bit
with the wording there, but we're talking
| | 01:56 | about rows or portions of rows, records
perhaps, found and removed. We're down to 741.
| | 02:03 | So we get down to Row 742 because of the titles.
| | 02:06 | You could see only there is a Juan
Bishop here. There had been one in Row
| | 02:09 | 10. Not there anymore.
| | 02:13 | Down at the bottom, used to go to
Row 762. Now it goes to row 742.
| | 02:17 | So we did eliminate 20 rows here.
| | 02:20 | Excel's new feature called Remove
Duplicates is fast and easy. It works ideally
| | 02:25 | with large or small lists to
quickly eliminate duplicate information.
| | Collapse this transcript |
| Using a specialized array formula to identify data that's been duplicated | 00:00 | As fast and easy as the Remove
Duplicates command is to get rid of duplicate
| | 00:05 | data, what it does not do for you
is indicate what has been duplicated.
| | 00:10 | So for example, in this list here
which does have duplicates in it, there is a
| | 00:15 | Juan Bishop in Row 5, also one in Row 10.
| | 00:18 | If we want to identify which records
have been duplicated before getting rid of
| | 00:23 | them, what we need to do is sort the data.
| | 00:26 | So I do have another list here, an
exact list, but it has been sorted,
| | 00:30 | clicking over here.
| | 00:31 | And in this list we will
see two Juan Bishops together.
| | 00:36 | If you do want to identify duplicate
records here, what we need to do is after
| | 00:41 | doing the sorting, insert a new column
to the left of Column A. And what we are
| | 00:47 | going to do here is to put in a formula.
| | 00:49 | I want to show you or start off at
least with the long way and then show you
| | 00:52 | a nice quick way using an array formula to
identify which records have been duplicated.
| | 00:59 | So, here's the formula I
am going to put in here.
| | 01:00 | I can start probably in Row 2.
| | 01:02 | It's good to see everything in place. =if.
| | 01:05 | Now you may or may not be familiar
with the If function but this I think
| | 01:09 | almost explains itself.
| | 01:10 | Here is what we'd like to say.
| | 01:12 | And we want to include the word And.
| | 01:14 | And for the moment we are only thinking
of rows 2 and 3 and we know they are not
| | 01:18 | duplicated but they could be.
| | 01:20 | So we'd like to indicate if this cell B2=B3, comma,
and if C2=C3, comma, and if D2=D3, comma, and on and on and on.
| | 01:36 | Now this is going to get really long,
but if they are all equal, and why don't I
| | 01:41 | just cut it short here for the moment
and then show you a better way to do this?
| | 01:44 | But we're on the path here for this to
make sense. If these pairs are all equal
| | 01:50 | to one another, I'll just go this for
right parenthesis, comma. In other words,
| | 01:55 | if all those pairings are true, that
they are all matched, then what do we want
| | 02:00 | to see here over in cell A2? "Dup".
| | 02:04 | But if that's not the case, if any ne
of those is not the same then we want to
| | 02:08 | put in a word Unique.
Right parenthesis and Enter.
| | 02:14 | And those are unique and we can copy
this down the column relatively quickly
| | 02:18 | simply by double-clicking
the lower right-hand corner.
| | 02:21 | So all these are unique and sure
enough down here in Row 50 there we go, 53,
| | 02:27 | that's a duplicate, and
we'll find others eventually.
| | 02:29 | Now we've got several hundred names.
No reason to scroll through all of this.
| | 02:33 | We could have 70,000 or 700,000.
| | 02:36 | This at least identifies the
records that have been duplicated.
| | 02:40 | Possibly you can use a filter and just
show the duplicated ones. We could do that.
| | 02:44 | You will need a dummy heading up
here. Put in anything for the moment.
| | 02:48 | If we introduced filtering right now
we could then on this column right here
| | 02:53 | simply show those that are dup and
then see the list of the records that
| | 02:59 | have been duplicated.
| | 03:00 | Sometimes that's important, sometimes not.
| | 03:03 | But this is the way to get there.
| | 03:04 | Let me remove the filter and to
complete this formula and actually do it all the
| | 03:11 | way across, in other words not just to
Column F but all the way across, seems
| | 03:15 | like it would take a lot of work.
| | 03:16 | In other words, to really do the check
here fully we'd want to check all the way
| | 03:20 | over into in this example into Column L.
| | 03:23 | But here is a better way to do this
and there is a special kind of formula in
| | 03:26 | Excel called an array dormula.
| | 03:29 | So what we'd like to do here is
actually change this to read B2:L2=B3:L3.
| | 03:42 | In other words, as I delete all this,
all the cells B2 all the way over to L2 we
| | 03:48 | are going to check all of those and
we are going to compare them with the
| | 03:51 | corresponding cells in the row below.
| | 03:53 | And this is a lot shorter than what we just saw.
| | 03:56 | Now, this is what we call an array formula.
| | 03:59 | In order for this to work properly I
need to press Ctrl+Shift+Enter, not simply
| | 04:04 | Enter. Ctrl+Shift+Enter, there we go,
and I'll double-click to recopy all this.
| | 04:11 | We'll still find our duplicates down
there in the same location, but it's a
| | 04:14 | much shorter formula.
| | 04:16 | Let me once again display this a
little larger so we can see it, make it a
| | 04:19 | little bit wider, and so what we are
seeing again in English-- let me make this
| | 04:24 | even wider so we can see it.
| | 04:28 | We're going to compare B2 with B3 and
C2 with C3 and on and on and on all the
| | 04:33 | way over in Column L. If all those
are the same, we've got a duplicate.
| | 04:39 | If any one of them is
different it's going to be unique.
| | 04:41 | So this is how to identify
the records that are duplicates.
| | 04:44 | It's an unusual kind of formula.
| | 04:46 | You may or may not have seen array
formulas, but I think you'll see pretty clearly.
| | 04:50 | Any time there is the need to identify
the duplicate records, a formula like this
| | 04:55 | will get the job done.
| | 04:56 | If you'd like more information on
array formulas and how they work, you might
| | 05:00 | want to check out another course in
this series, Advanced Formulas and
| | 05:04 | Functions for Excel 2010.
| | 05:05 | There is an entire
chapter on array formulas there.
| | Collapse this transcript |
| Using an array formula to count the number of unique items in a list| 00:00 | When you need to count how many unique
entries there are in a column or a row,
| | 00:05 | a special array formula
will give you a quick answer.
| | 00:07 | Now this is an unusual formula that
meets the need for the fact that there
| | 00:11 | really is not a function in Excel called Unique.
| | 00:14 | As we look at this data on this
worksheet, the question might come up, how many
| | 00:18 | different entries do we
actually have in Column C?
| | 00:21 | How many different buildings do we have?
| | 00:23 | Those are things you would want to
know. Perhaps you know some of them.
| | 00:26 | How many statuses we have?
| | 00:28 | We know there are four, for example here.
| | 00:30 | But way to do this and you don't
necessarily need to do this above your data,
| | 00:35 | but I think we might want to
put it here at least temporarily.
| | 00:38 | By inserting a new row above Row
1 here and here is the formula.
| | 00:43 | Now it's an unusual formula and it is
an array formula and in effect what we
| | 00:47 | like to be doing here is counting or
comparing every single name here with every
| | 00:52 | other name to see if there are any duplicates.
| | 00:54 | What we really are trying to do though
is count the number of different entries.
| | 00:59 | We recognize that possibly we might
have two people with the same name.
| | 01:02 | But why not do this for every column?
| | 01:04 | So here is the function.
| | 01:06 | Let me zoom in on this too,
because it is unusual.
| | 01:10 | And in this column, let me do a
quick check at the bottom here.
| | 01:13 | There are 763 rows right now,
since we inserted a new one.
| | 01:18 | So we want to keep that number in mind.
| | 01:19 | =sum(1/countif( and I don't want to
highlight all these cells but I do want
| | 01:32 | to start dragging here at least
down to here and I'll just complete the
| | 01:36 | typing by putting in the 63.
| | 01:38 | So we want to take everyone of these cells here.
| | 01:41 | I am going to copy so I
don't want to have to retype it.
| | 01:45 | And compare with everyone of those cells
to count how many times it appears there. )).
| | 01:53 | Again, it's a hard function to explain
and it would take at least five minutes.
| | 01:56 | There is more information in another
course on Excel Advanced Formulas and
| | 02:00 | Functions, both through 2010 and
2007 you might want to take a look at.
| | 02:05 | But I am going to be pressing Ctrl+Shift
+Enter here to get a count of how many
| | 02:09 | unique entries there are from cells A3
to cells A763 and there is the answer.
| | 02:16 | 741, so all the names are unique.
| | 02:18 | Let's do a quick test here. I am going
to duplicate Troy Sanders name just by
| | 02:22 | copying it downward here.
Now of course we now have 740.
| | 02:25 | Let me undo that by pressing Ctrl+Z.
How many different buildings do we have?
| | 02:31 | We'll just copy this to the right
here. Six different buildings. How many
| | 02:35 | different departments do we have?
23 different departments and so on.
| | 02:39 | Possibly you'd want to copy this all
the way across. Or once you had done this,
| | 02:43 | if this is vital information and you
want to keep it handy, maybeyou put it to
| | 02:47 | the right side of your list or
possibly below it, wherever you wish.
| | 02:51 | One-by-one each of these formulas
has a similar look to it, and they are very
| | 02:54 | unusual, but it gets the job done.
| | 02:57 | It allows us to quickly make a count
of how many unique entries there are in a range.
| | Collapse this transcript |
|
|
6. Using Database Analysis ToolsUsing SUMIF, COUNTIF, and related functions for quick data analysis| 00:00 | The SUMIF/COUNTIF family of functions,
introduced in Excel 97, now include four
| | 00:06 | new functions in Excel 2007 and they
give you lots of tools for tabulating a
| | 00:11 | variety of statistical totals
when you have database type lists.
| | 00:16 | The functions and questions are listed
in Column J here. Normally you wouldn't
| | 00:21 | see these listed just like this, and
I've separated them because they're more or
| | 00:25 | less in groupings here.
| | 00:26 | SUMIF, COUNTIF and AVERAGEIF.
AVERAGEIF is new in Excel 2007.
| | 00:32 | And these functions you can almost
figure out what they do by their names.
| | 00:36 | This list here is substantial.
| | 00:38 | It might grow, it might shrink.
| | 00:40 | And we might want to know, for example,
simple question, how many half-time
| | 00:45 | people do we have, how many
full-time people do we have?
| | 00:47 | So start off actually with COUNTIF and use
this function right here, =COUNTIF.
| | 00:55 | Two arguments. Where are we looking?
| | 00:58 | Now you certainly can and I certainly
could here highlight all of the cells in
| | 01:01 | Column D, but it's a lot simpler
to simply click Column D instead.
| | 01:06 | So instead of doing all that let's just click.
| | 01:09 | And what's great about this capability
too, or this approach to the function is
| | 01:14 | that if we add names to the list or if
we take them out we don't need to change
| | 01:18 | the way this formula is being set up.
| | 01:20 | So using an entire column here makes sense.
| | 01:23 | Where are we looking?
| | 01:24 | Column D. What are we looking for? Full Time.
| | 01:27 | Now if I don't have that phrase
nearby in a cell, although I do to the
| | 01:32 | right here, I'll simply put in double-
quote and type in "Full Time".
| | 01:36 | And we are all done, Enter.
| | 01:40 | How many full-time people do we have here?
| | 01:42 | 393. Obviously we're going to keep
that, adjust the display of the format.
| | 01:47 | If we do have the data nearby, possibly
instead you would just click on that cell.
| | 01:53 | So how many full-time people do we have?
| | 01:56 | And if we somehow found that to be
valuable and we have the other kinds of
| | 02:00 | statuses listed next to this we could
clean up the display a little bit by
| | 02:04 | decreasing the decimals and simply drag
this down a few more cells, there we go.
| | 02:10 | And so we've got a Half Time count
and an Hourly count and a Contract.
| | 02:14 | So COUNTIF is pretty straightforward
and easy to use, and it does provide us
| | 02:19 | with good information about our data.
| | 02:21 | And if you want something like this to
be setup along with your data it's not a
| | 02:25 | bad idea to put off to the right.
| | 02:27 | But with one slight exception it
might not be so good. If you do a lot of
| | 02:31 | filtering, which of course does hide
rows, some of this data may be hidden
| | 02:35 | at different times.
| | 02:36 | Now the other option possible is to put
this above your data, but then it makes
| | 02:41 | these kinds of formulas not quite as
easy to write, because you couldn't be able
| | 02:45 | to use entire columns there.
| | 02:47 | All right, without going through all
the variations here but just a few more,
| | 02:51 | the SUMIF function does go a step
further and it allows us to tabulate some
| | 02:58 | data from another range.
| | 03:00 | And when we are using different
ranges here they must be parallel.
| | 03:04 | So let's say that instead of counting
how many people that we have that are full-time,
| | 03:08 | we want to know their total salaries.
| | 03:11 | So SUMIF has a third argument, comma,
and in this example it's Column G so
| | 03:16 | we'll just G, all done.
| | 03:19 | Total salaries of our full-time people.
There too we could copy that down if
| | 03:23 | wanted the information for the others.
| | 03:25 | And initially these two functions
were around for quite a while, a lot of
| | 03:29 | clamoring for this function, new in
Excel 2007, and you can imagine what it does.
| | 03:33 | Real simply quickly here we'll just
simply put in-- we want to know the average
| | 03:38 | salary of for example, the people that
are full time, and there it is, and we
| | 03:45 | could do the same for the
others as well too, so AVERAGEIF.
| | 03:49 | In prior versions before Excel 2007, if
you wanted to use multiple criteria you
| | 03:55 | had to use array formulas but these
three new functions here are just that they
| | 03:59 | are exactly nearer the others except
they end in S. I'll start with this one.
| | 04:05 | And I am going to point out here that
actually SUMIFs and AVERAGEIFs are little
| | 04:09 | different than COUNTIFs
and start with this idea.
| | 04:15 | We need to add up the salaries, not
only the people who are full time, but the
| | 04:21 | people who are full time
and have a certain job rating.
| | 04:25 | And we can have multiple sets of criteria.
| | 04:28 | So the SUMIFs function begins with
what it is we are trying to add up.
| | 04:35 | So the order of things here might get
a little confusing if you just remember
| | 04:38 | what we've done in the previous ones.
In the other functions that we just saw
| | 04:42 | the data to be tabulated
came last, not comes first.
| | 04:46 | So we want to be tabulating salaries,
comma, and now in pairs various criteria.
| | 04:53 | And we can have one pair, two pair,
three pair, quite a few in fact.
| | 04:57 | The first pair might be Status, comma, Full Time.
| | 05:03 | That's in cell L2 so I'll just refer to that.
| | 05:06 | We could hit Enter right here and
simply get total salary of our full time
| | 05:10 | people. We already did that with SUMIF,
but to expand on the idea here, SUMIFs
| | 05:15 | allows us multiple criteria, so
what else are we looking for here?
| | 05:19 | People who also have a job rating,
comma and because this is a number we don't
| | 05:26 | have to put it in quotes,
those who have a job rating of 5.
| | 05:30 | We could have another pair.
| | 05:31 | So the first pair indicates we want to
be looking at those who are full-time
| | 05:37 | and, even though we don't see the word and, second
pairing here is job rating of 5. We press Enter.
| | 05:43 | The total salaries of our full yime
people who have a job rating of 5.
| | 05:48 | It might be more interesting and more
valuable to be able to do this as an actual average.
| | 05:53 | So why not just pull this down
here and readjust it, put it next to
| | 05:56 | average, make a change?
| | 05:58 | We want to know the average salary of all
those people in our list here who are full time.
| | 06:05 | So we've got a pairing here who are
full time and who have a job rating of 5.
| | 06:12 | There is the average salary for that group.
| | 06:14 | Now again, there could be following the
five, a comma, a pair of criteria, two
| | 06:20 | more of these, another pair, another
pair, on and on and on and on, without long
| | 06:24 | cumbersome formulas.
| | 06:26 | So these are great tools for
tabulating data, particularly adept at handling
| | 06:31 | information across lists.
| | 06:33 | SUMIFs, COUNTIFs, and AVERAGEIFs.
| | 06:36 | If you want more examples you might
also want to check out the course on
| | 06:39 | Advanced Formulas and Functions either
for Excel 2007 or Excel 2010. They are
| | 06:45 | identical in the two different versions.
| | Collapse this transcript |
| Using database functions like DSUM, DAVERAGE, and DMAX| 00:01 | In Excel there is a category of
functions called database functions, and
| | 00:05 | although less frequently used in the
past because of the SUMIF family of
| | 00:09 | functions has been expanded greatly,
| | 00:11 | these database functions rely on the
kinds of criteria ranges used with
| | 00:15 | the Advanced Filter.
| | 00:17 | If you haven't used the Advanced Filter
these might seem a little strange to you.
| | 00:21 | But there are some advantages here and there.
| | 00:23 | And these functions do mesh well
with those Advanced Filter applications,
| | 00:28 | particularly when you've got unusual
calculations SUMIF and COUNTIF can't handle.
| | 00:33 | Now for reference in Column J
I've simply listed here the various
| | 00:38 | database functions.
| | 00:40 | If you remove the letter D from each of
these of course you probably recognize
| | 00:43 | most of those anyway.
| | 00:44 | Now here is a strange thing. In Excel
2007 and in Excel 2010 if you're a little
| | 00:50 | bit curious about these database
functions, you heard a little bit about them, if
| | 00:54 | you go to the Formulas tab you say,
"Well, I don't see them here, they must be
| | 00:58 | here under More Functions."
| | 01:01 | But we're not seeing them;
they're not shown here.
| | 01:04 | Maybe that's some indication as to
what Microsoft plans to do with these
| | 01:07 | functions in the future. But we don't see them.
| | 01:10 | However, if you click the fx button on
the left side of the Formula Bar and look
| | 01:15 | at the categories that are available
here you will see Database functions, and
| | 01:20 | here are those 12 functions.
| | 01:22 | And what they all have in common is
that they refer to a criteria range as
| | 01:26 | the third argument.
| | 01:27 | And if you have worked with the
Advanced Filter you probably recognize on the
| | 01:31 | screen here that potentially this is a
criteria range. This is or maybe the two
| | 01:36 | of them together are.
| | 01:38 | So, let's talk about how we
might use one of these, for example
| | 01:41 | DAVERAGE, =daverage.
| | 01:44 | We'd like to find the average
salary of our full time people.
| | 01:48 | Now I think a lot of you know
possibly you could be using one of the SUMIF,
| | 01:51 | COUNTIF, AVERAGEIF functions here.
| | 01:53 | Let's choose this function as an
example of how we might use the others.
| | 01:57 | We're looking at a database.
| | 01:59 | I'm simply going to drag across
columns A through F. In other situations you
| | 02:03 | might be highlighting all the cells.
Make sure you do include the title row
| | 02:07 | there. Comma. Which field are we
trying to tabulate data from?
| | 02:12 | From left to right it's the seventh
column, Column G. So we put in the number 7
| | 02:18 | here, comma, and how about this criteria range?
| | 02:22 | And here your knowledge of using
Advanced Filter would come into play.
| | 02:27 | We simply want to highlight the cells J1 and J2.
| | 02:30 | What's the average salary of our or
full time people, that's what we are
| | 02:34 | calculating right now, and there it is.
| | 02:37 | If we wanted to know the total salary
of those salaries that are greater than
| | 02:43 | 80,000 or maybe more interestingly a
count of how many salaries are over 80,000,
| | 02:48 | perhaps we'd use DCOUNT, =dcount.
| | 02:53 | So once again like in the previous
example we're looking at this database here. Comma.
| | 02:58 | We're tabulating a data from Column G
so that's the seventh column, comma, and
| | 03:06 | our criteria range this time
around is these cells here.
| | 03:10 | Tell me how many cells have salaries
greater than 80,000, and there are 71 of them.
| | 03:17 | So perhaps you can begin to see how
this has some merit. Again they seem to be
| | 03:22 | on the wane in terms of being widely
used in Excel worksheets, but there's some
| | 03:27 | real power and capability.
| | 03:29 | And if you do work with the Advanced
Filter concept you're aware of how these
| | 03:33 | have some real potential here and
there are quite a few variations on how you
| | 03:37 | use the Advanced Filter.
| | 03:39 | And these functions do mesh
nicely with that capability.
| | Collapse this transcript |
|
|
ConclusionNext steps| 00:00 | Upon completion of this course you
may want to check out additional courses
| | 00:04 | available at lynda.com.
| | 00:06 | For related courses there is Setting
Up a Database in Excel and also Cleaning
| | 00:11 | Up your Excel data.
| | 00:13 | For a review of basic Excel functions,
take a look at Excel 2010 Essentials.
| | 00:19 | And if you want to become a more
proficient user of this powerful software,
| | 00:23 | check out the Excel 2010 Power Shortcuts course.
| | 00:27 | And check back from time-to-time for
additional courses add to this series.
| | 00:31 | Thank you for watching.
| | Collapse this transcript |
|
|
