IntroductionWelcome| 00:04 | Hi! I am Dennis Taylor and I am
pleased to present Excel 2010:
| | 00:08 | Advanced Formatting Techniques.
| | 00:11 | When you're working with Excel, there
is no question that appearances count.
| | 00:15 | A dull lifeless display won't
get the attention it deserves.
| | 00:19 | You need to know the scope of Excel's
formatting features and the quickest and
| | 00:23 | best ways to get to them.
| | 00:25 | In this course, I'll show you those
features that enhance the appearance of
| | 00:28 | worksheets, giving them wide visual
appeal, as well as making them clearer and
| | 00:33 | easier to understand.
| | 00:35 | Excel gives you formatting tools that
allow you to eliminate any doubt about
| | 00:40 | what your data means.
| | 00:42 | I'm looking forward to
showing them to you in Excel 2010:
| | 00:45 | Advanced Formatting Techniques.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you have access to the exercise
files for this course, you can put them on
| | 00:05 | your Desktop as I have
or anywhere else you want.
| | 00:07 | Here are the files I'll be using.
Recognize that there is a separate file for each
| | 00:12 | chapter in the course.
| | 00:14 | If you are about to view the Display
chapter, of course, you would be opening
| | 00:17 | that particular file
with its various worksheets.
| | 00:21 | If you do not have access to these
exercise files, you can easily follow along
| | 00:25 | and create files of your own.
| | Collapse this transcript |
|
|
1. Adjusting Numeric Formats Quickly Working with the numeric formatting options| 00:00 | Controlling the display of numeric
information is one of the most important
| | 00:04 | things that many Excel users need.
| | 00:06 | This worksheet, like many an Excel
worksheet, is loaded with numbers.
| | 00:11 | Look at the information in columns E
and F. We've got salaries here and nothing
| | 00:14 | wrong with those entries really.
| | 00:16 | But it's pretty much of a convention
that as soon as we have four digits in numbers,
| | 00:20 | we'd like to see commas
or someway to read them more easily.
| | 00:24 | We have to look the idea that
selecting the entire column often makes sense.
| | 00:30 | This will not use up any additional
memory. It doesn't make the file any larger.
| | 00:33 | Why not select both of
these columns, drag across here?
| | 00:37 | The fastest way to change the display
of these numbers in a sensible way might
| | 00:42 | be on the Home tab, in the
Number group, choose Dollar sign.
| | 00:46 | By the way, you may have noticed as I
slid over that dollar sign, the pop-up
| | 00:52 | Accounting Number Format.
| | 00:55 | In older versions of Excel, before
2007, frequently you would look at this
| | 01:00 | dollar sign and would say Currency.
| | 01:02 | Accounting Number Format,
not in everybody's vocabulary.
| | 01:05 | Now, would that be a good choice here?
And I am editorializing a bit here.
| | 01:09 | For a lot of us, this is too many dollar signs.
| | 01:12 | We see too many in every single case here.
| | 01:15 | Let's choose comma instead.
| | 01:16 | Now this is not the opposite.
| | 01:18 | The name doesn't quite ring true.
| | 01:20 | We're not sure what it means.
| | 01:21 | We haven't used it.
| | 01:22 | What does comma do?
| | 01:24 | Essentially the same thing,
but without the dollar signs.
| | 01:27 | Unlike other kinds of formatting
buttons, for example Bold, Italic,
| | 01:32 | Underlining, the dollar sign is not truly an on-
off or a toggle button. Click dollar sign. We see them.
| | 01:38 | Click dollar again, nothing happens.
| | 01:41 | So even though these two are not the
opposite, for many people it becomes the two
| | 01:46 | most important formatting buttons
when it comes to numerical information.
| | 01:50 | In either case, and depending upon the
circumstances, it's going to make sense
| | 01:55 | not to show the decimals
perhaps. Decrease the decimals.
| | 01:59 | You likely to want to see either
zero or two decimals, and of course
| | 02:04 | these buttons here.
| | 02:05 | If this were scientific data, you could
easily imagine situations where we might
| | 02:09 | want to show more decimals.
| | 02:11 | We can easily make these adjustments here.
| | 02:14 | As always, formatting simply changes appearances.
| | 02:18 | We are not changing values here.
| | 02:21 | What if the needs are greater than this?
| | 02:22 | What if some of the data we have
doesn't quite fit or perhaps we're surprised
| | 02:27 | with some of the choices here?
| | 02:28 | In columns J, K, and L, we've got
numerical information, financial
| | 02:32 | information as well perhaps.
| | 02:34 | Let's drag across columns J, K, and L.
Dollar sign might be a choice, might not be.
| | 02:40 | But what I wanted to bring out here is
the idea that negatives of course will
| | 02:44 | appear from time to time in data.
| | 02:46 | Is that the way you would like to see them
displayed, the way we're seeing them in column L?
| | 02:50 | That is the standard accounting notation style.
| | 02:54 | If we choose comma, watch the difference.
| | 02:57 | We'll still see the parenthesis.
| | 02:59 | Not everybody understands
or uses parentheses this way.
| | 03:04 | So what we need to be exploring here
is the idea that sometimes the buttons
| | 03:08 | that we see in this Number group on the Home
tab are not sufficient for all of our needs.
| | 03:13 | You'll notice in the lower
right-hand corner an arrow.
| | 03:16 | You'll also see this by the way on
the Alignment tab, on the Font tab.
| | 03:21 | When other tabs in the ribbon are active
from time to time, you won't see any on
| | 03:26 | the Formulas tab for example.
| | 03:27 | You will see them on the Page Layout tab.
| | 03:30 | But going back to the Home tab,
this Dialog Box Launcher, as it's called,
| | 03:36 | activates a dialog box,
the Format Cells dialog box.
| | 03:40 | Now this is one way to activate.
| | 03:41 | We'll show you a few more.
| | 03:43 | This is very familiar to long-time users
of Excel, and many, many times it takes
| | 03:48 | us behind the scenes.
| | 03:49 | It gives us more choices.
| | 03:51 | For those choices perhaps
that are not so obvious.
| | 03:54 | Now it could be that the choice you're
looking for is something you really are
| | 03:57 | going to need often.
| | 03:59 | So let's not minimize the use of this.
| | 04:02 | As we look at this, what might we choose here?
| | 04:04 | Here is another choice called Currency.
| | 04:07 | Now notice when I click on Currency,
you'll see the different choices here.
| | 04:11 | If you prefer the negatives to have a
leading minus, you might choose this option here.
| | 04:17 | I'll click OK.
| | 04:18 | We're not dealing with the decimals
just yet, but just see what happen there.
| | 04:22 | It actually shifted the data to the right.
| | 04:24 | I'm going to press Ctrl+Z to
undo, so you can see that again.
| | 04:28 | And I'll press Ctrl+Y to redo.
| | 04:31 | This is the difference.
| | 04:32 | Accounting Notation puts in parentheses and
it needs that extra half space to the right.
| | 04:37 | Notice in row five here, this data, this
34,469, has a half space out to the right.
| | 04:44 | So the decimals line up properly.
| | 04:47 | So it's a decision you have to make as to
which kind of format works best for you.
| | 04:51 | Again, clicking column L just
for the moment, choosing comma.
| | 04:55 | That's the choice we see.
The negative is in parentheses.
| | 04:59 | If that's not what you want, one choice
could be click that Dialog Box Launcher
| | 05:04 | and then choose Currency and
then choose a different negative.
| | 05:08 | Now you could choose red of
course or red in parentheses.
| | 05:11 | Just for contrast, I'm choosing
the leading minuses here and we see
| | 05:14 | how that plays out.
| | 05:17 | Now ideally, you should be consistent here.
| | 05:20 | So if we're doing this, perhaps we
would again go to that same location.
| | 05:24 | Instead of clicking the Dialog Box
Launcher, there are two shortcuts that
| | 05:29 | you should be aware of.
| | 05:30 | Ctrl+1 activates the Format Cells dialog
box that we just saw. You see that here.
| | 05:37 | By the way, the tab that will be active
is the one that you use when you most
| | 05:40 | recently used this particular dialog box.
| | 05:43 | If you were just for making
font changes, had we come here,
| | 05:47 | we'd see some font choices instead.
| | 05:49 | But for the moment, we're
interested in numerical choices here.
| | 05:52 | Notice also there is another choice
called Number, and this doesn't give us any
| | 05:56 | options to put in currency symbols.
| | 05:59 | Currency of course, does allow the
dollar here, and other kinds of symbols that
| | 06:05 | might be appropriate to your needs as well.
| | 06:08 | So in a certain sense you can say, if
you're dealing with numerical information,
| | 06:12 | you might want to explore
Number, Currency, and Accounting.
| | 06:16 | As we'll see a bit later too, there
are some keystrokes shortcuts associated
| | 06:20 | with these that will make them a bit faster.
| | 06:23 | So you have to make a decision here
and there about what works best for you.
| | 06:26 | I frequently steer people back to the
idea that try and use dollar and comma
| | 06:32 | and be happy with it.
| | 06:33 | Then you won't have to go behind the
scenes and explore some of the other options.
| | 06:38 | But here is another aspect of this
that you wouldn't see necessarily, and you
| | 06:42 | wouldn't probably see it in a column like this.
| | 06:44 | But I'm going to on purpose take
both of these columns and make then
| | 06:47 | substantially wider.
| | 06:49 | Sometimes in financial reports, you'll
see large numbers and smaller numbers
| | 06:53 | within the same column, and
look at the dollar signs here.
| | 06:56 | They are off on the left-hand side.
| | 06:58 | Now that may not be what you want and
so what you could choose here-- and you
| | 07:02 | wouldn't do this instinctively.
| | 07:03 | But once again, pressing either
Ctrl+1 or the Dialog Box Launcher, activate
| | 07:10 | Format Cells again, and this
time possibly choose Currency.
| | 07:15 | Click OK.
| | 07:18 | Watch the dollar signs.
| | 07:19 | So sometimes that's meaningful too.
| | 07:22 | So we see the variations with the
various number of choices that we see here.
| | 07:27 | Once again, recapping, for fast easy
changes, dollar sign or comma as needed.
| | 07:33 | You decide on the display of decimals.
| | 07:36 | Decrease, increase as needed.
| | 07:39 | If you have negatives, think about
whether the comma approach works, because it
| | 07:43 | does put in parentheses.
| | 07:45 | If not, either right-click, choose
Format Cells or press Ctrl+1, or use that
| | 07:51 | Dialog Box Launcher, and change these
to a display that suits your needs best.
| | 07:57 | We can quickly and easily format
numbers of this nature by using the buttons in
| | 08:02 | the Number group, on the Home tab,
dollar sign, comma, and where necessary,
| | 08:08 | Increase and Decrease Decimals.
| | Collapse this transcript |
| Learning the important formatting keystroke shortcuts| 00:00 | Excel has quite a few different
keystroke shortcuts that are going to help you
| | 00:03 | speed along the process of formatting numbers.
| | 00:07 | In column B for example,
we have Sales Times, times of day.
| | 00:12 | 24-hour type times here.
Nothing really wrong with that.
| | 00:15 | But if you would like to see AM/PM,
there is a keystroke shortcut, and only one,
| | 00:20 | for arranging the display of time.
| | 00:24 | It's Ctrl+Shift+@ sign.
| | 00:26 | Perhaps you might think
I'll meet you at 3 o' clock.
| | 00:28 | Maybe that's how you'll remember the at symbol.
| | 00:31 | Ctrl+Shift+@, and that's on
the same key as the number 2 is.
| | 00:36 | Over in columns M, N, and O, you'll
notice that some of the shortcuts we'll be
| | 00:39 | using here are
displayed here. Ctrl+Shift+@.
| | 00:42 | Some books by the way will
display that as a shortcut.
| | 00:45 | They might use the term Ctrl+@.
| | 00:47 | Of course, you'd have to use
the Shift key to get to it.
| | 00:50 | So however you think of it, it is there
on this key that has these two symbols
| | 00:54 | on it and the change in
column B as we now see is AM/PM.
| | 00:58 | For some people, they are more
comfortable in understanding this display
| | 01:02 | than the 24-hour type.
| | 01:03 | Nothing truly wrong with column C.
But the one keystroke shortcut associated
| | 01:08 | with dates is Ctrl+Shift+#.
| | 01:11 | Now perhaps that pound sign might
remind you of a calendar grid. That never
| | 01:16 | occurred to me once until I saw that in a book.
| | 01:18 | But Ctrl+Shift+# does
display the month as three letters and for
| | 01:24 | some people that eliminates that
momentary doubt when they're looking at a
| | 01:27 | dates to which month it is.
| | 01:30 | Now column D is just pure numbers and
nothing wrong with the display there either.
| | 01:34 | Although it's sort of a convention
that when numbers reach four digits,
| | 01:38 | it would be appropriate
possibly to have commas here.
| | 01:41 | Many of you know that the Comma
button in the Number group on the Home tab
| | 01:45 | certainly gets the job done pretty fast.
| | 01:47 | Now it does introduce
decimals. Nothing wrong with that.
| | 01:49 | I'm going to press Ctrl+Z. By the way
before I do, notice that these numbers
| | 01:55 | here do have a half space behind them.
| | 01:57 | That indicates that if the values were
negative, they would be in parentheses.
| | 02:02 | For the moment here, I'm
going to press Ctrl+Z to undo.
| | 02:05 | The keystroke shortcut that's
closely associated with this, but slightly
| | 02:09 | different in the way that it handles
negatives, and in the display here, is
| | 02:13 | Ctrl+Shift+Exclamation Point.
It is referred to as number format.
| | 02:19 | So the difference here between
this and watch the display difference.
| | 02:23 | Now when I press the Comma button,
watch that half space on the right.
| | 02:26 | You see what's happened.
| | 02:28 | I'm going to press Ctrl+Z and put a
minus in front of this, and again,
| | 02:34 | reemphasizing what we're doing here.
| | 02:35 | Ctrl+Shift+! will
keep the display the same. There we go.
| | 02:41 | Watch the Comma button.
| | 02:42 | Watch the difference.
| | 02:43 | So here and there, even though that's
not synonymous, that Ctrl+Shift+!
| | 02:49 | isn't synonymous with Comma,
| | 02:51 | they're very close.
| | 02:52 | You might consider that as part of
your repertoire of shortcuts here.
| | 02:56 | Columns E and F. Many people
will use the Dollar sign button.
| | 02:59 | I certainly do a lot.
| | 03:01 | It does apply Accounting
format and that's fine.
| | 03:05 | If the columns are wider, the dollar signs
will stay on the left-hand side of the cell.
| | 03:11 | A closely associated keystroke
shortcut, but again slightly different, is
| | 03:15 | Ctrl+Shift+$.
| | 03:16 | That's on the number 4 key.
| | 03:19 | Watch the display difference here.
| | 03:21 | The big obvious difference is that
dollar signs are next to the numbers.
| | 03:25 | Many people don't care where the
dollar sign is, and so if it doesn't bother
| | 03:30 | them whether the dollar sign is on
the left side or next to the numbers,
| | 03:33 | they'll use the keystroke shortcut or this.
| | 03:35 | But again, note the difference.
| | 03:37 | The keystroke shortcuts puts the
dollar sign next to the numbers. The symbol here,
| | 03:42 | the Accounting format,
puts the dollar sign out here.
| | 03:46 | Once again with negatives, I'll put one
negative here just for the moment. This is display.
| | 03:51 | We get on the dollar sign.
| | 03:53 | What's Ctrl+Shift+$ going to do for us?
| | 03:56 | We see the difference.
| | 03:58 | So you have to decide whether these
shortcuts, which are not exactly identical
| | 04:02 | to the buttons here, are going to work for you.
| | 04:04 | There is no question they are fast.
| | 04:05 | Let me change this back again,
by pressing Ctrl+Z, back to here.
| | 04:11 | A quick formula here for
calculating percent of change. Equal.
| | 04:15 | This is one of many
different ways to write the formula.
| | 04:18 | The later amount divided by
the earlier amount minus 1.
| | 04:24 | The answer here we would want to see
is a percent, and surely there is a
| | 04:27 | Percent button right there, and there is also
a keystroke shortcut that is identical to it.
| | 04:32 | That's Ctrl+Shift+Percent sign,
and we see the display there.
| | 04:37 | It's sort of a rule of thumb that if
percentages are below ten, you want to see
| | 04:42 | at least one decimal.
| | 04:43 | So we could increase the decimal there.
| | 04:45 | Re-copy this and then copy the
formulas downward by double-clicking.
| | 04:50 | Now here again emphasizing the idea
that the keystroke shortcut on the symbol
| | 04:54 | here are identical.
| | 04:55 | Whether we click this or press
Ctrl+Shift+%, we get exactly the same display.
| | 05:01 | In both cases, we would need if we
wished to increase the decimal this way.
| | 05:07 | Now columns J and K have
scientific information in them.
| | 05:10 | Not everybody deals with
that kind of information.
| | 05:13 | But if you do, you're familiar
with scientific type notation.
| | 05:17 | Let's highlight both of these.
| | 05:19 | Ctrl+Shift and the Caret symbol above
the number 6 key displays these using
| | 05:25 | scientific notation.
| | 05:27 | The numbers in column J were well below
zero and this number here is readable
| | 05:32 | as 6.32 times 10 to the -2.
| | 05:36 | The numbers in column K as we
saw them were well above zero.
| | 05:39 | This is 1.65 times 10 to the sixth.
| | 05:43 | Pressing Ctrl+Z again just to go back, just to
remind ourselves the difference there. Ctrl+Shift+^.
| | 05:52 | Now the default format in any worksheet
as we begin working with the worksheet
| | 05:56 | is General, and many times we don't
need to go back to a General format.
| | 06:01 | But if that need were to arise, for
example in column D, if you want to display
| | 06:05 | these in General format-- and
usually you don't want to go back to them.
| | 06:09 | It's Ctrl+Shift+Tilde.
| | 06:12 | That's the key just to the
left of the number 1 key.
| | 06:15 | So all the shortcuts that we have used
here in this movie are found next to each
| | 06:20 | other on the keyboard, to the left of
the number 1 key and then reading down as
| | 06:25 | we actually read across the
keyboard, one through six.
| | 06:28 | Here are all the shortcuts that we just used.
| | 06:29 | They are adjacent to one another.
| | 06:31 | Here and there, I think many people are going to
use three or four of these on a frequent basis.
| | 06:36 | I know I do. They're fast, they're easy.
| | 06:38 | Again, there are some slight differences
here and there with the buttons that we
| | 06:42 | find on the Home tab.
| | 06:44 | But they're a lot faster many, many
times, when you're dealing with data to
| | 06:47 | simply press one of these keystroke shortcuts.
| | Collapse this transcript |
| Setting special formats for dates, times, phone numbers, fractions, etc.| 00:00 | Not every numeric formatting need is
going to be met by a keystroke shortcut and
| | 00:05 | many times the choice that you need for
a particular set of numbers is also not
| | 00:09 | available in the Number group on the Home tab.
| | 00:12 | You may well be aware that the Dialog
Box Launcher in the lower right-hand
| | 00:15 | corner of the Number group does allow
us by way of Format Cells to see quite a
| | 00:21 | few different options this way.
| | 00:23 | Keep in mind also we can also get here
with Ctrl+1. Let's take a look at some of
| | 00:27 | the data in this particular
worksheet called SpecialFormats.
| | 00:32 | Nothing really wrong with the date
display in Column A. However, there are some
| | 00:36 | other alternates and
quite a few built into Excel.
| | 00:39 | Many, many times when we are formatting,
particularly in database type layouts,
| | 00:43 | it's going to be faster and
easier to choose the entire column.
| | 00:47 | And rather than clicking the
column first, I am simply going to be
| | 00:50 | right-clicking Column A and
go right into Format Cells.
| | 00:54 | And we're on the Number tab here.
| | 00:56 | We want to be looking at some date layouts.
| | 00:59 | Quite a few choices here.
| | 01:01 | Notice some of them have zero.
| | 01:02 | Some of them just show, for example,
the day of the month and a three letter
| | 01:08 | abbreviation for the month.
| | 01:09 | This is ideal when the entries are all
from the same year, but you see quite a
| | 01:13 | few variations here.
| | 01:15 | And I think for many, many people,
they are going to find something they want here.
| | 01:20 | So that's easily available.
| | 01:22 | Just pick one of these, for
example, for a difference here.
| | 01:24 | Maybe a long display this way.
| | 01:26 | That might be the one you want.
| | 01:27 | That's certainly going to take up
more space, but if that's what you
| | 01:29 | want, you've got it.
| | 01:30 | How about Column B? Same idea.
| | 01:33 | I am going to be right-clicking
Column B, going right into Format Cells
| | 01:36 | this way, Time layouts.
| | 01:39 | We see some choices there.
| | 01:41 | Here I think the number of choices
for most people is going to boil down to
| | 01:43 | either the second or the third one.
| | 01:45 | Now, depending upon the kind of day
that you have, if it does involve hours,
| | 01:50 | minutes, and seconds, you might want
to consider some of the others here.
| | 01:53 | But I think for the most part most people are
going to gravitate toward one or two of these.
| | 01:57 | Do note that you can
display date and time together.
| | 02:00 | That is appropriate for certain kinds of data.
| | 02:05 | Now, Social Security number,
this is not so obvious.
| | 02:08 | Let's say that whoever put in the
Social Security number here had a good idea,
| | 02:13 | and the good idea was, "Why should I
have to type those hyphens? Somehow there
| | 02:17 | ought to be a way in Excel for Excel to do that."
| | 02:19 | Well, there is a way.
| | 02:21 | Ideally, what you want to do here
though is do this even before you put in the
| | 02:25 | data, but let's say here is the data,
and nothing wrong with this. Right-click
| | 02:29 | column D, Format Cells, and the Number
tab here we want to go to Special, and
| | 02:37 | there we see Social Security Number.
| | 02:40 | Now we see the hyphens, but recognize
as I click on Cell D2, we don't see
| | 02:45 | hyphens in the Formula bar.
| | 02:47 | If we happen to double-click
here to edit, we don't see them.
| | 02:50 | We don't want to be typing them.
| | 02:51 | Why do all the work?
| | 02:52 | Let Excel display these as part of the format.
| | 02:56 | So if you're making a change here,
maybe this one had to be corrected,
| | 02:59 | you put in a 7 or whatever it has to be,
you don't deal with the hyphens at all.
| | 03:02 | You just press Enter.
| | 03:04 | Same idea with phone numbers.
| | 03:05 | We have got two sets of phone numbers
here, and again do this even before
| | 03:10 | you put in the entries.
| | 03:12 | Why type 14 characters
when you need only type 10?
| | 03:16 | Right-click, Format Cells,
Special, Phone Number.
| | 03:21 | It handles 7 and 10 digit numbers.
| | 03:24 | These days we rarely see just the 7, but
nevertheless that handles both of those.
| | 03:28 | We will click OK and watch the display change.
| | 03:31 | And here too, as with Social Security
numbers, the symbols are not truly here.
| | 03:35 | You don't see them in the Formula bar.
| | 03:37 | if you double-click, if you edit that
way, you won't see them here as well and
| | 03:40 | you don't want to be typing them.
| | 03:43 | Now, if you do get data from other
sources where these symbols actually are in place,
| | 03:48 | it doesn't take too long.
| | 03:50 | Now, they are not truly here, but if
you really had these, you can do a quick
| | 03:53 | Search and Replace and replace all
those left parentheses and right
| | 03:58 | parentheses, space, and hyphens, with nothing.
| | 04:02 | You have to do that in at least three
steps, but then once you had your data in
| | 04:05 | order from there on in, anytime you
needed to make changes or additions,
| | 04:10 | you wouldn't be typing all these extra characters.
| | 04:13 | So be aware of those that are built in.
| | 04:15 | Now, with fractions, I don't think
too many people are using these as they
| | 04:19 | might have in the past,
particularly with stock market type quotes, but
| | 04:22 | nevertheless, you will see these.
| | 04:24 | In the example here, these are simple formulas.
| | 04:27 | What might surprise you here is with some of
these you might say, well, is that exactly even?
| | 04:33 | We're seeing simple displays here.
| | 04:35 | What if we were dealing with
numbers where the quota was over a 100?
| | 04:39 | I will make this be a 102 or maybe to
make it more interesting, give it an
| | 04:44 | odd number there, 103.
| | 04:49 | 126, that's not right exactly, is it?
| | 04:53 | When you are displaying formats of this
type, again right-click Format Cells,
| | 04:58 | there is a choice called Fraction.
| | 05:01 | And note that it might be displaying
these as a single digit, two digits, three digits,
| | 05:05 | and you just have to
decide which is going to work for you.
| | 05:08 | If I choose one digit here, I think
you will see this is not a great choice.
| | 05:14 | Some of these are not really accurate.
| | 05:16 | For example, this one here,
that's not 5/8 really.
| | 05:22 | It's close enough for most of us,
but it's not really the case.
| | 05:25 | So what you want to do in situations
like this, and again I don't think too
| | 05:29 | many people use this but just be
aware that it's there, Format Cells.
| | 05:33 | If these are fractions, you might
want to display three digits, two digits.
| | 05:37 | In scientific type applications, this is
a poor choice because these numbers as
| | 05:41 | we see them are not exact.
| | 05:44 | Now, we have the same
situation here with regard to percents.
| | 05:48 | We have some options out here as well too.
| | 05:51 | And standard rule of thumb on Percents
is if the Percents are below 10,
| | 05:56 | you want to show decimals.
Otherwise you don't need to.
| | 05:59 | So I think many people would be
happy with the displays here as well too.
| | 06:02 | I don't think you'd want to use
fractions here, but once again, if you were to
| | 06:05 | use that, Format Cells. You could go
down that path and decide how many displays
| | 06:10 | you want. Perhaps this way, perhaps not.
| | 06:13 | So some ideas on how to display
different kinds of numbers, available again
| | 06:18 | typically by right-clicking.
| | 06:19 | You can also press Ctrl+1 to get into
the Format Cells dialog box and to explore
| | 06:25 | lots of different options for
different kinds of number displays.
| | Collapse this transcript |
| Building custom numeric formats| 00:00 | If you have unusual numeric formatting
needs, and it's better that you don't, but
| | 00:05 | if you do, you should be aware of
something called a custom numeric format.
| | 00:10 | In column B we have got ID numbers and
maybe the way your company for years has
| | 00:15 | displayed these ID numbers is to have
four numbers, then a hyphen, and then
| | 00:19 | three numbers, and the data in
Column B doesn't display that way.
| | 00:24 | We'd like to be able to just type in
numbers and have the hyphen appear there,
| | 00:28 | perhaps in a similar way that we can
do this with Social Security numbers.
| | 00:31 | So let's right-click on Column
B here and choose Format Cells.
| | 00:37 | And in the Format Cells dialog box, on
the Number tab, the last entry is Custom.
| | 00:44 | If you've ever looked at this list and
started to scroll through this, you could
| | 00:47 | easily become overwhelmed.
| | 00:50 | And without denigrating
anybody, this looks a little geeky.
| | 00:54 | That's the term we use
sometimes. A little nerdish perhaps.
| | 00:58 | Now, I use this occasionally and I'm not
proclaiming any unusual expertise here.
| | 01:03 | Occasionally I understand what all
these symbols mean and then I forget.
| | 01:07 | I don't use it very often,
let's put it that way.
| | 01:08 | But here and there you want to be
aware of what some of the symbols mean.
| | 01:12 | And it is, without focusing on one of
these necessarily, let's start with a
| | 01:16 | simple idea that in the early part of
this list you will see zeros here and there.
| | 01:21 | A single zero simply means a numerical character.
| | 01:25 | And if we're trying to explain what
we're trying to do in Column B, one way
| | 01:29 | would be to say we want to see four
numbers and then a hyphen and then three numbers.
| | 01:34 | So in the Custom category in the panel
right under the word Type, we can click
| | 01:39 | in here, take out whatever is
here simply by typing over it.
| | 01:43 | I want to represent four numbers. 0000.
| | 01:49 | By the way, if they are leading zeros, I
don't want any of those zeros suppressed, so
| | 01:52 | I'd put in the four zeros, and
then I'd like to see a hyphen.
| | 01:55 | So I am going to put double quote,
hyphen, double quote, and then three zeros.
| | 02:02 | Pressing Enter, Column B will be reformatted.
| | 02:06 | In this case you've got
to make the column wider.
| | 02:08 | We will double-click right
here and we see the display.
| | 02:11 | And there is already a built in format
for phone number, but you may have seen,
| | 02:15 | and I certainly have seen phone
numbers where the area code is followed by a
| | 02:18 | period and then the next three numbers.
| | 02:20 | They used to call it
exchange followed by a period.
| | 02:23 | So let's change this.
| | 02:24 | Column C, right-click, Format Cells,
Custom, and we'll simply use a format of
| | 02:33 | three zeros, double quote, period, double
quote, three zeros, double quote, period,
| | 02:40 | double quote, four zeros.
| | 02:42 | So within double quotes -- we don't see the double
quote on the display of course -- we want a period.
| | 02:47 | So embedded within the double quotes is
the symbol that we want to put in here.
| | 02:51 | It doesn't have to be a period, but this
one certainly is used from time to time.
| | 02:57 | Once again, readjusting the column width.
| | 02:59 | So that's a different way to display this.
| | 03:00 | So you can make up your own. I think
you get the idea on how you might do
| | 03:03 | that for some of these.
| | 03:05 | Now, in Columns E and G are some
other unusual formats and maybe a little
| | 03:10 | strange here and there, but on the
other hand you should know how to do these.
| | 03:13 | And here's a tip too. When you do see
numbers like this displayed, suppose I
| | 03:18 | sent you this worksheet and you might
say, "Well, I wonder how you got that
| | 03:21 | or how this happened?"
| | 03:22 | Right-click on one of these, like this
one, go to Format Cells, and there is the
| | 03:29 | display, and there it is.
| | 03:30 | Now, that's not so obvious either.
What is it, General? And then you
| | 03:34 | see asterisk period.
| | 03:35 | Who knows what that means? General*.
| | 03:40 | Maybe take a look at this one
here. Right-click, Format Cells.
| | 03:44 | What are we seeing here, *_General.
| | 03:49 | Well, maybe you've figured it out,
maybe you haven't, but the asterisk in these
| | 03:53 | particular formats, these custom
formats, signify that whatever follows the
| | 03:58 | asterisk will be repeated and
then if there is anything else in the
| | 04:02 | description, that will be used.
| | 04:04 | So what we're doing here is preceding
numerical general type formats with as
| | 04:11 | many underscores as is
necessary to fill in the column.
| | 04:15 | I know that's a mouthful, but again,
asterisk followed by underscore,
| | 04:19 | we're going to use as many underscores
as is necessary, along with the general
| | 04:23 | format of the number to
fill in the particular cell.
| | 04:27 | So these numbers are of different width.
| | 04:29 | You see what's happening
here, in all these cases here.
| | 04:32 | They all have the same format.
| | 04:34 | And here, once again, revisiting
this format. Right-click, Format Cells.
| | 04:39 | We see asterisk period
following the word General.
| | 04:43 | So the numbers go in first,
then trailing periods.
| | 04:47 | So the more of these you see,
the more you pick up on them.
| | 04:49 | And this one here, possibly and
probably how this was done, is these were
| | 04:54 | initially formatted...
| | 04:55 | Let's take a look at this. First of all,
right-click, Format Cells, and here are
| | 05:00 | the details. Maybe a bit much, but
the key element of this is the asterisk
| | 05:04 | followed by a period.
| | 05:05 | Now, probably the way these were set up
was that someone did apply Currency or
| | 05:10 | Accounting format and then made the adjustment.
| | 05:13 | So here are some numbers down here.
| | 05:15 | Maybe these numbers here might look
good if they had leading dots.
| | 05:19 | Not dollar signs but dots.
| | 05:21 | So how might we do this?
| | 05:23 | If we'd like to see commas as well,
maybe we will choose Comma first up here.
| | 05:27 | Maybe we don't want the decimals.
| | 05:29 | We'll decrease twice.
| | 05:30 | And now right-click, Format Cells.
Here is the current format and without going
| | 05:39 | through all the details here, behind the
asterisk here in the particular format
| | 05:44 | for this cell here, we are going to put
a period and then get rid of the space.
| | 05:50 | Now, another bit of information about this.
| | 05:52 | When you see semicolons here on
numerical formatting displays, this is the
| | 05:57 | display that will be used.
| | 05:58 | If it's positive, then we see a semicolon.
| | 06:01 | This is what's going to
be used when it's negative.
| | 06:03 | We want to cover all bases here.
| | 06:05 | The key element again is after the
asterisk here we want to put in a period
| | 06:10 | and then delete the space that follows it.
| | 06:11 | We will do that one more time out here.
| | 06:14 | Period, delete the space, and click OK.
And now we've got our leading periods or
| | 06:21 | dots in this format.
| | 06:23 | So you may or may not need to
experiment with some of these, but just by seeing
| | 06:27 | a few examples you will get some ideas.
| | 06:30 | If you have greater interest in this,
there are a number of different books.
| | 06:33 | The Excel In Depth books usually have
eight or ten pages on various kinds of
| | 06:38 | custom formats that you might be interested in.
| | Collapse this transcript |
| Building custom date formats| 00:00 | In addition to all of the built-in
formats for dates and times you might want to
| | 00:04 | display the date and
time in your particular way.
| | 00:08 | The data in this particular worksheet
is the same all the way across columns A
| | 00:12 | through F in the following sense:
| | 00:15 | Cell A1 has a date 8/7/2010.
| | 00:18 | We see it in the Formula bar. It is
displayed differently in B1, but it's the
| | 00:23 | exact same date, and in C1, and D1, E1, and
F1. Same thing over and over and over again.
| | 00:31 | But how do we change these displays?
| | 00:33 | Column C has a standard built-in display.
| | 00:36 | A couple of things to note about it.
| | 00:38 | The day of the week is spelled out.
The month is spelled out and the date is put
| | 00:43 | in as two digits, and so the dates
before the 10th have a leading 0.
| | 00:47 | So you might want to make some changes here.
| | 00:50 | I am going to right-click on
column C and go to Format Cells.
| | 00:54 | Here is the display that's being used.
| | 00:57 | Now, if you want to alter this and
build your own custom date format, simply
| | 01:02 | switch the Category to Custom.
| | 01:05 | Now, not exactly obvious, but four Ds here
represent the full spelling of day of the week.
| | 01:12 | In the same display you see two Ds over here.
| | 01:15 | That means that the day is going to be
displayed as a number with a leading 0.
| | 01:20 | If we put in a single D in this
portion of the format, it means that the date
| | 01:25 | will only display one
number if it's before the 10th.
| | 01:28 | The other dates will be two digits.
| | 01:30 | If we want an abbreviated day of
the week, we will put in three Ds.
| | 01:33 | So, just wipe out this existing
information here. Put in three3 Ds if we want
| | 01:39 | for example to show Sat and
Mon and that sort of thing.
| | 01:43 | The four Ms mean the month is spelled out.
| | 01:46 | Suppose we want an abbreviation there.
| | 01:48 | Take out one of those Ms.
| | 01:52 | You see the change.
| | 01:53 | Three characters for the day of the
week, three characters for the month, and
| | 01:58 | maybe we will do the same kind of thing here
slightly differently in column D. Format Cells.
| | 02:03 | Maybe this time we might
want to even change the order.
| | 02:06 | So here too we could jump into Custom.
| | 02:09 | Maybe we'll put the month first.
Three Ms. Spell it out possibly.
| | 02:15 | We can put in a space maybe.
| | 02:17 | Let's put in the day of the week.
| | 02:19 | We probably wouldn't put it in this
order typically, but let's put in four Ds.
| | 02:22 | Maybe we will spell it out.
| | 02:24 | So that's likely to be what?
| | 02:25 | It's going to be Aug Saturday.
| | 02:28 | In other words, you're in control
of how you put in the display here.
| | 02:33 | Saturday, this would be a little bit strange.
| | 02:36 | This would be Aug Sat, 07.
| | 02:38 | Let's take out the leading zero,
put in a single D. Click OK.
| | 02:45 | I think most of us probably wouldn't
use that kind of display, but you quickly
| | 02:49 | get the idea and by jumping back in
here with right-click Format Cells, you can
| | 02:54 | see how you could use Ms and Ds and Ys
in a variety of different combinations.
| | 02:58 | If you want only a two-digit year,
put in two Ys, and so on and so on.
| | 03:03 | Now, the display in column E is
unusual and we see actually the year
| | 03:09 | displayed, and by right-clicking on
one of these entries you can see pretty
| | 03:12 | quickly what's going on here.
| | 03:14 | Four Ys, two Ms, two Ds, and there too
we could put in hyphens if we wanted to.
| | 03:21 | Very simple here by doing this, this way.
| | 03:24 | I only did that for one of the cells,
but you see that kind of display as well.
| | 03:31 | So there are quite a few
variations on how you can do this.
| | 03:35 | And I wouldn't say you need to have all
these, but just be aware of that capability.
| | 03:38 | You can also insert words and
phrases here and these entries here.
| | 03:44 | It looks as if they are times of days, but
what if they were simply amount of time elapsed?
| | 03:49 | In other words, what if the
display here were different?
| | 03:52 | We'll change it this way and let's
say that the actual nature of these are
| | 03:56 | different so that they truly
represent maybe an elapsed time period.
| | 04:01 | There is no heading at the top.
| | 04:03 | A different way to display these, and not
so obvious, but here's what we can do here.
| | 04:10 | What if we switch to Custom here and
in the display here we use H followed by
| | 04:18 | double quote and anything within
double quotes will appear as text.
| | 04:22 | So we might have a leading space.
| | 04:24 | I am going to type in the
word Hours and space double quote.
| | 04:29 | So we are going to see the actual hours
followed by the phrase Hours, space and space, and then what?
| | 04:36 | M? Now here it doesn't mean month.
| | 04:39 | It's going to mean minutes and
following that, we want within double quotes and a
| | 04:44 | space and then minutes.
| | 04:50 | Double quote. So that we see the
entry being displayed this way.
| | 04:56 | Again, that display, under
Format Cells, you see it right here.
| | 05:00 | Let's bring it down to see them both together.
| | 05:02 | There is the display.
| | 05:04 | So you can imagine quite a few
variations on this in terms of displaying.
| | 05:08 | I think you're more likely to change the
display with dates, but also with times
| | 05:11 | as we are seeing in the example here.
| | 05:13 | You do have control over your own
custom formats for date and times.
| | Collapse this transcript |
|
|
2. Controlling Fonts, Borders, and Cell Colors Applying and adjusting fonts and font colors| 00:00 | You can quickly change a worksheet
and perhaps add a little flair simply by
| | 00:03 | changing a font design or the size of a font.
| | 00:08 | The title in cell A1 here is a so-called
Broadway font and of course we can see
| | 00:13 | in the Font group on the Home
tab the current font being used.
| | 00:17 | Cell A3, for example, uses the default
in Excel the Calibri font. Recognize
| | 00:22 | that when we do click on cell A1 we
see this choice and with the drop arrow
| | 00:27 | we can certainly explore quite a few
others, and as we slide over these, we're
| | 00:31 | not clicking of course,
| | 00:32 | we can see quite a few different choices and
see them as they will appear on the worksheet.
| | 00:37 | This could be a real time
waster if you're not careful.
| | 00:40 | There's just so many choices out here.
| | 00:42 | And again, you use the choices that
fit your particular needs. Easy and fast
| | 00:46 | changes there for sure.
| | 00:48 | And the color of them can be changed
just as easily with the drop arrow just to
| | 00:53 | the right of the Font Color button, and
here and there we slide across different choices,
| | 00:58 | and with white backgrounds
generally we want to choose a darker color
| | 01:02 | for readability purposes
like this one for example.
| | 01:04 | And there will be times when we want to
change multiple cells, of course, just
| | 01:09 | hide a few cells, and again,
explore some of those many options.
| | 01:13 | If these colors are not the ones that
you want, you might want to explore using
| | 01:17 | different themes or possibly click for
More Colors option and you've got a 127
| | 01:23 | color choices here, along with
some blacks and grays as well.
| | 01:27 | If you would like all of these three
revert to the more or less standard font
| | 01:31 | colors, then there is a choice you
may have seen there with the drop arrow
| | 01:36 | called Automatic and it simply uses
the black standard background there.
| | 01:41 | I would strongly suggest that you not
change the colors of numerical cells
| | 01:45 | particularly in accounting environments,
because there are standard techniques
| | 01:49 | they are for making negatives appear in
red automatically, and although it may be a
| | 01:54 | little bit of trivia, in Excel 2010 and
Excel 2007, the standard font is Calibri
| | 02:00 | size 11. In prior versions
of this it was Arial size 10.
| | 02:05 | As we look at the two next to each
other here, they certainly look very, very
| | 02:09 | similar in style. Slight
difference here and there.
| | 02:12 | A small point, but one that could be confusing.
| | 02:15 | This cell here actually is size 12, we
see that, but looking within the font
| | 02:20 | group here we see 12. This is 11.
| | 02:21 | But when you do highlight a cluster of
cells and you're wondering which font is
| | 02:26 | being used and what size is being used,
you could make the mistake of saying
| | 02:30 | well, looks like they're all what, Calibri 11?
| | 02:34 | Well, that is the size of the active
cell, but the others are not necessarily
| | 02:39 | that and so by changing this of
course we are saying make them all be 11, and
| | 02:44 | then of course this one now is 11 as well too.
| | 02:47 | If you do want to save a little bit of
ink here for printing purposes, you might
| | 02:51 | use a different font called Century
and you'll find it in list here too.
| | 02:59 | Somebody does some research on this.
| | 03:01 | If you want to use 20% less ink,
use this particular format.
| | 03:07 | So there are quite a few different
formats to be sure. The sizes are easily get to,
| | 03:11 | and we are talking about those
easy to reach buttons in the Font group on
| | 03:15 | the Home tab. The most important one is
for changing the font color, if that's
| | 03:18 | you focus, or the actual font itself or
the numbers. All together here on that Font group.
| | Collapse this transcript |
| Adding custom formats with font color variations| 00:00 | In this particular worksheet called
CustomFontColors, you'll recognize that
| | 00:04 | in column A we do have numbers
there with different colors, but there's
| | 00:08 | something a little different going on
here. I'm going to click on one of these.
| | 00:10 | For example, the cell A2.
| | 00:12 | Maybe I want to make this one green.
| | 00:15 | If I click the Font button here, as I
slide over these, we're not seeing any changes.
| | 00:22 | Something is a little
unusual here. What's happening?
| | 00:26 | Primarily for historical reasons,
but something that you might encounter
| | 00:29 | anyway and something you could possibly
use, there is a way to apply formats by
| | 00:35 | way of a custom format.
| | 00:37 | By simply selecting one of the cells
and right-clicking and going to Format Cells,
| | 00:42 | you're going to see something
unusual: a special custom format.
| | 00:47 | And this is a little harder to read here, so
what I'm going to do is simply drag it across it
| | 00:51 | and press Ctrl+C and Cancel, and
then display as a bit larger right here,
| | 00:58 | space Ctrl+V. The format that's being
used in column A, and by inference you can
| | 01:05 | figure out what it is now doing,
| | 01:06 | says the following:
| | 01:08 | if any cell one here is greater than or
equal to 80 it's going to be blue.
| | 01:14 | The double zero happens to relate to the
particular format, two numerical entries.
| | 01:19 | Magenta is being used for those that
are greater than or equal to 70 and of
| | 01:24 | course that means those that are
below 80 at the same time. So the logic is
| | 01:28 | inclusive as we move from left to right.
| | 01:31 | So the blues are the 80 and above
the Magentas are those that are 70 up
| | 01:34 | through but just short of 80, and the
greens are all the others, and we see
| | 01:39 | how it is also being used.
| | 01:40 | Now with some of the expanded
conditional formatting capability that you will
| | 01:45 | have available in Excel 2010 as well as
in Excel 2007, these are less frequently used,
| | 01:51 | but in older worksheets, you
still might see them, they're still viable
| | 01:55 | and they still work, and it does
provide a different way of providing custom
| | 01:59 | formatting for fonts
based on numerical entries.
| | 02:03 | The colors that can't be used within
brackets are listed right here in column C.
| | 02:07 | These are the only choices you have available.
| | 02:09 | You can certainly use other kinds of
logic here using the arrows and the equal to sign.
| | 02:13 | But again, primarily for
historical reasons, and the fact that you may
| | 02:17 | encounter them, a way to use custom
formats to apply colors within a worksheet.
| | Collapse this transcript |
| Applying underline, strikethrough, subscript, and superscript formatting| 00:00 | In addition to changing the color of a
font or the actual font design, there're
| | 00:05 | other buttons in the Font group on
the Home tab that we might explore also.
| | 00:09 | Certainly, every Excel user knows how to use
Bold and Italic, easy on/off toggle buttons.
| | 00:14 | recognize it for underlining, which we
might want to do for these three cells,
| | 00:18 | there's certainly is the U.
A lot of people have used that.
| | 00:21 | You may not have tried the other
variation available in the drop-down
| | 00:25 | called Double Underline.
| | 00:26 | If you look to the right, you'll notice
another reference here, and you wouldn't
| | 00:30 | necessarily know until you see it.
There is a third and even a fourth kind of
| | 00:34 | underlining available in
the Format Cells dialog box.
| | 00:39 | You can see these two side-by-side.
See the difference. The word Single and
| | 00:42 | then Single Accounting.
| | 00:43 | Notice how the line appears with the data.
| | 00:46 | We also see it in Column G.
| | 00:48 | And if you are interested in these
variations, let's suppose we want to
| | 00:51 | experiment with this a little bit.
| | 00:53 | We could certainly right-click
and jump right into Format Cells.
| | 00:57 | You can also get here by way of Ctrl+
Shift+F. And in the Underline section, in
| | 01:02 | the middle on the left-hand side
here, we do see the differences.
| | 01:06 | The previews here pretty
much tell the story as well too.
| | 01:09 | Single, as the preview shows, puts the
line pretty close to the data, compared
| | 01:15 | with Single Accounting, which moves the
line farther away from the information.
| | 01:20 | So use that as necessary,
but they're easy to get to.
| | 01:25 | And here we see the two examples of
Double and Double Accounting in these
| | 01:29 | two sets of cells here.
| | 01:32 | Another possible use for the Format
Cells dialog box is if you need to use a
| | 01:38 | superscript, for example the number 2
here to make it display the way we see
| | 01:41 | this in Column H, or 2 as a subscript,
as in H2O, the way we see it here.
| | 01:47 | And simply in these cases,
highlight the data in question.
| | 01:52 | Perhaps this time we can just press Ctrl
+Shift+F, another way to get in there,
| | 01:56 | into that same dialog box.
| | 01:57 | In this case for the example here,
we want to use Superscript, click OK, and
| | 02:02 | the 2 looks like that of course here.
| | 02:04 | Similarly we want this to be a
subscript. Hit Ctrl+Shift+F. We can do it that way
| | 02:08 | and use Subscript.
| | 02:11 | And a different feature that's also
here is something called Strikethrough.
| | 02:15 | You see how it's being used here.
| | 02:17 | And once again, different ways to
get there, but Format Cells. Here's the
| | 02:21 | Strikethrough option.
| | 02:23 | It's a toggle on/off.
| | 02:24 | We are taking it off.
| | 02:25 | We can just as easily put it on.
| | 02:27 | I'm going to highlight these cells
because I want to apply Strikethrough.
| | 02:30 | Ctrl+5 is the keystroke shortcut and that is
a toggle shortcut, so Ctrl+5 will remove this.
| | 02:37 | I suppose if you think that 5 sort of looks
like an S, maybe that will help you remember this.
| | 02:43 | I use this more than I thought I would
have when I discovered it a few years ago,
| | 02:46 | but it too is one of those choices
available when we enter the Format Cells dialog box.
| | 02:52 | We've talked about Subscript,
Superscript, Strikethrough and the various
| | 02:56 | underlining choices are available here as well.
| | Collapse this transcript |
| Applying border styles and adjusting gridlines| 00:00 | In this worksheet, you can see a
variety of different border styles being
| | 00:03 | applied to make the data
offset, stand out a little bit.
| | 00:07 | You'll also notice that the
gridlines have been turned off.
| | 00:10 | If you are going to apply borders, as we
see for example in columns B and C or
| | 00:15 | in column E, probably it shows up a
bit better for not seeing the gridlines.
| | 00:19 | Maybe you can click the View
tab and add or take off gridlines.
| | 00:23 | Throughout the entire
worksheet, it's an on/off button.
| | 00:26 | It's Gridlines and we'll show you
the difference in a second here.
| | 00:30 | Now, suppose I want to put a
border on these cells here.
| | 00:35 | Right-click, Format Cells, and don't always
count on the Border tab having been active.
| | 00:41 | It depends on what you have done recently,
but if it isn't, click the Border tab.
| | 00:45 | Now possibly when you're adding
borders you might want to consider color.
| | 00:49 | So you might start with that feature first.
| | 00:50 | You don't necessarily do that one first.
Maybe we want an orange color here and
| | 00:55 | then you pick a border style.
| | 00:56 | You want a thick line or a double
line, some other variation here.
| | 01:00 | Do you want this border just on the
perimeter as it might look here or do you
| | 01:04 | want it on the inside cells
as well? So possibly there.
| | 01:07 | I doubt if you are going to use
this one very oft. You might not.
| | 01:11 | You can also click the various
components in this display right here too.
| | 01:15 | For example, if I click the one on the
right, I would really want to proceed
| | 01:19 | with that, but that's a possibility.
| | 01:20 | Or click it again and here there
depending upon what's being outlined,
| | 01:25 | sometimes you might
consider that variation as well.
| | 01:28 | So as we click OK, here
we will see some changes.
| | 01:31 | And again, it's always a good idea,
once you apply changes, click outside of it.
| | 01:36 | Here it's pretty clear what's going on,
but you might want to turn off the
| | 01:39 | Gridlines anyway. It accentuates
what we've done here more clearly.
| | 01:43 | If there is a particular set of cells
where you simply want to put a simple
| | 01:47 | border on, suppose on these cells here,
| | 01:50 | there is a keystroke shortcut, Control+Shift+&.
| | 01:53 | Above the number 7 key. It puts a simple
border around the highlighted cells.
| | 01:59 | Again, click outside of the
area to see what's happened.
| | 02:01 | If you would like to get rid of borders,
maybe you decide these borders here
| | 02:05 | just don't serve much purpose.
| | 02:06 | I mean there are two
different kinds here as well.
| | 02:09 | Highlight all of these, keystroke
shortcut, Control+Shift+Underscore, and that
| | 02:15 | removes all borders within the selected area.
| | 02:18 | Now there is also a way to draw a grid.
| | 02:21 | And you see the grids here.
| | 02:22 | you can actually draw these if you wish.
| | 02:24 | It does mean resurrecting a
feature out of Excel's past.
| | 02:28 | If you right-click anywhere in the
Quick Access toolbar, you can choose
| | 02:32 | Customize Quick Access toolbar and
Choose commands from, choose All Commands, and
| | 02:41 | in this choice here you will see
a number of draw border features.
| | 02:47 | And you might want to experiment with
some of the different ones, but I am going
| | 02:50 | to use the last one here, which is
called Draw Borders. Add. Click OK.
| | 02:57 | Now you might later remove this from
the Quick Access toolbar because you
| | 03:01 | don't use it very often.
| | 03:03 | You certainly have that option.
| | 03:04 | I want to use it now.
| | 03:05 | So I am going to click this button and
I might want to consider just a simple--
| | 03:11 | Maybe I will use this dash line here.
| | 03:13 | I want to use the dashed line and I
could add color so I'll click this again.
| | 03:17 | This isn't exactly the fastest way.
| | 03:19 | Maybe use a dark green here. And then I'll
come back a third time possibly to do what?
| | 03:26 | Either I am about to draw just an outer
border or maybe a border grid, and down
| | 03:32 | here I want to draw a border
grid starting at this point.
| | 03:37 | So I come back up here. Choose Draw Border Grid.
| | 03:41 | It activates the pointer.
| | 03:42 | Notice the dots in the worksheet as well.
| | 03:45 | I want a grid over this section right here.
| | 03:47 | By pressing Escape, those other dots
that we see right here will disappear.
| | 03:52 | So you can either draw a grid as
I did here or the other approach.
| | 03:55 | Now it does take a little longer.
| | 03:56 | You have to constantly go back and
forth to that particular tool, so that may
| | 04:01 | not be something you'll use that often.
But there is a way to do that as well.
| | 04:05 | I think if you get used to the standard
way of doing this, this is going to work
| | 04:08 | pretty smoothly for most people.
| | 04:10 | And again, a reminder. You can quickly
get into format cells in a variety of ways.
| | 04:14 | Ctrl+1 is another way. Activate
this particular dialog box and if a
| | 04:20 | border isn't selected, select it, and
then pursue some of the options that we
| | 04:23 | saw earlier within this particular dialog box.
| | Collapse this transcript |
| Using fill color and patterns for cell backgrounds| 00:00 | You can clearly see in this
worksheet that cells have been colored.
| | 00:04 | Color backgrounds have been added, but you
might not know, but you might be seeing for
| | 00:07 | the first time here that you can also
use patterns within cells, and many times
| | 00:13 | these are not a great idea.
| | 00:14 | I think I wouldn't make a strong case
for saying this is a great feature, but
| | 00:17 | here and there it is going to make some sense.
| | 00:19 | You might also occasionally want to
use lines like this to separate data.
| | 00:23 | In other words, put them into an empty row.
| | 00:25 | Let's just show how we can do this here.
| | 00:27 | Maybe we want to either change
this or make a different pattern.
| | 00:31 | Let's begin by right-clicking and
jumping right into Format Cells and for the
| | 00:36 | highlighted area, we want to click the Fill tab.
| | 00:40 | We see colors here, Pattern Styles listed here.
| | 00:45 | Click the drop arrow and I think
you'll see almost immediately if we use a
| | 00:49 | choice like this, thin horizontal
crosshairs, you see what this is likely to be.
| | 00:53 | You can apply a color
along with that at the same time.
| | 00:57 | Let's say that wouldn't be a very good
choice for any cells that have data that
| | 01:00 | you are trying to read.
| | 01:02 | So the better choice here might be one
that has a very skimpy dot display this way
| | 01:06 | or possibly this one a little denser.
| | 01:09 | You will have to
experiment with this a little bit.
| | 01:11 | The pattern color itself, would you
want that to be a different color?
| | 01:17 | Now we have red dots instead of black dots.
| | 01:19 | So you can even go that far if you wish.
| | 01:21 | So we are going to change the look of
this by using a slightly different pattern style,
| | 01:26 | slightly denser, use
some dots and a color as well.
| | 01:30 | That's the color of the dots.
Click OK and you see the difference.
| | 01:36 | I'll press Ctrl+Z to
remind you how that did look.
| | 01:38 | Now I'll press Ctrl+Y to go
back and forth. Back again.
| | 01:42 | So different approaches there.
| | 01:44 | Over here is something a little bit
fanciful, sort of show-off. Really I don't
| | 01:48 | think too many people would use this,
but same location here, right-click Format Cells,
| | 01:53 | and here what we would use
possibly is Fill Effects, and you can see the
| | 01:59 | two colors here that have been
used. See them in the background.
| | 02:03 | Of course, you get tons of choices here.
| | 02:05 | You can waste a lot of time
on this one, couldn't you?
| | 02:07 | There is green and yellow.
| | 02:08 | It's actually being used
in the previous example.
| | 02:10 | So, how about some different colors here?
| | 02:11 | Some greens and blues, you mix them different
ways, decide how you want them to look here.
| | 02:16 | Maybe it's this one, maybe that one.
| | 02:19 | You can even do diagonally.
| | 02:20 | I think you can waste a lot of time
here, couldn't you? How about these?
| | 02:24 | Click OK.
| | 02:25 | Green on top and bottom, bluish in
the center, there we go. So you have
| | 02:30 | that look, one by one.
| | 02:32 | So, just fast easy way is to change the
look of certain worksheets, and here and
| | 02:36 | there perhaps it adds a light touch to
the worksheet and just makes it a little
| | 02:39 | bit more interesting, but you can add
patterns to cells as well as colors.
| | Collapse this transcript |
|
|
3. Alignment Tools Adjusting horizontal and vertical cell alignment| 00:00 | Most Excel users, even beginning Excel
users quickly, have become proficient with
| | 00:05 | alignment formatting.
| | 00:07 | For example, in these cells right here,
some of us would leave the cells looking
| | 00:11 | just the way they are, but if we'd like
to right-align the data in the Alignment
| | 00:15 | group on the Home tab in the ribbon,
surely right alignment makes sense.
| | 00:19 | Center may be your preference,
either way it can be changed.
| | 00:22 | In column G, these cells here look
perfectly fine in their default location.
| | 00:27 | Notice that sometimes you don't see
any alignment buttons that appeared to
| | 00:30 | have been chosen, so the default location,
left alignment of nonnumeric data is automatic.
| | 00:36 | We might want to center these
or even right align them.
| | 00:38 | Occasionally that looks
better to some people's eyes.
| | 00:40 | So these are pretty easy to get to
and most Excel users know them pretty
| | 00:44 | readily and quickly.
| | 00:46 | I might point out here that with
numeric entries, there is some other thinking
| | 00:50 | going on that might occur to you.
| | 00:52 | I have seen data like this where people
would like to center data and I want to
| | 00:56 | suggest that that's a bad idea.
| | 00:58 | If you are dealing with accounting
type information, particularly when it has
| | 01:01 | decimals, centering numbers is not a great idea.
| | 01:04 | What you might find too is if you try
to center these, as I'm about to do here,
| | 01:09 | that maybe what happened
here? A mix of reactions.
| | 01:13 | And Excel is a little bit
inconsistent about this.
| | 01:16 | Look what happens in these cells here, and
earlier I just try this but it didn't change.
| | 01:21 | I am trying to left align it.
I am trying to center it.
| | 01:24 | Now there is a slight wiggle there
with a dollar sign moving around a little bit,
| | 01:27 | but the data itself isn't.
| | 01:28 | Now these are in Accounting format activated
or created with the dollar sign button here.
| | 01:34 | If you use the keystroke shortcut for
applying currency, or if you happen to
| | 01:38 | just right-click, go to Format Cells
and choose Currency Format, and maybe
| | 01:44 | concerned about negatives isn't an
issue here, but we want two decimals.
| | 01:47 | The dollar sign. Let's click OK.
| | 01:49 | We have obvious change there with the
dollar sign, which you may have seen before,
| | 01:54 | but try centering the numbers now. We can.
| | 01:56 | Now again this is a bad idea, I
think, and left aligning is too.
| | 02:00 | But the fact that you can't do with
some formats and not others can be a
| | 02:04 | little bit confusing.
| | 02:05 | With numerical information,
keep it on the right side.
| | 02:08 | If it's strictly numbers and you don't
have dollar signs and/or commas, maybe
| | 02:12 | it's still in General format.
| | 02:14 | Again, think out the issues of how you
want alignment to occur, but as a general
| | 02:18 | rule unless the numbers happened to be
ID numbers, keep them in their native
| | 02:23 | right-aligned state.
They look best like this.
| | 02:26 | Now, new in Excel 2007 and also in
Excel 2010, we have available already
| | 02:33 | without any kind of customizing
alignment buttons for aligning data in the top,
| | 02:38 | middle or bottom of cells.
| | 02:41 | And in this particular worksheet, the
upper-left corner is a graphic that more
| | 02:45 | or less depicts what we mean by these.
And this is pretty apparent once you try
| | 02:49 | one or two of these buttons.
| | 02:50 | This data of course says top or
left and you see the two icons that are
| | 02:54 | currently active in the
Alignment group on the Home tab here.
| | 02:58 | Top, Middle, and again
notice how the change here.
| | 03:00 | So the three lower alignment
buttons have to do with the left to right
| | 03:05 | alignment of data within cells.
The three upper buttons have to deal with the
| | 03:10 | top down alignment.
| | 03:12 | So as you might expect, as we click here,
we are still within the left to right.
| | 03:17 | Actually we call it middle this time.
Sometimes you see the words center and
| | 03:20 | middle are used interchangeably, but
you get the idea pretty quickly as you
| | 03:24 | compare these with those
different combinations that we have here.
| | 03:28 | And many times when you're considering
centering data in the top bottom cells,
| | 03:33 | it's pertinent when the
cell is taller than usual.
| | 03:36 | If we had, we just normal data here and
these rows where we have typical height,
| | 03:41 | obviously this graphic doesn't
make a lot of sense right now.
| | 03:44 | But do think of those situations where
perhaps you've got wrapping text and when
| | 03:47 | you do, then you want to consider
how the text appears within a cell.
| | 03:52 | The alignment buttons as we've seen
them being used here and prior in the data
| | 03:57 | here are easily accessible in the
Alignment group on the Home tab of the ribbon.
| | Collapse this transcript |
| Creating angled text variations for greater variety and readability| 00:00 | For simply adding visual appeal to a
worksheet or maybe to improve readability,
| | 00:04 | you might want to consider using angled text.
| | 00:07 | I'm about to highlight the months here
along with Total and Average and in the
| | 00:12 | Alignment group on the Home tab
the angle choices appear here.
| | 00:16 | Angle Counterclockwise, common choice.
I think perhaps more common than the one
| | 00:21 | below it, Angle Counterclockwise.
| | 00:23 | Here and there you simply have your choices,
and we can see how quickly this happens.
| | 00:27 | Now in the same worksheet and I've separated
it a bit, and you will see why in a minute.
| | 00:33 | Off to the right here in columns N
through Z here, we see some other data.
| | 00:39 | Here the text is aligned vertically.
| | 00:41 | You might want to consider this option as well.
| | 00:43 | Vertical alignment, and yet
it doesn't say vertical alignment.
| | 00:46 | It says Rotate Text Up and you
see its opposite, Rotate Text Down.
| | 00:50 | I doubt if you want to choose vertical
text because once people see this,
| | 00:54 | most people say, "Never mind, that's hard to read."
| | 00:57 | Maybe here and there you might find a good use
for that, but I don't see it very much myself.
| | 01:02 | So these are pretty obvious
choices once you get used to using them.
| | 01:05 | By the way, this has absolutely no
impact on the ability to sort the data and
| | 01:09 | manipulate it, but you'll notice
something else that's happened here and it was
| | 01:13 | already setup in this particular list
and I'm actually going to remove the
| | 01:15 | feature and then put it back.
| | 01:17 | In addition to the angled text here,
these cells have borders on them.
| | 01:22 | Now, I'm going to use a keystroke
shortcut to take off the borders.
| | 01:25 | It's Ctrl+Shift+Underscore. You can also do that
with right-clicking and going into Format Cells.
| | 01:31 | But, I think to a lot of people's minds this
is not as good looking as it had been before.
| | 01:37 | This is not as appealing as it had been
before and actually I could have done
| | 01:40 | that over here as well.
| | 01:41 | Once again Ctrl+Shift+_. So here
is how it looks without borders.
| | 01:45 | Now there are two kinds of borders
that will make it look I think for many
| | 01:49 | people more appealing.
| | 01:50 | Let's right-click, use Format Cells,
and simply put a perimeter outline
| | 01:55 | border on these cells.
| | 01:56 | Take a look at this.
| | 01:57 | Now that's not quite what we saw
before, but that could be what you want.
| | 02:01 | Perhaps better from some people's
perspective is with these cells highlighted,
| | 02:06 | right-click, Format Cells.
| | 02:09 | This time also choose Inside, so
that we see lines around all the cells.
| | 02:13 | Now, I'm going to press Ctrl+Z a
couple of times to remind you how this
| | 02:17 | did look, like that. Click outside
of it, to compare it with, and now I'm
| | 02:22 | pressing Ctrl+Y to repeat.
| | 02:24 | So again, we're not talking about a
major feature here in terms of the overall
| | 02:28 | impact of your worksheet, but just the
idea that slanting text-- and I think 45 edges
| | 02:33 | is the most common choice-- is easy to get to.
| | 02:36 | Keep in mind too that if you happen
to highlight the cells and choose the
| | 02:40 | Alignment dialog box launcher this
way, and go to the Alignment tab,
| | 02:44 | you can certainly change the angles by
dragging this or typing in a different entry here.
| | 02:50 | What we had done earlier with
vertical text could have been done this way.
| | 02:53 | Tilting the text, putting in that
other unusual way. This way as well too.
| | 02:57 | So you have these
capabilities available here as well.
| | 03:00 | I think it's faster from using the
existing toolbars and again it focuses on
| | 03:03 | the features that people are most
likely to use, the angled text of 45 degrees.
| | 03:08 | So easy features to get to.
| | 03:10 | It adds a visual appeal to some
worksheets and in some cases does
| | 03:14 | increase readability.
| | Collapse this transcript |
| Indenting data and dollar signs| 00:00 | In this worksheet called Indenting
you'll notice that in column A, the text
| | 00:04 | entries, rows 5, 6, 7 begin at
different locations here and potentially someone
| | 00:11 | could have put in leading spaces here.
| | 00:13 | But if you do attempt to display data
differently with spaces, you don't always
| | 00:17 | get the exact alignment that you want
and there are two buttons on the Alignment tab
| | 00:22 | and there are two buttons in the
Alignment group on the Home tab designed
| | 00:26 | specifically for indenting.
| | 00:29 | By the way, you will notice that there
are some keystroke shortcuts listed here.
| | 00:33 | I recommend that you not use them.
| | 00:35 | Depending upon the operating system
you might be pressing one of these
| | 00:39 | combinations here and you'll end up
switching to another application and
| | 00:43 | it will completely throw you sometimes.
| | 00:44 | So I recommend do not use these.
| | 00:46 | The buttons are easily accessible
and let's apply them in this particular
| | 00:50 | worksheet where it would
make most sense right now.
| | 00:53 | We can certainly read this data but
these are in a certain sense and we're
| | 00:58 | talking about outlining characteristics,
these words here would look better if
| | 01:03 | they were indented to the right.
| | 01:05 | Simply click this button,
increase the indent, there we go.
| | 01:09 | Maybe do it again. Or
maybe we want to go back one.
| | 01:12 | That's the other button to the left.
| | 01:13 | You may recognize these buttons
that also exist in Microsoft Word.
| | 01:16 | They are kind of funny looking but they
certainly do the job fast and easy and
| | 01:20 | they are designed primarily for text entries.
| | 01:23 | Recognize that up above this was already done.
| | 01:25 | These are indented maybe
more than they need to be.
| | 01:28 | So we will decrease the
indent in this way possibly.
| | 01:33 | Again, you can easily manipulate these,
move them back-and-forth a little bit to
| | 01:37 | have them display the way
that's most effective to you.
| | 01:40 | A side benefit of this, although not a
great one, is that it also controls the
| | 01:44 | placement of dollar signs, and to
accentuate this idea I'm going to make Column B
| | 01:49 | considerably wider than it
needs to be at least for the moment.
| | 01:52 | I see nothing wrong with this display at
all, but on the other hand if I want to
| | 01:55 | move the dollar sign inward a little
bit to the right, I will click the button
| | 02:00 | here for increasing the indent.
| | 02:02 | It simply pulls in the dollar sign a bit.
| | 02:04 | Now if you always wanted the dollar
sign right next to the data, then you
| | 02:09 | may very well know that you could
use Currency format here and so the
| | 02:13 | existing Accounting format.
| | 02:15 | But these buttons have the effect
here of moving the dollar sign, but the
| | 02:18 | primary purpose of indenting is simply
to move text over and by doing it by
| | 02:23 | using the buttons here, we don't have to
worry about alignment issues because it
| | 02:26 | aligns the data perfectly.
| | 02:28 | Again, just experiment with the two.
They are fast and easy to get to.
| | 02:31 | Increase the indent or decrease the indent.
| | Collapse this transcript |
| Simplifying title adjustments with the versatile Merge and Center commands| 00:00 | In this worksheet entitled MergeCenter
if you were about to print this or use it
| | 00:05 | perhaps in a presentation, it would make
sense to center the information that we
| | 00:09 | see in rows 1 and 2.
| | 00:11 | And rather than taking the information
which actually is in cell A1 and deciding
| | 00:15 | whether to move it to C1 or D1 and just
guessing as how it might look, instead
| | 00:20 | of doing that or trying to put in
leading spaces, why not simply select all the
| | 00:25 | cells in which we want this to
be centered along with the data?
| | 00:28 | And we might be interested in doing the
same thing with the row below this too.
| | 00:32 | So you might start by selecting both of these.
| | 00:35 | Now, the feature we're talking about is in the
Alignment group on the Home tab of the ribbon.
| | 00:41 | It's called Merge & Center.
| | 00:43 | However, if you try this feature, you
will get a warning suggesting based on what
| | 00:48 | I have just done that I'm not going to
get that data from row 2 placed properly.
| | 00:53 | It's going to merge these into one cell.
Keep the upper left-most data only.
| | 00:57 | So let's cancel this.
| | 00:59 | It looks as if we need to do these separately.
| | 01:01 | I'll do one of them and then come back
and show you a way to handle both at once.
| | 01:05 | Let's just focus first on this idea.
Highlight this, Merge & Center, there we are.
| | 01:09 | So what has happened?
| | 01:10 | Well, the most obvious thing that has
happened is that the data is centered
| | 01:14 | left-right within those cells.
| | 01:16 | Not so obvious at first
maybe is that this is one cell.
| | 01:21 | This is all part of cell A1 and there
is no B1 or C1 or D1, etcetera, in this
| | 01:27 | worksheet for the moment.
| | 01:28 | All the data is in A1.
| | 01:30 | We did a Merge & Center.
| | 01:32 | Now if we really did want to take
care of the second one again, it wouldn't
| | 01:34 | take us that long, but let me press
Ctrl+Z here and then select this group of cells,
| | 01:41 | letting go of the left mouse button, use
the Ctrl key, and then highlight the second row.
| | 01:48 | So it's two different selections, then
we can press Merge & Center. It takes care
| | 01:53 | of both of these at once.
| | 01:54 | And most of the time this
feature is used for centering titles.
| | 01:59 | It's fast and it's efficient and
nearly always the examples that you see are
| | 02:03 | horizontal, but you can use these in
a vertical way as well. And I wouldn't
| | 02:07 | suggest that we necessarily
must do that in this case.
| | 02:10 | But to give this portion of the
worksheet a different look, imagine if we could
| | 02:14 | put the word Expenses in Column K here.
| | 02:17 | So let's first move that over here.
| | 02:19 | I'll just drag it and get rid of these cells
here. Right-click, Delete, Shift cells up.
| | 02:27 | Let's rotate this, make it vertical,
but not the Vertical option under
| | 02:31 | Alignment but Rotate Text Up this way.
| | 02:34 | But then let's merge this and center
it across these cells. Merge & Center.
| | 02:40 | Make the column a little bit narrower,
and then the last thing to do here would
| | 02:44 | be to put this in the middle and
we're talking about vertical alignment.
| | 02:48 | Middle align this way, and possibly one
other choice too. Make that text bigger or
| | 02:53 | add another word or phrase.
| | 02:55 | Now again, we've got more crying needs
sometimes, but that has a certain look to it.
| | 02:59 | And maybe adding a background color
would be something you'd want to do too
| | 03:02 | and a border and so on.
| | 03:03 | All the different things we do.
| | 03:04 | So you can use it in that way too.
| | 03:05 | Again, a little showoff-y maybe but I
think the major use, the more prominent
| | 03:09 | use of this is what we used here in
rows 1 and 2, simply to center titles
| | 03:14 | across our worksheet.
| | Collapse this transcript |
| Controlling titles and headings with the Wrap Text and Shrink to Fit options| 00:00 | If you work with large list of data in
Excel, you're used to the concept that
| | 00:04 | titles should be in a single row
and they are here in this example.
| | 00:08 | But Column A is a lot wider than it
needs to be because the title is so wide.
| | 00:13 | You might consider putting the word
Employee above the two words Last Name.
| | 00:16 | A couple of ways to do this.
| | 00:18 | I'm going to right-click on cell A1 and
jump right into Format Cells and you'll
| | 00:24 | see the choice called Wrap Text.
| | 00:26 | Now sometimes you're going
to be disappointed with this.
| | 00:28 | You click OK and nothing really happened.
| | 00:31 | Well let's make the column
narrower, about there. Or there.
| | 00:33 | Still nothing happening.
| | 00:35 | Let's double-click the
boundary between row 1 and 2.
| | 00:37 | Now, we see what's happening.
| | 00:39 | Now that's fine and it works well many
times and sometimes you'll highlight a bunch
| | 00:43 | of cells to do that.
| | 00:44 | We could have done that here as well.
| | 00:46 | You can also do this manually, and
here you control where the break occurs.
| | 00:52 | Now in this case there are only two words.
| | 00:53 | So it's pretty obvious we
want the break between the two.
| | 00:56 | Another way to approach this, and you
could do this from the moment of initial
| | 00:59 | typing, is after typing the 2011.
| | 01:02 | So I'm going to double-click
in here right behind the 11.
| | 01:04 | I'm going to press Alt+Enter, and
there is a space in front of it and
| | 01:08 | press Delete and Enter.
| | 01:10 | So is that any better
than what we just did here?
| | 01:12 | Well, it's slightly faster. If you now double-
click the boundary here to adjust the width.
| | 01:17 | But when we apply the Command feature
here, the wrap doesn't always occur where
| | 01:22 | we want it to and that's why Alt+Enter
is going to be better here as well too.
| | 01:25 | If I want tax rate on the bottom and
2010 on top, I'm going to double-click
| | 01:29 | just behind the 2010.
| | 01:30 | By the way, you can also do
this up in the Formula bar as well.
| | 01:34 | I'm going to press Alt+Enter,
Delete, and make the entry that way.
| | 01:39 | Now if this is a new worksheet--
I'm going to quickly create a new one--
| | 01:42 | if you're creating titles and now
you're simply trying to put in from the
| | 01:47 | beginning text on top of text for example,
| | 01:49 | you just might type the data this way.
| | 01:51 | Employee, Alt+Enter, Name, if
that's what you want the title to be.
| | 01:55 | We're finished, move onto
the next cell, and so on.
| | 01:58 | Sometimes you'll see a row taller
than it needs to be as well too.
| | 02:02 | So I suggest you wait until
you get the other data in here.
| | 02:04 | An other example might be 2011, Alt+
Enter, Salary and maybe in this cell we want
| | 02:10 | to see 2011 Tax Rate and if the
entries below are going to be pretty narrow,
| | 02:14 | why not have a
couple of Alt+Enters here?
| | 02:16 | In other words, we'll put
the data on three lines.
| | 02:19 | So 2011, Alt+Enter, Tax, Alt+Enter, Rate, Enter.
| | 02:25 | See the data that way.
| | 02:27 | And a followup to this of course would
probably be for many people to align the
| | 02:31 | data either in the top, meaning top or
bottom or possibly center. This way.
| | 02:36 | So you can achieve the Wrap Text in
two different ways, either by typing
| | 02:40 | Alt+Enter or editing and using Alt+Enter,
or by using by way of the right mouse
| | 02:45 | button and Format Cells, the Wrap Text option.
| | 02:48 | Notice that even if you apply the
feature manually as I did in these three cells,
| | 02:52 | the Wrap Text Box here is checked.
| | 02:55 | Now another option, going back to the
previous worksheet Wrap Text, is instead of
| | 03:00 | using the Wrap Text-- I'm going to come back
in here Format Cells and uncheck the option.
| | 03:05 | Another potential option which may
work or may not depending upon what your
| | 03:09 | needs are is to actually shrink the text.
| | 03:12 | Now suppose you have decided and
you've looked up and down column A for a bit,
| | 03:16 | you want your column to be about that wide.
| | 03:18 | How can we shrink the text?
| | 03:21 | Right-click, Format Cells, and
there's a choice called Shrink to Fit.
| | 03:26 | This is not a great option,
but it gets the job done.
| | 03:29 | It will put the data that way.
| | 03:30 | Now I think in this example it's too small.
| | 03:33 | I'll make it a little bit
wider and you saw the text grow.
| | 03:36 | If you happen to click here and if
you weren't aware of this feature,
| | 03:39 | I think you would be thrown because
you would see in the Font group in the
| | 03:43 | Home tab, this says size 11.
| | 03:45 | You say, "Well, I want to make it bigger."
| | 03:47 | You make it 16 or something
and what's happening here?
| | 03:49 | Nothing, nothing at all.
| | 03:51 | I'm going to press Ctrl+Z here to undo this.
| | 03:54 | So it still says 11 but
nothing really has changed.
| | 03:57 | So not a great feature but you might
see it here and there and I think it does
| | 04:01 | have its potential uses, but be a
little bit careful with the fact that it
| | 04:04 | doesn't by looking in the Formula
bar describe what really is going on.
| | 04:08 | So right-click here. Format Cells.
| | 04:10 | That's called Shrink to Fit.
| | 04:12 | I'll uncheck the box, go back to here,
and either double-click to bring back
| | 04:17 | the information this way or possibly
reinstitute the wrap capability that we saw earlier.
| | Collapse this transcript |
|
|
4. Other Valuable Formatting Tools Using the multipurpose Format Cells dialog box and the pop-up mini-toolbar| 00:00 | Despite all the advantages of the
formatting tools being so readily available in
| | 00:05 | the ribbon, either in the Font group,
the Alignment group, the Number group,
| | 00:10 | there are still times when we need to
go behind the scenes and we stressed
| | 00:13 | repeatedly in this course the idea
that at any time when you press Ctrl+1,
| | 00:19 | you activate the Format Cells dialog box.
| | 00:22 | And all those various additional
choices on the Number tab, the Alignment tab,
| | 00:26 | the Font tab, the Border tab, Fill
tab, and Protection, most of these are
| | 00:32 | available in the ribbon with some exceptions.
| | 00:35 | Now as a reminder you can also activate
that toolbar strictly on the Font tab at
| | 00:41 | any time by pressing Ctrl+Shift+F.
That always activates the Format Cells dialog
| | 00:47 | box with the Font tab active, and
anytime we right-click a cell we see the
| | 00:53 | choice Format Cells as well.
| | 00:56 | In addition what we also see is the so-
called mini toolbar here, and as you get
| | 01:01 | more familiar with this and you see it
around and notice that in the example
| | 01:04 | here it's below the drop-down menu.
| | 01:07 | If I happen to right-click on a cell
toward the top of the worksheet right here,
| | 01:11 | if I am about to consider a formatting
change, maybe that formatting change is
| | 01:16 | on the mini toolbar.
| | 01:17 | Here we see it displayed above the menu.
| | 01:19 | So of course, here are those many,
many choices that we certainly can get to
| | 01:24 | from the ribbon up above, but
they are right close to the data.
| | 01:27 | Now you will also see this mini toolbar
be activated when you're editing a cell,
| | 01:31 | although not at first.
| | 01:32 | Maybe you want to make some
editing changes here in cell A10.
| | 01:37 | That's a text entry over
here on the left-hand side.
| | 01:39 | So I will double-click.
| | 01:41 | While I am editing, where is that mini toolbar?
| | 01:43 | Maybe I want this word for
to be red or blue or italic.
| | 01:48 | As I highlight, you can faintly see a
mini-mini toolbar, we might say, with
| | 01:52 | just some features here.
| | 01:54 | So if I want to make this red, there
is a Font Color, maybe I'll do that.
| | 01:58 | But you can see how the mini
toolbar gets activated this way too.
| | 02:01 | So the main purpose of this of course
is just to give us a few of those likely
| | 02:05 | choices we would be making here,
gathered from the various groups in the ribbon.
| | 02:10 | Activated when we are actually editing cells.
| | 02:12 | So maybe we will complete the
entry here and press Enter and move on.
| | 02:16 | So that's going to be active at different times.
| | 02:18 | Be on the lookout for it.
| | 02:19 | You might overlook it at times.
| | 02:21 | So anytime you need more depth than what
we are able to get off of existing groups
| | 02:27 | in the ribbon, either by choosing
Format Cells through the varying ways.
| | 02:31 | Format Cells by right-clicking or by
pressing Ctrl+1 or Ctrl+Shift+F to activate
| | 02:36 | the Font group there.
| | 02:37 | Different ways to activate formatting
with the information right at our fingertips.
| | Collapse this transcript |
| Applying different formatting styles within the same cell| 00:00 | Although Excel has numerous formatting
tools immediately available on the Home tab
| | 00:05 | in the Font group, the Alignment
group, and the Number group, and also in
| | 00:10 | Styles and Cells, sometimes it might
make sense if you have unusual formatting
| | 00:14 | needs to take advantage of the fact
that the Format Cells dialog box, which you
| | 00:19 | can activate in number of different ways,
does allow you to explore some of the
| | 00:23 | more unusual formatting
features that you might need.
| | 00:26 | I've selected the data here in cells B6
through I6 and a quick reminder of the
| | 00:31 | ways to get to Format Cells.
| | 00:33 | We can certainly right-click.
Go to Format Cells that way.
| | 00:36 | We can press Ctrl+1.
| | 00:39 | If you know that you must go to the
Font tab you can also press Ctrl+Shift+Tab instead,
| | 00:44 | and certainly on each of the
groups that we see in the ribbon for Font,
| | 00:49 | Alignment, we see those dialog box launchers.
| | 00:51 | So no matter how we get here, what's
the advantage of using this particular
| | 00:55 | dialog box over the buttons?
| | 00:57 | The advantage could be that when you
need to apply multiple formats, there are
| | 01:01 | pretty much all at your fingertips here.
| | 01:03 | In other words, you might have an
unusual number format that isn't available
| | 01:07 | from the ribbon. Maybe it's some
kind of a special number format that you
| | 01:10 | might want to use here.
| | 01:11 | You might have some special alignment
needs that are a little bit unusual that
| | 01:15 | are not found in the Alignment group.
And we've got font choices here too.
| | 01:19 | Maybe you want to use Bold and
Italic together, apply color, use some
| | 01:22 | special underlining.
| | 01:24 | In other words, we can approach a
variety of different formattings at the same
| | 01:29 | time while we are within this dialog box.
| | 01:32 | So there could be a tendency to overdo
this and do too many things at once, but
| | 01:37 | let's make a couple of changes
here just to emphasize the idea.
| | 01:40 | I'm going to change this particular
design here to have negatives, if they
| | 01:45 | appear to have a leading minus.
I won't make any alignment changes here.
| | 01:49 | Let's change the font to
do Bold and Italic both.
| | 01:52 | Maybe we will put a border on these cells.
| | 01:54 | How about an outer border, an inner
border, just use maybe an unusual order type
| | 02:01 | this way? There we go.
| | 02:03 | Fill, we might want to use a different
color here. Possibly a pattern with it.
| | 02:07 | In other words, we are accomplishing a
number of different objectives at the same time.
| | 02:11 | A lot of these features we can't get
too quickly from the buttons that are
| | 02:16 | found in the ribbon.
| | 02:17 | When we are finally finished, we click OK.
| | 02:19 | I wouldn't say that's a great
format. But another issue comes up too.
| | 02:23 | If we need to apply these same
formatting features in a different location here,
| | 02:28 | we could select those cells and simply
press F4 or Ctrl+Y and all the actions
| | 02:33 | that were taken within that dialog
box could be achieved at the same time.
| | 02:37 | So again, ignoring perhaps the
unattractive look at this is the idea that
| | 02:41 | sometimes when you're performing
multiple formatting actions, it might be more
| | 02:45 | efficient to use that Format Cells dialog box.
| | Collapse this transcript |
| Copying formats quickly with dragging techniques and the Format Painter| 00:00 | Excel has a number of techniques for
quickly copying formats. Not content, but formats.
| | 00:06 | A couple of examples in
this worksheet come to mind.
| | 00:08 | One easy way, certainly is to copy by
first selecting the data in question.
| | 00:14 | We want to copy the format of all
these cells to the lower ones we see here.
| | 00:18 | One standard technique is to
simply right-click and copy this.
| | 00:21 | Now you could also press Ctrl+C and
rather than highlighting all of this data here,
| | 00:25 | we need to only highlight the
cell on the upper left-hand corner of the
| | 00:29 | receiving area, right-click, and then
under Paste Special there is an option
| | 00:34 | here for copying just the formatting.
| | 00:37 | In the preview we do see it immediately.
| | 00:39 | We will simply choose Formatting.
| | 00:41 | So we've copied the formats, not the content.
| | 00:43 | Content is obviously different in these cases.
| | 00:46 | Now a couple of other ideas too
though, and occasionally these are going to
| | 00:49 | surprise you because they
don't do always what you think.
| | 00:52 | I'm going to-- Let's say on purpose.
Maybe we're having a meeting. I want this
| | 00:56 | number to stand out a little bit.
| | 00:57 | So maybe I will apply a different
color background, something like that, just
| | 01:01 | a slight difference.
| | 01:02 | Now sometimes you lose your train of
thought or maybe a few days pass, you're
| | 01:07 | thinking about something else,
you say, "I like the look of that.
| | 01:09 | I think I will copy that format into Column A."
| | 01:12 | Well, the primary visual feature that
you might be thinking of is the color.
| | 01:17 | So let's right-click and copy this
and then highlight these cells and
| | 01:21 | right-click and paste just the formats
like we did before, do it that way, and
| | 01:26 | that seems to work just fine.
| | 01:28 | You say, "Well, I will do that
over in Column J as well too."
| | 01:30 | As long as these so-called marquee
lights are invisible we can continue to paste
| | 01:34 | repeatedly and we are going to
highlight these days over here.
| | 01:38 | Maybe we will do the whole column anyway.
| | 01:39 | Right-click, Paste Special, and we
will be doing a formatting as well.
| | 01:43 | Of course, the preview might stop you,
but if you go ahead and click, I think
| | 01:47 | you would see what's happened here.
| | 01:48 | We have copied the color for sure,
but formatting really does mean not just colors,
| | 01:54 | but all those various features
that we see on the Home tab, not only
| | 01:59 | fonts and those characteristics, but
also alignment tools and formatting and
| | 02:03 | numeric formatting and so on.
| | 02:05 | So a lot has been copied here.
| | 02:07 | I'm going to press Ctrl+Z and
reconsider different ways to do this, but if it's
| | 02:12 | simply color you might as well do this manually.
| | 02:14 | In other words figure out the color
here and then apply that color to the
| | 02:18 | other cells in question.
| | 02:20 | Now another technique
for copying is pretty fast.
| | 02:22 | It's called the Format Painter.
| | 02:24 | How about these cells here?
| | 02:25 | They look pretty good.
| | 02:26 | Why don't we click the Format Painter and
then simply click cell B10? Good enough.
| | 02:32 | It's fast. It's easy.
| | 02:33 | Once again many times when we are
copying we are using a cluster of cells or a
| | 02:38 | group of cells and the
receiving area is simply one cell.
| | 02:42 | We might change all of these displayed
numbers here to include dollar signs.
| | 02:47 | Now I could've done the group down below.
| | 02:50 | Another way to copy formats is to
drag with the right mouse button.
| | 02:54 | So I am going to drag this entire
list here downward with the right mouse
| | 02:58 | button, pop it on top of these numbers,
and as I let go, there is a menu,
| | 03:03 | Copy Here as Formats Only.
| | 03:06 | So a number of different techniques for copying.
| | 03:09 | And again, making this a little bit contrived
perhaps, but this cell has a certain color.
| | 03:14 | I'm also going to make it Bold and Italic,
because I want it to stand out at the next meeting.
| | 03:19 | I will maybe double-click
here to make the column wider.
| | 03:22 | Let's change the font as
well. Make it blue or something.
| | 03:25 | If we'd like to copy this to a few
other selected cells. How might we do it?
| | 03:31 | Double-click Format Painter and then maybe
click there, drag across these two cells.
| | 03:37 | Obviously, we've got some
column width adjustments to make.
| | 03:39 | Drag across those two and we can do this
indefinitely until we press the Escape key.
| | 03:44 | If you are familiar with this
in Microsoft Word, it works the same way.
| | 03:47 | So by double-clicking Format Painter we
are copying the cell that was active at
| | 03:53 | that time into other locations, and
we are only copying the format of course.
| | 03:57 | Eventually, when you're finished
copying the format, you want to press Escape
| | 04:01 | and then the examples here we will simply
drag across the top, double-click, and
| | 04:05 | we've made the quick adjustment.
| | 04:07 | So we are able to copy a format to
multiple nonadjacent locations by
| | 04:11 | double-clicking on the Format Painter.
| | 04:13 | So quite a few different options
for copying formats within and across
| | 04:18 | different worksheets.
| | Collapse this transcript |
| Formatting text from Word or the web with the Justify feature| 00:00 | If you've ever copied information
from Microsoft Word or perhaps off the
| | 00:04 | Internet, you'd sometimes run into a
situation where you've got the sentence or
| | 00:09 | a lot of text for example,
like we see here in C2.
| | 00:13 | It's a description of the numbers
below and it seems to go on and on and on.
| | 00:17 | And rather than trying to copy this
into cells below this and then chopping it
| | 00:21 | off and editing it, there is a
feature in Excel known as Justify.
| | 00:25 | Let's imagine that this text here-- and
it's about 250 characters or so, actually
| | 00:30 | a little bit more than that.
| | 00:31 | And a little tip here too by the way.
When you look in the Formula Bar and
| | 00:34 | you sense that there's more text and
you can't read all it, if you simply put
| | 00:38 | the mouse on the lower part of the
Formula Bar, you can drag this downward
| | 00:42 | and see more text this way.
| | 00:44 | Another way to do this too is also on
the right-hand side, you'll see a drop
| | 00:47 | arrow there. Possibly use the up and
down arrows to see different lines, but not
| | 00:52 | at the same time, or use the drop arrow.
| | 00:54 | Now, it doesn't always open to
exactly the height that you wanted to, so
| | 00:58 | possibly you can drag it around there.
| | 00:59 | But the real focus of what we are
talking about here is the ability to rewrap
| | 01:03 | this text without taking much time.
| | 01:05 | Let's imagine that you want this text to
fit into this many columns wide, right here.
| | 01:11 | Let's just highlight this portion of it.
| | 01:13 | Now it's worth noting that
the actual data is in cell C2.
| | 01:18 | It really isn't in D2, E2, etcetera. It's in C2.
| | 01:22 | So you must of course highlight the
C2 and then other cells to the right and
| | 01:28 | you will want to experiment with this.
| | 01:30 | The feature we are about
to use is on the Home tab.
| | 01:33 | It's in the Editing group and
it's called Fill and then Justify.
| | 01:38 | Nearly always you will get this message.
| | 01:41 | Text will extend below selected range.
| | 01:43 | Click OK and we see the rewrapping.
| | 01:46 | Now it looks like it's running into
the data below. That doesn't look so good.
| | 01:50 | Let's do an Undo, Ctrl+Z. Maybe what we
should do is take this text and wrap it
| | 01:55 | across this many cells. Fill > Justify.
| | 01:59 | The same message. Now it seems to fit okay.
| | 02:02 | Now notice something else. It looks
like it clipped off the end of the sentence.
| | 02:06 | This only handles about 255 characters.
| | 02:09 | Please check with Harvey Morgan in the...
| | 02:12 | We are not sure what it
means. Let's do an undo.
| | 02:14 | Ctrl+Z. And you are can see there's
a real limitation to this capability.
| | 02:19 | So if the text is longer than what we
are seeing here, "in the Accounting group
| | 02:23 | for more background," we could
probably remember that and add it to the text
| | 02:26 | eventually, but do recognize that aspect of it.
| | 02:28 | Now suppose this entire set of data here,
we just put below right here for the moment,
| | 02:34 | you might say, "Well, I think
this would look better if we rewrap it
| | 02:38 | across this number of cells."
| | 02:39 | Try it that way. Fill >
Justify, do it that way, same idea.
| | 02:43 | In other words, what I am
suggesting here is you might want to do
| | 02:45 | experiment with this.
| | 02:46 | In this case, here the data
is now in C13, C14, and C15.
| | 02:52 | You can see that up in the Formula Bar and
once again we have lost some data in the process.
| | 02:56 | So though it's got some limitations,
I think you can see how you might use this.
| | 03:00 | The other thing you might do here too,
you might for example edit this last line.
| | 03:04 | I am just going to take off everything after
Harvey Morgan here. Put in a Period and Enter.
| | 03:10 | Sometimes you say, "Well, I've got this
data here. Why don't we rewrap this into
| | 03:15 | say this many cells?"
| | 03:16 | Let's try it this way. Fill > Justify.
| | 03:19 | Actually, I said rewrap
and that's not the exact term.
| | 03:21 | It's called Justify, but the concept of
rewrapping is there. Extend below the range.
| | 03:27 | So it's something worth experimenting
with and you see the different results here.
| | 03:30 | It's certainly is a lot easier than
copying and pasting and editing and
| | 03:34 | sometimes you'd spend way too much
time realigning this data in the way that
| | 03:38 | it's going to look best for you.
| | 03:40 | It's called Fill > Justify.
| | Collapse this transcript |
| Adding a background image to a worksheet| 00:00 | If you would like to insert a background
image on a worksheet-- It could be a photograph.
| | 00:04 | It could be a company logo.
| | 00:06 | You might want to do this for display.
| | 00:08 | You might want to do this for
presentational reasons. It won't print.
| | 00:11 | And your first place to look possibly
would be the Insert tab in the ribbon but
| | 00:15 | it's not to be found there.
| | 00:17 | It's on the Page Layout tab. Background.
| | 00:21 | Now, here's what a photograph might
look like and I think you'll see this
| | 00:24 | probably is not the greatest choice.
| | 00:26 | Nice picture and all of that, but
can you see the data all the time?
| | 00:30 | So, only certain kinds of
photographs are going to work here.
| | 00:33 | I am going to zoom back a little
bit here too and scroll around.
| | 00:35 | Notice that the picture starts to repeat
after a while too and there's a break right there.
| | 00:40 | If you want to use this, you might want
to experiment by zooming and also seeing
| | 00:44 | where that break occurs.
| | 00:46 | Let's delete the background.
| | 00:47 | It's the reverse button.
This is a toggle button.
| | 00:49 | Now Delete Background.
| | 00:51 | Try something that might be a little bit
more appropriate in business type environment.
| | 00:54 | Hit this again. Here's a company logo.
| | 00:57 | Now I think you're probably right if
you were assuming this isn't going to look
| | 01:00 | so good either, but let's give
this a shot and see how that looks.
| | 01:03 | Same idea in the sense that is too
obtrusive, but we've got other logos.
| | 01:07 | Let's delete this. Try
another one on these lines.
| | 01:10 | This has a lot of white space in it,
but once again and I think you can see
| | 01:13 | here, it probably overwhelms the numbers.
| | 01:16 | You can't see it that well.
| | 01:17 | How about a more subtle background?
| | 01:21 | This kind of a logo where the picture
actually is gray, dark gray, and now
| | 01:25 | perhaps we've got something that's reasonable.
| | 01:27 | Again, this is strictly visual just
for display reasons. It will not print.
| | 01:31 | It adds a little flair
perhaps to this worksheet.
| | 01:34 | If you are making a
presentation, people might understand.
| | 01:36 | You might also, when you do this too,
go to the View tab and turn off the Gridlines.
| | 01:40 | That might help a little bit.
| | 01:42 | But again, personal choice there.
| | 01:43 | It's easy to add, easy to take
off, found on the Page Layout tab.
| | 01:47 | Delete the background and of course
just add the background from the
| | 01:50 | beginning as you choose.
| | Collapse this transcript |
|
|
5. Row, Column, and Sheet Adjustments Adjusting single and multiple row heights and column widths without commands| 00:00 | Adjusting column widths and row
heights is something that most Excel users
| | 00:04 | are doing constantly.
| | 00:06 | There are some commands available in the
ribbon in the Cells group under Format,
| | 00:10 | but there are some faster techniques.
| | 00:13 | Now many Excel users will click the
column, then put the mouse on the boundary
| | 00:17 | between that column and the next
one to the right and drag this.
| | 00:20 | Now this is a little bit of guesswork,
not wrong really, but getting the
| | 00:23 | habit of double-clicking.
| | 00:26 | That, by the way, means best fit and
rather than confronting just one column
| | 00:30 | at a time, in other words Column B has got a
problem as well, maybe Column A is too wide.
| | 00:35 | We can drag across columns.
| | 00:37 | This is one technique.
| | 00:38 | Maybe we want to adjust Columns A
and B, just those for the moment.
| | 00:42 | How can we adjust these quickly and easily?
| | 00:45 | Rather than dragging because they
might be of different width,
| | 00:48 | let's simply double-click either here on the
rightmost boundary or perhaps here. Double-click.
| | 00:56 | Both columns are now wide enough to handle
all the information that's in those columns.
| | 01:00 | No matter how much data is in the columns,
in each case here the column is wide
| | 01:05 | enough to display all the information.
| | 01:07 | If on purpose here, what I am going to
do is press Ctrl+Z to undo this, let's go
| | 01:13 | back to the previous display
too. I'll press Ctrl+Z again.
| | 01:17 | We now have a series of
columns of not the appropriate width.
| | 01:19 | We might as well click in the upper-
left corner and then double-click any of
| | 01:23 | these boundaries. It doesn't make any
difference which one, and all columns are now
| | 01:27 | the appropriate width.
| | 01:28 | Most Excel users quickly realize you
can adjust column widths at any time.
| | 01:33 | Although it's less likely to want to
adjust the height of a row, for titles
| | 01:38 | sometimes you might do this, even
though Excel pretty much handles this on its
| | 01:43 | own if you change the font size.
| | 01:45 | Now you might have your own needs and
all I'm doing here is simply putting the
| | 01:49 | mouse at the bottom of row one, clicking and
dragging to change the height here. Easy and fast.
| | 01:55 | When we double-click just as we did with
columns, it makes this be a best fit.
| | 01:59 | Most of the time, however, when you
do change a font size, I want to make
| | 02:03 | this be an 18, 16, whatever, you can see how
the row height is adjusting automatically.
| | 02:08 | So there's less of a need to adjust row heights.
| | 02:11 | When we do, we can
certainly do that as well too.
| | 02:14 | Another technique here too could be
that if you need to adjust the row height
| | 02:19 | of all these rows at once. Now there
is data off to the right. Let's ignore
| | 02:23 | that for the moment.
| | 02:24 | Perhaps that belongs on a different
worksheet anyway, but by clicking the upper
| | 02:29 | left-hand corner and then dragging any
of the row boundaries between the row
| | 02:33 | numbers, maybe we want to
make these all a bit taller.
| | 02:36 | So we've made all of the rows
taller, maybe for printing purposes, or
| | 02:39 | double-click will bring this back again.
| | 02:41 | So there are quick easy ways to make
the adjustments here for row height and
| | 02:45 | column width. Simply by double-
clicking. Even though you can drag,
| | 02:50 | double-clicking is going to be faster.
| | 02:52 | Off to the right here another idea too.
Let's suppose that we want to make an
| | 02:57 | adjustment here and we want to make sure
that these columns are all of the same width.
| | 03:02 | Now double-clicking is likely to do this for us,
| | 03:05 | as I double-click here, but what if the
numbers are a bit different in scope and
| | 03:09 | maybe this June number has reached to 1000.
| | 03:12 | So I am going to double-click here and
change the 45 into 100 so that becomes 1000.
| | 03:18 | We are going to press Enter and
you see now the column is wider and
| | 03:22 | nothing wrong with that.
| | 03:24 | Do note too if you put the mouse on
the edge here, this one is 70 pixels wide.
| | 03:30 | This one is 60 pixels wide.
| | 03:32 | Now most of us don't care, but sometimes you do.
| | 03:35 | What if you want all of these
columns to be exactly the same width?
| | 03:39 | Select all of them and then it doesn't
make any difference whether you point
| | 03:43 | here or here or here, but if we
simply drag it to a certain width, whatever
| | 03:49 | we might want and let go, all of these
will now be the same width. If that's the need.
| | 03:55 | Once again, if we would like to make all of
these the same width, we'll drag this way.
| | 03:59 | If we want every column to be just wide
enough to handle the widest entry, we'll
| | 04:04 | double-click one of these boundaries.
| | 04:06 | So quick, easy ways to make column
and row adjustments without commands.
| | Collapse this transcript |
| Working with rows, columns, and sheets| 00:00 | Inserting and deleting rows and columns
is another basic feature in Excel and
| | 00:04 | yet sometimes you might be spending
more time than is necessary if you use in
| | 00:09 | the Cells group the Insert
command or the Delete command.
| | 00:12 | Again, nothing really wrong with these,
but there are some faster techniques.
| | 00:16 | Let's imagine that in this particular
worksheet we need a new column to the
| | 00:20 | left of Column C. The fastest way to do
this is simply to right-click on Column C
| | 00:25 | and choose Insert to insert a new
column to the left of the selected column.
| | 00:32 | And similarly with rows,
I need a new row above row 3.
| | 00:35 | I am going to right-click on
the 3 and then choose Insert.
| | 00:41 | Now in the latter case here,
I am going to scroll rightward.
| | 00:44 | You probably haven't seen this
particular worksheet before, but I have and it's
| | 00:48 | up to me to know that what I just did
probably wasn't the greatest idea here.
| | 00:52 | And you can see what's happened.
| | 00:53 | I inserted a new row in the data to the right.
| | 00:56 | Now worst things could have happened
and nothing really got deleted here, but
| | 01:00 | that's probably not what one would want here.
| | 01:02 | I am going to press Ctrl+Z to reminder
us that sometimes we do want to insert
| | 01:07 | cells and by selecting these cells and
then right-clicking within the selected area,
| | 01:14 | we can choose Insert.
| | 01:16 | This time Insert has three dots behind it.
| | 01:19 | So the appropriate action to take in
this case, if we truly wanted to add a new
| | 01:22 | record and not disrupt the data to the right,
would be to Shift cells down. Click OK.
| | 01:30 | Then nothing to the right has been changed.
| | 01:32 | Now, what if I am not paying attention here and
I've forgot that I have got data to the right?
| | 01:38 | You can see the implications of
what's going to happen if I delete row 3.
| | 01:42 | But I'm just not paying attention.
| | 01:44 | I am going to right-click
on row 3 and choose Delete.
| | 01:48 | What kind of warning do I get from Excel?
| | 01:51 | Nothing at all. And maybe too late I
will discover this problem over here.
| | 01:57 | Now you could say well, that data
shouldn't have been on the worksheet anyway.
| | 02:01 | Well, it is and sometimes we have our
needs and we forget where the data resides.
| | 02:05 | So deleting a row is something we
want to at least think out and scope out
| | 02:10 | with any given worksheet.
| | 02:11 | I am going to press Ctrl+Z to undo this.
| | 02:15 | So the reverse of what we had done earlier
is to select these cells and right-click.
| | 02:21 | By the way, when you're doing this
too make sure you right-click within
| | 02:24 | the selected cells.
| | 02:26 | If we right-click on row number 3, the
Delete choice here doesn't have three dots.
| | 02:31 | It simply deletes the entire row.
| | 02:34 | So, once we have selected these, do
the right-clicking within the cells
| | 02:38 | somewhere and this time we
see Delete with three dots.
| | 02:42 | The action to be taken this time would
be to shift the cells below it upward.
| | 02:46 | And we see how quickly that happens as well.
| | 02:49 | Now, we might want to get rid of the
Column C, maybe we changed our mind about
| | 02:52 | the need for it at all.
| | 02:54 | A keystroke way, not necessarily better,
because it does means switching from
| | 02:57 | the mouse to the keyboard.
| | 02:58 | But if we have selected Column C,
we can get rid of this column with
| | 03:02 | Ctrl+Minus and it's gone.
| | 03:05 | Similarly, although no better than
what we have done earlier with the right
| | 03:08 | mouse button, if we need to add a new
row down here-- And there is no data to
| | 03:12 | the right down here. But after we have
clicked row 25, Ctrl+Plus will insert a
| | 03:18 | new row above that row.
| | 03:20 | So, different techniques here for
inserting and deleting rows and columns.
| | 03:24 | Now, we also from time to time might
need a new worksheet within the workbook.
| | 03:31 | And anytime we press Shift+F11.
| | 03:34 | We will get a new worksheet to
the left of the current worksheet.
| | 03:38 | So as you look at the bottom of the
screen, we are currently using the
| | 03:40 | sheet InsDelRowCol.
| | 03:43 | I am going to press Shift+F11.
| | 03:45 | A new sheet appears to the left of that.
| | 03:48 | Now in Excel 2007 and 2010 a new button
is on the right-hand side. Or let's say
| | 03:55 | it follows the very last
worksheet in the workbook.
| | 03:59 | And there you'll see an icon.
| | 04:00 | When you need to insert a worksheet
after the last sheet, just click here and
| | 04:05 | we are about to see a new sheet after
the sheet called Form. There it is.
| | 04:10 | So, different ways of inserting sheets.
| | 04:12 | Prior to that as we go back to this
data, techniques for inserting and
| | 04:16 | deleting columns and rows.
| | 04:17 | But let's also point out that
you might from time to time need to
| | 04:21 | insert multiple rows.
| | 04:23 | Now this might seem a little
contrived here, but I want to put a new
| | 04:26 | record above row 16.
| | 04:28 | I also want to do this above row 21.
| | 04:30 | So I am holding down the
Ctrl key to select row 21.
| | 04:34 | I might also need a new record above row 23.
| | 04:38 | Once again the Ctrl key is held down.
| | 04:40 | I am going to I click row 23.
| | 04:42 | I want a new row above each of these.
| | 04:44 | So I will right-click and Insert and
we get an empty row above each of these.
| | 04:49 | Similarly, in a different situation,
if you're trying to delete multiple rows,
| | 04:53 | use the Ctrl key to select multiple
rows and then right-click and Delete.
| | 05:01 | If we need two columns between columns
A and B, we can select columns B and C,
| | 05:08 | and then right-click, Insert, and we
get two columns to the left and of course
| | 05:14 | the other data gets shoved off to the right.
| | 05:16 | So, fast easy ways without going
into long menu sequences to insert and
| | 05:22 | delete columns and rows.
| | Collapse this transcript |
| Working with the Hide/Unhide options| 00:00 | For any number of different reasons,
you may want to hide one or more columns
| | 00:04 | within a worksheet or rows.
| | 00:07 | A typical example could be you are
ready to print this worksheet and you're
| | 00:11 | just a little bit cautious about
printing Social Security numbers or depending
| | 00:15 | upon where the printer might be located,
| | 00:17 | salaries, job ratings. That's
confidential type of information.
| | 00:20 | So maybe some people need this list,
but they don't need to see that
| | 00:24 | information on paper.
| | 00:26 | So we might hide a single or more columns.
| | 00:28 | If we only want to hide a single
column, the fastest way is simply to
| | 00:32 | right-click a column letter.
| | 00:34 | For example Column D and hide the
column and there will be no gaping hole in the
| | 00:39 | print preview or on the paper as well.
| | 00:42 | If we want to hide multiple columns,
I will undo this, Ctrl+Z. We might click
| | 00:47 | column D and then using the Ctrl key,
select Column J, possibly Column F,
| | 00:55 | possibly Column K, as many as we want.
| | 00:57 | Right-click and Hide.
| | 00:59 | The Print Preview, which you can get
too quickly with Ctrl+F2, reveals that the
| | 01:04 | information that we have hidden will not
appear on the printer output. Fast and easy.
| | 01:09 | It's a good idea too when you open a
workbook that you haven't seen in a while
| | 01:13 | or maybe you have never seen, always
be to looking at those column letters to
| | 01:16 | recognize that a column or two might be hidden.
| | 01:20 | If your first thought here is Column
D is hidden, simply drag across the
| | 01:25 | surrounding columns and take one of two actions.
| | 01:28 | Right-click and Unhide.
| | 01:30 | I am going to press Ctrl+Z to undo.
Or simply double-click the boundary between
| | 01:35 | the two, right here.
| | 01:36 | So, we can certainly bring
back the data to that way.
| | 01:40 | Pressing Ctrl+Z again.
| | 01:42 | If we see that there are many columns
hidden and we want to bring them all back,
| | 01:46 | simply click in the upper left corner
and then take either of those two actions.
| | 01:50 | I am just going to double-click
between A and B on the boundary and all the
| | 01:54 | hidden columns have been returned.
| | 01:56 | Similarly with rows. Maybe this person
has left the organization, but we want to
| | 01:59 | leave the name here for bit and maybe
there's another person down here as well,
| | 02:03 | using the Ctrl key and then right-click, Hide.
| | 02:07 | Once again, we don't see the information there.
| | 02:09 | Now printing isn't the only reason that
we want to hide, but it's a major one.
| | 02:13 | And at other times simply we
want to see more data here.
| | 02:17 | It's going to be simpler and easier for
us to not deal with this data that often.
| | 02:22 | We will simply hide these columns and
focus on the Status and the Hire Date and
| | 02:27 | the other columns here
that are more important to us.
| | 02:31 | So, you have all kinds of
different reasons for wanting to hide and
| | 02:35 | unhide columns and rows.
| | 02:37 | Now, you can also hide sheets.
| | 02:40 | This particular workbook has some other
sheets available and let's imagine that
| | 02:44 | I do need this data here for
SalesByDateTime, but I don't use it very often.
| | 02:50 | I don't use the Arrays very often nor the
Form sheet, but yet I want them to stay here.
| | 02:55 | So either right-clicking on one of these at
a time, we can choose Hide. You can do that.
| | 03:01 | I've hidden one.
| | 03:02 | We don't see that anymore, the one called Form.
| | 03:04 | If you want to hide more than one,
you can certainly use the Ctrl key to
| | 03:08 | select different sheets.
| | 03:11 | Or if it's a consecutive series
sheets, you can use the Shift key.
| | 03:14 | So, I want to hide this Array
sheet as well as the SalesByDateTime.
| | 03:19 | So I am holding down the Ctrl key.
| | 03:21 | I am going to click on the other sheet,
SalesByDateTime, and then right-click,
| | 03:26 | Hide, and we will hide two sheets at once.
| | 03:31 | Now we have only three sheets visible.
| | 03:33 | Now, at a glance, when you are looking
at a workbook, you don't necessarily know
| | 03:38 | unless you are the person who had done
the hiding whether there are any hidden
| | 03:42 | sheets or not, unless or until
you right-click on any sheet tab.
| | 03:47 | If Unhide is selectable,
then there are hidden sheets.
| | 03:51 | Now, even though we can hide
multiple sheets at once, we can only unhide
| | 03:55 | one sheet at a time.
| | 03:57 | So, I've got three hidden sheets here.
| | 03:59 | If I want to bring them all back,
we just got to do them one at a time, if
| | 04:02 | that's what we need to do.
| | 04:03 | Sometimes of course, we only
want to bring back one of them.
| | 04:06 | We can certainly do that too.
| | 04:08 | Right-click > Unhide. There we go.
| | 04:11 | Next time we right-click, Unhide is not
selectable because we don't have any hidden.
| | 04:16 | I don't think too many people use
this feature and is not critical for most people,
| | 04:20 | but it's certainly as handy at times.
| | 04:21 | Sometimes it's just clears the air.
| | 04:23 | Now you want a simplify view of the
number of sheets that you have, the ones you
| | 04:27 | are most focused on, hide
the ones you don't really need.
| | 04:30 | So it's fast and easy.
| | 04:31 | It should be something that you
always check out when you're opening an
| | 04:34 | unfamiliar workbook.
| | Collapse this transcript |
|
|
6. Displaying Data as an Outline Setting up data for outlining| 00:00 | Excel has an outlining capability that
escapes the notice of many Excel users.
| | 00:05 | It gives us the ability to
quickly collapse and expand the display.
| | 00:10 | This particular budget has tons of
detail in it, perhaps too much detail for a
| | 00:14 | coherent presentation.
| | 00:16 | It might make a lot of sense
here to show just the quarters.
| | 00:20 | Now you can do this manually of course,
and you could set it up ahead of time
| | 00:23 | simply by dragging across columns B,
C, and D using the Ctrl key. Drag across
| | 00:29 | these columns and these and these.
| | 00:33 | Right-click and hide those columns and
sure enough, we'd see this information.
| | 00:39 | However, what we don't have now is
flexibility to quickly unhide and
| | 00:43 | particularly when we are making a
presentation, we don't necessarily want our
| | 00:47 | audience to see all the details, all
the commands that we might use to expand
| | 00:51 | and collapse this particular display.
| | 00:53 | We might also want to collapse some
of the display here vertically as well.
| | 00:58 | We don't always necessarily want to
see all the expense items here.
| | 01:02 | Maybe just the total expenses.
| | 01:05 | We have to need some times to display
just the portion of this and we want
| | 01:08 | a quick ability at our fingertips to do
this without using standard Excel commands.
| | 01:13 | Now I'm going to undo what I just did
here, go back to the original display, and
| | 01:18 | suggest that in this worksheet where
there are formulas in Column E, for
| | 01:23 | example here, that tabulate data from the left.
| | 01:27 | Now they don't have to be Sum,
although typically they are.
| | 01:29 | These are adding up the totals.
| | 01:32 | With these present here and also with
certain formulas. For example in Row 8,
| | 01:37 | we are adding data from above.
| | 01:40 | In Row 14 we are doing the same thing.
| | 01:42 | In Row 15 we are subtracting them.
| | 01:44 | The outlining capability in Excel will
recognize these kinds of formulas and
| | 01:49 | allow us to quickly set up an outline.
| | 01:52 | Now a smaller version of this kind
of data is on a separate sheet here
| | 01:56 | called OutlineData2.
| | 01:58 | Here too we have that same
characteristic use of formulas in certain columns.
| | 02:03 | Here for example, tabulating data
from the left. Same thing over here.
| | 02:07 | Although the need here wouldn't be
quite as obvious, it's a good starting point
| | 02:11 | for some people if you want to
experiment with the outlining capability.
| | 02:15 | The third possibility is
done in a different way.
| | 02:18 | This is a rather large list.
| | 02:20 | Some 700 rows or so.
| | 02:21 | I will press Ctrl+Down Arrow, press
Ctrl+Up Arrow, and in this data you will
| | 02:25 | notice that it's sorted by department.
| | 02:27 | What if we could insert subtotal rows here?
| | 02:29 | Well, we can and I am going to do it
very fast simply by clicking on the
| | 02:33 | Subtotal button in the
Outline group on the Data tab.
| | 02:37 | Every time there's a change in the
department here I want to have totals for
| | 02:42 | Number of Contacts, the 2010 Sales,
the 2011 Sales, Number of Years here, and
| | 02:47 | I'd like to have after each
department some summaries.
| | 02:51 | Click OK and we do that.
| | 02:53 | But what we have set up here is the outlining
capability to quickly expand and collapse this.
| | 02:58 | I want to click the two.
| | 02:59 | We've collapsed this to show only the subtotals.
| | 03:02 | Now we are going to be doing the
same things here and here as well.
| | 03:06 | In this particular worksheet, however,
without using the Subtotal command, on the
| | 03:11 | Data tab we have a choice called Group.
| | 03:14 | It's also in the Outline group.
| | 03:16 | Group > Auto Outline.
| | 03:18 | Now you can do this manually,
but I don't recommend it.
| | 03:20 | I think Auto Outline is faster and better.
| | 03:22 | Look what happens here.
| | 03:23 | In other words, even though we are not
clear what the symbols might mean,
| | 03:27 | we recognize that Excel has figured out
that these formulas tabulate data from the
| | 03:32 | left and we see the 2 up here.
| | 03:34 | I am going to click it to collapse the
display to show only the quarterly totals.
| | 03:38 | In that initial set of data in the
OutlineData sheet, a more complex use of it
| | 03:43 | here too in the Outline group on the
ribbon. Data > Group > Auto Outline.
| | 03:49 | And here too we will collapse this display.
| | 03:50 | I am going to click the 2 that we see up here.
| | 03:52 | These are outlining symbols.
| | 03:54 | So we can easily set up data
and this is not a rigged example.
| | 03:58 | In fact, what you might even try and
I've tried this on an empty worksheet, and
| | 04:01 | let's do it right now.
| | 04:02 | I am going to press Shift+F11 to add
a new worksheet and just with a meager
| | 04:06 | amount of data here and it doesn't
have to be three-- Maybe it's four months.
| | 04:10 | There's a value for first month,
second month, third month, fourth month.
| | 04:14 | I am going to put a total right
here simply from the Home tab.
| | 04:19 | Click AutoSum, Enter, there we go.
| | 04:22 | Could we create an outline out of this?
| | 04:23 | Well, typically you wouldn't, but
as a starting point we sure can.
| | 04:27 | Click on one of those cells.
| | 04:28 | Go to Data > Group > Auto Outline.
| | 04:32 | You see the same grouping that we saw
before with symbol 1 and 2 appear over
| | 04:38 | on the left-hand side.
| | 04:38 | I am going to click the 1 to collapse this.
| | 04:40 | Now really you wouldn't do this in
real life, but it does illustrate the idea
| | 04:44 | that the outlining capability is
inherent in the fact that Excel sees the
| | 04:48 | formulas tabulating data from the left.
| | 04:50 | If you've got data of a different nature
that has totals on top, that's unusual.
| | 04:55 | Not that unusual perhaps, but
totals on the left is very unusual.
| | 04:59 | You also have the ability if you click
the dialog box launcher for the Outline group,
| | 05:04 | if you have summary rows above--
| | 05:06 | Now I think is strangely worded.
| | 05:09 | Why doesn't it give us the
option to say summary row is above?
| | 05:11 | By unchecking this, this would allow
us to create an outline when we have
| | 05:16 | summary rows on top of data. Or in the
extremely rare case, at least I don't
| | 05:20 | think I've ever seen it, where you've
got summary columns to the left of detail.
| | 05:23 | By unchecking these boxes, the
outlining capability would work for
| | 05:27 | those situations as well.
| | 05:29 | So that's a rare use.
| | 05:31 | So I don't think most people would use
this, but do recognize that's there as well.
| | 05:34 | So there are different kinds of data
where you might want to use the outlining
| | 05:38 | and this ability to collapse and expand,
which we will see in the next movie, is
| | 05:42 | easy to set up and we will even show
you different ways also for hiding those
| | 05:46 | symbols when necessary.
| | Collapse this transcript |
| Using outlining symbols and levels| 00:00 | Once you have created an outline in a
worksheet, you want to be able to quickly
| | 00:04 | manipulate the display of columns and
rows and recognize that we have numbers on
| | 00:09 | top of this worksheet, numbers 1, 2, and 3.
| | 00:12 | Right now the 2 has been clicked and
we see a collapsed view of the data.
| | 00:16 | I am going to click the 1 and watch the
display change here to show only this amount.
| | 00:20 | Now that's not very much.
| | 00:22 | You probably wouldn't leave it looking
like that very long, but you certainly
| | 00:25 | could emphasize just the
grand totals of all the entries.
| | 00:29 | Clicking the 2 brings back more detail.
| | 00:32 | Clicking the 3 brings back all of the detail.
| | 00:35 | Now possibly you might want to go
back to the display that shows just the
| | 00:41 | quarterly totals, but during a
discussion someone might bring up the issue that
| | 00:46 | last year quarter 2 was a
tough one. What's the detail?
| | 00:49 | What's the breakout for quarter 2 here?
| | 00:51 | So we see the plus above Quarter 2.
| | 00:53 | We will click it and there is the
detail, the April, May, June data for
| | 00:57 | that particular quarter.
| | 00:59 | We can certainly do this for
another quarter at the same time.
| | 01:02 | If we wish, we could click the plus
here for the fourth quarter and see the
| | 01:05 | detail for those two quarters at the same time.
| | 01:08 | And sure enough, the minus will collapse in
any order that we want this display as well.
| | 01:13 | So we can use outlining symbols
here as well as outlining numbers.
| | 01:19 | In this particular list here
we have interim totals in row 8.
| | 01:22 | We might want to collapse that to not
show the Sales Shipping breakout,
[00:01:26.7 2]
to compact that part of it.
| | 01:28 | Maybe we don't want to detail of
rows 11, 12, 13 showing as well.
| | 01:32 | We might collapse that part of it.
| | 01:34 | So in different ways you can
expand and collapse the Outline View.
| | 01:38 | Notice that there are four
outlining buttons along the left-hand side.
| | 01:42 | It's because there's a different
number of interim totals as we move down the
| | 01:46 | worksheet vertically, compared with
the number of totals that we saw working
| | 01:51 | across the worksheet.
| | 01:52 | I am going to click 3.
| | 01:54 | We see what's collapsed there.
| | 01:55 | Not a whole lot of change.
| | 01:57 | Click the 2. Substantial change. Click 1.
| | 02:00 | We are down to just this.
| | 02:02 | If they're both 1, not very useful.
| | 02:04 | So it's up to you to decide what's most
useful in the display here as we look at
| | 02:08 | the data, and again a variety of choices.
| | 02:10 | If it's also important at certain times
to freeze the display to show more data,
| | 02:16 | two things you could do.
| | 02:17 | One is to press Ctrl+8 to hide the
outlining symbols and numbers. Ctrl+8.
| | 02:24 | And if you want to show even more data and
hide the ribbon, you can double-click on
| | 02:28 | one of the ribbon tabs.
| | 02:29 | I am going to double-click
the word Data for example.
| | 02:31 | It collapses the ribbon and
there is even a third choice here.
| | 02:35 | We can click the View tab and show fullscreen.
| | 02:39 | That's even going to hide the ribbon
and the Quick Access toolbar to show
| | 02:42 | even more information.
| | 02:43 | So at different times you have this need to
display more information to collapse and hide.
| | 02:49 | I am going to press Escape to bring
back the less than Full Screen view.
| | 02:53 | Double-click one of the tabs here.
| | 02:55 | It doesn't be Data, but it could be.
| | 02:56 | Go back here and then Ctrl+8 will bring
back those outlining symbols. Which at
| | 03:02 | times of course you will want to have.
| | 03:03 | They take up some space, but at the
same time you might want to hide them
| | 03:07 | at different times.
| | 03:08 | So anytime you do have an outline, you
can quickly expand and collapse the view.
| | 03:12 | If you need to redesign the data or if
you no longer want to have the Outline
| | 03:17 | feature in use as you make some
adjustments here and there, the Outline group on
| | 03:22 | the Data tab, Ungroup has
a choice, Clear the Outline.
| | 03:27 | Great feature to use.
| | 03:28 | It doesn't do any damage to your data.
| | 03:30 | It might disorient you a little bit as
you use it, but it's a great feature for
| | 03:34 | quickly expanding and collapsing data.
| | Collapse this transcript |
|
|
7. Tables, Themes, Styles, and Other Essential Data Visualization Tools Converting data into a table and reformatting it| 00:00 | If you work with lists of data in
Excel, whether they are large or small,
| | 00:04 | you might want to explore a
table creating capability.
| | 00:07 | It's a feature that's
been around since Excel 2007.
| | 00:11 | In prior versions of Excel, before 2007
there was a list feature not very well
| | 00:16 | implemented, but in 2007 and now on
2010 we have the Table feature that allows
| | 00:23 | us primarily to establish some visual
features within this list. It makes it easier
| | 00:29 | to read, gives us some
filtering capability, somewhat quickly.
| | 00:33 | We can click on any cell here and
three ways to start this process are either
| | 00:38 | the keystroke shortcut Ctrl+L, think
of List; Ctrl+T, think of Table; or on
| | 00:45 | the Insert tab, Table.
| | 00:48 | In all three cases we'll see this
dialog box where Excel figures out all the
| | 00:54 | contiguous data and assumes that's
what we want to use in the table and that
| | 00:57 | would be correct and we click OK.
| | 00:59 | If the list here that you are
working with has empty rows in it and empty
| | 01:03 | columns, you want to get rid of those if
you want this feature to include all the data.
| | 01:07 | Click OK and the next choice, it may
not be our choice really, but the next
| | 01:12 | view shows every other row highlighted
and at the top of the screen a new
| | 01:18 | ribbon called Table Tools.
| | 01:21 | Most important a new tab called Design.
| | 01:24 | And the thing that catches most
people's eyes almost right away is the section
| | 01:28 | to the right called Table Styles.
| | 01:31 | You can certainly click on one of these.
| | 01:32 | Notice as you slide over them they
change, but there is a drop-arrow here
| | 01:36 | revealing 61 different choices.
| | 01:39 | As you slide over these,
you'll see what's happening.
| | 01:42 | Strangely enough, some of these seem to be
hiding your title row, like these down here.
| | 01:46 | Now, these are guaranteed to use up a
lot of the ink in your color printers if
| | 01:49 | you ever do print this.
| | 01:51 | But primarily this is visual and
it does make the data more readable,
| | 01:55 | particularly if it's like in this
example here a lot of scientific data,
| | 01:59 | your eyes need to move left and right across the
screen, the banded rows that we see makes sense.
| | 02:04 | So from time to time you'll change your use
of colors here and make the choice that way.
| | 02:09 | Now you'll recognize also that in this
Design tab often you'll see Header Row
| | 02:14 | and Banded Rows selected automatically.
| | 02:16 | I am going to uncheck the Banded Rows,
so we see the look now. Banded Columns?
| | 02:21 | Well, possibly in some worksheets.
I would suggest don't use both of these,
| | 02:25 | although you can if it's going to
make sense. Use one or the other.
| | 02:29 | Probably Banded Rows.
| | 02:30 | If we click last column as I'm doing
here, unless you're seeing it you won't
| | 02:34 | know what's happened until you look.
| | 02:35 | But the last column is now bold and
sure enough if we do this for first column,
| | 02:40 | same thing will happen there.
| | 02:42 | It just offsets the data. For some people,
| | 02:44 | that's exactly what they want to do.
| | 02:46 | Another obvious visual change
here is that we do see filter arrows.
| | 02:50 | You might not have used that
capability, but it's easy to use.
| | 02:54 | That's a handy thing to be able to
have at your fingertips whenever you are
| | 02:57 | working with lists of data.
| | 02:58 | I might click column B here.
| | 03:00 | First of all I'll make it a
bit wider. Spc data here.
| | 03:03 | I am looking for those
that are just five.
| | 03:06 | So I want to unselect these. Contain
just the ones that are equal to 5 there.
| | 03:10 | So it's an abbreviated list of the data.
| | 03:12 | See the row numbers off to the left-hand side.
| | 03:15 | So you don't necessarily have to know
all about filtering but the filter arrows
| | 03:18 | are there and it's handy.
| | 03:20 | There are some analytical
features associated with this.
| | 03:22 | If for example we were to add a
formula on the right-hand side maybe that
| | 03:26 | simply takes this data.
| | 03:28 | We put in a title first of all.
| | 03:29 | Look what happens when I put in the title here.
| | 03:32 | I'm going to put in the Index.
| | 03:33 | The Index is simply, you can see how
the display changes automatically to
| | 03:38 | include this column.
| | 03:39 | The Index is going to be one-and-
a-half times the quantity here.
| | 03:42 | So I am going to take a formula, equal
this, and notice what's happening on the
| | 03:46 | screen here, times 1.5, and look
what happens when I press Enter.
| | 03:51 | Formulas are put into
all of these cells at once.
| | 03:54 | Now again, feature although primarily visual
does have this analytical component to it.
| | 04:00 | It uses different kinds of formulas here.
| | 04:02 | We can also put totals on the bottom.
| | 04:04 | It is a nice feature for displaying
the data and keep in mind every time the
| | 04:09 | active cell is within the data,
you do see the Design tab.
| | 04:12 | If I click outside, we don't
see the Design tab anymore.
| | 04:16 | If for whatever reason you decide
that this feature doesn't offer you what
| | 04:20 | you want or it just doesn't grab you that much,
you may want to remove the Table capability.
| | 04:25 | Now there is something else before you
do this too that you might want to note.
| | 04:28 | I'm scrolling downward and
where are the column letters?
| | 04:32 | We don't see them within the table.
They have been replaced by the text.
| | 04:35 | I am not sure what the rationale
behind that and it doesn't necessarily
| | 04:38 | disturb me, but at some point in
using a table couple of times I said, what
| | 04:42 | column am I in here now?
| | 04:43 | I can see the address in
the upper left-hand corner.
| | 04:45 | That's column S, but I am not seeing
that here, unless I scroll all the way up
| | 04:49 | top and then they reemerge.
| | 04:51 | So that's a little bit unusual.
| | 04:53 | Now if you decide you don't want this,
possibly you'll just do a series of undos.
| | 04:58 | We can do it that way.
| | 04:59 | It might be too late to do that.
| | 05:01 | So again you can use Undo repeatedly
or click the drop-arrow. Possibly go all
| | 05:06 | the way back to Create Table. Undo that.
| | 05:09 | That's one way to do it.
| | 05:11 | Let me press Ctrl+Y to Redo.
| | 05:13 | The other way to remove would be to click
the Design tab and then Convert to Range.
| | 05:20 | Do you want to convert
the table to a normal range?
| | 05:23 | When you do this, however, if I say
yes, the colors will stay but the table
| | 05:28 | characteristics in other respects are not there.
| | 05:30 | And if we insert new rows here, we are
not going to retain that every other row
| | 05:35 | look as we would if we had the table in effect.
| | 05:38 | I am going to press Ctrl+Z. Now
there is a fourth way to create a table,
| | 05:42 | almost the same idea, but you also
see it in the ribbon, this time on the
| | 05:46 | Home tab, Format as Table.
| | 05:48 | The only difference between this and
the other three techniques is simply that
| | 05:52 | the first step is the visual change that we
make and then we get the prompt for the range.
| | 05:57 | Click OK.
| | 05:58 | And if you then click in the data,
we are in the same situation we were in
| | 06:01 | before when we were making changes.
| | 06:03 | So I think it's a feature
well worth looking into.
| | 06:06 | It's a nice quick visual look for
some people who don't really like to do
| | 06:09 | formatting manually and it does
offer some analytical capability although
| | 06:13 | primarily it's visual in nature.
| | Collapse this transcript |
| Using styles and themes for consistent formatting| 00:00 | In this worksheet you've recognized
almost immediately the shading in columns E,
| | 00:05 | F and G. What is a little unusual
about it is that the text is white.
| | 00:09 | Now you can certainly achieve this
effect by going to two separate buttons in
| | 00:13 | the Font group on the Home tab.
| | 00:16 | In no particular order you might first put in
a background color. Quite a few choices here.
| | 00:21 | We could certainly do this manually and
then after having done that, the button
| | 00:26 | to the right of it, pick a
font color. Possibly white.
| | 00:29 | Now that's not the way these cells were
formatted, not to say that we couldn't
| | 00:33 | have done that, but as I highlight these,
I am going to go into the Styles group
| | 00:39 | on the Home tab and choose Cell Styles.
| | 00:42 | Quite a few choices here
| | 00:44 | and you might recognize at the
bottom here, right here, Accent2.
| | 00:48 | Well, it's not exactly a memorable name,
but here we have some built-in choices.
| | 00:53 | And Excel's Live Preview feature is
kicking in and now as I am sliding the mouse
| | 00:58 | over these choices, we see
immediately what's about to happen or what could
| | 01:02 | happen in the worksheet.
| | 01:04 | So this is going to save us some work,
provided we like some of these choices here.
| | 01:09 | And what's not so obvious is that the
styles that we see here are also in sync
| | 01:16 | with the term "theme," which
you might have heard about.
| | 01:19 | In prior versions of Excel until 2007,
there is no such thing as a theme, but
| | 01:25 | in Excel 2007 themes and styles were
introduced and they have a complementary role to play.
| | 01:32 | Now, as I am sliding over the various
choices here, you see how the data is reacting.
| | 01:37 | Ignoring the emotional content of the
words bad, good, and neutral, maybe these
| | 01:42 | particular formatting
capabilities here are exactly what we want.
| | 01:47 | Now, which comes first here, the Theme
or the Styles is one of those chicken
| | 01:51 | and egg situations.
| | 01:52 | But when we make a choice here, and
maybe I will go back to my original one,
| | 01:55 | Accent2, sort of a pinkish
reddish color, and I'll choose this.
| | 02:00 | Excel also provides themes and they
are not located in the Styles group here.
| | 02:05 | They're located on the Page Layout tab.
| | 02:08 | I am going to click Themes here and as
I slide over some of these choices,
| | 02:14 | we see different colors appearing here.
| | 02:17 | Now what if I like one of these
colors and I say okay, I like this
| | 02:20 | variation possibly.
| | 02:22 | It's called Aspect.
| | 02:23 | I have changed the colors,
but why would I do that?
| | 02:27 | As I go back to the Home tab and now
click Cell Styles, we see similar yet
| | 02:34 | different kinds of colors.
| | 02:36 | Now I might be looking around for a
brighter green, a different green. How about
| | 02:41 | those army type greens that we saw before?
| | 02:43 | I don't see those anymore.
| | 02:45 | So I am going to go back, choose Page
Layout again, go back to Themes, and what
| | 02:51 | we're not seeing here just yet
are all possibilities of the colors.
| | 02:55 | How many themes do we have?
| | 02:57 | You'll see the term Built-in and possibly
you could go out on the web and find many more.
| | 03:03 | If you're currently sliding through
your previews right now, you might see a
| | 03:07 | different set here. Overlapping.
| | 03:09 | What if we choose Metro?
| | 03:11 | How has that changed things?
| | 03:14 | Let's go back to the Home tab again.
| | 03:15 | Take a look at some of the Cell Styles.
| | 03:18 | Here are colors we didn't see before
and where are those greens that had
| | 03:21 | been in this column?
| | 03:23 | They are not there anymore.
| | 03:24 | There is a different kind of green over here.
| | 03:27 | Now you could easily lose a lot of
time worrying about all these variations.
| | 03:31 | Notice also how we can slide
over currency here and percent.
| | 03:36 | So this could be a bit bewildering
because of all the choices that you have.
| | 03:40 | What I'm going to do is do it
the way I do it most of the time.
| | 03:44 | I usually stick with the default theme.
| | 03:47 | So on Page Layout >
Themes, up at the top, Office.
| | 03:52 | Now I am not suggesting that's the way
everybody should go because you're going
| | 03:56 | to be deciding over time what works best for you.
| | 03:59 | The other aspect of this that isn't so
obvious is that if you use Microsoft Word
| | 04:04 | and you use PowerPoint, you will see
the same themes. And there's a different
| | 04:10 | worksheet down here called Themes and
on purpose here I'm using different kinds
| | 04:14 | of colors in columns D through G.
Different kinds of style settings there.
| | 04:20 | There is a chart on the screen.
| | 04:22 | There's also a shape object placed here too.
| | 04:26 | If I change the theme by clicking Page
Layout and then Themes, as I slide over
| | 04:31 | some of these choices you see what's happening.
| | 04:34 | Three aspects of the screen are changing now.
| | 04:37 | The data in columns D through G,
the chart to the right, and the object,
| | 04:42 | the shape below the data.
| | 04:46 | Now, do we have time for this?
| | 04:48 | That's another question, without
ridiculing the concept exactly.
| | 04:51 | The choices seem to just go on and
on and on. And I think in some cases,
| | 04:57 | without editorializing exactly, some
of these charts start to look pretty bad
| | 05:02 | with some of these choices.
| | 05:05 | So you see the impact that
choosing different themes has.
| | 05:09 | But what if you're playing together a
project and you've got some files in
| | 05:12 | PowerPoint and Word and possibly you
are working with other people as well,
| | 05:17 | it would make good sense for you to
come up with some kind of an agreement
| | 05:20 | to say okay, we are all going to use a
certain particular theme that has a color set.
| | 05:26 | Maybe you like Metro.
| | 05:27 | I wouldn't pick that one ever.
| | 05:28 | Look at that chart. It's terrible.
| | 05:30 | How about Module?
| | 05:32 | I like that one better maybe.
| | 05:33 | Maybe we will click this.
| | 05:36 | What's happened, there is impact now,
as we suggested earlier, on the styles
| | 05:41 | that we have available.
| | 05:42 | Click Home > Cell Styles and
we see the color choices here.
| | 05:48 | So it's kind of hard to come up with
some kind of a concluding statement about
| | 05:52 | how you might use this, but the fact
that there is so much variety and you need
| | 05:57 | only slide over these to get a preview
certainly makes a lot of choices easy to
| | 06:02 | make in the sense that it
doesn't take long to make the choice.
| | 06:05 | Agonizing over the differences is a
different issue but still, if that's the
| | 06:09 | one you like, good.
| | 06:10 | We'll keep it that way.
| | 06:11 | I am going to improve this one possibly.
| | 06:13 | Same idea, and go like that, there we go.
| | 06:18 | But again recognize that when you
change themes, it has impact on the styles
| | 06:23 | that you have chosen in cells, also the
colors that exist in charts, and if you
| | 06:27 | have any objects, the colors
that are being used there as well.
| | 06:31 | So work back-and-forth, going to Page
Layout, choosing Themes, and then using the
| | 06:36 | Home tab to get into some of the style choices.
| | 06:39 | Another aspect of this too that you
will probably want to explore on the Page
| | 06:44 | Layout tab next to Themes, you will see
different colors displayed this way and
| | 06:50 | you can slide over these choices as well.
| | 06:52 | That's another approach to it, again
keeping your eye on the various color
| | 06:56 | palettes that are exposed here.
| | 06:59 | So, no shortage of capabilities for
exploring the various themes that work
| | 07:03 | with styles in Excel.
| | Collapse this transcript |
| Understanding the options in shape formatting| 00:00 | On the Insert tab in the ribbon
you'll notice a choice called Shapes.
| | 00:04 | If you click the arrow for Shapes,
you'll see almost 200 different kinds of
| | 00:09 | shapes including lines, a variety
of rectangles, some basic shapes.
| | 00:14 | It includes a smiley face in here. A lot
of block arrows, some flowchart symbols, a
| | 00:19 | lot of stars and banners.
| | 00:20 | How about a 16-point, 32-point star?
| | 00:23 | Lots of choices down here.
| | 00:25 | Many of these, particularly those that
encompass space, possibly could be used for titles.
| | 00:30 | Now, of course a lot of them
couldn't be that appropriate.
| | 00:32 | But, if you want to put a title on a
worksheet, you could encompass it in one of these.
| | 00:36 | You might want to put an
arrow that points to a cell.
| | 00:39 | For particular emphasis, you
want to annotate a worksheet.
| | 00:43 | So lots of different reasons for using
these and you'll find these in PowerPoint
| | 00:47 | as well and many people use these more
there perhaps than they might in Excel.
| | 00:52 | Suppose we want to create one of these.
| | 00:54 | I might want to put a title.
| | 00:55 | Let's first just talk about the basic
idea of how we draw one of these shapes.
| | 01:01 | I'll choose a rectangle for starters.
| | 01:03 | You see the crosshairs.
| | 01:05 | I can put it anywhere I want.
Maybe I want to put it over here.
| | 01:08 | As I'm dragging this, it
could be a tall rectangle.
| | 01:10 | It could be a wide rectangle.
| | 01:13 | If you hold down the Shift key, it
will become a perfect square, provided you
| | 01:18 | let go off the mouse first.
| | 01:20 | As I am doing. There we are, a square.
| | 01:22 | Now, maybe I didn't really need a
square, but just emphasize the idea.
| | 01:26 | Recognize also that when you create a
shape, there's a new ribbon called Drawing
| | 01:31 | Tools with a Format tab on it and
tons of options here for changing the
| | 01:37 | appearance of that object.
| | 01:39 | If we want to create another one,
the left-hand side of this tab does have the
| | 01:44 | options available here from the drop-down.
| | 01:47 | So here we are again.
| | 01:48 | Let's suppose we want to put a title in here.
| | 01:50 | Maybe I'll use a rounded
rectangle and draw it over here.
| | 01:56 | Once you have created this you
can begin typing immediately.
| | 02:00 | So maybe I want to put in a
phrase here, Domestic Sales.
| | 02:02 | It's going to be the title
for this particular worksheet.
| | 02:05 | Domestic Sales-2011.
| | 02:08 | Text could be a lot bigger.
| | 02:10 | Just jump to the Home tab
here. Let's use a bigger font.
| | 02:17 | We might center it with this button
here, possibly change the font color.
| | 02:20 | We want a red. That probably
isn't a great choice there.
| | 02:22 | But you can manipulate that
of course pretty easily too.
| | 02:25 | Make it be bold, so on, and all the
different things we might do with the shape.
| | 02:30 | If you'd like to draw an arrow,
we can go back to the Insert tab >
| | 02:36 | Shapes. There's an arrow.
| | 02:40 | When you draw an arrow, you might
want to point to a certain cell.
| | 02:43 | In a different situation, if you are
trying to draw an arrow or a line, if it
| | 02:47 | needs to be perfectly horizontal rather
than guessing, hold down the Shift key.
| | 02:51 | So when you are holding down the Shift
key here on arrows, you can draw this
| | 02:55 | at 45 degree increment.
| | 02:57 | So perfectly vertical or horizontal or
the 45 degree angles, and here too, let go of
| | 03:02 | the mouse first if you want to
retain that angle for whatever reason.
| | 03:07 | Possibly you are going to
accompany this with a box.
| | 03:10 | If you drag the edge of this,
you can associate it with a box there.
| | 03:14 | So ,there are lots of different things
we can do with these and going back here
| | 03:18 | if you wanted to draw for whatever
reason smiley face, you'll recognize this.
| | 03:22 | It could be wide or tall.
| | 03:24 | You might also recognize here that
there's a yellow diamond and you will see
| | 03:27 | this on certain shapes.
| | 03:29 | So smiley face there may be looking
like it's Friday. We'll drag the yellow
| | 03:33 | diamond. Sort of looks like Monday, doesn't it?
| | 03:36 | A little more meaningful use of that
might be Insert > Shapes. For whatever
| | 03:41 | reason you've drawn a hexagon or an octagon,
maybe some text is going to go into it.
| | 03:47 | You made it wide or tall.
| | 03:49 | The yellow diamond here
changes the shape of it as well.
| | 03:52 | If you're going to create a flowchart,
you might recognize that there are some
| | 03:56 | flowcharting symbols there under Shapes.
| | 03:59 | You can use those or you can
certainly use other boxes and triangles and
| | 04:03 | diamonds as you wish here.
| | 04:05 | If you do intend on creating a shape
multiple number of times, you can also
| | 04:10 | choose an option here under Shapes.
| | 04:13 | You can also choose to right-click a shape.
| | 04:16 | If I need a few triangles here, I am going to
right click the shape and Lock Drawing Mode.
| | 04:21 | What that means is I can create a
triangle there, I can create a triangle here,
| | 04:26 | create one there and on and on and on if
I need a few of them and if some point
| | 04:31 | I don't need them anymore,
| | 04:32 | I could press Escape.
| | 04:33 | If you'd like to duplicate a shape,
you can click on and simply press
| | 04:36 | Ctrl+D. Now sort of a few times if you want a
few more of those, to create multiple shapes.
| | 04:42 | So they're easy to create and although
it may seem a little bit like fun and
| | 04:45 | games here and there, it also can be
used for annotation purposes, for example
| | 04:50 | drawing arrows, putting titles on
worksheets, and possibly building flowcharts.
| | Collapse this transcript |
| Fine-tuning shape effects| 00:00 | It's highly unlikely you would ever see
a worksheet looking like this one but it
| | 00:04 | does encompass some shapes
that you might use here and there.
| | 00:08 | And it is worth noting that the
shapes can be manipulated fairly easily.
| | 00:12 | You'll also notice that some of these
shapes have reflections. The one on the
| | 00:17 | left here, the oval Two Trees Olive
Oil, there is a reflection below it.
| | 00:20 | The octagon here has a shadow behind it.
| | 00:23 | You can actually use both together.
| | 00:26 | The rounded rectangle to the right
here has a feature called Glow on it.
| | 00:30 | So we want to show you different ways
to manipulate and add these features.
| | 00:34 | Here's also a line. We might want to
make a change there, make it thicker.
| | 00:38 | So starting with the oval on the left-
hand side here, I am going to click the
| | 00:42 | oval and because it is an object, recognize
again the Drawing Tools ribbon is activated.
| | 00:48 | We've got the Format tab here and
under Shape Effects, a drop arrow, Shadow.
| | 00:55 | There's no shadow currently being used.
Let's ignore that feature for the moment.
| | 00:59 | How about Reflection?
| | 01:00 | Right now out of the nine choices
available the one on the lower-right is being used,.
| | 01:04 | And the so-called Live Preview
feature allows us to slide over the other
| | 01:08 | choices and see the subtle
variation here if we like to use Reflection.
| | 01:14 | Fast and easy. Or no
Reflection easily make the changes.
| | 01:18 | We will use this reflection instead.
| | 01:21 | There's a shadow here.
| | 01:22 | Let's click this object. Go to Shape Effects.
| | 01:24 | When we come to Shadow we
see which one is being used.
| | 01:28 | It's the one at the bottom here.
| | 01:29 | But as we slide over the other
choices-- and recognize from time to time you
| | 01:34 | might want to move an object because
we're not necessarily seeing the effect so well
| | 01:38 | since these choices
overlap the object in question.
| | 01:42 | So maybe what we'll do here is scroll
rightward, moving that octagon to the left side.
| | 01:47 | It's still selected. Choose shapes
this way and then maybe explore if we're
| | 01:52 | interested in some of those Shadow Effects.
| | 01:55 | Tons of options here for making changes
to the way these objects have shadows on
| | 02:00 | them, if that's what we're
interested in. Quite a few choices.
| | 02:05 | A line, perhaps you want that thicker.
| | 02:08 | The approach to formatting either
involves right-clicking and sometimes you'll
| | 02:13 | get this option, but if you right-click
again on the object, you're more likely
| | 02:16 | to see a larger menu and you will see
some options in the mini toolbar that
| | 02:21 | might be appropriate.
| | 02:22 | For example, Shape Outline here.
| | 02:25 | That will give us options for Weight.
| | 02:28 | How about a thicker line? We
can go down this path or come back
| | 02:32 | there again, right-click.
| | 02:34 | Another approach could
be how about Format Shape?
| | 02:39 | And that activates a
totally different dialog box.
| | 02:42 | There are certainly some options to
explore here. Way too many let's say for the moment.
| | 02:47 | And sure enough the Format tab
in the Drawing Tools ribbon also gives us
| | 02:54 | some capability here for controlling
maybe the length of a line, the angle, you
| | 03:01 | can manipulate these.
| | 03:03 | So there are just many, many different ways
to explore how these options are appearing.
| | 03:07 | This particular object here has glow on it.
| | 03:10 | Let's click there and go to Shape Effects.
| | 03:13 | Well, there are 24 options
down there for applying Glow.
| | 03:16 | Once again, the choices here are overlapping.
| | 03:19 | We could still see a corner of it.
| | 03:21 | So if that's something we think we'd like,
fine. Make those choices as well too.
| | 03:26 | When it comes to the size of a shape or
the ratio of height to width, sometimes
| | 03:30 | you want to make a shape larger or smaller.
| | 03:33 | If you drag a shape from the corner,
you could certainly make this shape for
| | 03:37 | example wider, taller, whatever,
or smaller in a variety of ways.
| | 03:42 | If you hold down the Shift key, you
are making it larger or smaller using
| | 03:47 | the same aspect ratio.
| | 03:49 | The ratio of height to width.
Sometimes you want to do that.
| | 03:53 | When you are using the Shift key in
these circumstances, be sure to let go of
| | 03:56 | the mouse first if you want to retain that look.
| | 04:00 | There are other times when you've
chosen the shape and you want to make it
| | 04:03 | larger but you want to keep it around the
same center. Maybe I will move this downward.
| | 04:08 | How do we move a shape?
| | 04:10 | Simply click-and-drag it.
| | 04:12 | If we'd like to make a copy of it,
we can hold down the Ctrl key and drag
| | 04:17 | it anywhere we want.
| | 04:18 | If we'd like to have a copy of it in
the same horizontal or vertical plane,
| | 04:23 | we'll be holding down Ctrl and Shift.
| | 04:26 | And so as I drag rightward here,
I can't drag it upward. There we go.
| | 04:31 | But if I do want a copy of this upward,
I'll hold down Ctrl and Shift and drag
| | 04:35 | it upward. It's not letting
me drag it left or right.
| | 04:39 | Don't really need that now, so Ctrl+Z.
If we'd like to make this shape larger or
| | 04:44 | smaller around the same center,
we want to drag any of the corners with the
| | 04:49 | Ctrl key to do this.
| | 04:51 | Larger, smaller, same center.
| | 04:54 | If we want to make it wider or narrower
around the same center, we're going to
| | 04:59 | drag either handle on the left or
right side, holding down the Ctrl key.
| | 05:05 | Larger, smaller this way. Or
above, larger, smaller this way.
| | 05:10 | If we would like to rotate the object, we can
drag this green circle in a variety of ways.
| | 05:16 | If we hold down the Shift key, it only
lets us drag this in 15 degree increments.
| | 05:21 | I am holding down Shift right now.
| | 05:23 | So for a variety of reasons and methods and
techniques, we can rotate objects as well.
| | 05:30 | If you double-click an object it activates
the Format tab if it had not been activated.
| | 05:36 | There are so many different options,
there are so many different choices, and
| | 05:39 | a reminder again, a shape like this, maybe
you don't like the color. What might you do?
| | 05:45 | Click Shape Fill is one option. Pick
a different color and as we slide over
| | 05:49 | these we see the choices.
| | 05:51 | If you don't see a color you like here,
you might find it down here or how about
| | 05:55 | other colors? More Fill Colors, on and
on and on. Maybe up this way. Pick green
| | 06:01 | possibly. Here we don't
get live preview. Click OK.
| | 06:04 | We will see the effect.
| | 06:06 | If you don't like the edge there,
maybe it's the shape outline you'd like to
| | 06:10 | change and immediately you
see what's happening there.
| | 06:13 | So I think many, many times if you've
worked with graphics features before,
| | 06:18 | a lot of the way we use these in
Excel is fairly obvious, but it's
| | 06:21 | definitely worth exploring.
| | 06:23 | It's not all fun and games. These
arrows could be used in an important way.
| | 06:27 | If you did have data in a cell, maybe
imagine this value is pretty important.
| | 06:32 | You've got it with some other data
where you might want to point to this.
| | 06:35 | Would you like to put an oval around it?
| | 06:38 | We could go back to the Insert tab,
Shape to find an oval, drag it roughly
| | 06:45 | there, move this around a little bit.
| | 06:46 | Now, obviously we like to see through the oval.
| | 06:49 | So once again what might we do?
| | 06:51 | Shape Fill > No Fill.
| | 06:55 | We want the edge. So just on and on.
| | 06:58 | We could make that thicker of course.
| | 06:59 | We could also make it translucent.
| | 07:01 | quite a few different ways to make changes here.
| | 07:03 | Here's another object.
| | 07:05 | I don't know that I've ever
used this in any intelligent way.
| | 07:07 | Look at all the yellow diamonds there
that will allow us to change the relative
| | 07:12 | relationship of the arrows
to the box in the background.
| | 07:16 | Just goes on and on and on in
terms of what we might do here.
| | 07:20 | Again, as you select an object,
whether it's a line or an object, we have a
| | 07:25 | variety of choices available in the
Format tab and a lot of them are available
| | 07:29 | also by right-clicking.
| | Collapse this transcript |
| Learning the WordArt essentials for titles and headings| 00:00 | If you are interested in putting in some
pretty fancy titles in Excel, you might
| | 00:04 | want to explore WordArt.
| | 00:06 | it's also available in PowerPoint and Word.
| | 00:09 | Click the Insert tab in
the ribbon. Choose WordArt.
| | 00:13 | First choice, make a letter style
selection, perhaps this one, and immediately
| | 00:18 | you will see a box more or less in the
middle of the screen. Probably you'd want
| | 00:22 | to drag this up this way.
| | 00:25 | Make it a bit larger.
| | 00:27 | We could simply drag across the text and
highlight that type-over if you'd wish.
| | 00:31 | As you've got worksheet data somewhere
that would be an appropriate title and
| | 00:34 | you don't want to retype it, you can click in
the Formula bar, type equal, then click
| | 00:40 | on the cell in question or type its
address, A22 in this case, Enter, and the
| | 00:45 | text is right there.
| | 00:46 | If that's a bit too big, you might
click on the border here and in the
| | 00:50 | mini toolbar right here, you could
change the font size this way possibly.
| | 00:54 | Drag over some of these choices, maybe do
this one, maybe that on, good enough and we
| | 00:59 | can resize and possibly
the word wrap will go away.
| | 01:01 | We'll choose this here and then maybe
resize this a bit and a bit more and
| | 01:09 | move that around as we need to.
| | 01:11 | And we'd like to give this a background.
| | 01:14 | Shape Styles on the Format tab in the
Drawing Tools ribbon, Shape Styles.
| | 01:19 | A number of choices and immediately with
Live Preview, we see what's about to happen.
| | 01:23 | Maybe we'll choose this one
and while that might be just fine,
| | 01:27 | while we are here we might also
consider filling those letters with a different
| | 01:32 | color and immediately we see some of
the many possibilities available here.
| | 01:36 | That's one particular option.
| | 01:38 | If you choose Text Outlines,
sometimes this appears to do almost nothing.
| | 01:42 | As you slide over the
choices here, it's real subtle.
| | 01:45 | So that's the least
important of these three choices.
| | 01:49 | You might want to consider Text Effects.
| | 01:51 | Maybe these would look
better with a Glow, maybe not.
| | 01:56 | Possibly a 3-D Rotation. I pretty
much doubt that. And maybe even Transform.
| | 02:02 | Most of these don't work so well but
here and there, you might see one or two
| | 02:05 | that maybe fit your needs and in each
case if you happen to choose one of these
| | 02:09 | that might be sort of close to what you want,
| | 02:11 | maybe you like this idea here,
recognize you will see diamonds in there and you
| | 02:16 | can make these shapes, again, larger,
smaller, taller, etcetera, as you drag the
| | 02:21 | diamonds in different
directions to change the effect of that.
| | 02:24 | So it's one of those features that's
not totally necessary and yet it does give
| | 02:29 | added flair to certain kinds of
worksheets, makes them more appealing.
| | 02:33 | It's called WordArt and it's also
available in PowerPoint and Word.
| | Collapse this transcript |
| Formatting Sparklines for optimum impact| 00:00 | Excel 2010 has a new visual
feature called Sparklines.
| | 00:05 | You'll find these on the Insert tab.
| | 00:08 | Line, Column, Win/Loss, Sparklines.
| | 00:11 | These are like in-cell charts.
| | 00:14 | Rather than creating a full-fledged
chart, it would be very handy if we could
| | 00:18 | have, for example in this cell here, some
measure of what's happening in these cells.
| | 00:24 | Now, we can certainly see the numbers,
but the visuals sometimes help us
| | 00:27 | tell the story quickly.
| | 00:29 | Similarly we'd like to have the same
kind of chart here, here, and here in
| | 00:33 | this particular example.
| | 00:34 | So let's highlight this data to begin with.
| | 00:36 | You can highlight the data ahead of time or
highlight the receiving area first, either way.
| | 00:42 | It doesn't make any difference.
| | 00:43 | Let's highlight the sending area, so
to speak, or the source data and then
| | 00:49 | choose Line, Column or Win/Loss.
| | 00:51 | Let's pick Line first.
| | 00:54 | Choose the data where
you want. We've done that.
| | 00:56 | Where do we want the Sparklines to be placed?
| | 00:58 | We can put these anywhere we want.
| | 01:01 | Below the data, to the right of it.
| | 01:02 | It's going to makes sense here to
simply put it in these cells out here.
| | 01:06 | Now, you don't really have to collapse the
dialog box, as long as those cells are visible.
| | 01:10 | We'll highlight them, there we
are, OK, and we see a line chart.
| | 01:16 | As you look at this at first, you'll say,
"Well, yeah, but there's no reference
| | 01:20 | point necessarily, and
are these the scale or what?"
| | 01:24 | By looking at the numbers and jumping
back and forth at the image, you can begin
| | 01:27 | to see what's happening here.
| | 01:29 | It is a reflection of the ebb and
flow of the data moving up and down.
| | 01:33 | I think that for many people we are going to
need to alter these to make them even clearer.
| | 01:39 | When you do click on one of these,
recognize that even though we haven't
| | 01:43 | selected all of them, we do have
some tools available. A new ribbon
| | 01:47 | called Sparkline Tools.
| | 01:49 | The first thing that might
catch your eyes is the design.
| | 01:51 | I'll just click here and
make a different choice.
| | 01:56 | A different set of display
colors there. How about red?
| | 01:59 | Maybe that looks better.
| | 02:00 | Notice how they all change once. In
other words, we didn't necessarily have to
| | 02:03 | do this to make the change.
| | 02:05 | That's certainly one change and the
Sparkline Color here also gives us the
| | 02:09 | option of making these thicker.
| | 02:10 | I think that's important in some situations.
| | 02:12 | How about this one here?
| | 02:13 | It's a lot more prominent than it had been.
| | 02:16 | But there're some other tools as well.
| | 02:18 | Let's choose Axis here.
| | 02:20 | We might want to show an axis.
| | 02:23 | Now, this isn't obvious until we actually
do it but you'll see the difference there.
| | 02:26 | I think in some situations
that's going to make some sense.
| | 02:29 | We do see that some data has
gone below the axis, meaning below 0.
| | 02:34 | So we see some negative numbers over there.
| | 02:37 | But even more helpful is probably
going to be those choices available in the
| | 02:40 | Show group on the Design tab.
| | 02:42 | How about showing the High Point?
| | 02:43 | We will see what's happened here.
| | 02:45 | The high point as a
particular marker associated with it.
| | 02:49 | How about Low Point?
| | 02:50 | Same kind of marker.
| | 02:52 | It was pretty obvious which is low and
which is high, but to distinguish the two,
| | 02:56 | we might want to choose Marker
Color here over in the Style group and maybe
| | 03:01 | for the high point we want that to be
blue and keep the low point red, since red
| | 03:05 | is often associated with negative data.
| | 03:07 | Back to the Show group on
the left-hand side here.
| | 03:10 | Negative Points, we can highlight those.
| | 03:14 | Then there are a few negatives that
aren't necessarily the low points.
| | 03:16 | So you might want to kick these around
a little bit and see the variations there.
| | 03:20 | The First Point, possibly, or how
about Markers for all, do it that way.
| | 03:25 | That too might have its merits.
| | 03:27 | So you might want to explore some
possibilities here with different kinds of
| | 03:31 | markers and negative points.
| | 03:32 | I am going to turn off Negative Points.
| | 03:34 | Sometimes that's the display.
| | 03:35 | Turn off High Point,
maybe that's the one you want.
| | 03:37 | Is it obvious that we always want a line chart?
| | 03:41 | How about a column chart?
| | 03:42 | So in the Type group here, we might
choose Column and get those kinds of entries.
| | 03:48 | Another possibility here is to take the
same data and have Sparklines appear below it.
| | 03:55 | In other words, how about a chart here
that reflects this data and a chart here
| | 03:59 | that reflects these?
| | 04:00 | So let's put Sparklines here and this
time for variation, I'll highlight the
| | 04:05 | receiving area first, the area where we wish the
Sparklines to appear, and from there on the Insert tab
| | 04:14 | maybe this time we'll
choose Column. Click right here.
| | 04:17 | Same dialog box we saw before, but this time,
we are going to fill in the source data.
| | 04:22 | No real reason to collapse this.
| | 04:23 | Simply go select the data and
click OK, and we have charts down there.
| | 04:30 | Now, what's a little confusing about
this at first is you have to kind of
| | 04:34 | recognize, let's say in this example
here, as we see the columns from left to
| | 04:39 | right, we are seeing this data here.
| | 04:41 | So you may think of that being rotated somehow.
| | 04:44 | And here too with this kind of
Sparkline entry, we might want to go back to the
| | 04:49 | Design tab and consider some axis changes here.
| | 04:53 | How about showing the
axis? That might be helpful.
| | 04:57 | Also here choosing Same for All
Sparklines for the minimum and also for the maximum.
| | 05:07 | That puts them on the same
relative plane in terms of their values.
| | 05:11 | To me, this isn't as valuable as what
we are seeing on the right-hand side but
| | 05:14 | nevertheless it has its merit and I
would necessarily suggest that you need to
| | 05:18 | do both at the same time.
| | 05:19 | Another variation on this could be you
simply want to show a relative and this
| | 05:26 | might be more appropriate with slightly
different kinds of data, but the third kind
| | 05:29 | of Sparkline is called Win/Loss.
| | 05:31 | I am going to choose that
here and you see what happens.
| | 05:35 | In the example here, the negatives
are interpreted as a loss and so they
| | 05:39 | appear below the bar.
| | 05:40 | So at a glance, we could say well, only in
August that we have two of these below 0.
| | 05:44 | Now you can certainly see it in the data,
but maybe this isn't little clearer here.
| | 05:49 | Now one other variation here that you
might be interested in, suppose we would
| | 05:53 | like to see this kind of chart, maybe
as a line chart, but below the data.
| | 05:58 | I am going to copy this simply by
holding down the Ctrl key, dragging the edge
| | 06:02 | down to here, letting go with the mouse,
and this time I want to create some
| | 06:07 | Sparklines and initially put them
right here, which doesn't sound right.
| | 06:11 | So it might start at
this location, click Insert.
| | 06:15 | Let's put in Line sparklines
and the source data is this here.
| | 06:21 | Click OK and although it actually does
what we want in one sense, it certainly
| | 06:26 | isn't what we want ultimately.
| | 06:27 | In other words, this truly is a
representation of these values.
| | 06:33 | But what if we were to merge this
cell with the ones to the right?
| | 06:39 | Now, if we want to do this for all
these cells below I will have to now hold
| | 06:42 | down the Ctrl key, drag across here,
Ctrl key is still held down, drag across here,
| | 06:47 | drag across here and then
merge these cells on the Home tab.
| | 06:53 | Merge & Center.
| | 06:55 | There we are.
| | 06:56 | Make the lines thicker.
| | 06:58 | we now have a visual representation of what
happened in the east across those 12 months.
| | 07:03 | If you want to get rid of Sparklines,
you could get rid of any one, although you
| | 07:08 | are more likely to want
to get rid of all of them.
| | 07:10 | But if for whatever reason you had a
situation where you say "Well, I don't want
| | 07:13 | to show that yet because the December
data isn't final," you don't delete.
| | 07:18 | You right-click and choose Sparklines
and say if you just wanted to get rid of
| | 07:23 | one of them, it will be
Clear Selected Sparklines.
| | 07:27 | If you want to get rid of the whole
group, you could right-click on any one of
| | 07:31 | them and simply choose Sparklines >
Clear Selected Sparkline Group.
| | 07:37 | So here and there, of course,
you want to get rid of these.
| | 07:39 | So room for a lot of experimentation
here as we insert these and again,
| | 07:43 | the basic idea as you can see is to give us
a quick visual read on data by putting
| | 07:49 | in effect a chart into a
cell by way of Sparklines.
| | Collapse this transcript |
|
|
8. Adding PicturesConverting a chart to a picture and adding new images| 00:00 | For either display or presentation
purposes or even printing purposes you may
| | 00:05 | want to alter this worksheet by
including some pictures and you might even want
| | 00:09 | to change that dynamic chart into a picture.
| | 00:12 | On the Insert tab in the ribbon
you'll see the choice Picture and in this
| | 00:18 | particular folder I've got some
photographs, I've got some logotype photographs,
| | 00:22 | an actual logo here.
| | 00:24 | Let's just start with the picture.
Perhaps it's just the picture of the vineyard.
| | 00:26 | I'll click Insert and nearly all as it goes
to a position that we don't want it to be in.
| | 00:31 | We can drag these usually simply by
clicking-and-dragging, and for resizing we
| | 00:37 | can drag the corners to change the
location of two of the edges or drag the side.
| | 00:42 | This, however, does distort the image
and it's better to getting a habit of
| | 00:46 | dragging just the corners because they
automatically get adjusted at the same aspect ratio.
| | 00:51 | If you work with shapes in Excel
or Clipart, same basic technique.
| | 00:56 | You'd like to position this in such a
way that it makes sense to you, make it
| | 01:00 | larger, smaller,
typically by dragging the corners.
| | 01:03 | Eventually, we will want to
put a border on there as well.
| | 01:06 | Let's also insert a logo.
| | 01:08 | Insert. Now in this case it is a
picture also. Many, many people and many
| | 01:12 | organizations have an official company logo.
| | 01:15 | You will actually double-click this to pop
it right there. Move it around a little bit.
| | 01:20 | I doubt if you want to rotate it, but
you could certainly do that as well.
| | 01:22 | Maybe this is not such a good idea.
Ctrl+Z. If you are rotating by the way, hold
| | 01:27 | down the Shift key if you somehow
insisted that that need to be at a certain
| | 01:31 | angle, because it does allow
you to rotate in 15 degree angles.
| | 01:35 | Now a less likely choice for a picture--
and this certainly is not a picture right now.
| | 01:40 | This is a dynamic chart.
| | 01:41 | It does reflect the data. If the
data is changing this chart changes.
| | 01:46 | For example, if I change the 84
here, that's the East January Sales.
| | 01:50 | If I make that be 200, imagine maybe
that's really a typo, but as I press
| | 01:55 | Enter watch the chart.
| | 01:56 | The data is in sync with the chart.
| | 01:58 | Now perhaps the data is frozen.
| | 02:01 | Maybe the data is history.
| | 02:02 | It's not going to change anymore.
| | 02:04 | We don't need this chart to be dynamic.
| | 02:07 | There are some advantages
to turning it into a picture.
| | 02:10 | The way we get there is not so
obvious but it's pretty easy.
| | 02:12 | Let's simply click the chart then right-
click, Copy, and we don't want to destroy this.
| | 02:19 | We'll leave it right here for the
moment. Either below this or to the right,
| | 02:23 | click in a cell and then on the Home
tab choose the arrow under Paste and then
| | 02:30 | paste this as a picture.
| | 02:32 | That looks almost the same as the chart.
| | 02:35 | I am going to zoom back a little bit
here with the Ctrl key and the mouse.
| | 02:38 | What's the difference?
| | 02:39 | The chart is still dynamic but we
don't need that feature anymore.
| | 02:43 | Notice when we do click on the chart too,
then we do have Chart Tools, Design,
| | 02:48 | Layout and Format choices.
| | 02:50 | We're not going to be
needing those anymore here.
| | 02:52 | Let's finally get rid of this chart.
| | 02:54 | Click down and we'll just press Delete.
| | 02:56 | This chart is a picture and recognize
now that instead of seeing those three
| | 03:01 | tabs for live charts
we've got a Format tab here.
| | 03:05 | So this is a picture in the same sense
that our logo is right here and notice
| | 03:10 | there we see that same tab in the
ribbon and on the picture over here the
| | 03:14 | same idea there as well.
| | 03:15 | So we now have three pictures here
and they have something in common in the
| | 03:19 | sense that as we manipulate them
they're all treated the same, they are all
| | 03:23 | pictures, and there are quite a variety
of things we will want to do with these
| | 03:27 | pictures once we have them here.
| | Collapse this transcript |
| Applying picture styles | 00:01 | In this worksheet we see a photograph,
a logo, and a chart as a picture. They are
| | 00:06 | all considered pictures.
| | 00:08 | If I click the picture below the data
here recognized that in the ribbon we see
| | 00:12 | the Picture Tools ribbon and
Format and we have these choices.
| | 00:16 | If I click on the logo we have the
same choices and if I click on this chart,
| | 00:20 | which is really a picture,
we have the same choices.
| | 00:23 | Although there are tons of different
features to explore here, the one that's
| | 00:27 | most critical is what we call Picture Styles.
| | 00:30 | Use this on the photograph first.
| | 00:32 | Picture Styles is located on the
Format tab in the Picture Tools ribbon.
| | 00:37 | Remember this is active only when
you have selected a picture, and the key
| | 00:42 | button here is the drop arrow
right here which reveals 28 variations.
| | 00:47 | Live preview is in effect. As we slide over
these choices here, we see quite a few variations.
| | 00:53 | Some of the borders on these,
particularly the one on the upper right at first,
| | 00:56 | is now overlapping the data. So we
might actually like that look, but we're
| | 01:00 | going to have to move the photograph.
| | 01:02 | So you can see these pretty
readily and see what's happening here and
| | 01:05 | decide what looks best.
| | 01:06 | Notice that on some of these
too we will also see shadows.
| | 01:10 | You'll see tilting like here.
| | 01:12 | Notice the shadow behind the lower
right-hand corner of that and here you can
| | 01:15 | barely see it but there is
shadow off to the left-hand side.
| | 01:18 | This slants the image with a
little shadow underneath and so on.
| | 01:21 | Certainly some of these options might
be appropriate for photographs and not
| | 01:25 | necessarily for logos, which are in a
certain sense things we want to be a
| | 01:29 | little bit careful with in
terms of manipulating them.
| | 01:31 | So one way or another we find
something we like here, maybe this one, maybe
| | 01:34 | that one, make a choice. There we go.
| | 01:36 | And for logo, something similar
perhaps although maybe be a little bit careful.
| | 01:40 | There's hard and strict rules about
how logos are used in some companies.
| | 01:43 | Maybe this is okay, maybe not, but you
can make your choice there as well too.
| | 01:48 | And for a chart, same general idea. This is a
chart as a picture though, not a regular chart.
| | 01:53 | With standard Excel chart you don't
have these options. And here too where you
| | 01:57 | might want to explore some of these.
| | 01:59 | This could possibly be part of a
presentation we're doing with PowerPoint, so
| | 02:03 | you can imagine a variety of choices here
that are going to fit your particular needs.
| | 02:07 | The ones that clip off the corners may be not
so good for charts, but again it's your call.
| | 02:12 | So the Styles feature has a lot of
visual appeal and we can apply them as we've
| | 02:17 | done here to a chart picture, to a
picture that's a logo, and to a picture that's
| | 02:21 | an actual photograph.
| | Collapse this transcript |
| Adding borders and effects to pictures| 00:00 | In this worksheet, we are seeing
three different kinds of pictures.
| | 00:03 | Photographs just below the data, a logo
just to the right of it, and a picture
| | 00:07 | of a chart, a chart that has been
converted into a picture. And in all cases,
| | 00:12 | styles have been applied.
| | 00:13 | Remember when you double-click on a
picture, it does activate the Picture
| | 00:17 | Tools ribbon and the Format tab, and we might
want to consider the look of the borders here.
| | 00:23 | Possibly on a border for this chart here,
click the drop arrow here for Picture Border.
| | 00:28 | We might want to change
this to be dashes possibly.
| | 00:31 | You'll see the effect immediately with
Live Preview here. Possibly this one.
| | 00:35 | I doubt if that's a
great choice for most people.
| | 00:38 | While we are here, we might also want
to change the Weight or the thickness of
| | 00:41 | the line, maybe a thinner line,
thinner than one we have selected earlier perhaps.
| | 00:45 | We've got some choices there as well.
| | 00:47 | I think on a photograph in
the lower left-hand corner.
| | 00:50 | We definitely want to consider that as well.
| | 00:52 | A white sky there seems to blend into
the worksheet. So here too and in no
| | 00:56 | particular order, maybe we want
to change the weight of a border.
| | 01:01 | Now it's a simple line.
| | 01:03 | We might want to make it dashed or not,
maybe a subtle dash like this maybe, and
| | 01:08 | possibly, come back again, change
the color of that, as appropriate.
| | 01:12 | Again, fairly self-
explanatory once you try a few of these.
| | 01:15 | Logo, maybe we don't
need to make a change there.
| | 01:17 | So Border Effects are certainly available.
| | 01:20 | We also have Picture Effects here too.
| | 01:23 | Would this look better
if we had shadows on them?
| | 01:26 | The number of choices here just gets
overwhelming at times, if you let it
| | 01:30 | become overwhelming.
| | 01:31 | I think many, many times you
don't need to use some of these.
| | 01:33 | A Reflection might be kind of a cool
feature that you like, and as you slide
| | 01:37 | over some of the variations,
like how about that logo right now.
| | 01:40 | It's got a reflection under it.
| | 01:41 | There is a different kind of reflection.
| | 01:43 | it goes deeper and it's right next to the logo.
| | 01:46 | How about sliding down, move
the reflection away from it?
| | 01:49 | You didn't know you have that many choices.
| | 01:51 | How does that look with a photograph possibly?
| | 01:53 | Back to Picture Effects,
consider Reflection. How about Glow?
| | 01:58 | That's another one.
| | 01:59 | Picture Effects, maybe we will
try it on the chart here. Glow.
| | 02:03 | surround the image with a
glow color. Lots of capabilities.
| | 02:08 | In addition to these here under the
Style section, we also have Corrections here
| | 02:14 | and Sharpen and Soften the focus.
| | 02:17 | Now, for many people that's not a great
choice, but here and there it gives us
| | 02:20 | a certain kind of artistic flair that you
might like. Brightness and Contrast as well.
| | 02:25 | The key idea here is not to remember
the names of these but just to slide over
| | 02:28 | the choice and see how it looks to
you and where do you think that would be
| | 02:31 | appropriate in this given worksheet, and
there's some color choices as well here too.
| | 02:35 | As we look at some of these here, how
is that for a choice? Maybe, maybe not.
| | 02:40 | And a new feature in Excel 2010,
Artistic Effects, and once again, it might be
| | 02:45 | hard pressed to justify why would we
use some of these, but here and there it
| | 02:49 | does have that look that appeals to
you and might make sense in a given
| | 02:54 | worksheet, either for
presentation or printing purposes.
| | 02:57 | Tons of choices available here on the
Format tab when we work with pictures.
| | Collapse this transcript |
|
|
9. Dynamically Accentuating Data with Conditional Formatting Creating value-based formatting using logical operators| 00:00 | If you need to apply formatting
characteristics to certain cells and those
| | 00:04 | cells are dynamic, if they might
change, a great tool we have available is
| | 00:09 | Conditional Formatting.
| | 00:10 | It's found on the Home tab.
| | 00:12 | In this worksheet, imagine that in
Column H, these Performance Ratings,
| | 00:16 | we want those who have a value of 5,
that's the best rating, we want those to
| | 00:20 | stand out more prominently.
| | 00:22 | We might select the entire column.
| | 00:24 | Many, many times when you're applying
formatting capabilities, selecting the
| | 00:28 | column makes the process happened
faster and more smoothly, provided you have
| | 00:32 | nothing else in the column of relevance.
| | 00:35 | Let's apply Conditional Formatting.
| | 00:37 | Basic concept: highlight the cells
here that are Greater Than, Less Than,
| | 00:42 | Between, in our case let's say Equal To.
| | 00:44 | We want the ones that are
equal to 5 to stand out.
| | 00:47 | Initially, this says 3.
| | 00:48 | We will just change it to 5 and
immediately, you see something occurring in
| | 00:53 | Column H. Light red fill with dark red text.
| | 00:56 | Well, possibly we want that
or maybe we choose green fill.
| | 01:00 | We don't really have Live Preview here
but at least we can make a change in the
| | 01:03 | dialog box that's still open,
so we can explore some of these.
| | 01:07 | If these are not to your liking, you
certainly can go into Custom Format and
| | 01:11 | then apply any combination of Fill
patterns and Colors and Borders and Fonts
| | 01:16 | and even in some situations,
numerical display differences to get that look
| | 01:21 | that you desperately need.
| | 01:22 | I'll just go with this one here that
we started off with. That's just fine.
| | 01:27 | And it's worth noting too that it is dynamic.
| | 01:30 | I suggested that earlier.
| | 01:31 | If this 5 here becomes a 4,
it no longer is highlighted.
| | 01:36 | If this 4 becomes a 5, as soon as I
press Enter, it does have that highlighting.
| | 01:41 | Then you can certainly revisit this
and apply different colors if you wish to
| | 01:46 | different values there, or make the
ones and twos together with certain amount.
| | 01:51 | If it's less than 3, you can make
those ones and twos be a certain column.
| | 01:55 | You also notice a choice in
here called Duplicate Values.
| | 02:00 | Now, Column A doesn't have any
duplicates as far as I know, but I will create
| | 02:04 | one on purpose here.
| | 02:05 | Here is Dan Calhoun and I'll drag it
down to here, holding down the Ctrl key,
| | 02:10 | and there are two Dan Calhouns.
| | 02:12 | Now we possibly would do this
checking like the Social Security column,
| | 02:17 | Phone Number column.
| | 02:19 | In situations where you are curious
about whether there are duplicates or
| | 02:23 | maybe you don't want them, this will not get
rid of them but at least it highlights them.
| | 02:27 | So in Column A, we will choose
Conditional Formatting > Highlights Cell Rules >
| | 02:31 | Duplicate Values and
immediately, you see what's happened.
| | 02:35 | And Dam Calhoun is there twice,
both of those are highlighted.
| | 02:39 | So that might have some potential use as well.
| | 02:41 | Let me go back here and
press Ctrl+Z to get rid of that.
| | 02:47 | Now looking at a Salary column like
Column G, under Conditional Formatting in
| | 02:52 | addition to these basic kinds of rules,
which are almost self-explanatory in all
| | 02:56 | cases here, we also have Top/Bottom Rules.
| | 03:00 | We might want to choose the Top 10 Items.
| | 03:04 | If you're familiar with the parallel
feature in filtering, you know that the 10,
| | 03:08 | you can keep it that way, of course,
but you could make it be any other number
| | 03:11 | that you might want.
| | 03:12 | I want to see the top 20
salaries here highlighted.
| | 03:16 | If you have other highlighting,
you probably want to use a different
| | 03:18 | scheme . Maybe this one.
| | 03:20 | Do we have any salaries
here that are in the top 20?
| | 03:23 | Yes, we do, they are selected.
| | 03:25 | And sure enough, again, the logic here, if not
totally self-explanatory, it's pretty close.
| | 03:30 | Top 10% is a variation on that and
that too could be a different number.
| | 03:36 | So percentage-wise a
different set there potentially.
| | 03:41 | Conditional Formatting, Top 10.
| | 03:43 | Also, Above Average, Below Average,
easy to get to and again, pretty much
| | 03:48 | self-explanatory there.
| | 03:49 | Right now, the highlighted cells
are those that are above average.
| | 03:53 | If the salaries are changing at
different times, of course that average is a
| | 03:57 | moving target and some of these may
change on you as you're making entries.
| | 04:02 | There are times too when you say "Well, I don't
want Conditional Formatting to be used anymore."
| | 04:07 | Say, we don't want this in Column G.
So one approach would be to use
| | 04:11 | Conditional Formatting and clear the
rules. Possibly from the entire sheet
| | 04:17 | which would make sense in some cases or
maybe this example here, just Column G.
| | 04:22 | Clear Rules from Selected Cells. There we go.
| | 04:25 | So you certainly have control over
that. Easy-to-use in its simplest form,
| | 04:30 | either by selecting Highlight Cell
Rules and a lot of those variations or
| | 04:34 | Top/Bottom Rules with those variations.
| | Collapse this transcript |
| Creating value-based formatting using data bars and color scales | 00:00 | With Conditional Formatting, not only
can you control the appearance by using
| | 00:04 | color backgrounds and font
differences, you can also use data bars.
| | 00:09 | There are 12 different kinds of those,
color scales, 12 different kinds of
| | 00:14 | those, and icon sets, 20
different variations there.
| | 00:17 | Suppose in Column G, we want to
augment the actual data there with the visual
| | 00:23 | component that adds color.
| | 00:25 | Now the Conditional Formatting feature,
by the way, is right on top of the
| | 00:27 | column that I'd like to use, so it's
a good idea when you are working with
| | 00:31 | conditional formatting, sometimes to
scroll leftward or rightward to move your
| | 00:34 | data so that's not under that feature.
| | 00:37 | Use Conditional Formatting > Data
Bars > Gradient Fill. Live preview's in effect.
| | 00:44 | As I slide over these, look at Column G
now. Colors. Just to see what's happening.
| | 00:49 | By the way, if the column is wider,
which I will make wider in a second, these
| | 00:53 | bars don't appear to the left.
| | 00:55 | They don't end to the left of the numbers.
| | 00:58 | Jump out of here for a
second. Make Column G wider.
| | 01:01 | The Color Bars are still going to be
wider and they overlap the numbers.
| | 01:04 | Not everybody likes that feature,
but that's the way it's implemented.
| | 01:08 | So we see what's happening here.
| | 01:10 | Then there is solid fill as
well too, if you'd prefer that look.
| | 01:14 | Once again, more choices than a
lot of us ever would've expected.
| | 01:17 | But that tells us in a
graphical way how large the salary is.
| | 01:21 | Now, you might want to have a graphic
here that doesn't show the real salary.
| | 01:26 | It simply shows the bars.
| | 01:28 | So without much to do here, we could
right-click on Column G and go to Format
| | 01:34 | Cells and on the Font tab, choose
White. And we don't see anything there
| | 01:40 | unless it's highlighted.
| | 01:41 | But let's come back and
apply Conditional Formatting.
| | 01:44 | We will use these Data Bars on the
solid maybe and you get this effect and if
| | 01:51 | we click outside of it, you get this effect.
| | 01:52 | Now unfortunately, you do see some of
the numbers overlapping there, but we
| | 01:56 | could choose possibly a different color. You
might want to experiment with that a little bit.
| | 02:00 | I am not sure if that's the ideal
solution either but that has some
| | 02:03 | potential for some people.
| | 02:04 | I am going to press Ctrl+Z to undo that.
| | 02:08 | Now in addition to the Data Bars, and
again there are 12 choices, we also have
| | 02:13 | Color Scales and here
different colors are being used.
| | 02:18 | One of the overriding themes of
colors as we see them here and we'll also
| | 02:21 | see these with icon sets, the red-yellow-
green traffic light kind of pattern is dominant.
| | 02:28 | In other words, we see it most often.
| | 02:30 | But one by one as you explore these, you
want to be asking yourself, how well do
| | 02:34 | these differentiate the numbers?
| | 02:37 | As we look here, you will recognize,
for example, if we choose this one,
| | 02:42 | those are of the same shade, but these are
of a different shade here, for example.
| | 02:46 | So the lower ones are darker, the ones
just above are slightly lighter, and how
| | 02:51 | about the ones in the middle?
| | 02:52 | In other words, is this
color scheme ringing true?
| | 02:55 | The real low ones are here and so we
are seeing a five-color scheme here.
| | 03:00 | To me, this would not be a good choice here.
| | 03:02 | I am going to go back to Conditional Formatting.
| | 03:04 | If I wanted to use Color Scales, I am
more likely to use those where the color
| | 03:08 | differentiation is a little stronger.
| | 03:11 | I'm thinking maybe the
first or the second one here.
| | 03:14 | Now again, you might have to
explain at least briefly to an audience
| | 03:17 | what's going on here.
| | 03:18 | But as we look at this first choice
here, Green - Yellow - Red Scale, you
| | 03:22 | could see that the higher numbers are darker
green. Those that are not so high are lighter green.
| | 03:27 | Then Row 13, for example, this is
65,000. That's obviously a lighter green than
| | 03:32 | what we see in Row 7 and 8.
| | 03:34 | Farther down the list here, if we were
to stick with that scheme, you will see
| | 03:39 | some little redder or pinker or darker.
| | 03:41 | There's one that's a lot darker than the others.
| | 03:44 | So we see what's happening there.
| | 03:46 | I guess part of the idea might be is
this for your eyes only or is it for others?
| | 03:52 | Is the scheme easily described or
should you describe it in a note or a comment
| | 03:57 | to the right? Or maybe it's been used
by other people in similar worksheets.
| | 04:01 | So we have to make some decisions about that.
| | 04:03 | Let's come back to Conditional
Formatting and also take a look at Icon Sets.
| | 04:08 | Quite a few choices here and
these are pretty obvious once we see them.
| | 04:12 | Now I have Color Scales as well as Icon Sets.
| | 04:15 | That's probably overkill but
I have seen them being used together.
| | 04:19 | You might want to do that.
| | 04:20 | If you prefer not to, what you
should do in this case is what?
| | 04:24 | Clear the rules from the selected cells,
come back again, and let's just focus
| | 04:29 | on Icon Sets for a bit.
| | 04:31 | Once again that Green - Yellow - Red
pattern is the domino and we see it
| | 04:35 | everywhere and green is good or high,
I guess you would say. Red is bad or low.
| | 04:40 | Maybe good and bad are not the
terms we are going to use here, but you
| | 04:44 | quickly get the idea as to how the data is
being interpreted using these various symbols.
| | 04:49 | If you were to choose three-
directional arrows, the data is analyzed and
| | 04:53 | divided into thirds. Same here.
| | 04:56 | If you choose four or any of these
options here that have four choices, then
| | 05:01 | it divides the date into quartiles,
based on the high and low values and all
| | 05:06 | the ones in between.
| | 05:07 | So we see different variations here, different
schemes as we slide over some of these choices.
| | 05:13 | Then round and every one of
these has a slightly different name.
| | 05:16 | This is Traffic Lights (Unrimmed) and
Traffic Lights (Rimmed), just for the
| | 05:20 | record, and of course once you try a
few of these it makes perfect sense and
| | 05:26 | you will decide what to use here.
| | 05:27 | Now in these examples too, if you simply
want to show the relative position of a
| | 05:32 | salary, for example you might choose
this one and then come back to Column G,
| | 05:37 | maybe not highlight G1.
| | 05:39 | But from here downward and you can hold down
the Shift key, double-click the bottom edge.
| | 05:44 | We could make the font here, right-
click, be white and we are not showing
| | 05:51 | any salaries at all.
| | 05:52 | So for presentation purposes we
might want to center everything there.
| | 05:56 | We don't know the salary exactly, but
we do know which quintile it falls into.
| | 06:01 | Top fifth, bottom fifth,
etcetera. If you like that scheme.
| | 06:04 | I am going to press Ctrl+Z
to bring back the numbers.
| | 06:07 | I think the combination
of both perhaps works best.
| | 06:11 | But again a lot of choices.
| | 06:12 | There are 20 different kinds of Icon
Sets that allow you to bring out the data.
| | 06:17 | As with previous examples, these are dynamic.
| | 06:20 | When the numbers change, here and there
some of the indicators will change and
| | 06:24 | whether you want to divide your
data into thirds or fourths, fifths.
| | 06:28 | We've got quite a few choices
here amongst these 20 variations.
| | Collapse this transcript |
| Creating formats based on formulas| 00:00 | In this worksheet in column H
Conditional Formatting is in effect.
| | 00:04 | Those performance ratings that are five
have a pink background and a red font.
| | 00:09 | And nothing wrong with that, but what if
you'd like to highlight the entire row when
| | 00:13 | that occurs? In other words, all of the
data from Nathaniel Nichols here and all
| | 00:17 | of the data for Erik Pratt and so on.
| | 00:19 | Let's select all of the columns,
all of the rows and columns first.
| | 00:24 | Now, we can alter the existing
rule or we can get rid of it.
| | 00:28 | It might be best first
simply to get rid of this.
| | 00:31 | So as we approach Conditional
Formatting and maybe we have no other conditional
| | 00:35 | formatting rules in effect, we might
just clear the rules here from the entire
| | 00:40 | sheet and now let's revisit
Conditional Formatting and set up a new rule.
| | 00:46 | We want to use a formula to
determine which cells to format.
| | 00:50 | Now the formula is not
going to be an obvious one.
| | 00:53 | But once you see a few of these,
you'll be able to create your own. =H1.
| | 00:59 | Now H1 doesn't even have a
performance rating in it. It's text.
| | 01:03 | When you select this, you'll see dollar signs.
| | 01:06 | Now, H1, think of it as a surrogate or a
substitute for all cells in the selection.
| | 01:13 | Whenever the column H entry has a
five in it, we want to apply a format.
| | 01:19 | So by pressing the function key F4 or
manually manipulating these dollar signs,
| | 01:24 | we want the dollar sign in front of the
H, but not in front of the one and now we
| | 01:29 | indicate =5 for every single row here.
| | 01:34 | Every time the column H entry is equal
to five, we want to apply a format and
| | 01:40 | we'll just make it yellow
background for the moment.
| | 01:44 | Click OK, click OK, and we see what's happening.
| | 01:48 | And as you might expect, if this
rating becomes a four, we'll lose
| | 01:52 | the highlighting there.
| | 01:53 | This becomes a five in the second
example here and that becomes highlighted.
| | 01:57 | Of course you can choose
your own color scheme there.
| | 02:00 | Once again by selecting these cells
here and choosing Conditional Formatting
| | 02:06 | we can come back and revisit the rule.
| | 02:08 | We can manage the rules.
| | 02:10 | We can see them again.
| | 02:12 | Edit the rule if we need to.
| | 02:13 | We don't need to in this
case but there is the rule.
| | 02:16 | So you can use formulas in
creating Conditional Formatting.
| | 02:21 | Another example here is in this same
worksheet and this probably in a real
| | 02:25 | life situation would be in a different
worksheet but let's focus on just this data here.
| | 02:30 | Based on what's going on here, we want
to know when the Shipping Date is more
| | 02:36 | than five days after the Order Date.
| | 02:38 | We've decided that everything
should be shipped within five days.
| | 02:42 | If it's not and it's
certainly hasn't been in all cases,
| | 02:45 | we want these dates to stand
out a little more prominently.
| | 02:49 | So in our logic here, we are going to
be comparing the entries in column M
| | 02:54 | with the entries in column L. So let's
select column M. Conditional Formatting.
| | 03:00 | A new rule here.
| | 03:04 | Use a format to determine which cells to format.
| | 03:07 | Here too not so obvious for once we see
that it's going to make perfect sense. =M1.
| | 03:13 | Now we don't want this to be absolute.
| | 03:15 | I'll press F4 repeatedly.
| | 03:17 | So I probably could have
typed this just as fast. > L1.
| | 03:22 | This time I'll just type it.
| | 03:23 | Now, if we are only looking for those
situations where a column M entry is more
| | 03:28 | than five days, we want this to read =M1 > L1+5.
| | 03:33 | So if it's more than five days
we want to apply a format here.
| | 03:39 | Maybe use red or
something, there we go. Click OK.
| | 03:42 | Any kind of formatting feature you
wish of course. Click OK and all those
| | 03:47 | entries in column M where the shipping
date is more than five days after the
| | 03:52 | Order Date are standing out.
| | 03:53 | Looks like we've got quite a few here
and you can quickly do the math in your
| | 03:56 | head and see what's going on.
| | 03:58 | That too as you would expect with
Conditional Formatting is dynamic.
| | 04:02 | If a mistake was made on one of these
shipping dates here and this really was the 12th,
| | 04:06 | now that is five days but it's not more
than five days, as soon as I press Enter here,
| | 04:11 | that no longer will retain that color.
| | 04:14 | One more aspect of Conditional
Formatting, if you are looking at a
| | 04:18 | worksheet that you haven't seen in a
long time or maybe never and you see
| | 04:22 | colors you suspect that there
probably are some conditional formatting
| | 04:25 | rules here and there,
| | 04:27 | on the Home tab and in the
Editing Group extreme right, click Find & Select.
| | 04:33 | If you'd like to track down where
Conditional Formatting exists. you can go to
| | 04:38 | the choice Conditional Formatting.
| | 04:41 | Now unfortunately with a lot of Color
Schemes, this might throw you a little bit.
| | 04:45 | It looks like there's Conditional
Formatting here. This is all highlighted.
| | 04:49 | That doesn't tell you what the rule is
but at least you know that it's there and
| | 04:52 | column M is highlighted as well.
| | 04:54 | So you still might have to do some
more sleuthing to figure this out.
| | 04:58 | But if you click on a given cell here
or in this case you probably would click
| | 05:02 | column M. But even if you click on a cell,
you might say well how did that get there?
| | 05:07 | What happens sometimes people will
see a red cell like this, they'll go to the
| | 05:11 | Home tab, go to the Fill Color bucket,
and say "Well I want that to be no fill or
| | 05:17 | blue or green or something."
| | 05:18 | They'll make a choice here
and nothing happens at all.
| | 05:22 | So what do we do here? If we simply
click on one of these cells, go to
| | 05:26 | Conditional Formatting and manage the
rules, we can see what rule is in effect
| | 05:32 | there and that helps us
figure things out as well too.
| | 05:36 | So there is no question that
Conditional Formatting has some real dynamic
| | 05:39 | aspects of it and previously in this
movie we saw few different examples of how
| | 05:43 | to use formulas to build
conditional formatting rules.
| | Collapse this transcript |
|
|
10. Specialized Power Formatting Techniques Hiding repeating titles in a column to clarify printed output| 00:00 | If you want to print this worksheet,
it might be more readable or you might like
| | 00:04 | the visual effect of not seeing
the department names being repeated.
| | 00:08 | In other words, let's have only the
first occurrence. So manually we could
| | 00:12 | certainly come along and get rid of
these subsequent ADCs here, do the same
| | 00:16 | thing with admin training. But how long
is this going to take? Quite a while.
| | 00:21 | There is a formatting tip,
| | 00:22 | it's actually a trick, that will do
this much-much faster. It is based on the idea
| | 00:28 | that we can insert logic that in effect
will allow us to compare each cell here
| | 00:34 | with the cell above it,
using Conditional Formatting.
| | 00:38 | Now, after clicking column A let's go to
Conditional Formatting and establish a rule.
| | 00:44 | Use a format to determine which cells
to format. Equal A1, don't really have to
| | 00:50 | capitalize it, equal.
Now what is the cell above A1?
| | 00:55 | Strangely enough it's the very last
cell in the worksheet and if you look
| | 00:59 | outside this dialog box just for the
moment, over in cell I1 I put in the total
| | 01:04 | number of rows in an Excel worksheet, 1,048,576.
| | 01:09 | So, if cell A1 is equal to one above
it and that's A1048576, if any of these
| | 01:19 | cells in column A is equal to the one
above it, that means it's a duplicate and
| | 01:23 | we want to use a format that doesn't
show the text entry and what we are going
| | 01:28 | to be doing here is changing the format.
| | 01:30 | Now we are not really changing the
data but we are going to make the font be white.
| | 01:34 | So stating this over again in English,
| | 01:37 | if any cell in column A is the same as
the cell above it make its font to be white.
| | 01:44 | And we'll click OK.
| | 01:47 | Now while they are selected you can
see that the names are still there,
| | 01:50 | the entries are still there.
| | 01:51 | Click outside of this.
| | 01:53 | On the printed page and I am just going
to jump into Print Preview with Ctrl+F2.
| | 01:57 | You will see how it will
look on the printed page.
| | 02:00 | In other words, we don't see the
recurring titles and for many, many people
| | 02:04 | that's a lot easier and simpler to read.
| | 02:06 | By the way when you press Ctrl+F2 to
get a Print Preview you can immediately
| | 02:10 | jump back into the worksheet
environment with Escape. There we are.
| | 02:13 | So the data is still here. You can
certainly see it in the Formula Bar and if you
| | 02:17 | highlight the data you can
see the text following through.
| | 02:20 | We haven't destroyed it.
| | 02:21 | We've simply changed the font to
be white for printing purposes.
| | Collapse this transcript |
| Restoring missing title data in a column| 00:00 | Although this worksheet might be
ideally set up for printing, recognize that in
| | 00:04 | column A recurring
department names don't exist here.
| | 00:08 | So it's easier to read
when it's on the printed page.
| | 00:11 | However, if we sort this data say by
years, contract sales, or any column,
| | 00:16 | we have lost potential information in column A
and we can't put the data back together again.
| | 00:22 | So what we need to do sometimes is
actually fill in this information here
| | 00:26 | with ADC and all this information here with
the phrase admin training and so on and so on.
| | 00:31 | This is based by the way on a real
life situation a few years ago where a
| | 00:35 | company that I was working with got
data from their Thailand office frequently
| | 00:39 | like this and they needed to fill
in those blank cells in column A.
| | 00:44 | Now it's based on a simple little
concept that you might have encountered in
| | 00:47 | your other use of Excel.
| | 00:49 | Some scattered data out here in columns J,
K and L. Maybe in each case here--
| | 00:53 | now none of these are labeled or
anything, so it's somewhat academic.
| | 00:56 | But in this cell here, I would like
to subtract the two cells above it.
| | 01:00 | I want to do the same thing
here and the same thing here.
| | 01:03 | So we can select different cells at
the same time by using the Ctrl key, in
| | 01:07 | no particular order.
| | 01:08 | This cell is selected. I am going to
hold down Ctrl and click here and hold down
| | 01:13 | Ctrl and click here and I'm writing a
formula now for cell J4=J2-J3, and rather
| | 01:23 | than pressing Enter and I
am going to press Ctrl+Enter.
| | 01:26 | So what have I done? I put the same
relative formula in all the three cells.
| | 01:31 | This subtracts the two cells
above it, so does this, so does this.
| | 01:36 | Now with that thought in mind, let's
take a look at column A and start off by
| | 01:41 | using a feature not so obvious.
| | 01:43 | We would like to eventually put
formulas in each of these cells here that get
| | 01:48 | data from the cells above. But how
do we select just the blank cells?
| | 01:53 | After clicking column A, on the Home tab
the extreme right-most group called
| | 01:58 | Editing has a feature called Find &
Select and the least obvious choice here
| | 02:04 | perhaps Go To Special and here is
a choice called Blanks. Click OK.
| | 02:11 | It's selecting the blank cells only.
| | 02:14 | For the moment A3 is the active cell.
| | 02:18 | So I am going to write a formula here.
Equal, up arrow, in other words A2, and I am
| | 02:24 | going to press Enter if I only wanted to
put it into that cell, but all the other
| | 02:29 | blank cells that have been
highlighted, I want the same kind of formula.
| | 02:32 | In other words, I want everyone these
blank cells to have the same formula
| | 02:36 | as the one above it. So I am going to
press Ctrl+Enter and there we see how
| | 02:41 | they are all filled in.
| | 02:42 | But they're all formulas so there's
one more step we need to take here.
| | 02:46 | Click column A and we can do this in a
number of different ways but we simply
| | 02:50 | want to copy this data and paste it
as values and you can do this very fast
| | 02:55 | simply by taking the right edge and hold
down the right-mouse button. Drag it
| | 03:01 | temporarily into column B and
then right back on top of itself.
| | 03:05 | Let go. Copy here as values only.
| | 03:08 | So we have cleaned up
the data. It's now readable.
| | 03:11 | Along the way we could've done some
special formatting as well but we now
| | 03:14 | have the data in shape.
| | 03:16 | It's the reverse of what we had done in the
previous movie but now the data is useful.
| | 03:20 | It can be sorted and we can come back
and actually print it if we wanted to and
| | 03:24 | hide the data or turn the fonts into
white font as we did in the previous movie.
| | Collapse this transcript |
| Displaying large values without using formulas| 00:00 | In this worksheet the entries in Column
B and in Column C are in the millions.
| | 00:04 | That's certainly
appropriate for the data at hand.
| | 00:07 | But there are times when you might want
to display numbers of this magnitude as
| | 00:13 | decimals and one approach certainly
would be write formulas in adjacent cells
| | 00:16 | and then redisplay these.
| | 00:18 | Suppose for example on populations,
if you did know the population of
| | 00:22 | California, as you see it here,
you might say it as 37 million or possibly 37.1 million
| | 00:28 | Texas has 24.8 million. That's
commonly used. When you're dealing with
| | 00:34 | dollar figures perhaps similarly.
| | 00:36 | That's what? 4.3 million.
| | 00:39 | Whether it's a millions or thousands
or billions you may want to display this
| | 00:43 | data in a decimal way without using formulas.
| | 00:46 | Let's just highlight a few cells here.
| | 00:48 | We will do it with the Sales for
starters here. Let's display these as millions.
| | 00:53 | Right-click, Format Cells, one of the
many ways we can get into this dialog box,
| | 00:57 | and the not so obvious way to
do this is to create a custom format.
| | 01:02 | Usually this process can be
simplified if we click the number category
| | 01:06 | first then click Custom.
| | 01:09 | Now, we don't need to know all the
details about custom formatting. What I would
| | 01:13 | suggest we do here is simply use an
existing format, modify it, and make sure it
| | 01:18 | ends with .0 and then the unusual
step here of putting in two commas.
| | 01:26 | If we put in one comma, we will be
displaying this as thousands. Two commas as millions.
| | 01:32 | So each comma will suppress the display of
three characters to the left of the decimal.
| | 01:38 | There we go.
| | 01:39 | This is 4.3 million. That's the way we would say it.
| | 01:43 | Now obviously if we are going to do
this for the entire column, eventually we
| | 01:47 | want to change our heading to
make sure it's clear to everybody.
| | 01:49 | But the other important aspect to
this is as we click on cell C3 ,look in
| | 01:55 | the Formula Bar. Or if you happen to
double-click. The value hasn't really
| | 01:59 | been changed at all.
| | 02:01 | It's all about the display. And once
again that format, right-click, Format
| | 02:06 | Cells, an easy way to get there.
| | 02:07 | We see what it is now: a custom
format that ends in comma-comma.
| | 02:12 | If it somehow makes sense you to show
these in thousands, then we want to show
| | 02:17 | only one trailing comma
and it would look like this.
| | 02:20 | But certainly we wouldn't have them mixed
together and you'll decide how to label them.
| | 02:24 | So it does make more complex number-
laden worksheets easier to read, but with
| | 02:30 | proper identification it would make sense.
| | 02:32 | Maybe in column B here we'll do
all these at once. Click right here.
| | 02:37 | Now we've already created this one
custom format so by right-clicking, going
| | 02:41 | into Format Cells here, we can go right
into Custom and we'll probably see it at
| | 02:46 | the bottom of the list there.
| | 02:48 | There it is right there. Click OK
and again we probably should adjust the
| | 02:53 | title to say "in millions."
| | 02:55 | So it certainly makes the
worksheet easier to read and handle.
| | 02:58 | But remember the pure value is there.
| | 03:01 | We're simply using a common way
to display this using that unusual custom format.
| | Collapse this transcript |
| Formatting column/row titles| 00:00 | In accounting type worksheets, there are
times when you might want to put in two
| | 00:04 | titles in the same cell.
| | 00:06 | You might want to have a title for our
regions here as well as a title for these
| | 00:10 | reporting dates or sale
dates, whatever they might be.
| | 00:13 | So I am going to type Reporting Date.
I might want to change the size of the
| | 00:18 | font eventually. Pressing Alt+Enter,
and in the same cell, I want to put in
| | 00:22 | Region, press Enter.
| | 00:24 | That's not exactly very clear as to
what's going on here, but we can do
| | 00:29 | something that has the
unofficial name of elbow formatting.
| | 00:32 | First of all, what we might do is
highlight these cells and then make sure they
| | 00:36 | appear in the top of the cell.
And then possibly making Column A wider.
| | 00:41 | We'd like to put in a diagonal here.
| | 00:43 | So let's do that that with a Border feature.
| | 00:46 | So we could do this on a
couple of different ways.
| | 00:48 | One way might be to right-click and go to
Format Cells or simply click here and press Ctrl+1.
| | 00:53 | In either case, we will end up
with the Format Cells dialog box.
| | 00:58 | We want to go to the Border tab
and choose this diagonal right here.
| | 01:03 | Now it's gong to look a little strange at first.
| | 01:05 | We don't necessarily need to have black
borders on the others, but we could use that
| | 01:09 | and maybe a thinner
line would make sense here.
| | 01:13 | Choose the thin line here and
then come back and click these.
| | 01:16 | We are not quite there.
| | 01:17 | The next thing we need to do is to put
some spaces in front of Reporting Date.
| | 01:21 | And a little bit of guesswork here.
| | 01:23 | I'm simply clicking in the Formula Bar,
pressing some Spaces, and as I do this I
| | 01:28 | would say okay that looks pretty
good about there. Press Enter. And maybe
| | 01:33 | that's not quite good enough. So making
the column wider. Come back again possibly.
| | 01:37 | You can double-click
in the cell and do this as well.
| | 01:40 | So you might have to
experiment with a little bit.
| | 01:41 | It's not a major feature, yet it's one
of those things that clarifies the data.
| | 01:46 | Possibly changing the font color here
might be helpful as well, but it just
| | 01:50 | shows how we can provide labels within
the same cell for both column and row data.
| | 01:55 | It's called elbow formatting.
| | Collapse this transcript |
| Formatting a list for double-spaced printing| 00:00 | This worksheet has about 400 rows of data.
| | 00:04 | Maybe you're ready to print this.
| | 00:05 | Of course, it's always a good idea to
go to Print Preview and Ctrl+F2 too is very
| | 00:09 | helpful and it instantly
gives us a print preview.
| | 00:12 | It might occur to you as you look at
this that this could be more readable and
| | 00:16 | since some of the people who are
viewing this might want to make some
| | 00:18 | annotations, this would be better
if we could print this double-spaced.
| | 00:22 | When you enter Print Preview by the
way you can immediately return to the
| | 00:25 | worksheet environment with the Escape key.
| | 00:28 | Now one approach could be, and if you
had only a small amount of data this
| | 00:31 | would make sense, you could click Row
2 and then holding down the Ctrl key
| | 00:37 | click Row 3, Row 4, Row 5.
| | 00:39 | We'll get about 500 rows.
| | 00:41 | But if you did have a small number of
rows, you could then right-click and
| | 00:45 | Insert and you would get
empty rows here and there.
| | 00:48 | And then just showing how the
Print Preview might look, Ctrl+F2.
| | 00:52 | We could take a look at that.
| | 00:53 | You see just the beginning of it there.
| | 00:54 | That's the look that we are after.
But obviously that's not appropriate or
| | 00:58 | feasible here in a very sensible way.
| | 01:01 | So I am going to press
Ctrl+Z to make a change.
| | 01:03 | A really fast way to do this and not a
standard built-in feature is simply to
| | 01:09 | select the entire worksheet and take
any row boundary between the row numbers
| | 01:14 | and simply drag this to be about twice as tall.
| | 01:17 | It sure looks like double-spaced to me.
You can make it triple if you wanted to.
| | 01:22 | In other words, nobody is stopping you
from dragging this to a specific height.
| | 01:25 | Let's do another quick
Print Preview here with Ctrl+F2.
| | 01:30 | It sure looks like double-spaced.
| | 01:30 | We are ready to print it.
| | 01:32 | We go ahead and print and that's it.
| | 01:34 | After doing that, we'll
press Escape. Come back here.
| | 01:37 | We could probably just do and
undo, because you can't unprint.
| | 01:40 | It's not going to undo that.
| | 01:41 | We are back to here. We are all set.
| | 01:44 | So double-spacing is real fast
and easy for printing purposes.
| | 01:48 | It wasn't really double-spaced.
| | 01:50 | We just made the rows taller.
| | Collapse this transcript |
|
|
ConclusionFinal thoughts| 00:00 | After viewing this course, you might
want to explore other Excel offerings
| | 00:04 | available at lynda.com.
| | 00:06 | Click Software, go to the letter E,
choose Excel, and you'll see an ever-growing
| | 00:11 | list here of courses available.
| | 00:13 | You might want to check out my Excel
2010 Power Shortcuts tip or Bob Flisser's
| | 00:18 | Essential Training course, and quite
a few others. Revisit this site often.
| | 00:22 | There will be new courses in this series.
| | 00:25 | Thank you for watching!
| | Collapse this transcript |
|
|
