IntroductionWelcome| 00:04 | Hi! I am Dennis Taylor and I am pleased
to present Setting Up Tables, Lists and
| | 00:09 | Databases in Microsoft Excel.
| | 00:11 | In this course I'll show the
best techniques for structuring data
| | 00:14 | efficiently and coherently.
| | 00:17 | We'll look at how to a setup a form for
when you need to view data on a record
| | 00:20 | by record basis or show you how to
display your data as a table to provide
| | 00:25 | structure and tools for more efficient layout.
| | 00:27 | And we'll also show you the powerful
data validation command to ensure that data
| | 00:32 | will be entered more accurately and
with its powerful pick list technique.
| | 00:37 | It will be entered much faster as well.
| | 00:39 | Let's get started with Setting
Up Tables, Lists and Databases in
| | 00:43 | Microsoft Excel.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you are a Premium Member of the
lynda.com Online Training Library or if you
| | 00:05 | are watching this tutorial on the DVD-ROM,
you have access to the Exercise files
| | 00:10 | used throughout this title.
| | 00:12 | As you open a file as I am now, you will
see that there are a number of sheet tabs.
| | 00:18 | Many of these will be referred to in the
training, here and there you will see a
| | 00:22 | tab that is not used at all.
| | 00:24 | If you are monthly subscriber or
annual subscriber to lynda.com, you don't
| | 00:28 | have access to the Exercise files, but you can
follow along from scratch with your own files.
| | 00:33 | Let's get started!
| | Collapse this transcript |
|
|
1. Database Design ConcernsUnderstanding design concepts for setting up large lists| 00:00 | Excel has no hard and fast rules about
how you build data in worksheet, and if
| | 00:05 | you're setting up a database, whether
it's about people, like the one we see on
| | 00:09 | the screen here, your inventory or
orders or shipments, regardless of the
| | 00:13 | environment, a few basic concepts are
going to make your use of that data much,
| | 00:18 | much more efficient.
| | 00:20 | In many respects this particular
worksheet here embodies the basic principles of
| | 00:25 | what good design should look like.
| | 00:27 | Keep titles in a single row, they
don't necessarily have to be in row one.
| | 00:33 | If you need to have the company name
here, put an empty row above row one,
| | 00:37 | separate it from the rest of the data.
| | 00:39 | In the data itself, don't have any
empty rows, don't have any empty columns,
| | 00:44 | that's probably unlikely
anyway, don't put in spacing.
| | 00:47 | We're going to see some other
worksheets here that violate some of these rules.
| | 00:51 | If you have other date on this
worksheet that's necessary to have, for
| | 00:55 | example, we will be using
that TaxTable, for example.
| | 00:58 | We need that here, but we don't want a
part of our list, and the reason is that
| | 01:03 | many, many times when you're working
with large list, your database, you want to
| | 01:08 | do Sorting and Filtering, possibly
Subtotals, and use some of the other features
| | 01:14 | available on the data tab.
| | 01:15 | You might also want to use a
Pivot table, found on the Insert tab.
| | 01:19 | To use all of those features most
efficiently, keep your data together,
| | 01:24 | single, title row on top, row after
row with no empty rows, column after
| | 01:30 | column, no empty columns.
| | 01:31 | Any data off to the right have at least
one column to separate that from the data.
| | 01:37 | Notice that you can have empty cells,
column J has empty cells and it's totally
| | 01:42 | appropriate based on the description of
what's in there that's going to happen,
| | 01:47 | that's not going to be a problem.
| | 01:48 | And what this means too, if you
stick to these rules, is that when you do
| | 01:53 | sorting and filtering and use some of
the other features on the data tab, you
| | 01:57 | don't have to constantly highlight all
the data before proceeding into those
| | 02:02 | commands, just click on the single cell here.
| | 02:04 | If we start the sort process right now,
in the background you can see the data
| | 02:08 | is automatically being highlighted.
| | 02:10 | Now we can't see at all, and so
there's one more suggestion I would make here
| | 02:14 | too before using a lot of these
different commands, and particularly if you're
| | 02:18 | not too familiar with the data.
| | 02:20 | A good little step ahead of time with
the active cell in your data, press Ctrl+A
| | 02:26 | and then to see how big this entire
list is, press Ctrl+Period a few times.
| | 02:31 | Now this seems like strange thing to
do, it simply moves the active cell
| | 02:35 | around the corners of the range, and
that verifies the data that will be used
| | 02:41 | when you click a single cell and start to do
sorting and filtering and the other features.
| | 02:47 | Now the next sheet in this workbook
Interim Totals has totals put in here and
| | 02:52 | that's all well and good, and that's
interesting information, but if we want to
| | 02:56 | do sorting and filtering and all those
other things, these are in the way, we
| | 03:00 | need to get rid of them.
| | 03:02 | So you will see worksheets like this,
and again, many, many times worksheets
| | 03:07 | that don't work so well on Excel in
terms of commands we're talking about,
| | 03:11 | have been designed for appearance if
for printing reasons or display reasons,
| | 03:15 | and that's not always in sync with the
actual need that you have to manipulate the data.
| | 03:21 | The next worksheet over same basic
idea really, but here the rows are empty.
| | 03:27 | And the example here is that just
brings out the data better, it is much easier
| | 03:30 | to read, that's good.
| | 03:32 | But on the other hand if we want to work
with our data efficiently, this is not so good.
| | 03:36 | So we need to get rid of the empty
rows here, quick way to do that, manually
| | 03:39 | select all the data and just sort it and
then the empty rows will end up at the bottom.
| | 03:44 | Multi-row titles, we mentioned
that's not a great idea either.
| | 03:47 | Fortunately, over the years excels has
clean this up a bit, and we've seen cell
| | 03:52 | A2, how the data should look, not
Employee, and then Name, take a look what
| | 03:57 | we've got in column D here,
Social Security Number and Number.
| | 04:01 | A quick fix here, by the way, if
you did see data like this, would be to
| | 04:05 | double-click right in front of the N
in Number, type SS and press Alt+Enter,
| | 04:11 | that introduces a line break.
| | 04:13 | And once you get used to that concept,
when you are putting in titles and you
| | 04:17 | don't want the column to be wide,
but you don't mind the data stacking up
| | 04:21 | like this, type an entry, press Alt+Enter for
a line break, and you can do it multiple times.
| | 04:27 | So ideally you'd be doing that for these
other cells over here too, and then get
| | 04:31 | rid of row one, you want to
have your titles on a single row.
| | 04:35 | And sometimes you'll see data like this
on this sheet here, this is based on a
| | 04:40 | real-life example from a city that I
did some work with, I've changed all the
| | 04:43 | pertinent data really, but this is
the basic layout in the way this looks.
| | 04:47 | Again, it's good information but it
laid out in such a way that you can't do
| | 04:52 | sorting on it, you can't do filtering.
| | 04:55 | What if we need to add data for the year 2012?
| | 04:58 | How we're going to do that efficiently?
| | 04:59 | You see the way that data is laid
out here in column B for the year?
| | 05:03 | Probably this was design for display
reasons, people can look things up, that's
| | 05:08 | again, all well and good, but if you
want to work with this data efficiently,
| | 05:12 | you have to go through
some serious restructuring.
| | 05:14 | And in this example here, probably
what you're going to have to do is maybe
| | 05:18 | put in a new column.
| | 05:20 | For example, imagine in this section,
if this data gets bumped to the right,
| | 05:23 | take this data and then put it in here
multiple times, then get rid of rows like
| | 05:29 | this, and you get the idea here, we've
got some work ahead of us, if we want to
| | 05:33 | turn this into something
that's going to be useful in Excel.
| | 05:36 | And so all these different ideas will
come together and the worksheet that we
| | 05:40 | first saw here, and even though this
is simply an HR list, it could certainly
| | 05:44 | cover the basic idea that we want to
see in all worksheets that we're going to
| | 05:49 | be using the data commands.
| | 05:51 | Titles on a single row, no empty
rows, no empty columns on our data.
| | Collapse this transcript |
| Creating appropriate name, address, and time fields| 00:00 | If you work with database software,
for example, Access, you're probably
| | 00:04 | familiar with the idea or the concept
where each column or each field should
| | 00:08 | contain the minimal amount
of information necessary.
| | 00:12 | And that's a bit vague.
| | 00:14 | This particular worksheet has four
potential problems, three major ones.
| | 00:18 | As you look at this list, you
might at some point say, well;
| | 00:21 | I want to sort this by last name.
| | 00:24 | Well, look at the data in
Column A, first name, last name.
| | 00:28 | That's great for mailing labels, but
we cannot sort this list based on the
| | 00:32 | data looking like that.
| | 00:34 | Ideally, we should have those names
either in separate columns, or we need
| | 00:39 | to reverse the order.
| | 00:40 | And similarly in Column C, if we
wanted to sort this data by State or by Zip
| | 00:47 | code, we cannot do that.
| | 00:49 | And so the idea that each column should
contain a minimal amount of information,
| | 00:53 | starts to make a little bit of sense.
| | 00:54 | Ideally, we should have a separate
column for City and State and Zip.
| | 01:01 | And if we have hundreds and hundreds
of records, thousands of records, not
| | 01:05 | only do we want to sort by State, but
we want to have within each State, have
| | 01:09 | those in order by City.
| | 01:10 | So it would make good sense to
split those into separate columns.
| | 01:14 | Column B, perhaps less of an issue,
depends upon the nature of the data, but you
| | 01:18 | can imagine if this were data for a
City Department, the Waterworks or the
| | 01:22 | Street Works Department.
| | 01:23 | If they need to notify people within a
certain range of addresses on Harrison
| | 01:28 | Street, there is no way to
sort this data by Street name.
| | 01:32 | And so, again, depending upon the
nature of the data and what you're doing with
| | 01:35 | it, the idea that you want to split
your data into more columns is a good one.
| | 01:40 | And it's much easier to pull data
together when it's in separate columns, than
| | 01:45 | to pull it apart when it's
like the data we see here.
| | 01:48 | And in Column D we're seeing
an unorthodox use of dates.
| | 01:52 | We can all read them and understand them,
but we can't work with dates like that
| | 01:56 | in any kind of computational sense.
| | 01:58 | And all these different issues of
cleaning up data, you'll get some good tips in
| | 02:03 | the lynda.com course on Cleaning up Your Data.
| | 02:06 | All of the issues here can be dealt with
directly and ultimately they can be fixed.
| | 02:12 | But this is not good design from a
number of different perspectives.
| | 02:15 | Keep a minimal amount of data in each
column, and then your use of database
| | 02:20 | features and efficient use of
Excel is going to improve greatly.
| | Collapse this transcript |
|
|
2. TablesUsing Excel tables for database data| 00:00 | If you want to work with a database
type of list efficiently or if you want to
| | 00:04 | set up a new list with a minimum
amount of difficulty, you might want to
| | 00:08 | consider using Excel's Table feature.
| | 00:11 | It was new in Excel 2007;
| | 00:13 | it's actually the successor to the
older feature called List, which was not
| | 00:17 | so well implemented.
| | 00:18 | This data here could look better;
| | 00:21 | it could be easier to work with
if we convert it into a Table.
| | 00:24 | The next sheet over actually is the same
data, but it has been converted into a Table.
| | 00:30 | The first obvious difference is every other
row is highlighted that makes it easier to read.
| | 00:35 | As I'm scrolling here notice what's
happening to the titles, now do you really
| | 00:39 | care what the column letters are?
| | 00:41 | Now you might, but on the other hand
the Table emphasizes the idea that we've
| | 00:46 | got field names that third column,
whether it's column C or not it contains
| | 00:52 | Department information that's
the pertinent fact about that data.
| | 00:55 | If it somehow bothers you and you do
want to see the column letters simply apply
| | 01:00 | the frozen titles feature, go to the
View tab>Freeze Panes, Freeze that Top Row.
| | 01:06 | Now there are other features
related to Tables, one is filters are
| | 01:11 | automatically in place that also means
we can Sort the data quickly and easily,
| | 01:15 | and anytime we use any of the
database like features here, we don't need to
| | 01:19 | highlight all the data.
| | 01:21 | The Table concept treats our data as an
entity and as we'll see, as we add rows
| | 01:27 | and columns or new data to the right
below everything will stay together intact,
| | 01:33 | so there are some real advantages.
| | 01:35 | Sometimes you want to start creating a
list and you've got no data whatsoever,
| | 01:40 | so on Sheet1 I do have a little bit of
data just some titles, I might want to
| | 01:44 | start by turning this into
a Table as well, same idea.
| | 01:49 | Now the way we convert data into a
Table can actually be done four different
| | 01:53 | ways, we can start with a keystroke
shortcut Ctrl+T think of T for Table or
| | 01:59 | Ctrl+L, L for list, more likely is
the Insert tab and choose Table, now the
| | 02:06 | active cells right there in the midst of that
single title row, so we can start from here.
| | 02:13 | Now as we type data in here, for
example, I'll just put in the name here.
| | 02:18 | I might not even have my other data to
the right available yet, now I am going
| | 02:21 | to put in name here and so on that
becomes part of the Table automatically, we
| | 02:26 | don't have to worry about that.
| | 02:28 | So that you can start to see some
advantages here, obviously, we don't enough
| | 02:33 | data yet to do filtering, but the filter
arrows are there waiting for us to use this.
| | 02:38 | If we look at our original data that we
first saw over here, this sheet here, or
| | 02:42 | possibly data like this or possibly
ScientificData, in all the cases here we
| | 02:48 | might want to consider using this Table concept.
| | 02:51 | This is ScientificData, pretty dry
stuff, it won't even fit into our fields,
| | 02:56 | sometimes it wants us to jump out a little
bit, make it little more readable and usable.
| | 03:00 | Let's turn it into a Table
| | 03:02 | Insert Table, OK, Excel nearly always
figures out your data accurately, a quick
| | 03:08 |
| | 03:08 | look at that, you should do each time.
| | 03:11 | Make sure if your Table has headers
and it probably does, you want the box
| | 03:14 | checked, click OK, it's a lot
more readable than it was before.
| | 03:18 | We can work with the data better.
| | 03:20 | So as we've seen, you can easily
convert existing data into a Table, you can
| | 03:25 | start with just a minimum amount
of information and create a Table.
| | 03:29 | The advantages are primarily going to
be visual, but as we'll see, there are
| | 03:33 | some additional features that make
working with your data much, much more
| | 03:37 | efficient if you convert it into a Table.
| | Collapse this transcript |
| Using the Ribbon Design tab to control table formatting| 00:00 | When you convert a list into a Table,
you automatically get this color layout,
| | 00:05 | and that might be fine.
| | 00:07 | However, anytime the active cell is
within a Table, you do have a Table tools
| | 00:13 | ribbon and Design tab, and we've got
Table Styles off to the right, and how many
| | 00:18 | choices do we have, if you're
really counting, there is 61.
| | 00:22 | We can easily make changes here and as we
slide over these choices, we get a preview.
| | 00:28 | Sometimes we do this just for the sake
of variety, at other times we think of
| | 00:31 | how this will look on the printed page,
maybe we have printed some of these.
| | 00:35 | Some of these look like their design
has used up all the ink in your color
| | 00:38 | printer, but you have your reasons for
changing, and of course, it's really easy
| | 00:42 | to make changes just by
sliding over the one you want.
| | 00:45 | Try this one for a while,
that one for a while, and so on.
| | 00:48 | If you're working with data and you want
to convert it into a Table, another way
| | 00:52 | to create a Table does give you the
choice before actually seeing the Table, and
| | 00:56 | that's on the Home tab if you choose,
Format as Table, it's in the Styles group.
| | 01:02 | And here, before actually seeing the data
as a Table, we make a choice ahead of time.
| | 01:07 | There is no live preview as we do this,
so perhaps we're just guessing that
| | 01:10 | how this will look.
| | 01:11 | We'll pick one, and then looking at
the dialog box quickly to confirm that
| | 01:16 | Excel has chosen the proper amount by the data,
Table has headers click OK, we have that choice.
| | 01:22 | So just a slight difference in the
way we use this, and here too, we can
| | 01:25 | make these changes.
| | 01:27 | Now there are some other things we
might want to consider changing too, you'll
| | 01:30 | see some choices here on
the Design tab, Banded Rows;
| | 01:34 | you probably do want those, although some
people might want to have Banded Columns.
| | 01:38 | Again, it might depend upon the
nature of the data, and maybe that's a
| | 01:41 | better choice here.
| | 01:42 | I am pretty sure you wouldn't want both, if
you try both of these, not a great choice.
| | 01:47 | You also see choices here for First
Column, so what happens when I choose First
| | 01:53 | Column here, notice that Column A has a
different look than the other data. Last Column;
| | 01:58 | that's way off to the right in this
contiguous list here, it's the same idea.
| | 02:03 | Now as you experiment with different
layouts here and you might want to consider
| | 02:07 | doing that, then see the effect of that
and you might have to comeback on some
| | 02:11 | of these and try these again, and
depending upon which these Table Styles you've
| | 02:16 | chosen, the first row
might not look that different.
| | 02:19 | Sometimes all it does is make the text
bold, so you want to experiment with that
| | 02:23 | probably a little bit to see how that plays out.
| | 02:26 | Well, what happen on this Table, for
example, if we choose First Column up
| | 02:30 | there in the Design tab?
| | 02:32 | It simply makes the data bold and
that might be just what you want too.
| | 02:37 | Last Column here, because we don't have
our data quite complete out in columns
| | 02:41 | K, L and Ml that really has no obvious effect.
| | 02:45 | But you want to experiment with these
a little bit, and although I wouldn't
| | 02:48 | suggest it's at all necessary if there
is some rationale that says, you don't
| | 02:53 | either like any of these, or you want
to create your own New Table Style, you
| | 02:58 | might want to go down that path.
| | 03:00 | This probably takes a lot of work,
but you can begin to see some of the
| | 03:03 | capabilities here as well.
| | 03:05 | So the visuals do count that makes a
difference to your audience sometimes for
| | 03:10 | display reasons, where you're going to
be printing this, you've got a lot of
| | 03:13 | control over the appearance of a Table.
| | 03:16 | And if you insert a new row in the
Table, as you would hope and expect,
| | 03:20 | the every other row up look, the so called
Banded Rows look will adjust automatically.
| | 03:25 | So if we Insert a new row here, anywhere,
right here, notice how all the color
| | 03:31 | scheme adjusts
automatically, we'll put in our data.
| | 03:33 | If we change our mind on this too, we'll
just get rid of it right here, then the
| | 03:38 | color scheme automatically adjust that
way, you don't have to worry about that.
| | 03:42 | So there's some great visual features
associated with using a Table and we've
| | 03:46 | got a lot of choices out here using
Table Styles and some others on that Design
| | 03:50 | tab here, as we work with Tables.
| | Collapse this transcript |
| Expanding tables automatically and adding totals| 00:00 | When you work with a Table you may need to add
new records perhaps at the bottom of a table.
| | 00:06 | You might want to add new columns to
the right both of these will happen
| | 00:10 | automatically you might even
want to consider adding a total row.
| | 00:14 | So let's take a look and
see how these features work.
| | 00:16 | In this particular list here I might
want to add a record by inserting from
| | 00:21 | within and you can usually right-click
and Insert and add the data that way, if
| | 00:25 | that seems appropriate, that's just fine.
| | 00:27 | Excel will automatically include
formulas where they exist in certain columns,
| | 00:32 | so we could just fill in this data if
we needed to, and then fill in this data.
| | 00:37 | We don't need to worry about this formulas;
| | 00:39 | that's one approach.
| | 00:40 | I am going to undo that.
| | 00:41 | We might want to go the bottom here
and maybe I am going to start typing and
| | 00:46 | look what's happening here, Smith,
Marion, I'll tab over, you can begin to see
| | 00:53 | based on the formatting changes, this is
automatically part of the Table, and so
| | 00:58 | that means if we do filtering, this
row is automatically included in the
| | 01:02 | filtering concept, if we do sorting,
sorting is going to include all the rows
| | 01:06 | down to row 743, and so on.
| | 01:08 | And as we're adding new data, it just so
happens, once again, we've got formulas
| | 01:13 | here that are automatically copied from above.
| | 01:16 | So that's fast and easy.
| | 01:18 | Take a look at another worksheet, this
hasn't been converted into a Table, let's do so.
| | 01:23 | Once again we could do this in a
variety of ways, we'll use the Format as Table
| | 01:26 | option on the Home tab, pick one of
these choices here, and there we go, click
| | 01:31 | OK, and what happens if
we want to add a new field?
| | 01:34 | For example, here it's
going to be called Sales Type.
| | 01:37 | Column doesn't have to so wide, we'll
just type in Sales Type, and as soon as I
| | 01:43 | press Enter, you can see what's happening;
| | 01:46 | that column automatically becomes part of
the Table, because it's adjacent to the data.
| | 01:50 | So we don't have to worry about
expanding our Table, we don't have to take any
| | 01:54 | special stuffs to do that, it becomes automatic.
| | 01:57 | And based on the nature of the data,
you might or might not want to have a
| | 02:00 | Total row at the bottom.
| | 02:02 | So on the Design tab as you're
working with the table, you'll see a
| | 02:07 | choice called Total row.
| | 02:08 | Let me check this, and immediately
we're pulled down to the bottom of the list
| | 02:13 | here, you'll see the word Total.
| | 02:15 | Now we might want totals for some of
these columns, but not all of them.
| | 02:19 | If we simply want to count the
number of Sales, we could probably use any
| | 02:22 | column, if each row here represents a sale.
| | 02:25 | So maybe we'll do it right here and I will
leave that word Total out there by itself.
| | 02:29 | And then say, for example, let's just
do a count here, 909 sales, we wouldn't
| | 02:35 | need that for here or here.
| | 02:37 | We might want to put in the average
Items sold per sale, something like that,
| | 02:43 | possibly the Total instead.
| | 02:45 | We might want a total amount.
| | 02:47 | In other words in each of these
various locations, we can put in the function
| | 02:51 | that serves us best.
| | 02:54 | Adjust the column there, so
that's the Total dollar amount.
| | 02:58 | This is the average number
of items per each row here.
| | 03:02 | We could even put in the average
Date, it probably wouldn't be a good
| | 03:04 | calculation, but we've got
our choices on all of these.
| | 03:08 | But now the question comes up, what if
you're saying yourself, well, I like to
| | 03:11 | add when I add new data put it at the
bottom, or let's turn off the Total row,
| | 03:15 | and we'll add new data here.
| | 03:17 | Make this a little bit simpler, I am
just going to copy one of these and say its
| | 03:20 | another repeat of this, so I'll just do
a Ctrl+Drag here, start to fill in the
| | 03:25 | other data, you see
immediately it's part of the Table.
| | 03:28 | Let's bring back a Total row;
| | 03:30 | do we have to recreate all
those totals all over again?
| | 03:32 | No, we don't, and so if we think of the
Total row as a toggle, we can easily add
| | 03:38 | it and take it off as needed.
| | 03:39 | So it really isn't going to get into
the way of the idea that we might want to
| | 03:43 | add our new information at the bottom
of the list, just turn off the Total row,
| | 03:46 | add the records, bring
back the Total row as needed.
| | 03:49 | So that's a great feature, and once
again, we can see how the Table concept
| | 03:53 | allows us to think of our data as an
entity, and it's easy to add new columns to
| | 03:58 | the right, new rows at the bottom,
simply by typing the information.
| | 04:02 | And then there's an added benefit of
using the Total row as we need it, using
| | 04:06 | the toggle concept, it's on, or
it's off, whenever we need it.
| | Collapse this transcript |
| Working with formulas in tables| 00:00 | If you worked with formulas in tables,
there is a distinct advantage to the fact
| | 00:05 | that Excel copies formulas down the
column instantly, it's also a slight
| | 00:09 | downside as we shall see.
| | 00:11 | In this particular worksheet which has
been converted into a table, we want to
| | 00:15 | put in New Salaries in column K. And
everybody in this list is going to get an
| | 00:20 | increase of 2.91%, that's
the value that's in cell M1.
| | 00:25 | Existing salaries are in column I.
| | 00:27 | So I'm going to write a formula
for the very first person here.
| | 00:31 | Equal and I want to click on the cell I2,
it's got the salary for the first person.
| | 00:37 | And when I do, what do we see?
| | 00:39 | An unusual kind of construction
here refers to this as a salary.
| | 00:43 | And what do we want to do with this salary?
| | 00:45 | We want to multiply it, there's an
asterisk, times this cell M1, and as a click
| | 00:50 | on M1, look what pops up on the screen.
| | 00:53 | Do we've to worry about this being an
absolute address, we're going to copy it
| | 00:57 | down the column, no, we don't
have to worry about that either.
| | 01:00 | What does this formula do as of now?
| | 01:03 | It's going to be calculating
the salary times the percent.
| | 01:06 | So if we were to press Enter, we would
only have the amount of the increase.
| | 01:10 | So let's add on to this the existing
salary plus, and once again I'll click on I2.
| | 01:17 | So that's a pretty long formula, and
it may not be as clear as you want.
| | 01:20 | But look what happens when I press Enter.
| | 01:23 | It's entered into every single cell here,
and every one of these, for example,
| | 01:27 | this one or anyone, if you double-
click it, you'll see what it's doing, it's
| | 01:31 | consistently referring to
the percent in cell M1; 2.91%.
| | 01:35 | We actually see it here as well too.
| | 01:38 | If you go to the very bottom,
we see how far this got copied.
| | 01:42 | In this list of some 741 names, it
got copied all the way to the bottom.
| | 01:48 | Now you might find those formulas on
wieldy, they certainly don't jump out and
| | 01:52 | say exactly what's going on.
| | 01:54 | I'm going to press Ctrl+Z, press Ctrl+
Z again, and rewrite the formula using
| | 02:00 | actual cell addresses.
| | 02:01 | And although I'm not a fan of typing
I think many people might prefer this
| | 02:06 | approach to using formulas in tables,
because it does retain the copying aspect
| | 02:11 | of it that's so powerful.
| | 02:13 | Equal and this time I'm going to
type I2, that's the first-person in the
| | 02:18 | list that has a salary.
| | 02:20 | I2 times and the salary increase
percent is in cell M1, let's type that.
| | 02:27 | We do need to make this absolute by pressing F4;
| | 02:31 | That'll take care of that.
| | 02:32 | And then let's add on this the existing
salary I2, and as I press Enter, again,
| | 02:40 | the powerful part of this is the
formula gets copied all the way down the
| | 02:44 | column, so everyone of these
entries here is accurate and sure enough.
| | 02:49 | Press Ctrl+Down Arrow, we
go all the way to the bottom.
| | 02:52 | So writing formulas in tables can be
very efficient and you may or may not want
| | 02:58 | to go with the standard technique of
clicking on the cells, or you might do as I
| | 03:02 | did in the example here,
type in the actual addresses.
| | 03:05 | And I think for many people this
latter approach is better, because it does
| | 03:08 | retain that familiar look of formulas.
| | Collapse this transcript |
|
|
3. Controlling the Creation of New DataBuilding and viewing data with the Form tool| 00:00 | When you work with large lists or if
you're starting to create a list or a
| | 00:05 | database on Excel, you might
want to use the Form feature.
| | 00:09 | Strangely if you click the Data tab
in Excel, you will not see the feature
| | 00:14 | called Form, and it's been in
Excel for a long, long time.
| | 00:18 | And maybe Microsoft is
sending us some message, maybe not.
| | 00:21 | But if you want to use what we call a
Form and its major advantage is you can
| | 00:27 | see all of the information
about one record all at once.
| | 00:31 | In this particular worksheet here, we
need to scroll back and fourth, left
| | 00:35 | and right to see this data, and maybe
it's really important to focus on the
| | 00:39 | data right now in row 4.
| | 00:41 | And in, in other worksheets, for
example, like this, maybe this is not so much
| | 00:45 | of an issue, because we can see
everything on the screen at once.
| | 00:48 | It depends upon the nature of the data.
| | 00:51 | If you want to use the Form feature,
you don't have to do anything special.
| | 00:54 | In fact, what you might have in,
in fact, is just a few records.
| | 00:58 | As long as you have a title row and at
least one record, potentially you can
| | 01:03 | use the Form feature.
| | 01:04 | But where is it and how do we get to it?
| | 01:07 | If you right-click anywhere on the
Quick Access toolbar, you'll see a choice
| | 01:12 | Customize Quick Access toolbar.
| | 01:16 | Once here, we want to choose by
clicking the drop arrow, and choose either
| | 01:21 | Commands not in the Ribbon, to get a
list of about 300 entries or All Commands,
| | 01:25 | doesn't make any
difference, either of these two.
| | 01:28 | And what we're looking for in here is
the word Form, and there used to be a
| | 01:33 | command called Data Form in
Excel 2003, and in prior versions.
| | 01:37 | Here's the icon for it, click it and then Add.
| | 01:41 | So we are adding the Form button to
the Quick Axis toolbar, just click OK.
| | 01:48 | So maybe starting with a worksheet
that's got a small amount of data or maybe
| | 01:53 | larger, doesn't make any difference.
| | 01:54 | Let's go to this Form button and
click it and here's what we see.
| | 01:58 | Now we probably wouldn't need this
particular feature right now, because we
| | 02:03 | don't have a lot of data, although
it's certainly not wrong to use it.
| | 02:06 | And this is a nice way to see the
information about just this one record at a time.
| | 02:11 | But let's show how this
might look with more data.
| | 02:15 | Here's the Employees list, click
anywhere in here, click the Form button again,
| | 02:21 | and we see how it's looking here.
| | 02:23 | Something a little different here
you'll notice, is that it looks like two of
| | 02:26 | the fields here, you can't make any changes too.
| | 02:29 | Now we didn't say you could make
changes, but we certainly can on the others.
| | 02:34 | This maybe isn't the
fastest way to make a change here.
| | 02:37 | Maybe it turns out that Irv Fleming
here really isn't in the West Building, you
| | 02:41 | know, you might as well change this out
in the worksheet, but we could change it
| | 02:44 | here if we want to, he is
really in the Taft Building.
| | 02:46 | So we can make the change there.
| | 02:48 | And you can see here, we can
scroll it around to other records here.
| | 02:52 | And as I scroll down into record 2 the
Irving Fleming Building change actually
| | 02:57 | took place out there in cell B2.
| | 03:00 | But I'm not really making a strong case
for saying this is a data entry vehicle.
| | 03:04 | However, if we were to do add a new
record, it would automatically calculate the
| | 03:09 | month, and although it would be a
little bit strange to calculate years for a
| | 03:13 | New Employee, that would happen as well to.
| | 03:15 | So suppose we add a new record here.
| | 03:17 | I'm just going to choose New, and I
won't do much typing here, but I'll just put
| | 03:21 | in somebody's name, Smith, Sam, and I
won't fill any other details just yet, and
| | 03:28 | I'll put in the Hire Date of 10/7/11,
that's all I need for now and scroll down
| | 03:35 | so maybe we will close this.
| | 03:37 | So what will have happened at the bottom?
| | 03:39 | I'll double-click the bottom edge here,
there is Sam Smith, hired on this date,
| | 03:45 | and this column here, which has the
anniversary month, has all ready been
| | 03:48 | calculated automatically.
| | 03:49 | So us this one too, but for the moment
it's irrelevant, this actually calculates
| | 03:53 | the number of years the person has been here.
| | 03:55 | And it doesn't work so well on this one,
because I actually put in -- I'm doing
| | 03:59 | here a future date, so you can ignore that.
| | 04:01 | Let's see what happens here
with large amounts of data.
| | 04:05 | This is the worksheet that's
got quite a few columns in it.
| | 04:08 | Let's choose the Form button here.
| | 04:12 | Now this actually is the maximum
number of columns we can use with this
| | 04:16 | particular feature, and it's 32.
| | 04:19 | And in this particular worksheet, it
looks as if there are one, two, three
| | 04:23 | fields for there formulas in place.
| | 04:26 | And so here too, if we were to add a
new record, automatically those formulas
| | 04:30 | would be copied downward.
| | 04:32 | The Form is really useful here, so we can
focus on one item at a time, one row, row one.
| | 04:39 | We can click the drop arrow at the bottom,
here's record two, record three, and so on.
| | 04:43 | So that's the advantage, and look what
happens here, if I decide to add more data.
| | 04:49 | I've got some data setup here off to the
right, so I'm going to delete column AG
| | 04:54 | and pull in this information, because
I needed to go along with this data.
| | 04:57 | So I'll simply Delete this.
| | 05:00 | We now have 34 columns in this list,
look what happens when I try and use Form.
| | 05:05 | It just doesn't work and you'd
think that maybe that it have a better
| | 05:08 | error message than this.
| | 05:09 | It doesn't say how many or too many,
well, if it's over 32, it just won't work.
| | 05:15 | So we can't put that data in here.
| | 05:17 | We might want to consider getting rid of
some other fields, so perhaps I'll come
| | 05:20 | back and insert, and again, if we want
to use this, fine, we can do this now.
| | 05:25 | I think you can see how this feature
works that has a distinct advantage in
| | 05:30 | allowing you to see all the
information about one record at a time.
| | 05:33 | It has some advantages
for putting in new records.
| | 05:36 | One other aspect of this which isn't a
strong case to be made for it, but it still works;
| | 05:41 | I think the filter will do this better.
| | 05:43 | But if for whatever reason, we
wanted to see all of the records that met
| | 05:48 | a certain criteria.
| | 05:49 | For example, we want to look at all of those
who have a description here of job control.
| | 05:55 | So we could go to the Criteria tab here
and type in under Description here Job
| | 06:00 | Control, so not all of the records
have that particular entry there.
| | 06:06 | If we now choose Find Next, this take
us to record two, next one, three, four,
| | 06:12 | or may be there is a whole cluster of
them there, but we're not necessarily
| | 06:15 | going to stop at every single one of these.
| | 06:18 | Looks like there are a lot of them there,
but still, for the moment we're only
| | 06:21 | looking at those that have Job
Control in the Description panel.
| | 06:25 | And we could have used
multiple criteria as well there.
| | 06:28 | Let's jump over to Employees here, and
maybe using Form here we just want to
| | 06:35 | view the hourly people.
| | 06:36 | So we'll go to Criteria, and here we'll
put in under Status, put in Hourly, and
| | 06:44 | along with that maybe choose Job Rating 5.
| | 06:47 | In other words, we want to see on a
record by record basis, just those people
| | 06:52 | who are Hourly and have a Job Rating of 5.
| | 06:55 | So we'll Find Next and there's the
first record, record number 77, there is
| | 07:00 | Michael Mosley, and there's
the next one, 87, and so on.
| | 07:04 | So some advantages there perhaps,
although I think a filter would work better.
| | 07:08 | So the Form capability, a bit buried
in Excel 2007 and 2010, nevertheless has
| | 07:14 | some advantages for not only building
data, but also viewing data, a record at
| | 07:19 | a time.
| | Collapse this transcript |
| Restricting data entries with data validation| 00:00 | When you're creating a list or database,
a great tool to accompany that is the
| | 00:05 | idea that you want your data to be accurate.
| | 00:08 | In other words you would like to validate it.
| | 00:10 | Keep out the bad data before it ever
gets into your database and that's almost
| | 00:15 | rule number one in creating data.
| | 00:19 | There are some validation tools.
| | 00:20 | I am going to switch to a worksheet
here called Validation and its showpiece
| | 00:25 | feature is what we call a pick list.
| | 00:27 | So the data off to the right may not
be so obvious at first as to why it's
| | 00:31 | there, but let's imagine a scenario
here where we are building a small list
| | 00:36 | here, potentially large of Employee
Names, Building, Department, Status and
| | 00:40 | Salary and to make this process a
lot faster, it sure would be great if
| | 00:45 | nobody had to type anything for
Department or Building or Status, just put in
| | 00:50 | the Employee Name and the Salary and pretty
much have these fall into place automatically.
| | 00:55 | And even with Salary, we might want to
have some controls here, maybe on the
| | 00:59 | list that we are thinking are we
working with an organization and your top
| | 01:02 | salary is 120,000, and for a while
nobody is going to go over that, and they
| | 01:06 | want to make sure that all the entries
fall below that value, and maybe at the
| | 01:10 | same time they've got a
minimum of 30,000 or whatever.
| | 01:13 | We want to put controls here to make
sure that no entry in this column falls
| | 01:18 | outside that boundary.
| | 01:20 | So let's click Column E. Many, many
times when you are setting up data
| | 01:24 | validation rules, if you can select
the entire column, it just smoothes the
| | 01:28 | process, makes it easier, rather
than highlighting specific cells.
| | 01:32 | On the Data tab in the ribbon, you
will see a choice called Data Validation.
| | 01:38 | We can allow anything here, that's
the current setting, allow Any Value.
| | 01:43 | For yearly salary I would imagine a
Whole number probably be a sensible choice.
| | 01:48 | If it does contain pennies, well,
then use Decimal, use one of these two.
| | 01:52 | And although, we won't be showing you
other examples here, think of Date and
| | 01:55 | Time and how you might have
restrictions on those, or maybe sales within an
| | 02:00 | organization only occur between 6
AM and 7 PM, something like that.
| | 02:04 | Think of certain kinds of ID numbers
where they have a limited text length,
| | 02:10 | there are other kinds of
restrictions you might want to consider using.
| | 02:13 | So on this example, let's say we use
Whole number, and immediately we see
| | 02:17 | this prompt, if we have only an upper
limit or only a lower limit we could
| | 02:21 | use some of these choices.
| | 02:23 | Let's say we use between and the
Minimum in this example here 30,000 and
| | 02:29 | our Maximum is 120,000.
| | 02:32 | If we wish we can even provide a
message in a box that will appear in the
| | 02:37 | cell when we click in any of these cells in
Column E. So let's provide an Input Message.
| | 02:42 | Don't necessarily need a Title, but you
could use a word like Reminder perhaps,
| | 02:49 | putting in a colon, makes it look a
little tidier and just a simple message
| | 02:53 | saying, Must be between 30,000
and 120,000 and click OK.
| | 03:08 | You can also provide an Error Alert too.
| | 03:10 | I will show you that after this.
| | 03:13 | So as we click here that should be
enough to keep out not a typo necessarily,
| | 03:17 | but it's a clear reminder of
what's supposed to go in here.
| | 03:20 | So, of course, we will put in a sensible amount.
| | 03:25 | But just to see what happens when we
violate the rule, there is the pop-up.
| | 03:32 | Now you even can control the wording
here if you wish, that's under the other
| | 03:35 | tab that we saw there.
| | 03:37 | For the moment here, let's say, that okay, we
made a typo, Retry, we will just change that.
| | 03:41 | It should have been 33 and we are all set.
| | 03:43 | So, pretty basic idea and
concept there to keep out the bad data.
| | 03:49 | Now for Status, for Department, for
Building, notice that on this worksheet off
| | 03:53 | to the right are various
lists of what can go in there.
| | 03:58 | Now the idea behind a list is you
don't want anybody typing any of these and
| | 04:03 | you want these to be the only possible
selections that can be made, and when
| | 04:10 | you set up lists, at least initially,
it's a good idea to have them on the same
| | 04:13 | worksheet so you can see them and review
then and make sure everything is working okay.
| | 04:18 | A pop up list that we will see in
these can only show eight at once, so on a
| | 04:22 | Department list, we are going to have
to consider the order of these, and the
| | 04:26 | fact that there will be a scrollbar.
| | 04:28 | Let's just do one of the smaller ones,
for example, Building for Column B.
| | 04:33 | In Column B, we don't want anybody typing
anything, we want them to pick from a list.
| | 04:38 | Data Validation>Settings>Allow>List and
let's just move this dialog box over, so
| | 04:47 | we can see the list.
| | 04:48 | Source, click here, refer
to these cells, click OK.
| | 04:57 | So, and even though that's
on B1, we'll ignore that.
| | 05:00 | Click here, there is our list,
click there, there is the next one.
| | 05:05 | We are not doing any typing of course.
| | 05:08 | If your hands are on the keyboard and
you want to get to the list, you can press
| | 05:10 | Alt+Down Arrow, then use the arrow keys,
come to the one you want, press Enter.
| | 05:15 | Similarly, we did the same thing with Status.
| | 05:19 | Now suppose we open a new building
that's in this campus like setting here.
| | 05:22 | So I'm going to right-click on any
one of these, except for the top one,
| | 05:27 | right-click and Insert and
simply shift the cells downward.
| | 05:32 | So we've got a new Building.
| | 05:34 | It's called the Kenton
Building. Put that in there.
| | 05:38 | We don't have to do
anything else with our definition.
| | 05:40 | Next time we click the drop arrow,
that's in the list, so we can easily do that.
| | 05:46 | For Department, we are definitely going
to have to make the column wider and you
| | 05:49 | see the list over there in Column K.
And not only are we going to save a ton of
| | 05:56 | time here, how long it takes to type
some of these, a lot of people will
| | 06:00 | misspell certain words.
| | 06:02 | People will use abbreviations
differently, if got different people doing this.
| | 06:06 | Even you yourself might type these
in differently at different times,
| | 06:10 | abbreviations here and there, no
periods, periods, that sort of thing.
| | 06:14 | If you want a standardized list,
here's where you do the standardization, and
| | 06:18 | then nobody enters this differently.
| | 06:20 | So that really helps too, not only
the speed, but the accuracy here.
| | 06:25 | One other aspect of this, this
list sensibly is alphabetized.
| | 06:30 | That makes good sense, because when we
see the entries pop-up in Column C, we
| | 06:34 | want to be able to find these quickly.
| | 06:36 | But what if you know ahead of time that
a large percent or a significant percent
| | 06:42 | of these is likely to be
used more often than others?
| | 06:46 | Maybe it's Manufacturing.
| | 06:48 | Let's put this at top of the list.
| | 06:50 | Now you can drag this entry with the
Shift key that will move it and insert.
| | 06:55 | I am going to put at the top of the
list, in other words I'm violating the
| | 06:57 | alphabetical concept, but because this
is used frequently, I don't want to have
| | 07:02 | to scroll to get to it.
| | 07:04 | So, in Column C, just as we did before,
let's apply Data Validation rule here.
| | 07:10 | Same idea, it's going to
be a list. There it is.
| | 07:13 | And where is that list? It's right here.
| | 07:17 | Highlight the data, click OK.
| | 07:20 | And as we use it, the list here, there
is Manufacturing at the top of the list.
| | 07:25 | We don't have to go scrolling for it,
let's say, because we know we use it often.
| | 07:29 | If we have to get to other choices though,
they're alphabetized, so we should be
| | 07:33 | able to find them relatively quickly.
| | 07:35 | We did the same thing with Status.
| | 07:37 | Now, it would make sense
eventually to put this list somewhere else.
| | 07:44 | So what if were to move this list?
| | 07:46 | Why don't we take this data
and put it on another worksheet?
| | 07:49 | Ctrl+X possibly, that's one way to do it.
| | 07:53 | Put it over here on the
Sheet3, right-click, Paste.
| | 07:58 | There we go, it's over there.
| | 08:01 | So what happens now?
| | 08:06 | Click here, drop arrow, there is the list.
| | 08:09 | If you're working in older versions
of Excel, you actually have to give
| | 08:13 | that list a range name.
| | 08:15 | You don't have to do that anymore,
and so this makes it simpler and easier.
| | 08:18 | Now there are lots of other examples
with data validation, and I would like to
| | 08:22 | refer you to another course from lynda.com on
the Data Validation, both for 2007 and 2010.
| | 08:29 | But there is no question;
| | 08:30 | this is an appealing feature,
particularly with regard to the pick list.
| | 08:34 | You want to make sure that the data
that goes into your worksheets is accurate,
| | 08:38 | and data validation gives you a
lot of tools for making that work.
| | Collapse this transcript |
| Using the data validation tools to check the validity of existing data| 00:00 | The data validation capability in
Excel is designed to work for new data, but
| | 00:06 | sometimes you want to apply
rules to an existing worksheet.
| | 00:10 | There's a lot of data there already,
but in effect you're saying, from now on I
| | 00:15 | have this restriction.
| | 00:17 | Let's imagine that in Column I here,
I'm going to put in a restriction that
| | 00:20 | says, any salary that we put
in here must be over 20,000.
| | 00:25 | And you can see already that there are a few
entries there that don't meet that criterion.
| | 00:29 | We're not going to change what's
there, data validation can't do that.
| | 00:34 | But data validation can show
us where there are exceptions.
| | 00:37 | And surely, an alternative way of doing
that here would be simply to sort the data.
| | 00:41 | Let's say we don't want to sort
the data, we can't do that right now.
| | 00:46 | Let's apply a data validation rule here
in Column I that says in effect from now
| | 00:52 | on, these must be Whole
numbers that are greater than 20000.
| | 01:01 | Okay, so that's the new rule.
| | 01:04 | From now on, if I change this salary
here, maybe I'll say that's going to be
| | 01:09 | 19,000, doesn't work.
| | 01:13 | I can't sort the data right now for a
couple of different reasons, and I want to
| | 01:17 | know which records have violated my new rule.
| | 01:21 | What can we do here?
| | 01:22 | Go to Data Validation>Circle Invalid Data.
| | 01:27 |
| | 01:32 | There are the records that violate our rule.
| | 01:33 | That's a good visual, however, there's not
much you can do with it, other than to see it.
| | 01:38 | I think it does point out the idea,
that yes, those are the ones we need to
| | 01:42 | change or at least consider, but you
can find records that violate rules.
| | 01:48 | And possibly you could have some
scenarios where you really don't want to use
| | 01:51 | the validation tool for controlling anything;
| | 01:53 | you just want certain data to stand out.
| | 01:56 | So apply a rule and then find out which
cells violate the rule by using this feature.
| | 02:01 | And if we no longer need this here,
we simply go, if it's too late to do
| | 02:05 | an Undo, go back to Data Validation,
click the drop arrow and choose Clear
| | 02:10 | Validation Circles. There we go!
| | 02:12 | A great tool for locating cells
that violate data validation rules.
| | 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's Cleaning
Up Your Excel Data and also Managing and
| | 00:11 | Analyzing Data in Excel.
| | 00:13 | For a review of basic Excel functions,
take a look at Excel 2010 Essentials.
| | 00:18 | And if you want to become a more
proficient user of this powerful software,
| | 00:22 | check out the Excel 2010 Power Shortcuts course.
| | 00:26 | And check back from time to time for
additional courses added to this series.
| | 00:31 | Thank you for watching.
| | Collapse this transcript |
|
|
