IntroductionWelcome| 00:03 | Hi! I am Dennis Taylor, and I'm pleased to
present Cleaning Up Your Excel Data.
| | 00:08 | This course designed to confront
the myriad of problems associated with
| | 00:12 | streamlining your data by
providing quick, easy-to-use solutions.
| | 00:16 | First we'll look at how to re-adjust
column row placement with simple dragging
| | 00:20 | techniques for easy viewing all of your data.
| | 00:22 | Then we'll examine how to transpose
and adjust data using tools like Find and
| | 00:27 | Replace, and Text to Columns, and we'll
also look at how to take full advantage
| | 00:33 | of powerful text functions to deal with
unwanted spacing and formatting issues,
| | 00:37 | as well as text-to-value
or value-to-text Conversions.
| | 00:42 | So let's get started with
Cleaning Up Your Excel Data.
| | 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 a
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 sheet tabs.
| | 00:20 | Many of these will be
referred to in the training.
| | 00:22 | Here and there you will see a
tab that is not used at all.
| | 00:26 | If you are Monthly subscriber or
Annual subscriber to lynda.com, you don't
| | 00:30 | have access to the exercise files, but you can
follow along from scratch with your own files.
| | 00:36 | Let's get started.
| | Collapse this transcript |
|
|
1. Readjusting Data LayoutsMoving and inserting rows and columns of data with a simple drag| 00:00 | When you need to change the order of
columns on a left-to-right basis, or possibly
| | 00:04 | rows--moving them up or down--there
are certainly standard techniques--no
| | 00:07 | shortage of those in Excel--but there
are some not-so-well-known shortcuts that
| | 00:12 | are going to make these tasks a lot simpler.
| | 00:14 | In this worksheet called MoveData it was
sent to us and we need to make some changes.
| | 00:20 | We like the content, but we
don't like the order of the columns.
| | 00:24 | But rather than inserting new
columns and then moving data, we can simply
| | 00:28 | achieve this with a simple drag--
and there are two ways to do this.
| | 00:32 | For example, suppose that we want
Column E to be placed between Columns A and B.
| | 00:37 | And we might have thousands of rows here;
| | 00:40 | that's not important right now.
| | 00:42 | We simply have selected the entire
column. And I am going to be dragging either
| | 00:46 | the left edge or the right edge.
| | 00:48 | Notice as I position the mouse on
either side--see the four way arrow, hold
| | 00:52 | down that left mouse button--and as I
drag leftwards here, I want to be holding
| | 00:57 | down the Shift key.
| | 00:58 | Now the icon changes as soon
as I hold down the Shift key.
| | 01:01 | So I am still dragging, holding down
the Shift key, dragging leftward, putting
| | 01:05 | it here between Column A and B.
Let go of the mouse button first, and we've
| | 01:11 | moved that column to the left.
| | 01:13 | We can go in either direction.
| | 01:14 | We can do this with multiple columns.
| | 01:16 | Maybe we need to move these two fields
here, Social Security Number and Phone
| | 01:21 | number, put them between Columns B and C.
Maybe we'll drag the right edge this time;
| | 01:25 | it doesn't make any difference. Left or
right edge, as we drag, we are holding
| | 01:29 | down the Shift key, drag this leftward,
put it between Columns B and C, let go
| | 01:34 | of the mouse button first,
and we've moved the columns.
| | 01:37 | Left or right, doesn't make any difference.
| | 01:39 | If we change our mind again, we want
Social Security to be to the right of Phone,
| | 01:44 | we are going to drag holding down the
Shift key, to the right. Let go the mouse.
| | 01:48 | We can also do this with the right
mouse button, and here you don't have to hold
| | 01:52 | down any special keys.
| | 01:54 | Make another change.
| | 01:55 | I've decided to move Status
between Building and Phone.
| | 01:58 | So click Column E. And wherever possible,
you do want to click the column letter.
| | 02:03 | I don't mean to suggest that
you can't do this with just cells;
| | 02:06 | you certainly can. But if the column is in
play, the entire column is simpler this way.
| | 02:12 | This time I'll use the right mouse button and
drag either edge; it doesn't make any difference.
| | 02:17 | With the right-mouse button, I want to
drag this on top of Column C with the
| | 02:23 | right mouse button, and as I let go,
here is the menu. Shift Right and Move.
| | 02:29 | So the Status column, as I click this,
is going to be between Phone, or to the
| | 02:35 | left of Phone, and Building. There we are.
| | 02:37 | So we can do this with what we call a
right-drag--there, too, you can use multiple
| | 02:41 | columns--or a standard drag. Use the
left mouse button with the Shift key held
| | 02:46 | down to simply control the position of columns.
| | 02:50 | Now it's less likely to need to do this
with rows, but in a different kind of a
| | 02:53 | worksheet, which really isn't a database.
| | 02:56 | A quick look at this might suggest that,
for example, the Expenses Change here,
| | 03:01 | if we want these three rows to be more
or less in sync with the data we see up
| | 03:05 | above, the Expense Changes category here
for Row 10 data really belongs between
| | 03:11 | 8 and 9, in other words
between Sales and Profits.
| | 03:14 | So here, using cells and not an entire
row--although we could use this entire
| | 03:19 | row--we are going to drag upward with
the Shift key this way, let go the mouse.
| | 03:24 | So we can easily move rows and probably
much more likely, move columns, as we saw
| | 03:28 | in the example here, simply by dragging,
in one of two ways, left mouse button
| | 03:33 | with the Shift key, or simply use the
right mouse button and then respond to the
| | 03:37 | corresponding menu that pops up.
| | Collapse this transcript |
| Using one-dimensional and two-dimensional transpositions| 00:00 | The column-row layout of some ranges of data
isn't always the way you want to see the data.
| | 00:05 | Sometimes you like to see it a little
differently, and you want to be able to
| | 00:08 | manipulate the data without having to ask the
originator of it to retype it or recreate it.
| | 00:13 | You can transpose row-column layouts
into column-row layouts, and it's pretty easy.
| | 00:19 | And sometimes maybe you're just experimenting.
You don't know if it's going to look better.
| | 00:22 | Maybe this list of names here makes sense
| | 00:26 | considering we have only six names,
but we are going to add some more names to
| | 00:29 | the list. And the more you think about
it, you realize, as we add more names,
| | 00:33 | adjust those columns, and so on, at some
point we might have to scroll back and
| | 00:37 | forth to see all this information.
| | 00:40 | So what if we took this information
and somehow could imagine say, Employee
| | 00:44 | Name over here somewhere, and then right
below it, Gary Trevino, and Nate Ramsey, and so on.
| | 00:50 | In other words, take this data and
somehow rotate it so that the data across row
| | 00:56 | 1 is in a column, and the
data down here is in a row.
| | 01:01 | We can do this with Transpose.
| | 01:03 | Highlight the data in question. Copy it.
| | 01:05 | It will be right-click > Copy, Ctrl+C, whatever.
| | 01:09 | Go to a different location and
envision how this will look when it's rotated,
| | 01:13 | so you don't want to be overlapping any data.
| | 01:16 | So, for example, here I'll just click in
column J, right-click, and it's called Transpose.
| | 01:22 | You can get there by way of Paste
Special, do it that way, or here's an icon
| | 01:27 | right here. And if you're using
Excel 2007, you'll probably want to do a
| | 01:30 | right-click and Paste Special.
| | 01:32 | Then choose Transpose, and we
can see it off there to the right.
| | 01:37 | Now I might want to zoom back a little bit,
so we can see both of these together.
| | 01:40 | I think you can see pretty quickly
what's happened, a quick adjustment of the
| | 01:43 | column widths here. And maybe this layout
will be better if we plan on adding more names.
| | 01:49 | There is not a question of right or wrong;
| | 01:51 | it's a question of preferences.
| | 01:53 | This is more of a database-type
list that's probably more typical.
| | 01:56 | Now, if the data comes to you like this
and you want to make it look the way we
| | 02:01 | see the data that we originally had
transposed, if we transpose this, it will
| | 02:06 | simply look like the data we see over here.
| | 02:09 | So at different times, it's
not a bad idea to do this.
| | 02:12 | Sometimes you're just
experimenting to see what it looks like.
| | 02:15 | It certainly can be done with
data in a single column or row.
| | 02:20 | If you want this data across the top
of the screen, or across a row somewhere,
| | 02:25 | same idea. This time it's only a
single column, it makes no difference.
| | 02:29 | Right-click > Copy.
| | 02:30 | Right-click somewhere else.
| | 02:32 | This time maybe we'll do Paste Special
just to remind you that it's out there
| | 02:35 | too. Transpose. Click OK.
| | 02:38 | So, good, fast, easy ways to
switch data from a row-column layout into
| | 02:43 | a column-row layout.
| | Collapse this transcript |
| Using Sort to eliminate empty rows and columns| 00:00 | Sometimes in order to clean up your
data, you may need to add temporary columns
| | 00:05 | and use various sorting techniques to
give you the tools for eliminating empty
| | 00:10 | rows or unnecessary rows in a list.
| | 00:12 | In this particular worksheet called
ExtraRows, recognize that when there is a
| | 00:16 | department change here, a row has been
added to indicate the department name
| | 00:21 | over here in Column A. And these are
going to get in the way when we do sorting
| | 00:25 | and filtering and trying to do
pivot tables on a worksheet like this.
| | 00:29 | And similarly, the next sheet over
called EmptyRows has a similar layout, except
| | 00:35 | here there are empty rows. And perhaps
that's going to look nice on a sheet of
| | 00:38 | paper when it's printed--it
nicely delineates the departments--
| | 00:42 | but in order to use Excel efficiently,
when it comes to database tools like
| | 00:47 | sorting and filtering, we need
to get rid of rows like this.
| | 00:51 | So let's tackle both cases.
| | 00:53 | Back to the sheet that has the extra rows.
| | 00:56 | If we could somehow sort the data
to pull these all together, then we can
| | 00:59 | easily delete them without having to
painstakingly go through and delete these one by one.
| | 01:05 | So let's insert a new column to the
left of Column A. Right-click Column A and
| | 01:10 | insert. And starting on A2,
| | 01:12 | let's put in the number 1,
and then right below it, the number 2.
| | 01:16 | The data in Column B is
contiguous all the way to the bottom.
| | 01:19 | So we can simply select Cells A2 and
A3, double-click the lower right-hand
| | 01:25 | corner, and if we now press Ctrl+Period--
this will take us down to the bottom--
| | 01:30 | we can see that we've copied in a
sequence number for every one of these rows.
| | 01:35 | Let's go back to the top.
Give it a temporary heading.
| | 01:37 | It doesn't really have to be anything
really, but just to make sure that we've
| | 01:41 | got some kind of a heading up
there, we'll call it sequence.
| | 01:43 | Remember, we are going to be
getting rid of that column.
| | 01:46 | Let's sort the data now in such a way
that these kinds of rows, like this one
| | 01:50 | and this one, end up at the top or the bottom.
| | 01:53 | So all we really need to do is to
click, for example, in one of our columns
| | 01:57 | that's got an empty cell in it, like this.
| | 01:59 | Now we wouldn't do this in Column I,
because there, there are some empty cells
| | 02:04 | that have a reason for being empty.
| | 02:06 | Some people don't have benefits.
| | 02:08 | But in Column C, let's say
we've got our data all filled in.
| | 02:12 | No empty cells here, except in
these kinds of rows, like these two.
| | 02:16 | So we can just click somewhere in
Column C and then on the Data tab use the AZ
| | 02:23 | button to sort our data based on
the data in Column C. There we are.
| | 02:28 | And at the bottom of this list, we
hope we'll have all of those Column C
| | 02:33 | entries that were empty.
| | 02:34 | This should be at the
bottom. And we don't need them.
| | 02:37 | We want to get rid of them.
| | 02:38 | So let's select the rows over here and
simply right-click and delete those rows.
| | 02:46 | And now we want to go back
to the original sequence here.
| | 02:49 | It looks like it might be there anyway,
but just in case, we do have our data still
| | 02:53 | available in Column A, we can click AZ
just to make sure, jump to the top here.
| | 02:59 | It looks like things are looking pretty good.
| | 03:01 | We don't need that sequence column anymore.
| | 03:03 | Simply right-click and Delete. And similarly,
in the EmptyRows worksheet, same general idea.
| | 03:10 | Slightly different here because as we
create our formula, we are going to
| | 03:14 | have special needs for
copying it down the column.
| | 03:16 | So we need to insert a new column here,
and as in the previous example, put in
| | 03:21 | a 1 and 2, but we really can't double-
click here because it will just stop
| | 03:25 | right there at Row 6.
| | 03:26 | So what we'll need to do here, starting
here or from the top, just drag this to
| | 03:30 | the bottom--and hopefully you don't
have thousands and thousands of rows.
| | 03:34 | So that's taking a little bit of time
to do this, a little bit longer perhaps.
| | 03:38 | But other than this, the same general
idea that we saw in the previous example.
| | 03:43 | We've got our data there.
| | 03:44 | Pressing Ctrl+Period to go
back up top there. There we are.
| | 03:48 | Let's put a temporary name up there
again. And simply sort on one of our
| | 03:53 | columns, any column, Column B, for example.
Let's try ZA, just to show what would happened.
| | 03:58 | It still puts the empty rows at the
bottom, and there they are down there. And
| | 04:05 | so, we don't really need
any of these data down here.
| | 04:08 | We don't really have to delete the
entire row; just delete the sequence numbers.
| | 04:11 | Even there, possibly you don't even
need to do that. But as we click here in
| | 04:15 | Column A and do another
AZ Sort, press Ctrl+Home.
| | 04:19 | Our data is together.
| | 04:20 | We don't have any empty rows.
| | 04:22 | Let's get rid of Column A,
and we've cleaned up our data.
| | 04:26 | So sorting techniques do help us at
different times for getting rid of those
| | 04:30 | rows that have unusual
characteristics that we don't need. And whether it's
| | 04:34 | empty rows, as we saw in this example,
or in the previous example on the other
| | 04:38 | worksheet where we had data in those
rows, sorting allows us to pull those rows
| | 04:43 | together so we can easily eliminate them.
| | Collapse this transcript |
|
|
2. Replacing Data at the Character LevelHarnessing the Find and Replace commands| 00:00 | You can control both the correction of
data and the replacement of it with the
| | 00:05 | efficient use of the Find and Replace
commands found on the Home tab in the
| | 00:08 | Ribbon, and there is also a Replace
function that we want to take a look at.
| | 00:13 | Let's imagine in this list here, in
a problem list worksheet, some of the
| | 00:17 | statuses need to be changed.
| | 00:18 | This company has decided, for example,
to change the term Hourly to Temporary.
| | 00:24 | So we don't necessarily need to sort the data;
| | 00:26 | we simply want to make a wholesale replacement.
| | 00:29 | Let's just select Column E--
that's where that data resides.
| | 00:33 | And on the Home tab, the extreme right
button with the Binoculars icon, Find & Select,
| | 00:38 | let's simply choose Replace.
| | 00:41 | So, what do we want to Replace?
| | 00:43 | Every time we see the word Hourly,
and we want to replace Hourly with Temporary.
| | 00:53 | And we want to replace
all of them, just like that.
| | 00:56 | 54 of them have been changed.
Fast and easy--could hardly be faster.
| | 01:00 | That's going to work for us.
| | 01:02 | You might similarly have a
need for code changes as well.
| | 01:06 | Now, this is a code.
| | 01:07 | We don't know what it stands for necessarily.
Do the 8s need to be changed to something?
| | 01:11 | Maybe the 8s have particular meaning in here.
| | 01:14 | Now that sounds maybe a little obscure,
but if we wanted to replace all the 8s
| | 01:18 | with a different number or different
text value, we could similarly use that
| | 01:22 | same command, Find & Select.
| | 01:25 | How about a replace here?
| | 01:28 | Find what? The number 8.
| | 01:30 | We're going to replace that with maybe a
letter, maybe a value. How about the letter Q?
| | 01:37 | Now there are options available under Find
and Replace. And Match entire cell contents,
| | 01:43 | well no entire cell contains just 8.
| | 01:46 | We don't want to check that.
| | 01:48 | We want to replace all the
8s with the Qs. Replace All.
| | 01:51 | There might be hundreds of
them--608 of them to be exact.
| | 01:56 | And you see the results in the Column B already.
| | 01:58 | Now that could have been
done with multiple characters.
| | 02:01 | Sometimes you'll replace multiple
characters with fewer characters or more.
| | 02:06 | So there are some possibilities there,
when you need to adjust certain kinds of
| | 02:10 | data just with Find and Replace.
| | 02:13 | Now there is a function called Replace,
and let's take a look at these two cells
| | 02:18 | here, B2 and C2, and zoom in a little bit.
| | 02:21 | Let's imagine that the need here is,
based on the coding structure and some
| | 02:25 | changes to it, the third character
needs to be turned into an X. Now we can't
| | 02:31 | use Replace as a command to say
replace the third character, and we can't say
| | 02:37 | replace the 2s with Xs because
it's not all the 2s necessarily;
| | 02:41 | it's the third character.
| | 02:43 | So let me leave that in
place just for the moment.
| | 02:45 | I'll start the function here
and work off of Row 3. =replace.
| | 02:50 | Here's the function, and it's not really
the same as the Replace command sequence.
| | 02:55 | What do we want to replace?
| | 02:56 | We're looking in cell B3, comma,
and starting at the third position--and of
| | 03:03 | course, that means from the left;
it could be fourth position, fifth
| | 03:07 | position, whatever--
| | 03:08 | how many characters do we want to replace? 1.
| | 03:11 | Now it could be 2, but just 1.
| | 03:14 | We want to replace the third character.
| | 03:17 | And what is the new text that we want here?
| | 03:19 | Within double quotes, X, and we can
just press Enter, and that has changed.
| | 03:26 | Just to test it out a little bit,
drag it down a few cells. Has the third
| | 03:31 | character been turned into an X?
| | 03:33 | Yes, it has, regardless of the length,
regardless of what the content had been.
| | 03:37 | Go back and do the top
one properly as a function.
| | 03:40 | There we go, and from here, we'll just double-
click to copy it all the way down the column.
| | 03:44 | So in effect, what we've created here
is something similar to the Search and
| | 03:49 | Replace capability, but it's
actually using the Replace function, and it's
| | 03:53 | based on positioning.
| | 03:55 | That will be a little clearer if we
move this to the right. There we go.
| | 03:58 | So we want to replace from B2, starting
at the third position, one character, we
| | 04:03 | want to replace it with X.
| | 04:05 | Now again, remember, too, we can
replace more than one character, and we can
| | 04:10 | replace it with 2, 3, 4, 5 characters.
| | 04:13 | So there's quite a bit of flexibility
in how we do this, based on our needs.
| | 04:18 | So two examples here of the Replace
function, and prior to that, the Find and
| | 04:24 | Replace command sequence.
| | Collapse this transcript |
| Dealing with special characters and using wildcards| 00:00 | In addition to standard kinds of
replacement techniques available with Find and
| | 00:04 | Replace, you may need to have to deal
with special characters, like the asterisks
| | 00:08 | that we see in Column D. And there
is also another function, one called
| | 00:12 | Substitute, that in many ways solves
problems that Find and Replace can't handle.
| | 00:18 | Let's deal, first of all, with the
issue in Column D. Who knows how those
| | 00:22 | asterisks got in there?
| | 00:23 | We simply want to get rid of them,
| | 00:25 | or possibly substitute
some other character for them.
| | 00:28 | Let's click Column D. And using Find
& Select, the rightmost button on the
| | 00:33 | Home tab in the Ribbon,
| | 00:35 | let's go to Replace, and we
would like to replace that asterisk--
| | 00:39 | that's all we want to replace.
| | 00:41 | We don't want to match the entire cell;
| | 00:43 | we simply want to replace the asterisk
with, how about nothing? So this is empty.
| | 00:49 | Replace All. 742 replacements,
but look at Column D. It's all gone.
| | 00:56 | So how do we deal with that?
| | 00:57 | What we've got to do for now
is click OK, close, and undo.
| | 01:01 | I'll press Ctrl+Z here.
| | 01:04 | The asterisk is a special wildcard character.
| | 01:06 | Perhaps you've encountered it in the
other uses of Excel, perhaps in the filter.
| | 01:11 | Let's go back to Find & Select.
| | 01:13 | We need to somehow refer to
that asterisk in a special way.
| | 01:17 | We'll do a replace.
| | 01:18 | What we need to do here, and it's an
unlikely character to use, is the tilde character.
| | 01:24 | This is that squiggly little
character that appears above the letter N in
| | 01:27 | certain Spanish words.
| | 01:29 | On the keyboard, usually this is found
below the Escape key, to the left of the
| | 01:33 | number 1 key, and above the Tab
key on the upper left-hand corner.
| | 01:38 | So if we embed the asterisk within
tildes, two of them there, we want to
| | 01:43 | replace all the asterisks in here with nothing,
so we'll leave the Replace with panel empty.
| | 01:49 | Let's do a Replace All.
| | 01:50 | There we go, and you see
that's happened on the background.
| | 01:54 | Turns out there are only four of
them, but we did get rid of them.
| | 01:57 | So that's an unusual construction.
| | 01:58 | You might need that at different
times when you're trying to clean up data.
| | 02:03 | Now sometimes a
coding-structure change is needed.
| | 02:07 | We saw one example in a previous movie
about how to make the third character
| | 02:11 | become an X, using a function called Replace.
| | 02:14 | Now we've got a different situation,
maybe working off the same data.
| | 02:18 | We want to replace the 2 that's in here,
but only the first 2, and it might occur
| | 02:23 | in the third position, as it does in a
lot of these, or it might occur elsewhere.
| | 02:28 | It might not occur there at all.
| | 02:30 | Let's replace the first
occurrence of 2 with a certain character.
| | 02:35 | Again, we're not just saying that to make it up.
| | 02:37 | Based on the concept, we really
need to get rid of that first 2.
| | 02:40 | The function is called Substitute.
| | 02:45 | We're looking at cell B2, comma.
| | 02:50 | The text we're looking for is 2, comma.
And what we want to replace it with
| | 02:57 | is, for example, the letter Q. Now if
it's a letter, we need to embed that
| | 03:00 | within double quotes.
| | 03:02 | And then you'll see, after this in
the prompt, instance number, comma, 1.
| | 03:08 | If we leave off that argument, we will
replace all the 2s with Q. So, you see what happened.
| | 03:14 | The third entry there, that's the first
2 that we encountered, becomes a Q, but
| | 03:18 | not the fourth entry, even though it's
a 2, but the function, remember, says,
| | 03:22 | just the first instance,
because we've put in a 1 there.
| | 03:25 | Let's copy this down the
column and check out a few more.
| | 03:29 | A lot of these that begin with 2,
| | 03:30 | well, you see immediately what's
happened: the Q is out there instead.
| | 03:34 | Others like this one, the 2 doesn't
appear until way over here, and it gets
| | 03:38 | replaced with the Q. When there are
multiple 2s, only the first one gets
| | 03:42 | replaced with Q. So, pretty arcane stuff
in a certain sense, but the tool that's
| | 03:48 | definitely needed when you're in that
situation and you've got to clean up data.
| | 03:52 | You're trying to make wholesale changes to
codes. Substitute function works fine for this.
| | 03:57 | And previously in this movie, we saw
how to use the Find & Select feature for
| | 04:02 | unusual characters, like the asterisk.
| | 04:04 | We used a tilde to embed the
asterisk within it to replace those kinds
| | 04:08 | of entries.
| | Collapse this transcript |
| Getting rid of leading single quotes| 00:00 | Downloaded or copied data might
contain leading single quotes.
| | 00:04 | And although generally not a
problem, you do want to get rid of them.
| | 00:07 | There are a couple techniques for doing this.
| | 00:09 | We don't see them so obviously either.
In Column B there are some, and as I
| | 00:14 | click on Cell B3, you can see in the
formula bar there's a single quote.
| | 00:18 | If you happen to double-click in the
cell, you'll also see it there too.
| | 00:21 | We don't have these on all the cells,
but we don't know how many, and we are
| | 00:24 | certainly not going to go to
every single cell to check it out.
| | 00:27 | We need to get rid of them though, for
the sake of consistency, and potentially
| | 00:30 | some other reasons as well.
| | 00:32 | One way to approach this is simply
to use a function called Substitute.
| | 00:36 | I'll make the column a little bit
wider here, so we can see this better.
| | 00:39 | We simply want to substitute for that
single quote if it exists, nothing.
| | 00:43 | The substitute function allows us to
do this, regardless of where it's found,
| | 00:48 | but in this case it will
only be in the first position.
| | 00:51 | So, we're looking in cell B3, comma.
What are we looking for? Single quote.
| | 00:57 | We need to embed that within double quote,
so it looks a little crowded there.
| | 01:01 | That's a double quote, a
single quote, a double quote, comma.
| | 01:04 | What do we want to replace this with?
| | 01:06 | Nothing, so we put in double quote twice,
with nothing between the double quotes.
| | 01:12 | It's a little strange there.
| | 01:13 | And we don't need to actually
refer to an instance number here;
| | 01:16 | we'll simply press Enter.
| | 01:18 | It looks good, but we can't tell exactly
whether that worked, because it's still a function.
| | 01:23 | So let's copy this down in the
column. And the next step is to turn our
| | 01:27 | results into actual values.
| | 01:29 | And a quick way to do this is simply
to take any edge--top, bottom, left, or
| | 01:33 | right--say the top edge, and we're
going to drag this with the right mouse
| | 01:37 | button temporarily upward and
then downward right on top of itself.
| | 01:42 | And because we're using the right
mouse button, when we release it, we see a
| | 01:47 | menu. Copy Here as Values Only.
| | 01:50 | So what do we have in this cell right now?
| | 01:53 | There's no single quote there or
there. Look in the formula bar.
| | 01:56 | If we happen to double-click,
we won't see them either.
| | 01:59 | So the single quotes are gone,
and that takes care of the situation.
| | 02:03 | I'm going to press Ctrl+Z to do
some undos here. Go back a bit.
| | 02:07 | Another way to do this--so now
they're back again--is--and this is an
| | 02:11 | unlikely, but perhaps faster way to
do it--right-click on an empty cell
| | 02:15 | somewhere and copy.
| | 02:18 | Then select the data in question.
So we could go here, and we can quickly
| | 02:22 | highlight all the cells from here
downward, simply by holding down the Shift key
| | 02:27 | and double-clicking the bottom edge.
| | 02:29 | I'm going to select all of those cells,
right-click, Paste Special, and Add.
| | 02:35 | In effect, we're adding a blank, or
a zero, to these cells. Click OK.
| | 02:41 | So what do we have left here?
| | 02:43 | No single quote, no single quote; they're gone.
| | 02:45 | So different techniques for
getting rid of those. And here and there,
| | 02:49 | particularly if codes are all numbers,
they do cause occasional problems.
| | 02:53 | We've just seen two techniques for
getting rid of single quotes--a problem with
| | 02:57 | downloaded data sometimes.
| | Collapse this transcript |
| Removing trailing minus signs while converting data to negative| 00:00 | Trailing minus signs, although common
in some accounting software, just don't
| | 00:04 | work in Excel, and you need to get rid of them.
| | 00:07 | And just as important of course is
not only getting rid of them, but making
| | 00:10 | sure that those values
are turned into negatives.
| | 00:13 | And there is a fast way to take care of this.
| | 00:16 | Select the data, or in this case here
let's select all of the data in Column T,
| | 00:21 | and simply use a feature that's
available on the Data tab in the Ribbon, Text to
| | 00:26 | Columns, a great tool for
splitting text into columns.
| | 00:30 | But here, we're going to take care
of the data within the existing cells.
| | 00:34 | We don't need an extra
column to the right necessarily.
| | 00:37 | First step in this wizard,
simply choose Delimited.
| | 00:40 | Move on to step two. Nothing really to do there.
| | 00:44 | Go onto step three and click the Advanced tab.
| | 00:48 | And the setting we're talking about
might already be in place, but check it out
| | 00:51 | anyway, trailing minus for negative numbers.
| | 00:56 | Click OK, Finish, watch Column T,
and you see what's happening.
| | 01:01 | That's a value now.
| | 01:03 | I want to undo here and
contrast the two in the following way
| | 01:07 | as I press Ctrl+Z here.
| | 01:08 | What if we had worked with this data
and we simply tried to use math with it,
| | 01:13 | this value times 2? Nothing happens.
| | 01:17 | How about the rest of these in here?
| | 01:19 | Wherever we don't have the
trailing minus, it works just fine.
| | 01:23 | So let's go back and do this
again, what we've done earlier.
| | 01:27 | Let's adjust these, get
rid of the leading minuses.
| | 01:30 | Back to the command Text to Columns,
Next, Next, Advanced. The setting is all there
| | 01:38 | anyway. Click OK, click Finish. There we go.
| | 01:42 | We've turned these into negative
values, and the math calculations do work.
| | 01:47 | And that could be a huge savings
of time if you've got thousands and
| | 01:51 | thousands of these.
| | 01:52 | There are other techniques, too,
| | 01:54 | involving the use of text functions,
but this is by far the best way to take
| | 01:58 | care of that problem of trailing minuses.
| | Collapse this transcript |
|
|
3. Adjusting Date-Like DataConverting dates with text functions| 00:00 | There are many different ways in which you
can enter a date data into an Excel worksheet;
| | 00:05 | however, a lot of different layouts
don't lend themselves to working with
| | 00:09 | Excel dates efficiently.
| | 00:11 | To work with dates efficiently in Excel,
they must be considered values, so that
| | 00:17 | we can work with them computationally.
| | 00:19 | Date, the way we see it in column A
and in column C isn't really set up
| | 00:24 | properly. But if we've got a ton of
entries like this, we want to be able to
| | 00:29 | convert them into actual dates,
and usually this takes a separate column.
| | 00:33 | Now, a function you wouldn't necessarily
know much about, but relatively easy to
| | 00:37 | use, is a function called date,
and this function needs a year, month and day.
| | 00:42 | Now the date in Column A doesn't have
any days associated with it, so we'll just
| | 00:47 | use the first day of the month.
| | 00:48 | So what can we do with this information?
| | 00:50 | From the right-hand side of each of
those entries there, the four rightmost
| | 00:55 | characters consist of the year.
So here's a function called right, and its sole
| | 01:00 | purpose is to pull data out of
the right-hand side of these cells.
| | 01:04 | And we want to pull off the four
rightmost characters, and that will constitute
| | 01:09 | the actual year that we're looking for.
| | 01:12 | Now we need the month, but recognize
that some of them months, as in A2, are
| | 01:17 | two characters, but in A3 and A4, it's one
character. But it wouldn't pick up the space as well,
| | 01:24 | so what do we need now? From the left
side of cell A2, right there, comma,
| | 01:30 | let's pick up two characters.
| | 01:32 | That's going to give us the month.
| | 01:34 | And now we need the day, comma.
Let's just put in the number 1. A right parenthesis.
| | 01:38 | So what we're doing is gathering some
of the information there and inserting
| | 01:44 | 1 for the day to come up with a date,
and this is what we see. And that looks good.
| | 01:50 | We'll double-click.
Check out the others here and there.
| | 01:53 | Now if some of these dates, by the way,
are impossible here, if somebody put in
| | 01:56 | a 13th month or at a three-digit year
here and there, we would have some other
| | 02:01 | problems. That appears not to be the case.
| | 02:04 | Now we're not quite finished with this,
so what we also need to do in this
| | 02:07 | example here is to take these
results and either paste them back onto the
| | 02:13 | original data--but let's say we want to
hold on to that at least for the moment.
| | 02:17 | A quick way to convert these into actual
values is, after selecting them all, use
| | 02:22 | the right mouse button and drag, for
example, upward and then downward right
| | 02:27 | back on top of itself
with the right mouse button.
| | 02:30 | Copy Here as Values Only, so we
see the data looking this like this.
| | 02:35 | It's all cleaned up.
| | 02:36 | And once you've seen one example like
this--now let me press Ctrl+Z to undo.
| | 02:41 | Once you've seen one of these--I'll
make the column a bit wider--you begin to
| | 02:45 | gather some sense of how you can
pull together information from different
| | 02:49 | locations. And the example
in Column C is very similar.
| | 02:53 | I think once you saw that, you would
realize we could easily put in Column D a
| | 02:58 | function similar to this, a
collection of various date, right, and left
| | 03:03 | combinations to come up
with dates there as well too.
| | 03:06 | Column E, perhaps a little bit trickier,
and you might even need to set up a
| | 03:09 | table over here. And converting this
into a date that Excel could use, what we
| | 03:15 | need to do here is to somehow look up
what those three letters are, or work with
| | 03:20 | some other techniques.
| | 03:21 | So, a quick look at this might be the following.
| | 03:23 | We need from here to gather
the actual year, so once again
| | 03:29 | we'll use this date function.
| | 03:31 | We want to gather the year from the
four leftmost characters, so we'll use the
| | 03:36 | left characters from here.
| | 03:38 | That's going to give us the year.
| | 03:41 | Now, how do we get the month?
| | 03:43 | The next thing we need to do is to pull
out characters that start in the sixth
| | 03:47 | position, reading from the left,
and then three characters.
| | 03:50 | So here's a function you might
not have used. It's called mid.
| | 03:54 | And, by the way, if you want more
information on how to use some of these text
| | 03:57 | functions, you might want to check out
the Excel 2010 or 2007 course on advanced
| | 04:03 | formulas and functions, and learn more
about text functions like Left and Mid.
| | 04:08 | Think of the word middle here. What
we're going to be doing is pulling out data
| | 04:12 | from the middle here, and that's going
to be starting in the sixth position from
| | 04:16 | the left. So we're looking at cell E2,
6 position, Starting there, we want to
| | 04:22 | pull out three characters.
| | 04:24 | Now in this particular case, we'll be
looking at Sep. And now we want to use, by
| | 04:29 | using by using vlookup, we want to take
that Sep value, comma, and look it up in
| | 04:37 | this list that we see over here, using a
vlookup, comma. And then we want to get
| | 04:44 | our data out of the second
column of that list. That's a 2.
| | 04:48 | So there's a lot going on here,
and I wouldn't expect anybody to remember
| | 04:51 | this. And if you haven't seen the other
functions, this is quite a stretch all of a sudden.
| | 04:55 | Following the 2, then we need to put in
a 0, indicating it's an exact match, and
| | 05:01 | you should also see the word false down there.
| | 05:03 | 0 is a little bit shorter. It also works.
| | 05:05 | So that's going to give us the actual
number of the month, comma. And now the
| | 05:10 | actual day is going to be the
two rightmost characters out of E2.
| | 05:16 | And again, recognize that some of the
other days that we see there have only one
| | 05:20 | character, so that means
we'll be using the space there.
| | 05:23 | So the last thing we need here in this
combination of functions here is to get
| | 05:29 | the day out of that by choosing right,
meaning from the right side of this cell,
| | 05:37 | comma, 2, right
parenthesis, right parenthesis.
| | 05:40 | And there is the date.
| | 05:43 | That's the way we see it.
| | 05:45 | And let me make this, as I scroll to
the right, make the column a little bit
| | 05:48 | wider, so we can see this even better.
| | 05:50 | There is a lot going on there.
| | 05:51 | The result tells the story more than
anything. But a combination of functions,
| | 05:56 | and this may seem a bit involved and
pretty tricky, and yet the payoff is, a
| | 06:01 | lot of times when you encounter dates like this,
| | 06:03 | it's not just having 8 or 10, the way
we see here, but thousands of these. And
| | 06:07 | so it's worth the effort, and the work
sometimes to turn these into dates that
| | 06:12 | Excel really can use.
| | 06:14 | The one thing that needs to be changed
here before we can copy this down the
| | 06:17 | column is to make sure that as we copy
this with the reference to are table
| | 06:23 | data over there, which you do have to
set up manually, is referenced right here.
| | 06:28 | J2 to K13 must be made absolute,
and you can do that by pressing the F4 key.
| | 06:34 | And now, one more time here, this is complete,
| | 06:37 | we'll double-click to copy this down the column.
| | 06:39 | So at different times, you will need a
variety of techniques, possibly using text
| | 06:45 | functions, to convert unusual date
formats into actual Excel dates that can be
| | 06:50 | used computationally in your Excel worksheets.
| | Collapse this transcript |
| Using Text to Column for selected date formats| 00:00 | A powerful organizational tool and
one of great help in cleaning up your data,
| | 00:05 | is the Text to Columns command. It's found
on the Data tab in the Ribbon, and in this
| | 00:10 | particular worksheet we've got two examples
of dates in columns A and C. And they
| | 00:15 | are different, and yet similar in one
respect, they're trying to show, for example,
| | 00:19 | in cell A2, year, month and day data.
| | 00:23 | And at a glance, they might look the same,
but we'll focus on column A first, and
| | 00:27 | recognize that in all the entries
here, the four leftmost characters
| | 00:32 | represent a year; 2011, 2010, 2009,
| | 00:35 | and so on. And after looking at it for a little bit
you'll quickly see that the two rightmost
| | 00:40 | digits as here, represent day of
the month, and the two characters to the
| | 00:45 | left of that represent
and actual month number.
| | 00:47 | So for example, this is going to be May 31,
2011. And in all the examples here, or atleast
| | 00:53 | the ones we can see, and we assume the
data is accurate and correct. We hope it is.
| | 00:57 | But we would like to convert this into
a date format that we could really use in
| | 01:02 | Excel. In its current state, unless you
are very familiar and adept with text
| | 01:07 | functions, these aren't very usable.
But with this command, Text to Columns,
| | 01:12 | we can quickly convert this information
into something that we can use
| | 01:16 | computationally in Excel.
| | 01:18 | So select in the entire column first,
and then on the Data tab, go into the
| | 01:23 | command Text to Columns.
| | 01:26 | Even though we've got a step 1, 2 and 3
Wizard here, we'll simply skip over
| | 01:31 | regardless of the settings, step one
and step two, and go right to step three.
| | 01:36 | And you'll see an option here called Date.
You don't necessarily have to click that button.
| | 01:41 | The arrow to the right, click it,
we've got six different choices here.
| | 01:45 | M refers to month, D refers to day,
| | 01:49 | Y refers to years, and so as we
look at the entry here, comparing with
| | 01:53 | the data in column A, the year is on the
left-hand side. So we might be considering
| | 01:58 | the third choice here, or the sixth one, and
again looking back at our data we see that
| | 02:03 | the days are at the ends.
| | 02:04 | So YMD, years, month, day, left to
right, that's what we're seeing in column A.
| | 02:11 | So we choose this option, and then it's simply
finish, and the date is all cleaned up.
| | 02:16 | And now we can use this with various
date functions. We can calculate date
| | 02:20 | differences, differences between
two dates, number of days, ahead and following
| | 02:25 | dates, that sort of thing.
| | 02:26 | We can use the dates in a computational sense.
And we want to do the same kind
| | 02:31 | of thing in column C.
| | 02:32 | It's got a different layout from the
data that we'd originally seen in column A,
| | 02:36 | but it's certainly similar. We're going to be using
the same commands, same feature, in almost the same way.
| | 02:42 | We selected column C, Text to Columns,
next, next; skip over those first two steps
| | 02:50 | in the Wizard, go right into the Date options
here, and the example here looking at our data,
| | 02:56 | the years end up on the right-hand
side. So we're looking at the two Y's here.
| | 03:00 | The first two entries, and looking at
the data again, we can see, for example
| | 03:04 | in the second entry there in C3, the day comes
first. So DMY, day, month, year; left to right.
| | 03:12 | Let's make that choice, finish, and our data is
all cleaned up again. So in both examples
| | 03:17 | essentially the same kind of thing,
starting with different layouts, and although
| | 03:22 | it doesn't cover all bases, it certainly is a
quick and fast way to get this
| | 03:26 | information into date formats so that
we can use it computationally.
| | Collapse this transcript |
| Using the DATEVALUE and TIMEVALUE functions| 00:00 | If you have different kinds of date
or time entries that are formatted as
| | 00:03 | text, you can't really directly
reformat them all to be workable values.
| | 00:09 | So there are two special functions
devised just for these situations.
| | 00:13 | One is called DATEVALUE;
the other is called TIMEVALUE.
| | 00:15 | Look at the data in column A.
| | 00:18 | Now, I want to contrast that with what
might happen; what if you would actually
| | 00:22 | type something in the cells
that were not formatted as text?
| | 00:26 | I'm going to type Jan 1, 2010.
| | 00:33 | It works just fine. And that--as we look in the
formula bar, we can see this is an Excel date.
| | 00:38 | We can work with it in computational ways.
| | 00:41 | The data in column A looks
like exactly what I typed in.
| | 00:45 | Well, let's just convert
this in the cells below.
| | 00:47 | Let's convert it to dates.
| | 00:49 | Right-click > Format, and as we try
one of the various formats, say, this one
| | 00:55 | here, it looks like one of the right path.
| | 00:57 | Let's just change the look
of that so it looks like this.
| | 00:59 | When we click OK what happens? Nothing.
| | 01:03 | So we've got a problem.
| | 01:04 | We want some control over this. How can we do this?
| | 01:07 | As I suggested, a function called DATEVALUE,
designed to handle certain kinds of text entries.
| | 01:16 | In other words, dates look okay,
but they've been formatted as text.
| | 01:19 | DATEVALUE of the entry we see here.
| | 01:23 | Now, that might throw you a little bit too.
| | 01:25 | That's an actual date value.
| | 01:28 | All dates in Excel ultimately start
January 1, 1900--that's day 1; January 2 is day 2.
| | 01:36 | This is the 40,178th day since then.
But let's do all the others first and then
| | 01:42 | change the formats of these.
| | 01:44 | You can either press Ctrl+1 or right-
click > Format Cells, and pick a date
| | 01:50 | format that's most acceptable
to you under these situations.
| | 01:54 | Date, pick one of these, maybe this one,
maybe that one, or any of these that you
| | 01:58 | choose--perhaps this one.
| | 02:01 | Now the function is still in place;
| | 02:03 | we see that it's DATEVALUE.
| | 02:05 | You could potentially leave there for a
bit, but if we don't need to keep the
| | 02:08 | data in column A, what do
we do with this result here?
| | 02:12 | Simply copy these results, either onto
themselves or into column A. And you can
| | 02:17 | do that with a simple right-drag.
| | 02:20 | The date is selected. Drag any edge;
| | 02:23 | it doesn't make any difference which one.
| | 02:24 | Hold down the right mouse button, drag
the edge, perhaps into column A itself
| | 02:30 | with the right mouse button, let go
over the mouse, Copy Here as Values Only.
| | 02:35 | Now we can just throw
away this information here;
| | 02:38 | we don't need that anymore.
| | 02:40 | So a couple of steps there, but the
DATEVALUE function does its job, and
| | 02:44 | similarly, with the data here.
| | 02:46 | If we saw this, looks okay, doesn't it?
Let's right-click and format this and just as we
| | 02:52 | saw with dates, we might, for example,
want to display the data 24-hour style
| | 02:57 | using this format here. Click OK.
| | 03:00 | That doesn't work. So as you might
expect, =timevalue. And that, too, looks a
| | 03:10 | little bit strange at first.
| | 03:11 | Times are considered portions of a day.
The unit of the time calculation is one
| | 03:16 | equals a day, so that 70% of the day.
| | 03:20 | Here, too, what we do eventually is right-
click and format these cells as a time.
| | 03:27 | If we do want to display these in 24-
hour style, we use the 13:30 option.
| | 03:33 | And now those are values that
can be used computationally.
| | 03:36 | We didn't really need to use that format--
| | 03:39 | we could've used an AM, PM format--but
the main idea here is we have converted
| | 03:45 | this data so that it can
be used computationally.
| | 03:49 | So Format Cells, just as likely, we
could have used the AM, PM option, just by
| | 03:54 | choosing 1:30 PM. But now we
have data we can work with.
| | 03:59 | So from time to time, you will need to
check out either the DATEVALUE or the
| | 04:04 | TIMEVALUE functions to make those conversions.
| | Collapse this transcript |
| Converting between time units| 00:00 | If you've got data that includes hours
or days entered with decimals, you might
| | 00:05 | need to convert these into either day
or hour entries so that the values can be
| | 00:09 | used in a computational way.
| | 00:11 | There are a few standard formula
techniques that provide these solutions.
| | 00:15 | Look at the data in Column A. 8.25 hours.
| | 00:19 | Now if we actually want to convert this
into a time, typically we'd need, at least
| | 00:23 | for a while, a temporary
column or cells to the right.
| | 00:27 | And what we need to do here is
take this value and divide it by 24.
| | 00:34 | The unit of measure in the Date/Time
system in Excel is one equals a day.
| | 00:38 | So we end up with this. That's actually
0.34, or you could say 34% of a day, but
| | 00:45 | let's display this as a time.
| | 00:48 | Now we could right-click and press
Format Cells, but a nice keystroke shortcut,
| | 00:52 | if you'd like to use it, is Ctrl+Shift+@.
| | 00:55 | The problem with that though is we see
in AM on there, and that's not really wrong,
| | 01:01 | but I think in this context we
wouldn't want to use Ctrl+Shift+#.
| | 01:04 | What we really need here, what would be
better, would be to right-click and go
| | 01:08 | right into Format Cells and pick the
Time format option that contains the 13.
| | 01:15 | Now, that by implication means this
will tabulate up to 24 hours, so that's the
| | 01:21 | display I think most of us would like to see.
| | 01:23 | Double-click to copy this down
the column, and that's the way
| | 01:27 | perhaps the data should have entered that way
originally, but you know how it is sometimes.
| | 01:31 | You get data from other sources.
| | 01:33 | Here is where hours have been entered decimally.
| | 01:36 | This is how we want to convert these, by
way of a formula, into times that we can use.
| | 01:41 | And here, too, as in a lot of other
examples, once we've made the conversion, we
| | 01:46 | would then copy this into column A,
simply by dragging with the right mouse
| | 01:51 | button any of the edges of this data.
Right-drag onto the date in column A. As
| | 01:57 | soon as you let go of the mouse, Copy
Here as Values Only, and we don't need the
| | 02:01 | data in column B anymore. Delete that.
| | 02:03 | Now once again, the little oddity here
is we do have to format this again.
| | 02:08 | So right-click > Format Cells, as fast
as any way to get there. Back to Time.
| | 02:14 | Choose the second option here, OK. There we go.
| | 02:19 | And similarly, sometimes we
have this issue, Days Worked.
| | 02:22 | Now in this environment, let's
assume that days means eight hours.
| | 02:26 | So what are we seeing here?
| | 02:27 | 2.7 days. That's 16 hours plus 70% of a day.
| | 02:34 | Let's turn this into something we can work with.
| | 02:36 | Let's turn this into actual hours
that we can use here. And here, we want to
| | 02:42 | actually multiply this,
equal, this data here, times 8.
| | 02:50 | That's how many hours it is, but then we
want to take that data and display it as time.
| | 02:57 | Now, what else do we need to do here?
| | 02:58 | 21 actually would represent 21 days
if we work it into the time system.
| | 03:03 | So we need to do one
more thing with our formula;
| | 03:06 | we need to divide this by 24 and then
right-click > Format Cells, convert to
| | 03:15 | Time, use 13:30, and that
represents 21 hours and 36 minutes.
| | 03:22 | Double-click and see what's happening elsewhere.
| | 03:25 | So after copying these, it
looks like this is correct.
| | 03:28 | Remember, this was two days, so that's
16 hours plus 70%. We're talking about
| | 03:32 | 21 hours. But as you start to look at the list,
some of these things don't quite make sense.
| | 03:37 | These 8 days, what do you mean eight days?
| | 03:39 | That times 8 hours, that's 64 hours.
| | 03:41 | Why are we getting this?
| | 03:43 | And here is an oddity:
| | 03:44 | when you're working with times and the
hours tabulate to go over 24, or 48, or
| | 03:49 | 72--in other words, in modules of 24--we
need to use a different format here for
| | 03:56 | these, and it's an unlikely choice at first.
| | 04:00 | Right-click, go into Format Cells.
And perhaps if you have explored these
| | 04:05 | formats, you may have seen, as
you click on different answers,
| | 04:09 | who would naturally gravitate
toward this one, the one with 37 hours?
| | 04:14 | And since our entry in D2 is working okay,
| | 04:16 | this doesn't jump out at us,
but this is the format that we want.
| | 04:20 | But another issue suddenly gets involved here;
| | 04:23 | this is going to show
hours, minutes, and seconds.
| | 04:26 | Let's click OK and see what's happened here.
| | 04:28 | Well, first of all, the main issue that we were
dealing with has been corrected. This is 8 days.
| | 04:35 | There is our 64 hours turning up there.
| | 04:38 | We do have correct answers now,
and they're displayed correctly, but we probably
| | 04:42 | don't want the seconds there.
| | 04:43 | So one more time, let's go back in here
and format the cells. And we could have
| | 04:48 | done this in the prior step when
we were actually formatting here.
| | 04:52 | Going back into Time--remember, we
chose the 37--from here, let's jump over to
| | 04:58 | Custom and alter this so that
we don't see the ss in there.
| | 05:03 | Take out the colon and the ss and
everything that follows it. And even though
| | 05:07 | it's a bit arcane, the h within brackets
means that we can handle hours over 24.
| | 05:14 | Click OK and finally, we're back in shape.
| | 05:18 | So it took a while to get there,
but there certainly will be times when
| | 05:22 | you're working with date data, time
data, you need to make conversions.
| | 05:27 | If you want more information on these
issues, you might also want to check out
| | 05:31 | the lynda.com course on dates and times.
| | 05:34 | There is one for Excel 2010 and also Excel 2007.
| | Collapse this transcript |
| Converting to or from Julian dates| 00:00 | In some environments, you might
encounter what are called Julian dates, and they
| | 00:04 | exist in one of two forms.
| | 00:06 | In column A, we see some entries, and in
cell A2, the first four characters that
| | 00:12 | are on the left, 1990, represent a year.
The last three characters represent the
| | 00:17 | day of the year, the 31st day of the year.
| | 00:20 | Second example, that's in the year 2004,
and the day is the 211th day, and we see
| | 00:27 | some other examples here.
| | 00:28 | So what we need to do, in effect, is turn
that into an Excel date that we can work with.
| | 00:33 | In column D is a variation on Julian
dates that is shorter, and the first two
| | 00:38 | characters that represent the year.
| | 00:41 | There is a parallel in column D
with column A--same dates actually,
| | 00:44 | but different form.
| | 00:45 | So here it's 90, the year 1990, and then 031.
| | 00:49 | So the way we handle these is to
essentially start with the Date function, which
| | 00:53 | you might not have used.
| | 00:55 | It's pretty straightforward.
It allows us to construct an Excel date out of
| | 00:59 | existing information.
| | 01:00 | It would be a little bit tricky here
and we won't actually use these terms, but
| | 01:04 | this is the basic structure. And I'll
just start right here by editing this cell
| | 01:08 | in B2 and then make the adjustments.
| | 01:10 | We want to use the DATE function,
and instead of Year, what we really need to do
| | 01:15 | is to pick out the four
leftmost characters out of cell A2.
| | 01:20 | So we use the function called left.
| | 01:21 | We're looking in cell A2,4.
| | 01:26 | That's going to give us the year.
| | 01:28 | Next part is a little trickier.
| | 01:30 | The 31st day of the year, I
think you all know, is January 31st.
| | 01:35 | If we could add 31 to something--I guess
we could say, why don't we add it to the
| | 01:39 | first day of the year, and you could
imagine on other days that would make sense,
| | 01:43 | but that's going to take us one over the level.
| | 01:45 | So what we really want to do
here is to put in January 1st -1.
| | 01:50 | So instead of Month here, put in 1,
representing January, and let's put in January
| | 01:56 | 1st. But not so obvious, but you can do
it, is to put in 0. And effectively, what
| | 02:02 | we're constructing right here is
actually December 31st of the previous year.
| | 02:09 | And if we add 31 to that, this
is going to take us to January 31.
| | 02:11 | So we'll simply add onto this--using a plus--
| | 02:16 | the three rightmost characters out of cell A2.
| | 02:20 | So from the right of cell A2,3,
and that should do it. And there is our display,
| | 02:29 | January 31st, 1990.
| | 02:31 | We'll copy this down the column.
And again, most of those you don't recognize
| | 02:36 | onsite, unless the numbers happened
to be low, but you can see eventually
| | 02:40 | what's happening there.
| | 02:41 | And again, that function is the DATE
function, in kind of an unusual way, picking
| | 02:46 | out the year and effectively using
December 31st of the prior year and then
| | 02:50 | adding on the dates.
| | 02:52 | Now, I don't want to go into the detail,
but at least I'll display for you what
| | 02:55 | needs to be done in column D. And the
difference here is that when you use dates
| | 02:59 | in Excel, if you use any year from 0
through 29, Excel automatically makes that
| | 03:06 | in the 21st century.
| | 03:08 | If you use any year from 30 through 99,
it's automatically in the 20th century,
| | 03:14 | and what will happen is, somewhere down
the road, perhaps 5 years from now, 7
| | 03:18 | years, at some point,
| | 03:19 | there will be an adjustment in
Excel and they'll move that boundary.
| | 03:23 | So the formula that we see in cell E2
that's already set up is a lot more complex,
| | 03:28 | but the difference is, without going
into all the details, is it includes an IF
| | 03:33 | function here that
effectively looks at the year--
| | 03:36 | that's the two characters on left-hand
side--and if they're under 30--in other
| | 03:40 | words, if there is 0 through 29--we
start with the year 2000; otherwise, we're
| | 03:44 | starting with the year 1900,
and then it gets adjusted.
| | 03:48 | And the problem with a formula like
this is maybe 6 or 7 years down the road,
| | 03:52 | these aren't going to work.
| | 03:53 | Now I think by then perhaps we won't
see as many Julian dates, and I know that in
| | 03:58 | many environments they are not a
factor at all, but at least we have that
| | 04:01 | capability when you run into these
kinds of constructions, ways to deal with
| | 04:05 | them and turn them into Excel dates.
| | 04:07 | The final step in both of these
situations, by the way, is to take these
| | 04:12 | cells here--and it could be a whole
column; maybe as well click there--and
| | 04:16 | then do a Copy/Paste Special.
| | 04:18 | And you may well know you can do this very fast.
| | 04:20 | In other words, let's essentially throw
away the formulas and keep the results.
| | 04:24 | We can select all the data and with the
right mouse button temporarily drag it
| | 04:29 | into another column and then right back
on top of itself, let go with the mouse,
| | 04:33 | Copy Here as Values Only.
| | 04:35 | And so we've truly turn that into
an Excel date in every one of these.
| | Collapse this transcript |
|
|
4. Number-Text Conversion IssuesConverting text data to values | 00:00 | Sometimes you encounter columns of
numbers that have been formatted as text.
| | 00:04 | Now, usually that's done not
necessarily directly, but sometimes by mistake. And
| | 00:10 | in some respects, it's not a major
issue, but at other times, it is.
| | 00:13 | First of all, rarely do you see
numbers aligned on the left-hand side.
| | 00:17 | So in Column B here--we can certainly do this with
Column E as well too--perhaps we'll just
| | 00:22 | go the Home tab and right-align.
| | 00:24 | Now that doesn't mean that they are
numbers necessarily. And what you run into
| | 00:28 | sometimes is suppose these represent
thousands, or suppose they represent dollars.
| | 00:34 | What if we use one of the symbols in the
Number group on the Home tab in the Ribbon?
| | 00:38 | I want to turn these into dollars,
or display them with dollars.
| | 00:42 | It's shifted the data over a little bit,
but it didn't apply the dollar signs.
| | 00:46 | And how about comma here?
| | 00:47 | It's not doing anything either, is it,
other than shifting the data over here?
| | 00:51 | So what's happening, how can we deal with this?
| | 00:54 | Well, one way, and it seems like a
strange thing to do, but all we need to do is
| | 00:59 | to copy an empty cell and add it to these.
| | 01:02 | So I'm going to right-click an empty
cell somewhere and simply choose Copy.
| | 01:06 | I could have done Ctrl+C just as well.
Select all this data here, and again, it
| | 01:10 | could be thousands and thousands
of entries. Right-click there, Paste
| | 01:15 | Special, and we'll simply Add.
| | 01:20 | Now, two things happened there.
| | 01:22 | Why are they bold? Perhaps C1 was bold and was,
| | 01:25 | so the format got copied.
| | 01:27 | Can we make sense out of
these in terms of formatting now?
| | 01:30 | Let's choose the Comma button.
| | 01:31 | Yes, so simply by adding nothing to
the cells, we've turned text entries
| | 01:36 | into actual values.
| | 01:38 | Now, similar list in Column E.
| | 01:40 | Another way to approach this could be--
and this isn't necessarily better, but
| | 01:44 | it's another approach--is to write a
function here in an adjacent column, =value.
| | 01:53 | And just by the fact that in this
example here it made it right aligned,
| | 01:56 | that would be a clue that
something is happening here.
| | 01:59 | Let's double-click to copy this down
the column. Apply a format here, dollar
| | 02:03 | sign, comma--there we go.
| | 02:06 | So those are now values.
| | 02:08 | In this example though, we need one
additional step, and that would be to take
| | 02:11 | these values, and if we actually want
to put them into Column E, with the right
| | 02:16 | mouse button, simply drag these cells
with the right mouse button into Column E,
| | 02:22 | and as we let go, Copy Here as Values Only.
| | 02:26 | So two different ways to turn data
from text entries, turn them into actual
| | 02:33 | Excel values. And we wouldn't
need Column F data anymore here;
| | 02:37 | simply press Delete.
| | Collapse this transcript |
| Error-checking options with Convert to Number| 00:00 | Excel has built-in error checking
tools that help you uncover nonstandard
| | 00:04 | entries, for example, like the text we're
seeing in Column B--that actually is text.
| | 00:10 | Now, the error checking that exists in
Excel might be turned off--maybe someone
| | 00:14 | else has been using your computer.
maybe from the day that Excel was set up.
| | 00:18 | These were not turned on.
| | 00:19 | We should know where they are
and what they can do for you.
| | 00:22 | Let's go to the File menu
and then choose Options.
| | 00:26 | If you are using Excel 2007, you want to
choose the Office button and then Excel
| | 00:32 | Options. And at that point, you'll
be looking at the same kind of screen.
| | 00:36 | Then go to Formulas along the left-hand
side there, and here are the boxes that I
| | 00:41 | was mentioning here.
| | 00:42 | And it looks as if, in this example
here, numbers formatted as text or
| | 00:47 | preceded by an apostrophe.
| | 00:49 | These have been turned off.
| | 00:50 | Now maybe you did that, maybe
someone else did it a different time.
| | 00:54 | Notice that even though these are
called error checking rules, not all of them
| | 00:58 | represent errors, and the more familiar
you have become with Excel, the more you
| | 01:03 | begin to recognize what these mean and
whether they have any meaning to you.
| | 01:08 | It's certainly not wrong to have
unlocked cells containing formulas, but do you
| | 01:12 | want to be notified of that all the
time? Well, if you do, if you don't mind,
| | 01:16 | check that box as well.
| | 01:18 | So, I think it's worth revisiting this
location from time to time to consider
| | 01:22 | whether you want to be notified.
| | 01:24 | Now, as I click OK, watch
the difference on the screen.
| | 01:27 | We see not only green triangles on
the upper left-hand corner, but a little
| | 01:32 | indicator to the left here, a so-called
Smart Tag, and as we slide the mouse over
| | 01:37 | it, we see the description:
| | 01:39 | "The number in this cell is formatted
as text or preceded by an apostrophe."
| | 01:43 | It looks all these are.
| | 01:45 | Why don't we select all them
here? And it could be a huge list,
| | 01:48 | so what can we do now?
| | 01:49 | We can actually click that indicator
again, but this time with its drop arrow,
| | 01:54 | let's convert these to number.
| | 01:57 | So that possibly is a better
approach than using other techniques.
| | 02:00 | It's just a question of whether
you have those features turned on.
| | 02:04 | I've run into people who find these
triangles actually annoying--it gets in the way.
| | 02:08 | Well, first of all, they do not print,
so don't worry about that. But think of
| | 02:12 | them as being potentially helpful to
allow you to clean up data quickly.
| | Collapse this transcript |
| Managing leading zeroes in zip, numerical, and text codes| 00:00 | If you use numeric codes that begin
with zeros--and of course some ZIP codes
| | 00:04 | do begin with zero--
| | 00:05 | you need to be able to
see and retain those zeros.
| | 00:08 | There are a couple techniques
we want to take a look at there.
| | 00:10 | First of all, in Column A, let's
imagine that someone typed the ZIP codes, maybe
| | 00:14 | looking at a sheet of paper,
heads down, just typing the zeros where
| | 00:17 | appropriate, but the
zeros actually didn't appear.
| | 00:20 | So you can imagine, in Cell A5, for
example, whoever was typing this probably
| | 00:24 | typed 02299, as I'm typing here.
| | 00:29 | So what happens though when we press Enter?
| | 00:31 | The zero doesn't appear there.
| | 00:33 | Now we wouldn't have to go through
any shenanigans or anything unusual
| | 00:36 | here; simply format Column A--right-
click > Format Cells. And ideally, you
| | 00:42 | do this before you put the data in--it
makes a bit more sense--and you'll see
| | 00:46 | correct entries as we go.
| | 00:48 | There is a special category on
the Number tab in Format Cells.
| | 00:52 | It's called Special. And we
got two ZIP code entries:
| | 00:56 | Zip Code, the standard five
digit, or the Zip Code + 4.
| | 00:59 | In this case, just Zip Code.
There we go, click OK.
| | 01:03 | And the zeros are in place.
| | 01:05 | So in situations like that, for example
suppose this ZIP code here needs to be
| | 01:08 | changed and it's 00123,
| | 01:11 | we'll just type 123, and the
zeros pop into place automatically.
| | 01:16 | So that's an easy fix.
| | 01:18 | Now at other times we have situations
like we see in columns C and D. Let's
| | 01:23 | imagine that these ID numbers
here, too, need to have leading zeros.
| | 01:27 | Now everything in Column C is actually
a number as it turns out, and here, too,
| | 01:31 | we possibly could have been typing in
leading zeros and then look up and see
| | 01:35 | what's going on. But we need to make
some adjustments here. And as we look at
| | 01:39 | some of the numbers, recognize some
are three digit, four digit, five digit.
| | 01:43 | We actually want to turn these
into six-character ID numbers.
| | 01:48 | So what we're seeing in Column D is
actually what we want to have happen.
| | 01:52 | So let's actually shows how this is set up.
| | 01:55 | What we need to do here is to type in,
in this particular example here, a
| | 01:59 | function called =text. And we want
to take the data from C2, comma. The text
| | 02:07 | function requires that we give this a format,
and the format is going to be within double quotes, six zeros.
| | 02:14 | Now the zeros, think of them sort of as
placeholders that will contain values,
| | 02:19 | and if there's no real value to be
picked up, we'll see a leading zero, or
| | 02:23 | zeros, as necessary.
| | 02:25 | So six zeros, double quote and we don't
need the right parenthesis here since it
| | 02:29 | is just a single set, and we see what's happening.
| | 02:32 | Copy this down the column.
| | 02:34 | Now the others were already done anyway,
but at least we see how that looks, and
| | 02:37 | here's the function again.
| | 02:39 | And you see what's happening. For example, in C5,
the 814 now has three leading zeros.
| | 02:45 | So you can imagine some
variations on how to use that as well.
| | 02:48 | In Column F, a different situation.
| | 02:50 | The code numbers here are a mix of
letters and numbers, and we want them to turn
| | 02:55 | into nine characters and have leading zeros.
| | 02:59 | Here it's a little bit different,
and what we want to do here is to use a
| | 03:03 | function called rept, which means
repeat. And what we would like to do is put
| | 03:08 | in some leading zeros,
| | 03:10 | so the symbol we want to put in,
within double quotes, is a zero.
| | 03:15 | Now the question is, how many
times do we want to put it in there?
| | 03:19 | And look at the different entries in
Column F. The first entry in Cell F2 is six
| | 03:24 | characters long, the next one is seven,
the next one after that looks like it's
| | 03:27 | nine, and so on; it varies.
| | 03:30 | So the total length here is 9.
| | 03:32 | How many zeros do we need
to put in here in cell F2?
| | 03:36 | We need to put in 3.
| | 03:37 | So, how do we figure that out?
| | 03:39 | We want the number 9 minus the existing length.
| | 03:42 | 9- and here is a function
called len, meaning length.
| | 03:48 | So the length of F2 right
now in this example is 6.
| | 03:53 | So what we're about to say here--we're
not finished with the formula just yet--
| | 03:58 | we want to take this zero character and
put it in here in the first case three times.
| | 04:04 | 9 minus the length of F2, which is 6.
| | 04:08 | So we're going to put three leading zeros
there, and then what are we going to put in?
| | 04:14 | The ampersand means "and," the
actual content of F2. There we are.
| | 04:19 | And we see what's happening and double-
click, copy this down the column. And it
| | 04:24 | had been set up again anyway, but just take
any of these at random, take a look at
| | 04:27 | them--for example, this one here.
| | 04:29 | As we're looking at this, once again,
in all cases here, the length is going to,
| | 04:35 | well, here and there, vary.
| | 04:36 | The length here is 5. 9-5 is 4.
| | 04:40 | So how many leading zeros do we want here?
| | 04:42 | Four leading zeros.
| | 04:45 | So in these situations here in Column G,
we're dealing with data that's a mix
| | 04:50 | of numbers and texts.
| | 04:51 | Over in column D, we're dealing with all values.
| | 04:55 | So there is a difference in how
these are set up. But these are all using
| | 04:59 | various text functions that allow us
to take data that's not in the best of
| | 05:04 | shape and turn it into the
format that we would like.
| | 05:07 | It's not always directly done by
using the Format command, as you've seen in
| | 05:10 | these examples here.
| | 05:11 | Those are some great tools for cleaning up data and
| | 05:14 | turning it into the appearance
and the content that you want.
| | 05:17 | Here, too, as in a lot of other examples,
if we want to keep the results here, we
| | 05:23 | would select all of this data, and it
could be thousands and thousands of
| | 05:27 | entries, possibly it might be better
even to pick the column in that example.
| | 05:31 | We could adjust the headings later and
simply with the right mouse button, drag
| | 05:35 | this on top of Column F, let
go, Copy Here as Values Only.
| | 05:39 | Change your headings.
We're all set. Get rid of this.
| | Collapse this transcript |
|
|
5. Checking SpellingCorrecting multiple misspellings| 00:00 | Excel has a built-in spellchecker, and I
highly recommend that you use it before
| | 00:04 | either printing your work sheet or
possibly displaying it in a public forum.
| | 00:08 | A sharp-eyed observer out there might
immediately see that environmental has been
| | 00:11 | misspelled here in cell C3--it
might have been misspelled elsewhere.
| | 00:15 | Let's apply the Spellchecker.
| | 00:18 | It's found on the Review tab,
the leftmost button.
| | 00:21 | Possibly you might want to
check all of your data here.
| | 00:23 | For the moment, maybe just
column C, activate spellchecking.
| | 00:28 | There we see environmental, the
correction, and we would certainly want
| | 00:31 | to Change All here.
| | 00:33 | So we see it in C3. It's also in cell C10,
and perhaps later, too; we don't know necessarily.
| | 00:38 | Let just change all of them.
| | 00:40 | It gets the job done quickly.
| | 00:43 | Now there's some other aspects of
spellchecking that go beyond actual spelling.
| | 00:47 | I put the word Monday out here as a
reminder that if you type in any day of the week--
| | 00:52 | I'm going to type in Monday--
| | 00:53 | now I haven't capitalized it, but what
happens when I press Enter or Space or
| | 00:57 | any punctuation, it
automatically capitalizes it.
| | 01:01 | It does that for days of the week
but not for months of the year.
| | 01:05 | Here is another situation, Lxi.
| | 01:08 | Maybe you want the L and
the x both to be capitalized.
| | 01:11 | So I'm going to type it that way, LX
and then i, and what happens when I press
| | 01:17 | Enter? It de-capitalizes the x,
but I want that x to be capitalized.
| | 01:23 | So we have got some settings out
here that need to be looked at.
| | 01:26 | Let's go to the File menu and if you're
using Excel 2007, you will want to go to
| | 01:31 | the Office button, and there
you will choose Excel options.
| | 01:35 | Here we choose Options. And what we want
to be looking for here is the Proofing
| | 01:40 | tab on the left-hand side. And you'll
notice some choices here, also something
| | 01:46 | called AutoCorrect Options.
| | 01:50 | Notice here, Correct TWo INitial
CApitals, and I think that practically
| | 01:53 | explains what happens.
| | 01:55 | Many times we let go off the Shift key a tad
too late and we capitalize the second letter.
| | 01:59 | But in the case of that Lxi that we've
just put, we do want the x to be capitalized.
| | 02:05 | So we can have some exceptions.
And under INitial CAps right here, we want to put
| | 02:11 | in here LXi, because that's something
perhaps we use frequently, and you can
| | 02:18 | expand on this list too if you wish.
| | 02:19 | Let's add that to our list there, click
OK, click OK, as we close this dialog box.
| | 02:26 | Try that at a different location just
to check it out. LXi, and that's the way
| | 02:32 | it's going to look from now
on, when we type it that way.
| | 02:34 | So you can override some of those
settings, and it is worth taking a look at some
| | 02:38 | others of those as well.
| | 02:39 | Again, it's on the File menu
if you're using Excel 2010.
| | 02:42 | It'll be the Office button, Excel 2007.
| | 02:46 | There you go to Excel options.
| | 02:47 | Here you go to Options.
And then it's the Proofing tab.
| | 02:52 | Check out some of the
AutoCorrect options that are there.
| | 02:55 | You will see some other choices here as well too.
Capitalized names of days, we saw that already,
| | 03:00 | just to point out that does exist
here too. And there are some other
| | 03:03 | options here as well.
| | 03:04 | So the spellchecking capability, along
with the AutoCorrect options, give you
| | 03:08 | tools to clean up your worksheet and
make the text look the way you want it to.
| | Collapse this transcript |
| Using additional Spelling options| 00:00 | In addition to standard
spellchecking capability, Excel also has some
| | 00:05 | auto-correct features that go well
beyond that capability and give you some real
| | 00:08 | tools for making your use
of Excel more efficient.
| | 00:12 | In column E there's a few words here--
common words of course: "the," "and."
| | 00:17 | But commonly mistyped typos of course.
| | 00:19 | "Teh," you might be typing that;
| | 00:22 | you meant to type The.
| | 00:24 | Now that might be the first word in
what you're about to type, it might be
| | 00:28 | between other words; but as soon as
we either press Enter or punctuation or
| | 00:33 | Space, that automatically gets corrected.
| | 00:35 | And if we have the word "and,"
you might mistype it as adn.
| | 00:40 | As soon as we hit Space, Enter,
punctuation, that automatically gets corrected too.
| | 00:46 | Not quite so obvious though is the
fact that the AutoCorrect capability also
| | 00:51 | includes a lot of misspelled words.
| | 00:54 | Now that's the correct way to spell
accommodate. And it's probably not the most
| | 00:58 | commonly used word in Excel, but if you
were to type that word--and probably it
| | 01:02 | wouldn't be the first word, but it could be--
| | 01:04 | if you mistype it, as I'm doing here,
as soon as I once again press Enter,
| | 01:09 | punctuation, or Space, it
automatically gets corrected.
| | 01:12 | I guess you would say possibly some
people could go through life misspelling
| | 01:16 | this word in Excel and never know the
difference, because it automatically gets corrected.
| | 01:20 | The Excel has three misspelled versions
of this that it automatically corrects.
| | 01:26 | Also in Excel, certain symbols--and
I've typed these in a different way using
| | 01:31 | spaces--but these might come in handy
for those who need to use either the
| | 01:35 | copyright, the registered,
or the trademark symbol.
| | 01:39 | I'm going to type now. And this too probably
would be alone and itself, but not necessarily.
| | 01:44 | I'm going to be typing here (c), followed
by Space or Enter. You see what happens.
| | 01:50 | (r), same idea. There's the
registered symbol, pretty tiny there.
| | 01:58 | The last one might be a little bit
surprising, tm within parentheses.
| | 02:02 | You see what happens there.
| | 02:04 | Now the word telecommunications,
| | 02:05 | suppose I need this often--
something I want to use frequently.
| | 02:10 | If I could have a code and type the code
instead of this, so what if I need to type this?
| | 02:15 | I'm going to type qt Space.
| | 02:19 | Automatically that happened. What happen there?
| | 02:21 | How did that occur?
| | 02:22 | I've actually added this to the list.
| | 02:24 | Let's show how this is handled.
| | 02:25 | Go to the File menu. Choose Options.
| | 02:29 | If you're using Excel 2007, it would be
the Office button and Excel options, the
| | 02:35 | Proofing tab, AutoCorrect Options.
| | 02:38 | Here are the symbols we just
saw and how they were used.
| | 02:44 | Here are--starting here--a huge list of
misspellings in the left column, correct
| | 02:49 | spellings in the right.
| | 02:50 | Most of them of course would be typos,
and sure enough we will see there's one of
| | 02:55 | the bad spelling of accommodate,
two c's and one m. There is the correct
| | 02:59 | spelling, and so on.
| | 03:00 | There are a few others in there
too, and you see a lot of commonly
| | 03:03 | mistyped, misspelled words.
| | 03:05 | There's "acn" instead of "can"
and so on and so on and so on.
| | 03:09 | Now, earlier I had added one here,
and I started with the letter q. Why?
| | 03:14 | The letter q isn't used very
frequently, and usually it's follow by a letter.
| | 03:18 | So I put in a special code.
| | 03:21 | Every time I typed qt and press
Enter, punctuation, or Space, the word
| | 03:26 | telecommunications automatically appears there.
| | 03:29 | We could be thinking of
longer words, and even phrases too.
| | 03:33 | So if it turns out that I have to type
in, from time to time, Southwest Colorado
| | 03:38 | Power and Light Company--I
really need to type that all out--
| | 03:41 | I'll develop a code for it.
| | 03:43 | And you'd want to make sure that the code
wouldn't normally appear in any other text.
| | 03:48 | qt doesn't, so that's
why I used something like that.
| | 03:51 | I, on my own particular computer, often
will have a lot of codes, beginning with
| | 03:55 | the letter q. Of course
you can't have too many.
| | 03:57 | You will forget them.
| | 03:58 | So it's put them on the
yellow stick, that sort of thing.
| | 04:01 | So there are a lot of approaches to that.
| | 04:03 | The other situation where you might want
to use this too is if I, for example, I
| | 04:07 | want to type in The
Telecommunications Department.
| | 04:10 | I want it to be capitalized, so I'll
use capital QT and as soon as I press
| | 04:15 | Space, there we go, and then Department.
| | 04:18 | So you can develop your own shorthand
lists, so to speak, of codes for frequently
| | 04:23 | used entries and really expand the
capability of the AutoCorrect options, a
| | 04:28 | great tool in Excel for making your
use of this software much more efficient.
| | Collapse this transcript |
|
|
6. Specialized TechniquesCombining data from different columns via concatenation| 00:00 | Sometimes you need to pull data together
from different locations within a worksheet.
| | 00:04 | The data in columns A, B, and C is
structured in such a way that it's ideal for
| | 00:09 | sorting by last name,
and even first name if necessary.
| | 00:13 | But suppose you want to take that
information and create the kind of names that
| | 00:16 | you would put on mailing labels.
| | 00:18 | In Cell D2, for example, what we would
like to see here is Mark R. Baker, and
| | 00:24 | just below that, Sheila H. Hansen.
| | 00:26 | We can pull data together by using the
Concatenate function or a technique that
| | 00:31 | pulls the data together.
| | 00:33 | Let's take a look at both of these.
| | 00:34 | The function is called Concatenate.
And we simply want to pull the data from B2--
| | 00:43 | that's the first name where we see Mark--
comma, and we would also like to see
| | 00:47 | a space after Mark.
| | 00:49 | So double quote, space, double quote, and a comma,
and now we want to see the middle initial--it's in C2.
| | 00:56 | Recognize that some of the names
below don't have a middle initial.
| | 00:59 | Following the middle initial another
comma, double quote, space, double quote, comma, and now the
| | 01:06 | location of the last name--
it's in cell A2. We're done.
| | 01:10 | That's what it looks like.
| | 01:12 | As we copy this down the column, keep
your eye on those rows where there is no
| | 01:16 | middle initial, and they
look slightly different.
| | 01:19 | It's like there's an extra space.
| | 01:20 | Now that may or may not be a big
problem on a mailing label, but if you want to
| | 01:24 | deal with that issue, what we can do here
is actually use another function called
| | 01:29 | Trim, whose general purpose is to remove
leading and trailing spaces, but it also
| | 01:35 | converts multiple consecutive inner
spaces to simply one space. And we do need an
| | 01:41 | extra parenthesis on the right side here, too.
| | 01:43 | So changing this and then recopying
by double-clicking here, we see what's
| | 01:47 | happening. And that looks slightly different
than before, and it looks like only a single space.
| | 01:52 | So what we're doing here, again, using
the Concatenate function to pull the
| | 01:55 | data together and using the Trim function to
get rid of that multiple spaces when they occur.
| | 02:01 | Now, instead of using Concatenate, another
approach is to use the ampersand symbol.
| | 02:06 | So what we don't need here is
the actual word concatenate.
| | 02:11 | We do want to get B2, but instead of
a comma here, we put in the ampersand--
| | 02:15 | that's above the number 7 key, the & symbol.
| | 02:18 | So it's as if we're saying, let's get
Mark and the space and the data from C2--
| | 02:24 | that's the middle one--put in another
ampersand in place of the commas in all
| | 02:28 | cases here, and another space and the data in A2.
| | 02:33 | We don't need two
parentheses out there, just one.
| | 02:35 | There we go!
| | 02:37 | This is substantially shorter than
Concatenate, and if it is clearer to you,
| | 02:41 | perhaps it's the better choice.
| | 02:42 | Let's see what's happening there,
recopy these. Everything looks the same as it
| | 02:47 | did before, but for many people a little
more direct, a little faster to get to it.
| | 02:51 | Different techniques for pulling
together data from different locations.
| | 02:55 | If this is our final result, of course
we copy this all the way down the column
| | 02:58 | here for as long as that might be. And
then the final step in many situations
| | 03:04 | like this for creating conversions
is to save the data and essentially
| | 03:08 | throw away the formulas.
| | 03:10 | Once again using the right-mouse
button drag any edge in any direction.
| | 03:13 | How about up and then down?
| | 03:15 | As soon as we let go of the
mouse, Copy Here as Values Only.
| | 03:19 | So that's the pure result that we have.
| | 03:21 | By the way, if you wanted to put in
periods, that would take a little bit more
| | 03:24 | work, but that could be done as well.
| | 03:26 | Probably have to incorporate an if function.
| | 03:28 | It would be substantially longer.
| | 03:30 | Let's say for the moment it's not worth it.
| | 03:31 | It might be faster to just put
the periods over in column C.
| | 03:35 | Now, there are certain times when you
might want to gather information that
| | 03:39 | involves text and numbers together.
| | 03:42 | You see what's happening in
column E and F, just two values there.
| | 03:46 | And here is the Total Price.
| | 03:47 | Let's take a look at
what's here already in cell G2.
| | 03:51 | Total Price is, and we're showing a
calculation, and yet that calculation
| | 03:56 | isn't that readable.
| | 03:57 | It certainly isn't a standard
way to display a number like that.
| | 04:00 | What we would like to do is
show the dollar sign and format it.
| | 04:05 | There are a couple of different ways to do this.
| | 04:06 | One way might be to include this
calculation inside of a function called Dollar,
| | 04:13 | which automatically applies currency format.
| | 04:17 | So the data looks like this--much more readable.
| | 04:19 | You won't see a great need for
something like this, but it does illustrate the
| | 04:23 | concept. And sometimes the dollar isn't
the appropriate choice to make here. so
| | 04:28 | you might want to use fixed--
| | 04:29 | that's another option--and there
are other techniques here as well.
| | 04:32 | Lots of different choices here for
pulling together information from different cells.
| | 04:37 | The more important example we saw
earlier here is with the names. And also be
| | 04:42 | aware of certain situations. If you had
names like this and you wanted to put
| | 04:46 | them together as "Baker," and then Mark,
space, R, you can certainly do that,
| | 04:53 | similar to what we did here in our use
of Concatenate or the ampersand approach.
| | 04:58 | Both techniques work fine here for
getting data from different sources and pulling
| | 05:02 | them together into one cell.
| | Collapse this transcript |
| Reviewing text functions| 00:00 | Text functions in Excel can play
an important role in cleaning up
| | 00:03 | and restructuring data.
| | 00:05 | Some of these functions let you adjust
the appearance of upper- and lowercase;
| | 00:09 | others let you extract
portions of data from cells.
| | 00:12 | There are quite a few different ones.
| | 00:14 | Why don't we take a look at just a few
of these text functions, and we'll also
| | 00:17 | talk about another course here too
that I'd recommend if you need more depth
| | 00:21 | in these functions,
| | 00:23 | an Excel 2010 or Excel 2007 course on
advanced formulas and functions from lynda.com?
| | 00:31 | As we look at the data in column A,
I think a lot of us would want to have those
| | 00:35 | names so that just the
first letters were capitalized.
| | 00:39 | It's going to take up less space. It tends
to be more readable. And whenever we are
| | 00:42 | using text functions, it seems like most
of the time we do need to have an extra
| | 00:46 | column, at least
temporarily, to the right of our data.
| | 00:49 | So we've got some other examples in
column D. We've got some unusual spacing
| | 00:53 | there that we'd like to control. Column F,
maybe we would like to capitalize the letters.
| | 00:58 | These are the kinds of things that we
can tackle with various text functions.
| | 01:03 | In column B, the function we want to use
right here in B2 to adjust that data in
| | 01:08 | cell A2 is proper, meaning--and you can
see the description--converts a string
| | 01:14 | so that the first letters of each word
are uppercase, and letters that follow
| | 01:21 | punctuation, like the B in O'Brien, are uppercase.
| | 01:24 | Copy this down the column.
| | 01:26 | We can see the adjustment
pretty quickly there. Fast and easy!
| | 01:29 | It tends to be easier to read, and it does
a good job, except maybe it doesn't do
| | 01:33 | that first D in McDonald the way we might
want, but in other respects, it works just fine.
| | 01:38 | In column E, we might want to clean
up the spacing problems that we have in
| | 01:43 | column D. Now, it's worth pointing out
that at a smaller zoom, these don't jump
| | 01:48 | out at you perhaps. Or what if they
are all down in row 200 or 300 or 500?
| | 01:52 | You're just not looking there.
| | 01:53 | You don't always see these, but then one
day you happen to sort based on this column.
| | 01:58 | Now, whether this is by itself or
next to other data, if the sorting is on a
| | 02:02 | column like this--and I will do it real
quickly on the Data Tab by clicking AZ--
| | 02:07 | I think you begin to see what's happening here.
| | 02:09 | Those with leading spaces go to the top
of the list. And if one of them had more
| | 02:13 | spaces than the other, it would be on top.
| | 02:15 | So we need to clean these up,
regardless of whether they are sorted or not.
| | 02:19 | There might be trailing spaces here;
| | 02:21 | those tend to be less of a problem,
| | 02:23 | but once in a while those get in the
way of calculations as well. And sometimes
| | 02:27 | we've got multiple spaces between, for
example, the comma and the M in Mark.
| | 02:32 | We can clean all these up
with a function called Trim.
| | 02:38 | So Trim does three things for us.
| | 02:40 | It removes leading spaces, trailing
spaces, and it converts multiple consecutive
| | 02:46 | inner spaces to simply one space.
| | 02:48 | So let's see what's
happening in all these cases.
| | 02:50 | As in the case of what we
did in column B, here, too,
| | 02:54 | ultimately what we would do if we
want to keep these results is use the
| | 02:57 | right-mouse button to drag this data
on top of the old data, and as we let go
| | 03:02 | over the right-mouse button, Copy Here
as Values Only, and we could clear out
| | 03:06 | column E. Another use over in column F.
Sometimes what we want to do, as in this
| | 03:12 | case here, is make all
these letters be uppercase.
| | 03:20 | Nothing happens to the numbers,
but the letters all become uppercase.
| | 03:23 | It tends to be easier to read.
| | 03:25 | I think most people would prefer that upper.
| | 03:27 | And sure enough there is another function
called lower, probably less likely to be needed.
| | 03:32 | Off to the right here in column I, just
for point of reference, I have put in
| | 03:36 | and listed some of the different text
functions that you might want to take a look at.
| | 03:40 | Some of these text functions we've used
in this current movie. We've also used
| | 03:44 | them in other movies in this course.
| | 03:46 | There are more than I've listed here.
| | 03:48 | I think these are the ones you're
most likely to need at different times.
| | Collapse this transcript |
| Splitting data into multiple columns via Text to Columns| 00:00 | Splitting data into separate columns
is an efficient way to not only change
| | 00:04 | the layout of data, but make it more suitable
for sorting and use with functions and formulas.
| | 00:09 | I can't tell you how often I've seen
data the way it appears here in column A.
| | 00:14 | With all good intentions,
people enter names this way.
| | 00:16 | That's Eric Hendricks;
| | 00:17 | why wouldn't you type his name that way?
| | 00:20 | And many times people forget that they
would later like to sort this data by last name.
| | 00:25 | Well, you can't do that with the names
as they're displayed in column A. So a
| | 00:30 | common need is to split that data into
multiple columns and either leave them
| | 00:34 | in multiple columns or put them back together
so that it's last name, comma, space, first name.
| | 00:39 | So as you look at the data in column
A, do recognize that I am making the
| | 00:43 | example slightly simpler than it might
be in real life, because I've omitted
| | 00:48 | middle names and middle initials.
| | 00:50 | We certainly, can based on the
techniques you are going to see, accomplish the
| | 00:54 | objective of working with data like that.
| | 00:57 | But let's just show how we can use Text
to Columns, a command found on the Data tab,
| | 01:03 | to split this data into separate columns.
| | 01:05 | Now, even though A1 is not in play here,
let's imagine we have got thousands of names.
| | 01:11 | Let's make it just a tad faster. Might as
well click column A. On the Data tab, we
| | 01:16 | see the command Text to Columns.
| | 01:18 | Now, the other prerequisite here
that's already set up, make sure we've got
| | 01:23 | enough empty columns.
| | 01:25 | We don't see anybody with three
names, or let's say we check that out.
| | 01:28 | We don't know that.
So we need, at most, one additional column.
| | 01:32 | We have that in column B. Click the Text
to Columns button, and the keyword here,
| | 01:38 | not an obvious one at first, is called Delimited.
| | 01:41 | Characters such as commas
or tabs separate each field.
| | 01:45 | Well, neither of those in
this case, but actual spaces.
| | 01:49 | We know it separates the
first and the last name.
| | 01:51 | Occasionally, we might need to use the
other option where you're splitting data
| | 01:55 | every four characters, every eight
characters, or something like that.
| | 01:58 | We don't need that option.
| | 02:00 | Let's go right into step two here.
And what is it that's separating the names?
| | 02:03 | As we suggested, space.
| | 02:05 | Now sometimes when you come into this
command if you had used it a while ago
| | 02:10 | or someone else had used it on this
particular computer, you might see some of
| | 02:13 | these checked already. It doesn't hurt to have
some of these checked if they are not there anyway.
| | 02:18 | But to be more logical, let's
uncheck these and choose Space.
| | 02:23 | Then look at the preview down below.
| | 02:26 | If you have enough time, you
want to scroll through this too.
| | 02:29 | You might see some oddities here and there.
| | 02:31 | That might take you a long, long
time though with thousands of records,
| | 02:34 | so that's not always workable.
| | 02:36 | But generally, when you see the
preview, it looks as if you're on the right
| | 02:39 | track or you're not,
without scrolling. Looks good!
| | 02:43 | Let's Finish. That's it!
| | 02:46 | Next step would be to
re-label this, re-label this.
| | 02:50 | We could now sort by last name if we had to.
| | 02:52 | Some people might want to put
the names back together again.
| | 02:55 | That's not a requirement,
but you might possibly prefer that.
| | 02:58 | But it certainly didn't take very long to
split those names into two separate columns.
| | 03:03 | Similarly, in column D, we have names,
and here the difference will be that the
| | 03:08 | separator actually is comma and space.
| | 03:11 | So a quick use of Text to
Columns here, Delimited, Next.
| | 03:17 | Notice if we just use Space what we are
likely to end up with, we are going to
| | 03:20 | have all the commas left over as well.
| | 03:23 | So let's use Comma and
Space both, and then Finish.
| | 03:27 | Much less likely to be needed in this
case, but nevertheless, if you want to do
| | 03:31 | that, that's how we would proceed with it.
| | 03:33 | And in column G, another common example here.
| | 03:36 | Imagine we've got
thousands of these addresses here.
| | 03:39 | We want to isolate the City,
State, Zip in separate columns.
| | 03:44 | And recognize of course some city
names, like Wheat Ridge and Walnut Creek
| | 03:48 | and San Francisco, are multiple names,
and some city names are three names, four names--
| | 03:54 | North Las Vegas--that sort of thing.
| | 03:56 | So what we know, at a glance though, is
that the comma delineates, or signifies, the
| | 04:02 | end of the city name.
| | 04:04 | Click column G. Text to
Columns, Delimited, Next.
| | 04:10 | We don't want to be choosing Comma
and Space, or do we? Look at the list.
| | 04:13 | See what's happening here on those double names?
| | 04:16 | So what we need to do here is choose
Comma. And we really can't deal with the ZIP
| | 04:22 | code and the state yet,
| | 04:24 | so let's make this be a two-step process.
| | 04:27 | A Comma shows us, and we can see by
scrolling, that it's just handling the
| | 04:31 | multiple-named cities.
| | 04:33 | So let's just proceed with this, click
Finish, and now we have taken care of the
| | 04:38 | cities and now we are left with State and Zip.
| | 04:42 | There is a leading space there.
That might cause a slight problem, but now, let's
| | 04:46 | tackle column H. Also Text to Columns
and of course now what we want to use,
| | 04:52 | after choosing Delimited,
is not Comma, but Space.
| | 04:56 | You see what's about to happen.
| | 04:57 | We will get an extra column
but no real problem with that.
| | 05:00 | Let's do a Finish, delete column H.
That would be the easiest thing to do,
| | 05:03 | right-click and delete that.
| | 05:05 | Now, we've isolated the data this way, and so
now we could sort our data by State or Zip.
| | 05:12 | There's no shortage of other techniques
available by way of Text to Columns, and
| | 05:16 | you might have seen this being used
in prior movies in this course as well.
| | 05:20 | It's a great tool for
cleaning up and restructuring data.
| | Collapse this transcript |
| Filling in missing title information| 00:00 | Sometimes for printing reasons, or just
for clear visual display, all but the
| | 00:04 | first entry of a series of
repeating entries have been deleted.
| | 00:08 | For example, as we look in column A,
we don't see the department name ADC
| | 00:13 | repeated in rows 3 through 6.
| | 00:15 | We don't see Admin Training repeated in
row 8 downward. And setting up data like
| | 00:20 | this does make the data easier to read.
| | 00:24 | But if we want to sort this data by
Status or Benefits or Salary or any other
| | 00:28 | column, once we do that, we don't
have any way to return to this layout.
| | 00:34 | In other words, we actually need to fill
in Column A with the data that's missing.
| | 00:39 | So what we like to do is to have
ADC appear here, here, here, and here.
| | 00:44 | Now, this might be a huge list.
| | 00:46 | It happens to be about 700 names.
| | 00:48 | It could be 7,000,
hundreds of thousands possibly.
| | 00:52 | What we need to do is use a technique
not so widely used that essentially
| | 00:56 | allows us, first of all, to select the
blank cells here, and then what we are
| | 01:01 | going to do is write a formula for each of
the cells that gets data from the cell above.
| | 01:06 | So let's start the process. And this
is a pretty obscure technique, actually,
| | 01:11 | but it's really powerful.
| | 01:12 | I have run into situations like this
a number of times where people do get
| | 01:16 | list like this, they're all set to
start to use the data, they do sort it, and
| | 01:20 | they've got a disaster on their hands.
| | 01:22 | Let's select Column A first.
| | 01:25 | What we'd like to do here is
to select just the blank cells.
| | 01:29 | On the Home Tab in the Ribbon, the
extreme-right button, Find & Select the
| | 01:34 | binoculars button, there's a choice
beneath it, an unlikely choice if you
| | 01:39 | were looking for blanks,
| | 01:41 | Go To Special. And let's choose Blanks. OK.
| | 01:47 | We've chosen all the blank cells.
| | 01:49 | Right away some of you might be
wondering, well, does it select blank
| | 01:52 | cells below the data?
| | 01:53 | So I am going to scroll
down at the bottom here. Nope!
| | 01:57 | Just down to the end of it.
And that's a nice bit of built-in
| | 02:00 | intelligence there.
| | 02:01 | In other words, we are not
selecting cells A743, 744, and so on.
| | 02:06 | Right now, the only cells highlighted
in this worksheet are the blank ones.
| | 02:10 | Now, I am going to write a formula.
| | 02:12 | Equal. I am going to press up arrow.
| | 02:15 | So for this cell here, if I were to
press Enter, this cell would contain
| | 02:20 | ADC, cell A3 would.
| | 02:22 | But if I press Ctrl+Enter, every one of
these blank cells is going to have the
| | 02:27 | same relative formula in it.
| | 02:30 | So in cell A4, we are going to be
seeing a formula reading =A3, and cell A5
| | 02:36 | is going to say =A4.
| | 02:39 | So we are putting the same relative
formula in all these cells. As I press
| | 02:43 | Ctrl+Enter, we are there.
| | 02:45 | You can see what's happening.
| | 02:46 | Admin Training, Audit
Services, they're all filled in.
| | 02:50 | Now before we proceed though, if we
want to keep this data here--remember
| | 02:55 | these are all formulas--
| | 02:56 | we need to select column A. And with
the right-mouse button, we'll simply drag
| | 03:01 | this into column B, then
right back on top of itself.
| | 03:05 | Let go the right-mouse button,
Copy Here as Values Only.
| | 03:09 | A really powerful technique
for getting this data into shape.
| | 03:14 | Now we can print this data.
| | 03:16 | We don't have to worry about sorting
the data. No matter how we sort it, the
| | 03:19 | information in each of the rows is
together, and we won't have to recreate
| | 03:23 | the information that we did in column
A. It's a one-time cleanup, and it's
| | 03:27 | very powerful.
| | 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 Setting Up
a Database in Excel and also Managing
| | 00:11 | and Analyzing Data in Excel.
| | 00:14 | For a review of basic Excel features,
take a look at Excel 2010 Essentials.
| | 00:19 | If you want to become a more
proficient user of this powerful software, check
| | 00:23 | out the Excel 2010 Power Shortcuts
course, and check back from time to time for
| | 00:28 | additional courses added to this series.
| | 00:31 | Thank you for watching!
| | Collapse this transcript |
|
|
