IntroductionWelcome| 00:00 |
(music playing)
| | 00:04 |
Hi, I'm Chris Grover, author of several
books on Microsoft Office and a long time
| | 00:08 |
spreadsheet developer.
Over the years I've used Excel to create
| | 00:12 |
budgets, sales plans, statistical
reports, and many other business systems.
| | 00:17 |
Today I work with a number of clients,
helping them to develop and use
| | 00:21 |
spreadsheets that are customized for
their needs.
| | 00:24 |
The purpose of this course is to get you
up to speed, not only using Excel, but
| | 00:28 |
making the most of it.
In addition to teaching you the basics,
| | 00:32 |
I'll also share some helpful tips and
tricks that I've learned throughout the
| | 00:36 |
years, and show you how to avoid of the
some common pitfalls people encounter
| | 00:39 |
when using this powerful program.
This course starts with basic spreadsheet
| | 00:45 |
skills like entering and formatting
numbers and text, and then it moves on to
| | 00:48 |
writing formulas and using Excel's built
in functions.
| | 00:53 |
Data techniques like sorting, filtering,
and look up are covered thoroughly.
| | 00:58 |
Excel provides a number of ways to
analyze data through charts, graphs and
| | 01:02 |
sprite mines, so several lessons explore
those topics.
| | 01:07 |
Along the way, you'll become familiar
with the new features in Excel, like the
| | 01:11 |
ribbon, a recently introduced tool for
choosing and using commands.
| | 01:16 |
Each lesson builds on techniques already
covered.
| | 01:18 |
By the time you get to the end, you'll
know how to use advanced tools, such as
| | 01:22 |
data tables and pivot tables, and you'll
have the knowledge to troubleshoot your
| | 01:25 |
spreadsheet when something goes wrong.
After watching this course, you'll be
| | 01:30 |
prepared to create spreadsheets for all
sorts of business applications.
| | 01:34 |
You'll become familiar with the most
frequently used and popular features that
| | 01:38 |
Excel has to offer.
So whether you're a first time user or
| | 01:42 |
you just want to expand your skills, this
course should give you the information
| | 01:45 |
you need to use Excel capably and
confidently.
| | 01:49 |
So let's get started.
| | 01:51 |
| | Collapse this transcript |
|
|
1. Understanding Excel and Its User InterfaceWhy use Excel?| 00:02 |
So the question is why use Excel?
And the simple answer is that you use
| | 00:06 |
Excel to keep track of the details.
Those details might be your household budget.
| | 00:11 |
Or maybe you need to determine whether
your business is operating at a profit or
| | 00:15 |
a loss.
One of the most important things that
| | 00:18 |
Excel does is that it helps you
understand the story behind the numbers.
| | 00:23 |
So you can keep track of all sorts of
details in Excel using the grid that
| | 00:27 |
Excel creates using rows and columns.
And you can put words and numbers in the
| | 00:33 |
cells and then you can turn those cells
into a chart where you visualize what the
| | 00:38 |
numbers really mean.
You can keep track of all sorts of things.
| | 00:44 |
Here's a worksheet that keeps track of
household expenses and then here's the
| | 00:48 |
pie chart that explains where the money
goes.
| | 00:51 |
Excel is great for creating forms and
reports and things like that.
| | 00:57 |
This is a time sheet where someone keeps
track of the time they spend working on
| | 01:02 |
certain projects.
You can create entire business systems
| | 01:06 |
using Excel.
For example, if you have salesmen in the
| | 01:10 |
field, they can email monthly reports as
Excel files, and then back at the head
| | 01:14 |
office those reports can be combined into
a single report for the entire company.
| | 01:21 |
And again, there are all sorts of ways
that you can visualize the information
| | 01:25 |
once you've collected it in an Excel
worksheet.
| | 01:29 |
Here's a chart that keeps track of the
car sales by employee.
| | 01:32 |
We've got the employee names down at the
bottom, the type of cars they're selling,
| | 01:36 |
and the actual numbers of cars that they
sell.
| | 01:38 |
Number crunchers love Excel, so people
that work in the financial business or
| | 01:44 |
the stock market use charts like these to
keep track of daily stock prices.
| | 01:53 |
Now, Excel is well, excellent when it
comes to crunching numbers, and many
| | 01:56 |
people think that's all it does, but
that's not the case.
| | 02:00 |
You can actually use Excel as a database.
You can keep over a million records on
| | 02:05 |
individual items where you have something
like this movie database that keeps track
| | 02:11 |
of the people involved in making movies,
and what type of movie it is.
| | 02:18 |
And once you have your information in a
database, you can sort it, you can filter
| | 02:21 |
it, you can zero in on specific things.
And the database doesn't have to have a
| | 02:26 |
single number in it, this one only has
dates.
| | 02:32 |
Now, if you have any question about the
variety of things that Excel is capable
| | 02:36 |
of doing, head over to the File tab, and
then click on the New button here.
| | 02:43 |
And you'll see templates down here of all
the different things that Excel can do.
| | 02:48 |
Let's see there're agendas and budgets
and calendars.
| | 02:51 |
Forms and inventories, invoices,
planners, memos, schedules and statements.
| | 02:55 |
And then just take a look at some of
these and you'll see how Excel can tackle
| | 02:59 |
problems, and all the different ways it
can tackle the same kind of problem.
| | 03:04 |
So, Excel helps you keep track of all
sorts of details.
| | 03:09 |
And as you explore the lessons in this
course, you'll see a variety of different applications.
| | 03:15 |
And you'll start thinking of ways that
you can put Excel to work.
| | 03:18 |
| | Collapse this transcript |
| Checking out the Backstage view with the File tab| 00:02 |
One of the things that you notice when
you first start using Excel, is that
| | 00:05 |
green File tab up there in the corner.
Now, there's a reason that it looks
| | 00:09 |
different than the other tabs on the
ribbon.
| | 00:11 |
The commands on the other tabs usually
deal with elements in your worksheet
| | 00:16 |
while the commands on the File tab deal
with your Excel files as complete entities.
| | 00:22 |
In this lesson you'll explore the File
tab, and you'll learn how to Save, Open,
| | 00:25 |
and Create new Excel files.
You'll see where you can look up details
| | 00:28 |
about your files, and you'll get a quick
tour of the Print and Send features that
| | 00:32 |
are built into Excel.
When I have a document open I click the
| | 00:36 |
File tab.
The document disappears or taken to
| | 00:39 |
what's called the Back Stage Area.
And here you'll see commands on this side
| | 00:44 |
and then the middle part here will change
depending on what you selected.
| | 00:50 |
Now when I want to save a document that
I've made changes to, go up here and
| | 00:54 |
click the Save button.
That saves my document, and it puts me
| | 00:59 |
right back where I can go to work.
Now, the Save As command works a little
| | 01:04 |
bit differently.
You use the Save As command when you want
| | 01:07 |
to save your file in a different
location, or with a different name.
| | 01:12 |
So when I click this button, I see this
window that opens up, where I can use the
| | 01:17 |
panel over here to navigate to different
locations on my computer, double-click
| | 01:22 |
folders to open them, and then I can name
my file, change the name here.
| | 01:31 |
And then I can click the Save button to
save it.
| | 01:33 |
Or if I change my mind, I could click
Cancel.
| | 01:36 |
Now the file that I saved is the one
that's open in Excel.
| | 01:39 |
You can see the name up here in the title
bar.
| | 01:42 |
The Open command works kind of the same
way.
| | 01:47 |
You see a similar window where you have
your locations over here, and then you
| | 01:50 |
have documents showing in here where you
can just pick a document.
| | 01:55 |
You can either click Open, or you can
just double-click on it, to open that document.
| | 02:00 |
I'm going to make a little change to this so
that when I go over here and click Close,
| | 02:05 |
you'll see if you make changes to your
document, Excel gives you three options.
| | 02:12 |
You can save your changes and close the
document.
| | 02:15 |
You can not save your changes and close
the document.
| | 02:18 |
Or you can click Cancel and in that case,
you won't save any changes and your
| | 02:22 |
document stays open.
On the Info Tab, you see a little
| | 02:30 |
thumbnail picture of the document that
you have open, and details about it over
| | 02:34 |
here, such as its size and who worked on
it, and when they worked on it.
| | 02:40 |
And then in the middle, you have, options
for sharing your document with other people.
| | 02:44 |
Gives you control over what kind of
things they can change.
| | 02:49 |
This Recent tab is a really helpful one.
Documents that you've recently opened
| | 02:53 |
appear here, and all you need to do is
click on their name, to open the document
| | 02:57 |
in Excel.
Now, if you'd like a document to always
| | 03:01 |
show up on this list, what you can do is
you can click the thumbtack over here,
| | 03:05 |
and that pins the document to this list.
It'll always be there every time you open
| | 03:11 |
it up.
Until you turn it off.
| | 03:14 |
You can do the same thing with your
folders over here.
| | 03:16 |
Next button is, the New button.
When I click New, I see this window.
| | 03:24 |
If I want to create a blank document, I
can choose this first option.
| | 03:28 |
And click Create, or I can double-click
the blank workbook icon here.
| | 03:32 |
Down here at the bottom, you have some of
the hundreds of templates that come with Excel.
| | 03:37 |
These are predesigned workbooks with a
specific purpose in mind, so you can
| | 03:40 |
browse through here, choose one that
might fit the job you need to do, and
| | 03:43 |
it'll save you a little bit of work.
Next is the Print section.
| | 03:50 |
Over here you see a preview of the
document that you've got open, and if it
| | 03:53 |
has more that one page you can page
through and look at the different pages.
| | 03:58 |
You have printer settings down here where
you can choose a printer and make other
| | 04:01 |
changes, and you can choose the number of
copies that you want to print.
| | 04:06 |
But most of the time what you'll do is
you'll click Print and then you'll click
| | 04:09 |
the Print button here.
That sends it to your printer and you're
| | 04:12 |
ready to go.
Here are the save and send options.
| | 04:16 |
You can send your Excel files using
email.
| | 04:19 |
You can save them to the web.
Or if your company uses Share Point, you
| | 04:23 |
can send it to the Share Point server.
You have options so that you can change
| | 04:27 |
the file format when you do that.
The next button is Help.
| | 04:33 |
The important one here is the blue
Microsoft Help button.
| | 04:36 |
You'll notice it looks just like the
button over here and that's because they
| | 04:38 |
both lead to the same place.
Click Help.
| | 04:43 |
And you see Microsoft's Help window.
If you want to search for a particular
| | 04:46 |
term you can, type it in here, or you can
browse through Help using these links
| | 04:50 |
that are shown down here.
Next on the list is Options.
| | 04:59 |
Options is a preferences folder.
You have different subjects over here and
| | 05:03 |
you can change these preferences to make
Excel work the way you like to work.
| | 05:08 |
There are a lot of options but you need
to be a little bit careful when you make
| | 05:10 |
the changes.
Last on the list is the Exit button.
| | 05:16 |
And that Exit button also looks like the
Exit button over here and they do exactly
| | 05:20 |
the same thing.
They close Excel and if you have any open
| | 05:25 |
documents it closes them as well.
If you've made changes to your documents,
| | 05:30 |
you have a chance to save those changes
before it closes them.
| | 05:33 |
So in this lesson you got a quick tour of
all the Backstage services provided by
| | 05:37 |
the File tab.
You learned how to save, open and create
| | 05:40 |
new Excel documents.
You saw that the Info section provides
| | 05:45 |
details on your currently open document,
and you were introduced to the Print and
| | 05:50 |
Save features in Excel.
| | 05:53 |
| | Collapse this transcript |
| Examining your file's info| 00:02 |
In this lesson, we take a close look at
the info section for Excel files.
| | 00:05 |
You'll see how to access the information
that Excel stores with your files.
| | 00:09 |
And you'll learn some tips for sharing
files with your colleagues.
| | 00:13 |
When you have a document open and you
want to see some details about it, go up
| | 00:16 |
and click the File tab.
You're immediately taken to the info
| | 00:21 |
section of the backstage where you can
see details about your file.
| | 00:26 |
For example up here, you have a thumbnail
that shows what it looks like.
| | 00:30 |
Under properties you can see the file's
size and its title.
| | 00:33 |
You can see the dates when the file was
created.
| | 00:36 |
And when it was changed, and you can see
the people that made those changes.
| | 00:40 |
In the middle there are three sections.
Permissions is all about protecting your workbook.
| | 00:45 |
For example you can mark a file as final,
and that way people can open it up and
| | 00:48 |
look at it, but they won't be able to
make any changes to it.
| | 00:53 |
If you want more security you can encrypt
the file with a password.
| | 00:57 |
And that way only people with passwords
will be able to open the file, or make
| | 01:01 |
changes to it.
You can protect parts of your worksheet.
| | 01:06 |
Or you can protect the entire structure
of the workbook.
| | 01:10 |
Now this last option is Add a Digital
Signature.
| | 01:13 |
If you're sending a contract or some
important document to somebody and you
| | 01:17 |
want to veridy that it came from you and it
hasn't been changed, you can add a
| | 01:20 |
digital signature to it.
Then that person will know it came from
| | 01:25 |
you and if there have been any changes to
it in the meantime, your digital
| | 01:29 |
signature will no longer be valid.
The next section is called prepare for sharing.
| | 01:35 |
So you have items like inspect document.
Suppose you have a workbook that you took
| | 01:40 |
a long time to create and there are lots
of comments in there.
| | 01:44 |
And perhaps you don't want other people
to see some of those comments.
| | 01:47 |
You can inspect the document and find
those comments and remove them using this command.
| | 01:53 |
Accessibility is another important issue.
So for example, if you're working with
| | 01:57 |
someone who uses a screen reader, you can
make sure that your document will work
| | 02:00 |
with their screen reader.
Use check compatability when you're
| | 02:04 |
working with somebody that might have an
older version of Excel.
| | 02:08 |
You can make certain that they are able
to open your workbook and use it.
| | 02:13 |
The last item is called versions, and it
helps you manage different iterations of
| | 02:17 |
the same file.
So for example, if the power went out and
| | 02:20 |
you didn't close an Excel workbook
properly, you can click Manage Versions
| | 02:25 |
and go down here and recover an unsaved
copy of your workbook.
| | 02:32 |
So those are all the details that you'll
find in the info section.
| | 02:36 |
In this lesson, you saw that the info
section on the file tab keeps track of
| | 02:40 |
information about your Excel files, who
made the changes and when those changes
| | 02:44 |
were made.
You also saw that when you share files,
| | 02:48 |
you can control the types of changes that
other people make.
| | 02:51 |
| | Collapse this transcript |
| Exploring the Ribbon| 00:02 |
If you're new to Microsoft Office, or if
you haven't used new versions since
| | 00:05 |
around 2005, you may need an introduction
to the ribbon.
| | 00:10 |
The ribbon sits up here at the top of all
the Office applications.
| | 00:13 |
You can think of it as kind of a
supercharged toolbar.
| | 00:17 |
It puts all the commands you most
frequently use into one location.
| | 00:21 |
In this lesson you'll learn how to choose
commands from the ribbon, how to shrink
| | 00:25 |
the ribbon when you need more screen
space, and how to use the shortcut keys
| | 00:29 |
that are related to the ribbon.
The ribbon organizes commands by tabs,
| | 00:36 |
then by groups.
Here's the font group and the alignment group.
| | 00:40 |
And then it puts the commands inside of
the groups.
| | 00:43 |
The commands are typically either buttons
or drop down menus or sometimes they're
| | 00:46 |
larger panels.
If you don't know what a particular
| | 00:49 |
widget does, the thing to do is to point
to it and a tool tip appears that
| | 00:53 |
displays a name and a description and
sometimes a shortcut key that's used with
| | 00:57 |
that command.
So this is the Bold command.
| | 01:01 |
Control B is it's shortcut key.
Before you use these commands, usually
| | 01:06 |
you want to select the cell that has the
text or the numbers that you want to change.
| | 01:11 |
And then you go up and use the command.
You can see that the Bold key works like
| | 01:15 |
a toggle.
When it's selected and my text is bold,
| | 01:18 |
it's highlighted, and then if I click it
again, it turns it off.
| | 01:23 |
Now, the drop down menus usually give you
several choices and they always have a
| | 01:27 |
little triangle button next to them.
So I click the triangle and here are all
| | 01:31 |
the choices for font sizes.
I point to a number and it changes to
| | 01:36 |
display exactly what that font size would
look like and if I wanted I could click
| | 01:39 |
it to choose it.
Or if I decide I don't really want one, I
| | 01:44 |
can just click away from the menu, and
the menu disappears.
| | 01:48 |
Now you can't fit every single command
that Excel has to offer into the ribbon,
| | 01:52 |
so in some cases, you'll still drill down
to those old-fashioned dialog boxes.
| | 01:59 |
So in a group, if you see this little
button here in the lower right corner,
| | 02:03 |
that means there's a dialog box
underneath.
| | 02:06 |
If I click that, I see the dialog box.
It might have tabs at the top, a bunch of
| | 02:10 |
controls in the middle, and I could make
several changes at once.
| | 02:15 |
And then if I click okay it enacts those
changes.
| | 02:18 |
If I click Cancel, it doesn't.
Let's take a quick tour of the different
| | 02:22 |
tabs and see what they have to offer.
On the Home tab, Microsoft put all the
| | 02:27 |
commands they think you'll use most of
the time so you have your editing
| | 02:30 |
commands, cut, copy and paste, formatting
commands for both text and numbers.
| | 02:37 |
Things like Autosum are popular command
that you use to total rows and columns of numbers.
| | 02:43 |
If you want to pop something into your
workbook use the Insert tab and then you
| | 02:47 |
can pop in a table, a photograph, clip
art.
| | 02:50 |
This is where you'll find charts and
sparklines, sparklines are little tiny graphs.
| | 02:56 |
And you can insert text items and
equations and symbols.
| | 03:00 |
The page layout tab primarily affects how
the printed document is going to look.
| | 03:05 |
So you can choose colors and fonts here
and things like margins, page
| | 03:08 |
orientation, portrait or landscape, and a
lot of other things that affect how the
| | 03:12 |
printer document will look.
Click the Formulas tab and you'll see
| | 03:19 |
there's large library of functions that
you can use in your formulas.
| | 03:23 |
You have Autosum here but you have other
things like financial functions.
| | 03:28 |
Here you've got interest rates and
duration.
| | 03:32 |
There are also functions for logical
functions and dates and times and math
| | 03:36 |
and trigonometry.
The Data tab shows a lot of the database
| | 03:41 |
capabilities that are built into Excel.
You can bring in external database files
| | 03:47 |
or you can make connections to existing
databases.
| | 03:50 |
Then you use the rest of these tools to
manipulate the data and look at it in
| | 03:54 |
different ways.
You can sort it and filter it.
| | 04:00 |
The Review tab has two types of commands
on it.
| | 04:03 |
This section over here really are
reference tools.
| | 04:06 |
You have your spellchecker, a thesaurus,
translation services.
| | 04:10 |
The rest of the tab is devoted to sharing
your workbooks with somebody else so you
| | 04:15 |
have controls for inserting comments and
managing comments.
| | 04:22 |
If you want to share your workbook with
somebody you can do that.
| | 04:25 |
But you can protect parts of it so they
can only make changes to certain aspects
| | 04:29 |
of the workbook.
The View tab changes how your workbook
| | 04:33 |
looks on the screen.
So most of the time you're going to use
| | 04:36 |
the Normal view.
But once in a while, you may want to
| | 04:39 |
change to the Page Layout view, where you
see how all those things fit onto a
| | 04:43 |
single page or several pages.
There's a Full Screen view that really
| | 04:48 |
maximizes your view of the workbook.
It's not very good for editing, but it's
| | 04:53 |
great for reading and studying your, your
worksheet.
| | 04:55 |
If I press escape, I can get back to the
regular view.
| | 04:59 |
And then you have things like Zoom tools.
And one of my favorites over here is the
| | 05:02 |
Switch Windows.
If you have a couple of workbooks open at
| | 05:06 |
once, you can jump back and forth between
them with the Switch Windows button.
| | 05:11 |
One of the things people worry about when
they first start using the ribbon is the
| | 05:14 |
amount of space that it takes up here.
But you don't really have to worry about that.
| | 05:18 |
If you want to minimize the ribbon Just
double-click on it and the largest part
| | 05:21 |
disappears leaving just the tabs.
Then if you want to use a command you can
| | 05:25 |
just go click the tab, choose the
command, and the ribbon disappears again.
| | 05:30 |
If you want to bring it back the way it
was before, just double-click on a tab
| | 05:35 |
and there's your entire ribbon again.
A lot of people like to use keyboard
| | 05:41 |
shortcuts because you can work quickly,
you don't have to take you hands off the
| | 05:44 |
keyboard and you can keep working away.
The only problem with keyboard shortcuts
| | 05:49 |
is you have to memorize the shortcuts
that you want to use.
| | 05:52 |
Well, the ribbon gives you a new twist on
that.
| | 05:55 |
The only key that you have to remember is
the ALT key.
| | 05:57 |
So if you press and release the ALT key,
you see these badges appear down here.
| | 06:03 |
And if you press one of those, so if I
press h for the home tab, then I see the
| | 06:06 |
home tab and a whole bunch of new badges
down here.
| | 06:11 |
So if I press one for b, I can use the
bold command.
| | 06:15 |
Alt h one toggles the bold command on and
off.
| | 06:19 |
In this lesson, you learned how to choose
commands from the ribbon, explore the
| | 06:23 |
different tabs and you saw how to
minimize the ribbon when you need more
| | 06:27 |
screen space.
You also saw how to use the shortcut keys
| | 06:32 |
that are part of the ribbon.
| | 06:35 |
| | Collapse this transcript |
| Customizing the Ribbon and Quick Access Toolbar| 00:02 |
You'll be more productive if Excel is set
up to work the way you like to work.
| | 00:06 |
So this lesson is about customizing the
commands on the Quick Access toolbar in
| | 00:11 |
the Ribbon.
Now the Ribbon shows different commands
| | 00:14 |
depending on which tab you click.
That's not true of the Quick Access toolbar.
| | 00:20 |
It shows the same commands all the time,
and that means it's a great place to have
| | 00:23 |
the commands that you want to use all the
time.
| | 00:27 |
When you start off, the Quick Access
toolbar shows three commands.
| | 00:31 |
Save, Undo and Redo.
To add more commands to it you can use
| | 00:35 |
this menu here.
When you open it up you see Save, Undo
| | 00:40 |
and Redo are checked.
All we have to do to add new is click it.
| | 00:46 |
Maybe we want to add open two.
Now we've got two new commands that work
| | 00:51 |
on the Quick Access toolbar.
Suppose we want to add another command to
| | 00:55 |
the toolbar that isn't shown there.
I have a command I really like.
| | 00:59 |
It's switch windows, where you can jump
between two open workbooks.
| | 01:03 |
Books, so I'm going to go back here and
choose more commands.
| | 01:07 |
Let me see this window on this side, we
have all the commands that Excel has to offer.
| | 01:13 |
Then on this side, we have the command
that are currently on the toolbar.
| | 01:20 |
Another command that I want is in the
view tab so I'm going to use this menu here.
| | 01:24 |
And choose View tab, and then get down
here to switch windows.
| | 01:29 |
I'll click the Add button to move it over
to the tool bar.
| | 01:33 |
I can use these arrows to move it up and
down the list.
| | 01:36 |
When I'm done, I can say OK.
Now I've got 3 new commands, Switch
| | 01:42 |
windows, New and Open on the Quick Access
toolbar.
| | 01:48 |
Now the Ribbon is customizable too.
Best way to get to that is to go and
| | 01:52 |
right-click on an empty spot and then
choose Customize the Ribbon.
| | 01:57 |
And this scene looks pretty much the
same.
| | 02:00 |
These are your tabs over here.
And you really can't make too many
| | 02:04 |
changes to these, you see they're grayed
out.
| | 02:08 |
But what you can do is you can show and
hide tabs.
| | 02:11 |
So for example, if you never use the
commands that are in the data tab, we can
| | 02:15 |
uncheck that option.
And on the other hand, suppose we want to
| | 02:19 |
show the developer tab and we can turn
that one on.
| | 02:23 |
Then when we click okay, there's no data
tab up here where it used to be, but
| | 02:26 |
there's a developer tab.
I'm going to go back to the customized ribbon.
| | 02:34 |
And another thing that you can do if you
want to get really fancy is you can create
| | 02:37 |
your own tabs an your own commands.
An to do that, I click the New tab button
| | 02:43 |
here, an a new tab an new group appears.
Going to right-click > New tab and rename
| | 02:50 |
it, I'm going to name it Print.
We'll make this a bunch of print options,
| | 02:56 |
going to rename the group options.
Okay, now all I have to do is add some commands.
| | 03:05 |
Gonna use this menu to show commands that
aren't in the Ribbon.
| | 03:09 |
I'll scroll down to where we find some
print commands.
| | 03:15 |
Here they are.
And then I can add them to my new tab.
| | 03:20 |
And click OK.
Now we've got our new Custom tab that has
| | 03:28 |
the print options in it.
So it's just that easy to make changes to
| | 03:32 |
the Ribbon and the Quick Access toolbar
to make them work the way you like to work.
| | 03:37 |
| | Collapse this transcript |
|
|
2. Getting Started with Basic Tasks in ExcelCreating workbooks from templates| 00:00 |
When you're just beginning a project like
a financial statement or a household
| | 00:04 |
inventory or even something like a
calendar, now you could build that
| | 00:08 |
project from scratch with your own
workbook.
| | 00:12 |
Or you could take advantage of one of
Excel's predesigned templates.
| | 00:16 |
This lesson demonstrates the process of
choosing and using a template.
| | 00:21 |
So why would you want to use a template?
Well the short answer is, that it will
| | 00:24 |
save you a lot of time.
Somebody else has already done all the
| | 00:28 |
formatting, put in all the text, and
created all the formulas for the project.
| | 00:34 |
And also it's probably been tested and
refined by other people using it, so you
| | 00:37 |
can be relatively sure, that it's going
to work as advertised.
| | 00:42 |
So the first question is where do you
find a template.
| | 00:45 |
You do that by going to the File tab,
click on it and you see this backstage
| | 00:49 |
area and then click New.
And this is where you create a new
| | 00:53 |
document, so if you wanted to create a
blank document you just double-click on
| | 00:57 |
this and you Open A New Blank Document.
These items up here at the top, are items
| | 01:02 |
that are on your computer.
So if your company, uses templates,
| | 01:06 |
they're likely to be in this My Templates
section.
| | 01:09 |
It opens this window, to use one of these
templates all you need to do is select
| | 01:13 |
it, and then click OK.
Now if you're creating a project that
| | 01:18 |
isn't part of your company, you want to
look down at this bottom section.
| | 01:23 |
These are the templates that are offered
by Microsoft and they live on the
| | 01:26 |
office.com website.
And you can go on the office.com website
| | 01:30 |
and see these and see other templates as
well.
| | 01:35 |
But it's easier just to use the previews
that you see of them in here.
| | 01:39 |
If you want to search for a specific
template.
| | 01:42 |
You can type in here, something, like
grocery list.
| | 01:48 |
And Excel searches the office.com website
and returns the items that match your list.
| | 01:55 |
So, you see that you have thumbnails.
You can look at them, click on the
| | 01:59 |
thumbnail here and you see a display over
there of what the list would look like
| | 02:02 |
and you can see often there are all
different choices just on a single topic.
| | 02:08 |
To navigate through your template use
this bar up at the top so these are
| | 02:11 |
search results that we are looking at now
and if we click Home this will take us
| | 02:14 |
back to the original page.
What you're looking at here are
| | 02:20 |
categories of templates.
So you can see there are Agendas and
| | 02:23 |
Books and Budgets and Calendars.
Reports and Schedules.
| | 02:27 |
Even things like Memos and Letters and
Labels.
| | 02:31 |
To examine one of these categories.
Click on it.
| | 02:36 |
Let's look at Inventories, and you see the
thumbnails.
| | 02:40 |
Now over here, you see a preview of it,
and you can see who created the template,
| | 02:44 |
its size, and its rating is shown as
stars, and you see the number of people
| | 02:49 |
that have voted.
This is a good thing to check when you
| | 02:53 |
are choosing a template.
You can see how many people used it
| | 02:56 |
before you, which gives you an idea of
how refined it might be.
| | 03:00 |
And like open up this inventory and cost
of goods analysis sheet, and we'll see
| | 03:05 |
what's inside.
Now when you open a template, you're
| | 03:10 |
actually downloading it from the
Microsoft website.
| | 03:13 |
That goes pretty quickly if you have a
quick connection.
| | 03:16 |
And then you see the template, and once
it's loaded into Excel, you can make
| | 03:20 |
changes to it.
Everything you need is right in your
| | 03:24 |
excel workbook at this point.
So you can change it, you can save it and
| | 03:28 |
it doesn't need to be connected to that
template any longer.
| | 03:31 |
In fact that's the great thing about
templates, they leave the original intact
| | 03:36 |
so you can use it over again and it
creates a new document so when I save
| | 03:40 |
this It doesn't have any effect on the
original.
| | 03:45 |
Most templates show you where you want to
make changes by using brackets, so here
| | 03:49 |
you can see company name has brackets
around it.
| | 03:53 |
And then I can type in my company name
and change these items as well.
| | 03:57 |
So this template is used for inventory
and cost of goods sold analysis.
| | 04:03 |
I can put in four different products, in
here.
| | 04:06 |
And then, of course, I'll want to change
the numbers.
| | 04:08 |
If I look at the numbers, I can see in
the formula bar up here, these are raw
| | 04:13 |
numbers in the white cells.
But these grey cells have formulas.
| | 04:18 |
So I probably want to enter numbers for my
products in the white cells, and leave
| | 04:22 |
the grey cells alone so they calculate
the totals as they're supposed to.
| | 04:27 |
When you want to save something from a
template You just use the regular Save As
| | 04:31 |
method and you can chose a location on
your computer and save it.
| | 04:37 |
And your saving it not as a template,
you're saving it as a standard Excel worksheet.
| | 04:43 |
Now there may come a time where you want
to save you own Excel template, and
| | 04:46 |
that's easy to do too.
Just go to File, Save As.
| | 04:51 |
Been using the save as type list here.
Open that up, and then choose Excel template.
| | 04:57 |
Or if you have a template with macros,
you want to choose Excel macro enabled template.
| | 05:04 |
And then click Save.
Now before I click save, if you look up
| | 05:07 |
here you'll see that it's not saving the
template In your usual documents folder
| | 05:12 |
there's a special place where Excel saves
templates in your user identity.
| | 05:19 |
And it automatically chooses that
location here.
| | 05:23 |
In this lesson you learned how to create
a new document from template and you
| | 05:27 |
Explore the wide variety of templates
that are available.
| | 05:31 |
You learn how to work with a template and
how to save a document as a template.
| | 05:37 |
| | Collapse this transcript |
| Saving workbooks to different file formats| 00:02 |
Excel lets you save files to different
formats.
| | 00:04 |
So, this lesson talks about the most
common file formats and why you're likely
| | 00:08 |
to use them.
You'll learn how to save workbooks for
| | 00:11 |
older versions of Excel.
You'll learn how to save data from Excel,
| | 00:14 |
so that it can be imported into another
program like a database.
| | 00:18 |
And you'll learn how to save in the
universal PDF format so your files can be
| | 00:22 |
used by just about anyone.
When you're working away in Excel, there
| | 00:26 |
are about three ways that you're likely
to save an existing document.
| | 00:30 |
You could go up here to the tool bar and
click the Save button.
| | 00:34 |
You can use the keyboard shortcut,
Ctrl+S.
| | 00:39 |
Or you can use the File tab, click File,
and then click Save.
| | 00:43 |
When you save with these methods, Excel
saves the file with its existing name.
| | 00:48 |
If you want to save a file in a different
format, you need to go File and Save As.
| | 00:56 |
Because what you're doing is you're
saving the file with a different name.
| | 01:00 |
And the part of the name that's changing
is called the suffix.
| | 01:04 |
It's the part of the name after the
period.
| | 01:06 |
So here the .xlsx identifies this file as
a Microsoft Excel worksheet.
| | 01:15 |
It's the standard format for Excel files.
Now if you want to make a change, the thing
| | 01:19 |
to do is go File > Save As, and then come
down here to Save As Type, and use this
| | 01:23 |
menu to choose a different file type.
So at the top, we have our .xlsx Excel
| | 01:31 |
Workbook File.
Right underneath it is another common
| | 01:35 |
file format, it's called Macro-Enabled
and it ends in .xlsm.
| | 01:41 |
These files usually show an exclamation
point in their icon.
| | 01:45 |
I think that's to scare us.
And here's why.
| | 01:48 |
Programmers can build incredible
applications right inside of Excel using
| | 01:52 |
macros and Visual Basic programming.
The problem is sometimes bad guys use the
| | 01:58 |
same tools to cause trouble, so Microsoft
has a system to alert you when you open a
| | 02:03 |
program that has macros, and it lets you
choose whether or not those macros can run.
| | 02:10 |
So let me show you how it works.
I go File > Open.
| | 02:13 |
I've got two files in here, this is the
standard file and this is the one that
| | 02:18 |
has a macro.
So if I double-click on it to open the
| | 02:22 |
macro I get this yellow bar at the top
that says security warnings and there's a
| | 02:26 |
button that says Enable Content.
Well right now I can work with this file,
| | 02:32 |
but the macros aren't going to run until
I click this Enable Content button.
| | 02:38 |
So the rule of thumb here is you don't
want to click Enable Content unless you
| | 02:42 |
know exactly where that file came from
and who put it together.
| | 02:48 |
I'm going to close it now.
Let's look at some of the other file options.
| | 02:55 |
Another one that you're likely to use is
the template.
| | 02:58 |
Templates are pre-designed workbooks that
you can use over and over.
| | 03:03 |
It works like this, when you open a
template file, Excel automatically makes
| | 03:06 |
a copy of it and leaves the original
unchanged so that you can use it again.
| | 03:12 |
So to create a template file, all I have
to do is select Template, or if it has
| | 03:16 |
macros, I want to select Macro-Enabled
Template, and then click Save.
| | 03:22 |
Gonna shrink this window down so that we
can see the entire list here.
| | 03:26 |
If you're sharing data from your Excel
spreadsheet with a database.
| | 03:32 |
If you want to kind of suck that
information into a database, there are a
| | 03:37 |
couple of good formats to use.
One is Cab Delimited text, the other is
| | 03:44 |
Comma Delimited CSV.
Now either one of these, you can choose,
| | 03:48 |
and click Save.
You might get a little warning about
| | 03:51 |
whether all the features are going to be
transferred to the new file format or not.
| | 03:56 |
But in most cases the thing to do is say
yes and go ahead and save the file, and
| | 03:59 |
then experiment and see if it got
everything that you needed.
| | 04:03 |
If you are working with somebody that has
a older version of Excel you want to
| | 04:10 |
choose, probably this format, Excel
97-2003, and it has a .xls suffix.
| | 04:20 |
Now this 97-2003 are the versions of
Excel that use file format and you click
| | 04:26 |
save, again you'll get a warning.
And again the thing to do is probably
| | 04:33 |
just go ahead and click continue and save
the file.
| | 04:37 |
Make sure that all the things you wanted
to share are there in the file so the
| | 04:40 |
person that you're sharing it with will
be able to see them.
| | 04:43 |
One last file format to look at is the
PDF file format.
| | 04:48 |
Go File > Save As.
Scroll down the list, PDF is down at the
| | 04:54 |
bottom, choose that.
You'll see there a couple of options you
| | 04:58 |
can choose.
Standard, or you can minimize the size if
| | 05:01 |
you know it's going to be stored online.
It's good to leave this box checked, open
| | 05:06 |
after publishing.
Just click save, your file is saved in
| | 05:11 |
the PDF format.
You'll need Adobe Reader installed on
| | 05:16 |
your computer to read a PDF file.
Here's what it looks like in Adobe Reader.
| | 05:22 |
You can see that it looks a lot like our
original workbook.
| | 05:24 |
So that's how you save to different file
formats.
| | 05:28 |
This lesson showed the different file
formats you can use in Excel.
| | 05:31 |
You learned that the .xlsx is the
standard final extension, but if your
| | 05:36 |
workbooks have macros, you want to save
in the .xlsm format.
| | 05:42 |
You can save your files in formats for
older versions of Excel, and you can save
| | 05:47 |
your files in the universal PDF format
too.
| | 05:52 |
| | Collapse this transcript |
| Switching between views| 00:02 |
You can change the view of your Excel
workbook using the controls down here in
| | 00:05 |
the lower right corner.
In this lesson, you'll take a look at
| | 00:08 |
three different views, Normal, Page
Layout, and Page Break Preview.
| | 00:13 |
You'll also get some tips about making
the view zoom in and out.
| | 00:17 |
Now there are three buttons down here
that control the view.
| | 00:20 |
And you just click on the button to
change the view and when you click on the
| | 00:22 |
button it highlights it, you can see this
first button is highlighted.
| | 00:26 |
And we're looking at the Normal view
right now.
| | 00:29 |
And this is the view that you'll use most
of the time, especially for a basic
| | 00:32 |
worksheet like this.
It shows your worksheet as a piece of
| | 00:37 |
ledger paper.
It starts up in the corner here with
| | 00:40 |
cells and they, cells extend off to the
right and down to the bottom seemingly
| | 00:44 |
forever, these are enormous worksheets.
And you can easily change the numbers and
| | 00:49 |
change the text in this view, and
navigate it.
| | 00:52 |
If you want to change to a different view
you could click the button, so I'm
| | 00:55 |
clicking on the Page Layout view, and
this shows me my worksheet as it would
| | 00:58 |
appear if I printed it out on pieces of
paper.
| | 01:02 |
I can see that it would extend over two
pieces of paper.
| | 01:06 |
If I scroll down I see other pieces of
paper, but there's no data on these.
| | 01:10 |
So, they wouldn't print.
Now you can make changes in this view.
| | 01:14 |
So you can change the numbers.
You can change the text, and you can do
| | 01:17 |
it, almost everything that you would in
the other view.
| | 01:20 |
And you might want to use this view if
you have a highly formatted worksheet,
| | 01:23 |
like one of those templates that, you
find that Excel offers.
| | 01:27 |
The other handy thing that you can do in
here is you can change the header.
| | 01:34 |
And you can change the footer just as
easily.
| | 01:36 |
Just click and type.
Now this last view is pretty
| | 01:41 |
single-minded, it's the Page Break
Preview.
| | 01:45 |
And when I click that, I see my
spreadsheet with an automatic page break
| | 01:49 |
in here.
This dotted line indicates that's an
| | 01:52 |
automatic page break, but I can manually
change it by dragging it to a new location.
| | 01:59 |
So, now this solid line shows that
there's a manual page break in here.
| | 02:02 |
We've got another automatic one over
here.
| | 02:04 |
I could adjust it a little bit.
And now I've divided my document into
| | 02:09 |
three fairly equal pages.
If you want to reset the page breaks that
| | 02:13 |
you put in, just right-click on a cell,
then go down here and choose Remove Page
| | 02:17 |
Break or Reset All Page Breaks.
And it goes back the way it was.
| | 02:23 |
Gonna go back to the Normal view to look
at the Zoom tools.
| | 02:28 |
Down here are the Zoom tools.
You have a plus and minus button and a slider.
| | 02:32 |
So if I click plus, zoom in, the zoom
level is shown here as a percentage.
| | 02:40 |
If I want I could just drag the slider.
It does the same thing.
| | 02:43 |
Or I can click the number and it brings
up this panel with automatic settings.
| | 02:49 |
Now, one of the handy things in here is I
can select a group of cells.
| | 02:55 |
Click the number again and bring up that
panel and choose Fit To Selection.
| | 03:01 |
And it zooms in automatically to show the
selection that I've made.
| | 03:06 |
This can be pretty handy as you're
working on your spreadsheets.
| | 03:09 |
So, in this lesson you learned about the
View controls down in the Status Bar at
| | 03:12 |
the bottom of Excel.
You saw that there are buttons for the
| | 03:16 |
Normal View, the Page Layout View, and
the Page Break Preview.
| | 03:21 |
You also explored some of the ways you
can zoom in and out of your documents.
| | 03:25 |
| | Collapse this transcript |
| Moving around your worksheets and workbooks| 00:02 |
It's important to know how to move around
Excel, and how to navigate worksheets and workbooks.
| | 00:07 |
It's a basic skill that you want to learn
early on, and it just happens to be the
| | 00:10 |
subject of this lesson.
When you have your hands on the mouse,
| | 00:14 |
it's natural to point to a cell.
And you can see the highlight around it.
| | 00:18 |
That's the way you select an individual
cell.
| | 00:20 |
If you want to select more than one cell,
you can hold the mouse button down and
| | 00:24 |
drag, and you'll select more than one
cell.
| | 00:28 |
If you're working with your hands on the
keyboard you may not want to reach over
| | 00:31 |
and grab that mouse so you can use the
Arrow keys to move around.
| | 00:35 |
So if I hit the left arrow, I moved left
and up, down, right, they all work as advertised.
| | 00:43 |
If I want to select more that one cell,
I'll hold this Shift key down when I make
| | 00:46 |
the move.
If I keep pressing the arrows, I select
| | 00:50 |
more cells and that's how you select a
range of cells.
| | 00:54 |
You can use the Ctrl key with your arrows
as well.
| | 00:57 |
So, up here if I hold the Ctrl key down
and hit the left arrow.
| | 01:02 |
It'll take me to the last bit of data in
that row.
| | 01:05 |
And then if I hit it again, it'll take me
to the very first column, going the other
| | 01:10 |
direction, here's my last column.
Now you've probably noticed that columns
| | 01:16 |
are indicated by letters up here at the
top so this is column M.
| | 01:21 |
If If keep going out here all the way to
Z, it starts over again and we have
| | 01:25 |
double A, AB, AC, AD, and so on.
If I hold the Ctrl key down, there's no
| | 01:31 |
more data to the right.
If hold the Ctrl key down and hit the
| | 01:35 |
right arrow it will take me to the end of
the worksheet.
| | 01:40 |
That last column is XFD, so that's a lot
of columns for you to work with.
| | 01:45 |
I'm going to hold Ctrl down, and hit the
left arrow again.
| | 01:48 |
Move all the way back to the beginning,
and the same thing works if you're going
| | 01:52 |
down your rows.
If I hit the down arrow, it takes me to
| | 01:56 |
the first bit of data.
I hit it one more time.
| | 01:59 |
It'll take me to the last bit of data.
If I hit it one more time it'll take me
| | 02:04 |
to the last row in this worksheet.
And you can see that, that is 1 million
| | 02:11 |
48 576.
I'm going to use Ctrl and the Up arrow to go
| | 02:15 |
back up.
Now if you're a touch typist.
| | 02:18 |
You may not even want to take your hands
off those home keys to move over to.
| | 02:22 |
The arrow keys.
So you can use Tab to navigate your worksheet.
| | 02:29 |
Hitting the Tab moves to the right.
Hold Shift and you move to the left.
| | 02:34 |
And you can use the Enter key to move up
and down.
| | 02:38 |
Pressing Enter moves down, and
Shift+Enter.
| | 02:41 |
Makes you move up.
It's probably a good time to talk about
| | 02:44 |
the difference between worksheets and
workbooks.
| | 02:48 |
We're looking at a worksheet now, and if
you look down at the bottom here, you see
| | 02:52 |
several tabs.
And each of these tabs is a worksheet.
| | 02:57 |
And they're all inside of one workbook.
Book and that workbook is householdexpenses-exp.
| | 03:02 |
You can see the name of the workbook up
here in the title bar.
| | 03:05 |
The workbook is the file that we save, so
a workbook can contain many worksheets
| | 03:11 |
and each Excel file that you save is a
complete workbook.
| | 03:18 |
Now you can open up more than one
workbook at a time in Excel, so if I go
| | 03:21 |
over here to File and click Open, I can
open up another one.
| | 03:26 |
Let's use this invoice here.
Now we have an invoice open and it's
| | 03:32 |
taking up the entire workspace in Excel.
You can see that it has worksheets as
| | 03:36 |
well, multiple worksheets inside of this
workbook.
| | 03:40 |
So the question is, if I want to go back
to that other workbook and take a look at
| | 03:43 |
it, how do I do that?
Well you do that with a command on the
| | 03:48 |
View tab, you go View, you go over here
to Switch Windows and click that button
| | 03:52 |
and you see a list of the open workbook.
So I can click on Household Expenses and
| | 03:58 |
go back to that.
I can jump back and forth between the two.
| | 04:02 |
When you have multiple workbooks open,
often you'll want to copy data back and
| | 04:06 |
forth or do actions, something like that.
So you might want to see both of those in
| | 04:12 |
the workspace at the same time.
Well there's a way to do that too.
| | 04:16 |
See this icon right here?
It looks like two windows.
| | 04:21 |
Don't confuse it with the one up here.
This button operates on the Excel window
| | 04:25 |
as a whole, whereas these buttons operate
on the workbooks inside of the workspace.
| | 04:32 |
When I click that.
It shrinks my workbooks down, so I can
| | 04:36 |
see them, and they float in this space.
I can drag and move them around.
| | 04:40 |
I can grab an edge or a corner and resize
them.
| | 04:46 |
And I can jump between workbooks by just
clicking on them or clicking on a cell
| | 04:49 |
anywhere in them.
And I can copy data from one and paste it
| | 04:53 |
into the other.
Very handy when you want to work with
| | 04:56 |
both at a time and there's a way to make
these take up an equal amount of space in
| | 05:00 |
the window.
You can do that by right-clicking on the
| | 05:04 |
Title Bar up here.
You see this menu, go down to Arrange,
| | 05:08 |
and you have several options.
So if I choose Vertical and click OK.
| | 05:13 |
It automatically resizes these so they
take an equal amount of vertical space in
| | 05:18 |
the workspace.
So that's how you work with multiple windows.
| | 05:21 |
If you want to go back to the view where
it, a single workbook fills the workspace.
| | 05:26 |
You can just click this button in the
corner here, says Maximize, and now we're
| | 05:30 |
back at the view where it fills the
entire workspace.
| | 05:35 |
In this lesson you explored some of the
different ways you can navigate through
| | 05:38 |
Excel, through your worksheets and
through your workbooks.
| | 05:41 |
You can use your mouse or you can use the
keyboard.
| | 05:44 |
You can expand your workbook to fill the
available space in Excel or you can
| | 05:47 |
reduce the window making it possible to
see more than one workbook at a time.
| | 05:53 |
| | Collapse this transcript |
| Adding, copying, and removing worksheets| 00:02 |
A workbook can hold multiple worksheets,
and often you'll put related worksheets
| | 00:06 |
in a single workbook.
And this lesson focuses on worksheet
| | 00:10 |
managements, so you learn how to create,
copy, move and delete worksheets inside
| | 00:14 |
of your workbook.
So, let's take a look at this invoice.
| | 00:19 |
And it's actually a system.
You start off with a blank invoice here,
| | 00:22 |
then you make a copy of it to create
numbered invoices, which, each of which
| | 00:26 |
are a separate worksheet.
If you want to move one of the worksheets
| | 00:31 |
to a different position in this line, all
you have to do is click on it and drag,
| | 00:34 |
and you see the little triangle that
shows where it will be inserted.
| | 00:40 |
And you can drag it back to where it was
most of the other commands you access
| | 00:44 |
when clicking on a worksheet tab, and
then you see a little list of commands
| | 00:48 |
that you can use here.
So, for example, Insert creates a new worksheet.
| | 00:55 |
This window appears, gives me a few
options, the first one is worksheet but
| | 00:58 |
there's chart and there's some other
options in here.
| | 01:01 |
So If I click Worksheet and then OK, we
have a new sheet that's inserted inside
| | 01:07 |
of our workbook.
We don't need that one, and it's not
| | 01:11 |
formatted so we'll delete it.
So again, I right-click on the tab, and
| | 01:14 |
then I choose Delete, and then it goes
away.
| | 01:18 |
Now if I want to create a new invoice,
the thing to do is make a copy of it,
| | 01:21 |
give it a number, and then enter the data
into it.
| | 01:26 |
So I'm going to right-click on Invoice.
I'm going to choose Move or Copy because
| | 01:31 |
I want to make a copy.
I see this little window, and I want to
| | 01:35 |
check this box down here that says Create
A Copy.
| | 01:41 |
I'm going to, move it to the end of the
list, so I'll select that option there,
| | 01:45 |
showing here that it's going to go into,
this same workbook that we have.
| | 01:50 |
I'll click OK, and I've got a copy of the
blank invoice.
| | 01:54 |
So the next thing I need to do is give it
a number, so again I'll right-click on
| | 01:58 |
the tab, choose Rename.
We'll number this one 2308, then we can
| | 02:03 |
go ahead and use it.
Put in some data.
| | 02:12 |
And it's ready to send off to my
customer.
| | 02:15 |
Now suppose in this system you have a
separate workbook where you keep all of
| | 02:19 |
your paid invoices, that's the way you
organize them.
| | 02:23 |
So when I'm done, and this is paid, I'll
want to copy it to that other workbook.
| | 02:28 |
The process is the same.
Right-click on the tab.
| | 02:31 |
I'm going to choose Move Or Copy.
I'm going to check the box, create a copy.
| | 02:37 |
But in this case, I'm going to use this
menu here and change it to My Paid Workbook.
| | 02:43 |
Now, My Paid Workbook is already open
inside of Excel.
| | 02:46 |
You see in this list, only the workbooks
that you have open.
| | 02:50 |
So this paid workbook is open.
I'm going to choose that.
| | 02:53 |
I'll click OK.
And now here we are in My Paid Workbook.
| | 02:58 |
It automatically switches to that
workbook.
| | 03:01 |
Here's the invoice that I copied over,
and I can drag it to a new location.
| | 03:05 |
And we're all squared away.
So in this lesson you saw how easy it is
| | 03:10 |
to manage multiple worksheets inside of a
workbook.
| | 03:14 |
When you right-click the worksheet tabs
you see the commands that you can use to
| | 03:18 |
create, delete, copy, and rename your
worksheets.
| | 03:22 |
| | Collapse this transcript |
|
|
3. Developing Your SpreadsheetEntering and formatting text and numbers| 00:02 |
When you develop a spreadsheet, a big
part of the process is entering text and numbers.
| | 00:06 |
And often you'll want to use formatting
to emphasize certain aspects of your worksheet.
| | 00:10 |
So this lesson shows you how to use
different font styles and colors to make
| | 00:14 |
your point.
You'll also see some tricks for quickly
| | 00:17 |
copying and applying formatting.
Just to keep things simple, let's suppose
| | 00:22 |
we're creating a worksheet to keep track
of household expenses each month, and
| | 00:25 |
we're starting from scratch.
When you want to enter text, just select
| | 00:30 |
the cell that you want to use and start
typing.
| | 00:33 |
Now if you make a mistake while you're
entering text you can just backspace,
| | 00:37 |
over the mistake, and make a correction.
When you're done, press Enter, an you'll
| | 00:43 |
move down a row, an you can keep on,
entering text.
| | 00:48 |
If you enter a word that's too long for
the space, you'll see it while you're
| | 00:51 |
typing it in, but you might have to go
back later and make some changes to make
| | 00:55 |
sure it works with the other things in
your worksheet.
| | 01:01 |
If you use a word that you've already
used before, Excel's AutoText prompts
| | 01:04 |
you, and basically it's saying you
already used this word in the column.
| | 01:09 |
You can use it again by pressing Enter.
Or if you don't want to use it, keep on typing.
| | 01:15 |
So I'm going to press enter, and it enters
the word.
| | 01:18 |
Now we don't really need two phones in
here, so lets look at editing text that's
| | 01:21 |
in a cell.
There are two places you can do that.
| | 01:24 |
You can do it up here in the formula bar.
All you have to do is click where you
| | 01:28 |
want to make the change.
Make your change, and then when you're
| | 01:33 |
done, click the Check Mark for enter, or
click the X for cancel.
| | 01:38 |
I'm going to click Cancel so we can look at
another option.
| | 01:42 |
You can also edit right in the cell, and
the way to do that is to double-click on
| | 01:46 |
the cell, and your cursor appears in the
cell, and you can move it using the
| | 01:49 |
arrows, or you can click in a location to
move the cursor and make your change.
| | 01:56 |
When you're done, just click Enter again
and you're all set.
| | 01:59 |
And when you enter text and numbers
you're likely to be doing it in columns,
| | 02:03 |
or in rows, so let's look at putting some
months up here.
| | 02:07 |
If you want to move your selection to the
right, you'd press Tab, and that enters
| | 02:11 |
the text, and we're ready for the next
option.
| | 02:18 |
So using Tab like the Enter key becomes
automatic the more you work with in Excel.
| | 02:24 |
Now we got several words in here.
I'm going to switch to a different worksheet
| | 02:28 |
where I've entered some numbers.
We can see our little mini worksheets in
| | 02:32 |
here and let's look at some of the
formatting options.
| | 02:36 |
We're going to look at the Home Font
group of formatting options in here.
| | 02:41 |
You really see the usual suspect.
Just select the cell that you want to
| | 02:45 |
format, and then you can choose one of
these options.
| | 02:47 |
So I can choose a font style.
When I open up the menu I see a list.
| | 02:52 |
Not all of them are, would be great for a
spreadsheet, but I can go through and see
| | 02:56 |
a preview of them as I point to them.
And then just click to choose the font
| | 03:01 |
that I want.
The same thing with size.
| | 03:03 |
You see size and numbers and you get a
preview, and you just click to make,
| | 03:07 |
choose your option.
These two buttons also give you a way to
| | 03:11 |
change the size.
You can bump the size up with one click,
| | 03:14 |
or you can bump it down with one click.
Down here you have your typical text
| | 03:19 |
styles, Bold, Italic, and Underline.
So, I can format several things at once
| | 03:24 |
just by clicking and dragging to select
the cells, and then I can click Italic to
| | 03:28 |
change to italic.
I'll change that to Bold.
| | 03:34 |
Another option here is borders.
You can put a border around the cell.
| | 03:40 |
Now, you see this grid when you're
looking at your worksheet, but these
| | 03:43 |
lines are faint and they don't print when
you print out on a piece of paper.
| | 03:48 |
If you want something to print on a piece
of paper, you want to choose one of these
| | 03:51 |
border options.
So if I open up this menu I can see I can
| | 03:55 |
put a bottom border, top border, outside
border, I can make the border double or thick.
| | 04:01 |
So you have all sorts of options.
I'm just going to choose a bottom border,
| | 04:04 |
and now we have a bottom border below our
months, and that will print when we go to
| | 04:08 |
the printer.
The last thing to look at are color
| | 04:11 |
options with a paint bucket we have this
letter here.
| | 04:15 |
If I select Foam, the paint bucket
changes the background of the cell color.
| | 04:20 |
So I can choose a light blue, and then I
could change the text color to a dark blue.
| | 04:29 |
So now we're adding color to our
spreadsheet.
| | 04:32 |
Now let's look at some ways to copy
formatting to other cells.
| | 04:37 |
You can format several cells at once just
by selecting them and then choosing an option.
| | 04:44 |
But sometimes you'll want to make those
changes after the fact, and the tool to
| | 04:47 |
do that is this Format Painter.
Click on the Format Painter, you see the
| | 04:51 |
little paintbrush icon, and then click on
the cell that you want to change.
| | 04:58 |
You can apply changes to several cells at
once just by clicking and dragging.
| | 05:02 |
Another trick with the format painter is
to make it sticky.
| | 05:06 |
So if you select a cell and you double
click on the format painter, you see the
| | 05:10 |
paintbrush icon.
And you can click on different cells and
| | 05:14 |
apply that formatting to different
locations.
| | 05:17 |
When you're done, just click on format
painter again to turn it off.
| | 05:21 |
Kinda undo some of that, make things look
better around here.
| | 05:25 |
Now last, let's look at adjusting the
cell size.
| | 05:30 |
We have a couple of words here that
really don't fit in the cell.
| | 05:33 |
What we want to do is change the width of
the A column.
| | 05:36 |
So if I point to the line between A and
B, we get a cursor with two arrows and I
| | 05:41 |
can click and drag to change that cell
width.
| | 05:46 |
We can do it just how we want it.
Now there's another way to do it too
| | 05:50 |
that's even faster.
When I see the double arrows cursor, if I
| | 05:54 |
double-click, it automatically sizes the
cell to fit the text that's in there.
| | 05:59 |
And the same thing works with row height.
If I click at the bottom of the two I can
| | 06:05 |
drag the bottom down, and it makes this
cell much taller.
| | 06:10 |
So in this lesson, you learned how to
enter text in your worksheet then how to
| | 06:13 |
spruce it up with formatting.
You reviewed some of the basics at, such
| | 06:18 |
as changing font size and using Bold and
Italics.
| | 06:22 |
And you learned how to use the Format
Painter to speed things up.
| | 06:25 |
All these formatting techniques work with
both text and numbers.
| | 06:29 |
| | Collapse this transcript |
| Aligning text and numbers| 00:02 |
When you build an Excel worksheet, you're
likely to spend a fair amount of time
| | 00:05 |
lining up rows and columns of text and
numbers.
| | 00:08 |
This lesson explores the commands in the
Home Alignment group in the Ribbon.
| | 00:13 |
So, one of the first things that you'll
notice when you, enter text and numbers
| | 00:18 |
is that, text is automatically aligned to
the left, while numbers are automatically
| | 00:22 |
aligned to the right.
Most of the time that works really well,
| | 00:27 |
but you'll see some cases where you need
to make some changes.
| | 00:30 |
For example, these months don't align
very well over the numbers here.
| | 00:35 |
So, that's when you need to turn to the
Home tab, and the Alignment group where
| | 00:39 |
you see all sorts of controls to align
your text and numbers.
| | 00:43 |
So, if I select all three of these
months, then I can make changes to them.
| | 00:49 |
You'll see this cell has some extra
height here, so we can look at some of
| | 00:52 |
the vertical alignment options which you
find up here.
| | 00:56 |
You can align to the top or the middle,
or the bottom.
| | 01:01 |
And the bottom option is the one that
Excel uses most of the time for text and numbers.
| | 01:05 |
You can also align horizontally, left is
standard for text, but you can use center
| | 01:10 |
or you can align to the right.
When you have text labels over columns of
| | 01:16 |
numbers, that right option is usually the
best one.
| | 01:22 |
Now, one of the challenges in developing
a spreadsheet, particularly when you're
| | 01:27 |
printing it out, is to get a lot of
information into a very small space.
| | 01:33 |
And one of the things that you'll see is
that the text usually takes up more room
| | 01:36 |
than the numbers.
So, you might want to find some ways to
| | 01:40 |
squeeze text into less space.
And one way to do that is to change the
| | 01:45 |
text orientation.
So, for example If I go up here to this
| | 01:50 |
Orientation menu, I can angle the text,
and that'll make it fit in a little bit
| | 01:55 |
smaller space.
Or we can choose some of the other options.
| | 02:01 |
You can even make it go directly up and
down, and then you can fit a lot in this space.
| | 02:09 |
So, those are some of the options you'll
want to consider when your trying to
| | 02:12 |
squeeze things onto the page, going to undo
that for now.
| | 02:16 |
We'll take a look at something else.
You see, first quarter up here at the
| | 02:21 |
top, and it's in the B cell.
But it would really be better if it were
| | 02:25 |
sort of distributed across all three of
these cells.
| | 02:29 |
And you can do that with the Merge and
Center command.
| | 02:32 |
So, if I select three cells, click Merge
and Center.
| | 02:37 |
Now we have first quarter in a single
cell that's taking up the space of these
| | 02:42 |
three cells.
Then you can go ahead and you can format
| | 02:47 |
it, you could make it bigger, if you
wanted to.
| | 02:49 |
And that's one way to emphasize a certain
aspect of your spreadsheet.
| | 02:55 |
If you want to, you can indent text, so
suppose that we wanted to have Gas
| | 02:59 |
indented underneath Phone, we could use
the indent commands, here.
| | 03:05 |
But it works just like Tabs in a Word
Processor.
| | 03:08 |
If I click Indent, it moves it over a
bit, I can use the other button to remove
| | 03:12 |
the indent.
Now sometimes you'll have cells with more
| | 03:19 |
than one word in them, and it might not
all fit inside of the cell.
| | 03:25 |
One remedy is to Wrap text, this command
here and that way, we can spread those
| | 03:31 |
two words over two lines.
In this lesson, you learned how to align
| | 03:36 |
text and numbers vertically, horizontally
and on a slant.
| | 03:41 |
You learned how to use indents, how to
Wrap text, and how to use the Merge and
| | 03:44 |
Center commands.
| | 03:46 |
| | Collapse this transcript |
| Choosing number formats| 00:02 |
When you use numbers in Excel, you need
to make some decisions about how to
| | 00:05 |
display them.
This lesson explores your options,
| | 00:08 |
showing you the different ways you can
display numbers as currency, percentages,
| | 00:12 |
or dates.
So consider a spreadsheet like this that
| | 00:16 |
keeps track of the time spent developing
an ad.
| | 00:19 |
It shows numbers in several different
formats.
| | 00:22 |
For example, here's a date.
It was entered in up here, but it was
| | 00:26 |
formatted so it just shows the month and
the year.
| | 00:32 |
These columns here also show dates and
times.
| | 00:36 |
So, you see the start time and the finish
time.
| | 00:39 |
And then the time spent on the project is
calculated in this hours column.
| | 00:43 |
And here the numbers are formatted to
show hours and minutes.
| | 00:48 |
These numbers should look familiar,
they're currency.
| | 00:50 |
And they're formatted to show dollars.
So, you have a dollar symbol, and it
| | 00:55 |
shows decimal points.
And then on the far end here, we have
| | 00:58 |
fractions that are shown as a percentage.
So, let's see some the ways we can do
| | 01:04 |
that kind of formatting magic.
When you enter a number in your
| | 01:07 |
spreadsheet, if you don't give any
formatting instructions, the number is
| | 01:10 |
shown just as you typed it.
So if you put in a number with no decimal
| | 01:14 |
point, that's what you'll see.
If you add a decimal to it, it'll show
| | 01:18 |
that as well.
If you type in a long number, you won't
| | 01:21 |
see any commas to separate it or anything
like that.
| | 01:25 |
So to make formatting changes you need to
format specific cells with the formatting
| | 01:30 |
that you want.
So, for example, we'd like to add commas
| | 01:34 |
to separate these large numbers.
We can choose the numbers that we want to
| | 01:39 |
format, choose the cells.
And then go up here to Home Tab and the
| | 01:43 |
Number Group and we can make some choices
off of here.
| | 01:47 |
So, for example, if we click comma, that
formats our numbers.
| | 01:52 |
With comma to separate the large numbers.
It also adds decimal points to the
| | 01:56 |
numbers automatically.
We want to use currency.
| | 02:01 |
We can select those.
And then we can choose on of the currency options.
| | 02:06 |
You can use this menu.
You see general at the top, the format
| | 02:09 |
that Excel usually uses.
And then you can choose number, currency,
| | 02:13 |
accounting, and you have all these
choices down here.
| | 02:16 |
So if we choose currency, we see dollar
signs and decimal points.
| | 02:22 |
If we want to change that currency, we
could go up here, and we could use a
| | 02:25 |
different unit, for example.
We could use Euros.
| | 02:29 |
Now, decimal points are always a big
issue.
| | 02:32 |
You usually want to line up your decimal
points so that your numbers make sense
| | 02:35 |
when you look at them in columns.
You can add or remove decimal points
| | 02:39 |
using these buttons here.
So you can increase the decimal point.
| | 02:46 |
You can show percentages as fractions,
but usually it's clear if you use the
| | 02:49 |
percentage style.
And you can do that just by clicking this
| | 02:53 |
button here, and it turns fractions into
percentages.
| | 02:57 |
You can also display fractions in the
fraction style.
| | 03:00 |
So select the numbers that you want, and
go up here and you can choose fraction
| | 03:04 |
off this drop down list.
And you can use this kind of fraction in
| | 03:09 |
your formulas.
So, for example, you could find out what
| | 03:13 |
one third of a specific number is by
using this in your calculation.
| | 03:18 |
Now Excel's pretty smart about dates.
If you enter a date, say if you enter May
| | 03:25 |
30, 2013.
Excel knows that's a date, and it
| | 03:30 |
automatically formats it to show it as a
date.
| | 03:33 |
You could also enter that same number,
using slashes.
| | 03:38 |
Five slash 30 slash 13.
And again it formats it as if it were a date.
| | 03:45 |
Now if these options aren't enough for
you, you can see even more number
| | 03:49 |
formatting options up here.
Just go to the number group, click the
| | 03:53 |
button in the lower right corner, and
you'll see this dialog box, which is
| | 03:56 |
all about numbers.
You can choose a category.
| | 04:02 |
And then you'll see there may be more
options in here than you were presented
| | 04:05 |
in the ribbon.
So, for example, if I go to symbol I can
| | 04:08 |
see there are more currency options up
here.
| | 04:12 |
And I can choose different ways to
display my negative numbers.
| | 04:16 |
I can use a negative sign.
Or I can put it in parans.
| | 04:19 |
I can even make it red.
So those are some of the things you can
| | 04:24 |
do with the number formatting options.
In this lesson, you explored the number
| | 04:28 |
formatting options in the home tab.
You saw that you can fine tune your
| | 04:32 |
number formatting using the number tab in
the format cells dialog box.
| | 04:37 |
| | Collapse this transcript |
| Totaling rows and columns with AutoSum and Fill| 00:02 |
This lesson focuses on what just might be
the 2 handiest commands in Excel.
| | 00:06 |
Auto sum, and fill.
Using these two tools, you can create
| | 00:10 |
remarkable spreadsheets, and you don't
even have to know very much about writing formulas.
| | 00:17 |
So consider a spreadsheet like this that
keeps track of different expenses and
| | 00:20 |
shows us expenses over the course of a
month.
| | 00:24 |
It's really natural to want to know what
your total expenditure on water, gas, or
| | 00:28 |
any of these items might be.
So the natural thing to do would be to
| | 00:33 |
get a total of this row, and place it
over here in this column at the end.
| | 00:38 |
That's exactly what you can do with
Autosum.
| | 00:40 |
So I select a cell, I'm going to go to the
Home tab, over here in the Editing group
| | 00:45 |
you can see Autosum.
So all I have to do is click that, so
| | 00:49 |
Excel wrote a formula up here for me.
So this cell will equal the sum of these
| | 00:55 |
numbers, from B2, and this is B2 here, to
M2, and this is M2 here.
| | 01:00 |
That's exactly what I want to do.
I want the sum of this row of numbers.
| | 01:06 |
So all I have to do to enter that formula
is just click it.
| | 01:09 |
And there we go.
So just to repeat.
| | 01:13 |
To use auto sum to calculate the total of
this row of numbers, all I have to do is
| | 01:17 |
click auto sum, and then go over here and
click the enter button.
| | 01:24 |
Auto sum is pretty clever too because it
anticipates what you want to do.
| | 01:27 |
So if I'm down here at the bottom of the
column of numbers and I click auto sum,
| | 01:31 |
it shows that I'm going to total this
column of numbers.
| | 01:35 |
So from B2, that's that number, to B7,
that's the number down here.
| | 01:40 |
Again all I have to do is click.
And it gives me the total of those numbers.
| | 01:44 |
Now you don't always have to accept the
decisions that auto sum makes, either.
| | 01:49 |
Suppose we wanted to have a grand total
for all of these numbers, and place it
| | 01:52 |
down here so we know exactly the total of
what we spent for the entire year.
| | 01:57 |
If I click auto sum.
It's going to guess that I want to add
| | 02:01 |
these numbers here.
But if that's not what I want to do, I
| | 02:04 |
can override it.
I can just select this number here, and
| | 02:07 |
then drag it out to the point that I
want.
| | 02:11 |
So now we're going to total all the
numbers from B2, way down here, to M7.
| | 02:17 |
All I have to do is click the enter
button, and there we have my grand total.
| | 02:21 |
So that's the way that auto sum works and
it's very clever.
| | 02:25 |
Now the other feature I want to talk
about is fill.
| | 02:28 |
If I select a number, it has formula in
it, as it does over here, and I move my
| | 02:32 |
cursor over the lower right corner of the
selection box, it turns to theis plus
| | 02:36 |
sign, and that's the fill tool.
And to use that, if I hold down my mouse
| | 02:44 |
button, and drag this down, it copies
those formulas into these other cells.
| | 02:52 |
And not only that, when it copies the
formula, it translates them.
| | 02:55 |
So you can see that this formula totals
in the number 4 row.
| | 03:02 |
If I double-click it I can see that's
exactly what it does.
| | 03:06 |
That's what you want it to do most of the
time.
| | 03:09 |
You can also use the filter total
columns.
| | 03:11 |
So there we go, so just like that we have
a spreadsheet that totals the expenses,
| | 03:17 |
and totals the amount of money we spent
each month, and gives us a grand total
| | 03:22 |
for all those numbers.
In this lesson, you learned how to use
| | 03:28 |
Autosum to calculate the total value of
rows and columns, and then you saw how
| | 03:32 |
you can use the Fill command to copy
those formulas to other cells.
| | 03:37 |
| | Collapse this transcript |
| Referencing cells by row and column| 00:02 |
Spreadsheets are electronic ledgers where
the sheet is divided into rectangular cells.
| | 00:06 |
At the core of a spreadsheet, is the
ability to reference cells and use the
| | 00:10 |
values stored in those cells in your
formulas.
| | 00:14 |
So, that's the topic for this lesson.
You see how to reference cells and how to
| | 00:18 |
build simple formulas.
Now your spreadsheet is organized in
| | 00:23 |
columns, which are referenced by letters,
and rows, which are referenced by numbers.
| | 00:30 |
If you click on a single cell, you'll see
a highlight for the column and the rows.
| | 00:36 |
So we know the reference for this cell is
C4.
| | 00:40 |
And that's exactly what you'd use in a
formula.
| | 00:42 |
So, let's take a look at creating a
formula.
| | 00:44 |
Suppose we want to add the house phone
and the cell phone expenses to see what
| | 00:49 |
the total is.
So to start a formula you always use the
| | 00:54 |
equal sign, that's your way of telling
Excel what follows is going to be a formula.
| | 00:59 |
And then the easiest way to reference a
cell is to click on it, and you can see
| | 01:04 |
the reference appears in our formula so
B8 pops in here.
| | 01:09 |
Now I'm going to hit the plus sign on my
number pad and then I'm going to choose
| | 01:14 |
another cell, B7.
And that's our complete formula.
| | 01:18 |
B8 plus B7.
Looks good, so I'm going to click on the
| | 01:22 |
enter check mark here, and that puts our
formula in here.
| | 01:27 |
And you notice, if you want to edit the
formula you can come back up in here.
| | 01:32 |
And you notice that when you're editing
or building your formula, you get this
| | 01:36 |
great little reference showing you what
cells are affected by the formula.
| | 01:41 |
Suppose we wanted to continue to build
this formula?
| | 01:44 |
I could keep hitting the plus and a cell
reference plus cell, plus cell, plus cell.
| | 01:52 |
Now we built a formula that uses all the
cells in this column and I click the
| | 01:56 |
check mark and there's a formula that
totals all the numbers in this column.
| | 02:02 |
If I go in here, just one other handy
thing to notice.
| | 02:05 |
Now, you can edit your formula up in the
formula bar or you can edit it down in
| | 02:09 |
the cell itself.
Either way, you notice this handy color coding.
| | 02:13 |
So, for example, if we're wondering what
B4 refers to, we can go up here.
| | 02:19 |
And see the cell that has the green
border around it, that gives you another
| | 02:24 |
way to analyze what's going on in your
formulas.
| | 02:29 |
Now that's not the, the best way to total
a column of numbers.
| | 02:33 |
There's a way that you can reference a
range of numbers by referencing the first
| | 02:38 |
point and the last point.
And the operator that you use in that
| | 02:43 |
formula is the the colon character, you
can think of it as two.
| | 02:47 |
Let me show you how it works.
I'm going to press equals to start my formula.
| | 02:51 |
Now I'm going to use a function in my
formula, the sum function.
| | 02:57 |
So I start typing S U, and you can see
Excel prompts me with a list of functions
| | 03:02 |
that start off with SU.
I can click on sum, that's the one I
| | 03:07 |
want, double-click.
And you see it enters the name of the
| | 03:11 |
function in here and a parenthesis to
start the function.
| | 03:15 |
Now all I have to do is click on the
first cell in my range then hold my mouse
| | 03:20 |
button down and then drag down to the
bottom.
| | 03:25 |
You can see it builds this formula for
me, C3 to C8 and the colon in there is
| | 03:29 |
the operator for referencing a range of
cells.
| | 03:34 |
All I have to do is close the function
with a parentheses, and then click Check,
| | 03:41 |
and we have our formula built in here
that uses the sum function and the C3 to
| | 03:46 |
C8 range reference.
Now, it's not all about adding numbers,
| | 03:54 |
so let's do one more formula that's
subtraction.
| | 03:57 |
Suppose you want to subtract the cell phone
from the house phone bill for some reason.
| | 04:04 |
So we start off our formula with, with
equal.
| | 04:06 |
Let me click the house phone bill, then
use a minus key and then click the cellphone.
| | 04:16 |
So now we have a formula that does
subtraction.
| | 04:18 |
So that's just how easy it is to build a
simple formula and when you do complex
| | 04:23 |
formulas you really just build on those
principles.
| | 04:27 |
So in this lesson you saw how to
reference a single cell and how to
| | 04:30 |
reference a range of cells using the
colon operator.
| | 04:34 |
You also saw how to use those cell
references in your formula.
| | 04:39 |
| | Collapse this transcript |
| Referencing cells in another worksheet| 00:02 |
Spreadsheets have a way of growing and
becoming more complex.
| | 00:05 |
Sooner or later you'll have a workbook
with multiple worksheets, and you'll want
| | 00:09 |
to reference a cell that's in a different
worksheet.
| | 00:12 |
Now as you see in this lesson, that's
pretty easy to do.
| | 00:16 |
So, consider this workbook where we keep
track of our expenses for each month,
| | 00:19 |
different type of expenses.
We've got some detail, in these other
| | 00:23 |
worksheets, on when the bills were paid.
So, we have phone, municipal water and
| | 00:29 |
consolidated gas.
Suppose I want to reference the date that
| | 00:33 |
my phone bill was paid in January.
I choose my cell, start the formula as I
| | 00:38 |
always would with an equals sign.
And then I can click on the phone tab.
| | 00:44 |
And then I can click on the cell that I
want to reference in that tab.
| | 00:48 |
And here's my complete formula, the
equals sign.
| | 00:51 |
Phone is the name of the worksheet I want
to reference.
| | 00:54 |
And there's an exclamation point that
separates the name of the worksheet from
| | 00:58 |
the cell that I'm referencing.
So if I click enter, it takes me back to
| | 01:03 |
my first worksheet where I'm entering the
formula.
| | 01:07 |
So there's my value, and there's my
formula.
| | 01:09 |
Now if a worksheet has more than one word
in the name, Excel handles it a bit differently.
| | 01:15 |
So let's look at that.
Here's my equal sign.
| | 01:18 |
I'm going to click on municipal water.
Click the date.
| | 01:23 |
So you can see it builds my formula
again.
| | 01:26 |
Here we have the equal sign and then
municipal water is inside of single quotes.
| | 01:31 |
That's the way that excel handles a
worksheet that has more than one word in
| | 01:35 |
the name.
We've got the exclamation point that
| | 01:39 |
separates the name of the worksheet from
the cell reference.
| | 01:43 |
And there's the formula.
It's that simple.
| | 01:46 |
So your formulas that reference other
cells in other worksheets can be complex,
| | 01:50 |
they can use functions, and they can use
ranges of cells.
| | 01:54 |
So let's look at that.
We'll get the total for the gas bills.
| | 01:58 |
So I'm going to use the equals sign, and
I'm going to use a function, use the sum
| | 02:02 |
function here, and I've got the function
started.
| | 02:07 |
Here's where I want to put the range of
cells that are totaled in that sum.
| | 02:12 |
Then click on the consolidated gas, and
I'll click and drag to select this whole
| | 02:17 |
range of numbers.
You can see I've selected C2 through C13.
| | 02:25 |
And I closed my function.
So here's the whole formula, equals the
| | 02:30 |
sum function, and you then have the
paran.
| | 02:33 |
You've got quotes around the name of the
worksheet.
| | 02:36 |
You have the exclamation point that
separates that worksheet name from our
| | 02:41 |
cell reference.
And in this case we're referencing a
| | 02:45 |
range of cells from C2 to C13.
When I click check, we see the value, the
| | 02:51 |
total of that range of cells.
So that's how easy it is to reference
| | 02:56 |
cells that are in other worksheets.
In this lesson you saw that you can
| | 03:01 |
reference the worksheets in your work
book using their name followed by an
| | 03:04 |
exclamation point, and then your cell
reference.
| | 03:08 |
If your worksheet is more than one word,
it needs to be surrounded by.
| | 03:12 |
Quotes, and you can use these cell
references to other work sheets in any
| | 03:15 |
type of formula that you want to create.
| | 03:18 |
| | Collapse this transcript |
| Cutting, copying, and pasting| 00:02 |
When you work in Excel, you use the same
editing tools that you use in your Word
| | 00:06 |
processor, cut, copy and paste.
However, there are a few quirks that are
| | 00:10 |
unique to spreadsheets when you use these
tools, so you get to see them in action
| | 00:14 |
in this lesson.
Now, the editing tools are on the Home
| | 00:18 |
tab in the Clipboard row, and you see
Cut, Copy, Format Painter, and Paste.
| | 00:24 |
In this lesson we're going to look at
cut, copy, and paste.
| | 00:27 |
Format Painter is a special tool that you
use really to, just to copy formats from
| | 00:31 |
one cell to another.
Now cut has a shortcut key as you can see
| | 00:36 |
from the little pop-up toolbar there.
Ctrl+X for Copy it's Ctrl+C, and for
| | 00:43 |
paste it's Ctrl+V.
Now these should be easy to remember
| | 00:47 |
because a lot of programs use these same
shortcut keys for cut, copy and paste.
| | 00:52 |
You'll find them in Word and Outlook and
in PowerPoint and tools that are non
| | 00:56 |
Microsoft tools as well.
Now there's one other way that you can
| | 01:00 |
bring up these commands and that's if you
select a cell and then you right-click.
| | 01:05 |
You'll see this context menu, and at the
very top of the list you see cut, copy,
| | 01:09 |
and paste.
If you right-click on a cell, you see
| | 01:13 |
this context menu, and at top of the list
you see cut, copy, and paste.
| | 01:19 |
Now let's see one of these in action.
I'm going to select this cell, it has
| | 01:22 |
text in it, consolidated gas, it's not a
formula.
| | 01:26 |
You don't see an equals sign.
It does have some formatting there.
| | 01:30 |
I'm going to cut that, select a new cell,
and then I'm going to click the clipboard up
| | 01:34 |
here and paste it in.
So, what you see is it cuts the text but
| | 01:38 |
it also cuts the formatting out of that
cell and it pastes it in down here.
| | 01:44 |
So, the Cut command includes formatting
when you paste it back in.
| | 01:49 |
So, that's the way it works with text and
you could see it works the same way with numbers.
| | 01:53 |
We could select a range of numbers here.
Go up and cut, go down here and paste
| | 01:59 |
them in.
Now when I pasted in, notice the formulas
| | 02:03 |
here, cuz these numbers change.
The reason those numbers change is
| | 02:09 |
because these are formulas, it's a sum
formula.
| | 02:12 |
And it's referring to the sum of these
cells now that I removed the numbers from
| | 02:16 |
those cells, it changes the total value
down here.
| | 02:22 |
The paste operation pasted the numbers
literally into these cells.
| | 02:26 |
Gonna undo that.
Bring them back up there.
| | 02:32 |
Now, let's see what it does to a formula.
Let's choose this formula.
| | 02:37 |
This is a sum and it's referring to these
cells and the range that it's referring
| | 02:40 |
to is shown here in this outline.
It's C3 through C8.
| | 02:44 |
So, so I'm going to cut and paste.
And you can see it cut the formula out.
| | 02:56 |
You can see that it cut the formula and
copied it literally into the new cell.
| | 03:01 |
I have that cell selected, and here's the
formula in here.
| | 03:04 |
And you can see that the range it refers
to is the same.
| | 03:09 |
Even though this formula is in a
different cell.
| | 03:12 |
It's still referring to the same range of
cells.
| | 03:15 |
So, it's still providing the total to
those cells.
| | 03:17 |
So, I'm going to undo this.
So, you can see that the Cut command
| | 03:23 |
literally copies.
Either the text or the numbers or the
| | 03:27 |
formula that's in that cell.
Now the Copy command works a little bit
| | 03:30 |
differently, so let's take a look at it.
If I select this cell, contains this
| | 03:35 |
formula, and I copy it, now I'm going to
paste it down here.
| | 03:40 |
We get different numbers.
So, this formula is referring to these
| | 03:44 |
cells, while this formula is referring to
these cells.
| | 03:50 |
What happened here is, when you copy
Excel uses a relative reference, so you
| | 03:54 |
can think of it like this.
Excel looks at this formula and says,
| | 04:00 |
okay I want this sum of the six cells
above this formula.
| | 04:05 |
And when we copied it and pasted it.
And it gave us a relative reference.
| | 04:11 |
It's still saying, I want the sum of the
six cells above this formula.
| | 04:16 |
And that's how the Copy command works.
And that's a good thing, because that's
| | 04:20 |
usually what you want.
Consider this, we, if we want to copy this formula.
| | 04:26 |
Then paste it over here, what we've
probably want is the sum of these cells.
| | 04:30 |
That's exactly how it works.
And it makes developing a spreadsheet
| | 04:35 |
very quick.
For example you can copy a formula then
| | 04:39 |
you can paste that formula into several
cells at once and look at that we've got
| | 04:43 |
totals of all these columns.
And they work just like we want them to.
| | 04:50 |
They add the numbers that are above the
formula.
| | 04:53 |
So, it's important to understand how Cut
and Copy work differently.
| | 04:58 |
The Cut command literally takes numbers,
text, and formulas and places them in A
| | 05:03 |
new cell when you paste it in.
Whereas the Copy command uses a relative
| | 05:09 |
reference when it does the same thing.
What if we want to copy something, and we
| | 05:14 |
want to paste in, not the formula but we
really want to paste in the values of those
| | 05:18 |
numbers into a cell.
We want 242.24 that's what we want in the
| | 05:24 |
cell that we're going to.
We know we can't copy and paste the
| | 05:28 |
formula in the normal way, because we
want a relative reference.
| | 05:32 |
What we want to do is use one of the
special copy, what we need to do is paste
| | 05:36 |
it in, in a special way.
So, I'm going to copy those cells and then
| | 05:41 |
choose my location where I want to paste it
in, but instead of clicking on the
| | 05:45 |
clipboard, I'm going to go down here to this
menu.
| | 05:49 |
An you can tell that this is a menu,
because of the little triangle button there.
| | 05:53 |
When I click that, we see a menu with a
lot a different paste in options, and
| | 05:57 |
they're grouped in different categories.
We have paste, we have paste values and
| | 06:02 |
we have other paste options.
Well paste values is the one we want.
| | 06:07 |
So, I'm going to click paste values and you
can see, that we pasted numbers if you
| | 06:12 |
look up in the formula bar you can see
these are numbers that we pasted into the
| | 06:16 |
new cells.
These cells still show the formulas.
| | 06:23 |
And for example, if we take some numbers
out of here, these formulas show a new
| | 06:29 |
value cuz they're still working on those
six cells, whereas these numbers.
| | 06:36 |
Remain the same, because these aren't
formulas at all.
| | 06:39 |
These are literally numbers that we
pasted into those cells.
| | 06:43 |
So, those are some of the things that you
need to consider when you're using cut,
| | 06:47 |
copy and paste in Excel.
In this lesson, you saw that you can cut
| | 06:51 |
and paste the contents of a cell, and
Excel handles the content literally.
| | 06:55 |
But when you copy and paste the contents
of a cell, Excel uses a relative
| | 06:59 |
reference to the cells and range of
cells.
| | 07:03 |
If you want to override the way Excel
pastes data, you can open the Paste menu
| | 07:08 |
and use one of the many paste options
inside.
| | 07:14 |
| | Collapse this transcript |
| Clearing contents and deleting cells| 00:02 |
In Excel, there's a difference between
deleting a cell and clearing the contents
| | 00:05 |
of a cell.
This lesson points out the differences,
| | 00:09 |
and it shows how to do both.
So I put together this spreadsheet that
| | 00:12 |
just uses bands of colors for the
columns.
| | 00:15 |
And the numbers in the cells match the
row numbers over here, just to help
| | 00:18 |
illustrate my point.
Often you want to cut the values out of a cell.
| | 00:23 |
For example, if you use the cut and paste
method, select a group of cells here, I
| | 00:27 |
can cut them, click on a new location,
and then paste that in.
| | 00:33 |
And you can see that it cuts the values
out, and it copies the formatting as
| | 00:37 |
well, and it pastes it into the new
location.
| | 00:41 |
I'm going to undo that.
But what if I don't really want to paste
| | 00:45 |
it in someplace else?
What if I just want to remove the values
| | 00:49 |
from those cells?
The thing to do is select the cells, and
| | 00:53 |
then press the Delete key.
When I press the Delete key, it removes
| | 00:58 |
the values in the cells.
It leaves the formatting behind, but it
| | 01:02 |
removes the values, and they're no longer
in there.
| | 01:04 |
And you might think of that as deleting
the cells, but that's not really the case.
| | 01:09 |
You're deleting the value or clearing the
contents of those cells.
| | 01:14 |
Gonna undo.
In fact, if I don't want to use the
| | 01:16 |
keyboard, I can right-click on my
selection, and I can choose this command,
| | 01:20 |
Clear Contents.
And that does exactly the same thing.
| | 01:25 |
It clears the values out of the cells.
Now, to show you the difference, we'll
| | 01:29 |
choose a group of cells.
Now we'll delete the cells, and you can
| | 01:34 |
do that by going Home, and then over to
the Cells group in the home ribbon, and
| | 01:38 |
then clicking Delete.
You can see the kind of dramatic change
| | 01:42 |
that makes.
It literally removed the cells from that
| | 01:45 |
area, so that this cell had to shift over
to the left, because those other cells
| | 01:49 |
were no longer there.
It literally deleted the cells.
| | 01:54 |
Most of the time that's not what you
want to do.
| | 01:56 |
When you're working with a spreadsheet
that goes on forever in all directions,
| | 02:00 |
if you delete the cells in the middle of
it, it changes the relationship of everything.
| | 02:05 |
And that's likely going to have some
effect on your formulas, as well.
| | 02:09 |
So, this lesson showed that when you work
in Excel, it's more common to want to
| | 02:13 |
clear the contents of a cell than it is
to want to actually delete the cells.
| | 02:19 |
You saw that you can clear the contents
using the Delete key.
| | 02:22 |
Or by using a context menu, you can
right-click somewhere in your worksheet,
| | 02:25 |
and then choose Clear Contents from the
menu.
| | 02:29 |
| | Collapse this transcript |
| Splitting the worksheet view and freezing panes| 00:02 |
Some spreadsheets are nice and compact
and perfectly happy to fit on your
| | 00:05 |
computer screen, while others are big,
unwieldy affairs and are a lot more
| | 00:09 |
difficult to view.
This lesson shows how to view two
| | 00:13 |
different parts of your worksheet at
once.
| | 00:15 |
So consider a worksheet like this one,
that the spreadsheet keeps track of
| | 00:19 |
Google stock data, such as open, high,
lows, and closing prices over different days.
| | 00:25 |
And if I scroll down, you can see it
covers a few months.
| | 00:29 |
There's a lot of data there.
And it goes off in this direction, too,
| | 00:32 |
if I scroll over to the right.
But doesn't all fit on the screen at once.
| | 00:37 |
Now what if I wanted to compare these
November prices with some of the prices
| | 00:41 |
earlier in the year.
I can't really fit them both on the
| | 00:45 |
screen at once.
But what I can do is I can split the
| | 00:48 |
screen, and I do that by going up here
and you see this little button above the
| | 00:52 |
scroll bar arrow.
And if I drag that down it splits my
| | 00:56 |
screen and now my screen is in two parts
You have a scroll bar for the upper part
| | 01:00 |
and a scroll bar for the bottom part.
So I can go down here and look at my July
| | 01:06 |
and June data and compare it to my
September data.
| | 01:09 |
And that can be pretty handy when you
have a worksheet that goes on forever.
| | 01:13 |
You can split your screen vertically too,
just find the similar little button
| | 01:17 |
that's next to your scroll bar arrow and
drag it over this way and you can split
| | 01:21 |
your screen vertically.
You have scroll bars for each section
| | 01:26 |
down on the bottom here.
And you can look at all different parts
| | 01:29 |
of your worksheet at once.
When you're through with that view, you
| | 01:33 |
can go over here and you can drag these
bars back to their little home and you're
| | 01:37 |
back looking at a single view.
Now there's another way that you can
| | 01:42 |
reset it too.
If I go to View, then I'll see that the
| | 01:45 |
Split Screen button is highlighted there.
All I have to do to turn it off is click
| | 01:51 |
it, the highlight goes away, and so does
the bar that splits the screen.
| | 01:55 |
Now there's another command that's
related to this that's really handy and
| | 01:59 |
it's called Freeze Panes.
Often you'll want to freeze that first
| | 02:04 |
row in your spreadsheet, so that these
headings appear here even though you
| | 02:07 |
scroll down to look at other data.
The way to do that is, again, you go to
| | 02:13 |
the View tab, and you go over here to
Freeze Panes, and choose Freeze Top Row.
| | 02:19 |
So now we see a little line below the top
row, and when I scroll, that top row
| | 02:23 |
remains that you can see that row 1 still
there even though we're down to row 32
| | 02:27 |
over here.
So that's really handy sometimes you'll
| | 02:32 |
want to do exactly the same thing with this
first column over here.
| | 02:37 |
So the thing to do is go View > Freeze
Panes > Freeze First Column and now we
| | 02:40 |
can scroll on this direction.
And that first column stays there but we
| | 02:47 |
can look at the rest of the data.
To undo it I just go back here and click
| | 02:53 |
Unfreeze Panes, and we're back to the way
we were.
| | 02:56 |
Now, one last thing, sometimes you'll
want to see this top row and this first
| | 03:01 |
column at the same time, or maybe some
other combination.
| | 03:07 |
The way to do that is just to choose a
cell that's right at the corner where you
| | 03:11 |
want to freeze the panes.
Go up here, and just choose Freeze Panes,
| | 03:15 |
and now it splits the screen in a couple
of directions, so we can see the top row.
| | 03:22 |
And we can see that first column.
And they're displayed all the time.
| | 03:25 |
So in this lesson, you learned how to use
Excel's Split Screen feature to view two
| | 03:31 |
parts of a worksheet at once.
You also explored a related feature
| | 03:36 |
called Freeze Panes which is great for
keeping specific rows and columns in view.
| | 03:43 |
| | Collapse this transcript |
| Working with two workbooks and syncing the view| 00:02 |
You can view two or more workbooks at
once in Excel, and there are some special
| | 00:05 |
commands in the view ribbon, that make
the process easier.
| | 00:09 |
This lesson examines some of those
commands.
| | 00:13 |
Right now I'm looking a workbook, has a
single worksheet in it, and it keeps
| | 00:17 |
track of Google prices and other data.
It keeps track of that by date, and the
| | 00:23 |
dates are shown over here.
If I go up here to View, and then Switch
| | 00:27 |
Windows, you can see I have another
workbook called Apple Prices, and it
| | 00:32 |
looks quite similar to that.
The dates are the same over here, the
| | 00:37 |
symbol's different.
This is the Apple symbol for stocks, and
| | 00:40 |
you can see these are both fairly long
worksheets.
| | 00:44 |
So I can jump back and forth between them
using the Switch Windows command here,
| | 00:48 |
but what if want to see them both on the
screen at the same time?
| | 00:52 |
Then the thing to do is to go over here
to Arrange All.
| | 00:56 |
When I click that, this little panel
appears and I can choose to arrange it
| | 00:59 |
horizontally, vertically or to cascade
the windows.
| | 01:03 |
So I'll just leave it horizontal and then
we see this view.
| | 01:07 |
We have both of the worksheets.
This is the Apple prices, it's in its own window.
| | 01:12 |
And here are Google prices down here
that's the file name for the workbook there.
| | 01:17 |
And it's down here.
So now I can jump back and forth between
| | 01:21 |
the two, and look at them independently.
However, it might be handy, to compare
| | 01:26 |
the same dates here, with the ones down
here.
| | 01:31 |
And to do that, I can use the View Side
by Side command.
| | 01:35 |
So I'm going to turn that on, and then I'm
going to turn on this synchronous scrolling option.
| | 01:41 |
What that does is, it makes these two
workbooks scroll in unison.
| | 01:47 |
So, if you look at the dates over here
you can see they're showing similar dates
| | 01:50 |
right now.
If I scroll down they stay in lock step.
| | 01:56 |
I'm still showing the same dates and I
can easily compare one of these to the other.
| | 01:59 |
I can turn synchronous scrolling off.
It's highlighted when it's turned on.
| | 02:04 |
So now it's off.
And then I can scroll these workbooks independently.
| | 02:09 |
I can lock in different row numbers, so
if I leave this set to 22, have this set
| | 02:13 |
to 11, if I turn on synchronous
scrolling, it uses those as my starting
| | 02:17 |
point for the scrolling, and it keeps
these showing the same portion of my worksheet.
| | 02:26 |
In this lesson you saw that you can have
two workbooks open at once, and that you
| | 02:29 |
can view those workbooks in separate
windows in the Excel workspace.
| | 02:35 |
You can arrange the windows horizontally
or vertically in the workspace, and with
| | 02:39 |
the View Side by Side command, you can
turn on synchronous scrolling which makes
| | 02:43 |
it easy to examine related data in both
worksheets.
| | 02:48 |
| | Collapse this transcript |
|
|
4. Creating More Complex FormulasWriting your own formulas| 00:02 |
Formulas are at the heart of Excel, and
there are plenty of spreadsheets that do
| | 00:06 |
nothing but total columns of numbers, but
when you need more horsepower, Excel is
| | 00:10 |
up to the task.
This lesson covers the fundamental
| | 00:14 |
arithmetic functions, addition,
subtraction, multiplication, and division.
| | 00:19 |
So let's take a look at a spreadsheet
like this one.
| | 00:22 |
It's a garden shopping list.
So, what we're doing here is, we're
| | 00:25 |
figuring out what we need to buy at the
garden store, the quantities, and what
| | 00:28 |
it's going to cost us and we want to figure
out the subtotals, the tax, and the total.
| | 00:34 |
So, for subtotal, this is obviously a job
for multiplication.
| | 00:40 |
In Excel, the multiplication operator is
the asterisk symbol.
| | 00:43 |
So to write our formula, we go up to the
formula bar here and we click equal, that
| | 00:47 |
tells Excel that we're going go write a
formula, and then we want to reference
| | 00:52 |
these two cells.
So unit cost would be C4, we can type
| | 00:57 |
that in, and then we want to multiply so
we use the asterisk and then we want to
| | 01:02 |
multiply that by B4, which is the
quantity.
| | 01:07 |
That's our entire formula.
We can click enter if we like it.
| | 01:12 |
And it looks like it's operating
correctly.
| | 01:14 |
Now one note about typing in your
formula, is it's usually better just to
| | 01:19 |
point and click because you are less
likely to make a mistake and it's just easier.
| | 01:26 |
So I'm going to do this again, just clicking
to reference my cell so the fast way to
| | 01:31 |
do this is to click on that cell, hit the
asterisk, click on that cell, click the
| | 01:36 |
check mark.
And everything looks fine.
| | 01:41 |
Now the easy way to copy this to these
other cells, well we need similar
| | 01:45 |
formulas in those cells, is just to use
the Fill tool.
| | 01:49 |
When you hold your cursor over the lower
right corner of a cell, you can just
| | 01:52 |
drag, and that copies, that's the same as
doing a copy and paste command in all
| | 01:56 |
those cells.
So you can see we have different totals here.
| | 02:01 |
And here's another little trick that you
can use when you're working with formulas.
| | 02:06 |
You want to see the underlying formula some
place, go up here to the Formula tab,
| | 02:10 |
then over here in the Formula Auditing
group, click this button that says Show Formulas.
| | 02:18 |
Now we see the formulas that are
underlying the values.
| | 02:20 |
So we can see here, these are just
values, but here, these are all formulas,
| | 02:24 |
and you can clearly see that when we use
the Fill tool, it altered the formula
| | 02:28 |
just a little bit so it referred to each
row that we want it to refer to.
| | 02:34 |
You can see what cells are affected by a
formula, just by clicking on that formula.
| | 02:40 |
And then you see these outlines around
the cells.
| | 02:43 |
You can turn off Show Formulas and we're
back to where we were.
| | 02:47 |
So now we want to calculate the tax.
Let's say our tax rate is 9%.
| | 02:53 |
We will go up here.
Equals to say we're using a formula.
| | 02:58 |
We'll use our sub total and we'll
multiply it by 0.09 and that gives us our
| | 03:03 |
tax rate.
So you can see here, that your formulas
| | 03:06 |
don't have to just reference cells, you
can actually use real numbers in your
| | 03:10 |
formulas too.
Click check and that looks right.
| | 03:16 |
And then use the fill tool to copy our
text down and now we have our taxes.
| | 03:21 |
This last one's easy, we're just adding
the tax and the subtotal.
| | 03:26 |
By now this should be second nature.
Equal sign, point to the subtotal, hit
| | 03:32 |
the plus key.
Now I like to use the plus key over on my
| | 03:36 |
number pad because, if you use the plus
key above the letters on your keyboard
| | 03:39 |
you have to hold the shift key down.
'Kay, now we're going to add the tax.
| | 03:46 |
And that looks okay, so we'll click
Enter.
| | 03:48 |
And we have our total and we can drag
down here to see total for all the items.
| | 03:55 |
And here at the bottom, we already have
some functions that are adding up the columns.
| | 04:00 |
Okay, that takes care of addition and
multiplication.
| | 04:06 |
Now we want to take a look at division and
subtraction.
| | 04:10 |
So for division, let's figure out what
percentage the lawn mower is of our total
| | 04:14 |
value down here.
So that would be a division job.
| | 04:18 |
So I click this cell, I'm going to press
equals.
| | 04:21 |
And then we'll take this number and its
the Forward Slash key that we use to, for division.
| | 04:28 |
Just as if it were a fraction.
And we'll click on the total down here.
| | 04:35 |
And that's our formula, so I'll click
Enter.
| | 04:37 |
We see our formula as a percentage.
Going to go to the Home tab and use our
| | 04:43 |
number formatting here, that will show
our percentage with the percentage mark
| | 04:48 |
on it.
So that just leaves subtraction.
| | 04:51 |
Suppose we want to remove one of these
rain barrels from our total and we're
| | 04:56 |
going to get a refund on it.
So what I'm going to do is I'm just going to
| | 05:02 |
select this whole row.
If you click here, you're selecting
| | 05:07 |
everything on that row and that works
fine.
| | 05:09 |
I'm going to copy that.
I'm going to move down here and select
| | 05:13 |
another row and I'm going to paste that
in.
| | 05:16 |
So we just copied this line that shows us
what three rain barrels cost and I'm
| | 05:21 |
going to change the three to one, and
we'll figure out what one rain barrel costs.
| | 05:28 |
Okay, now for the subtraction, we will
subtract the cost of one rain barrel from
| | 05:34 |
the total.
I'll do that down here, in this cell.
| | 05:38 |
So again, we go to the formula bar, press
equals, show our total and then subtract
| | 05:44 |
the cost of my rain barrel.
And we've got our new calculation for our total.
| | 05:53 |
So in this lesson, you saw that can
display the formulas in a worksheet by
| | 05:56 |
going to the formulas tab.
The formula auditing group and then
| | 06:00 |
clicking show formulas.
And you also learned to use the basic
| | 06:03 |
arithmetic operators for addition,
subtraction, multiplication and division.
| | 06:08 |
| | Collapse this transcript |
| Understanding relative and absolute references| 00:02 |
In several places throughout this course
you've seen that when you copy a formula,
| | 00:06 |
Excel uses a relative reference and most
of the time that's just great.
| | 00:11 |
This lesson deals with the other times
when you want an absolute reference to a cell.
| | 00:17 |
So, let's consider this shopping list
that calculates several items.
| | 00:23 |
Takes the quantity and unit cost and
figures out the subtotal, the tax and the total.
| | 00:28 |
Now, over in this column we took one
item, we figured out what percentage this
| | 00:32 |
value was of the total cost, down here.
And to do that we used a division, so we
| | 00:39 |
divided this number by this number.
We got our value.
| | 00:44 |
Now suppose we want to figure out the
percentage for the rest of the items in
| | 00:47 |
this list.
The natural thing to do would be to copy,
| | 00:53 |
and then paste, our formula.
Well, it's not working.
| | 00:59 |
We, we're getting an error message, an
this pound sign, an these words, an the
| | 01:02 |
exclamation point, that's Excels way of
telling you that we got an error here.
| | 01:07 |
And this one in particular is a divide by
zero error.
| | 01:11 |
So to figure out what's going wrong.
The best thing to do is to choose a cell
| | 01:15 |
and then click in your formula.
And you get the boxes that show you what
| | 01:19 |
cells are being used in your formula and
it's easy to see here.
| | 01:24 |
That instead of using the total that we
want to use in our division.
| | 01:29 |
It's, referencing the cell below and
that's because the formula is using
| | 01:34 |
relative references.
This formula here is using the right references.
| | 01:41 |
But when we move the formula down one
cell, it tries to move the total down one
| | 01:44 |
cell too, and that just doesn't work.
So let's go back and delete this.
| | 01:52 |
There is a way to tell Excel that you
want to use an absolute reference.
| | 01:58 |
And the operator for that is the dollar
sign.
| | 02:02 |
So what we need to do is go up to our
formula here, and put a dollar sign in
| | 02:06 |
front of the column reference.
We also need to put a dollar sign in
| | 02:11 |
front of the row reference.
So now when we copy this formula to any
| | 02:16 |
of these other cells, this part of it
will always refer to, F14, and that's
| | 02:20 |
exactly what we want.
So I'm going to save that, and now, when I
| | 02:26 |
copy, and move down a row and paste, I
get a new value in here.
| | 02:33 |
And that looks like it's working out just
fine, so if I use my fill tool to fill it
| | 02:39 |
in the rest of these columns.
And I can use the fill tool and go up
| | 02:45 |
two, in fact I can go down here to make
sure everything's right.
| | 02:51 |
So now we use an absolute reference in
all of these formulas.
| | 02:55 |
And to double check we can see, let's go
to formulas and here in the auditing
| | 03:00 |
click show formulas.
We go over here.
| | 03:05 |
We can see our absolute reference was
copied all the way down the line.
| | 03:09 |
There are a number of places that you
might want to use this.
| | 03:13 |
Let's just look at another example.
Suppose we wanted to use it for our tax,
| | 03:18 |
and here we used a literal number for our
tax.
| | 03:23 |
But suppose we want to use a number that we
could easily change.
| | 03:27 |
Suppose we have our tax value up here.
And we want to reference that cell.
| | 03:31 |
I'm going to format it as a percentage here.
Suppose we want to refer to that value in
| | 03:39 |
these calculations.
So in this case, all I have to do is
| | 03:44 |
remove that part of our formula, and
reference this cell.
| | 03:50 |
Now I'm going to use my dollar signs in
front of both the column and the row
| | 03:55 |
value by click Enter.
Now we've got the formula with the
| | 04:00 |
absolute reference in there.
I can copy it to the rest of these.
| | 04:05 |
It shouldn't change anything except when
we look at the formulas, we can see we've
| | 04:10 |
got an absolute reference in there.
If we wanted to change our tax rate at
| | 04:15 |
some point, we could.
Lower it to seven percent, and we see all
| | 04:21 |
our values change automatically.
So in this lesson, you focused on the
| | 04:27 |
differences between relative and absolute
references in your worksheet.
| | 04:31 |
And you learned that the dollar sign
operator is used to create absolute references.
| | 04:36 |
| | Collapse this transcript |
| Using functions like ROUNDUP, AVERAGE, and IFERROR| 00:02 |
You don't have to create every formula
from scratch.
| | 00:05 |
Go to the Formulas tab, look at the
Function library, and you can see that
| | 00:09 |
Excel comes with a bunch of built-in
functions.
| | 00:13 |
They're grouped into categories like
Financial, Logical and Text.
| | 00:17 |
Now, if you click on one of those book
icons, you can see the functions inside.
| | 00:22 |
This lesson shows you how to use
functions in Excel.
| | 00:27 |
This is a worksheet that might be used in
a bookstore, for example, where you have
| | 00:30 |
different titles, different books for
sale.
| | 00:33 |
You keep track of the orders per month,
and then the cost, what it costs to
| | 00:36 |
purchase each one of these titles.
And the sales unit price, and it goes on
| | 00:42 |
to calculate the gross sales and the net
sales for each of the titles.
| | 00:48 |
This is the kind of worksheet where you
may want to use functions.
| | 00:51 |
For example if you look at this column of
orders per month it might be interesting
| | 00:55 |
to know what the average number of sales
is per title.
| | 00:59 |
So to do that we start off with an equal
sign, like we'd start any formula.
| | 01:04 |
And then, because average is such a
common function, I'm just going to guess
| | 01:08 |
that there's an average in Excel.
I'm just going to start typing the word average.
| | 01:14 |
And here we get, several different
functions.
| | 01:18 |
You can tell they're functions because
this symbol, this fx symbol here.
| | 01:21 |
That's the symbol for function in Excel
and you can see it in a few different places.
| | 01:25 |
You can see it there and you can see it
there in this insert function button.
| | 01:29 |
So to use the average function, what I
need to do is just double-click on it and
| | 01:33 |
Excel starts my formula.
So it has my function name in here,
| | 01:38 |
Average, and then this bold, this is a
prompt here.
| | 01:42 |
This, it's saying that it wants a number
next.
| | 01:45 |
So when a function prompt says that it
wants a number it means one of three things.
| | 01:51 |
It wants an actual number or it wants a
cell referencing a number or it wants a
| | 01:55 |
range of cells representing numbers.
So in this case we're going to use a
| | 02:00 |
range of cells.
I'm just going to click there and drag
| | 02:04 |
down here, select all these numbers and
then I need to close the function with
| | 02:08 |
the closing parentheses.
You can see the range that we're
| | 02:13 |
operating on here and I'm going to click
enter and we get my number.
| | 02:18 |
And when you look at that you may wonder,
when it's doing its division is it
| | 02:21 |
counting this empty cell in here when
it's doing the average?
| | 02:25 |
We can double check on that and I'll show
you how.
| | 02:28 |
Write another formula, we're also going
to use average here.
| | 02:34 |
Double-click on average to enter that.
Now if you look at this prompt here we
| | 02:39 |
see that it's prompting us for a number,
has a comma and then it has another
| | 02:42 |
number in brackets.
When you see something in the prompt
| | 02:47 |
that's in brackets it means that is
optional.
| | 02:51 |
So, what it says here is that I can enter
individual numbers into this average
| | 02:54 |
function, and the function'll work just
fine.
| | 02:58 |
So what I'm going to do is I'm going to select
that cell, press a comma to separate it.
| | 03:04 |
I'm going to select another cell, comma,
cell, comma, cell, comma, then I'm going to
| | 03:08 |
skip the blank space.
Then come down here and enter the rest of
| | 03:12 |
the numbers.
Now that's all my numbers.
| | 03:17 |
I'm going to close with the parenthesis.
You can see I've got all my numbers
| | 03:22 |
selected and skipping that space.
If I hit the check mark, we come up with
| | 03:26 |
exactly the same number.
What this tells us is that when we do an
| | 03:30 |
average, Excel is smart enough to skip
cells if they don't have any value in
| | 03:34 |
them at all.
Now, I'd really like to see this as a
| | 03:39 |
single number without any decimals.
So to do that we can look at another function.
| | 03:45 |
Let's look at Rounding.
We can operate on the same principle.
| | 03:48 |
Let's put the equals sign in there and
start typing round to see what comes up.
| | 03:53 |
So we have three rounding functions here,
round up, round down and then just plain round.
| | 03:58 |
Let's use that one and then I can click
on my number.
| | 04:03 |
After I put the comma in I get a bold
highlight on these words.
| | 04:08 |
The number of digits.
It wants to know the number of digits
| | 04:10 |
that I want to round to.
And those are not in brackets.
| | 04:13 |
So that's something that needs to be in
this formula.
| | 04:16 |
Put in zero and then close the function
with a parenthesis.
| | 04:23 |
Click Check.
And you can see I have my number and it's
| | 04:25 |
rounded properly.
Now another thing that you can do with
| | 04:29 |
functions is that you can put one
function inside of another so you can
| | 04:33 |
nest them, and if we wanted to get this
number in just a single formula what we'd
| | 04:37 |
want to do is we get our average and then
we'd wrapped the rounding function around
| | 04:42 |
the average, so the average is nested
inside of that rounding function.
| | 04:50 |
And here's how that would look.
Equals.
| | 04:54 |
From the start off with the rounding,
I'll double-click round.
| | 04:58 |
Now it's asking me for my number.
To get my number I want to do the
| | 05:01 |
average, so I'm going to start my
average, function when I see the prompt here.
| | 05:07 |
So I double-click on that.
Now I'm going to give it the range.
| | 05:11 |
Select the range of numbers.
I'll close that function with the closing
| | 05:16 |
parentheses, and notice when I do it,
Excel has a nice way of showing you how
| | 05:20 |
the parentheses match.
When I close this, briefly, the opening
| | 05:27 |
parentheses will be bold.
And not only that, they're color coded,
| | 05:31 |
both of these are green letting me know
that's the beginning and end.
| | 05:36 |
And why is this important?
When you get more complicated formulas,
| | 05:40 |
it's very easy to get confused in the
number of parentheses that you need for
| | 05:43 |
your formulas.
So I'm going to put my comma in.
| | 05:48 |
Give it zero digits, round to zero
digits, and then my closing parentheses.
| | 05:52 |
And there we are.
We've got single formula, it gets the
| | 05:57 |
average of these numbers and then rounds
it down to a whole number.
| | 06:01 |
So let's, consider some of these other,
formulas that we have over here.
| | 06:05 |
Let's take a look at one of the text
formulas.
| | 06:08 |
Suppose we want to, use a text formula on
that.
| | 06:11 |
You might wonder what a text formula is.
We'll let's take a look.
| | 06:13 |
You can see, concatenate, and dollar, and
you have upper and lower.
| | 06:22 |
And these two formulas will convert
strings of text to either upper or lower case.
| | 06:27 |
So let's see how upper case works here.
We're going to choose that formula and I'm
| | 06:32 |
prompted with this window.
And it's asking me what do I want to put
| | 06:37 |
inside of the function.
So I'm going to put the authors name inside
| | 06:44 |
of that function.
(audio playing) And I click OK.
| | 06:49 |
And you can see it takes this string of
characters and then it forces it into
| | 06:53 |
upper case.
So that's how that, particular text
| | 06:57 |
function works.
Let's take one more look at a function.
| | 07:01 |
Here's a formula that's kind of
interesting because it deals with the
| | 07:05 |
calculation precedence.
And you might be like me, I don't always
| | 07:10 |
remember which comes first, subtraction
or addition or multiplication when you do
| | 07:14 |
a math formula.
So just to be safe, this is what I do: I
| | 07:19 |
always put the things that I want
calculated first inside of parentheses.
| | 07:24 |
That way, one, I don't make a mistake cuz
I forgot the law, and the other thing is,
| | 07:28 |
it's actually a little bit more clear to
anybody that comes along and reads the
| | 07:31 |
formula after me.
What we want to do is we're trying to
| | 07:36 |
figure out the net sales.
So this first formula takes the number of
| | 07:41 |
orders per month and it multiplies that
by the unit sale price and that gives us
| | 07:46 |
a gross sales price.
Then we want to subtract our cost of
| | 07:51 |
goods from that, so we calculate our cost
of goods in this formula.
| | 07:55 |
We take the same unit sales, and then we
multiply that by D4, which is our cost of
| | 08:00 |
goods figure.
So we get those two numbers, and then we
| | 08:06 |
subtract our cost of goods over here from
the gross sales.
| | 08:12 |
So that's all fine, but what we end up
with is a number that I'd like to round,
| | 08:16 |
so what I'm going to do is wrap a round,
one of those round functions around this
| | 08:20 |
number, and I can do that just by
editing.
| | 08:25 |
I can do round in here.
I'm going to round down just to make sure
| | 08:30 |
I don't overestimate our income.
And I can come over here and enter my
| | 08:36 |
comma, and I'll want two decimal points
here.
| | 08:40 |
I'm going to close my parentheses, and when
I click Enter I get an error.
| | 08:49 |
So, this error is telling me that I have
too few arguments.
| | 08:52 |
Arguments are the items that are
seperated by commas in a function.
| | 08:57 |
So, here's one argument, the number of
decimals that I want and here's the other
| | 09:00 |
argument and that's this number.
What I can see here is I've got a green
| | 09:05 |
closing parenthesis here, but I don't
have a matching one over here.
| | 09:09 |
The problem with this formula is there
are actually too few parentheses.
| | 09:14 |
So I'm going to go over there, type in
another parenthesis, and now you can see
| | 09:18 |
that I have the right number of closing
and opening parentheses.
| | 09:23 |
And if I click Enter, I get my number.
So to finish this off, I'll just use the
| | 09:28 |
Fill tool, drag it down here to copy my
formulas to the rest of the worksheet here.
| | 09:36 |
In this lesson, you learned where to find
Excel functions and how to use them.
| | 09:40 |
You were introduced to the function
builder that helps you while you're
| | 09:44 |
creating a function.
And you saw how to handle an error when
| | 09:47 |
you're writing a function.
| | 09:49 |
| | Collapse this transcript |
| Formatting dates and times| 00:02 |
Excel is pretty sophisticated when it
comes to working with dates and times.
| | 00:06 |
In most cases, the key is in the number
formatting.
| | 00:09 |
This lesson examines the date and time
number formats and provides tips and
| | 00:13 |
tricks for working with the clock.
So, if you enter something that looks
| | 00:17 |
like a date or time, Excel automatically
formats it to be a date or a time.
| | 00:23 |
Let me show you what I mean.
Here's a cell.
| | 00:25 |
Right now, it has the general formatting.
This is the Home tab and the Number group
| | 00:30 |
where you would choose different
formatting, like Percentage or Dollar.
| | 00:35 |
Right now it's set to General, and that's
the one that is sort of the default when
| | 00:40 |
you first start a worksheet.
So if I put in a date, let's say March
| | 00:47 |
14th, 2013.
A little bit of magic takes place.
| | 00:52 |
You can see that Excel automatically
formats this as a date.
| | 00:56 |
It changes the number formatting to a
date.
| | 00:59 |
It even changes the number a little bit.
It's showing it as 2013 instead of just
| | 01:03 |
the 13 that I typed in.
And that same thing happens when you put
| | 01:09 |
in what appears to be a time.
So if I do 10:45 and 0 seconds, Excel
| | 01:14 |
changed that from general formatting to
custom.
| | 01:18 |
And you can see here that the custom
formatting is its formatting and as a
| | 01:22 |
time because we have the a.m.
on the end of it.
| | 01:27 |
You can even put a date and time in the
same cell, and you do that by putting a
| | 01:30 |
space between them.
So here we can do, put in our same date,
| | 01:37 |
put a space in, and then the same time.
And if I click Enter, there it shows it
| | 01:46 |
as a date and a time.
You can see the date and time up here
| | 01:51 |
where it's changed them both slightly.
And it uses another custom formatting, to
| | 01:56 |
display it in that manner.
So, behind the scenes something
| | 02:00 |
interesting is going on.
Excel treats dates and times as a number.
| | 02:06 |
And let me show you what I mean.
I'll take this from the Date formatting,
| | 02:09 |
turn it back to that General formatting.
And you can see that, to Excel, that date
| | 02:15 |
is actually just a number.
And if you do the same thing with time,
| | 02:20 |
we'll go from the Custom formatting back
to General, you can see that the time is
| | 02:23 |
just a number too.
And it happens to be on the right side of
| | 02:28 |
a decimal point.
Now, if we look where we put them both in
| | 02:32 |
the same cell, you can see to Excel, it's
still just a single number.
| | 02:38 |
The date happens to be on the left side
of the decimal point, while the time,
| | 02:42 |
rounded a little bit, is on the right
side of the decimal point.
| | 02:47 |
So the thing to take away from this is
that Excel has a number for every moment
| | 02:51 |
in time, and it's displayed as a single
number with a decimal point, and it
| | 02:54 |
calculates the date on the left side of
the decimal and the time on the right
| | 02:58 |
side of the decimal.
And you show the number in your
| | 03:04 |
spreadsheet the way you want to show it
through formatting.
| | 03:08 |
So for example, if we want to change this
back to a date, what we want to do is go
| | 03:12 |
up here and then choose one of these
dates.
| | 03:15 |
We can choose Short Date or Long Date.
Long date is kind of clever.
| | 03:20 |
It shows the day of the week as well.
And the same thing with the time.
| | 03:24 |
We can go up here, choose a time, and it
puts it back in the Time format.
| | 03:30 |
And if we want to do something a little
bit different, we can go in here and go
| | 03:34 |
down here to the More Number Formats, and
this opens up the Format Cells, for
| | 03:38 |
Number Formatting.
We can go down here and choose either the
| | 03:43 |
Date or the Time formatting.
I'll choose the Date.
| | 03:46 |
It shows us a list.
We can scroll through the list until we
| | 03:49 |
see something that works for the project
we're working on.
| | 03:52 |
I'll choose that one.
Click OK and it formats it as a date and
| | 03:56 |
a time.
And it displays it as a date and a time
| | 03:59 |
in there.
So the thing to keep in mind is that
| | 04:02 |
dates and times are single numbers.
And you can get the information to
| | 04:08 |
display the way you want to through the
Number formatting.
| | 04:12 |
Before we go one, one last trick.
There's a, there's a shortcut key to
| | 04:16 |
enter the current date and the current
time.
| | 04:19 |
So for the current date, I hold down the
Ctrl key and I press semicolon, and it
| | 04:24 |
enters today's date.
And for time it's, Ctrl key, and the
| | 04:29 |
colon, which means you have to hold down
the, Ctrl key and the Shift key and press
| | 04:33 |
the same key on the keyboard, to get a
colon, and it enters, the current time.
| | 04:40 |
If you want to enter both the date adn time
in a single cell as we did over here, all
| | 04:43 |
you have to do is put a space between
them.
| | 04:47 |
So, Ctrl+semicolon for the date.
Space.
| | 04:51 |
And Ctrl+colon for the time.
And there we have both in a single cell.
| | 04:56 |
And as you can imagine, if you're working
on something like a time sheet or you're
| | 04:59 |
trying to keep track of how much time
you're working on a project, this can be
| | 05:02 |
really handy.
All you have to do is use the shortcut
| | 05:06 |
keys to enter the time that you begin.
And then, when you're through with the
| | 05:11 |
project, use the shortcut keys again and
you'll get the ending time.
| | 05:15 |
And then do a little bit of math and you
can figure out how long you've worked on
| | 05:18 |
your project.
So this lesson showed that Excel
| | 05:21 |
automatically applies a number format
when you enter a date or time.
| | 05:25 |
Excel uses a single number to express a
moment in time, and date values are to
| | 05:29 |
the left of the decimal point, where time
values are to the right.
| | 05:35 |
The shortcut key to enter the current
date is Ctrl+semicolon, and the shortcut
| | 05:39 |
key to enter the current time is
Ctrl+colon.
| | 05:42 |
| | Collapse this transcript |
| Using date and time functions| 00:02 |
You can use functions to extract
information from a date or time.
| | 00:06 |
And this lesson looks at some of those
date and time functions, that are built
| | 00:09 |
into Excel.
And it shows how to use dates and times
| | 00:12 |
in your formulas.
Excel stores moments in time as serial
| | 00:18 |
numbers, and it displays them as dates
and times.
| | 00:23 |
So right now, this number is displayed as
a date and time.
| | 00:25 |
But if we go up here, and change it to
the general format, we can see the serial
| | 00:29 |
number that's behind the date and time.
The date is to the left of the decimal
| | 00:34 |
point, and the time is to the right of
the decimal point.
| | 00:37 |
Now, those numbers are pretty
incomprehensible to most people, so it's
| | 00:41 |
much better to keep your numbers
formatted as a date or a time.
| | 00:45 |
I'm going to undo that, to get the
formatting back.
| | 00:49 |
Now when you want to extract a portion of
this, suppose we want just the year or
| | 00:53 |
the month or the day?
That's a job for a function.
| | 00:58 |
So we go over here to the Formula tab.
Let me look at the date and time functions?
| | 01:03 |
You can see that, Excel offers a lot of
different date and time functions.
| | 01:09 |
For example, it can figure out how many
workdays there are in a certain time period.
| | 01:13 |
It can extract the hour or minute from a
time, or the month or the day.
| | 01:18 |
And those are the ones that we're going
to look at right now.
| | 01:22 |
So, let's start off with the year.
We create a formula with the equals sign.
| | 01:26 |
Going to start typing year and we see our
year function in there, so I'm going to
| | 01:30 |
double-click on it.
Then I'll reference our date and time,
| | 01:35 |
which we know as a serial number,
underneath the formatting.
| | 01:39 |
And close the parentheses, and we get the
year extracted from that date.
| | 01:46 |
Same thing for the month.
Reference the cell.
| | 01:52 |
Close the function, we get the month,
March, midday.
| | 01:58 |
So each of these works just as you'd
imagine.
| | 02:00 |
And what's more, the functions are easy
to remember.
| | 02:05 |
(audio playing)
| | 02:09 |
Double-click, to enter it.
So you can see how easy it is, to extract
| | 02:25 |
any portion of that date, serial number
that we want.
| | 02:29 |
You might want to use these in
calculations somehow.
| | 02:32 |
So let's look at a practical example.
Go over here to a timesheet.
| | 02:36 |
This is timesheet for a company, and it
has setup, so that you enter a start time
| | 02:41 |
and an end time.
What you're doing, is you're trying to
| | 02:46 |
calculate how much time was spent on a
particular project.
| | 02:49 |
So when we calculate the minutes over
here, you can see that, it simply
| | 02:53 |
subtracts the start time from the end
time, to get the difference between those
| | 02:57 |
two times, and we get a number.
Right now, this is formatted as a time.
| | 03:06 |
But if we change this back to the general
format, we can see that it's one of those
| | 03:10 |
time serial numbers.
There's no date, because the date's
| | 03:14 |
actually the same, so there's no
difference in the date.
| | 03:18 |
There is a difference in the hours,
though, and that's what's showing up on
| | 03:21 |
the right side of the decimal point.
Undo that.
| | 03:25 |
Set it back to the time.
Now when we get over to our formula, we
| | 03:28 |
use some of those functions.
We're using the hour function and the
| | 03:33 |
minute function to extract the hours and
the minutes from this number right here.
| | 03:38 |
And then this B11 reference here, is the
hourly rate that we're applying to the
| | 03:42 |
hours spent.
So it's calculating the fee.
| | 03:46 |
Uses the hours and then when it gets to
minutes, it divides that fee by 60,
| | 03:49 |
because there's 60 minutes in an hour.
And that's how this formula is created
| | 03:55 |
for a timesheet.
So this lesson showed how to extract the
| | 03:59 |
year, month and day, from a serial
number.
| | 04:02 |
It also showed how to get the hour,
minute, and second from a time.
| | 04:06 |
And you saw how date/time functions can
be used in formulas.
| | 04:10 |
| | Collapse this transcript |
| Naming cells and ranges| 00:02 |
Which is easier to remember?
C3, or tax rate?
| | 00:05 |
D12, or April?
For most human beings, words are easier
| | 00:08 |
to remember than numbers or codes.
Computers don't seem to care, but to
| | 00:13 |
accommodate the humans, Excel lets you
name cells and ranges of cells.
| | 00:19 |
Then you can use those names in your
formulas, instead of C3 and D12.
| | 00:24 |
So let's look at a practical application.
Here we've got a shopping list with items
| | 00:28 |
and quantities, unit cost.
We figure out the subtotal, then we apply
| | 00:32 |
a tax that's a percentage, and we figure
out the total.
| | 00:35 |
Well, the tax is an ideal thing to use a
name for.
| | 00:40 |
I'll show you why.
Here we're using the actual number 0.09,
| | 00:44 |
we multiply the subtotal by 0.09 to get
our tax.
| | 00:49 |
And we could copy that all the way down,
use 0.09 for each one of these items.
| | 00:55 |
The problem with that is, if the tax rate
changes, then we have to change each and
| | 00:59 |
every single formula.
You'll often run into things like this,
| | 01:02 |
there's a better way to do it and that's
to reference this cell that's up here.
| | 01:06 |
So to do that, we'd remove the number,
and then we'd reference the cell.
| | 01:12 |
And we have to remember to make that an
absolute reference, because it's just a
| | 01:16 |
single cell.
And then we could copy that down our list here.
| | 01:22 |
And that's okay.
If you look at this formula, though, it
| | 01:25 |
really doesn't say anything to you.
You can't, to translate what's going on
| | 01:28 |
here, you have to go back and reference
the cells.
| | 01:31 |
You have to see D4, oh yeah, that's the
subtotal, and E2, okay, that's my tax rate.
| | 01:36 |
And then you understand what's going on.
Another option is to name the cell Tax.
| | 01:42 |
The way that you do that is you go over
here, this is the name box.
| | 01:45 |
So you click in that, and then you just
type in the name that you want to apply.
| | 01:50 |
Wanna call that Tax.
And then in our formula, we can get rid
| | 01:54 |
of this absolute reference, and just use
the word, use the name.
| | 01:58 |
You can see as I start typing, it shows
up on my list here.
| | 02:02 |
I've got a couple of functions here.
But down here, you see the little label.
| | 02:06 |
That's the symbol for a name, and you can
see Tax.
| | 02:09 |
So I can just double-click on that, and
now we have our new formula, and it
| | 02:13 |
references the Tax cell.
And it's an absolute reference, so if I
| | 02:18 |
want to copy it down, it works for all of
my formulas, and I can go in and look at
| | 02:22 |
these formulas, you can see each one of
them uses the tax name in the formula.
| | 02:28 |
And that's really handy, particularly
handy for things like tax rates or any
| | 02:33 |
kind of rate that you apply regularly to
a work sheet.
| | 02:38 |
Let's look at another example.
Here we have household expenses, we've
| | 02:43 |
got months across the top, and we have
the expenses on the left side here.
| | 02:49 |
I'm going to select the whole group of them,
and I'm going to use a command in the
| | 02:54 |
formulas, Defined Names group.
This is where you'd go to manage your names.
| | 03:01 |
You can bring up a dialog box that'll
help you do that.
| | 03:04 |
You can define the name for a specific
thing.
| | 03:06 |
What we're going to use is Create Names
From Selection.
| | 03:09 |
This is a really speedy way to apply
names to the columns and to the rows.
| | 03:14 |
So I'm going to click this.
A little box comes up asking me how I
| | 03:18 |
want to create my names.
Well I want this top row to define the
| | 03:22 |
names of the columns, and I want the left
column to define the names of the rows.
| | 03:27 |
So that's just exactly what I want, I'm
going to click Okay.
| | 03:32 |
And now we have names.
You'll see that those names appear in our
| | 03:36 |
name box.
So, if we want to look at April, I click on that.
| | 03:40 |
And it shows me a range of cells, and
these cells have the name of April.
| | 03:44 |
If I go back to my list, I could choose
Gas, over here, and it shows me the range
| | 03:49 |
of cells that have the name Gas.
Once you do that, here's something that's
| | 03:55 |
kind of interesting.
If I press = to start a formula, and I
| | 03:58 |
could type in April, as I start to type
in April, it shows me, prompts me with
| | 04:02 |
April, and I can put a space in there.
And then, you can see it shows the
| | 04:09 |
selection box around April.
Now, if I type in gas, it shows the
| | 04:14 |
selection box around gas.
And if I click Enter, it shows me the
| | 04:20 |
intersection of April and gas, and I can
use that in a value.
| | 04:26 |
So it gives you a really handy way that
you can look up something, and you can
| | 04:29 |
use that in a formula.
We could also use the whole range in a formula.
| | 04:34 |
So suppose we wanted to find out the
total of our phone bill for entire year.
| | 04:38 |
We could go =, use the sum function.
And then in here, we could use the name,
| | 04:44 |
phone, and you can see it prompts us with
the phone, so I can double-click that,
| | 04:49 |
close our function.
And what it gives us is the sum for that
| | 04:55 |
phone range, and if we look over here, we
can double check it, and there it is, and
| | 04:59 |
the value is correct.
So, in this lesson, you saw how to name a
| | 05:05 |
cell or a range of cells.
Then you saw how you can use the name in
| | 05:09 |
a formula.
The lesson also demonstrated the speedy
| | 05:13 |
Create Names From Selection feature.
| | 05:16 |
| | Collapse this transcript |
| Fixing common errors| 00:02 |
No matter how careful you are, someday
you'll be faced with the worksheet that
| | 00:05 |
looks like this.
Or maybe it'll look like this one.
| | 00:09 |
So what's going on?
It's Excel's gentle way of telling you
| | 00:13 |
that something went wrong.
This lesson shows you some of Excel's
| | 00:16 |
common error messages, and more
importantly you learn how to find a fix.
| | 00:20 |
Fix.
Start off with some simple ones.
| | 00:24 |
These are just a couple of examples.
You can see, this is a simple formula
| | 00:28 |
that divides this cell by this cell.
And this formula below is supposed to do
| | 00:33 |
pretty much the same thing.
And the error here is probably obvious.
| | 00:38 |
But we've got an error message.
And you can tell that it's an error
| | 00:42 |
message by the pound sign and then the
exclamation point and the capital letters.
| | 00:46 |
That's a signal from Excel that this is
an error.
| | 00:49 |
This is the infamous divide by zero
error, and it's pretty clear what the
| | 00:54 |
issue is.
But if we needed help, we could go over
| | 00:57 |
here to this exclamation point and point
to it, and then we get a tool tip that
| | 01:01 |
tells us the formula or function used is
dividing by zero or by empty cells.
| | 01:07 |
Now this kind of error occurs a lot if
you're deleting cells or moving things
| | 01:10 |
around on your spreadsheet.
You may end up referring to a cell that's
| | 01:15 |
empty, and you're dividing by that cell
and the result is this divide by zero error.
| | 01:23 |
So the solution is really to change our
formula or to put a whole number in here.
| | 01:28 |
Then we'll get a calculation and
everything is working just fine.
| | 01:31 |
Let's look at another example.
Here we've got another sum function.
| | 01:37 |
We're getting the sum of the values
between B6 and C6, so just these two cells.
| | 01:42 |
Down here we're doing the same thing.
The difference is instead of number
| | 01:46 |
values in here we actually have words in
here.
| | 01:49 |
Now the sum function is smart enough to
know if there are words in the range that
| | 01:53 |
you're trying to sum, those words might
be labels or something.
| | 01:58 |
There's probably a reason there.
So it just gives them a value of zero,
| | 02:00 |
and that's why we end up with a zero
here.
| | 02:03 |
If we changed one of these to a number,
we'd get 0 plus 12 equals 12.
| | 02:09 |
So that works fine, and that works fine
most of the time.
| | 02:12 |
Now, what you can't do is you can't do
something like this.
| | 02:15 |
Say I want a sum from George to Smith.
Cuz it doesn't really know what George
| | 02:20 |
and Smith are.
If those names aren't defined names up
| | 02:24 |
here, it doesn't understand those as
values or as cell references.
| | 02:29 |
So we get this name error here.
Likewise, here are two cells, and we're
| | 02:33 |
just trying to add them together.
So we're trying to add George and Smith together.
| | 02:38 |
And Excel doesn't know how to do that.
So it's giving us a value error.
| | 02:43 |
If we point to it over here, we can see
that it says a value used in the formula
| | 02:46 |
is the wrong data type.
Well, that's exactly right.
| | 02:49 |
We're trying to add text to each other.
There are some functions that you can use
| | 02:54 |
with text to do something like that but
you can't just use the addition operator
| | 02:59 |
to do that.
And down here we see the same thing.
| | 03:03 |
We're trying to add Fred plus Smith and
we get a name error.
| | 03:06 |
Again, it thinks these are names of cells
or ranges of cells.
| | 03:11 |
Come over here and look another issue.
This little worksheet looks like it's
| | 03:15 |
going fine except when you look down here
you see we've got a value, value, and
| | 03:19 |
then this should be a total but it's
coming up zero.
| | 03:23 |
Now these are adding a range of cells and
everything's fine.
| | 03:28 |
When you look at this one it's also
adding a range of cells.
| | 03:33 |
But it's going from D3, down to D7, and
then trying to put that value into D7.
| | 03:39 |
That's what's known as a circular error.
And circular errors are where you're
| | 03:43 |
trying to come up with a calculation and
put that into a cell, but you're using
| | 03:47 |
that particular cell as part of the
calculation.
| | 03:52 |
That's the circular part of it.
And that you can't do.
| | 03:55 |
When you put that in there, Excel usually
will give you a warning right away.
| | 04:00 |
If you're doing a simple formula, like
this, Excel may ring a bell and pop up a
| | 04:04 |
warning box to tell you what's going on.
The solution here is to change our range
| | 04:10 |
so it doesn't include the cell that we
have in there.
| | 04:14 |
And then we've got a real value.
I'm going to undo that.
| | 04:19 |
So we can look at one other thing.
(audio playing) Here's the circular reference
| | 04:22 |
warning that I was speaking of.
I'm going to say okay and leave it there
| | 04:26 |
for a second.
There's an area that you use for formula auditings.
| | 04:32 |
I go over to the section Formula
Auditing.
| | 04:35 |
There are all these different tools in
here that you can use to figure out
| | 04:38 |
what's going wrong with your spreadsheet.
I'm going to go to Error Checking and down
| | 04:43 |
here to Circular References.
You can see here's the reference.
| | 04:47 |
So if there were any other circular
references in this workbook it would list
| | 04:51 |
them all.
If I click on that it takes me right to
| | 04:54 |
the cell with the error and then I can go
ahead and fix it.
| | 04:58 |
So that's one way you can zero in on
circular reference.
| | 05:01 |
Let's look at one more formula.
Here's a formula that's referring to two ranges.
| | 05:06 |
This little worksheet here has several
named ranges, and if we go up here we can
| | 05:10 |
see, so February is a range, couple of
the other months are range, Shelter is a range.
| | 05:17 |
And what this formula is doing is it's
finding the intersection of Shelter and
| | 05:21 |
February and it's working right where
it's coming up with this number 220.
| | 05:26 |
But what would happen if we deleted this
group of cells?
| | 05:31 |
So I'm going to go over to Home > Delete and
say Delete Sheet Column.
| | 05:36 |
So we delete that whole column and it's
gone.
| | 05:39 |
What's more, that February name is also
gone, because those cells were deleted
| | 05:44 |
and they don't exist anymore.
So, now we've got this reference error in here.
| | 05:51 |
We point to it, the tool tip pretty much
tells us whats going on, moving or
| | 05:54 |
deleting cells caused an invalid cell
reference or function is returning a
| | 05:58 |
reference error.
So those are some of the common errors
| | 06:03 |
that you'll see and those are some of the
reasons why you'll see them.
| | 06:07 |
In this lesson you were introduced to the
div, name, value, and ref errors.
| | 06:12 |
You also saw how to hunt down circular
errors in your worksheet.
| | 06:16 |
| | Collapse this transcript |
| Tracing Errors| 00:02 |
When you have a worksheet where one value
depends on another, a single error is
| | 00:06 |
likely to create a cascade of error
messages.
| | 00:10 |
This lesson shows you how to use Excel's
trace tools, to find the error that's
| | 00:13 |
causing all the trouble.
So if you look at a spreadsheet like this
| | 00:17 |
one where you have errors in one place
and errors in another place, there's a
| | 00:21 |
good chance that these formulas may be
relying on each other.
| | 00:27 |
So, that's a good area to apply some of
the trace tools that Excel provides.
| | 00:32 |
Let me give you a simple example of how
trace works.
| | 00:35 |
Here's a healthy formula, where we're
getting a value and we aren't getting any
| | 00:39 |
error message.
I'm going to go up here to the Formulas tab,
| | 00:43 |
the Formula Auditing group and click
Trace Precedents.
| | 00:47 |
It shows me what cells are used to
calculate this value, these blue dots
| | 00:50 |
show the cells that it, the value's
coming from and the arrows end up in the
| | 00:54 |
cell that I'm tracing.
So everything's fine on that, and we see
| | 00:59 |
blue arrows.
Click this button to remove arrows.
| | 01:02 |
Let's go over to one of these that has an
error value.
| | 01:07 |
I'm going to trace precedents for that, and
instead of a blue arrow, you can see we
| | 01:12 |
have a red arrow, a red line.
We've got red dots and blue dots.
| | 01:18 |
Well the blue dots indicate that these
values are okay.
| | 01:21 |
The red dot indicates that there is
something wrong going on here.
| | 01:25 |
And as a matter a fact there is a value
error in there.
| | 01:27 |
So that is pretty clear that, that's
where the error is.
| | 01:30 |
If we point to this we can see a value
used in the formula is the wrong data type.
| | 01:37 |
Well that's interesting and that gives us
a clue of what to look for.
| | 01:41 |
I'm going to remove the arrows there.
You can use trace precedents and trace
| | 01:44 |
dependents on any formula but there's a
special trace command that you can use
| | 01:48 |
for errors and let's take a look at that.
I'm going to select my cell.
| | 01:54 |
I'm going to go over to error checking,
and choose trace error.
| | 01:58 |
This shows me everything that is used to
calculate the value of this cell.
| | 02:03 |
So we can see that it uses these two
cells.
| | 02:06 |
We've got blue dots on both of these, and
blue arrows that point to this cell, and
| | 02:09 |
then out from this cell we have a red
arrow.
| | 02:12 |
Where the blue lines and the red lines
come together, that's where your problem
| | 02:16 |
is, in a formula.
So we need to examine this more closely.
| | 02:20 |
I'm going to go up here and remove the
arrows, and we'll look at this formula.
| | 02:26 |
So it's using B3, which is this cell
here, and it's using B10.
| | 02:31 |
If we point to the little exclamation
point, it says, "a value used in the
| | 02:34 |
formulas is the wrong data type." so we
examine it.
| | 02:37 |
This looks fine, but this one doesn't.
We've got a, quote mark there which
| | 02:42 |
indicates that this is text, and that's
exactly what's going on here.
| | 02:46 |
Instead of, a number in here we have
numbers an text.
| | 02:50 |
This is actually, the letter O, not the
number zero.
| | 02:54 |
So what we want to do, what we really need
in there is, we want a sum in there.
| | 02:59 |
We can go up here and use Auto Sum to put
a sum in there.
| | 03:03 |
And that fixes our formulas.
So this gives you an idea of how to use
| | 03:07 |
the trace formulas.
Now we can use these trace tools, and
| | 03:11 |
we'll get blue lines whenever we use
them, so trace precedents.
| | 03:15 |
We've got a blue line and blue dots.
If I remove the arrows and go up here
| | 03:19 |
where we were having the problem, we can
do Trace Dependents.
| | 03:23 |
And we can do trace precedents and
everything's blue and everything's
| | 03:26 |
looking good.
Let's look at one more example.
| | 03:29 |
Here's a spreadsheet that calculates tax,
a total and percentage and obviously we
| | 03:33 |
haven't had a problem in here.
We can go in here and do our error
| | 03:38 |
checking, Trace error.
And again, where the blue line stops and
| | 03:43 |
the red line begins, that's where the
problem is.
| | 03:46 |
So our problem is in this cell.
If I point to the tool tip, it says " The
| | 03:50 |
formula contains unrecognized text," so
I'm going to remove the arrows.
| | 03:56 |
I've got my cell selected and I'm looking
at the formula.
| | 04:01 |
Well there's only one bit of text in
there and that's tax.
| | 04:04 |
So, it's referring to tax as if it's a
named range or a named cell.
| | 04:10 |
All right, if I go over here and look in
here, I can see tax doesn't exist so
| | 04:14 |
that's my problem, I want to identify a
cell that has the tax and that's this
| | 04:19 |
cell here.
So I select that.
| | 04:23 |
I look back over here in my name box.
This cell is not named, but I want to
| | 04:29 |
name it.
I'm going to name it Tax and press enter.
| | 04:33 |
And that fixes that, fixes my formulas.
I can trace dependents and trace precedents.
| | 04:41 |
And everything's working the way it ought
to.
| | 04:42 |
Now if I try to do error checking on a
cell that doesn't have an error.
| | 04:50 |
So if I got over here and choose trace
error, I get a warning box.
| | 04:57 |
You can't use trace error on a cell that
doesn't have an error.
| | 05:01 |
In that case, you need to use these other
trace options to get your arrows and dots.
| | 05:06 |
So in this lesson, you learned how to use
trace precedent, trace dependent, and the
| | 05:11 |
trace error tools.
These tools help you pinpoint a formula
| | 05:15 |
that's causing a cascade of errors.
| | 05:18 |
| | Collapse this transcript |
|
|
5. Making Changes to Your WorkbookMoving, inserting, and removing cells, rows, and columns| 00:02 |
Sometimes when you're developing a
worksheet, you'll want to rearrange the furniture.
| | 00:05 |
Move things around, remove some, add
others.
| | 00:08 |
This lesson focuses on moving, inserting,
and removing cells, columns, and rows.
| | 00:15 |
So moving is probably the easiest thing
to do.
| | 00:17 |
To move something, just select your group
of cells that you want to move, and then
| | 00:21 |
put your cursor over the border and you
see this symbol, it's a cross with
| | 00:25 |
arrowheads, that's the Move symbol, and
you can just drag your cells to another
| | 00:28 |
location, and you can see, it copies the
formulas intelligently.
| | 00:35 |
You can see that this formula still
operates on those cells that I want it to
| | 00:38 |
operate on.
I could drag it to another location, drag
| | 00:44 |
down and over, and it's still operating
on those same cells.
| | 00:50 |
So, the move operation is very
intelligent and very easy to use.
| | 00:56 |
Now what you don't want to do, probably.
You don't want to move your cells to
| | 01:00 |
something that already has values in
there or formulas in there.
| | 01:04 |
You get an alert message like this that's
asking do you really want to copy the
| | 01:07 |
contents of these cells with the stuff
that you're dragging around?
| | 01:12 |
And I don't want to so I'm going to click
Cancel.
| | 01:14 |
If that was what I wanted to do I could
click OK.
| | 01:16 |
So, that's the move operation.
Now, what you want to do.
| | 01:22 |
If you want it to move something here,
for example say we wanted to move this
| | 01:26 |
unit cost column, and put it in front of
quantity.
| | 01:30 |
We know we don't want to just drag it on
top of quantity.
| | 01:33 |
We want to insert a cells in there and
most of the time you'll want to insert an
| | 01:37 |
entire column of cells, it just keeps
your spreadsheet more organized if
| | 01:41 |
there's other things that might be
affected in that move.
| | 01:47 |
When I get an arrow like this that points
down, I can select an entire column of cells.
| | 01:53 |
Then I can go over on the Home tab over
to the cells group and I can click Insert.
| | 01:59 |
But there's another option I like to use
that's even easier.
| | 02:02 |
I'm going to undo that for a second, when
you have your selection made if you right
| | 02:06 |
click on a selection you go down here and
choose Insert from the context menu, and
| | 02:10 |
that's really the faster way to do it
then the way I usually do it.
| | 02:16 |
So now I want to drag, my unit cost over to
those empty cells.
| | 02:21 |
And then I can delete this empty column.
I'll select it in the same manner, and
| | 02:27 |
right-click, and then choose Delete.
We made, rearranged our worksheet a bit,
| | 02:32 |
and everything's working the way it
should.
| | 02:35 |
You can do the same thing with with rows
as you do with columns.
| | 02:39 |
So suppose we want to move our rain barrels
here up to the top of the list.
| | 02:44 |
We go up to the top, right-click, and
choose Insert, so we insert an empty row.
| | 02:50 |
And we can select our entire row with
rain barrels, and drag them up to the
| | 02:56 |
empty spot.
Go down here and right-click and Delete
| | 03:01 |
the empty row, and we've done a little
rearranging in our worksheet.
| | 03:06 |
In this lesson, you learned how to move
cells by dragging them to a new location.
| | 03:12 |
If you move cells to a new location that
already have values or formulas, it will
| | 03:16 |
replace those values.
And you also learned how to insert, move,
| | 03:20 |
and delete entire rows and columns of
cells.
| | 03:24 |
| | Collapse this transcript |
| Transposing rows and columns| 00:02 |
There are plenty of reasons why you might
want to turn your rows into columns and
| | 00:05 |
vice versa.
Perhaps you want to move your data and make
| | 00:08 |
it match some other existing format or
maybe you're analyzing data and you need
| | 00:11 |
a fresh view.
Or maybe you just don't like the way you
| | 00:15 |
set it up the first time.
Whatever the reason, the process is
| | 00:18 |
called Transposing or Transposition and
fortunately as you'll see in this lesson,
| | 00:22 |
Excel makes it really easy.
Let's start off with a single column.
| | 00:27 |
Suppose we want to take this Unit Cost
column and turn it into a row.
| | 00:32 |
First step is to copy it.
Now I'm going to go over to a new tab, a
| | 00:36 |
new worksheet, just so we have some clean
space here to work in.
| | 00:41 |
I'm going to go up to the Paste command, I'm
going to use the menu below the Clipboard
| | 00:44 |
and open it up.
You can see this top group is called
| | 00:48 |
paste, the last item on paste is called
Transpose.
| | 00:51 |
So if I point to any of these options, it
shows what that option will do.
| | 00:55 |
Sometimes it's copying value, sometimes
it's copying formulas, sometimes it's
| | 01:00 |
copying the formatting, but this last
one, Transpose changes the order from
| | 01:04 |
columns, to a row.
So if I just click on that, paste it in
| | 01:10 |
as a row.
Might want to do a little bit of cleanup to
| | 01:13 |
get my column widths looking right, but
it does the job, and it works quite well.
| | 01:19 |
You can do the same thing taking this
whole table of data.
| | 01:23 |
Let's see how that would work.
Let's go up here and click Copy.
| | 01:27 |
I'm going to go over to a clean worksheet
and select my cell.
| | 01:33 |
Now instead of using the commands here,
I'm going to right-click, and you'll see
| | 01:36 |
that you have paste options on the
context menu.
| | 01:40 |
And as you point to it it shows you the
different options.
| | 01:44 |
And what I'm going to do is go over here to
Transpose, and click on that.
| | 01:49 |
See things don't fit quite right, but
what I can do while it's selected is I
| | 01:53 |
can go up to this line between the A and
B column, double-click on that, and it'll
| | 01:57 |
fix my column widths.
I'm going to go up here and select these
| | 02:03 |
guys, give everything a little bit more
space, and you can see it's done a good
| | 02:07 |
job of transposing the columns into rows
and the rows into columns.
| | 02:14 |
Sometimes there's a little bit of cleanup
that you'll need to do after the fact.
| | 02:18 |
In this lesson you saw how easy it is to
copy data and then transpose the rows and
| | 02:22 |
columns when you paste it into a new
location.
| | 02:25 |
| | Collapse this transcript |
| Merging and unmerging cells| 00:02 |
Your data doesn't always look best when
it's divvied up into an even number of
| | 00:05 |
cells like a piece of graph paper.
Sometimes by merging cells, you can make
| | 00:09 |
your worksheet look better, and even more
important, you can make it communicate better.
| | 00:15 |
So let's look at this worksheet here.
This tracks individual salespeople's car
| | 00:20 |
sales over the course of a year.
Frankly it looks like they're pretty good salespeople.
| | 00:26 |
The spreadsheet looks a little bit dull,
everything is kind of weighted with the
| | 00:29 |
same importance.
And it would be nice to divide it up a
| | 00:32 |
little bit, highlight it a little bit.
So, we're going to do that, we're going to
| | 00:37 |
use a few formatting techniques but
primarily we're going to merge cells.
| | 00:41 |
But first, let's look at borders.
Here we have a quarter, over the course
| | 00:44 |
of three months, and it would be really
nice to have a line going down, dividing
| | 00:48 |
each of these quarters one from another.
So I'm going to select the column, go up
| | 00:54 |
here and click on the C, select the
entire column.
| | 00:57 |
Then I'm going to go to my borders, this
is the Home Tab, and I will choose the
| | 01:00 |
left border.
And then I'll go over here to the second
| | 01:04 |
quarter and do the same thing, right on
down the line.
| | 01:09 |
Okay, that looks good.
So we've got lines separating each of our quarters.
| | 01:17 |
Let's do the same thing for the sales
people.
| | 01:19 |
I'm going to click on the row next to the
name of the sales person.
| | 01:24 |
Then go up here and choose top border.
We'll do that for each of the sales people.
| | 01:30 |
Create sort of a grid that'll give us
kind of the major divisions for our information.
| | 01:35 |
And that looks great.
Now first quarter of course applies to
| | 01:39 |
all three of these months.
So it would be great to have it centered
| | 01:42 |
over all of them, and there's a single
command that you can use for that.
| | 01:45 |
You can drag across these three cells,
and then again on the Home Tab go up here
| | 01:49 |
to Merge and Center, and click Merge and
Center.
| | 01:53 |
So that centers it for us.
And we could make it look even better
| | 01:57 |
with a little bit of highlighting, so
let's go in here and choose a color, for
| | 02:01 |
the background of the cell, and we'll
then choose white for the font.
| | 02:07 |
That looks pretty good, so I like that
I'm going to double-click on Format
| | 02:11 |
Painter here.
Which leaves it sort of sticky, and then
| | 02:15 |
I can click each of the other places
where I want to make that change.
| | 02:19 |
So now all those quarters match and
they're centered above the months that
| | 02:22 |
looks great.
So in that case we were merging cells
| | 02:26 |
along a row.
We can also merge cells in a column.
| | 02:30 |
Click this to turn off Format Painter.
Point to select the columns related to my
| | 02:36 |
salesperson here, and click Merge and
Center again.
| | 02:42 |
That merges all these cells into a single
cell.
| | 02:44 |
But I really need to do something with
the name here.
| | 02:47 |
Going to center it vertically, and I
think I'll spin it around a little bit.
| | 02:54 |
That looks good.
We can, bump up the size a little bit
| | 02:58 |
since we've got the room.
And we can do the same thing with the
| | 03:05 |
color here.
Let's choose blue background and white text.
| | 03:11 |
That looks great.
So, I'm going to use my Format Painter,
| | 03:14 |
double-click on it.
And then do the same thing to these other names.
| | 03:19 |
turn Format Painter off by clicking on
it.
| | 03:24 |
Size this a little bit.
There, just a few little formatting
| | 03:29 |
techniques turn, what was it dull spread
sheet, into something a little bit livelier.
| | 03:34 |
And it's much clearer where the quarter
divides are and the salespersons range of
| | 03:39 |
sales here.
Now if you want to unmerge your cells,
| | 03:44 |
all you have to do is select the cell,
and you can see that's a single cell
| | 03:48 |
right now, and go back up here to your
Merge menu.
| | 03:52 |
Go down to the bottom and do, choose
Unmerge Cells.
| | 03:57 |
And now you can see that it's turned that
back into three separate cells.
| | 04:00 |
We've got our first quarter label over
there.
| | 04:03 |
Undo that just so it looks better.
So in this lesson, you saw how to merge
| | 04:09 |
and unmerge cells, and you learned that
the process for rows and columns is
| | 04:13 |
exactly the same.
| | 04:16 |
| | Collapse this transcript |
| Adding comments to worksheets| 00:02 |
There are a few ways that you can add
text to your worksheets and it's not
| | 00:04 |
always a matter of typing text into
cells.
| | 00:07 |
For example, this lesson shows you how to
add text boxes that float over the grid
| | 00:11 |
of your worksheet.
There's another option, you can add
| | 00:15 |
comments, they're sort of like those
sticky yellow notes and you attach them
| | 00:18 |
to a cell but they remain hidden until
somebody moves the cursor over that cell.
| | 00:23 |
So let's take a look at this worksheet.
It's a pretty basic affair.
| | 00:28 |
It has a profit and loss statement
keeping track of profit and loss over a
| | 00:31 |
number of years.
The company has this little statement
| | 00:35 |
here, that time is not money.
So that might be nice to bump up into a
| | 00:40 |
little bit bigger presentation.
So, we can put it in a text box, going to go
| | 00:45 |
to the insert tab.
And then down here to the text group and
| | 00:50 |
click on text box, then you just click
anywhere on the work sheet, and start typing.
| | 01:01 |
You know, if you'd made a mistake, you
could go back and you could type over it again.
| | 01:04 |
And your text appears in the box.
If I click on it, we see the border
| | 01:10 |
around the box and a couple little
handles.
| | 01:14 |
If I want to move it I can move the cursor
over the border and then just drag it to
| | 01:18 |
a new location.
Right now it's looking a little bit plain
| | 01:22 |
so let's spruce it up a little bit, let's
give it some background color, click on
| | 01:26 |
that and we'll change the color of the
font, make that white so it stands out a
| | 01:31 |
bit better.
I'll make it bold.
| | 01:35 |
Let's bump up the size a bit.
As we bump up the size, you can see, it
| | 01:40 |
makes the, the box bigger.
And we can drag it, let's drag it into
| | 01:43 |
the open here and look at some of these
other options.
| | 01:46 |
You can change the size of the text box,
but that doesn't really change the size
| | 01:51 |
of the type, it really just changes that.
If you wanted to add some more text, you
| | 01:56 |
can use the return key like you normally
would.
| | 01:59 |
(audio playing) You can add text.
You can do a lot of text, you can do
| | 02:04 |
several paragraphs if you want to.
Go backwards here a little bit.
| | 02:10 |
If you want to rotate the text, you can
just grab this handle here and use that
| | 02:13 |
to rotate it.
Twist it around to a new location.
| | 02:16 |
I'm going to Undo that.
Move it back here, you'll notice the text
| | 02:21 |
box just floats over anything that you
happen to put it on, it's always at the
| | 02:25 |
top level of your worksheet.
So that's a text box, you can do those in
| | 02:31 |
various sizes and put a lot of different
content in it, they can be very long if
| | 02:35 |
you want them to.
Now the comments work a little bit differently.
| | 02:41 |
Comments are attached to a single cell,
and they're really used to communicate
| | 02:44 |
information to other people that are
using the spreadsheet.
| | 02:48 |
If you're, maybe you're developing a
spreadsheet together, or maybe different
| | 02:51 |
people are reviewing what's going on in
your company.
| | 02:54 |
So I click on a cell, and then I go to
the review tab.
| | 02:58 |
And here we see different review options,
and there's quite a few related to comments.
| | 03:03 |
Click on New Comment and it appears here
and I can type in my message.
| | 03:07 |
So there's my comment, has my name and I
can go in here and edit it I could,
| | 03:15 |
remove my name for example if I wanted
to.
| | 03:24 |
And I can come back and edit it later,
but that's the comment.
| | 03:29 |
And people know that there's a comment
there because of this little red flag
| | 03:32 |
that's up in the upper right corner.
And if they move their cursor over it, it
| | 03:36 |
makes the comment pop up.
There are commands related to comments.
| | 03:41 |
You can use the two buttons Previous and
Next up here to thumb through the
| | 03:44 |
different comments in the Worksheet.
That's great if you're opening up a brand
| | 03:48 |
new worksheet, you want to make sure you
see every comment that's in the Worksheet.
| | 03:52 |
You can just go, keep hitting next, until
you go all through and see all the comments.
| | 03:57 |
You can delete comments using the
commands here.
| | 04:00 |
And you can use this Show All Comments
command you can turn that on or off.
| | 04:05 |
So there are a number of different things
that you can do and all the commands are
| | 04:08 |
in here to manage your comments.
So in this lesson, you saw how to create
| | 04:12 |
a text box, you saw the text boxes can be
edited and re-sized and moved around and
| | 04:16 |
that they always float above the grid of
your worksheet.
| | 04:21 |
You also saw how to attach comments to
cells and how to manage the comments that
| | 04:27 |
are in a workbook.
| | 04:30 |
| | Collapse this transcript |
|
|
6. Visualizing Your Data with Color, Charts, and GraphicsChanging worksheet colors, fonts, and effects| 00:02 |
Excel works hard to bring images an
color, to what would otherwise be dull,
| | 00:05 |
monotone tables of numbers.
An even if you're artistically impaired,
| | 00:10 |
you have tools at hand to make your
worksheets look the best.
| | 00:14 |
The key is on the page layout tab in the
themes group, an that's the subject for
| | 00:18 |
this lesson.
But first let's look at the standard way
| | 00:22 |
that you'd make changes to the colors in
your document.
| | 00:25 |
So normally, if you have a worksheet that
looks like this, which is pretty plain,
| | 00:29 |
but it has a bit of color and some extra
design features added to it, you'd make
| | 00:33 |
those changes up here in the Home, Font
group.
| | 00:37 |
And here you can change the background
color of cells, you can change the color
| | 00:41 |
of the fonts, and you can change the font
choices, you can choose different font
| | 00:45 |
styles and sizes, and you can make them
bold.
| | 00:51 |
So that's the manual way of doing things.
There's also a quick, automatic way to
| | 00:55 |
make these changes.
So for that we go over to the Page Layout
| | 00:58 |
tab and we look over here in the Themes
group.
| | 01:02 |
You can see that there are three groups
here, Colors, Fonts, and Effects.
| | 01:07 |
Then you have themes which actually
combines all three of these together.
| | 01:11 |
So let's look at these one by one.
Here we have our worksheet and if we
| | 01:16 |
wanted to change the color of the
headings here, we'd go up to the colors
| | 01:19 |
group and then point to one of these
groups of colors swatches.
| | 01:25 |
You see they have names.
And then there are groups of swatches in here.
| | 01:28 |
And it automatically applies these colors
to specific elements in your worksheet.
| | 01:36 |
So all you have to do to see the effect
is to point at one of these options.
| | 01:41 |
And then if you like it, you can click to
select it.
| | 01:43 |
And it's automatically applied.
And as you can see they're all different
| | 01:47 |
options in here, different color
combinations, and the list goes on and on.
| | 01:53 |
And you have similar tools for fonts.
You can go up here and you can see
| | 01:57 |
combinations of fonts, so you can see
that you have some that use different
| | 02:01 |
styles of fonts for the data and
different styles for the heading.
| | 02:07 |
And all you need to do to preview it, or
to make a choice, is point to it or click
| | 02:11 |
on it.
And again, you can see that the list,
| | 02:14 |
quite a long list, gives you a lot of
different options.
| | 02:17 |
And it's really quick and easy to see if
that's the effect that you want.
| | 02:22 |
Now the effects group you'd think would
have the greatest, most dramatic change
| | 02:26 |
of all, but in a lot of spreadsheets it
really doesn't because this, the Effects
| | 02:30 |
group applies to any 3D elements in your
document.
| | 02:36 |
And unless you've added a chart or some
smart art or something like that to your
| | 02:41 |
document, you won't see great changes
when you point to these 3D effects.
| | 02:47 |
If you want to combine all three together,
go over here to the Themes tab and just
| | 02:51 |
point to one of these options.
The themes combine colors, fonts, and effects.
| | 02:58 |
And again, all you have to do is point to
see the changes in fonts and colors and effects.
| | 03:05 |
And there are several options.
You can scroll down the list and all you
| | 03:08 |
have to do is click to select it.
Now this is a fairly plain worksheet.
| | 03:13 |
Let's look at one of the templates that
have even more design elements and you'll
| | 03:17 |
see how dramatic the changes can be.
I'm going to go over here to the View tab,
| | 03:22 |
we'll switch windows, and here's a
document with a doughnut chart and some
| | 03:27 |
3D effects in there.
We'll go back to Page Layout and the
| | 03:33 |
themes group, and now you can see the
dramatic changes When you choose a
| | 03:40 |
different theme.
And really it's just a matter of pointing
| | 03:46 |
to the one you like and clicking on it.
And the great advantage of themes and
| | 03:50 |
even the color and font options is that
you don't have to spend a lot of time
| | 03:54 |
fiddling with the graphics and visual
effects in your document.
| | 03:59 |
You can focus on the numbers and the
important underlying data, and you can
| | 04:03 |
let Excel do the formatting for you.
So in this lesson you saw that you can
| | 04:08 |
quickly change the color, fonts and
effects in Excel worksheets.
| | 04:13 |
You can change each individually, or you
can work even faster by using Excel Themes.
| | 04:18 |
Those are pre selected combinations of
color, fonts, and effects.
| | 04:22 |
| | Collapse this transcript |
| Creating charts| 00:02 |
In general, the reason we spend time
keeping track of all these numbers is so
| | 00:05 |
that we can see and get a handle on the
underlying story.
| | 00:09 |
Is business getting better?
How much of my household budget goes
| | 00:13 |
toward gasoline?
Excel makes it easy to visualize your
| | 00:16 |
numbers by translating them into charts
and graphs.
| | 00:21 |
That's what we'll look at in this lesson.
So here's a spreadsheet where we keep
| | 00:24 |
track of different sales people and the
cars that they sell.
| | 00:27 |
And across the top here we divide sales
into quarters and months.
| | 00:33 |
So suppose we want to zero in on the
first month of the year and see what kind
| | 00:37 |
of sales each of these salespeople had.
I'm going to select the first three
| | 00:43 |
columns here and then go to insert and
take a look at the chart groups here.
| | 00:49 |
And this is where you decide what format
chart will work best for the data that
| | 00:54 |
you've chosen.
I'm going to choose the Column chart, and
| | 00:58 |
that's a really common one to use along
with Line and Pie, probably.
| | 01:02 |
We're just going to use this first simple
column chart for this data, but you can
| | 01:05 |
see there are lots of options.
Some of these are really just visual options.
| | 01:10 |
So, you have cylinder and cone and
columns, and you have 3D and 2D.
| | 01:15 |
And then some of them are good for other
types of data.
| | 01:18 |
So these stack charts are good when you
have a couple different types of data.
| | 01:22 |
That's not what we have here.
We'll just choose this first one here,
| | 01:26 |
and just that easy, Excel takes our data
and our numbers and our labels and turns
| | 01:30 |
it into a chart.
And it's pretty smart about doing so.
| | 01:34 |
You can see we have a heading up here,
First Quarter January, that it took from
| | 01:38 |
this cell.
Then we've got our salespeople's names
| | 01:42 |
down here, and lines showing each of the
car sales that they had.
| | 01:47 |
Right now this is floating over our data
and we can move it to a new place just by
| | 01:52 |
dragging it.
Or we can resize it, or we could do a
| | 01:57 |
couple of things to it and often you
might want to keep your chart on the same
| | 02:02 |
Worksheet as your, as your data.
Look at them both at once.
| | 02:08 |
You might be able to print them out on
the same page depending on how much space
| | 02:11 |
each of them take up.
You can stretch the chart by just
| | 02:15 |
dragging on a corner and get a little bit
better view of the data.
| | 02:19 |
The other thing you can do that is often
helpful is to move the chart to a tab of
| | 02:23 |
it's own and I'm going to do that now.
Right-click on this, move down here and
| | 02:29 |
choose Move Chart and I'm going to move
the chart to a new sheet so I'll click
| | 02:35 |
that button and we can give it a new
name.
| | 02:41 |
We can say sales January, okay.
Now you see we have a sale January tab.
| | 02:48 |
Here's our chart, where we get a view of
it all by self and it tells the story
| | 02:51 |
pretty well when it's just by itself.
When you have your chart selected, the
| | 02:56 |
chart tools appears up here in the ribbon
and you can see there's several different
| | 03:00 |
tabs related to chart tools and this
first one you can change the colors and
| | 03:03 |
the styles and you can change the layout.
One interesting thing you can do here is
| | 03:09 |
switch row and columns.
So, if the chart didn't come out exactly
| | 03:12 |
the way you expected it to, if you click
this button you'll get a different view
| | 03:16 |
where the rows and columns are swapped.
So in this view, we have a bar for each
| | 03:21 |
individual car sold by each individual
person, and that gives you a different
| | 03:25 |
view of your data, and maybe that's the
view that you wanted.
| | 03:31 |
These options change the appearance of
the charts.
| | 03:35 |
For example, this middle one here shows
numbers for each of the bars.
| | 03:39 |
So at the same time that you see the
visualization you can see the actual
| | 03:43 |
number that lies under the data.
Let's go back to our worksheet and build
| | 03:48 |
another chart.
Let's take a look at the first three
| | 03:52 |
months of the year, this first quarter.
So I'm going to do the same thing.
| | 03:56 |
I'll just go here and select through
column E.
| | 03:59 |
That gets us through March.
Go Insert>Column.
| | 04:05 |
This time I'm going to use the Stacked
Column.
| | 04:07 |
And we see a new chart.
I'm going to go ahead and move this over
| | 04:11 |
to its own tab so we can get a better
view.
| | 04:14 |
Call this sales first quarter.
But now we have a new chart and you can
| | 04:24 |
see that it's stacking the months of the
quarter together.
| | 04:31 |
So we see the entire quarter sales for
Steve, The Tesla S.
| | 04:36 |
So we see a visualization of how many
cars he sold for the entire quarter as
| | 04:40 |
well as a month by month breakdown of the
sales.
| | 04:45 |
And we can make the same changes up here.
We could click this button and see the
| | 04:49 |
numbers on top of the stacked bar chart.
So, that's just how easy it is to go in
| | 04:56 |
and take data from your worksheet and
turn it into a chart or a graph.
| | 05:03 |
This lesson showed you how easy it is to
turn the number in your worksheet into
| | 05:06 |
charts and graphs.
You can select the data and then you go
| | 05:10 |
to the Insert tab to choose your chart
style.
| | 05:13 |
You'll want to match your chart style to
the underlying data, obviously.
| | 05:17 |
And you can move your chart to its own
tab or you can leave it displayed on the
| | 05:21 |
worksheet with the underlying data.
And using the chart tools, you can change
| | 05:28 |
the layout and format of your chart.
| | 05:30 |
| | Collapse this transcript |
| Choosing the right chart for your data| 00:02 |
There's a certain art to matching your
chart to the underlying data.
| | 00:06 |
For a chart to look good and make sense,
you need to feed it the right data.
| | 00:11 |
Now this lesson looks at a few different
types of charts and explains why you'd
| | 00:14 |
choose them.
Here's a worksheet that looks at
| | 00:18 |
household expenses.
It keeps track of them over the year, and
| | 00:22 |
then it has a total for each expense down
here at the end.
| | 00:25 |
Suppose we want to make a pie chart that
would give us a relative comparison of
| | 00:29 |
the different expenses for the entire
year.
| | 00:33 |
So the thing to do would be to select
these expenses, and then go down here and
| | 00:38 |
we can hold the Ctrl key down while we
select the totals at the end of the year.
| | 00:45 |
Notice that we don't have to select
contiguous data.
| | 00:50 |
We can jump from one end of our data to
the other.
| | 00:53 |
Now we can go up here and choose Insert
and choose Pie Chart.
| | 00:59 |
Let's make this one of the 3D pie charts,
and there we've got our chart.
| | 01:06 |
And it looks pretty good.
We can drag it out and make it a little
| | 01:09 |
bit bigger.
So we see a legend over here that matches
| | 01:13 |
the colors to the pieces of the pie.
But it would be even better if the data
| | 01:18 |
were listed on top of it.
So I'm going to go up here to Chart Layouts
| | 01:22 |
and click on that option.
You can see it shows a labels describing
| | 01:27 |
the data and then the percentage
underneath.
| | 01:31 |
We can even add the dollar value onto
this chart by doing this.
| | 01:35 |
Select the label and then go Format Data
Labels and we get this window with
| | 01:39 |
several label options, one of which is
Value so we can turn things on and off here.
| | 01:47 |
We're going to turn Value on and now, we
see the name, the percentage and the
| | 01:52 |
dollar value of each one of these items.
Now, if you wanted to, this doesn't
| | 01:59 |
really have title yet.
Suppose we want to go up and give this a
| | 02:02 |
title, all we need to do is click on it
and then click inside, we can select the
| | 02:05 |
text and give it a title.
So that's a pie chart, and pie charts are
| | 02:12 |
obviously good to compare percentages, to
compare quantities and things like that.
| | 02:23 |
There are a lot of cases where a pie
chart wouldn't be appropriate.
| | 02:26 |
Let's look at some of the other options
in here.
| | 02:28 |
So here's a chart that is comparing sales
and it's just comparing values.
| | 02:34 |
Numbers of cars sold by car and by
salesperson.
| | 02:39 |
Works quite well just putting it on an
angle and making it a 3D chart.
| | 02:43 |
Gives it a little bit more visual
interest.
| | 02:44 |
Otherwise, it'd be a fairly dull chart.
Here's an example of a stacked column
| | 02:50 |
chart, and its formatting, it's a column
and it's a cylinder style.
| | 02:55 |
And you can see that it shows the quarter
in a single column, then it breaks that
| | 03:00 |
quarter down into months, so you have
January, February, and March expressed as
| | 03:05 |
different colors.
This also is one of the 3D charts, so
| | 03:10 |
it's angled just a little bit.
Gives it a different appearance than if
| | 03:14 |
it were just a 2D chart.
Now in some cases 3D may be flashier than
| | 03:17 |
you might want to be a little more button
down so a 2D chart might work well.
| | 03:22 |
If you want to change your chart type after
the fact, you can go up here to Type and
| | 03:26 |
click on that and you'll see all the
different chart types that are available
| | 03:30 |
to you.
Drag this out to make it a little Bigger.
| | 03:34 |
So you can see all these different types
of charts and you can choose one simply
| | 03:38 |
by clicking on it.
We could turn our chart into a bar chart
| | 03:42 |
by clicking on that and clicking OK.
And now we see it displayed in a
| | 03:47 |
different manner.
The numbers go across the base of the chart.
| | 03:52 |
And you have the cars listed over here on
the left.
| | 03:56 |
There are some specialized charts and the
stock charts are one of them.
| | 04:00 |
Here's a stock chart that's keeping track
of daily prices.
| | 04:03 |
It's using color to show whether the
value Went down or the value went up, and
| | 04:09 |
it shows the open, the close, the high,
and the low for each day that the stock
| | 04:15 |
was being sold.
And you can go in here to Chart Type, go
| | 04:21 |
in here and click on Stock, you'll see
that there are several different styles
| | 04:24 |
that use different types of data.
And obviously, you need to prepare your
| | 04:29 |
worksheet beforehand so that you're
feeding your chart the right type of data.
| | 04:34 |
In this lesson you examined different
types of charts.
| | 04:37 |
Pie, column, stack column, bar And stock
charts.
| | 04:42 |
You saw that Excel lets you select
non-contiguous data from your worksheet
| | 04:46 |
to build a chart, but in some cases, it
might be better to arrange your data
| | 04:50 |
beforehand so that it provides the
information that you need for your chart.
| | 04:56 |
| | Collapse this transcript |
| Changing your chart's appearance| 00:02 |
Excel is pretty savvy when it comes to
building a chart from your data.
| | 00:05 |
A lot of the time you'll be happy with
its choices when it comes to colors,
| | 00:08 |
fonts, font sizes and similar issues.
However, for those times when you want to
| | 00:13 |
make changes, you'll find that it's a
fairly simple and fairly intuitive process.
| | 00:18 |
So, once you select your data and you
create a chart like this, you'll notice
| | 00:22 |
that when the chart is selected, these
chart tools appear up here.
| | 00:28 |
And they're divided into three parts.
There's Design, Layout and Format.
| | 00:32 |
And each of the tabs give you options so
that you can change the appearance of
| | 00:36 |
your chart.
The Design tab makes the biggest changes,
| | 00:40 |
for example you can change the chart
type, you can choose to turn this pie
| | 00:44 |
chart into a bar chart if we wanted to.
And it would display the data in that format.
| | 00:51 |
We're going to undo that so we can look at
some other changes.
| | 00:54 |
You can change the data, so you can
switch the row and column definitions,
| | 00:57 |
and that will display your data in a
different manner.
| | 01:00 |
Or you can use this charts layout group,
so for example here we have the data
| | 01:05 |
labels on top of the pieces of the pie.
We could choose this layout and it would
| | 01:11 |
create a legend and remove the data
labels from on top of the pie.
| | 01:17 |
There are several color options that you
have here, and there are lots of reasons
| | 01:19 |
why you might want to choose one over
another.
| | 01:22 |
Perhaps you'd like to match the colors in
your logo, you could do that, or, for
| | 01:25 |
more practical reason, if you're printing
on a black and white printer, you might
| | 01:29 |
want to choose this Grayscale option just
to get an idea of how that would look if
| | 01:33 |
you print it in black and white.
The Layout tab gives you different
| | 01:40 |
options and you can choose to add
pictures shapes or text, and that's as
| | 01:46 |
easy for example if we want to provide
some explanatory text.
| | 01:53 |
We could just click on that box and then
put the text right in there.
| | 02:00 |
That might help you highlight a specific
point that you're trying to make and you
| | 02:04 |
can drag this around different location
to the side where you want your text to
| | 02:08 |
appear in, so that it looks good.
You decide to delete it, you can just
| | 02:17 |
select it and delete it.
And you can move most of these options in here.
| | 02:21 |
For example, you can select the title and
you can drag it to another location if
| | 02:26 |
you prefer.
Or you can use the Labels layout to make
| | 02:32 |
those decisions.
So up here we could say Center Above the
| | 02:36 |
Chart, and it moves the title above the
chart.
| | 02:39 |
But you're not limited to these options,
as I mentioned.
| | 02:41 |
You can drag any of these to a different
location.
| | 02:45 |
And Excel responds pretty cleverly, so
you can see.
| | 02:48 |
It leaves these labels here, but it pulls
this one out, and uses a line to identify
| | 02:52 |
that piece of the pie.
You might want to do that to emphasize
| | 02:56 |
that piece of the pie in a, certain
situations.
| | 03:00 |
Or, another great, thing that you can do
with pie charts, is you can, remove a
| | 03:03 |
piece of the pie, and drag it out from
the rest of them.
| | 03:09 |
So those are some of the Layout options.
Let's look at some of the Formatting options.
| | 03:14 |
So here you see the Formatting options
and you can select individual elements
| | 03:18 |
and then apply some of these formatting
commands to them.
| | 03:22 |
There are a lot of commands to look at.
So for example, if we took a piece of
| | 03:25 |
pie, we could decide what color that
piece of the pie is going to be.
| | 03:30 |
Or if we take a label, we could choose to
format that selection.
| | 03:39 |
Now we could choose the option up here to
bring up a dialog box, where we could
| | 03:43 |
choose a border color to apply to our
labels.
| | 03:47 |
You can see now each of the labels has a
border around the label.
| | 03:55 |
Another thing you can do is to select an
element like these data labels.
| | 03:58 |
And then right-click, and you can choose
something like Font from that.
| | 04:03 |
And we could decide we want to bump our
font up to a larger size.
| | 04:08 |
I'm going to make those 12 point instead of
10 point.
| | 04:11 |
When I click OK, everything goes up a
notch and you can see, if necessary, if
| | 04:16 |
there's not enough room there, Excel
actually moves some of these labels out
| | 04:21 |
from the pie.
You can see with the Design layout and
| | 04:27 |
Format tabs, you're given an off a lot of
different options.
| | 04:31 |
You'll have different options for
different types of charts.
| | 04:34 |
So, in this lesson you saw how to make
visual changes to your chart.
| | 04:38 |
And these kind of changes can help you
emphasize the points you want to
| | 04:41 |
communicate to your audience.
| | 04:44 |
| | Collapse this transcript |
| Using sparklines as "data words"| 00:02 |
Edward Tufte is something of a guru when
it comes to different ways to visualize data.
| | 00:06 |
He calls sparklines data intense,
designed simple, word-sized graphics.
| | 00:13 |
Now it would be hard to find a better
definition.
| | 00:15 |
Sparklines fit in a single cell and
they're miniature charts that can express
| | 00:19 |
a data trend of a row or column of
numbers.
| | 00:23 |
So let's see how they work in Excel.
Here's a spreadsheet for a wildlife
| | 00:27 |
sanctuary and they do a bird count each
month.
| | 00:30 |
So this top table up here keeps track of
the count of the different birds.
| | 00:34 |
If we want to see how those populations
trend up or down over the course of the
| | 00:38 |
months, we could put a little sparkline
here for each one of the birds, and
| | 00:42 |
visualize that data.
So to do that, I select my cell.
| | 00:48 |
I go over to Insert, and then I look at
the Sparklines group.
| | 00:52 |
There are three different types of
sparklines that you can use and let's use
| | 00:55 |
this first one, so line chart.
It's asking me what range of cells I
| | 01:00 |
want to choose from my data, so I'm going to
select, just click and drag across these
| | 01:03 |
cells all the way to December.
When I hit enter we see the sparkline
| | 01:08 |
shown right in there in the cell.
You can see it peaks and then drops and
| | 01:12 |
then it goes back up again.
You can also use the sparkline design
| | 01:16 |
tools to either change the color of the
line or the data points, or probably even
| | 01:20 |
more useful, you can mark a high point
and a low point in the lines.
| | 01:26 |
We look at that one, in fact we could
zoom in a little bit and get a better
| | 01:29 |
view of it.
We can see there the low point in the
| | 01:32 |
line is marked as well as the high
points.
| | 01:34 |
If we're happy with that sparkline, and
we'd like to see similar ones for the
| | 01:38 |
other birds, we don't have to repeat the
process, we can just use the Fill tool.
| | 01:43 |
And drag down here, and it pops
sparklines in for each of the birds.
| | 01:48 |
And you can see these trends are really
different for the different birds.
| | 01:52 |
So that's the line sparkline.
We could change that if we wanted to and
| | 01:56 |
look at a different type of sparkline in
there, and the column line would probably
| | 02:00 |
be just as appropriate.
I'm just going to click on that, and you can
| | 02:04 |
see it instantly changes to columns.
We still see highlights for the high
| | 02:08 |
points and the low points.
We have different colors, and again, we
| | 02:12 |
could choose different colors to express
the data if we want to.
| | 02:17 |
And we could mark first and last points
if we want to, that kind of thing.
| | 02:22 |
So that's the way the sparkline works.
We didn't look at this last type of
| | 02:28 |
sparkline, that's called the Win Loss
Sparkline.
| | 02:32 |
We didn't look at this last type of
sparkline called the Win Loss Sparkline.
| | 02:35 |
And it's great to identify positive and
negative numbers in a range.
| | 02:40 |
So let's look at this bottom table down
here.
| | 02:43 |
We're looking at donations and comparing
that to the staff expenses to see if we
| | 02:47 |
come out ahead or behind each month.
So to use a sparkline to help illustrate
| | 02:53 |
that point, I can go over here to my
cell, again, choose Insert.
| | 02:59 |
Go to my Sparklines group, choose Win
Loss, and once again I need to show my
| | 03:04 |
range of data and then hit enter and we
get our win loss sparkline which
| | 03:09 |
unfortunately shows us losing more than
it does winning.
| | 03:16 |
But that's how sparklines work, and they
won't lie, they'll tell the data as is
| | 03:20 |
really is.
Now the one other thing you might want to
| | 03:23 |
know about sparklines is you can't just
press a delete key and get rid of them.
| | 03:27 |
What you need to do for sparklines is to
go back to the sparkline tools.
| | 03:33 |
Select your range that has the sparklines
in it.
| | 03:35 |
And then over in this group over here,
you need to choose Clear.
| | 03:38 |
So if you ever want to get rid of your
sparklines, that's the process.
| | 03:43 |
Go Design, and then go over to the group
and choose Clear.
| | 03:46 |
So this lesson introduced sparklines,
small data words that you can insert into cells.
| | 03:54 |
They provide snapshot insight into your
data, and you can format sparklines to
| | 03:57 |
display details, such as the high and low
points in your data.
| | 04:02 |
| | Collapse this transcript |
| Inserting photos, graphics, and clip art| 00:02 |
Excel worksheets can be pretty dull
affairs if you don't dress them up a bit.
| | 00:06 |
Now you can use colors and formatting, to
make those columns and rows of numbers
| | 00:09 |
stand out.
But when something more is called for
| | 00:12 |
consider adding photos, graphics and Clip
Art to your worksheets.
| | 00:17 |
Here's how you do it.
Look at this table.
| | 00:19 |
We've got a table of numbers here that
shows tire sales in different cities,
| | 00:23 |
different locations around the country.
And perhaps we'd like to add a photo that
| | 00:29 |
would help explain what's going on here.
To do that we go to the Insert Tab and we
| | 00:34 |
look in the Illustrations group.
There are several different ways you can
| | 00:38 |
add illustrations to your worksheet, and
we will look at all of them.
| | 00:41 |
But first lets look at Picture.
I click on the Picture Tab, it opens this
| | 00:46 |
window, and it initially opens up in the
My Pictures Group, and you can move
| | 00:50 |
around and choose a different folder if
you want to.
| | 00:55 |
What, but, what I want is right here.
So I'm going to choose my photo from this list.
| | 01:00 |
I could scroll down and see other ones.
I'm going to click Insert.
| | 01:03 |
And then it puts my photo on the
spreadsheet.
| | 01:06 |
A lot of times your photos are going to
come in too large, just because that's
| | 01:10 |
the way the cameras shoot them.
It doesn't need to be that large for a spreadsheet.
| | 01:15 |
So what I'm going to do is grab the corner
here, and drag it to shrink it down, and
| | 01:19 |
you can see, when I do it from the
corner, it shrinks my photo proportionally.
| | 01:26 |
If I grab one of these edges, it's going
to stretch it.
| | 01:29 |
Same thing if I grab the top or bottom,
it can distort the picture a little bit,
| | 01:33 |
so to size it and keep it proportional
you want to use the corner.
| | 01:39 |
The other thing you can do to is you can
spin it around if you have a need to do
| | 01:42 |
that, and you do that with this little
green handle up at the top.
| | 01:46 |
Don't think we need that in this case,
but we'll just put our photo a little bit
| | 01:51 |
above our data, and we'll size it, so it
looks good and everything shows.
| | 01:58 |
And now we've got a dressed up table that
explains that we're talking about tires here.
| | 02:04 |
Clip Art's another type of illustration
that you can add to your worksheet.
| | 02:09 |
So here we have an invoice.
It'd be nice to add something to the
| | 02:12 |
letterhead, and the name of the company
is Turtle Transport, so you can guess
| | 02:16 |
what I'm going to add here.
The thing to do is go up to insert again,
| | 02:21 |
choose Clip Art, and Clip Art is artwork
that comes with Excel and the other
| | 02:25 |
Office programs, and you can search
through your Clip Art.
| | 02:30 |
For example we've got turtles up here
now, but suppose we wanted to look at cars.
| | 02:35 |
We could click on cars and it would show
us a variety of different cars and you
| | 02:39 |
could scroll through the options.
You have some control over what it's
| | 02:45 |
going to show, so for example with cars,
we could choose just to see photographs.
| | 02:51 |
And with a little time, it would just
show us photos instead of drawings of cars.
| | 02:56 |
So let's go back and look for our turtle.
So here we have several different
| | 03:02 |
drawings of turtles.
I know the one that I want's going to be
| | 03:08 |
down toward the bottom, and it's an
illustration.
| | 03:15 |
.
When I click on it, it show up in my
| | 03:20 |
worksheet, and all I have to do is drag
it into place.
| | 03:28 |
If I want to re-size it, it works just like
the photo artwork.
| | 03:35 |
You can drag a corner, or you can drag it
from one of the edges, and you can spin
| | 03:41 |
it around.
But in this case, I think he's looking
| | 03:45 |
pretty good there.
Now, in addition to photos and Clip Art,
| | 03:49 |
you can also use shapes and it works
exactly the same way.
| | 03:54 |
Just go Insert then go to the Shapes menu
and you can see all these shapes that are
| | 03:57 |
available to you.
Click on one of them, and drag it out to
| | 04:02 |
add it to your worksheet.
You can re-size it in the same way, you
| | 04:07 |
can rotate it if you want to.
One of the handy things that you'll find
| | 04:13 |
in the shapes menu are arrows.
And arrows are always handy to highlight
| | 04:18 |
particular points.
So you can drag an arrow from one place
| | 04:22 |
to another.
Then once you've added your artwork, you
| | 04:26 |
can use the format tools to change its
appearance.
| | 04:30 |
Down here I can make my arrow bolder.
We can choose different colors if we want
| | 04:35 |
to, different outlines.
So you have some variety in the
| | 04:39 |
appearance, so you add the basic shape
and then you can format it the way you
| | 04:44 |
want to.
There's another bit of art that is called
| | 04:48 |
Smart Art.
It also includes drawings.
| | 04:51 |
But Smart Art works a little bit
differently.
| | 04:54 |
These are preformatted.
Bits of art that are set up to be very
| | 04:58 |
adaptable, and they're set up to accept
text.
| | 05:03 |
So you can see we have different
categories over here.
| | 05:05 |
You can have Lists or Processes.
If you want to see what one looks like
| | 05:09 |
just click on the Thumbnail and you'll
see an example of it over here.
| | 05:14 |
You have different cycles which are handy
to illustrate your point.
| | 05:19 |
Relationships, you might use this for
office or business relationships,
| | 05:24 |
pyramids we've seen that one used for
food groups.
| | 05:31 |
Let's look at one of these smart art
examples in place.
| | 05:35 |
Here's one that I've entered already into
the spreadsheet.
| | 05:39 |
If I click on it, you can see how you go
about formatting it.
| | 05:43 |
You can just type in the text in this box
over here and it will appear in your clip
| | 05:47 |
Clip Art, and your Clip Art will
automatically re-size to accept the text
| | 05:50 |
that you type in.
So, for example, say we want to add
| | 05:55 |
another field office, all we have to do
is go in there, press enter.
| | 05:59 |
We get our new box in here.
I can type in the name of the office.
| | 06:09 |
And it's just as simple as that to add a
new field office.
| | 06:13 |
Suppose, say Atlanta closed down, you
could delete that, just by going in here
| | 06:18 |
and deleting it.
So Clip Art is very adaptable and again
| | 06:24 |
once you have it you can use the design
tools to change the colors, or change the style.
| | 06:35 |
And just by pointing to any of these you
can get a preview as usual with Excel.
| | 06:43 |
So those are some of the ways that you
can add artwork to your worksheet and
| | 06:46 |
dress them up a little bit.
In this lesson, you saw that by using the
| | 06:51 |
Insert tab, you can add photos, Clip Art
and Smart Art to your worksheets.
| | 06:57 |
And with the help of the formatting
tools, you can make that artwork look the
| | 07:00 |
way you want.
| | 07:01 |
| | Collapse this transcript |
| Using an image for a header| 00:00 |
If you plan on distributing a worksheet
to customers or employees, you may want
| | 00:04 |
to make it look more formal, make it
match your business letterhead.
| | 00:10 |
And in that case you probably want to add
your logo and address and other details
| | 00:13 |
to the header of the page.
That's the natural place for these
| | 00:17 |
elements, and that's the topic of this
lesson.
| | 00:21 |
So here we have a billing timesheet for
employees where they keep track of the
| | 00:24 |
hours they spend working on projects, and
it calculates the minutes and the fees
| | 00:28 |
that are involved in that.
And it would be natural just to put the
| | 00:32 |
letterhead up at the top of this page.
We're looking at the page view right now,
| | 00:36 |
so you see it as separate pages.
So, what I want to do is go to Page Layout,
| | 00:41 |
where I see the different options for
page setup.
| | 00:47 |
So we can see things like margin and
orientation and page size.
| | 00:50 |
But what we don't see in here are any
details for doing page headers.
| | 00:55 |
So I'm going to dig down a little bit
farther.
| | 00:58 |
I'm going to go to this expand button
down here in the lower right corner of
| | 01:01 |
the page set up tab.
And open up this dialog box and in here
| | 01:07 |
we see more details on all these items.
And in addition we see a tab that's,
| | 01:12 |
header and footer.
When I click on that, I see a little box
| | 01:17 |
that shows what will appear in the header
or the footer.
| | 01:22 |
And they're actually both done pretty
much the same way.
| | 01:26 |
We're just going to look at headers, but
you can apply what you've learned here to
| | 01:28 |
the footer as well.
There are some predesigned options in here.
| | 01:33 |
So you can do page numbers or page one of
12, something like that.
| | 01:39 |
So there's several different options here
that are sort of prepackaged that Excel
| | 01:42 |
gives you, but that's not what we want.
We want to customize our header.
| | 01:47 |
So I'm going to click the Custom Header
button here, and it divides our header
| | 01:51 |
into three sections.
What appears on the left side, what
| | 01:55 |
appears in the middle, and what appears
on the right.
| | 01:58 |
And it has these handy buttons in here,
for inserting specific things.
| | 02:03 |
So I can insert page numbers or the
number of pages.
| | 02:06 |
I can insert a date and time.
All of these things can be really helpful
| | 02:10 |
if you're developing your header, but
what we want is something custom.
| | 02:15 |
What we want to do is over here on the
right, and it's Insert Picture.
| | 02:19 |
So I'm going to click that button and a
window opens up where I can go into my
| | 02:23 |
pictures folders and pick out the item
that I want to insert.
| | 02:29 |
And in our case our graphic arts
department has already provided us letterhead.
| | 02:35 |
So all I need to do is click on that, and
then click Insert.
| | 02:40 |
And click OK a couple of times.
You can see it says picture in here so we
| | 02:44 |
know that something happened there.
If we look at this box, we can see it
| | 02:49 |
shows the little top of our picture in
there.
| | 02:52 |
I'm going to click OK.
And once I do that, we've got our
| | 02:56 |
pre-designed letterhead appearing on our
worksheet.
| | 03:04 |
So you can see it here while we're in
Page view.
| | 03:06 |
Now keep in mind, if we go over to the
normal worksheet view, it's not going to
| | 03:10 |
show up, but it does show up when we go
to our Page Layout view.
| | 03:15 |
And I'm going to zoom out a little bit.
You can see when you're in Page Layout
| | 03:19 |
view you see several pages for your
worksheet.
| | 03:22 |
If I go over here and click in one of
these as if I'm going to add new data to
| | 03:27 |
these sheets, you can see it
automatically adds our letterhead to each
| | 03:31 |
new page that I create.
If we go over here to Print and look at
| | 03:37 |
the print preview you can see our
letterhead in our print preview.
| | 03:43 |
And we can see how our page would print
out.
| | 03:45 |
So in this lesson you saw how to add
elements to your worksheet header.
| | 03:50 |
You can add Excel's preformatted texts
such as page number or title or you can
| | 03:54 |
create custom elements which can include
artwork or other predesigned elements
| | 03:58 |
that you have in store.
| | 04:01 |
| | Collapse this transcript |
|
|
7. Analyzing DataCreating a data table| 00:02 |
Excel is designed for number crunching,
but it has some pretty impressive tools
| | 00:05 |
for simply storing and retrieving data,
and it doesn't have to have anything to
| | 00:09 |
do with numbers.
This lesson shows how to turn rows of
| | 00:14 |
data into a table.
And now it's not hard to think of uses
| | 00:18 |
for this feature.
Excel can store the names, addresses, and
| | 00:22 |
contact details of friends and
colleagues, just like a, an address book.
| | 00:27 |
Or you might want to develop a household
inventory for insurance purposes.
| | 00:31 |
Basically anything that you'd store in a
list, you can store in Excel, and then
| | 00:36 |
you can use Excel's Table tools to get
access to that information.
| | 00:41 |
So lets consider this table here that
we've got.
| | 00:45 |
It's a pretty simple group of cells, but
it is organized.
| | 00:49 |
And up at the top, we have a row of
categories.
| | 00:53 |
Title, genre, director, writer, and
actor.
| | 00:56 |
So these are the categories that you
might have if you were making a list of movies.
| | 01:00 |
That's in our header, but then down below
we have an individual movie.
| | 01:05 |
This is Red River, it's a western, Howard
Hawks was the director, Gordon Chase was
| | 01:10 |
the writer, and John Wayne was the actor.
Each of these items in, database lingo,
| | 01:15 |
would be called a record.
So each row has information that belongs together.
| | 01:21 |
If you were doing a card file, you might
put all these details on a single card
| | 01:25 |
and have a different card for each of
these movies.
| | 01:30 |
Each row is a record, and each one of
these columns would be referred to as a
| | 01:34 |
field in database lingo.
So the title there would be a title
| | 01:39 |
field, a genre field, director field and
so forth.
| | 01:43 |
So that's the beginnings of what could be
a table in Excel.
| | 01:48 |
Now the way to turn it into a table is to
select the group.
| | 01:54 |
Then go over here and choose Insert, and
click on Table.
| | 01:58 |
This dialog box pops up with the
question, where is the data for your table?
| | 02:03 |
Well, we already selected the area that
we want in the table, so that job is done.
| | 02:08 |
Now, this other little check box here
says, my table has headers.
| | 02:12 |
And that's true, our table has these
headers which are title, genre, director
| | 02:16 |
and so forth.
So I'm going to check that box and click OK.
| | 02:20 |
When that's done, it really transformed
the cells in here.
| | 02:25 |
You can see these cells have little drop
down menus, and then these cells are
| | 02:30 |
formatted with banded rows different
colors.
| | 02:34 |
And you can see up here we've got Table
tools, which let us control some of these features.
| | 02:42 |
I can turn the header row off by
unchecking that box.
| | 02:45 |
I could turn the banded rows off by
unchecking that box.
| | 02:50 |
You could have banded columns by checking
that one.
| | 02:53 |
So these are really just kind of
formatting details here and as usual you
| | 02:57 |
can point to different table styles and
get a different appearance.
| | 03:02 |
But the important thing is that Excel
considers this a table, and that gives us
| | 03:06 |
some options that we wouldn't have
otherwise.
| | 03:10 |
After you've created a table, if you
decide you want it to go back to being
| | 03:14 |
just regular Excel cells, you can do that
by going up here to Convert Range.
| | 03:21 |
Click on that and it's asking us do you
want to convert this to a normal range of cells?
| | 03:28 |
And we say yes, and it goes back to
normal cells without the drop down menus.
| | 03:33 |
Now the formatting still exists, but we
can turn that back off too but with our
| | 03:37 |
Format Painter.
So that's how to create a table, and how
| | 03:42 |
to turn a table back into regular cells.
In this lesson you saw how to use the
| | 03:49 |
Insert Table command to turn the cells in
a worksheet into a table.
| | 03:53 |
| | Collapse this transcript |
| Adding records and modifying a data table| 00:02 |
You can add data to a table after you
created it, and you can continue to make
| | 00:05 |
changes to the structure of the data,
too.
| | 00:09 |
This lesson shows you how.
So here we have a data table that we
| | 00:12 |
created using the Insert Table command,
and then we formatted these cells,
| | 00:17 |
entered it into a data table.
At the top, we have fields.
| | 00:23 |
So it has the title of a movie, the
genre, the director, the writer, and the actor.
| | 00:27 |
And then each row represents a different
movie.
| | 00:30 |
So, these are records in the database
that we've got going here.
| | 00:35 |
Now if we wanted to add a new record, we
could go in here and you can insert a row.
| | 00:42 |
And that expands the database, and gives
us a new row where we could put in data.
| | 00:48 |
The other thing you can do if you're at
the end of the database, you can see it's
| | 00:51 |
the end because it has this little mark
in the the lower right corner here.
| | 00:56 |
When you're at the end of a database, hit
the Tab key.
| | 00:59 |
It automatically gives you a new row,
where you can add more data.
| | 01:05 |
And you just continue to hit Tab and add
data as you work along.
| | 01:09 |
So it gives you a fast way to keep on
adding records to your database, if
| | 01:13 |
that's what you wanted to do.
You could also delete records from your database.
| | 01:23 |
So I can select this row, and
right-click, and choose Delete, and that
| | 01:27 |
deletes the row.
Now, the other thing that you can do is
| | 01:32 |
you can change the structure of your
database, suppose we wanted to add
| | 01:35 |
another actor to our database.
We could go in here, and select a column,
| | 01:40 |
and right-click and choose insert, and
now we have a new column.
| | 01:46 |
We can give that a name, we could call it
actor also.
| | 01:50 |
And you notice when we do that, it can
only have one actor in the database, so
| | 01:54 |
it automatically renames this one actor
two.
| | 01:58 |
But then we could add more actors into
this column here.
| | 02:02 |
And likewise, if you wanted to delete one
of these fields in your database, all you
| | 02:07 |
have to do is select the column, with the
field, and then go up here and choose Delete.
| | 02:13 |
And that's the way you can change the
underlying structure for the table in
| | 02:17 |
your database.
So in this lesson, you saw how to add new
| | 02:20 |
records to a database, how to delete
records if you want to.
| | 02:25 |
And you also saw how to add fields and
delete fields from your data table.
| | 02:30 |
| | Collapse this transcript |
| Sorting and filtering data in a table| 00:02 |
So the question is, how is an Excel table
different from just a bunch of cells?
| | 00:06 |
Well the main difference has to do with
the built in tools that Excel provides to
| | 00:09 |
help you work with tables.
At the top of that list are the tools
| | 00:13 |
that let you sort and filter data.
So let's take a look at those options in
| | 00:18 |
this lesson.
Here's a database that was created in a
| | 00:21 |
normal fashion.
We selected all of the cells that we
| | 00:25 |
wanted and we went up to Insert and Table
and it turned our cells into a table.
| | 00:32 |
And we could tell that, because we have
these drop down menus at the top and
| | 00:36 |
these are actually the tools that we use
to do our sorting and filtering.
| | 00:42 |
So for example, if we got our titles here
and then genre and director, each one of
| | 00:46 |
these would be known as a field, and then
the rows across would be known as records.
| | 00:53 |
So each movie gets it's own row.
The title and a director and the cast and
| | 00:57 |
the other people.
Say we want to sort this by title
| | 01:01 |
alphabetically, we just come to this list
here and choose Sort A to Z from the list.
| | 01:07 |
And it sorts it so that numbers come
first, then As, then Bs and you can see
| | 01:11 |
all the titles are sorted.
And of course the data related to those
| | 01:17 |
titles stays with the title so
everything, all those rows reorganize
| | 01:21 |
themselves so everything about this
movie, A New Leaf, is on that line and
| | 01:25 |
that's how you do the sorting.
You even have this little indicator here,
| | 01:32 |
you can see a little tiny up arrow,
that's showing that this is being sorted
| | 01:36 |
from lowest to highest, and if we wanted
to sort by something else, say, perhaps
| | 01:41 |
we want to sort by the dates over here.
We could go over here, and choose this.
| | 01:48 |
In this case, we might want the more
recent movies at the top of the list.
| | 01:52 |
So I'm going to do largest to smallest.
And it sorts by the date with the largest date.
| | 01:58 |
Biggest number at the top and our list is
completely reorganized.
| | 02:03 |
So that would make it easy to find things
by movies or by actors or anything.
| | 02:08 |
And you have to keep in mind that your
worksheets in Excel can have a million rows.
| | 02:14 |
So that means you can have a database, a
flat database like this, with a million
| | 02:18 |
different records and sort it very
quickly to find what you need.
| | 02:24 |
In addition to sorting it, you can filter
it.
| | 02:27 |
So you can filter it so you just see
specific information.
| | 02:31 |
So for example, let's say we want to
filter by director.
| | 02:34 |
We want to just choose a couple directors
in here and see their work.
| | 02:38 |
I open up this menu, right now it says
Select All, so it's showing the names of
| | 02:42 |
all the directors, and there are check
marks by all of their names.
| | 02:47 |
I'm going to turn that off, now none of them
are checked.
| | 02:52 |
And we'll choose say Howard Hawks and
John Houston here.
| | 02:56 |
And then we'll click OK, and, if we look
at this list of directors, we can see all
| | 03:00 |
the movies are either John Houston or
Howard Hawks.
| | 03:04 |
We can use our filter to sort it and put
Howard Hawks at the top and John Houston there.
| | 03:09 |
Can actually filter on multiple fields at
a time, so we could see we've got our
| | 03:14 |
sort arrow there.
We also have a little filter indicator
| | 03:19 |
there showing that the movies are
filtered by the director.
| | 03:25 |
If we go over here and look at the row
numbers, you can see that some of the row
| | 03:28 |
numbers are missing, which is also
another indicator that a filter is at work.
| | 03:33 |
It's filtering out some of the movies.
Let's go down here by date and add some
| | 03:39 |
more filters.
So right now we have Select All so it's
| | 03:44 |
showing all of our dates.
I'm going to go in and we'll get rid of some
| | 03:49 |
of these recent years so that we're just
looking at movies that are earlier than
| | 03:55 |
the 1960s.
So now it filters out our movies.
| | 04:03 |
We just have dates from the 40s and the
50s.
| | 04:08 |
And if we go over here, we've got our
directors.
| | 04:11 |
It's still sorted by director and
filtered by director.
| | 04:14 |
And you can imagine how easy that makes
it to zero in on the pieces of your data
| | 04:19 |
that you want to look at.
If you want to turn these filters off
| | 04:24 |
that's easy too.
We can just go in here and we can clear
| | 04:28 |
directors from the filter so the
directors are taken out.
| | 04:33 |
And we're seeing movies sorted by
director and we're still filtered by the date.
| | 04:41 |
So we're only seeing some of our movies
in here.
| | 04:44 |
We can go ahead and turn this other
filter off too, and now we're back to all
| | 04:49 |
of the movies in our list.
So in this lesson you saw how you can
| | 04:54 |
sort and filter the information that's
stored in an Excel table.
| | 04:58 |
It's as easy as making a couple of
choices from a drop down list.
| | 05:02 |
| | Collapse this transcript |
| Importing data from an outside source| 00:02 |
You won't always want to create a data
table from scratch that's a lot of work.
| | 00:06 |
In many cases you can save time by
importing your data from another source.
| | 00:11 |
This lesson shows how to import data that
was saved in the comma seprated value
| | 00:16 |
file format that's a standard file
format.
| | 00:20 |
That you might use if you exported data
from Microsoft Access or some other
| | 00:24 |
database program.
So let's take a look.
| | 00:29 |
Here we have an empty workbook, with a
single sheet in it and there's no data at all.
| | 00:34 |
And we want to import data from an
external file.
| | 00:39 |
So I'm going to go up here to the ribbon,
over to the data tab.
| | 00:43 |
And then on the left side, we see several
get external data options.
| | 00:48 |
So, if I had an access database on my
computer, I could go up here and click
| | 00:52 |
access, then navigate to that database
and find it.
| | 00:57 |
And likewise if your company stores data
on the web, or in some other database in
| | 01:01 |
a local area network, you can use on of
these options.
| | 01:06 |
Now, you probably need some help and some
information from the people that manage
| | 01:09 |
your database, to know exactly what to
get and how to bring it into your program.
| | 01:15 |
But in our case we're going to look at
bringing data in from a text file.
| | 01:19 |
And that's a common way that you exchange
data between one program and another,
| | 01:23 |
that maybe don't speak exactly the same
language.
| | 01:27 |
But by storing it as a text file any
program can bring that in and read it and
| | 01:31 |
translate it, and turn it into useful
data.
| | 01:36 |
So that's what we're doing here.
We're going to click on, From Text.
| | 01:40 |
Now it opens up this file window where I
can navigate to different folders and
| | 01:43 |
find files.
If we scroll down here a little bit we
| | 01:47 |
see this file called table of DVDs, and
it ends in .csv for comma separated value.
| | 01:54 |
That's the one we want.
I'm going to choose that file, and I'm going to
| | 01:58 |
click on import, and that brings up this
window.
| | 02:02 |
It's the Text Import Wizard, and it's
step one of three.
| | 02:06 |
That means we have to make a couple of
decisions as we move along to bring this
| | 02:10 |
data into our Excel worksheet in the
proper format.
| | 02:15 |
First of all we've got two choices.
One says the delimited, characters such
| | 02:19 |
as commas or tabs separate each field.
That's the option that we want to use.
| | 02:25 |
Our data is separated by commas.
Another common format is, use fixed width.
| | 02:31 |
And in that case every field has a
certain defined character width, a
| | 02:35 |
certain number of characters in each
field.
| | 02:39 |
And that's the way they figure out where
one field ends and another field begins.
| | 02:44 |
But we're going to choose this first option.
And then we can see our data is sort of
| | 02:48 |
down here, not really divided up, but
it's divided into rows.
| | 02:52 |
I'm going to click Next.
Gives us some more choices.
| | 02:57 |
This screen lets you set the delimiters
your data can change.
| | 03:01 |
Well, we know ours uses commas.
You can turn tabs off.
| | 03:05 |
And now when we make that choice, we can
see, now it's really divided our text
| | 03:09 |
into different fields and we can see how
it's breaking out, and it does look like
| | 03:14 |
it's correct.
Click Next, again.
| | 03:20 |
And it's asking us which format each of
these fields should be in.
| | 03:23 |
Well, all of these fields really are
text.
| | 03:27 |
The only numbers in here are the dates,
and in a way, it's fine if that's treated
| | 03:31 |
as text too.
If you look at the options up here, we
| | 03:35 |
have text, and date, and the general tab.
Now it says general converts numeric
| | 03:40 |
values to numbers, date values to dates,
and all remaining values to text.
| | 03:46 |
Well that's going to work just fine for
this database and the data that is inside
| | 03:50 |
of it.
So I'm going to click finish, and its
| | 03:54 |
going to ask me, where do I want to put the
data?
| | 03:58 |
Well, let's start right up here in the
corner up there.
| | 04:01 |
Now, click Okay and it enters all my data
into the worksheet.
| | 04:07 |
You can see that there's quite a bit of
it.
| | 04:09 |
It's a pretty good database of movies,
pretty good size.
| | 04:15 |
It's not really formatted as a table yet.
So we probably want to take the extra step
| | 04:20 |
of selecting all the data.
And using our Insert Table command to
| | 04:28 |
convert it to, a database.
There we've got our database.
| | 04:37 |
It's formatted in alternating rows.
And we've got our drop down menus up here.
| | 04:44 |
That we can use to sort and filter the
data.
| | 04:48 |
So it's that easy to bring data in from
an outside source.
| | 04:52 |
In this lesson, you saw how to import
data from a Text file, and you saw how to
| | 04:56 |
convert the imported data into an Excel
data table.
| | 05:01 |
| | Collapse this transcript |
| Grouping data in outlines| 00:02 |
Sure it's good to have all that detail
behind your numbers.
| | 00:05 |
But sometimes, less tells more of the
story.
| | 00:08 |
When your data fits neatly into groups
you can use outlines to show and hide as
| | 00:12 |
much as you want.
Here's how it works.
| | 00:16 |
We've got a spreadsheet here that shows
sales and expenses, and then calculates
| | 00:20 |
whether there's a profit or a loss, and
it keeps track of the information each
| | 00:23 |
month and then totals it for the
quarters.
| | 00:27 |
Well, sometimes you may want to just see
the quarters and you don't really need to
| | 00:31 |
see the monthly detail of this.
So, the thing to do is to go to your Data
| | 00:36 |
Tab, look over here in the outline group
where we have the group commands.
| | 00:43 |
So what we're going to do is select three
columns for the three months, January,
| | 00:47 |
February, and March, and we're going to
group them, and when we do that you can
| | 00:51 |
see that there's a bar above the group.
And it's got this little minus button on
| | 00:58 |
it right now.
When I click that, it hides the group,
| | 01:01 |
and all we see is the quarter, and it
turns into a plus button.
| | 01:06 |
And I can click that to expand it again.
So, when you do that for something like
| | 01:09 |
this you probably want to do it for all
of the columns.
| | 01:14 |
So we select our columns and then click
Group, and, you want to click in the top
| | 01:18 |
here where the letters are to select the
columns.
| | 01:22 |
Now we have four groups, one for each
quarter, and we can show and hide them,
| | 01:26 |
as we want to, or we can use the buttons
over here.
| | 01:31 |
One stands for the first level of the
group, and two stands for the second
| | 01:35 |
level of the group.
And if we wanted to put groups inside of
| | 01:39 |
groups, we could do that as well.
You can go down to eight levels of groups
| | 01:43 |
nested inside of other groups in this
organization.
| | 01:48 |
The other thing that you can do is you
can group rows as well as columns.
| | 01:52 |
We're looking at grouping columns over
here, but here's another work sheet that
| | 01:56 |
is grouping rows.
And what we've got here is we have
| | 02:01 |
products, we have books magazines, DVDs
and music, are the major groups, and the
| | 02:06 |
sub data inside of it are locations where
these are stored.
| | 02:12 |
Locations, Mission Boulevard, Main Street
and Fourth Street.
| | 02:15 |
So we can see all of the books and all
the detail where they're sold, or we can
| | 02:20 |
hide the location detail, and just see
the totals for each of these items that
| | 02:25 |
we're selling.
Now there may come a time where you want
| | 02:32 |
to remove a group, and let's go back to
our original worksheet here.
| | 02:38 |
So say we decide we don't need groups
here anymore.
| | 02:41 |
The thing to do here is to select the
group, and then click the Ungroup button
| | 02:44 |
and then it disappears.
You can do that individually or you can
| | 02:48 |
select a whole bunch of them at once, and
click Ungroup and they'll go away.
| | 02:53 |
So in this lesson you learned how to
group your data so that you can show and
| | 02:57 |
hide it using the outline totals.
You saw that you can group both columns
| | 03:02 |
and rows of data, and you saw how to
ungroup your data if you don't want it in
| | 03:06 |
a group any longer.
| | 03:09 |
| | Collapse this transcript |
| Building a pivot table| 00:02 |
It's amazing how much you can learn from
a different perspective.
| | 00:05 |
And that's true when it comes to numbers
in a worksheet too.
| | 00:08 |
Excel's pivot table feature makes it easy
to get a different view of the numbers
| | 00:12 |
and data in your worksheet.
Pivot tables work particularly well when
| | 00:17 |
you have a worksheet like this one, where
you have information that's related, but
| | 00:21 |
it's not necessarily apples and apples.
For example, here we're tracking the
| | 00:27 |
driving, loading and servicing hours for
a particular track, and we have the track
| | 00:32 |
size, we have the revenue generated by
that truck on runs to particular destinations.
| | 00:40 |
And we're also tracking the year for the
trip.
| | 00:43 |
So, these things all hang together but
they aren't exactly the same kind of items.
| | 00:49 |
So this is the kind of, data where it
helps to reorganize it and look at it in
| | 00:53 |
different combinations.
I'll show you what I mean.
| | 00:57 |
To create a pivot table, we're going to
select the data in the table, we go over
| | 01:02 |
to Insert, in this first group here has
pivot tables.
| | 01:08 |
So, we click on that.
This dialog box appears, and it asks
| | 01:13 |
what our range is.
We've already selected that, so we don't
| | 01:16 |
need to.
Then it's asking us where do we want to put
| | 01:19 |
the pivot table.
Generally new worksheet is a good option
| | 01:23 |
because it gives you a clean worksheet to
operate with.
| | 01:28 |
Your other option is to put it in the
existing worksheet, that would be, mean
| | 01:32 |
putting it in with the table as it
already exists.
| | 01:36 |
Let's go with the new worksheet.
When we click New Worksheet.
| | 01:40 |
We see instructions over here, and then
the tools to build our pivot table over here.
| | 01:46 |
So the instructions say to build a
report, choose fields from the pivot
| | 01:50 |
table list.
So here are the fields in the list, and
| | 01:54 |
we've got different little boxes down
here where we can put them.
| | 01:58 |
First of all, let's choose Destination as
a category, and you can see all of a
| | 02:04 |
sudden we have rows with Destination on
the label.
| | 02:10 |
So we could choose another item like Net
Revenue, and that would appear in another
| | 02:14 |
row, and we get the sum of Net Revenue
for each of these Destinations, and a
| | 02:18 |
Grand Total.
That's kind of helpful.
| | 02:23 |
We could choose truck size.
And all of a sudden under our
| | 02:27 |
destinations, we see the different sizes
of trucks that were used, and it breaks
| | 02:31 |
out the information on the net revenue.
That's all helpful.
| | 02:38 |
We could go ahead and keep clicking on
these items and we'd see summaries of the
| | 02:42 |
driving hours, the loading hours, and the
servicing hours, all broken down into
| | 02:46 |
these major categories.
Now the other option that you can do is
| | 02:54 |
to drag an item down to, one of these
areas.
| | 02:58 |
So we have column labels over here, and
row labels over there.
| | 03:02 |
So if we drag, the year down to row
labels, we are going to get another
| | 03:06 |
breakout where the year, is shown for
each of these categories.
| | 03:13 |
So we have destination Truck size and
then the year, and it's all calculated
| | 03:17 |
across here.
Now these are all collapsable fields too.
| | 03:22 |
So we can click on one of these items to
collapse or expand it.
| | 03:28 |
So that's how pivot tables work.
You can learn a lot by experimenting with
| | 03:32 |
different categories.
For example if we wanted to remove the
| | 03:36 |
year from this, all we'd have to do is
click on this menu, and then say remove fields.
| | 03:42 |
And we could put years over in the
column labels, which isn't quite as helpful.
| | 03:47 |
It's a little bit more confusing that
way.
| | 03:49 |
But that's the kind of versatility that
you get with pivot tables.
| | 03:57 |
The other thing that you can do with the
pivot table is you can create a chart in
| | 04:00 |
pretty much the same manner.
Let's go back to our original table, and
| | 04:06 |
we go to insert.
Pivot table.
| | 04:09 |
Click on this Arrow button out of the
bottom of pivot table.
| | 04:12 |
And we'll choose Pivot Chart.
And we get the same questions.
| | 04:16 |
What range are you selecting?
So the range is already pre selected in there.
| | 04:20 |
And we'll go ahead and we'll put our
chart on a new worksheet also.
| | 04:24 |
So I've clicked okay there.
And it's pretty much the same set up here.
| | 04:29 |
We can choose items here.
And as we check items off of this list,
| | 04:35 |
it creates a table, and it creates a
chart based on that information.
| | 04:44 |
So, pivot tables give you a way to
rearrange data in a preexisting table and
| | 04:49 |
look at it in different ways.
In this lesson, you learned how to get a
| | 04:54 |
different view of your data by creating a
Pivot Table, you saw you can add and
| | 04:58 |
remove items in your Pivot Table.
| | 05:01 |
| | Collapse this transcript |
| Highlighting data with conditional formatting| 00:00 |
With conditional formatting, you can get
your numbers to tell their story.
| | 00:05 |
In this lesson, you learn how to apply
cell and font colors based on the values
| | 00:09 |
stored inside of a cell.
You'll also be introduced to some of
| | 00:14 |
Excel's predesigned conditional formats.
So, here we have a sample spreadsheet.
| | 00:21 |
Which includes numbers that are both
negative numbers and positive numbers.
| | 00:25 |
The concept here is that this is a
bookseller, and they're keeping track of
| | 00:29 |
books that are sold.
The negative numbers represent where more
| | 00:33 |
books were returned than were sold.
So basically, you're seeing the net
| | 00:38 |
number of sales or returns here.
So you have positive and negative
| | 00:42 |
numbers, and it would be nice to
highlight the negative numbers in here in
| | 00:45 |
some way.
And you can do that with conditional formatting.
| | 00:49 |
So to find the conditional formatting
controls, go to the Home tab, then over
| | 00:53 |
here to this style group there's a
Conditional Formatting menu.
| | 00:57 |
And if you click on that, you'll see that
there are several different options.
| | 01:02 |
So the first thing to do is to select the
area that we want formatted, so I'm
| | 01:06 |
selecting all these numbers.
And let's set up a format that
| | 01:12 |
highlights the negative numbers.
So I'm going to go up here to Highlight
| | 01:17 |
Rules, and then you can see we have
greater than, less than, between, equal to.
| | 01:23 |
Those are the conditions that we're going
to apply.
| | 01:26 |
So we're going to apply less than zero.
So, this little box appears after I click that.
| | 01:33 |
And it's got a number in there already,
but we're going to change that to zero.
| | 01:38 |
So format cells that are less than zero
to.
| | 01:42 |
And then we have several different color
options here.
| | 01:45 |
We can use red text, or I'm going to use
light red fill with dark red text,
| | 01:49 |
that'll make negative numbers look very
red.
| | 01:54 |
Once I do that, I'm going to click outside
here.
| | 01:57 |
All of a sudden, all the negative numbers
really pop out, and we can see them very,
| | 02:02 |
very quickly.
We can get an impression of how they're
| | 02:07 |
blocked together.
And that's why I say that conditional
| | 02:10 |
formatting really helps to tell the story
behind the numbers, and any time you can
| | 02:14 |
do that it just makes it easier to
understand what's going on in your worksheets.
| | 02:19 |
Let's do one more, let's select the same
group of cells, and we're going to
| | 02:23 |
highlight the numbers that are greater
than 20, those would be a a positive
| | 02:27 |
thing for this bookseller here.
So we're going to go to the Home tab, the
| | 02:34 |
Styles group, click the menu, and we're
going to highlight cells that are greater
| | 02:40 |
than 20.
Change this number over here to 20.
| | 02:45 |
And we'll change the color to, we're
going to make it yellow filled with dark
| | 02:49 |
yellow text, so it'll be real contrast
between the, the good times and the bad times.
| | 02:56 |
So here we have our negative numbers with
a light red background and red colored text.
| | 03:03 |
And then we have our best numbers with
the yellow and, and yellow text.
| | 03:10 |
So, you can see just how easy it is to
apply conditional formatting, and you
| | 03:14 |
have a lot of different conditions that
you can use to make those numbers jump out.
| | 03:20 |
To make it even easier, Excel has some
great predesigned conditional formats
| | 03:25 |
that you can use.
So, for example, data bars really have
| | 03:31 |
the ability to express values.
I'm going to go over here and choose the
| | 03:35 |
first data bar option.
And you can see, again, we have red for
| | 03:39 |
our negative numbers, but in addition to
the color formatting, we get a little bar
| | 03:43 |
that shows how big a value that negative
number is.
| | 03:47 |
The same thing with the positive numbers,
our 29 and 27 here are bigger bars than
| | 03:52 |
the eight and 14.
They're kind of like spark lines in a bit
| | 03:56 |
that you're seeing the data expressed in
a single cell.
| | 04:00 |
Take a look at another one of the options
that we have in the predesigned format,
| | 04:05 |
color scales.
And again, I'll just choose one of these,
| | 04:10 |
and you can see it colors the background,
in addition to providing the bars.
| | 04:17 |
Now, if you get tired of something like
that, that's pretty likely, you can turn
| | 04:21 |
off conditional formatting using the same
menu.
| | 04:26 |
So I've selected all the cells, and I'm
going to clear the rules from selected cells.
| | 04:30 |
Or if you want to remove the conditional
formatting from an entire worksheet, you
| | 04:34 |
can use Clear Rules from Entire Sheet.
And then we're back to where we were when
| | 04:40 |
we started.
So in this lesson, you learned how to
| | 04:43 |
manually apply conditional formatting to
your worksheet.
| | 04:46 |
For even faster results, you saw how to
use Excel's predesigned data bars and
| | 04:50 |
color scales.
| | 04:52 |
| | Collapse this transcript |
|
|
8. Printing and Sharing Your WorksheetPrinting a worksheet with column and row headers| 00:02 |
Printing your spreadsheet isn't quite as
easy as printing text from your word processor.
| | 00:06 |
For one thing, spreadsheets can be all
sorts of sizes and dimensions, and often
| | 00:10 |
they aren't easily squeezed into a single
page.
| | 00:14 |
In this lesson, you learn how to fit your
work to a page and how to print column
| | 00:17 |
and row titles in the right spots.
So here's an example of a spreadsheet
| | 00:22 |
that might give you some trouble
printing.
| | 00:25 |
It's keeping track of book orders and it
keeps track of them over the course of
| | 00:29 |
the years, so we've got a few columns
here, but it's also got a lot of rows.
| | 00:34 |
It has all the rows for each of the
states and territories in the U.S, so
| | 00:37 |
scroll down here to the bottom, you can
see that we have 58 rows.
| | 00:42 |
That's going to be a lot to squeeze onto a
single page.
| | 00:46 |
Now, the first thing you want to do
probably, when you have a worksheet like
| | 00:49 |
this is to get a feeling for how many
pages Excel would printed to automatically.
| | 00:55 |
So to do that, we go File and then Print.
And here we've got a great preview of our
| | 01:01 |
page, and we have controls over here.
These are some of the most frequently
| | 01:06 |
used tools for printing the pages.
So you can see, you can choose what to print.
| | 01:11 |
You can print the entire workbook, or you
could just print a selection.
| | 01:15 |
You can choose the number of copies.
You can choose different printers, if you
| | 01:18 |
have different printers.
Important for spreadsheets is the orientation.
| | 01:22 |
You may want to choose portrait or
landscape orientation depending on your project.
| | 01:27 |
And you can set the margins.
And you can set the scaling.
| | 01:32 |
So when excel tries to squeeze something
onto a page, it uses scaling.
| | 01:36 |
It scales it up or scales it down to make
it fit properly, and we'll take a look at
| | 01:41 |
some of those things too.
The print preview on this backstage area
| | 01:46 |
is great, it shows you your first page
and then down here, you can see it shows,
| | 01:50 |
this is the current page that would be
printed, and it's 1 of 4.
| | 01:55 |
So, without any adjustments, Excel plans
to print this on 4 separate pages.
| | 02:01 |
The first page looks fine, we've got our
months at the top and our states along
| | 02:05 |
the side.
But when we get to the next page, you can
| | 02:07 |
see we have our states here.
These are the last few states and
| | 02:11 |
territories, but the months are gone.
So somebody looking at this page has no
| | 02:15 |
way of understanding what these numbers
really represent, because it doesn't show
| | 02:19 |
the months.
And over here we have the opposite problem.
| | 02:23 |
Here we have some columns of numbers with
months, but we don't see the states along
| | 02:27 |
the side.
So obviously that's an area that we need
| | 02:30 |
to fix.
To do that, I'm going to go to the Page
| | 02:35 |
Layout tab, and in the Page Setup group
we'll look at Print Titles.
| | 02:41 |
So these are titles.
We want the months to be titles on each
| | 02:44 |
page, we want the states to be titles on
each page.
| | 02:48 |
So we go up here to Print Tiles and that
opens up this Print Setup dialog box
| | 02:52 |
where you can control pages margins and
headers and footers.
| | 02:58 |
What we want to do Is control the sheets,
and we want these two items here, rows to
| | 03:02 |
repeat at top and columns to repeat at
left.
| | 03:05 |
So to set the range for that, all I have
to do is click this button here and then
| | 03:11 |
choose row two, like that, and you can
see it says two to two.
| | 03:20 |
Now I'll press Enter, and in row to
repeat, it's given the definition for row two.
| | 03:25 |
We'll do the same thing for column A.
I'll go up here where the A is and click
| | 03:31 |
down there, press the Enter key, and now
we have A.
| | 03:36 |
So if you want to take a look at how this
changes things, let's click the Print
| | 03:40 |
Preview button here.
We go back to our backstage print area,
| | 03:44 |
we have our preview here.
It's still printing on four pages, but
| | 03:49 |
when we go to page two, you can see we do
have the names of the months at the top
| | 03:53 |
and the names of the states there.
And that's true for all of the pages.
| | 03:59 |
We always have the names of the months
and the states and that's a good thing.
| | 04:05 |
Okay, now we want to force this to print on
fewer pages.
| | 04:11 |
We could try and take a look at how it
would print on a single page.
| | 04:17 |
And to do that we go down to the Scaling
Option down here, and we have several choices.
| | 04:22 |
No scaling is what we're set at right
now, so it's going to print ten point font,
| | 04:26 |
at ten points.
When we scale it, we're going to either
| | 04:30 |
shrink that up or down.
Let's look at, Fit Sheet on one Page.
| | 04:35 |
So when we do that, it's getting pretty
microscopic here.
| | 04:38 |
We do have everything fitting on one
page, but it's awfully small.
| | 04:43 |
It's going to be about 60% of the normal
size looking at that.
| | 04:46 |
So that's a bit too small.
But what we could do is maybe get all the
| | 04:50 |
rows on a page, but have states on a
couple of different pages.
| | 04:57 |
So, let's try that Option.
So we go here and look at the other
| | 05:01 |
options we have, It Fit All Rows on one
Page.
| | 05:05 |
And I'll choose that.
And you can see, it's gotten a little bit
| | 05:09 |
bigger, better use of the page.
It's now printing on two pages and we've
| | 05:13 |
got just a couple of months tailing off
on that second page, and all the states
| | 05:18 |
on a page.
And that looks pretty good.
| | 05:22 |
So all we have to do is make sure we have
the right printer selected and then we
| | 05:25 |
could choose Print, and we're ready to
go.
| | 05:29 |
So those are some of the tools that you
use when you're trying to make your
| | 05:33 |
worksheet print respectiby on a page or a
couple of pages.
| | 05:37 |
So, in this lesson you explored some of
the tools that you use to print your worksheets.
| | 05:42 |
you saw how to change page orientation,
how to set the number pages and how to
| | 05:46 |
choose titles for the rows and columns.
| | 05:49 |
| | Collapse this transcript |
| Setting up page breaks| 00:02 |
When your worksheet won't fit on a single
page, page breaks become a major issue
| | 00:06 |
and there usually logical places to split
your worksheet between one or more pages
| | 00:10 |
and you know where those places are but
you need a way to communicate that to
| | 00:13 |
Excel before it sends the pages to the
printer.
| | 00:19 |
So, let's take a look at this worksheet
that is a little too big to fit on one page.
| | 00:24 |
So we're going to want to split it between two
pages.
| | 00:27 |
And initially when you look at it, you
don't see any indication where those page
| | 00:32 |
breaks might be.
Where, for example, Excel automatically
| | 00:36 |
will split the work up into multiple
pages but we can't see where those splits
| | 00:40 |
are going to be.
One way to see that in this view, this is
| | 00:45 |
your normal view, is to go to File and
then go Print.
| | 00:50 |
And it gives you this preview and you can
see that we're going to print on four pages.
| | 00:55 |
It says one of four down here.
Now, if we go back to the home tab, we
| | 00:59 |
see some lines in our document.
This dotted line, is a page break, so
| | 01:04 |
it's going to split those rows into two
parts.
| | 01:08 |
And it's going to split the columns into
two parts too.
| | 01:10 |
We've got a dotted line down here, where
we've got a pretty clear indication how
| | 01:15 |
it's going to print on four pages.
Well, this particular workbook might be
| | 01:22 |
better if it were printed in landscape
mode instead of portrait mode.
| | 01:28 |
I'm going to go over to the page layout
group here and change the orientation to landscape.
| | 01:36 |
And now we've got everything except these
last two columns fitting in here, and
| | 01:40 |
it's still printing on four pages, which
is probably unnecessary.
| | 01:45 |
So, I'm going to go over in the Scale to
Fit group here, it has different options,
| | 01:50 |
and I think what we want to is we want to
change the width to squeeze these last
| | 01:55 |
two columns onto our page.
So I'm going to go up here, and we have
| | 02:02 |
width and we can choose the number of
pages that accommodate the width, and we
| | 02:05 |
want it on one page.
So now we've got a fit there, and we can
| | 02:11 |
see we still have a page break down here.
Now there's another way that you can view
| | 02:16 |
the page layout in addition to that Print
Preview view.
| | 02:20 |
You can use the buttons down here.
One of these is Page Layout.
| | 02:25 |
And it shows you your document as if it
were printed on pages.
| | 02:29 |
It sort of gives you a representation of
it.
| | 02:31 |
You can see here are the edges of the
pages here, if we scroll down a little
| | 02:34 |
bit we see the bottom of this page and
the top of the next page.
| | 02:39 |
So we can see our document as it's
printed on a couple of pages.
| | 02:43 |
And sometimes if you zoom out a bit you
can get a better view of how the pages
| | 02:47 |
actually look.
It's too small to really read but it
| | 02:51 |
gives you a picture of what's going on
here.
| | 02:55 |
So now there's one other thing, we have a
lot of data on this page but not so much
| | 02:58 |
on this one.
It might be nice to even them out a
| | 03:01 |
little bit.
We can do that by adjusting this page
| | 03:05 |
break here, and you can do that over in
this other view, this is the Page Break
| | 03:09 |
Preview view, and we click on that, and
we see our document like this.
| | 03:15 |
And it clearly says page 1 here and page
2 there.
| | 03:18 |
The blue dotted line is a page break as
set by Excel, but we can manually change
| | 03:25 |
that page break.
And I'm going to click on it and just drag
| | 03:30 |
it up here, and we'll put the page break
where the M states change to the N
| | 03:35 |
states, and that's a better division of
the data on this worksheet.
| | 03:42 |
So now we have two pages.
Page break in the middle and if we go
| | 03:48 |
over here to Print Preview, you can see
that looks pretty respectable.
| | 03:56 |
So, in this lesson, you explored the
tools that you use to adjust page breaks
| | 03:59 |
in your printed page.
You've viewed your document in page
| | 04:03 |
layout view and page break preview, and
you saw how to change the page
| | 04:06 |
orientation between portrait and
landscape.
| | 04:10 |
| | Collapse this transcript |
| Emailing a workbook| 00:02 |
After you put in a lot of work on a
spreadsheet, you probably want to share
| | 00:05 |
it with some of your colleagues.
Now sometimes you may want them just to
| | 00:09 |
view the data, but other times you may
want them to be able to load the workbook
| | 00:13 |
into their copy of Excel and get to work.
This lesson shows how to email a workbook
| | 00:20 |
to someone else.
So here we have a workbook that we want
| | 00:24 |
to send off to somebody and it's really
pretty easy, it's almost like printing to
| | 00:28 |
email it to someone.
We go over here to the File and then the
| | 00:34 |
Save and Send command down here.
And you have several options, where you
| | 00:39 |
can save to the web, or if your company
uses Sharepoint, you can save to that.
| | 00:44 |
You can save it in different versions.
We're going to use the Send Using Email
| | 00:49 |
option here.
Then we have more choices down here.
| | 00:52 |
These choices let you save it in
different formats, so if we wanted to
| | 00:56 |
send a PDF of the workbook to somebody,
we would use this option.
| | 01:01 |
Now the advantage of sending a PDF to
somebody, it's a, it's like a text file.
| | 01:06 |
They're really not going to be able to
work with it like a workbook, they won't
| | 01:09 |
be able to change formulas and that kind
of thing.
| | 01:13 |
But they'll see what a printed copy of it
would like.
| | 01:16 |
Now, if you want somebody to be able to
use it as an Excel workbook, you need to
| | 01:20 |
send an Excel file to them as an
attachment on an email, and that's what
| | 01:24 |
this command here does.
So when I click on this, it starts Outlook.
| | 01:32 |
And this, the assumption is that you'll
already have Outlook set up on your
| | 01:35 |
computer and you're already sending and
receiving email in Outlook.
| | 01:39 |
So, it creates a message window that
works with Outlook.
| | 01:46 |
And if you've used outlook this should
look familiar to you.
| | 01:48 |
It has a two button, so if you click on
that, I can choose somebody to sent this to.
| | 01:53 |
I'll send it to this Chris Grover guy.
Then I can click OK and now this email
| | 01:59 |
address, that contact is in there.
You could send this same file to several people.
| | 02:06 |
And in that case, each person would get
an individual copy of that worksheet.
| | 02:11 |
If you wanted to later put all those
details together, somebody's going to have
| | 02:15 |
to go through it and merge all the
changes that people have made.
| | 02:19 |
You can also send a copy as a CC to
somebody.
| | 02:23 |
Here's the subject line for our email
which is the title of the workbook.
| | 02:29 |
You might want to change that to something
else.
| | 02:32 |
And then down here you can see the
attachment.
| | 02:39 |
This is actually the file and it tells
you how big the file is and the file name.
| | 02:45 |
And then here's the body of your email.
And you'd probably want to give some
| | 02:48 |
information in here, just so that they
know what's inside of your email.
| | 02:54 |
And then at the point, all you have to do
is click the Send button and off it goes.
| | 03:02 |
So it's that easy to send a copy of your
workbook to somebody else, in a form that
| | 03:07 |
they'll be able to use it as a workbook.
In this lesson, you saw how to use the
| | 03:15 |
File > Save and Send Command to email
your workbook to a colleague.
| | 03:19 |
| | Collapse this transcript |
|
|
