IntroductionWelcome| 00:04 | Welcome to New Features of Excel 2010.
| | 00:07 | I'm Bob Flisser, and I'll be
your instructor for this course.
| | 00:11 | I'm going to show you the new Backstage
view and templates that live up to the
| | 00:14 | promise of being easy to use.
| | 00:17 | I'll also show you enhancements to
data analysis as well as integration with
| | 00:21 | enterprise computing.
| | 00:23 | We'll look at ways of converting
workbooks between different versions of Excel
| | 00:27 | and options for sharing workbooks
within your company and in the cloud.
| | 00:31 | We'll also see tiny charts that
actually fit inside a single cell.
| | 00:36 | If you ever needed to insert graphics
or math equations in your worksheets,
| | 00:40 | there are some great new
features I'm sure you'll like.
| | 00:43 | You can even compile data from many
different sources, even enterprise
| | 00:47 | databases, into the same worksheet and
analyze them as easily as if they were on
| | 00:52 | your own hard drive.
| | 00:54 | Since this is a New Features course,
I'm going to assume you already use
| | 00:58 | Excel and just want to know what the
new features of the 2010 version are.
| | 01:02 | If you are new to excel, you should watch
some of the Essential Training movies
| | 01:06 | on lynda.com first.
| | 01:08 | So, let's get started learning the
new features of Microsoft Excel 2010.
| | Collapse this transcript |
| Comparing Excel 2007 and Excel 2010| 00:00 | Before we start clicking and typing
around in Excel, I just want to go over what
| | 00:04 | some of the differences are between
Excel 2007 and 2010, and also an overview of
| | 00:10 | what some of the new features are that
we're going to look at in this course.
| | 00:13 | I'll actually start out by telling you
what is not different, and that is the
| | 00:18 | file format of Excel 2010 is the
same as the file format in Excel 2007,
| | 00:23 | so you don't have to worry about that.
| | 00:25 | Of course, those are both different from
the file format of Excel 2003 and earlier.
| | 00:30 | The Ribbon bar is now customizable, and
I think that's really great, because in
| | 00:34 | Excel 2007 Microsoft introduced this
round Office button here in the upper-left
| | 00:39 | corner, and a lot of people weren't
really sure what that was or how to deal with
| | 00:42 | it, and you can see now that's been
replaced by the File tab and what's also
| | 00:47 | called Backstage view.
| | 00:48 | In Backstage view is not only
opening and saving and closing and all that,
| | 00:52 | but also collaboration.
| | 00:54 | And I'm going to show you how you
could save your work to Microsoft's
| | 00:57 | SkyDrive, which is a free web service and
also to a SharePoint portal, if you're using one.
| | 01:04 | There is now better
editing of imported pictures.
| | 01:07 | So if you have pictures coming in that
may be need to be recolored, you don't
| | 01:12 | have to go and do that in an
image editing program first.
| | 01:14 | Now, as for pasting, in the older
versions, you didn't really know what you were
| | 01:19 | going to get until you paste,
when you're copying and pasting.
| | 01:23 | Now you have a Paste preview.
| | 01:24 | So, before you actually paste your data,
you could get a preview of what that's
| | 01:28 | going to look like first.
| | 01:30 | If you ever used PivotTables, you'll
appreciate the new Slicers feature.
| | 01:34 | PivotTables are kind of interactive, in
the sense that you can switch the rows
| | 01:38 | and columns, but you can't
really sort them, or filter them.
| | 01:41 | Slicers are going to help you do that.
| | 01:44 | If you ever need to create math
equations for physics, engineering, statistics,
| | 01:49 | you're going to like the math Equation support.
| | 01:51 | It doesn't actually solve equations
for you, but you can make your equations
| | 01:55 | look really very nicely formatted.
| | 01:57 | Probably my favorite new
feature in Excel 2010 are Sparklines.
| | 02:02 | Sparklines are tiny little
charts that actually fit into a cell.
| | 02:05 | So the same way that you can have a
column of numbers, or a column of text,
| | 02:10 | you can now have a column of tiny
little charts and apply some really neat
| | 02:14 | formatting to them.
| | 02:15 | One of the biggest improvements in
Excel is what's called PowerPivot.
| | 02:19 | It used to be called Project Gemini.
| | 02:22 | This is a free plug-in from Microsoft,
and you could get it at powerpivot.com.
| | 02:27 | What this allows you to do is
combine data from many different sources.
| | 02:31 | It could be enterprise databases,
web sites, Excel spreadsheets, tiny
| | 02:35 | databases, and query them and work
with them all as though they're one and
| | 02:40 | sitting on your computer.
| | 02:41 | So, let's roll up our
sleeves and see what we've got.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you are premium monthly or premium
annual member of the lynda.com Online
| | 00:04 | Training Library, you have access to the
exercise files that I use through up this title.
| | 00:09 | The exercise files are in the Exercise
Files folder, which I have placed here on
| | 00:13 | the desktop, but you can
store it wherever you like.
| | 00:15 | There are files for most movies, and
they reside in subfolders named according
| | 00:19 | to the chapters, that you can see here.
| | 00:21 | It isn't absolutely necessary for you
to use these files. You can use files of
| | 00:26 | your own in place of them.
| | 00:27 | If you are a regular monthly or a
regular annual subscriber to lynda.com, you
| | 00:32 | don't have access to the exercise files,
but you can follow along with your own work.
| | 00:37 | So let's get started.
| | Collapse this transcript |
|
|
1. File Tab, AKA Backstage ViewCustomizing the Ribbon bar| 00:00 | When the Ribbon bar made its first
introduction in the Office 2007 applications,
| | 00:05 | everybody either really liked it or
they really hated it, and to tell you the
| | 00:09 | truth, I wasn't so crazy about it myself.
| | 00:12 | People were saying, 'Gee!
| | 00:13 | I knew where all my commands were,
everything that I needed to know, but
| | 00:17 | now they took away the menus and the toolbars,
I don't know where everything is anymore.'
| | 00:22 | So the good news is that in the 2010
version, and not just Excel but all of the
| | 00:26 | other Office applications, this
Ribbon bar is now completely customizable.
| | 00:31 | So I want to show you how to do
a couple of neat things with it.
| | 00:34 | The first thing is let's go up to the File
menu to go into Backstage view, and down
| | 00:38 | here towards the bottom choose Options,
and in our Options dialog box we'll go
| | 00:43 | over here to Customize the Ribbon.
| | 00:45 | Now it's a lot of work to get in here.
| | 00:47 | So I' just going to cancel out for
a second, or you could press Escape.
| | 00:50 | A much easier way of going in is
whichever of these tabs is selected - it
| | 00:55 | doesn't matter which one - right-click
anywhere and from the pop-up menu choose
| | 01:00 | Customize the Ribbon.
| | 01:02 | And now we are right back.
| | 01:02 | So it's just a much easier way to get in here.
| | 01:05 | First thing is let me just tell
you about turning things on and off
| | 01:08 | and rearranging things.
| | 01:09 | Let's say, for example, the View tab
is something that you use often, so you
| | 01:13 | can select it and hit that up arrow a
bunch of times, and now I have the View
| | 01:17 | tab at the very top.
| | 01:19 | Now I'm just going to move
the dialog box out of the way.
| | 01:21 | Notice the View tab hasn't changed
just because we haven't clicked OK yet, and
| | 01:25 | this is something also that's
changed since the last version.
| | 01:29 | Click OK, and now the
View tab is right up there.
| | 01:32 | We're going to right-click and
go back to Ccustomize the Ribbon.
| | 01:35 | Another way to move it is you
can simply take it and drag it.
| | 01:38 | So let me drag it down here, just below
Page Layout, and hit OK so you can move that here.
| | 01:43 | So there are few ways
that you can deal with that.
| | 01:47 | Well, let's say I decide that
maybe I don't need the View tab at all.
| | 01:52 | I'm just going to deselect it.
| | 01:54 | Now when I click OK, you see
the View tab isn't even there.
| | 01:58 | So let me right-click and go back to
Customize, and I'll just turn that back on.
| | 02:02 | Well, what about that stuff that's in it?
| | 02:04 | I'm just going to close
up Formulas and open Home.
| | 02:09 | Well, I want to add something to it.
| | 02:11 | So I'm going to go over here to
Popular Commands, click that down arrow, and
| | 02:15 | I'll go here to the Tool Tabs.
| | 02:18 | Let's say some of these Drawing
tools are things that I need in there.
| | 02:21 | So I'm going to open up that format.
| | 02:23 | Notice, Home is still selected so I'm
going to choose Insert Shapes and Add.
| | 02:28 | Notice that gets added to the bottom.
Maybe sizing is something I want, and maybe
| | 02:32 | Headers & Footers a thing I
need to use ever so often.
| | 02:35 | So I'll choose Header & Footer and add that.
| | 02:37 | So I've added these three groups to the bottom.
| | 02:40 | By the way - I'm just going to open one of these -
| | 02:42 | you'll notice that the items
in the groups are grayed out.
| | 02:46 | You can't customize that.
| | 02:47 | You cannot change the
content of what's in a group;
| | 02:50 | you can only put the groups in or take them out.
| | 02:53 | So I'll click OK, and now you
see these three things I've put in.
| | 02:56 | Insert Shapes, Size, and Header &
Footer are there, and I will just right-click
| | 03:00 | over here and go to Customize the Ribbon.
| | 03:02 | The same thing like with the tabs.
I could take those, and I can move them, or I
| | 03:08 | can use the Up arrow and move them.
| | 03:10 | So let me click OK.
| | 03:11 | So now I've moved them over here to the left.
| | 03:15 | So that sort of thing is very customizable.
| | 03:17 | So I'm just going to right-click on the
Ribbon bar and go back here to Customize.
| | 03:22 | So I'm going to create my own tab here.
| | 03:25 | It will be a list of maybe some of the
favorite things that I need to use often.
| | 03:30 | So I'm going to go down here and choose
New Tab, and it gives me kind of this
| | 03:34 | default name of New Tab and New Group.
| | 03:36 | So I'm just going to choose that New Tab,
choose Rename, and I'll call this Bob's faves.
| | 03:44 | Click OK. I'm going to go back here to this dropdown.
| | 03:49 | I'll go back here to the Tool Tabs.
| | 03:50 | I like the stuff that's under there.
| | 03:52 | I'll go here back under Drawing
tools, let's say Insert Shapes.
| | 03:56 | Now you notice this Bob's faves is selected.
| | 03:58 | So I'll choose Insert Shapes and
click Add, and it goes in there.
| | 04:02 | Now if I choose New Group, go here
under Size and maybe choose Height,
| | 04:06 | notice that goes in there -
Width also while we are it.
| | 04:10 | Let's put that in there.
| | 04:11 | But this New Group isn't much of a name either,
| | 04:13 | so I'm going to choose that Rename,
and I'll call this Dimensions.
| | 04:20 | I'll put a couple of other things in here.
| | 04:22 | I will reselect that Bob's faves, and
maybe here under Header & Footer I'll put a
| | 04:27 | couple of things in there, maybe one more.
| | 04:29 | I will go to Options.
| | 04:30 | I'll put one more in here.
| | 04:32 | Notice right now Bob's faves
is the second item in this list.
| | 04:34 | So when I click OK, there it is.
| | 04:37 | It's now the second item in the list,
and here's all that stuff that I put in it
| | 04:41 | and all of these are the tabs are unaffected.
| | 04:42 | Of course, I could always
go right-click and Customize.
| | 04:45 | If I decide that I want Bob's faves
down towards bottom, I can take that, and
| | 04:49 | I can drag that here. OK it.
| | 04:52 | So I could put that really anywhere that I want.
| | 04:54 | So this is really very nice and customizable.
| | 04:56 | Customize the Ribbon again.
| | 04:59 | Well, I could also rename
some of the existing tabs.
| | 05:03 | So, for example, Formulas.
| | 05:05 | Maybe I really don't like formulas,
or maybe I don't know how to use them.
| | 05:08 | I can take that and rename an existing tab
and call this, Formulas I don't understand.
| | 05:15 | If I decide that, oh, here is some
stuff under Review and maybe Language,
| | 05:21 | I only do stuff in English,
| | 05:23 | I don't have to worry about French or
Spanish or anything. Or maybe I'm not using
| | 05:26 | a tablet here, so I don't need Ink.
| | 05:28 | So, see I could really ruin this completely.
| | 05:31 | But let's say this is the way that I like it.
| | 05:34 | I don't want somebody else to be able
to use my tabs, or maybe I'm doing this
| | 05:38 | on my office computer, and I want to bring
this back home and use this on my laptop.
| | 05:42 | So whichever you want to do it is go
down here to Import/Export, and I am going
| | 05:47 | to choose Export all
customizations, and I'll give this a name.
| | 05:51 | I will delete that.
| | 05:53 | You notice it gives you an
extension of exported user interface.
| | 05:56 | I'll call this bob's set of tabs.
| | 06:00 | Click Save or press Enter.
| | 06:01 | Now let's say I'm now on another
computer, and this is maybe my laptop.
| | 06:07 | I can come back in here, go to Import >
customization, and there it is, and then
| | 06:12 | I can simply double-click this and
import this to my other computer.
| | 06:15 | I'm not actually going to do it now,
because it's obviously the same computer, but
| | 06:17 | you see it's a fairly easy thing to do.
| | 06:20 | Now, let's say that I have done all of
this, and I decided, gee, I have renamed things,
| | 06:26 | I've removed things,
I've really ruined in this;
| | 06:28 | I want to get back to the way Excel was
when I first installed it. Really easy to do.
| | 06:33 | All you have to do is hit this
Reset button, choose Reset all
| | 06:37 | customizations. Confirm Yes.
| | 06:38 | That's what I really want to do.
| | 06:40 | When I click OK, now here it is.
| | 06:42 | It's all back to its original
state with none of those changes.
| | 06:47 | So that's a really great handy thing,
and I do highly recommend that you play
| | 06:51 | around with customizing the Ribbon.
| | 06:53 | So things that you need to use often,
you can put them there, literally right
| | 06:57 | at your finger tips.
| | Collapse this transcript |
| Looking at Backstage view| 00:00 | In Excel 2003 and earlier versions
when we had menus and toolbars under the
| | 00:06 | menus, we had the File menu that
gave us all the file management tasks.
| | 00:11 | Then in 2007 Microsoft replaced it
with the round Office button in the
| | 00:14 | upper-left corner, and lot of people had
no idea what it was or that they could
| | 00:18 | even go and save and open their files there.
| | 00:20 | So in 2010, and this is in the other
Office applications as well as Excel, we now
| | 00:26 | have the File tab, and
that's also called Backstage view.
| | 00:29 | So I'll click on the File tab that
brings us into Backstage view, and I'll use this
| | 00:34 | term synonymously here.
| | 00:35 | You can see all the familiar things that
you would have had under the File menu,
| | 00:39 | like saving and opening and closing and so on.
| | 00:42 | We also have our Recent files list,
which I'll talk about in a second.
| | 00:47 | But let's say you didn't even know
about the File tab, or you didn't even know
| | 00:51 | anything about the Office
button in the previous versions.
| | 00:55 | I'm just going to go back here to Home.
| | 00:57 | If you know what some of the shortcuts
are, those things aren't really necessary.
| | 01:01 | If, for example, Open begins with the
letter O, so you could press Ctrl+O to
| | 01:05 | Open, or Ctrl+S to Save, or you might
know that let's say Ctrl+F4 is the same
| | 01:10 | as clicking on this button here to Close, or
F12 is the same thing as the Save As dialog box.
| | 01:17 | I'll just Escape out of here.
| | 01:18 | So if you know what some of those are,
and I recommend that you learn at
| | 01:21 | least some handy ones,
| | 01:23 | you don't have to depend on going
to the File tab over and over again.
| | 01:27 | Let's go back here, and over here in
the Info tab we can see Permissions.
| | 01:32 | This used to be in a different place.
| | 01:33 | Prepare for Sharing is new Versions is new.
| | 01:36 | But I really want to show you
here the Recent files list.
| | 01:38 | So I'm going to go here to Recent files list.
| | 01:41 | In the 2003 version, it gave you a
maximum of nine files showing up.
| | 01:46 | In 2007 and 2010 we could have up to
50 files showing up here in the list.
| | 01:51 | But what's new in 2010 is we have Recent
Places, so we could go into an entire folder.
| | 01:57 | In 2007, we had these pushpins, and
obviously we still have them here, but
| | 02:03 | they've changed a little bit.
| | 02:04 | Right now, these are all are gray,
none of them are pushed in, but let's say
| | 02:07 | this Backstage file here,
| | 02:10 | I clicked that pin, not only does it get
pinned to the list, but you see it also
| | 02:14 | gets pushed up to the top.
| | 02:15 | Or let's say this printing
document. I'll click that.
| | 02:18 | So it gets pinned and all of
the pinned files go up to the top.
| | 02:23 | Let's say I decide that, well, maybe I
don't need this to be pinned anymore.
| | 02:27 | I don't care if it scrolls off or not
and I click and remove it; it just goes
| | 02:32 | back down towards the bottom.
| | 02:33 | So that's kind of a nice feature that
anything that's pinned will stay on the
| | 02:37 | list until you want to pin it, and
the pinned files stay up to the top.
| | 02:42 | Well, what if there is a file
here that you don't want here at all?
| | 02:45 | This is something also new to this version.
| | 02:47 | Here's a confidential
spreadsheet that I had open.
| | 02:50 | Now I'm going to right-click
and choose Remove from list.
| | 02:54 | So now you see that
confidential file is no longer in the list.
| | 02:57 | Now that doesn't delete it from your hard drive.
| | 03:00 | That file is still there, and anyone
with access to your hard drive is still
| | 03:03 | going to be able to go ahead and access
it, but at least nobody can come in here
| | 03:08 | to the Recent file list and see that
you had that file opened and editing.
| | 03:14 | So now that we see where some of our
familiar commands are located in this new
| | 03:18 | version of Excel, I'm going to come
back and revisit Backstage view throughout
| | 03:22 | the chapter, so you can see some of
the other features that are in here.
| | 03:25 | But before we do that, we need to
understand how to deal with file compatibility
| | 03:30 | between this new version and the previous ones.
| | Collapse this transcript |
| Maintaining compatibility| 00:00 | One great thing about this new version
is that the file format hasn't changed
| | 00:05 | from the 2007 version.
| | 00:07 | The .XLSX file format of Excel 2010 is
the same format used in Excel 2007, and
| | 00:14 | also the same as the format
used by Excel 2008 on the Mac.
| | 00:18 | But this is different from the three letter
.XLS format used by Excel 2003 and earlier.
| | 00:27 | Even though the format in 2010 is the
same as a format in 2007, they are still
| | 00:32 | formatting features that you could
apply here that won't appear as you expect, or
| | 00:38 | may not appear at all, if you open up in 2007.
| | 00:40 | Now you might think to yourself, well
gee, I am just working on this by myself.
| | 00:44 | Maybe I'm not going to share this with anybody.
| | 00:47 | I guarantee, at some point down the
road, you will need to share your work with
| | 00:50 | someone who is using an older version of Excel.
| | 00:54 | So hopefully the info you get in this movie
will save you some headaches down the road.
| | 00:58 | So I have this file open here, 01_03_current
format.xlsx, and we have a couple of things here.
| | 01:06 | We have this conditional
formatting that's applied now.
| | 01:08 | These particular icons
are new in the 2010 version.
| | 01:12 | We'll also have the SmartArt.
| | 01:14 | Let me just click this here, and the
SmartArt here was introduced in 2007.
| | 01:19 | So if I were to open this workbook in
Excel 2007, that conditional formatting
| | 01:25 | here, these icons, would disappear,
but this green SmartArt here would
| | 01:28 | function just great.
| | 01:30 | If I were to give this document to
someone who is using Excel 2003, first of all
| | 01:36 | they might not be able to
open the document at all.
| | 01:39 | If they did, I guarantee you these
conditional formatting things are going to be gone.
| | 01:43 | The SmartArt, it will be visible, and
they will be able to edit it as regular
| | 01:48 | shapes, but not as SmartArt.
| | 01:49 | And if they did that, and then went ahead
and open up in 2010, it would still be shaped.
| | 01:54 | It wouldn't re-become SmartArt again.
| | 01:57 | So you really need to be
careful about that. Let's do this.
| | 02:01 | Let's go and save this down here.
| | 02:04 | So I am going to go up to the File menu,
and I am going to Save As, and I am
| | 02:10 | going to save this with an older 2003
extension, and I am going to call this 03 saved down.
| | 02:18 | Click Save.
| | 02:20 | This gives us all kinds of warnings
about all sorts of different things that
| | 02:25 | aren't going to appear correctly by
opening an older version, and up here you can
| | 02:31 | see in the upper-left there is some
formatting that's going a little haywire.
| | 02:34 | So it's really good idea to
keep tabs of what's going on here.
| | 02:38 | You see this check box to Check
compatibility when saving the workbook,
| | 02:41 | so that way, it will catch
anything that you might not notice.
| | 02:44 | I'll just click Continue.
| | 02:45 | I am going to go and close it,
and I am going to reopen it.
| | 02:50 | I'll go to the File tab and open
up saved down. And look at that!
| | 02:55 | This conditional formatting,
| | 02:57 | that stuff has gone.
| | 02:58 | It was just removed.
| | 02:59 | But the SmartArt, when we click here,
| | 03:01 | you can see that this is still SmartArt,
and it's still working perfectly fine.
| | 03:06 | So when you're applying formatting,
especially if you know that your work is
| | 03:10 | going to be opened up by someone who's
using an older version of Excel, it's a
| | 03:14 | good idea to keep track of what
formatting features you're using, what kind of
| | 03:19 | features you are using in Excel, so that
things won't disappear when you're not
| | 03:23 | expecting them to disappear.
| | 03:25 | Well, this was all about taking a
current 2010 version file and saving it down.
| | 03:32 | Let's go the other direction.
| | 03:33 | Let's take an older file format and save it up.
| | 03:37 | So first, I'll just close this file
here, and I am going to open, I'll just
| | 03:41 | press Ctrl+L, and I am going to open
this file here called 03 older format
| | 03:46 | workbook in your Exercise Files and
see this is just a plain old Excel sheet.
| | 03:52 | First thing you'll notice here on top is
where it has the file name it tells us
| | 03:56 | Compatibility mode, so that this is an
older version file that we can edit in
| | 04:01 | 2010, but because this is an older
format file, we can edit only using those
| | 04:09 | features that are available in 2003,
or those formats that 2003 can accept.
| | 04:16 | I'll select all the numbers.
| | 04:17 | I'll click that first number,
Shift+Click the last number there,
| | 04:20 | so we have all of them selected. And on
the Home tab here, I am going to go over
| | 04:25 | to Conditional Formatting, and I am
going to go over here to Data Bars.
| | 04:30 | Then I'll just choose the first one.
| | 04:33 | If you don't like this blue one,
you want a green one, that's okay.
| | 04:35 | You can just choose that.
| | 04:36 | I'll just deselect so we
can see. That's lovely!
| | 04:39 | I am going to save over it.
| | 04:41 | Now this is in Compatibility mode, so I
could press Ctrl+S, or just click the Save button.
| | 04:46 | Again, this is telling us that, oh,
there is going to be some loss of
| | 04:49 | functionality because 2003
doesn't accept all of this.
| | 04:53 | So I'll click Continue, and there it is.
| | 04:57 | I am going to close the file,
press Ctrl+F4. I'll reopen it.
| | 05:02 | I'll go to the File tab. Click.
| | 05:05 | There it is, and everything is here,
and that's because this is 2010. Hey!
| | 05:11 | It's super version.
| | 05:12 | It will recognize everything.
| | 05:14 | So keep in mind though, if you were to
open this up in an older version, they
| | 05:19 | probably wouldn't see this formatting at all.
| | 05:21 | Also in this chapter, we'll talk
about how we can head off some of these
| | 05:25 | conflicts before we save it.
| | Collapse this transcript |
| Making templates easier| 00:00 | Templates have been part of Excel for many
versions, but they've never been obvious.
| | 00:04 | Microsoft has long had free templates
around for you to download, but it wasn't
| | 00:07 | obvious how to get them. The process
wasn't straightforward, and most people had
| | 00:11 | no idea that they were even there.
| | 00:13 | So that's all changed in Excel 2010.
| | 00:16 | Let's go up to the File menu and choose New.
| | 00:21 | This is one way to create a new blank workbook.
| | 00:23 | We could just Blank Workbook.
| | 00:24 | It's already selected, and we
could go down here and Create.
| | 00:27 | You could also press Ctr+N, but I bet
you are not looking at that right now.
| | 00:31 | You're probably looking all these
great icons like, oh, my goodness.
| | 00:34 | I can create new reports and plans and
invoices and inventories, and you don't
| | 00:38 | really have to do all that much work.
| | 00:40 | So there is a lot of really neat stuff here.
| | 00:42 | It's all free; it all comes
from Microsoft's web site.
| | 00:45 | So I'm going to go into Budgets.
| | 00:48 | So you could click any of these and
see larger thumbnails of what there.
| | 00:52 | There are some nice colorful
ones, and all kinds of neat stuff.
| | 00:55 | So I'm going to go down here
and choose this Event budget.
| | 01:00 | So I'm going to choose Download and boom!
| | 01:04 | In an instant, it
downloads and loads up in Excel.
| | 01:07 | You don't have to jump through all those
hoops that you used to have to jump through.
| | 01:10 | Now let's start editing this. Instead of
Event Name, I'll call this Bob's Big
| | 01:14 | Bash, and I'll change this as Event
budget for Bob's Big Bash, and we can edit
| | 01:26 | this just as though it's an
Excel file we created on our own.
| | 01:29 | I want a bigger room than that.
| | 01:30 | So let's say I'll double the size of
the room and all this kind of stuff Tables
| | 01:35 | and Chairs, Balloons. I like balloons.
| | 01:37 | Now I don't know who uses Fax services,
so I am going to change this to Sky
| | 01:41 | writing, and that's probably expensive.
| | 01:43 | So let's put that in.
| | 01:45 | You see it's calculating just like an
Excel file that you created yourself.
| | 01:49 | Also, there is more than
just this one worksheet.
| | 01:52 | You see there is Income, Profit - Loss,
but I wish I could make money like
| | 01:57 | this on parties, but just because these
worksheets are here doesn't mean you have to use them.
| | 02:02 | You could go and delete them if you want.
| | 02:03 | They are there really just for your convenience.
| | 02:06 | Once I am done doing it, right
now you see it has a temporary name
| | 02:09 | of PMG_EventBudget1.
| | 02:11 | So this is the first instance,
| | 02:12 | kind of like normally you'll
have Book1, Book2 and so on.
| | 02:16 | So this is number one of
this particular template.
| | 02:19 | So I am going to go and save it, and even
though it has this temporary name, I am
| | 02:25 | going to give it a real name.
| | 02:26 | So let me go into the chapter 1 folder,
and I will call this bob's party expenses,
| | 02:36 | and then I will click Save.
| | 02:38 | Now it has a regular old
name of bob's party expenses.
| | 02:42 | Now let's say you use this file and
sometime later you want to use it again.
| | 02:47 | Well, I am just going to close this, Ctrl+F4.
| | 02:49 | It's already saved.
| | 02:50 | I will go back to the File menu, I will go
down here to New, and there is Recent templates.
| | 02:56 | So when I click Recent templates, there
is that template that I used just before.
| | 03:01 | So I will double-click it, and there it is.
| | 03:03 | It opens up again, fresh for
editing without any of those changes.
| | 03:07 | So I can use this over and over again.
| | 03:08 | Now that's great, but what if you
want to create a template of your own?
| | 03:14 | Well, the easiest way to create your
own template is to use a worksheet that
| | 03:18 | you created yourself.
| | 03:19 | So I am going to close this, and I am
not going to save it, and let's go and
| | 03:24 | open up the file in the chapter 1
folder, called 01_04_turn into template.
| | 03:30 | Now this is just a plain old regular
sheet, and it has some formulas in there
| | 03:35 | that we can use. I will just put some
numbers here to show you, and that's fine.
| | 03:39 | I don't want to leave the numbers in.
| | 03:41 | I just want to show you that yes.
there is stuff in here that works.
| | 03:43 | So I will just delete that.
| | 03:44 | So this is called 01_04_turn into template.
| | 03:47 | So we will do that.
| | 03:49 | I am going to press F12 to Save As,
and where it says Save as type I will
| | 03:53 | click the drop down, and I want to choose Excel
Template, and you can see the extension is xltx.
| | 04:00 | Also, you see it puts it
into the Templates folder.
| | 04:05 | You can change that folder if you want,
but probably not a good idea to do that,
| | 04:08 | and I don't want to use this file name.
| | 04:10 | So I am going to call this
expense template, and I will click Save.
| | 04:17 | So now it's called expense
template.xltx. I will close this.
| | 04:21 | So let's say sometime later I come in.
| | 04:23 | I want to use that.
| | 04:24 | I go to the File menu, choose New.
| | 04:26 | Now instead of Recent templates,
I am going to go to My templates;
| | 04:29 | these are templates that I have
created that I have saved, and when I
| | 04:32 | clicked that, look at that!
| | 04:33 | There is my expense template.
| | 04:36 | I could double-click it.
| | 04:37 | There it's clean. I can
start putting in numbers again.
| | 04:41 | You see over here, kind of like what we
saw before, its expense template.xltx1.
| | 04:46 | So that means that it's just this new
instance that we are using this template.
| | 04:52 | So again, you can use it over and over again.
| | 04:54 | I think of templates as kind of like a
form that you put in a photocopy machine.
| | 04:58 | You can make one copy or dozens of
copies, and whatever you change on the copies
| | 05:02 | doesn't change the original.
| | 05:03 | So, you can have these over and over again.
| | 05:06 | So Templates are a great feature. A lot
of people don't know that they are there,
| | 05:10 | but starting in 2010, since you have
those readily available, you might want to
| | 05:14 | look into some of them.
| | Collapse this transcript |
| Handling document versions | 00:00 | Having editable properties isn't new to Excel,
but Excel 2010 has a new way of editing them.
| | 00:06 | I also want to show you the new
Versions feature, which is pretty neat.
| | 00:11 | So in this file I am going to go up
here to the File tab and down here to Info.
| | 00:17 | Over here we can see the Properties
that are associated with this file.
| | 00:21 | Also, notice there is a little
thumbnail here. Before we even do anything here,
| | 00:24 | if you just click this thumbnail, that's
just a way of returning to the document.
| | 00:28 | Let's go back here.
| | 00:30 | So it shows us the file size.
| | 00:32 | Let's say I want to change the Title.
| | 00:33 | So here where it says Add a title, let
me click, and let's type in Inventory
| | 00:39 | list and press the Tab key.
| | 00:41 | For Tags, this is just like you can put
tags on a web site and so many other places.
| | 00:45 | We can put some tags here, too.
| | 00:47 | Let's say maybe pure, refined,
virgin, extra virgin and so forth.
| | 00:55 | If you don't need to put in any other
properties, you can just click somewhere
| | 00:58 | on the background here to seal that in.
| | 01:01 | Now over here, where it says Show All
Properties, if you click that, it'll show
| | 01:06 | you a couple of other
things: Manager, Author and so on.
| | 01:09 | You can click Show Fewer Properties.
| | 01:11 | But if you go up here to the Properties
dropdown and choose Advanced Properties,
| | 01:16 | this brings up the dialog box that
you might be used to from the old
| | 01:19 | versions General, Summary.
| | 01:21 | Here's some of the stuff that
we typed in as keywords,
| | 01:24 | statistics of when it was
created and modified and all that.
| | 01:26 | Contents just shows us the worksheets.
| | 01:28 | In Custom, you can put in
whatever other tags and things.
| | 01:33 | They normally call it metadata in here.
| | 01:35 | I am just going to cancel out.
| | 01:36 | But if you click Properties - this is really
what's new - and choose Show Document panel,
| | 01:41 | now you could see the
Properties on top of the worksheet.
| | 01:44 | So that way if you want to assign
Keywords or Comments, you might find it easier
| | 01:48 | doing it here with the
Properties on top of the Worksheet.
| | 01:51 | So I'll put in Current
list of inventory by month.
| | 01:59 | And once you put in anything, here you
can simply just tab over, if you want,
| | 02:05 | click Close, and now it's in.
| | 02:07 | If you go back to the File tab and back
to Info, any of that stuff you put in is
| | 02:12 | still going to be there,
whether it's visible here or not.
| | 02:15 | Or if you go to Advanced Properties,
there's your Comments that you just typed in.
| | 02:21 | Now here's the thing about Versions
is every so often Excel will save a
| | 02:27 | version of this for you.
| | 02:29 | And you can see over here this file has
one version, saved at 4:40 PM is an autosave.
| | 02:34 | Well, what is that autosave?
| | 02:36 | Excel has had autosave for a long time.
| | 02:38 | If you are not sure what that is, just
go to Options, go up here to the Save
| | 02:42 | category, and this will
autosave every 10 minutes by default.
| | 02:46 | If you have a large worksheet that
takes a long time to autosave, you might
| | 02:51 | want to boost that up.
| | 02:51 | 10 minutes is generally okay.
| | 02:53 | So I am going to cancel out.
| | 02:54 | Well, let's say I messed something up.
| | 02:57 | Well, over here, the very first
number here, Pure olive oil for January,
| | 03:01 | well, 8521 that's not correct.
| | 03:03 | I happen to know that the
number here should really be 9683.
| | 03:08 | And somehow I made a mistake and put this in.
| | 03:11 | Maybe I am not sure where that
mistake is, but I do know that I want to get
| | 03:15 | that autosaved version.
| | 03:17 | So I am going to go to the File menu,
and down over here I'll simply select that
| | 03:22 | version, and there it is.
| | 03:24 | It tells me here that
this is an Autosaved Version.
| | 03:28 | Now if I choose A newer version is
available, that's going to be with
| | 03:30 | the incorrect number,
| | 03:32 | that is the incorrect number here.
| | 03:33 | But I'll choose Restore, and I am going to
override the last version and OK. So great.
| | 03:38 | Now I've restored that
autosaved version of this file.
| | 03:41 | So now I have the correct number here.
| | 03:44 | Now I will go back to the File tab here.
| | 03:46 | You notice there is no facility
here that says create a version now.
| | 03:49 | It just doesn't exist.
| | 03:50 | Excel does it on its own,
usually when it autosaves.
| | 03:54 | Also, let's say if Excel crashes - hey,
it happens or maybe something else
| | 03:58 | happens, there is a power outage -
| | 04:00 | there is a very good possibility that
Excel will save your worksheet, even though
| | 04:05 | you didn't save it manually.
| | 04:07 | Then you'll be able to
bring it back up as a version.
| | 04:09 | So this is something you probably won't
deal with day-by-day, but it's handy to
| | 04:13 | know that it's here.
| | Collapse this transcript |
| Changing the file type and converting to Adobe PDF| 00:00 | In this movie, I'll show you the new way
that Excel can save to other formats, and
| | 00:04 | also a new and easier way
of creating an Adobe PDF.
| | 00:09 | So let's go over here to the File tab.
| | 00:12 | Then over here, we want to choose Save &
Send, and over here in this column under
| | 00:17 | File Types, choose Change File Type.
| | 00:20 | Now we've got all these
different file types that we can choose.
| | 00:23 | If you want to save back, and we
covered this in the Versions movie,
| | 00:26 | you can save back a version.
| | 00:28 | But let's say you are working with
somebody who is using OpenOffice -
| | 00:31 | and OpenOffice uses the ODS file format -
| | 00:35 | or maybe you want to choose comma-
delimited, or you want to change this into
| | 00:39 | tab-delimited, whatever it is,
| | 00:42 | all you have to do is double-click
it and then go and change the file.
| | 00:46 | And we don't really have to do that.
| | 00:47 | So I am just going to cancel that.
| | 00:48 | Let's go back into the File tab, back to
Save & Send, and again Change File Type
| | 00:53 | if you want to do a different way,
| | 00:56 | let's say you want to save to a
file format that you don't see here,
| | 00:59 | you can choose Save as Another File
Type, either double-clicking it or
| | 01:03 | click Save As, either way.
| | 01:04 | Now this brings you into the
good old Save As dialog box.
| | 01:09 | You would use it there just if you say, gee,
I am really sure what I want to save it as.
| | 01:14 | If you know from the beginning that
you want to do a Save As, it's probably
| | 01:17 | easier just to press the F12 key.
| | 01:19 | When you press the F12 key,
immediately you're in the Save As dialog box.
| | 01:24 | But let's go back to the File tab.
| | 01:25 | We will talk about PDFs.
| | 01:27 | Everybody loves PDFs.
| | 01:28 | So let's go down here to Save & Send.
| | 01:30 | If you want to create a PDF out of this
workbook, you go here to Save & Send and
| | 01:35 | Create PDF/XPS Document.
| | 01:37 | Don't worry about what XPS is.
| | 01:40 | Here is what's also really neat is
you don't have to have Adobe Acrobat
| | 01:44 | installed on your computer.
| | 01:45 | You can create the PDF right from Excel.
| | 01:48 | Excel has its own built-in PDF creation.
| | 01:52 | So just click Create PDF. Give it a name.
| | 01:56 | I'll keep the same name here, 01_06_
converting, and here it's giving us a three
| | 02:01 | letter extension of PDF.
| | 02:03 | If you want to choose some
options, you go in here Options.
| | 02:06 | So some various options you can choose;
most of these are self-explanatory.
| | 02:10 | You can print All pages or just selected pages.
| | 02:14 | You can print the Active sheet, just
whatever you have selected or the whole
| | 02:17 | workbook, Ignore print areas.
| | 02:19 | And under non-printing information, you
can choose, we were talking about properties;
| | 02:24 | you can print them are not.
| | 02:25 | Structure tags for accessibility that's
specific structure for people who have
| | 02:29 | physical difficulty using the computer.
| | 02:32 | Here are some PDF options,
which you can safely ignore.
| | 02:35 | I am just going to click OK or
Cancel, because we don't really need to
| | 02:39 | change anything in here.
| | 02:41 | Here is our file name. Click Publish.
| | 02:43 | This is now going to create a PDF,
and it's going to open in either we have
| | 02:49 | Adobe Acrobat on this machine - on
yours you might just have the Adobe Reader;
| | 02:53 | either way it's fine.
| | 02:53 | There is the spreadsheet. It opens
up in Adobe Acrobat or Adobe Reader.
| | 03:01 | That's really all there is to it.
| | 03:02 | Creating PDFs has never been easier,
and it works the same way in all of the
| | 03:07 | other Office programs as it does in Excel.
| | Collapse this transcript |
| Preparing to share a document| 00:00 | If you watched the movie where we
talked about compatibility, you saw some
| | 00:04 | issues of converting files
between older and newer formats.
| | 00:07 | In this movie, we will see how we can
solve compatibility problems, or find
| | 00:11 | compatibility problems, even
before we get to the Save As dialog box.
| | 00:15 | So in this file we have some of these
conditional formatting icons, and we also
| | 00:21 | have this SmartArt here with some names
of products in them. So let's do this.
| | 00:26 | Let's go to the File menu and go to the
Info section, if you are not there already.
| | 00:33 | Let's look at this Prepare for Sharing area.
| | 00:35 | So this is before sharing the file.
| | 00:38 | It tells us that it contains various
things that we might not want to share,
| | 00:41 | some document properties that
we see over here on the right.
| | 00:44 | There are hidden rows in this document.
| | 00:46 | And also there are some issues with
that SmartArt diagram would not be picked
| | 00:52 | up very easily by someone who uses a
screen reader, someone who has problem
| | 00:57 | seeing the document.
| | 00:59 | So what I want to show you over here
is what we could do to check for issues.
| | 01:03 | So click Check for Issues,
| | 01:04 | and we have these
three different options.
| | 01:06 | Let's start with the one on
bottom, Check Compatibility.
| | 01:09 | When we go in here, this
shows us some familiar messages.
| | 01:14 | Over here, where it says Select versions
to show, when you click that, this shows
| | 01:19 | you that it's comparing
with which older versions?
| | 01:23 | Maybe you don't care about people who are
going to be using this workbook in 2003.
| | 01:27 | You only want 2007.
| | 01:28 | So now this only shows you what's
happening for someone who is going to
| | 01:34 | view this with 2007.
| | 01:35 | Just minor loss of fidelity, and that
usually means things like color shifts,
| | 01:38 | like the green might shift
to a slightly different green.
| | 01:40 | Now if we come back here and we choose
just the 97 to 2003, not the 2007,
| | 01:47 | now we see we have Significant loss of
functionality, and a whole bunch of warnings in there.
| | 01:52 | So you could really have this
whichever way you want, turned on and turned off.
| | 01:56 | The thing is let's say you fix something,
and then you can back to this dialog
| | 02:00 | box, and then you fix something
else you come back to this dialog box.
| | 02:03 | Well, that's kind of tedious.
| | 02:04 | So here is a really great feature:
Down in the lower left corner, click where it
| | 02:07 | says Copy to New Sheet.
| | 02:10 | Now this puts a new worksheet down
at the bottom of the workbook with all
| | 02:15 | of that information.
| | 02:16 | You can go through here, and
you might find it a lot easier.
| | 02:19 | Let me just hold the Ctrl key and roll
the wheel to scroll out a little bit,
| | 02:22 | so you can see all of this information
here is probably easier than going to the
| | 02:28 | dialog box over and over again.
| | 02:29 | So do you notice that some
of the cells here are links?
| | 02:33 | And that just makes it easier
for us to find what's going on.
| | 02:36 | So if you click one of them, it selects
those particular cells that are affected.
| | 02:41 | You notice here all those cells are selected,
because of this big green title at the top.
| | 02:45 | So that's affecting a lot of the cells below it.
| | 02:47 | Well, let's go back to the File tab, back to
Info if you need to, back to Check for Issues.
| | 02:53 | This time, let's choose the
top option, Inspect Document.
| | 02:57 | This is going to tell us that we need
to save the file first. So that's fine.
| | 03:00 | Let's do that.
| | 03:01 | Now we could choose,
| | 03:02 | we can select or deselect any of
these options that we want to check for.
| | 03:06 | I am going to leave them all
selected, and let's click Inspect.
| | 03:10 | Now this tells us, oh,
okay, we have this problem.
| | 03:13 | We don't have that problem.
| | 03:14 | So there are some Document
Properties and Personal Information.
| | 03:17 | Let me move this out of the way, so you could
see there are properties and things in there.
| | 03:20 | So let's click Remove All.
| | 03:23 | And you notice that those properties - most
of them, not all of them - have been removed.
| | 03:27 | Now there are Hidden Rows and Columns.
| | 03:29 | I'll click Remove All, and now those are gone.
| | 03:32 | So now we know, and we can see by
those nice blue check marks that this
| | 03:36 | workbook is pretty clean.
| | 03:37 | We don't have any weird things in
there that we don't want getting out.
| | 03:41 | So let's click Close, and one more
time let's go back to Check for Issues.
| | 03:46 | Now let's look at Check Accessibility.
| | 03:48 | Again, this is for the benefit of
people who may have physical difficulty
| | 03:52 | dealing with a computer, or dealing with Excel.
| | 03:54 | So I'll click that, and over here it
gives us this pane of some warnings.
| | 03:58 | Now it tells us Missing Alt Text in the diagram.
| | 04:01 | If you click this diagram here, you
notice that it selects the SmartArt, and
| | 04:05 | here is what's going on.
| | 04:06 | Someone who is using a screen reader,
the screen reader is going to come along
| | 04:09 | and say, oh diagram!
| | 04:11 | And that really isn't going to be very
useful for the person using the screen reader.
| | 04:15 | So what I am going to do is I am going to right-
click, and I'll go over here to Format Object.
| | 04:19 | We've got all these different options here.
| | 04:21 | I am going to go to the bottom, Alt
Text, and move this dialog box here.
| | 04:26 | I'll click over here where it says
Title, and I'll call this Key Products.
| | 04:32 | Then here for the Description, I am
going to use the names that are in here.
| | 04:35 | Now you can't really copy and paste,
because the dialog box is open.
| | 04:38 | So I'll just type them.
| | 04:39 | So this way, the screen reader will come
along, it will read the names of these
| | 04:44 | particular olive varieties,
and it'll pick them up.
| | 04:47 | So it won't just be a blank
diagram. I'll click Close.
| | 04:51 | The other warning that it's
giving us is Default Sheet Names.
| | 04:55 | These are simply these
Default Sheet1, Sheet2, and Sheet3.
| | 04:58 | So then we could just double-click.
| | 04:59 | This hasn't changed, and
we can call this Inventory.
| | 05:03 | I'll just call it January
to June and press Enter.
| | 05:08 | So if you take the time to go through
some of these options before saving a file
| | 05:13 | and sending along, you'll find that
you'll save some headaches later on.
| | Collapse this transcript |
| Making printing easier| 00:00 | Printing is an easier task in 2010 and
the previous versions, and of all of the
| | 00:06 | features that Microsoft changed in this version,
| | 00:09 | this one really went through
some of the biggest changes.
| | 00:12 | I think it's really for the better.
| | 00:13 | Now, to get to printing, fairly easy,
you can go to the File tab here, and
| | 00:18 | then go down to Print.
| | 00:20 | I'm just going to escape out of here,
because you can also press Ctrl+P, and that's
| | 00:25 | something that hasn't
changed in years and years.
| | 00:27 | So notice more than half of the
screen is taken up by Print Preview.
| | 00:32 | Now, I think that is really great,
because in the 2007 version nobody really knew
| | 00:36 | where the Print Preview screen was.
| | 00:39 | And in the older versions, the Print
Preview screen wasn't really very useful.
| | 00:44 | It was there, but couldn't really do very much.
| | 00:46 | So here what they are doing is they're
putting this Print Preview right in the
| | 00:50 | same screen as Printing.
| | 00:51 | So let's go through some of
the options that are here.
| | 00:55 | This is your default Printer.
| | 00:56 | If you have other printers configured
and you want to choose them, you can
| | 00:59 | simply choose any other printer that you
have, or if you have Adobe Acrobat and whatnot.
| | 01:04 | If you want to get to printer-specific
properties, then you can click here on
| | 01:09 | this Printer Properties link, and this
brings you into the properties that are
| | 01:13 | specific to your printer.
| | 01:15 | So if you have an HP LaserJet 4200,
you're going to see this particular screen.
| | 01:19 | If you have another printer, a Canon, or
Lexmark, you are going to see other screens.
| | 01:25 | So this is kind of handy to have.
| | 01:27 | You might also decide okay, what part
of this workbook do I want to print?
| | 01:30 | And if you click this dropdown, you can
choose, well just the one sheet if have
| | 01:35 | active, if you have multiple sheets
that are active, or you can Print Entire
| | 01:38 | Workbook or the Selection.
| | 01:40 | None of that is really changed, but the
way they present it to you has changed a lot.
| | 01:44 | If you going to print multiple
copies, you can Collated or not Collated.
| | 01:48 | Now, here's something else,
| | 01:49 | before we look at this Orientation.
Let's go over here to Page Layout, and this
| | 01:53 | is something also they change in this version.
| | 01:55 | I think it's - well, long overdue,
but it's a great feature here.
| | 01:59 | Under Orientation, you see this is Landscape.
| | 02:01 | Now, let's go back. I'll just
press Ctrl+P and come back in here.
| | 02:05 | Now this is Landscape Orientation.
| | 02:06 | Well, what you used to happen in old
versions is you might have Landscape set up
| | 02:10 | in the document, Portrait set up here
in the Print dialog box, and then when you
| | 02:16 | print, you might get half the stuff cut
off, or you are not seeing things on the
| | 02:20 | page the way you expect them to be.
| | 02:21 | When I change this to Portrait here, and
I go back to Page Layout here and go to
| | 02:26 | Orientation, check it out;
| | 02:28 | it changes to Portrait.
| | 02:29 | So this is a really great feature, and
of course, we can do this a reverse.
| | 02:33 | If I go here and choose Landscape - I'll just
press Ctrl+P again - here it's back to Landscape.
| | 02:38 | So it changes to what you want instead
of having the two parts of the program
| | 02:44 | fighting with each other, which is great.
| | 02:46 | And here, what Paper Size do you
want? Kind of self-explanatory.
| | 02:49 | Do you want Letter Size, Legal size and so on.
| | 02:51 | I'll leave it with that.
| | 02:53 | And with Margins also this has
different options for margins, and I am just
| | 02:57 | going to leave the default there.
| | 02:59 | Now Scaling, they have this also in
previous versions, but they made this a
| | 03:03 | little easier, as well.
| | 03:05 | Scaling, do you want it to Fit to a Page,
do you want it Fit to One Page wide or
| | 03:09 | one page tall and so on.
| | 03:11 | And again, this is the small worksheet,
so I am going to leave No scaling here.
| | 03:15 | Now, maybe you're feeling a little
nostalgic, and you like the old method of
| | 03:19 | spelunking through all of these
multiple levels of dialog boxes to print.
| | 03:24 | Well, you can still do that.
| | 03:25 | Go down here to Page Setup, and here
is the old-fashioned Page Setup dialog
| | 03:30 | box, and there's everything that we have
here, but just in this less intuitive interface:
| | 03:36 | Portrait and Landscape,
here's your Scaling, Margins.
| | 03:40 | We had all that on there.
| | 03:42 | Headers/Footers, oh God!
| | 03:43 | You don't have to do this anymore, do you?
| | 03:45 | But you can still do it if you want.
| | 03:47 | And go to the Sheet tab and Print
titles and all these you can do it in
| | 03:50 | easier ways in Excel.
| | 03:52 | So I am just going to cancel out,
and that's really all there is to it.
| | 03:56 | Once you're here and you decide how many
copies you want, choose how many copies
| | 04:00 | you want and then click Print and
your worksheet has then printed out.
| | Collapse this transcript |
|
|
2. Collaboration ToolsSetting document permissions| 00:00 | If you were following along in
Chapter one, you saw some new features of
| | 00:04 | Backstage view, also known as the File tab,
where we saw a few new ways of saving
| | 00:09 | and opening and closing documents.
| | 00:11 | In this movie, I want to show you some
new ways of setting workbook permissions.
| | 00:17 | So let's go to the File tab and in
Chapter 1, we looked at this middle section,
| | 00:24 | but now let's take a look
at protecting the workbook.
| | 00:27 | So click that, and we have a half dozen options.
| | 00:30 | Now these features are not new, but
what is new is that these features are now
| | 00:34 | all in the same place.
| | 00:36 | Most of them are somewhat self-explanatory,
marking as final, so that someone who opens
| | 00:40 | that up will find the document is read only.
| | 00:43 | Encrypting with the Password.
| | 00:45 | That's self-explanatory.
| | 00:46 | It's been there for a long time.
| | 00:47 | Protecting Sheets and Protecting
Workbooks, also those have been in Excel for
| | 00:50 | many, many versions.
| | 00:51 | But what I want to spend a little
bit more time on are these two options
| | 00:55 | down here at the bottom.
| | 00:56 | Let's look at this one first, then
we'll go back up to Restrict Permission.
| | 01:00 | Click Add a Digital Signature, and you
will get this dialog box coming up, and
| | 01:05 | what this is saying is okay, if you
want a digital signature, you have to sign
| | 01:08 | up for one first, and there are many
different vendors that you can sign up
| | 01:12 | for a signature from.
| | 01:14 | And once you get the signature, then
it's kind of like notarizing a document.
| | 01:20 | If you've ever dealt with notarizing,
maybe getting a student loan or a mortgage
| | 01:25 | or something like that, it basically
says that you are who you claim to be.
| | 01:28 | So if you are going to order 1,000
barrels of olives for your Olive Oil Company
| | 01:33 | that the person knows that
it's really you who did that.
| | 01:36 | Well, we are not going to go into this
because this is a kind of a far field from
| | 01:39 | Excel, but I just wanted
you to see what that was.
| | 01:41 | So we could just cancel out.
| | 01:43 | Let's go back to Protect Workbook.
| | 01:44 | Now, let's go here to
Restrict Permission by People.
| | 01:48 | Now, the first item, by default
Unrestricted Access, also pretty
| | 01:51 | much self-explanatory.
| | 01:52 | That's just like a new
workbook that you've created.
| | 01:54 | But click Restricted Access, and you
get this dialog box which basically says
| | 02:01 | that if you want to use the
service, you have to sign up for it.
| | 02:04 | Now, if you work for a big company
and you need digital rights management,
| | 02:08 | there's a good possibility
that you have your own server.
| | 02:11 | If you really need this, you know what
this is all about, and you can now go
| | 02:15 | and sign up for the free service from
Microsoft, and you can just choose that right here.
| | 02:20 | Now, let's say you decide well, I
don't want to do that right now.
| | 02:23 | Well, when you cancel out, you're going
to get this error message saying that,
| | 02:26 | sorry, you can't use the feature.
| | 02:28 | You have to have the credentials first,
| | 02:29 | so just click OK.
| | 02:30 | Now, let's go back there for a moment.
| | 02:32 | Click on Protect Workbook,
back to Restrict Permissions.
| | 02:35 | If you decide you want to manage
credentials, maybe there are several people who
| | 02:39 | are going to be looking at your data,
click that, and again, you are going to get
| | 02:44 | the same screen coming up.
| | 02:46 | So you do have to get this service.
| | 02:49 | Even though it's free, you have to
deal with it first, and you can read
| | 02:52 | the screen on your own.
| | 02:53 | It's a pretty good explanation.
And again if you cancel out, it will give you
| | 02:57 | the same error message that you need to have
some credentials in order to use the feature first.
| | 03:02 | So you can just click OK.
| | 03:03 | And that's really it.
| | 03:04 | If this is something you need, you
probably know it already before watching this
| | 03:08 | movie, but what's really nice now, in
Excel 2010, is that they've integrated it in
| | 03:13 | Excel rather than making you go
out onto the web to do it first.
| | Collapse this transcript |
| Sharing a document by email | 00:00 | The most common way of sharing
workbooks, or any other type of document, is by
| | 00:05 | e-mail, and you probably
don't need me to tell you that.
| | 00:07 | But what is really nice is that Excel
2010 has made this easier by putting a
| | 00:12 | lot of the options in one place, and you can
do sharing by e-mail really with one click.
| | 00:17 | So let's go to it.
| | 00:19 | Click the File tab, so we go back into
Backstage view, and we want to go down
| | 00:23 | here to where it says Save & Send, and
at the top Send Using E-mail should be
| | 00:29 | selected automatically - if not, just click it.
| | 00:31 | And I want to go through these options here.
| | 00:33 | So the first option you have
here is Send as Attachment.
| | 00:37 | Just click it, and you see it creates a new
e-mail message with the file name as the subject.
| | 00:44 | There it is. It's attached, and now you could
just address it as you normally would, and then
| | 00:51 | you can type the body.
| | 00:54 | So in one click, you've been able
to take this workbook and make it an
| | 00:57 | attachment in the e-mail message.
| | 00:59 | By the way, you might be wondering,
wasn't there a way of putting the workbook
| | 01:03 | itself as the content of the
e-mail message in the body?
| | 01:07 | Well, that was an option up until a few
versions ago, but Microsoft decided that
| | 01:11 | it was causing too many problems, so
that hasn't been an option since 2003,
| | 01:16 | so you won't find that here anymore.
| | 01:18 | So let's cancel out.
| | 01:20 | We don't really have to send this
and No, I'm not going to save changes.
| | 01:24 | Now, let's go back to the File tab,
and let's go back to Save & Send, back
| | 01:28 | to Send Using E-mail.
| | 01:29 | Now this Send a Link, you'll notice this
is grayed out, and what's happening is this.
| | 01:33 | This workbook that we have is saved on
my computer locally, but if this were
| | 01:39 | saved on a network share where
several people could access it, there really
| | 01:44 | wouldn't be any purpose of sending it
as an attachment, because we would eat up
| | 01:48 | hard drive space unnecessarily.
| | 01:49 | You'd have a copy on the server, and you
have a copy here in yours, and then the
| | 01:53 | copy and the person who is receiving it,
and not only disk space, but it could
| | 01:57 | be confusing after awhile as to
which is the real current document.
| | 02:01 | So if you have several people with
access to the same file, then you can
| | 02:04 | simply send the link and anybody on your
network will get the link to that network share.
| | 02:09 | So that's just what that is.
| | 02:11 | Let's go down here to Sending as PDF.
| | 02:13 | Now, the same way that we were able to
send this an attachment, what we could do
| | 02:17 | here is just click this, and just like
before, we have the subject as the file
| | 02:23 | name and instead of the Excel
workbook as an attachment, in one step Excel
| | 02:28 | created the PDF and made this as
an attachment in the e-mail message.
| | 02:32 | And what's really nice is you don't even
have to have Adobe Acrobat or any other
| | 02:37 | PDF-creation software installed.
| | 02:39 | That is built-into Excel.
| | 02:40 | I think that's pretty cool.
| | 02:41 | Well, we don't really need to send
this, so we could just cancel out.
| | 02:46 | Let's go back to the File tab,
| | 02:47 | again, back to Save & Send, and we have two
other options: Send as XPS and Internet Fax.
| | 02:54 | XPS is sort of Microsoft's version
of PDF. Chances are you're using PDF
| | 03:00 | anyway. XPS works pretty much the same.
| | 03:03 | So let's click on Send as Internet Fax.
| | 03:05 | Now, to do that you have to have a fax
service set up and installed, and if not,
| | 03:10 | you'll get this message saying that yes, you
have to sign up with a fax service provider.
| | 03:14 | If we had this on this computer,
then it would launch right into the fax
| | 03:18 | software, and we'll be able to fax directly out.
| | 03:22 | And that's really all there is to it,
so just cancel out, and now you have some
| | 03:26 | very easy ways of sharing
your workbooks by e-mail.
| | Collapse this transcript |
| Sharing a document on the free Microsoft SkyDrive web site| 00:00 | Sometimes you need to share your work
with people who aren't on your network.
| | 00:04 | Maybe they work for different companies,
or you want to share documents with
| | 00:07 | the general public.
| | 00:09 | Other times you might want to make
documents available for download only to
| | 00:13 | people who have a password that you
give them, or you might want to store
| | 00:16 | documents online just for yourself to use,
kind of like having a virtual USB memory stick.
| | 00:21 | Well, Microsoft makes these
features available for free.
| | 00:24 | So let's take a look.
| | 00:25 | We are going to go to their SkyDrive web site.
| | 00:28 | So I am going to go up here and go to
SkyDrive, and also a little tip here for you:
| | 00:33 | when you want to go to a regular .com
you can just type the thing in the middle
| | 00:36 | and press Ctrl+Enter, and it
fills in the ww and all that.
| | 00:40 | This is a free service.
| | 00:41 | So the first time you're using it,
if you haven't signed up already, you
| | 00:45 | can click that Sign Up.
| | 00:46 | I've already created an
account, so I am going to sign in.
| | 00:51 | So I have logged in, and I
have my four default folders:
| | 00:55 | My Documents, Public,
Favorites, and Shared favorites.
| | 00:58 | Now you see Public has a little
globe on there, so that means that anyone
| | 01:02 | who stumbles onto my SkyDrive site will
have full access to anything that's in there.
| | 01:06 | Now My Documents has a lock, so that's
accessible only to people who have the password.
| | 01:12 | Also, don't confuse My Documents
here with My Documents on your computer.
| | 01:16 | Microsoft has just given the
same name to two different things.
| | 01:20 | Well, let's say I want to share an
inventory list of my olive products with
| | 01:24 | vendors, or again, anyone who has the password.
| | 01:27 | Now, even though My Documents is
password protected, let's say I have
| | 01:31 | other stuff in there.
| | 01:32 | I don't want to get it cluttered up.
| | 01:33 | So I am going to create a new folder.
| | 01:35 | So I will go up here.
| | 01:35 | I will Create the folder, and I will
call this Two Trees, and I am going to
| | 01:43 | share just with me, so that
will keep it locked. Click Next.
| | 01:47 | If I had any files that I wanted to
upload immediately, I could do that using
| | 01:51 | these Browse buttons here.
| | 01:53 | But I don't have anything right now,
so I am just going to click on the
| | 01:55 | Two Trees link here.
| | 01:57 | So now this shows me the folder.
| | 01:58 | And it's empty, obviously,
because I just created the folder.
| | 02:01 | Now that we have this set up, let's
go into Excel, and in Excel we can save
| | 02:06 | files into this folder in our SkyDrive account.
| | 02:11 | If you still have the workbook opened
from the last exercise, that's fine.
| | 02:15 | I have opened here 02_03 sky drive.xlsx.
| | 02:18 | I am going to into Backstage View, so
I will click the File tab, and let's go
| | 02:23 | down here to Save & Send, and in
this panel let's choose Save to Web.
| | 02:29 | Now you may have other things in there.
| | 02:31 | I am going to hit this Refresh button,
and there it finds that Two Trees.
| | 02:36 | Now you might be wondering, "Well wait a minute.
| | 02:38 | How on earth does Excel
know that I just created this?"
| | 02:42 | Well, we will come to that in a moment.
| | 02:44 | So first let's just click that and Save As,
or we could double-click it, either way.
| | 02:51 | Here, this is looking at the Two Trees folder.
| | 02:53 | Again, it's empty because we
haven't put anything in there yet.
| | 02:57 | So here is where you can
tell Excel where your folder is.
| | 03:00 | If this is the first time you are doing
it, what you want to do is go back into
| | 03:04 | the browser and select the URL from
the Address bar, copy it - I will just
| | 03:10 | Ctrl+C - come back in here, then you can
simply click in there, paste it in, and
| | 03:15 | then Save, and it will be there.
| | 03:17 | So that's how you can get Excel
to find it for the very first time.
| | 03:20 | Once you've done that, you
don't have to do that again.
| | 03:23 | So I am going to keep the same file
name, I will click Save, and you see down
| | 03:28 | here, it says Uploading to
the server, and now it's saved.
| | 03:32 | So up here, you can see
the file name hasn't changed.
| | 03:35 | Now let's go back into the browser,
and by the way, you could use Internet
| | 03:37 | Explorer or Firefox;
it works well with both of them.
| | 03:41 | Now, you say, "Well, wait a minute.
| | 03:42 | There is nothing here."
| | 03:43 | Well, we have to Refresh.
| | 03:45 | So you could either click the
Refresh button, or press the F5 key on your
| | 03:48 | keyboard, and there it is.
| | 03:50 | So there is the file that we have just saved.
| | 03:51 | So now we know - there is no smoke and
mirrors - that the file that we saved in
| | 03:55 | Excel is indeed up in the cloud.
| | 03:57 | So let's go do something with it.
| | 03:59 | So I am going to click the icon here,
and you see we have a few options, and
| | 04:03 | here is a web address.
| | 04:04 | So if I wanted to share this file with
someone who has my ID and password, I
| | 04:08 | could simply copy this web
address and e-mail them the link.
| | 04:12 | Well, right now let's click
this big icon, and there it is.
| | 04:17 | There is the file that we just had.
| | 04:19 | Now right now you can't edit it.
| | 04:21 | You can click around, you can scroll up
and down, but there's really no editing.
| | 04:25 | So if all you need to do is
view it, well that's great.
| | 04:29 | But maybe you want to edit this in the browser.
| | 04:32 | So click up here, Edit in browser.
| | 04:36 | Now we get this error message, says,
This workbook is locked. Let me OK it.
| | 04:40 | Well, here's what's happening is
we still have it open in Excel.
| | 04:45 | So you need to remember to close the workbook
in Excel first before opening it up on the web.
| | 04:50 | So I am going to do, I will
just press Ctrl+F4 to close it.
| | 04:53 | Now let me go back here into the browser,
and I am going to Refresh. There it is.
| | 05:00 | Now it still just view only.
| | 05:03 | Now I will click Edit in
browser, and here it is.
| | 05:05 | Now it's opened for editing, and now
you see a Ribbon bar that looks kind of
| | 05:09 | like what you have in Excel and you
see up here, this tells you it's a
| | 05:13 | Microsoft Excel Web App.
| | 05:15 | And at the end of this course, I am
going to go back into the Excel web app in a
| | 05:18 | little bit more detail.
| | 05:19 | But for now, I just want to show you few things.
| | 05:21 | So let's say if I decide that I want to
change some numbers, I can go and do that.
| | 05:29 | If I want to change some of the
formatting, I can do that as well.
| | 05:34 | I could also go and add things.
| | 05:36 | I am going to go down here.
| | 05:38 | Let's say I want to add the total of
column B. Well, you might notice that this
| | 05:42 | Ribbon bar doesn't have an AutoSum tool.
| | 05:45 | So you have to know some functions.
| | 05:46 | I am going to use the Sum function.
| | 05:48 | So I will just say =sum open up the
parenthesis, and I want to get the sum
| | 05:53 | of, now this is B5, through - just
scroll down - through B27, enter it, and
| | 06:03 | there it gives me the total.
| | 06:04 | I will just type the word "Total."
| | 06:07 | Also, you notice there is no Auto Fill,
so I would have to go and manually
| | 06:12 | copy that, go down here,
| | 06:15 | double-click and paste, but instead of
column B, I want column C. So you can do
| | 06:25 | some calculations, if you know what
they are without having to use tools.
| | 06:29 | Now let's say I am done editing the file
on the web, and you might wonder "Well,
| | 06:33 | okay, wait a minute.
| | 06:35 | If I have Excel, why on earth would I
want to go in here that has a nice Ribbon
| | 06:39 | bar, but it's only a little bit of the
ribbon that they have in Excel, why would
| | 06:42 | I want to edit it here as opposed to
Excel?" and the reason is let's say you
| | 06:47 | might be on the road.
| | 06:48 | Maybe you're at your client's office
and you want to do some quick edits.
| | 06:51 | Maybe you don't have your laptop with you
and you want to be able to do a quick edit.
| | 06:56 | So you could come in here and do some
quick edits, and then when you get back to
| | 07:00 | your office, then you can go
and download it and use it.
| | 07:03 | So the Excel web app is great, but
it's not really a full substitute for the
| | 07:09 | entire Excel package.
| | 07:11 | But if you want to save this to go and use it,
well you notice, hey there is no Save button.
| | 07:15 | And if you go to the File tab,
you'll notice there is a Save As.
| | 07:19 | If I want to save it under a new name, it
even tells us, hey, where's the Save button?
| | 07:22 | The reason there isn't a Save button
is this is saving for you automatically.
| | 07:26 | Every time you make a
change and Enter, it saves it.
| | 07:30 | So what I want to do is I
want to open this up in Excel.
| | 07:33 | So I will click this, Open in Excel,
and it's giving me a warning. Click OK.
| | 07:39 | And now it's going to take that file
from SkyDrive and download it, open it here -
| | 07:45 | boom, and there it is.
| | 07:46 | So now I would say, oh, maybe I want to put my
AutoSum tool here, and I'll just put that in.
| | 07:51 | Now I have to enable editing,
because this came from the web.
| | 07:54 | So we will have to click up
here, and it's perfectly fine.
| | 08:01 | Now, of course, it's adding
the wrong thing, but that's okay.
| | 08:04 | I can just add the correct stuff.
| | 08:06 | Press Ctrl+Enter, and there it is.
| | 08:09 | So I could edit it here, as well
as I can edit it up on the web.
| | 08:12 | Also, notice the file name has
changed a little bit to be web compatible.
| | 08:16 | I am going to save it.
| | 08:17 | You see it's Uploading to server. To close it,
| | 08:20 | I will press Ctr+F4.
| | 08:22 | Now let me go back here into SkyDrive,
and now I will click the File, go back
| | 08:29 | in, and there you see is the
total that I added in Excel.
| | 08:35 | So you can roundtrip this pretty easily.
| | 08:38 | So I am going to go back
into the Two Trees folder.
| | 08:42 | There is my file, so now at anytime, I
could come back in here later and edit it.
| | 08:47 | So SkyDrive is a really great feature.
| | 08:50 | It's not a full substitute for Excel,
but it's really nice being able to put
| | 08:54 | your work up in the cloud, so that
you can retrieve it whenever you want.
| | Collapse this transcript |
| Sharing a document through a Microsoft SharePoint portal| 00:00 | If you use Microsoft SharePoint, you'll
love how easy it is now to use with Excel.
| | 00:05 | The integration between SharePoint
and all the Office 2010 applications
| | 00:09 | is almost seamless.
| | 00:10 | Now if you still have the workbook
opened from the previous movie, you could
| | 00:13 | leave it open, or if you have access to
the exercise files, you can go into the
| | 00:17 | chapter 2 folder and open up
this 02_04_sharepoint.xlsx.
| | 00:23 | So let's go into Backstage view.
| | 00:25 | Click the File tab here, and go down
here to Save & Send, and then go over
| | 00:30 | here Save to SharePoint.
| | 00:32 | If you have your SharePoint Portal
already configured, it's going to be up here,
| | 00:36 | so all we have to do now is Save As.
| | 00:39 | Now you see when I go to Save As,
it's going into this Inventory folder.
| | 00:45 | Let me go back to the browser for a second.
| | 00:48 | Here in our portal, in our Two Trees
Olive Oil Company, I've already created a
| | 00:52 | document store called Inventory.
| | 00:54 | So I'm going to click that.
| | 00:55 | So you see this is empty.
| | 00:56 | So this is what we're looking at here in Excel.
| | 00:59 | So I'm going to keep the
same name. I'll click Save.
| | 01:06 | And there it is. It saves it, and it brings us
back into the web browser with the file open.
| | 01:13 | So you can see here, you can select
cells, you can scroll, you can look at the
| | 01:18 | things, but there's no editing
that you can do. Not yet, anyway.
| | 01:23 | I'm going to go back into Excel.
| | 01:26 | And I'm going to close the file.
| | 01:27 | I'll just press Ctrl+F4.
| | 01:29 | Now we come back into the browser.
| | 01:32 | So let's say I come in here one
day, and I want to do some editing.
| | 01:37 | You can keep in mind in real life you
would be switching back and forth like this.
| | 01:42 | I'm going to click Open in Excel.
| | 01:48 | And it gives me a warning.
| | 01:48 | I'm going to choose Edit. Click OK.
| | 01:50 | And it brings me back, but notice it
gives me this yellow warning, just in case
| | 01:54 | there's some nefarious code on there.
| | 01:56 | But I know what this is, so
I'll click Enable Editing.
| | 01:59 | And now I'm right back in Excel.
| | 02:00 | And let me make some changes here.
| | 02:02 | Now we can see what's going on.
| | 02:06 | Maybe I'll take this, and I'll make it bold.
| | 02:09 | And you know what, while we're
at it, let's go and put in a sum.
| | 02:15 | Okay, I'll just type the word Total.
| | 02:21 | So now we see we have some changes.
| | 02:23 | Let me scroll back up.
| | 02:24 | Now I'm going to save this.
I'm going to close it.
| | 02:28 | Let's go back into the SharePoint Portal.
| | 02:32 | And now you see nothing has happened here yet.
| | 02:35 | I'm going to go to the File menu,
Reload Workbook, choose Yes to confirm.
| | 02:42 | And there it is, and there is our Total.
| | 02:44 | And there are the new numbers that we typed in.
| | 02:46 | So this is all working very well.
| | 02:49 | Let's go back to the Inventory folder,
| | 02:51 | so I'll just click that Inventory folder.
| | 02:54 | And now here we see the
file is there, as we saved in.
| | 02:57 | You notice this little new marker, so
that's how you know the thing is new.
| | 03:01 | When you roll over it, if you click
it, that's another way to open it up.
| | 03:06 | Let's go back here to the Inventory folder.
| | 03:08 | I want to show you a couple of things.
| | 03:09 | When you roll over, you
notice this little check box.
| | 03:12 | And if you check that, now all of a sudden
you have this whole Ribbon, full of options.
| | 03:18 | Now this is not complete SharePoint training.
| | 03:21 | But I just want to show you a couple things.
| | 03:22 | If I wanted to delete the
document, I could do it there.
| | 03:25 | I'm not ready to do that just yet.
| | 03:27 | One really nice feature that
SharePoint and Excel share now is that Excel
| | 03:33 | recognizes SharePoint's
Check In and Check Out feature.
| | 03:36 | And what that's all about is, if I'm
going to edit this file, I want to make sure
| | 03:40 | that no one else is editing it
simultaneously because then there is a conflict
| | 03:45 | of which is the right version.
| | 03:47 | So when I have this selected,
I'm going to click Check Out.
| | 03:51 | You see there's this little icon there.
| | 03:53 | When checking it out, it doesn't mean
that I've actually opened the document or
| | 03:57 | retrieved the document.
| | 03:58 | It simply means it's locked, so that
nobody else can edit it while I'm editing it.
| | 04:03 | So I'm going to go back to Excel.
| | 04:05 | I'm going to reopen it.
| | 04:06 | This time I'm going to go back to the File tab,
and in my Recent File list, there is the file.
| | 04:11 | And you can see the path
there is the portal.
| | 04:15 | So I'm going to open it up.
| | 04:17 | And this time maybe I'll
make all of these items Bold,
| | 04:21 | again, so it's just an obvious difference.
| | 04:25 | I have to close this in order
to edit it, back in SharePoint.
| | 04:29 | So I'm going to save it.
| | 04:32 | Now when I close it, notice the message.
| | 04:34 | It tells me that I need to check it in in
order for someone else to be able to check it out.
| | 04:39 | So I'm going to choose
Yes that I'm checking it in.
| | 04:41 | And I could enter some comments, like
Changed some formatting, and click OK.
| | 04:49 | I'll go back to the portal.
| | 04:51 | And I'm going to refresh.
| | 04:53 | Just hit the F5 to Refresh.
| | 04:55 | And now you notice that the
Check Out symbol is no longer there.
| | 04:58 | So someone else can come in and edit the file.
| | 05:01 | If you want to learn more about
SharePoint in depth, take a look at SharePoint
| | 05:06 | Essential Training here on lynda.com.
| | Collapse this transcript |
|
|
3. Data ToolsUsing enhanced smart tags and the Paste preview| 00:00 | Microsoft discovered that about 90% of
the time the first thing the user does
| | 00:05 | after pasting is to undo.
| | 00:07 | So obviously something
wasn't working quite right.
| | 00:09 | So now when you paste, you'll see the
New Paste Options pop-up which would make
| | 00:13 | it easier to choose what you want.
| | 00:15 | So even if you don't get what you expect
immediately, you'll have a better shot at doing it.
| | 00:20 | So let's do this.
Let's click inside this table here.
| | 00:23 | And when you do, you may see this
Table tools and Design tab come up.
| | 00:26 | And that's really not important right now.
| | 00:28 | What we want to do is select
all of the cells in this table.
| | 00:32 | Now press Ctrl+A to select all.
| | 00:34 | That selects all the data.
| | 00:35 | Press Ctrl+A a second time, and
that selects the column headers and the
| | 00:40 | total row down at bottom.
| | 00:42 | Now copy to the clipboard as you normally
would. I'll press Ctrl+C. And let's go to Sheet2.
| | 00:47 | And I'm going to go over here to around E6, because
I want to leave that left side opened. You'll see why.
| | 00:53 | Rather than just pasting in, I'm
going to click on the Paste button here.
| | 00:57 | So now you can see the menu that comes out.
| | 00:59 | Now if you simply roll over these, it
shows you a preview of what you will
| | 01:04 | get before you paste.
| | 01:06 | Now this first - let me go
back to this for a second.
| | 01:07 | This is what you'd get if you just
did a normal paste, if you just pressed
| | 01:10 | Ctrl+V. But you can see just the formulas,
formulas & numbering format if you have any.
| | 01:16 | If you have borders, you can get rid of them.
| | 01:18 | The next one is pretty cool, is if you want
to keep the column widths, you can do that.
| | 01:23 | If you are just concerned with the
values, you have some other options here.
| | 01:26 | And you can see the tooltips that come up.
| | 01:28 | Here is the new thing that I
want to show you is Paste Picture.
| | 01:32 | So I'll choose this.
| | 01:34 | So now this looks like a regular Excel
worksheet, but it really is a picture.
| | 01:38 | And you can see there are sizing handles.
| | 01:40 | So I could do things like stretch it out,
I can shrink it, and I can rotate it.
| | 01:45 | Let me make this a little bigger here.
| | 01:47 | When I get this rotation handle, I can
rotate it, because it's a picture and you
| | 01:52 | see it's floating on top of the worksheet.
| | 01:55 | In older versions of Excel, you were
able to copy as a picture and paste as a
| | 01:58 | picture, but had to jump hoops and
find hidden commands to do that.
| | 02:02 | So here it's right up in front,
which is a really great feature.
| | 02:05 | Let's say you wanted to have maybe a bunch of
sheets, kind of as collage, one on top of another.
| | 02:11 | This is a great way to do it.
| | 02:13 | You notice it doesn't look as smooth
and nice as a regular sheet, just because
| | 02:18 | it's turned into a picture, and it's
rotated - not much we can do about that.
| | 02:21 | But if that's something you
needed that's right there.
| | 02:24 | Okay, I'm just going to delete this here.
| | 02:26 | And you notice down on bottom, it
says Select destination and paste because
| | 02:30 | it's still the clipboard, if you go
to sheet where you can see the marching
| | 02:32 | ants are still there.
| | 02:33 | And this time instead of using the
Paste button up here, I'm just going to do a
| | 02:37 | plain old Ctrl+V. And now we have the Smart Tag.
| | 02:41 | Now the Smart Tags have been
around for a couple versions.
| | 02:43 | But now when you click it, you get
the same options that you did before.
| | 02:47 | But there's a bit of a difference.
When we used the Paste button on the Ribbon
| | 02:51 | before, we got a preview before we click.
| | 02:53 | Now here when you rollover, it shows
you the same little tooltips, but nothing
| | 02:58 | happens until you click.
| | 02:59 | So I'm going to choose Keep Source Column Width.
| | 03:01 | So when I roll over or
roll away, nothing happens.
| | 03:04 | When I click it then it keeps the column width.
| | 03:06 | So that's really the difference is do
you want to see the preview before you
| | 03:09 | paste or after you paste?
| | 03:11 | But this is a really great feature,
and you don't really have to do much
| | 03:14 | to learn about it.
| | Collapse this transcript |
| Filtering a table by searching| 00:00 | Tables aren't new to 2010, but there are
a couple of table enhancements that are.
| | 00:05 | The first I want to tell you
about is multithreading, which is a
| | 00:08 | performance enhancement - nothing I
can actually show you, but I just want
| | 00:11 | to tell you briefly.
| | 00:12 | What it means is that Excel can really do
more than walk and chew gum at the same time;
| | 00:16 | it can do many things at the same time,
which means that it works faster and
| | 00:20 | with fewer errors, if you are
dealing with large data sets.
| | 00:24 | Well, as an analogy with
multithreading is all about, well, don't get
| | 00:27 | multithreading confuse with multitasking.
| | 00:29 | We are all familiar with multitasking
of driving and talking on your cell phone
| | 00:34 | and drinking your coffee, which
hopefully you don't do, but those are three
| | 00:37 | completely separate, independent activities.
| | 00:40 | Multithreading means the same
activity doing multiple parts of it.
| | 00:44 | So let's say if you have an activity
that you call getting ready for work, and
| | 00:48 | you shower, you shave, you
brush your teeth, you feed the cat.
| | 00:51 | Well, imagine if you could do all
of those things all at the same time.
| | 00:55 | Well you can get ready for
work a heck of a lot faster.
| | 00:57 | That's what multithreading is all about.
| | 01:00 | Let me show you something
that you can actually see.
| | 01:02 | If you're looking at this exercise file,
03_02_filter.xlsx, click somewhere here
| | 01:08 | in this data area, and we're going to
turn this into a table, and this works a
| | 01:11 | same way as it did in 2007.
| | 01:14 | Click the Insert tab, then click Table,
and of course we are not inserting a table;
| | 01:18 | we're really converting
this data area to a table.
| | 01:21 | And it guesses correctly, our data area, and it
sees that we have table headers. That's great.
| | 01:26 | Click OK.
| | 01:27 | And if you need to stretch out the
columns feel free, but just click somewhere
| | 01:30 | in here so that you deselect.
| | 01:32 | Well, if you click this down arrow
here for this Column1 of what our products
| | 01:37 | are, some of this is not new.
| | 01:39 | You can Sort A to Z. You can Sort Z to A.
That's really - I am going to go back
| | 01:43 | A to Z - that's really not what we
came here for. Here's what's new.
| | 01:47 | Click the down arrow, and you see we
have all of the products are selected, and
| | 01:53 | of course, we can deselect.
| | 01:54 | But let's say we wanted to
display products by searching.
| | 01:58 | So click in here and start typing C
and A, and you see it starts filtering.
| | 02:05 | Type N, and now you see it filters
just those products that begin with C-A-N.
| | 02:13 | Click OK, and now you see just those
products that begin with C-A-N, and if you
| | 02:18 | take a look at the row numbers here,
you can see that it's filtered, that it's not
| | 02:22 | displaying every row.
| | 02:23 | When you decide that you want to see
all of your data again, click the Filter
| | 02:28 | button, and a couple things you could do.
| | 02:30 | So either you could click that Select
All so it's checked and everything is
| | 02:33 | checked, or you could click this
Clear Filter From "Column1", and now
| | 02:38 | everything is back again.
| | 02:39 | So that's really what it's all about.
| | 02:41 | So that might seem like a little
feature, and maybe it is, but I think it's
| | 02:45 | really big improvement, especially if
you have a lot of data that you want
| | 02:49 | to search for.
| | Collapse this transcript |
|
|
4. Graphic FeaturesInserting Sparklines | 00:00 | Let's face it; spreadsheets aren't the most
exciting things in the world, but the feature I
| | 00:04 | want to show you in this movie
is a really cool new feature.
| | 00:08 | Sparklines are like tiny
little charts that fit in a cell.
| | 00:11 | They are fairly easy to use, and they have
some nice features, so let's check them out.
| | 00:15 | The idea is this.
| | 00:16 | We have all our data here, and
we have some space over here.
| | 00:19 | So we'll put the charts, the little
sparklines, in this column. So let's start.
| | 00:25 | I'll click the first number, scroll down.
| | 00:28 | I'll click the last number, and
sparklines are things that you insert.
| | 00:31 | So let's go to the Insert tab, and
over here is the Sparklines group.
| | 00:36 | Let's start of with Line, so click Line.
| | 00:38 | And we already have the data range, in
fact if you click up here in Data Range
| | 00:42 | you can see the marching ants.
| | 00:43 | So I'll go down here to Location Range.
| | 00:46 | I'll click the first cell there in H5,
scroll down, and I'll Shift+Click down at
| | 00:51 | the bottom there, and then click
OK, and boom, it's just put them in.
| | 00:55 | So I'll just click to deselect. The width
of the column could be anything we want.
| | 00:59 | If we stretch it out, the lines get more flat.
| | 01:01 | If you make it really narrow, they get more
pointed. I'll just move them back to where they were.
| | 01:05 | We can also modify where the data are
coming from, and here's what I mean by that
| | 01:11 | is when we go over here to Edit Data
and choose Edit Group Location & Data, we
| | 01:16 | can change where we want the sparklines to
go or where we want the Data Range to be.
| | 01:21 | So you can modify any of this.
| | 01:22 | I am just going to cancel out.
| | 01:23 | Also, let me go back here to Edit Data.
| | 01:26 | If we want to edit the location of one
specific sparkline, right now I have this
| | 01:31 | one sparkline active, cell H7,
| | 01:33 | when I click here Edit Single
Sparkline's Data, if you wanted to, you could
| | 01:38 | redirect the data to pick
up cells somewhere else.
| | 01:42 | I am not going to do that.
I just want you to see it.
| | 01:44 | So I am going to cancel out.
| | 01:45 | Let's scroll back up here.
| | 01:47 | Now I said that you could also choose
where the lines are going to go first and
| | 01:52 | then pick up the data, so
let me show you how to do that.
| | 01:55 | Now you can't simply delete. What you
do is you make sure that any one of the
| | 01:59 | sparklines are selected and up here
make sure that the Sparkline tools, and the
| | 02:03 | Design tab is selected and go over here
to Clear, and just choose Clear Selected
| | 02:09 | Sparkline Groups, and they are gone.
| | 02:12 | So let's do the reverse.
| | 02:14 | We'll select the range first where the
sparklines are going to go and then the data.
| | 02:18 | So let's go up here to H5, I'll go
down here, and I'll Shift+Click on H29, and
| | 02:25 | again, we'll go back to Insert tab.
| | 02:26 | I'll go to insert a line, and here it
has a location range, and now we'll
| | 02:31 | choose the Data Range.
| | 02:33 | So click at the top, Shift+
Click at the bottom. Click OK.
| | 02:37 | So it's a same difference. Really,
doesn't matter which way you like to do it.
| | 02:41 | Let's do a little bit of formatting here.
| | 02:43 | Now because this is a group, you don't
have to have the whole thing selected;
| | 02:46 | as long as anyone of these Sparklines are
selected, you'll end up formatting the whole thing.
| | 02:51 | So we could look at things like
the First Point, the Last Point.
| | 02:56 | You see the little dots are coming on
there, if you want to see the High, the
| | 02:59 | Low, if you have Negative Points and if
you'd want to see every little marker,
| | 03:04 | that is every little data point, every
change, you can click Markers over here.
| | 03:09 | So you could turn these on and off as you like.
| | 03:11 | Change a couple of other things.
| | 03:12 | Let's change the Color.
| | 03:15 | Let's change the Weight, make these a
little thicker. And maybe let's change the
| | 03:20 | Marker Colors, over here. And from that
you have all these different styles that
| | 03:27 | you can choose from.
| | 03:28 | So you really have a lot of control over it.
| | 03:31 | We have some negative values, so I may
remove some of these points here, so just
| | 03:37 | the negative points are sticking up.
| | 03:39 | So you can see them, and they are okay,
but I'm going to show you a way of
| | 03:44 | seeing much more clearly
where the negative values are.
| | 03:48 | So instead of having lines,
maybe you want to have columns.
| | 03:51 | So let's just click over here on a
column, and boom there you are, and we could
| | 03:55 | do the same thing, like with any of the
others we can change the colors and the
| | 04:00 | Markers and so forth.
| | 04:01 | But what I really want to show you is Win/Loss.
| | 04:04 | So click over here, Win/Loss.
| | 04:06 | Again, we make sure that
anyone of these is selected.
| | 04:09 | Now the negative ones, you can see the
lines are a little bit below that, but
| | 04:13 | that's really hard to see.
| | 04:15 | So let's go over here to Marker
Color, and let's turn Negative Points
| | 04:21 | into something else.
| | 04:22 | So here if they are bright yellow or some
other bright color, now they really stand out.
| | 04:29 | So if all you are concerned with is
positive value and negative value, this, I
| | 04:33 | think, is the best way to do it, and
you can still change any of the other
| | 04:38 | formats that you like.
| | 04:39 | So my advice is: Go play around with
them, and I think you are going to enjoy
| | 04:43 | the new feature.
| | Collapse this transcript |
| Applying the enhanced conditional formatting features| 00:00 | Excel has done Conditional Formatting
for a long time, and the feature got a big
| | 00:04 | facelift in the last version.
| | 00:06 | What's new in this version are some
new formatting choices and the ability to
| | 00:11 | apply multiple rules, one on top of another.
| | 00:13 | First thing is we want to
select all the data here.
| | 00:16 | You can take your mouse and drag down to
the end, but to make it a little easier
| | 00:20 | for you, I created a range name, so you
could click that down arrow up here in
| | 00:24 | the Name box and choose data and select it all.
| | 00:27 | So you want to be in the Home
tab, and let's go over here to
| | 00:31 | Conditional Formatting.
| | 00:32 | First, I'll show you the cosmetic improvements.
| | 00:35 | If you go down here to Data Bars, you
can see in addition to gradient fills, we
| | 00:39 | now have solid fills for automatic comparison.
| | 00:44 | In Color Scales, we now have more color
scales to choose from. That's very nice.
| | 00:49 | And if you look at the
Icon Sets, we have more icons.
| | 00:52 | So, for example, we have more arrows,
and for like cell phone-type bars, we have
| | 00:58 | a four bar scale, and we
also have a five bar scale.
| | 01:02 | And here are some box icons that are brand-new.
| | 01:05 | The whole purpose for using any of
these icons is, if you simply want to compare
| | 01:09 | the numbers with each other, low
numbers, medium numbers, high numbers, rather
| | 01:14 | than writing formulas and doing all
sorts of complicated comparisons, the
| | 01:18 | Conditional Formatting Color Scales
just do a lot of that work for you.
| | 01:22 | So you can get a rough idea of what's happening.
| | 01:24 | Let me show you the real nice
improvement in Conditional Formatting.
| | 01:28 | Let's go down here and choose New Rule,
and we have all different rules that
| | 01:34 | we can choose from, and let's choose
the first one here: Format all cells
| | 01:38 | based on their values.
| | 01:39 | And let's get maybe a greenish set of colors.
| | 01:44 | So we'll do 3-Color Scale, going from
maybe a fairly dark green to a medium
| | 01:50 | green, to a very light green. There's our scale.
| | 01:55 | Click OK.
| | 01:56 | Now click somewhere over here to deselect.
| | 01:58 | So we have what we would expect
from dark green to light green.
| | 02:02 | But let's say, in addition to this, we
wanted to flag any numbers lower than 100,
| | 02:08 | and we wanted to make those very
dark and make them stand out quite a lot.
| | 02:12 | So let's go back and select the data area.
| | 02:16 | Now let's go back to Conditional
Formatting and go down to Manage Rules.
| | 02:22 | So here is where we can
pile one row on top of another.
| | 02:26 | So let's go over here and choose New
Rule and take cells that only contain, and
| | 02:31 | where it says, Cell Value, let's click
this where it says between, and let's
| | 02:35 | choose less than or equal to, and let's make it
100, and let's set a Format. We want bold text.
| | 02:45 | We want the color to be white.
| | 02:49 | Let's go over here to Fill,
and I'll go here to More Colors.
| | 02:53 | I'll choose a very dark green. Click OK.
| | 02:55 | Okay, there is our sample. Click OK.
| | 02:59 | Now we have our two rules, and we
can reorder the rules if we want.
| | 03:03 | If we want to evaluate these low
numbers after the Color Scale we can hit the
| | 03:07 | down arrow, or vice versa, hit that up arrow.
| | 03:10 | Also, you see where it says, Stop If
True. What that means is after Excel
| | 03:14 | does its initial analysis, we can stop
processing rules for those cells that it flags.
| | 03:20 | So if it flags something that's less
than 100, Excel won't go and override that
| | 03:25 | with this other color scale.
| | 03:27 | So click OK and deselect.
| | 03:30 | Now we can see those very low numbers
have been flagged in, reverse text, while
| | 03:36 | all of the other numbers are
in the automatic Color Scale.
| | 03:39 | So if you want to do a quick comparison
of numbers, but maybe you also have an
| | 03:43 | idea of a certain range, a specific
range that you want to flag, the new
| | 03:48 | features here of Conditional Formatting,
I think, will probably fit your bill.
| | Collapse this transcript |
| Formatting math equations with the improved equation tools| 00:00 | What we're going to look at in this
movie will make you very happy if you work
| | 00:04 | with applied math, like economics,
statistics, engineering, maybe physics.
| | 00:08 | Excel now has the ability to create
beautifully-formatted equations with all the
| | 00:12 | symbols you know and love, like
integrals, sums, radicals, matrices and so on.
| | 00:16 | Just keep in mind that Excel isn't
actually going to solve the equations -
| | 00:19 | that's your job - but it will
make them look nice on the page.
| | 00:24 | Also, just to let you know that the
equations are built on MathML, which is
| | 00:28 | an application of XML, and you might be
familiar with XML, if you ever do any web development.
| | 00:33 | If not, don't worry about it.
| | 00:34 | So let's just scroll down here.
| | 00:36 | This is an example of the Lorentz
Transformation, which has to do with speed
| | 00:40 | close to the speed of light,
but let's ignore that for now.
| | 00:44 | Equations are things that we insert.
| | 00:45 | So let's go to the Insert tab, go over
here to Equations, towards the end, and
| | 00:50 | just click the down arrow, and
here are some pre-made equations.
| | 00:53 | So if you need to insert the
Binomial Theorem, you can just click that.
| | 00:57 | You see it comes up kind of tiny, so
you might need to stretch it out in order
| | 01:00 | to make it a little more visible.
| | 01:02 | I'll just move that over here, and
I'll show you maybe one or two others.
| | 01:05 | I'll go up to the Insert tab, click
the down arrow here, and let's say
| | 01:11 | maybe Expansion of a Sum.
| | 01:12 | It's kind of a common equation.
| | 01:14 | Again, this goes in kind of tiny, so you might
need to stretch it out to make it look right.
| | 01:20 | Now, if that's all you need to do, well
that's great, but I'm going to show you
| | 01:24 | how to build an equation from scratch.
| | 01:25 | So let's just delete that.
| | 01:27 | We'll click in here and just select that and
delete it, now let's go down maybe over here.
| | 01:31 | So go back to the Insert tab, and
this time instead of clicking the down
| | 01:35 | arrow, click the pi itself, and here you
have all kinds of great stuff. Here are symbols.
| | 01:39 | If you just need to insert
specific math symbols, you have that.
| | 01:43 | Let me just close that.
| | 01:45 | You have fractions, you have
superscripts, you have radicals, you have
| | 01:51 | integrals, and notice you have even
doubles, and triples, you have volumes,
| | 01:56 | large operators, brackets. I'll show you
some things with brackets and matrices later.
| | 02:00 | You have some logarithmic functions,
and accents here and various types of
| | 02:05 | operators and matrices of different dimensions.
| | 02:08 | We'll use some of those.
| | 02:09 | We have this box that says Type equation here;
| | 02:11 | let's just move this down underneath it.
| | 02:13 | I'm going to stretch it out.
| | 02:15 | And right now, it's just the box that's
selected, so you only have the Drawing
| | 02:19 | tools and Format tab there, but click
on where it says Type equation here, and
| | 02:23 | now you see how the Equations tools show up.
| | 02:25 | So now click it; now we can start typing it up.
| | 02:28 | Let's just begin at the beginning over here.
| | 02:30 | We have a 1 x 2 matrix.
| | 02:32 | So I'm going to go over here.
| | 02:34 | Now the thing is if you click on the matrix,
you see you have a 1 x 2, but not with brackets.
| | 02:40 | So let's put the brackets in first.
| | 02:42 | So let's go over here. I'll put in
a bracket. There's only one item in there.
| | 02:45 | So I'm going to click this little box.
| | 02:47 | Whenever you see this little dotted box,
that's what will be replaced when you
| | 02:50 | choose something else.
| | 02:51 | So I need a 1 x 2, so I'm going to go
over here, scroll up, and I'll choose 1 x 2.
| | 02:58 | We have T prime and Z prime.
| | 02:59 | So that's Time and the Z
axis, but we need a script.
| | 03:04 | So I'm going to leave that selected.
| | 03:06 | Click this one here.
| | 03:07 | So now I can put in my T, and for
Prime I'm just going to type in an
| | 03:11 | apostrophe, and it italicizes it, and over
here I need the Z axis, so the same thing.
| | 03:17 | I'll put in a Z, and I'll type in my apostrophe.
| | 03:21 | Now, I need an equal sign.
| | 03:22 | I'll click to the right of the bracket.
But notice what happens; Excel kind of
| | 03:26 | thinks that we're no
longer editing the equation.
| | 03:28 | You see the tab is removed.
| | 03:29 | So you have to go back to the Insert
tab and then click on the pi again.
| | 03:33 | That's just something
that happens every so often.
| | 03:35 | Now, I'll type the space, and I'm
going to click on Equal sign up here, and I
| | 03:40 | click that equal sign rather than
typing it on the keyboard because it's just
| | 03:43 | a little bit bigger.
| | 03:44 | I'll type another space.
| | 03:45 | Now, I need a fraction.
You see we have an inverse.
| | 03:47 | So I'm going to go up to Fraction, and
this is what's called a Stacked Fraction.
| | 03:52 | This is kind of a big fraction.
| | 03:54 | So I'll go over here, and I'll put in my 1.
| | 03:56 | Now, the denominator needs to be a radical.
| | 03:58 | So I am going to select that, put in a
radical, and click on the box, and I'll say
| | 04:03 | 1 minus. Now I need a smaller fraction in here.
| | 04:08 | So when I go back to Fraction, instead
of this big Stacked Fraction, I'm going
| | 04:12 | to choose the Small Fraction over here.
| | 04:15 | So now I need my velocity
squared, so I'll choose the script.
| | 04:20 | So I'll just type in V and my square,
and down here on the denominator I need
| | 04:26 | the speed of light squared, so again
I'll choose the script, and I can put in
| | 04:30 | my C and my square.
| | 04:33 | Now, I need a 2 x 2 matrix in brackets.
| | 04:37 | So I'll click over there again.
| | 04:38 | You notice the thing disappears.
| | 04:40 | So I'm going to go back to the Insert tab.
| | 04:42 | Click on Equation. Now when I go to
Matrix, I need to scroll down a little bit.
| | 04:46 | Here is a 2 x 2 in brackets.
| | 04:49 | I'll just put in the easy numbers first.
| | 04:52 | So there's 1, and there's a 1, over here
we have negative velocity, and over here
| | 04:57 | I need a little fraction.
| | 04:58 | So I'm going to go back here, put
in another little fraction, negative
| | 05:03 | velocity, and the numerator, and
here I need speed light squared in the
| | 05:06 | denominator, and there we go.
| | 05:10 | So we type that in, and we'll type that in.
| | 05:13 | Finally, I need to match this
with a 1 x 2 matrix in brackets.
| | 05:18 | Again, go to the Insert menu;
you have to click that again.
| | 05:20 | But when I go to Matrix, you can see
that I have, here is a 2 x 2 in brackets.
| | 05:27 | If I go to Brackets, I have
brackets with only one thing in it.
| | 05:31 | So here's what I'll do is I'll go
over here, and I'll choose my 2 x 2.
| | 05:36 | But I'm going to click this one here
on the right, then right-click it, go to
| | 05:40 | Math Options, and I'm going to Delete Column.
| | 05:42 | So now I have a 1 x 2 in brackets,
and now I can simply put in my original;
| | 05:46 | T and Z, and there we go.
| | 05:51 | Deselect it, and there we
have the Lorentz Transformation.
| | 05:53 | So if you ever need to have nicely
formatted equations, I highly recommend
| | 05:57 | playing around with this.
| | 05:58 | I hope you have as much fun doing it as I did.
| | Collapse this transcript |
| Editing pictures with the improved picture tools| 00:00 | You probably don't think of Excel as
being the software to use if you want to
| | 00:04 | edit pictures, but the 2010 version
has some excellent tools to manipulate
| | 00:08 | images, which could be useful when
you need your worksheets to really make
| | 00:12 | the best impression.
| | 00:13 | So let me show you a couple of things here.
| | 00:15 | Let's go to the Insert tab, and
because we're inserting pictures, that's why
| | 00:19 | we're in the Insert tab,
| | 00:20 | click Picture. And in the Exercise Files
in the Chapter4 folder, you should have
| | 00:24 | this 04_04_olive_tree_valley.jpg.
| | 00:28 | Double-click it, and I'll just move
this out of the way, because we're going to
| | 00:31 | have some menus coming back.
| | 00:32 | Now, the picture is okay, but it's kind of dark.
| | 00:35 | We want to brighten it up a little bit.
| | 00:37 | We might want to recolor it
or put some artistic effects.
| | 00:39 | So the first thing is make sure it's
selected so we have the Picture tools
| | 00:43 | section in the Format tab.
| | 00:44 | So over here, click on
Corrections, and this is the default.
| | 00:48 | This is where we're starting out.
| | 00:51 | Now, you can make this a little
brighter, maybe a little too bright, and it
| | 00:56 | shows you what the Brightness and
Contrast settings are going to be.
| | 00:59 | I'm going to choose this one here.
| | 01:01 | I want it to be a little sharper, so
I'm going to go back here, and this is
| | 01:04 | going to be kind of blurry.
| | 01:06 | This might be a little too sharp.
| | 01:07 | So I'm going to choose this
one here, and that's fine.
| | 01:10 | Now, we might want to recolor it.
| | 01:11 | I'll move this out of the way here,
so go to Color, and if we want black-and-
| | 01:16 | white, we can do that to remove the color,
or we can saturate it a little bit more.
| | 01:21 | So if we want this looking a little
bit more verdant, now, this looks really
| | 01:24 | false color, so we don't want
to do that, so I'll choose this.
| | 01:27 | We could also change Color Tones, if we want.
| | 01:31 | If we wanted to recolor it and have
something like pure black-and-white with very
| | 01:34 | high contrast, or something like a duotone
or a tritone, we have these options, also.
| | 01:40 | Pretty cool, I think, that a
spreadsheet program has these.
| | 01:43 | So I'm going to reset this back here,
because I want to show you some artistic effects.
| | 01:48 | So click on Artistic Effects, and you
have some pretty neat options, if you
| | 01:53 | want like Pencil, Sketches and very
blurriness, some diffused glass here, if
| | 02:02 | you want a texture of some sort, and you
can do all sorts of things with your images.
| | 02:08 | Now, if you decide that, well, maybe
that's a lot of fun, but I really want this
| | 02:12 | image back to the original way
that it was when I inserted it,
| | 02:16 | well, rather than deleting it and
reinserting it, all you have to do is go up
| | 02:19 | here and choose Reset Picture.
| | 02:21 | You can also Reset Picture & Size.
| | 02:23 | We didn't change the size. There it is.
| | 02:25 | So there's the slightly
underexposed image that we started with.
| | 02:27 | I'll move this back down here. And we
have some borders that we can choose and
| | 02:33 | just slide these over.
| | 02:34 | Now, unlike the corrections of the color,
these borders will take effect only if we click.
| | 02:39 | It won't be just on rollover, and
click this down arrow here, and we have all
| | 02:44 | sorts of other really neat-looking options here,
| | 02:48 | so things that you really would not
expect to see in a spreadsheet program, if
| | 02:52 | you want to change Border Colors.
You can see it's going to get a thin color
| | 02:57 | over there. I'm not
going to put a border on here.
| | 03:00 | Actually, what I'm going to do is I
want to reset this, because I want to show
| | 03:04 | you how we can use some of
these pictures in SmartArt.
| | 03:07 | So leave it selected, and go
over here to Picture Layout.
| | 03:11 | Now this is going to take your image
and it insert it into SmartArt, and
| | 03:15 | you see we have all these different
SmartArt features available, which I
| | 03:19 | think is pretty cool.
| | 03:20 | I like this Hexagon here.
| | 03:22 | So I'm going to call this Olive Grove,
press Enter, and I have some bottles that
| | 03:29 | I want to put in, so I'll
call this Olive Oil Bottles.
| | 03:37 | Well, that's going to go in here.
| | 03:38 | You see there's this Picture icon.
| | 03:40 | So click it and also in the Chapter4
folder of the Exercise Files, you have this
| | 03:45 | 04_04_bottles.jpg, and just double-
click that, and that gets inserted, also.
| | 03:49 | Now we can just close this.
| | 03:52 | So I think this is pretty neat that we
can put SmartArt and pictures into the
| | 03:56 | SmartArt and do all this
editing in the spreadsheet program.
| | 04:00 | You might not know it's there,
but it's a pretty handy tool.
| | Collapse this transcript |
|
|
5. Pivot TablesUsing the Slicer to make PivotTables more interactive| 00:00 | PivotTables haven't changed
dramatically since the 2007 version, but the Slicer
| | 00:05 | is a new feature that gives
PivotTables some interactivity.
| | 00:09 | Now in this workbook, 05_01_
slicer.xlsx, we have two sheets:
| | 00:15 | We have Data, which is the underlying data,
and we have Table, which is the PivotTable.
| | 00:19 | So if you're not already in the
PivotTable, you might want to go there and
| | 00:22 | click somewhere in it.
| | 00:23 | When we're in there, you see under
PivotTable tools we have the Options tab, and
| | 00:29 | we have the Design tab.
| | 00:30 | We want to insert a Slicer.
| | 00:32 | So go to the Insert tab and choose Slicer,
and this tells us what Slicer we can do.
| | 00:38 | Basically, what this means is we can
filter the data by any of these items.
| | 00:44 | Let's put in Region and Month, and this
is going to create two Slicers, so click
| | 00:50 | OK, and maybe arrange them
where they're easier to see.
| | 00:56 | So we have Region, and we have Month.
| | 00:57 | Before we even use them, we can go and
format them to make them a little easier to use.
| | 01:01 | I have Month selected.
| | 01:02 | So you have various styles
that you can choose from.
| | 01:04 | Hit the down arrow. Whatever floats your boat.
| | 01:06 | I'll go over here to Region, and I'll
give that also a corresponding look.
| | 01:11 | Now, let's go and use it.
| | 01:13 | Right now, I have Central, Northeast,
Southeast, and West, but in the Slicer, I
| | 01:18 | can narrow this down just to one of those.
| | 01:21 | If I decide that I want to unfilter it,
just click on this little Filter button,
| | 01:25 | and that unfilters it.
| | 01:27 | Same thing for Month, maybe I just want
to see January, or maybe I just want to
| | 01:31 | see February or March,
and then I can unfilter it.
| | 01:34 | Now, there are some settings
here that you might want to use.
| | 01:36 | You could either get there by right-
click, or when you're in the Slicer
| | 01:41 | tools > Options, over here on the
left you have Slicer Settings and click
| | 01:47 | that Slicer Settings.
| | 01:48 | So you could change the name.
| | 01:50 | I'm just going to change the caption.
| | 01:51 | Right now, this is Month, so I'm going
to change this to say Month 2010. And some
| | 01:56 | other options here that are
pretty much self-explanatory.
| | 01:59 | Click OK, and now this tells us that
it's Month 2010, just in case you wanted
| | 02:04 | that to be a little bit more specific.
| | 02:06 | So the rest of the PivotTables are
pretty much the same, but the Slicer will let
| | 02:10 | you filter it a little bit more interactively.
| | Collapse this transcript |
| Analyzing data from multiple sources using the PowerPivot plug-in| 00:00 | In this movie, I want to show you the new
PowerPivot plug-in. Say that three times fast.
| | 00:06 | PowerPivot was originally called
Project Gemini, and you might have heard that
| | 00:10 | name bantered about a little bit.
| | 00:13 | This is a free plug-in from Microsoft,
and you could get it at PowerPivot.com
| | 00:18 | and what it does is it puts
PivotTables kind of on steroids.
| | 00:24 | You can query Excel sheets, databases,
and many other sources all at the same
| | 00:31 | time. And this generally falls under
the category of business intelligence, and
| | 00:37 | let's talk about some of the main
advantages over regular PivotTables.
| | 00:42 | PowerPivot has the ability to handle millions
and millions of rows of data. You may know that
| | 00:48 | Excel 2007, and now 2010 will give
you about a million and change rows.
| | 00:54 | But using PowerPivot, you can query
tens of millions of rows from a database
| | 01:01 | that you are looking at.
| | 01:03 | You could import data from
many different types of databases.
| | 01:06 | We will look at that as we go.
| | 01:08 | You can import data from web sites,
text files, just about anything that
| | 01:13 | anybody can send you;
| | 01:14 | as long as there is a structure to it,
you can query the data. And what's also
| | 01:19 | nice is that the data
will refresh automatically.
| | 01:22 | You might know that when you're
dealing with a regular PivotTable, you have
| | 01:25 | to refresh the PivotTable manually every
time there's a change to the underlying data.
| | 01:29 | Well, you don't have to do that with PowerPivot.
| | 01:33 | And not only that, but once you've
done your analysis, you can publish your
| | 01:37 | PowerPivot analysis to a
SharePoint Portal, if you're using one.
| | 01:42 | So let's take a look.
| | 01:44 | So in Excel, let's start with a blank
empty workbook, and I already have the
| | 01:50 | PowerPivot plug-in installed, so you
can see up here there is a PowerPivot tab.
| | 01:56 | So let's click the PowerPivot tab, and
the first thing on the Ribbon bar is to
| | 02:00 | launch the window, so
click that. It brings it up.
| | 02:05 | So what we want to do is
start querying some data.
| | 02:08 | So click over here where it says
From Files and then From Excel, and the
| | 02:15 | friendly connection name is Excel, here
in this window, and click Browse and if
| | 02:19 | you have access to the data files, go
to the Chapter 5 folder and double-click
| | 02:24 | 05_02 powerpivot sales volume.
| | 02:29 | So if you want to make sure that the
connection with your data source is working
| | 02:32 | properly, you can click Test Connection.
| | 02:36 | It tells us that the connection is successful.
| | 02:39 | By the way, we can see that Gemini is
still up there in the Title bar, and click OK.
| | 02:43 | Well, because our data source is on
our own computer, it's really not a big
| | 02:48 | surprise that the connection is okay.
| | 02:50 | This is really when you're querying
data that may be on a network source
| | 02:54 | somewhere, and click Next.
| | 02:57 | And this tells us what part
of that data are we getting.
| | 03:02 | Now, if we want, click Preview &
Filter and here this is showing us that
| | 03:06 | Excel workbook, and we can sort, and we can
filter any of these rows before we bring them in.
| | 03:13 | I'm just going to leave it the way
it is and click OK, and click Finish.
| | 03:18 | And then it tells us that it's successful.
| | 03:20 | That's great, so click Close.
| | 03:22 | Now, you see in the PowerPivot window,
we see the data, and down here in the
| | 03:26 | lower-left corner we can see the tab
that is showing us that is Volume by month.
| | 03:30 | Well, let's bring something else in.
| | 03:32 | So let's go back here From Files, From
Excel, and again click Browse, and this
| | 03:39 | time let's bring in the PowerPivots
sales reps worksheet, and again if you want
| | 03:45 | to Test Connections, you can do that.
| | 03:47 | If you click Advanced, this gives you
all sorts of information about the data.
| | 03:51 | Most of this you can ignore,
especially when it's Excel.
| | 03:54 | But I wanted you to see that it's there.
| | 03:55 | I will click OK, then click Next, and
here it shows us what it's bringing in.
| | 04:01 | You can Preview & Filter this
also, if you want. Same thing.
| | 04:04 | I will just cancel out and click Finish.
| | 04:08 | It tells us it's successful. So click Close.
| | 04:10 | Now you see, down here in the lower-left, we
have Volume by month tab and the Reps tab.
| | 04:18 | Well, I saw that you could query data
from many different sources, so let's get
| | 04:22 | some data from a database. And up here,
the upper-left, click From Database. Then
| | 04:27 | you see we have some common databases,
SQL server access and so on, but I just
| | 04:31 | want to show you this.
| | 04:32 | Click Other Sources. And I
will move the dialog box here.
| | 04:35 | And this really shows you
the power of PowerPivot:
| | 04:38 | SQL Server, Oracle, Sybase, Informix.
| | 04:41 | These are the types of databases that
are used by huge enterprises, and you see
| | 04:46 | all so many different places, all
different types of databases that you can get.
| | 04:51 | Well Access is here, but I am going to
cancel out, just to show you that if you
| | 04:55 | do want an Access database, it's
easier just to get it from the first level.
| | 04:59 | You don't have to go all the way into that menu.
| | 05:02 | Again, click Browse, and also in the
Chapter 5 folder of the Exercise Files, we
| | 05:07 | have this Access Database.
| | 05:08 | You can see this accdb means
it's Microsoft Access Database.
| | 05:13 | And double-click, and again if you want,
you can test the connection, go Next,
| | 05:18 | and we are going to
choose from the list of tables.
| | 05:21 | We are not going to write our SQL query here.
| | 05:23 | There is really no need for this
data. And it shows you the same thing.
| | 05:26 | We can Preview & Filter, if we want.
| | 05:28 | This is just a small table anyway. And click Finish.
| | 05:32 | Successful, click Close, and
now we have that code table.
| | 05:36 | It's just the code of our products,
and we could, at anytime, switch back and
| | 05:40 | forth between these.
| | 05:41 | So let's say, for example, in Volume by
month, I only want to see the Northeast.
| | 05:44 | So I can click this.
I want to just turn these off.
| | 05:46 | I am going to turn on Northeast.
| | 05:49 | Click OK, so I can see this.
| | 05:50 | Now when I go to Reps, again if I want,
I can show just the Northeast, so you can
| | 05:57 | see we can do this independently.
| | 05:58 | I am going to turn these back on,
and I am going to clear that filter.
| | 06:02 | I am going to go back
here and clear that filter.
| | 06:07 | Well, we want to do a little bit of
manipulation with this data, and if you're
| | 06:11 | familiar with databases at all, you
might know that you have to tell your
| | 06:15 | database application, Access in this
case, how your data are related, and to do
| | 06:21 | that we set up what are called relationships.
| | 06:24 | So let's click the Table tab -
| | 06:25 | we are still in the
PowerPivot window - and over here click on
| | 06:29 | Create Relationship.
| | 06:31 | So we want to choose the first table,
and if Volume by month is there, that's
| | 06:35 | great, choose that. And the Related
Lookup table that we want to relate that to,
| | 06:40 | let's choose Code Table.
| | 06:42 | For Column, let's choose Size,
and then the Related Column Lookup,
| | 06:48 | let's choose Product name.
| | 06:50 | So this is telling us which table and
column is related to which lookup table
| | 06:55 | and column. And click Create.
| | 06:58 | Let's create one more relationship.
| | 07:01 | Click that button. And this time
let's say we will stay here with Volume by
| | 07:06 | month, but now we want to
relate that to the Sales Reps.
| | 07:10 | The Column, let's change that to
Quantity. And we want this also related to
| | 07:17 | Quantity, because what's happening is
we have two columns called Quantity in
| | 07:20 | two different tables, and that's how
these data will relate to each other
| | 07:24 | because you always have to have at least one
thing in common between them. And click Create.
| | 07:30 | Let's go back to the Home tab.
| | 07:32 | Now that we have our data related,
let's do something useful with it, and let's
| | 07:36 | create a PivotTable.
| | 07:37 | Now over here on this tab, we have
PivotTable, and we have a whole bunch of choices.
| | 07:42 | Let's start simple, just
with a single PivotTable.
| | 07:44 | Click that, and we will put this one a
New Worksheet, which is fine. Click OK.
| | 07:50 | Now, this looks like a regular
PivotTable, but look at this.
| | 07:53 | This pane shows us all the data
from all the sources that we queried.
| | 07:57 | Now, a regular
PivotTable isn't going to do that.
| | 07:59 | You see up here it also
tells us Gemini Task Pane.
| | 08:03 | So let's go and fill out this PivotTable.
| | 08:06 | So the first for the Report Filter,
let's take Month, and bring that in here.
| | 08:15 | Let's open up Rep, and we will take
Rep Name, and bring that into Row Labels.
| | 08:21 | Let's take Region, and also bring
that into Row Labels, and you'll see we
| | 08:26 | are building this up.
| | 08:28 | For Column Labels, let's take Product Name
for Column Labels and for the sum of Values,
| | 08:38 | let's take Quantity.
| | 08:39 | I am going to take this
Quantity here. And look at this.
| | 08:43 | So this now shows us which
reps are selling which items.
| | 08:49 | This is now just like a regular old
PivotTable, and we can collapse and expand,
| | 08:53 | and do all kinds of great stuff that
you normally do with a PivotTable.
| | 08:58 | Well, let's do one more.
| | 08:59 | We will go back to the PowerPivot
window, again click PivotTable, and this
| | 09:06 | time - this is really cool - choose Four Charts,
and again, we will put this on a new sheet.
| | 09:11 | I need to zoom out,
| | 09:15 | so I am just going to hold the Ctrl key
and roll the wheel backwards a little bit.
| | 09:19 | Let's select Chart 1, and we
will start building this up.
| | 09:22 | So when we have that selected, we go in here.
| | 09:26 | Let's open up Reps, and let's take
Rep Name, and we will make that an Axis
| | 09:31 | field. And let's take the first Quantity, and
that will be the sum of Values, and look at that.
| | 09:39 | Now, let's go click Chart 2, and for
that, let's take Region. And we will make
| | 09:46 | Region an Axis field, and we will take
Quantity and make that sum of values.
| | 09:53 | So again, we have some more useful information.
| | 09:56 | Let's select Chart 3, and for Chart 3,
let's create a Report Filter for this.
| | 10:02 | So let's take Region, and we will make
that the Report Filter. For Axis Field,
| | 10:09 | Let's take Rep Name. And for
Sum of Values, we want size.
| | 10:15 | So now we see what size our sales reps
are selling. Now let's go here to Chart
| | 10:22 | 4. And the Axis Field,
| | 10:24 | let's choose Month.
| | 10:28 | For the Sum of Values, let's choose
Quantity from the Reps table. There we go.
| | 10:37 | Now at anytime we can go
back to the PowerPivot window.
| | 10:40 | If we want, we can change things.
| | 10:41 | We could even close it, because keep
in mind, this is a plug-in that's just
| | 10:44 | putting the data into Excel.
| | 10:46 | So I am actually going to click this,
just to show you that we can continue
| | 10:49 | manipulating these PivotTables,
even though PowerPivot is closed.
| | 10:54 | Also, in this chapter, we talked about Slicers.
| | 10:57 | So let's put that to use here.
| | 11:00 | Let's click the first chart, and
remember Slicers are things we insert.
| | 11:04 | So go to the Insert tab,
and over here click Slicer.
| | 11:10 | We want to slice this by Rep Name.
| | 11:12 | So choose Rep Name, click OK, and I
am just going to move this over here.
| | 11:19 | You see the pin gets out of our way.
| | 11:21 | I just want to stretch this out.
| | 11:25 | Let's do this also for this chart.
| | 11:27 | So like this chart, go back to Insert,
Slicer, and again let's slice this by Rep Name.
| | 11:34 | Click OK, and we can drag this right
over here, and again stretch this out.
| | 11:41 | So when we have Chart 1 selected - and
if you want to you can just close that -
| | 11:47 | now we can filter by any Sales Rep,
and if we decide we no longer want to
| | 11:52 | filter, of course we can turn the
Filter off, and let's click on this chart
| | 11:55 | over here, and again I'll close this.
| | 12:00 | We can sort by any Sales Rep. And then
if we want, we can turn that filtering off.
| | 12:07 | So this is just a simple example
using a small number of data records.
| | 12:11 | But if you need to handle very large
data sets, not only will you find this
| | 12:15 | helpful, but amazingly fast, especially
if you normally have to go through the
| | 12:19 | process of modeling data warehouses.
| | Collapse this transcript |
|
|
6. Going Beyond the DesktopEditing a worksheet in the Excel Web App| 00:00 | In addition to editing worksheets in
Excel, you can also edit your worksheets in
| | 00:05 | a web browser using the free
web-enabled version of Excel.
| | 00:09 | Word, PowerPoint, and
OneNote also have web versions.
| | 00:12 | So you might be asking
yourself a couple of questions.
| | 00:15 | Number one, why did I buy Excel if
I can use it for free over the web?
| | 00:19 | Number two, why would I even want to do this?
| | 00:21 | Well, the answer is that you might
want to share your work with someone who
| | 00:25 | doesn't have Excel on their computer,
or maybe you'll be on the road without
| | 00:28 | your own computer, but you'll have
Internet access, and you want to be able to
| | 00:32 | modify your worksheets.
| | 00:34 | Also, the web version of Excel does
about 10% of what the desktop version does.
| | 00:40 | So it's not a complete substitute.
| | 00:42 | Well, right now, I'm logged into
Windows Live, into the SkyDrive application.
| | 00:48 | We did this in Chapter 2.
| | 00:51 | I created this folder called Two Trees
where we're putting some of our files for
| | 00:56 | the fictitious Two Trees Olive Oil Company.
| | 00:58 | So let's just click that.
| | 01:00 | It's a new folder, so
there is nothing in there yet.
| | 01:02 | You can see it's empty.
| | 01:03 | Well, it won't be empty
for long. Let's go to Excel.
| | 01:07 | If you're following along in the
Exercise Files, you can open up 06_01_web app.
| | 01:12 | It's in the Chapter 6 folder.
| | 01:15 | Before we do anything to this file,
let's immediately put it up in SkyDrive.
| | 01:20 | So click the File tab, go down here to
Save & Send > Save to Web, and there is
| | 01:27 | the Two Trees folder that we just saw.
| | 01:29 | So you can double-click it.
| | 01:30 | Now, you can see up here in the title bar,
we're in the Two Trees folder. Yes, it's empty.
| | 01:35 | I'm going to keep the same File name.
| | 01:37 | So I'll just click Save.
| | 01:41 | You can see down there
it's uploading to the server.
| | 01:43 | Now we don't want any conflicts between
what's happening in the desktop version
| | 01:48 | of Excel, and what's
happening in the web version of Excel,
| | 01:50 | So I'm just going to close this file here.
| | 01:54 | Let's go back into the web browser.
| | 01:57 | Now, you see it's empty just
because we haven't refreshed.
| | 01:59 | You can click the Refresh button or press
the F5 key - will refresh any web browser.
| | 02:04 | There it is. There is the file that we
just saved. So click it.
| | 02:08 | Let's go up here, and click Edit.
| | 02:12 | You see it opens up with part of a Ribbon bar.
| | 02:16 | We can scroll down and see
everything that's in there.
| | 02:19 | So let's go and modify a few things.
| | 02:22 | We're not just looking at it.
| | 02:23 | We can actually do some editing.
| | 02:26 | Well, let's say we want to do some math here.
| | 02:29 | We have Hours, and we have Rate.
| | 02:31 | Let's say we want to
multiply them, and get a Total.
| | 02:34 | So we can just click here in F4.
| | 02:36 | Well, one thing you may notice when
you're looking at the Ribbon bar is there
| | 02:39 | is no AutoSum tool.
| | 02:41 | There is no Format Painter.
| | 02:43 | So some of the stuff we have to do manually.
| | 02:45 | Let me show you a couple of ways of doing this.
| | 02:46 | First here in F4, let's just type
the word Total, and press Enter.
| | 02:50 | Now, we want to format it,
| | 02:51 | so click on any of these column
headers. Copy to the Clipboard.
| | 02:56 | I'll just press Ctrl+C, click on Total,
and we're just going to paste formatting.
| | 03:00 | So you can click on this Paste here,
and choose Paste Formatting. There it is.
| | 03:05 | We can just press the Escape key.
| | 03:08 | Now, that's one way of doing it,
| | 03:09 | but there is a way that
I think is little faster.
| | 03:11 | So I'm just going to press Ctrl
+Z a couple of times to Undo.
| | 03:14 | I'm going to click any of these
headers, doesn't really matter which.
| | 03:18 | I'm going to Ctrl+C, Copy to Clipboard,
click over here in F4, Ctrl+V to paste.
| | 03:24 | Now, I don't need two Rate columns.
| | 03:26 | So I'm just going to go up here, and I'm
going to change this to Total, and Enter.
| | 03:31 | So I've kind of done the formatting,
and the typing sort of in one step.
| | 03:35 | It's up to you how you want to do it.
| | 03:37 | Well, we want to calculate Hours times
Rate, so we have to do this manually.
| | 03:41 | So using F, we can say equals Hours,
times, Rate, press Enter, and scroll down.
| | 03:50 | You might think to yourself oh boy!
| | 03:52 | I've got all the stuff here.
| | 03:53 | There is no AutoFill.
| | 03:54 | I've got to do all of the stuff manually.
| | 03:56 | Well, there is an easy way, if we
know what some of the shortcuts are.
| | 03:59 | Let' click this first one here that we just did.
| | 04:02 | I'll press Ctrl+C. Copy to the Clipboard.
| | 04:05 | Click the cell right underneath it.
| | 04:06 | By the way, this is going to work in
regular Excel also, and not just the
| | 04:09 | web-enabled version.
| | 04:10 | Scroll down, and Shift+Click the last
one here and scroll back up, and now Paste.
| | 04:17 | I'm just going to press Ctrl+V.
Now, we have everything pasted.
| | 04:20 | I'll just hit Escape.
| | 04:22 | Here is the original one.
| | 04:23 | You can see there is the formula.
| | 04:25 | Each one is now pasted correctly.
| | 04:28 | So even though you don't have AutoFill
and you don't have the AutoSum tools, if
| | 04:32 | you know what some of the
shortcuts are, it'll help you out.
| | 04:35 | There we are down to the bottom.
| | 04:37 | Well, let's do a couple of other things.
| | 04:39 | Let's go over here to the Department column.
| | 04:41 | Let's say we want to sort this.
| | 04:44 | So let's go up here to Sort &
Filter as Table. Let's click that.
| | 04:49 | Yes, it confirms that's the area
where we have the data, and it recognizes
| | 04:52 | that we have headers. So click OK.
| | 04:56 | If you need to, you can stretch out the columns.
| | 04:59 | So this looks very much like Excel,
doesn't it, with our tables in there.
| | 05:02 | So let's say we're going to sort by
Department, and we can sort in ascending, or
| | 05:07 | maybe we want to go to State, and
Sort Descending, all the stuff that you
| | 05:11 | normally do you can do here.
| | 05:13 | Well, let's do some filtering while we're at it.
| | 05:15 | Let's go to Department,
and go down here to Filter.
| | 05:18 | I'm going to deselect all of them.
| | 05:21 | Let's say I want to see Finance, HR
and Sales, just those three. Click OK.
| | 05:26 | Now, we see just HR, Finance, and Sales,
and maybe let's sort this in ascending
| | 05:33 | order while we're at it.
| | 05:35 | Maybe I want to see only those people
in those departments who are in maybe New
| | 05:41 | Jersey or Pennsylvania.
| | 05:42 | I'll click State, and I'll
Filter, and I'll get rid of this.
| | 05:46 | Let's say we'll do New Jersey
and Pennsylvania. Click OK.
| | 05:48 | So now we can see only those people
who are in the Finance or HR or Sales
| | 05:54 | departments, and are also in
New Jersey or Pennsylvania.
| | 05:58 | Well, that's great,
| | 05:59 | but what if we want to
bring this back into Excel?
| | 06:01 | Well, you notice there is no Save button,
| | 06:04 | there is no Close button, and that's
because Excel autosaves all the time.
| | 06:09 | To close it, what you really do is you
just click back on the folder name here.
| | 06:14 | So that's kind of closed.
| | 06:15 | Well, let's go back to Excel.
| | 06:18 | Let's go up to Backstage View, click the
File tab, and go to the Recent section.
| | 06:23 | And you see here is the file that we
were just working on, on the web with this
| | 06:26 | very long file name.
| | 06:28 | Now, on your computer, it's probably
going to be a different file name, but it's
| | 06:31 | going to be a long one, nonetheless. Click it.
| | 06:34 | Now we are opening it from the web.
| | 06:37 | You can see, down here, it's probably
telling you that it's downloading.
| | 06:41 | There it is, filtered just
the way we had it before.
| | 06:43 | Again, in real life, we wouldn't be
flipping back and forth like this.
| | 06:46 | We might be two different people.
| | 06:48 | This might be two
different times, day one, day two.
| | 06:51 | We're just kind of
wearing two different hats here.
| | 06:54 | Well, I'm going to un-filter this.
| | 06:54 | So I'm going to clear that filter.
| | 06:58 | I'm going to go here, and clear that filter.
| | 07:00 | But what I do want is I want a total of
the Hours, and I want a total of the Total.
| | 07:05 | So let's go down here. I'll type Total.
| | 07:10 | I'll strike a Total.
| | 07:11 | I'll just press Alt+Equal, the shortcut for
Sum. Go over here and again Alt+Equal is
| | 07:15 | the shortcut for the Sum, and
maybe let's bold it while we're at it.
| | 07:19 | Okay, and let's go up here.
| | 07:22 | Let's check that Payroll. We'll bold it, as well.
| | 07:25 | Okay, that's all we need.
| | 07:27 | Now, this is the regular desktop
version of Excel, so we do have to
| | 07:30 | manually save and close.
| | 07:32 | So we'll just Ctrl+S to Save, and you
see it's uploading to the server here.
| | 07:37 | When it's done, Ctrl+F4 to close it.
| | 07:40 | Now, let's go back to the web.
| | 07:43 | There is a file. We were
just editing that in Excel.
| | 07:46 | Click that. Edit. Here it is.
| | 07:53 | You see the Payroll is bolded.
| | 07:54 | It's all unfiltered.
| | 07:56 | There is the Total row that we just created.
| | 07:58 | So the Excel web app is really great.
| | 08:01 | It's not a substitute for Excel, but
if you just need to do a few things, or
| | 08:05 | you need to share your files with a
few other people on the web, this is a
| | 08:09 | really great way to do it. And hey!
| | 08:11 | It's free. So check it out.
| | Collapse this transcript |
|
|
ConclusionGoodbye| 00:00 | Well, that concludes our tour of
the new features of Excel 2010.
| | 00:04 | I'm glad I had the
opportunity to show them to you.
| | 00:06 | I hope you enjoyed taking this course as
much as I enjoyed presenting it to you.
| | 00:10 | So until next time, take
care, and I'll see you later.
| | Collapse this transcript |
|
|
