IntroductionWelcome| 00:00 | Hi and welcome to Getting Started with
OpenOffice2 Calc, the free spreadsheet
| | 00:04 | application that's part of the
OpenOffice.org suite of applications. Now,
| | 00:09 | if you didn't already know this,
OpenOffice2 is the leading open source office
| | 00:14 | software suite for spreadsheets as
well as word processing, presentations,
| | 00:19 | graphics, databases and more.
| | 00:22 | Further to that the entire suite is
available in a number of different
| | 00:26 | languages and it will work on almost
any computer, PC or Mac. It stores all
| | 00:31 | your data that's everything that you
create in an International Open Standard
| | 00:36 | format, but it can also read and write
files from other common office software
| | 00:41 | packages like those found in the
Microsoft Office auite of applications, for example.
| | 00:45 | Now best of all, it can be
downloaded and used completely free of charge.
| | 00:51 | That's right! Free for any purpose at
all. Tell your buddies, make copies,
| | 00:57 | download it to several machines at home
or even the office. It's all free. Well
| | 01:01 | in this title, we focus on the
spreadsheet application known as Calc.
| | 01:06 | We'll cover all the basics to get you
up and running and if you are a premium
| | 01:10 | subscriber, you have access to the
exercise files, so you can follow along with me.
| | 01:14 | Now if you don't have the exercise
files, don't worry about it;
| | 01:18 | you'll definitely learn lots just by
sitting back and watching or using your own
| | 01:22 | spreadsheet. I will be working in a
Windows Vista environment on a PC for this
| | 01:27 | title, but you can follow along from
any environment. So, without further delay,
| | 01:32 | let's get started with OpenOffice2 Calc.
| | Collapse this transcript |
|
|
1. Getting Started with CalcStarting Calc and exploring the interface| 00:00 | This first chapter is going to be all
about the basics in OpenOffice Calc.
| | 00:05 | We'll do things in this chapter like
launch the application using different
| | 00:08 | methods. We will tour the user
interface. We'll look at creating new
| | 00:12 | spreadsheets, opening existing
spreadsheets, saving techniques, all that kind
| | 00:17 | of stuff coming up in this chapter.
But right now, we are going to start by
| | 00:20 | launching the application and get
comfortable in our surroundings by touring
| | 00:24 | the user interface.
| | 00:25 | Here you can see, I'm in Windows Vista.
I've got a shortcut on my desktop to
| | 00:29 | OpenOffice Calc, there it is right
there. Double-clicking will launch the
| | 00:32 | application. If you don't have Windows
Vista, maybe you're in Windows XP on a
| | 00:37 | PC, maybe you're on a Mac; you may not
have shortcuts. You can always go to the
| | 00:41 | Start button down here in Windows Vista;
there is a Start button in Windows XP
| | 00:46 | as well. If you're on the Mac, you can
use Spotlight to locate OpenOffice Calc.
| | 00:51 | I am going to click my Start button
down here. And the first thing I'm going to
| | 00:54 | do is just start typing OpenOffice
and as soon as I type Open, look what
| | 01:00 | happens here at the very top. This is
kind of like on the Mac using Spotlight,
| | 01:04 | I've got all the OpenOffice programs up here.
| | 01:08 | Now, this includes the QuickStarter
up here, which we will talk about in an
| | 01:11 | upcoming lesson when we create new
spreadsheets, but down below you can see,
| | 01:16 | I've got Base, there is Calc right there.
I've also got Draw, Impress, Math and
| | 01:20 | Writer. We are going to be launching
Calc, so I am going to click on it right
| | 01:23 | here and that launches the
application, that's the equivalent of
| | 01:27 | double-clicking, that
shortcut to Calc on my desktop.
| | 01:30 | This is the default view, this is what
we see when we launch the application;
| | 01:34 | we get a brand new spreadsheet in
front of us. Launching an OpenOffice
| | 01:39 | application from your Mac computer is
done in a slightly different manner. I've
| | 01:44 | installed the latest version of
OpenOffice for the Mac and you can see here on
| | 01:47 | my desktop that I've an alias to
OpenOffice.org. And this is not created for
| | 01:53 | you automatically during the
installation process. You will need to do this
| | 01:56 | yourself, if you want one. This means,
we will access OpenOffice from our
| | 02:00 | applications, the first time around.
| | 02:03 | Let's click Go, then Applications and
notice that the OpenOffice.org app is
| | 02:10 | down here on my list in alphabetical
order. And there is no icon for each of
| | 02:15 | the separate applications in the suite,
so we need to launch this first. So, I
| | 02:20 | can do it from here or I can close this
up and double-click my alias. So, that
| | 02:28 | launches up and now from the OpenOffice
window, I can choose to create types of
| | 02:31 | files, which will launch the
appropriate app; for example, I choose Spreadsheet
| | 02:37 | over here if I wanted to launch Calc.
| | 02:39 | So let's do that, I'm going to click
on Spreadsheet and now, the Quick Launch
| | 02:44 | screen closes and I'm ready to start
using the app, slightly different from a
| | 02:48 | PC. You can see at the very top and
that's where we are going to start on our
| | 02:53 | tour of the user interface. This
spreadsheet is untitled and it's the first
| | 02:57 | one. So, Untitled1 appears up here next
to the name of the program or software
| | 03:01 | application I'm using, OpenOffice.org Calc.
| | 03:05 | So, on the Title bar we are going to
see information such as the name of the
| | 03:08 | file we are working with. So, when we
do start to create our own spreadsheets
| | 03:12 | and save them, and name them; we will
see those names up here, always keeps us
| | 03:16 | straight with what we are working on.
Over here, on the far right hand side,
| | 03:20 | we've got our Minimize, we've got our
Restore and our Close button to close the
| | 03:24 | application altogether.
| | 03:27 | On the Mac, you will have those options
way over here on the left side of your
| | 03:31 | Title bar. Right below that, like
most software applications, you will see
| | 03:36 | we've got our Menu bar with File, Edit,
View, Insert; all the way over to Help.
| | 03:41 | If you've ever used other spreadsheet
applications, maybe Microsoft Excel,
| | 03:44 | Quattro Pro; for example, on the Mac
there is one called Numbers. A lot of this
| | 03:49 | is going to look familiar; you're not
going to feel like you're in a brand new
| | 03:53 | environment here, it designed to
look like most other applications.
| | 03:57 | So, if I click on the File menu for
example, I see File commands like here is
| | 04:02 | where I go to create a New file, Open
an existing file, access Recent Documents
| | 04:07 | I've worked with. There are Wizards
here in Calc to create things. We've got
| | 04:12 | the Close button, saving and Save As,
Exporting, Properties. All kinds of File
| | 04:19 | commands; look at all the Print
options down below. Exit, down at the very
| | 04:23 | bottom will exit the entire application,
not just the spreadsheet you are working on.
| | 04:27 | So, that's a little bit different from
Close, which we see up here on our Menu
| | 04:32 | and you may notice that a lot of
these have keyboard shortcuts. Some people
| | 04:35 | prefer to use the keyboard, if you're
busy typing away on the keyboard and you
| | 04:39 | want to save, you might want to use
the keyboard shortcut Ctrl+S or use
| | 04:44 | Ctrl+Shift and letter S, together to
open up the Save As dialog box. Ctrl+P to
| | 04:50 | print and these are keyboard
shortcuts you may already be used to in other
| | 04:54 | applications.
| | 04:55 | It's beautiful how it's very similar
to all of those other applications. So,
| | 04:59 | what you've learned in those other
apps like Excel and Quattro for example;
| | 05:02 | they also apply here. Under the Edit
menu, we've got Editing commands Cut,
| | 05:08 | Copy, Pasting. We've got the View menu
and you will checkmarks next to certain
| | 05:13 | options, telling me that those are the
options currently selected such as, I'm
| | 05:17 | in Normal view as opposed to Page
Break view. Down below the Formula bar is
| | 05:22 | currently being viewed, as this is the
Status bar, we will talk about those in
| | 05:25 | a moment. Column and Row headers are
turned on and you will see sometimes, that
| | 05:30 | options on a menu may have a little arrow
to the right indicating there is a submenu.
| | 05:35 | So over here, you will see
checkmarks next to Formatting, our Formatting
| | 05:40 | toolbar is currently turned on, as is
the Standard toolbar and our Formula Bar.
| | 05:44 | We will talk about those in a second
as well. Now, we've got our Insert menu
| | 05:49 | for inserting different types of
objects, Rows, Columns, Pictures, Movies,
| | 05:54 | Charts, you name it. Under Format, we
go here to format Cells, Rows, Columns,
| | 05:58 | entire sheets. All kinds of cool things
down below for formatting objects as well.
| | 06:04 | There are some tools for checking
spelling, Language, AutoCorrect options. You
| | 06:09 | can see down below Macros and cool
things like that as well, all on the Tools
| | 06:13 | menu. Under data, we can Define and
Select Ranges, use Filters, check Validity;
| | 06:19 | all kinds of neat things under Data.
Window, this is handy if you're going to
| | 06:23 | be working with multiple spreadsheets
at a time, you can Split your screen, you
| | 06:27 | can Freeze columns and rows, you can
move between opened spreadsheets from the
| | 06:31 | Window menu here as well. We will be
doing this later on as we start to get creative.
| | 06:36 | And last but not least, here is our
Help menu where you can get OpenOffice.org
| | 06:40 | Help, that's help on all of the
application in this suite. We've also got
| | 06:44 | What's This? Which is very handy, if
you see something on your screen, you're
| | 06:47 | not sure what it is? Click on What's
This? And then just simply hover over it.
| | 06:51 | Let's try that now; I'm going to click
on What's This? And you can see there is
| | 06:54 | a little question mark attached to
mouse pointer and as I start hovering over
| | 06:58 | these buttons, I get detailed
descriptions, not just a little quick check bit
| | 07:02 | normally shows up.
| | 07:04 | If you're wondering what this bar is,
use this bar to enter a formula. This is
| | 07:07 | my Formula bar, okay! To turn it off,
you just click anywhere. Well, I'm going
| | 07:11 | to click here in a cell in my
spreadsheet, go back up here to the very top left
| | 07:15 | corner and click, and now
watch these option is turned off.
| | 07:19 | So, just below our Menu bar we do
have our Standard toolbar. So, there are
| | 07:24 | number of commands we saw in the
menus, here are some shortcuts to those
| | 07:28 | commands. The more commonly used
commands and of course you can totally
| | 07:31 | customize your Standard toolbar. There
are items here you never use, you can
| | 07:35 | remove them. If there are items you
wish were there, you can put them there.
| | 07:39 | And right at the very beginning, is
where we go to creating new spreadsheet.
| | 07:43 | Here is Open; this both fell under the
File menu. We've got Save and Document
| | 07:49 | as email. So, we can send what we
were working on directly using our email
| | 07:53 | application. These are all File commands;
you will see a little separator. Then
| | 07:56 | we get into some other ones, as we
hover over these, we see the Tool tips, Edit
| | 08:00 | File, Export Directly as PDF. There
is our Print icon, there is our Page
| | 08:05 | Preview; so if you want to what it is
going to look like before you print, go
| | 08:08 | ahead and use the Page Preview.
There is our Spellchecker, Autocorrect is
| | 08:13 | turned on, AutoSpellcheck as it shows up here.
| | 08:16 | There is Cut, and Copy, and Paste.
We've got our Formatting Paintbrush, Undo
| | 08:21 | and Redo; all kinds of options here
on our standard toolbar. And just below
| | 08:26 | that is a context sensitive toolbar
that changes our Formatting toolbar. Now,
| | 08:31 | currently I'm going to see options
for changing the Font, the Font Size,
| | 08:34 | appearance, alignment and you can see
I've got some formats here for numbers
| | 08:39 | like Currency and Percentages and so
our Decimal places. These are all part of
| | 08:44 | the Formatting toolbar. And if
wish to work on a Chart let's say, the
| | 08:48 | Formatting toolbar would change to
display options for formatting a chart.
| | 08:52 | So, I really like having this one
turned on; quick and easy access to things
| | 08:56 | again, we would find under the menus
here, usually under the Format menu. Then
| | 09:01 | we've got that Formula bar, so if
you're going to be creating lots of formulas,
| | 09:04 | you can create them here. And this is
called the input line right here, we
| | 09:08 | click inside. Once we've got our formula,
we can accept it or cancel it and it
| | 09:13 | will be inserted into
the previous selected cell.
| | 09:16 | So, we'll talk about formulas a little
bit later on in this title as well. And
| | 09:21 | down below is our actual grid, here is
where we create, you will notice that
| | 09:25 | we've got a series of columns A, B, C,
D, E and so on. And then we've got a
| | 09:29 | number of rows starting at
row1 and working its way down.
| | 09:34 | Now, there are tons of columns and rows;
so, you will run out of space and you
| | 09:39 | can see as I scroll down, I can see
the first 68, 69 rows using my screen
| | 09:43 | resolution. And I've got a scroll bar
down here for scrolling left to right.
| | 09:48 | And I can go across, you can see I'm
going to go all the way to W, X, Y; there
| | 09:53 | is more, if you've got a different
screen resolution, you might be seeing more
| | 09:56 | as well and we can continue
pass those limits, if we need to.
| | 10:00 | The other option is to create multiple
sheets and you'll see down below we've
| | 10:04 | got tabs, currently on Sheet 1, there
is Sheet 2, 3, we can have more sheets if
| | 10:09 | we need them, rename these sheets. A
good example might be where you've got
| | 10:13 | several departments, putting in
figures, maybe it's for revenue tracking.
| | 10:18 | So, on sheet 1 you will have your
grand totals and then each of these tabs
| | 10:21 | might represent each of the
departments and individuals go into those tabs to
| | 10:26 | enter their values which would show up
in the grand total here on sheet one.
| | 10:30 | So, we will get into working with
multiple sheets later on as well.
| | 10:33 | Now, if you do have multiple sheets and
more than you can see down here at the
| | 10:37 | bottom, you've got these all
Navigation buttons for moving back and forth
| | 10:40 | through the sheets, the first one, the
last one and you can see right now. We
| | 10:44 | are on sheet one of three, according to
our Status bar down at the very bottom
| | 10:48 | of our screen. Default style being used,
the current view or zoom level is set
| | 10:54 | to a 100%; our Insert
key is turned on right now.
| | 10:58 | So, if you want to type over something,
you can come down here or go to your
| | 11:02 | keyboard and click on the Insert key
to turn type over on, standard options
| | 11:06 | down here, because I've clicked in my
Formula bar, you can see sum=0 showing up
| | 11:11 | there, but we'll get important
information about what's on our screen down here
| | 11:14 | on the Status bar.
| | 11:15 | So, as we work through the lessons in
the upcoming chapters, we'll take a peek
| | 11:19 | down here quite often at our Status
bar to see what's going on. So that's a
| | 11:24 | quick tour of your User Interface. Now,
we're ready to start moving on, start
| | 11:28 | creating some files, opening up
existing files, saving our changes and so on;
| | 11:33 | that are all coming up in the
upcoming lessons in this chapter.
| | Collapse this transcript |
| Creating and opening workbooks| 00:00 | There are a couple of tasks you're
going to be repeating over and over as you
| | 00:03 | began to use OpenOffice Calc. One is
to create a brand new spreadsheet. Of
| | 00:08 | course the other is to open existing
spreadsheets that you've already created,
| | 00:13 | to go in and make changes and updates and so on.
| | 00:16 | So, in this lesson we'll look at different
ways to create new spreadsheets as well
| | 00:19 | as open up existing ones and if you've
got the exercise files, you're premium
| | 00:23 | subscriber, excellent! You will be
able to follow along with me. If you don't
| | 00:27 | have the exercise files, don't worry
about it. You can open up any existing
| | 00:30 | spreadsheet you may already have on
your computer. Well, let's talk about
| | 00:34 | creating new spreadsheets.
| | 00:36 | When you launch the application, this
is what is presented to you, a brand new
| | 00:40 | blank spreadsheet; you can see up
here on the Title bar, Untitled1 appears,
| | 00:45 | it's a very first spreadsheet that I've
started to work on since launching the
| | 00:49 | application. If I want to create
another new one, I have some options; I can go
| | 00:54 | to the File menu and you
will see New right at the top.
| | 00:58 | Now, If I click on New you will notice
that I've the ability to create new text
| | 01:02 | documents, spreadsheets; Ctrl+N is the
keyboard shortcut which creates a brand
| | 01:08 | new spreadsheet because I'm in Calc,
but I can create presentations, drawing,
| | 01:12 | databases; all using the appropriate
application in the suite. I can create
| | 01:17 | HTML and XML documents, Master
documents, there is the formula, labels,
| | 01:23 | business cards. And I can access
templates and documents, which is the
| | 01:26 | equivalent to that QuickStarter we saw
on the desktop in the previous lesson.
| | 01:31 | So, if click on templates and documents,
this is what I see; a new document,
| | 01:35 | there is all my choices over here
including spreadsheet right there. I've got
| | 01:40 | templates; you can see they are in
folders. My templates as I create my own.
| | 01:45 | Presentation backgrounds and
Presentations, doesn't apply to Calc.
| | 01:48 | So, I'm going to go back to New document,
click on Spreadsheet and click Open.
| | 01:53 | As soon as I do that, really it
appears as though nothing has changed, but
| | 01:57 | that's not true. Check out the Title
bar, this is my second new spreadsheet.
| | 02:02 | Untitled2 appears up here. Until I
give this a name. To close it, I go to the
| | 02:07 | File menu and click Close and
that takes me back to Untitled1.
| | 02:13 | The other option is go to my New button
and by default spreadsheet should show
| | 02:17 | up as the default, but I can click
spreadsheet from here again, it creates a
| | 02:22 | brand new spreadsheet, Untitled2 now
appears. I'm going to go up to File and
| | 02:26 | choose Close.
| | 02:28 | So, by clicking this button without
clicking the dropdown, I will automatically
| | 02:32 | create a brand new spreadsheet with
each click, but we've got several different
| | 02:37 | ways to do that. And now, you know
about them. Another, task you're going to be
| | 02:41 | performing on a regular basis is
opening existing spreadsheets. Let's go over
| | 02:45 | to the Open button here. This is
the equivalent to going up to File and
| | 02:49 | choosing Open or if you prefer to use
the keyboard, Ctrl+O as the keyboard
| | 02:54 | shortcut. If you're on a Mac that's
Command+O as in Open; I'm going to click
| | 02:59 | the Open button though.
| | 03:01 | And let's navigate to the Lesson1
folder of the exercise files, if you've got
| | 03:05 | them. You can go to your desktop, you
can go to your own folders, navigate
| | 03:09 | through My Computer, if you're on the
network you can access network drives as
| | 03:13 | well, but I have accessed my Lesson1
folder which by the way is in the Exercise
| | 03:18 | Files' folder on my desktop.
| | 03:20 | So, if you copy the exercise files
over wherever they are, navigate to the
| | 03:24 | Lesson1 folder. You will see a couple
of different spreadsheets in here with
| | 03:27 | similar names. Now, if you look at
the names down here, they appear to be
| | 03:31 | identical, but the extensions are
different and the preview that you see here
| | 03:35 | is a little bit different. Here, from
the Open dialog box we can change how we
| | 03:40 | view our icons.
| | 03:41 | So, I'm going to click the dropdown,
currently you will see large icons here
| | 03:45 | selected. If I go down to Details,
I'll see information about these two
| | 03:50 | spreadsheets and as I hover over them,
you can see the top one is an open
| | 03:54 | document spreadsheet and the extension
is actually .ods, so the real name is
| | 04:00 | ExpenseSheet1.ods. This one down
below is Microsoft Excel worksheet. The
| | 04:05 | identical file, but its extension will
be .xls and over here under Type you can
| | 04:10 | see Open document spreadsheet
and Microsoft Excel worksheet.
| | 04:15 | Different names for the same thing,
worksheets, spreadsheets; sometimes you
| | 04:18 | will hear workbook as well. I'm just
going to expand this out a little bit. So,
| | 04:22 | you can see the sizes are a little
bit different. Even though they are the
| | 04:24 | exact same files, the Open document
spreadsheet takes up less space than the
| | 04:29 | Microsoft Excel spreadsheet. So, let's
open up ExpenseSheet1, the Open Document
| | 04:35 | Spreadsheet, .ods; give it a click,
click Open and it opens it up.
| | 04:41 | You can see this is an ExpenseSheet, US
Dollar Expenses. There is some data in
| | 04:46 | here, some numbers, some dates, some
values, even formulas that give the
| | 04:51 | totals. So, this is the spreadsheet
in OpenOffice Calc. It's a spreadsheet
| | 04:56 | that's native to OpenOffice.ods. Let's
see what happens now, when we open up
| | 05:00 | and Excel spreadsheet. Go back to the
Open button, back to the Lesson1 folder
| | 05:05 | and this time we'll go to the Microsoft
Excel worksheet and click Open. Looks identical.
| | 05:13 | Now one difference, it is a different
format, it's a little bit larger, because
| | 05:17 | it's an Excel file but it looks
exactly the same. I'm going to go up to File
| | 05:23 | and Close. It takes me back to my
Open documents spreadsheet and as you can
| | 05:29 | see, there are no changes.
| | 05:31 | So, this is the real beauty behind
OpenOffice.org Calc. We can open up Excel
| | 05:37 | spreadsheets; we can open up native
spreadsheets .ods files. We can open up
| | 05:42 | other formats as well and the reverse
is true. When we start creating, we can
| | 05:47 | save to those formats.
| | 05:49 | So, if you're sharing with someone who
only has Excel, no problem. You can Save
| | 05:53 | As an Excel file. We are going to get
into all of that, when we talk about
| | 05:56 | saving your work. For now though,
let's go back up to File. We'll close this,
| | 06:02 | we haven't made any changes. So, we
won't be prompted to save anything. When
| | 06:05 | you close up the last spreadsheet,
this is what you see in OpenOffice.
| | 06:10 | Notice it says OpenOffice.org; it
doesn't say Calc up here. So if I want to
| | 06:14 | create a new spreadsheet. I've to
make sure I click the dropdown and select
| | 06:17 | spreadsheet from here to launch Calc.
And now, I'm back to a brand new untitled
| | 06:22 | spreadsheet ready to
continue on into the next lesson.
| | Collapse this transcript |
| Saving workbooks| 00:00 | If you were following along with me in
the previous lesson, you saw just how
| | 00:04 | easy it was to open up a spreadsheet
here in OpenOffice Calc, whether that
| | 00:09 | spreadsheet was a native format .ods file
or another format such as Microsoft Excel.
| | 00:16 | Opening is fairly straightforward.
Well the same is true for the
| | 00:19 | opposite, saving. When you make
changes to a spreadsheet, you can save those
| | 00:24 | changes keeping the same format or
select from various other formats, that's
| | 00:28 | what we are going to
explore right now in this lesson.
| | 00:31 | And you could see, I already opened up
a file it's our ExpenseSheet1.ods file,
| | 00:36 | this is a spreadsheet that's native to
OpenOffice Calc and we are going to make
| | 00:41 | a simple change and then save that
change. So, here is what we are going to do.
| | 00:46 | Let's go down to the actual name and
address that we see here. We'll just click
| | 00:51 | right on the postal code. And I'm going
to type is something different up here
| | 00:56 | in the Formula bar. Instead of 4R4, I'm
going to click in there, Backspace over
| | 01:01 | that and type 5R5 and when I
hit Return; I've locked that in.
| | 01:06 | So, I made the change; very small change,
but it needs to be saved. The easiest
| | 01:12 | way to save changes, keeping the same
name, format and location is to hit the
| | 01:16 | Save button, on your keyboard, Ctrl+S
on the PC, Command+S if you're on a Mac,
| | 01:22 | we will also save those changes and
again you don't get to choose a different
| | 01:27 | name, a different location or a
different format. However if you wanted to
| | 01:32 | change the location or the format,
even the name of the file; you can do that
| | 01:36 | by using Save As.
| | 01:37 | If we go up to the File menu and down
to Save As. Notice that the keyboard
| | 01:42 | shortcut is Ctrl+Shift and as here on
the PC keyboard. Command+Shift, that's on
| | 01:48 | the Mac. Well, we can just click Save
As and when we do, we see the Save As
| | 01:52 | dialog box. I'm going to move that up
here, you can see it's in the Lesson1
| | 01:56 | folder, the name is ExpenseSheet1, it's
an ODF spreadsheet, so the extension is
| | 02:03 | ods, Open Document Format.
| | 02:05 | If I want to change any of those I can;
for example, if I want to change the
| | 02:09 | location to the desktop, I can click
Desktop here or I can use the dropdown
| | 02:14 | here to select from different drives
and locations; I'm going to leave it at
| | 02:18 | Desktop. If I want to change the name
such ExpenseSheet1 to ExpenseSheet2, I
| | 02:24 | just click down here take
out the one and put in a two.
| | 02:27 | And now If I want to change the Type,
this is the important part, because if
| | 02:30 | you do plan on sharing your files
with others, you may need to save to
| | 02:34 | different formats. So clicking this
dropdown allows me to select from lots of
| | 02:39 | different formats. Up here at the top,
the very top is our default format,
| | 02:43 | which is Open Document Format
Spreadsheet .ods file, you can save
| | 02:48 | templates. You can see, we can go back
to previous versions, OpenOffice.org1 by
| | 02:53 | default we are saving to version two.
| | 02:56 | We can go down to dBASE files .dbf.
Look at all the different Excel formats and
| | 03:02 | versions we can choose from 97/2000/XP,
97/2000/XP Templates, there is 95,
| | 03:10 | version 5.0; you may have heard of
StarCalc or you maybe you haven't, but you
| | 03:14 | can save to those to formats if you need it to.
| | 03:16 | Also, down below you've got HTML, Text
and the .csv format is very interesting;
| | 03:23 | it can be opened by almost any
spreadsheet application. So, if you're not sure,
| | 03:27 | what people are using when you share
your files, choose .csv and they will be
| | 03:31 | able to open it, no matter what they
are using. There is another old version of
| | 03:35 | Microsoft Excel, but saving to an XML
format for Excel, Pocket Excel, all kinds
| | 03:41 | to choose from; I'm going to go to
Microsoft Excel 97/2000/XP, when I click on
| | 03:48 | that; it shows up here, the new extension
will be applied when I click the Save button.
| | 03:54 | So, when I click Save, this document
makes formatting a content that cannot be
| | 04:00 | saved in Microsoft Excel format, you
want to save the document in this format
| | 04:04 | anyway. So, you're taking your chances,
there is not a lot of formatting in
| | 04:07 | this. So, when I click Yes, I'll
actually save it to my desktop in that format.
| | 04:14 | Notice that it's still open here as well.
| | 04:16 | So, I'm continuing to work on this
Excel file. I'm going to go up to File and
| | 04:21 | choose Close. I'm going to minimize
OpenOffice.org, so I'm going to click the
| | 04:27 | Minimize button, it takes me to my
desktop and you will notice my spreadsheet
| | 04:31 | icon is here; this is the document
I've just saved, ExpenseSheet2. And as I
| | 04:36 | hover over it, you'll notice that
it's a Microsoft Excel worksheet.
| | 04:39 | So, if I use to double-click this;
automatically, Microsoft Excel would launch
| | 04:43 | and open it up in Excel. So, if you
want it to open it in OpenOffice Calc. Well
| | 04:49 | then you have to run OpenOffice Calc.
I'm going to go back down to Taskbar here
| | 04:54 | and I'm going to go to the Open button.
This time, now I'm going to go to my
| | 04:58 | desktop and look for that ExpenseSheet,
keeping in mind that's an Excel file
| | 05:05 | and there it is right there;
ExpenseSheet2 with it selected, I click Open and
| | 05:11 | it opens up; there we go right there in
Calc, looking exactly like it did when
| | 05:15 | I saved to that format.
| | 05:17 | So, that's a very, very nice feature
of OpenOffice Calc, the various formats
| | 05:23 | you can save to; you can be
compatible with anybody out there. Even those
| | 05:28 | people, who have spend thousands of
dollars on fancy software. Here you are in
| | 05:32 | OpenOffice Calc, a free application
working on the exact same files that they can.
| | Collapse this transcript |
|
|
2. Working with Cells and SheetsCell basics| 00:00 | In this next chapter, we get into the
meat and potatoes behind any spreadsheet
| | 00:04 | application. I'm talking about the
actual cells in your spreadsheet. The sheets
| | 00:09 | that make up the spreadsheet and we are
going to talk about the different types
| | 00:12 | of data that you can enter into a
spreadsheet. We'll talk about filtering and
| | 00:17 | sorting. Automatically filling up
cells in a spreadsheet; we also talk about
| | 00:21 | using multiple sheets, creating new
ones, deleting sheets, reordering them,
| | 00:25 | even renaming them; it's all coming up
in this chapter. Right now though, we're
| | 00:30 | going to talk about some cell basics.
| | 00:32 | So, here you can see I've got a brand
new spreadsheet in front of me. If you're
| | 00:35 | following along, that's what you're
going to need. You can click the New button
| | 00:39 | as long as spreadsheet icon is
visible, you will create a brand new
| | 00:42 | spreadsheet. Of course if you launch
OpenOffice Calc, this is what you're
| | 00:46 | presented with a brand new sheet and
you will notice down below we've got three
| | 00:50 | tabs, Sheet1, Sheet2 and Sheet3. So,
by default you're given three separate
| | 00:55 | sheets as part of this
brand new blank spreadsheet.
| | 00:59 | So, here we are in Sheet1 and you'll
notice that, this very top cell here,
| | 01:04 | which is in column A, row 1 is selected.
This is the very top left corner of
| | 01:09 | our spreadsheet. Columns are labeled
with letters A, B, C, D etcetera, going
| | 01:14 | from left to right. And rows are
numbered going down from one. And you can see
| | 01:19 | there are lots and lots of rows, lots
of columns more room than you'll probably
| | 01:23 | ever need on a single sheet. And of
course you've got the ability to have
| | 01:26 | multiple sheets in a spreadsheet as well.
| | 01:29 | So, let's talk about the cell address.
Every cell has its own address and if
| | 01:34 | you've ever played the game Battleship,
you will know what a cell address is?
| | 01:39 | You go across, let's go to column D
and let's go down to row 5 and click.
| | 01:45 | Notice that the D is highlighted or
shaded as is the 5. The cell address is
| | 01:50 | therefore D5 and I see that up here in
my Formula bar; D5 selected right here.
| | 01:57 | So, every cell has its own address
and as we click on various cells, we see
| | 02:01 | that address up here in the top left
corner. We also see the column and row
| | 02:05 | headings shaded for us to help us
figure out what cell addresses we're at.
| | 02:09 | There are three types of
data that can be entered into any given
| | 02:13 | cell, it could be text, it could be
numbers or it could be some kind of formula
| | 02:18 | that works with those numbers.
| | 02:20 | So, let's enter one of each here in
our brand new spreadsheet. I'm going to
| | 02:24 | start with text and I'm going to go up
here to cell A1, I'm just going to type
| | 02:29 | now, Expense Report. And you can see it
actually overlaps column A, column A is
| | 02:36 | not wide enough, so my text goes into
cell B1, but when hit Enter or Return on
| | 02:42 | my keyboard. That's okay, there is
nothing in cell B1, so I don't see my text
| | 02:47 | getting cutoff; it overlaps into the
next cell that's okay! Because there is
| | 02:51 | nothing in that cell; down below I'm
just going to type in the date, let's make
| | 02:56 | one up; August 28, 2008 and I'll hit Enter.
| | 03:03 | So, you can see what happen when I
Enter? A different format for that date
| | 03:07 | appeared. So, Calc is smart enough to
figure out that I was typing in a date
| | 03:11 | and then use the default format. Of
course, I can change that format and will
| | 03:15 | be doing that later on in this title.
| | 03:18 | So, Expense Report, the data is in
there. Now, it's time to put in some
| | 03:22 | additional labels. So, over here on the
left; I'm going to go cell A4, skipping
| | 03:28 | over a row to just leave a little bit
of space. So, here I'm going to type in
| | 03:33 | Airfare, hit Enter, it takes me down to
the next cell. Next, I'm going to type
| | 03:38 | in Hotel; hit Enter.
| | 03:41 | Now, I'm going to type in Food, hit
Enter; Car and Enter one more capital Misc.
| | 03:48 | and Enter. So, there we've got some
labels they are just text, but default text
| | 03:55 | is left aligned. You can see it on the
left side of the cell. It happened up
| | 03:58 | here in cell A1 as well, but because
we had more text than what fits in the
| | 04:02 | cell, it's hard to see.
| | 04:04 | Now, dates and numbers are a little
bit different, you can see this is a date
| | 04:07 | and its right aligned in cell A2; in
other words it's over on the right side of
| | 04:11 | that cell. Let's type in some numbers
now; I'm going to over here to cell B4
| | 04:16 | and type in an amount. And I'm not
going to worry about dollar sign, that's all
| | 04:20 | part of the formatting we'll talk about
later. I just need to get the number in there.
| | 04:24 | So, Airfare; I'm going type in 650
and hit Enter, look at that numbers are
| | 04:30 | right aligned as well. Hotel, I'm going
to type in 299; Food, I'm going to put
| | 04:38 | in at 145; Car, 200; each time I hit
Enter, I move down to the next cell.
| | 04:46 | Miscellaneous items; I'm going
to put 50 bucks just like that.
| | 04:49 | So, when I hit Enter. Now, I've got
in two types of data. I've got text and
| | 04:54 | I've got numbers. And even though this
is a date that I typed in using text it
| | 04:59 | appears as a number. It's a number
format, the date format. And we'll talk it
| | 05:03 | about more later on. Now, its time
for the third type, which is a Formula?
| | 05:08 | So, down here in cell B9 is where we
might want to put in the total. I'm going
| | 05:13 | to skip a line and I'm going to click
here in cell A10 and type in the word
| | 05:17 | TOTAL in capital letters. Now, to move
from here over to the right; instead of
| | 05:21 | hitting Enter, I'm going to hit Tab key
that moves me over to the next cell and
| | 05:26 | locks in TOTAL.
| | 05:27 | Now, here I want to have an answer of
some kind. Now, one thing that you could
| | 05:32 | do is just type in these numbers with
plus signs to add them all up; problem
| | 05:37 | with that is if any of these
numbers change your total doesn't change
| | 05:41 | automatically. The beauty of a
spreadsheet is that I can change any of these
| | 05:44 | figures and hopefully the
total will be adjusted for me.
| | 05:48 | So, in that case I want to start of my
formula using the equal sign, that's how
| | 05:52 | we tell Calc that we are about type in
a formula. Notice, that I'm typing it in
| | 05:56 | right here in the cell. Now, I could
come up here to my Formula bar in the
| | 06:00 | input line and type it there as well,
but usually we would use the Formula bar
| | 06:05 | for more complex formulas. We don't
really need it here to add up some numbers.
| | 06:09 | So, like I said I could type in 650+
299 and so on. And, I will get the right
| | 06:17 | answer. Accept if I come up here and
change any of these, my total would not be
| | 06:21 | correct. So, what you prefer to do
and what you should always do is use the
| | 06:25 | cell address we talked about. I want
whatever is in B4 added to whatever in
| | 06:32 | B5+B6, 7 and 8.
| | 06:34 | So, let's do that; I'm going to type
in B4, B and the number 4 then a plus
| | 06:41 | sign. So you can see that the formula
has been created up here and I could be
| | 06:45 | doing that up there as well. Now, I
want to add B5; do I have to type in B5 and
| | 06:50 | B6 and so on. I could actually click
on the cell, when you do, it's added for
| | 06:55 | you. Notice, B5 is there and again hit
the plus sign, B6, plus, click on B7;
| | 07:04 | plus click on B8.
| | 07:07 | Now, watch what happens when I hit
Enter on the keyboard, there is my answer,
| | 07:11 | 1344. Now, if I type in the actual
numbers and went up to my Airfare and
| | 07:17 | increase this to 1000 and hit Enter,
I would not see this jump to 1694 if I
| | 07:25 | hadn't put in the cell address. If
I'm saying whatever is in there, plus
| | 07:29 | whatever is in now, plus, plus, plus; I
get an updated total, the moment I come
| | 07:34 | in here and make a change.
| | 07:36 | So, there you have it, the different
types of data; text, numbers and formulas.
| | 07:41 | Notice that formulas that give you a
numerical answer like this are also right
| | 07:46 | aligned. We'll formatting our cells as
move through the lessons in the upcoming
| | 07:51 | chapters, but for now you should have
a good handle on simple cell basics and
| | 07:56 | the different types of data that can
be entered into a spreadsheet here in
| | 08:00 | OpenOffice Calc.
| | Collapse this transcript |
| Auto-filling cells| 00:00 | In this lesson, we are going to
explore a huge time saver when it comes to
| | 00:04 | entering data into cells in your
spreadsheet. In the previous lesson, we typed
| | 00:08 | in some text and some numbers, even a
formula. Now we are going to look at a
| | 00:13 | feature that will save you some time,
when you need to enter data that follows
| | 00:17 | some kind of pattern. Maybe it's the
months of a year, maybe it's a numeric
| | 00:21 | format like one, two, three, four, days
of the week, you name it. If there is a
| | 00:25 | pattern, OpenOffice Calc will be
pretty good at picking on that pattern and
| | 00:30 | automatically filling cells for you.
| | 00:32 | You can see I have opened up a
spreadsheet to experiment with here. It's called
| | 00:36 | ExpenseReport1. If you have got the
exercise files, go to your Lesson 02 folder
| | 00:40 | and open this one up to follow along
with me. But really you can be working
| | 00:44 | with a blank spreadsheet here if you like.
| | 00:46 | We are going to experiment down below
here. I am in cell B16. It really doesn't
| | 00:50 | matter where you start and I am
going to type in Monday. When I hit Enter
| | 00:56 | Monday is locked in and now let's say I
want to fill up the rest of these cells
| | 01:01 | with the rest of days of the week. I
could start typing Tuesday, hit Enter.
| | 01:05 | Wednesday Thursday, Friday etcetera,
that's time consuming. When you click on a
| | 01:09 | cell that contain something like a day
of week, Monday. You will notice in the
| | 01:13 | bottom right hand corner this little
handle and when you hover your mouse over
| | 01:17 | it, the pointer changes to a crosshair.
That means you can now click and drag
| | 01:22 | to automatically fill cells.
| | 01:24 | So I am going to click and drag
straight down. You are going to see a little
| | 01:29 | pop up Tuesday, Wednesday, Thursday,
Friday, Saturday and Sunday and when I let
| | 01:32 | go, I have just automatically filled
those cells based on this pattern which
| | 01:37 | was only one cell, but because of it
was the day of the week Calc had a really
| | 01:41 | easy time figuring out what should go
in the other cells. Let's try something
| | 01:45 | else. Over here in the cell C16. I am
going to type in January. Okay I will hit
| | 01:51 | Enter. Let's go back to that cell
click on it, go down to the handle in the
| | 01:56 | bottom right corner and click and drag
down. There we go. We can go all the way
| | 02:00 | to December.
| | 02:00 | If we keep going it just loops around
and starts over again which is kind of
| | 02:04 | nice. What about this, Jan maybe I
want shortcuts or short forms of those
| | 02:10 | words. If I click and drag down, I
will get that all the way to December but
| | 02:16 | short forms for those words.
| | 02:18 | How about numeric patterns? Well, if I
type in 1 and hit Enter. Go back to that
| | 02:23 | cell here in cell E16 and click and
drag, you can see 2, 3, 4, 5, 6, 7, 8, 9,
| | 02:29 | and so on. I am going to stop at 10.
If I come over here and type in 2, hit
| | 02:34 | Enter, type in 4, hit Enter. Now I have
got a pattern: 2, 4. I am going to click
| | 02:41 | and drag over both of these now. So I
click and drag from F16 down to F17,
| | 02:47 | go down to the handle it's still there,
click and drag 6, 8, 10, 12, 14, 16, 18,
| | 02:53 | 20, it picks up on that pattern.
| | 02:55 | The AutoFill handle that we see there
is also good for copying formulas and
| | 03:01 | data for example. So up here I am just
going to click and drag from cell B16
| | 03:07 | across and down to highlight all of
those contents all the way to F27 and hit
| | 03:13 | the Delete key on my keyboard. You can
see what happens Delete Contents shows
| | 03:17 | up, Text, Numbers, Dates, Formulas,
Notes etcetera. I am going to select Delete
| | 03:22 | All and click OK. It clears those cells.
| | 03:25 | Let's go up here cell B4 and we will
type in a date. This will be the date of
| | 03:30 | our trip. I am going to start it on
the first of August 1-aug-08 and then I
| | 03:40 | will hit my Tab key. You can see it
automatically formats, recognizes that's a
| | 03:44 | date. I am going to click on that and I
am going to click and drag across this time.
| | 03:47 | Let's say it was a three-day trip.
As I drag it across there it 08/02/08
| | 03:53 | and 08/03/08. It filled out the dates for me.
| | 03:56 | So let's say the Airfare was $650 and
it was on the first day that I paid that.
| | 04:02 | The hotel didn't kick in until the
second day and let's say it was 175 a
| | 04:09 | night. So 175, I will hit my Tab key
type in 175 there as well. Under Food,
| | 04:16 | well we did spend some money. Let's do
40 bucks on the first day a travel day
| | 04:21 | and we will do $80 and $80. The car,
let's say we just paid for it in the last day.
| | 04:29 | So I am going to go over here at to
cell D8 and I am going type in, let's say
| | 04:34 | it was $400 and Miscellaneous items we
paid for on each day. I am going to do
| | 04:39 | 15. I am going to click in the next
cell; you can hit Tab as well. I am going
| | 04:43 | to type in 30 hit Tab type in 25. Now
down here I have already got my Total
| | 04:50 | formula in here. You can see B5+B6+B7+B8+B9.
| | 04:55 | So even though there are empty cells
here, the Total is correct. Now if I want
| | 05:00 | the same formula here and here as well.
I can simply copy it by using that
| | 05:06 | little AutoFill handle. You might be
thinking, the formula is B5+B6+B7+B8+B9.
| | 05:15 | If I copy that over here, will Calc
be smart enough to use these cells,
| | 05:20 | C5+C6+C7+C8+C9? You bet it will.
I'll go to the AutoFill handle, click and
| | 05:28 | drag across and let go and I got
different totals in each column because Calc
| | 05:33 | uses what's known as relative
addressing. So if I copy a formula that uses
| | 05:38 | these numbers over to the next column,
Calc is smart enough to use those
| | 05:42 | numbers and so on as I move across the columns.
| | 05:45 | So here you can see there is C5+C6+C7+
C8+C9. If I click over here in column D,
| | 05:50 | D5+D6+D7+D8 and D9. So that's the
beauty of AutoFill, a huge time saver whether
| | 05:57 | you are entering text, numbers or even
formulas, remember that little handle in
| | 06:02 | the bottom right hand corner called AutoFill.
| | Collapse this transcript |
| Quick and easy functions| 00:00 | If you have been following along with
me in this chapter, you know there is
| | 00:03 | various types of data that can be
entered into a spreadsheet. There is text,
| | 00:06 | there is numbers and formulas. Now the
only formula that we worked with to this
| | 00:11 | point was a simple one that added
up the content of cells we typed in.
| | 00:15 | So we started with an equal sign, typed
in cell addresses with plus signs to add
| | 00:20 | up each of the contents of those cells.
What if you have got lots and lots of
| | 00:24 | cells to work with? That can be very
time consuming, tedious and prone to
| | 00:28 | errors. So that's where Functions come
in. We are going to explore a couple of
| | 00:32 | basic functions in this lesson.
| | 00:34 | One, that will total up the values
found in various cells as well as average
| | 00:39 | out the values found in selected cells.
I have opened up a file we are going to
| | 00:43 | work with. It's called Revenues1.ods.
You will find this in your Lesson 02
| | 00:48 | folder of the exercise files, if you
are following along, otherwise any old
| | 00:52 | spreadsheet will do with the series of
numbers. Here you can see we have got
| | 00:56 | Totals for each of the years as well
as the quarters and we got a column over
| | 01:00 | here to average out the values as well.
| | 01:03 | So let's start down here in cell C9.
Here we want to total up these values
| | 01:07 | above and there is one way to enter a
function and the Sum function itself has
| | 01:14 | its own button on the Formula bar. This
little symbol right here represents Sum
| | 01:19 | and when I click on it, you can see
what happens. It's automatically selecting
| | 01:23 | the range for me, which in this case
is C3 through C8. If you look at this
| | 01:29 | range it's not correct. We
don't want to add in the year 2005.
| | 01:34 | So all we have to do is adjust this
slightly and we can do that by using the
| | 01:38 | little handle at the bottom. I am going
to drag it up. I am actually going drag
| | 01:41 | up two and with the hand drag it down
one. So I am getting the range that I
| | 01:47 | want which is in this case C4 through
C7. If you are familiar with the Sum
| | 01:52 | function you can just type in the way
you see it here in the cell as well as
| | 01:56 | the Formula bar, =Sum and then in round
brackets the range C4 to C7, we use the
| | 02:02 | colon as a separator. When I hit
Enter or Return on my keyboard I get the
| | 02:07 | answer. There it is right there.
| | 02:09 | Now of course I have used the function,
which is using a range of cells that
| | 02:13 | means if I go into any of these cells
and change the values. For example, if I
| | 02:16 | change this to 49,000 and hit Enter.
My total automatically updates itself to
| | 02:24 | 200,000 and we talked about auto
filling cells in the previous lesson. If I
| | 02:29 | click on the cell I see that little
handle in the bottom right corner, when my
| | 02:32 | mouse pointer changes to the crosshair,
I can quickly and easily drag across
| | 02:36 | two more cells when I release I get
the proper totals in each of those cells
| | 02:41 | because of relative cells addressing.
| | 02:44 | So the formula here actually knows to
use the values in that column in this
| | 02:48 | case D4 to D7. This one is using the
values in E4 to E7. Let's try that again.
| | 02:54 | A different way here in cell F4. We
will type it out ourselves this time, =sum.
| | 02:59 | Let's start with an open round bracket,
Shift then the number 9 on your keyboard.
| | 03:05 | Now we could type in the range, which
is going to be C4:E4, or we can just come
| | 03:11 | here and select the cells by clicking
and dragging. See how it writes in the
| | 03:15 | range for us. All we need now is the
closing bracket. Shift and the 0 and when
| | 03:20 | I hit Enter I get my answer 149,000. So
I am going to click in that cell F4, go
| | 03:26 | to the bottom handle in the right
corner here. When I see the crosshair click
| | 03:30 | and drag straight down to get my totals
for each of the quarters in each of the
| | 03:35 | years. That was fast, that's a simple one.
The Sum function, a very popular one.
| | 03:40 | Now we are going to go another simple
one known as the Average function. Now if
| | 03:44 | you have never used it, you might not
know what to type in here. In that case
| | 03:48 | you might want to go to a list of
functions on the Formula bar, which is this
| | 03:51 | symbol here. You can see the f and
the x in brackets. This is the Function
| | 03:56 | Wizard and when I click on it I am
going to see a list of functions by Category
| | 04:01 | and by default the All selection
appears here. But when I click on the
| | 04:06 | dropdown I can get database functions.
Date and Time functions, Financial,
| | 04:10 | Logical, Mathematical. Let's go
down to Statistical and click on that.
| | 04:15 | So this just filters out some of the
functions that don't apply to Statistic
| | 04:19 | and look it that, there it is right
there Average. So when I click on that and
| | 04:23 | click Next I can start choosing the
numbers that are going to be used right
| | 04:28 | from here and you can see the
formula itself will be written down below.
| | 04:33 | But now that I know it's equals,
average and then the brackets. I can click
| | 04:37 | Cancel down here and just type in the
actual function myself, =average and
| | 04:44 | opening round bracket, now I can select
the range which is the same range as my
| | 04:48 | Sum and it go from C4 across to E4,
it's written for me. Closing round bracket
| | 04:56 | when I hit Enter there is my answer,
the average amount for those three years
| | 05:01 | is 49,667 and using AutoFill I can
click on the cell. Go to the bottom right
| | 05:06 | corner, click and drag straight
down to quickly fill up the rest of the
| | 05:10 | quarters with the average functions.
Again, using Relative Cell Addressing
| | 05:14 | knowing that it should be using the
range in each of the rows where it's copied to.
| | 05:19 | Click out here to see the end
result and those are the couple of basic
| | 05:23 | functions. Keep your mind though; when
you go to the Function Wizard, there is
| | 05:26 | lots and lots to choose from. These
are just a couple of the many functions
| | 05:30 | that will save you a lot of time and
lot of effort here in OpenOffice Calc.
| | Collapse this transcript |
| Modifying cell properties| 00:00 | So far in this chapter we have been
working with cell contents, entering data
| | 00:04 | such as text and numbers and formulas
and functions. Now it's time to make
| | 00:09 | those cells look right. So we are
going to explore some formatting techniques
| | 00:13 | using the same spreadsheet we were
working with in the previous lesson.
| | 00:16 | So if you are continuing on, perfect,
you are in the right spot. If you skip to
| | 00:20 | this lesson and you do have the
exercise files go to the Lesson two folder.
| | 00:23 | You'll find Revenues2.ods. Open that
up and you will be all caught up.
| | 00:29 | So let's start right up here at the top
where the title Revenue Growth appears
| | 00:33 | to overlap a few different cells and
if I click right here in cell C1, I
| | 00:39 | noticed there is nothing up here in
my Input line on the formula bar. If I
| | 00:43 | click in cell D1, that's
where I see Revenue Growth.
| | 00:48 | So it actually exists in that cell and
because it is centered, I can tell by
| | 00:52 | the Center button here looking like
it selected. It's overlapping into
| | 00:56 | neighboring cells because there is
nothing in those cells. I am able to see the
| | 01:00 | full title. But a better option
would be to maybe select all of the cells
| | 01:05 | across the top of this data and merge
them into one and then center Revenue
| | 01:10 | Growth within there. Then if there is
anything entered in neighboring cells
| | 01:14 | nothing gets cut off.
| | 01:16 | So let's do that now. I am going to
click and drag from cell B1 across to G1.
| | 01:22 | So that's right above all of my data to
get Revenue Growth centered among those
| | 01:26 | cells. I just merged the cells first
and we do that by going up to the Format
| | 01:30 | menu. Down below Cells, Rows,
Columns and Sheets we see Merge Cells right
| | 01:35 | there, very easy.
| | 01:37 | Now because there is data in one of the
cells I see this little message. Should
| | 01:40 | the content of the hidden cells be
moved into the first cell? If I say No then
| | 01:45 | I will lose Revenue Growth. If I say
Yes I will keep it but just lose some of
| | 01:49 | the formatting perhaps. So let's click
Yes and there we see Revenue Growth over
| | 01:53 | here at the left hand side of the cell.
It's one big cell now, which means I
| | 01:58 | can do stuff like center.
| | 02:00 | Let's go back to the Center button here.
Now I can change the Font, the Size
| | 02:05 | etcetera and that's what we are going
to do next with some of the data that we
| | 02:07 | see here. We are going to work with
the contents of the cell and because we
| | 02:11 | have selected our one big merged cell
here. Let's go up to the Font size and
| | 02:16 | change it from 10 to 14. That looks better.
| | 02:21 | Now we can bold it by clicking the
Bold button. Maybe it'll look good
| | 02:24 | Underlined. Well it doesn't really
show very well, if I click off the cell
| | 02:29 | that's not bad. But let's go back to
our cell our large cell here that's been
| | 02:33 | merged and turn the underline off. So
these are Toggle buttons we can turn on
| | 02:37 | and off.
| | 02:39 | Now the Merge Cells button appears
here right here as well and because it's
| | 02:42 | pressed in, it means that the selected
cells in this case the range from B1 to
| | 02:46 | G1 is merged. I can unmerge those
cells by clicking this button, but I don't
| | 02:51 | want to do that right now.
It too is a toggle though.
| | 02:53 | Now we can also do things like Format
the cell contents by going over to some
| | 02:59 | of these buttons where we can choose
different Number Formats, Decimal Places
| | 03:03 | we can do Indenting and then the cells
can have Borders and Shading and Font
| | 03:08 | Colors as well.
| | 03:09 | Another option is to go up to the
Format menu here and selects Cells. Noticed
| | 03:14 | that there is Shortcut here Ctrl+1 on
a PC keyboard, Command+1 on your Mac
| | 03:19 | keyboard. This opens up the Format
Cells dialog box where we can do all kinds
| | 03:23 | of things from one location. For example,
Numbers we can choose Number Formats
| | 03:29 | and Decimal Places. Leading Zeroes if
we want. We can change Font Properties
| | 03:33 | for the selected cells. Font
Effects > Alignment. Here is Borders and
| | 03:37 | Backgrounds and even
protect cells if we needed to.
| | 03:40 | So I am going to click Cancel right now
and select a different range of cells.
| | 03:45 | All of this data in here. I am going to
leave the labels for the years and the
| | 03:50 | quarters but all of the actual numbers
that we see here. I am going to click
| | 03:53 | and drag over them. So I am going
from C4 across to G and down to 7.
| | 03:58 | So C4 to G7 is the range and I am
going to go up to Format and down to Cells
| | 04:06 | and in this case I am going to click on
Numbers because all of the contents of
| | 04:09 | those cells are indeed numbers and
currently you can see they do show up with
| | 04:13 | commas, no decimal places and
dollar signs. Number is the category that
| | 04:18 | selected here and the General Format
is being used, but if I wanted to set up
| | 04:22 | Currency format I can come here decide
what negative numbers might look like. I
| | 04:28 | am thinking maybe red.
| | 04:30 | So I am going to down here to this one.
I see a sample over here on the right
| | 04:34 | hand side. You can see there is Format
Code, Decimal Places is defaulting to
| | 04:39 | Zeros. Negative numbers in red, that
Thousands separator. I am going to choose
| | 04:44 | zero for Leading Zeroes. I don't need
any zeros in front and there is a sample
| | 04:49 | of what that will look like,
if I would just say okay.
| | 04:51 | Now just before I do that because I am
adding some Decimal Places. I don't know
| | 04:55 | if these numbers are going to fit in
those cells anymore. So I might want to
| | 04:59 | change some of the Font attributes as
well. I am going to go up to the Font
| | 05:02 | tab. Right now Arial is being used
Regular Typeface. Look at the size set to
| | 05:07 | 14. Let's bump that down to 12
by selecting 12 right from here.
| | 05:13 | Now when I click OK the end result.
You can see everything fits, it's smaller
| | 05:18 | font but the formatting looks
different. Now I have realized that I really
| | 05:22 | don't need the decimal places because
everything is a double zero. Well there
| | 05:25 | is a couple here I that have sense on
because we are doing averages but maybe
| | 05:30 | they should be rounded up.
| | 05:31 | So I am going to click and drag across
of all of these numbers cells again and
| | 05:35 | instead of going back to Format Cells
and finding the currency format and the
| | 05:40 | decimal places. I can do that right
from here. First of all I have got Number
| | 05:45 | Format, Currency is currently selected.
There is Percent. Here is the Standard
| | 05:49 | Number Format. If I want to set the
default but over here is where I can choose
| | 05:53 | to Add or Remove Decimal Places and
I want to delete two decimal places.
| | 05:59 | So I click this button, one is gone.
You can see the averages just got rounded
| | 06:03 | up to 0.7. I am going to take out
another one by clicking this button again and
| | 06:07 | there is the end result and you can
see that everything is rounded up here
| | 06:10 | where they were actual sent values. I
will deselect by clicking outside to see
| | 06:15 | the end result.
| | 06:17 | Let's go up here to these cells where
we see 2005, 6 and 7 and then we have got
| | 06:20 | total for quarter and average for
the quarter. These two cells need to be
| | 06:25 | centered. I am going to click and
drag over those two. Go right from my
| | 06:28 | formatting toolbar to the center
button to center them. Just like the others.
| | 06:33 | Let's do some more now with
formatting the cell attributes themselves as
| | 06:37 | opposed to the contents. I am going to
click on this row here and drag across
| | 06:43 | and I am going to shade those cells.
Now I could go back up to Format and down
| | 06:48 | to Cells to do it from there or
because I have got shortcuts on my Formatting
| | 06:52 | Toolbar, this might be faster.
| | 06:54 | Here I can add Borders and Background
Colors. Background Color, when I click
| | 06:58 | the dropdown it gives me the color
palette and I just choose the background
| | 07:02 | color and I am going to go for a
subtle shade. So I am going to go this its
| | 07:06 | kind of light orange color, Orange 4
and when I click on that, I need to select
| | 07:11 | any other cell to deselect the selected
cells to see the end result. That looks good.
| | 07:16 | Let's go to these cells now from B3
across and down to G7 and let's do a border
| | 07:23 | around the whole thing. We will go
to the Border dropdown this time and
| | 07:27 | instead of putting borders around
every cell or just the left or the right or
| | 07:32 | left and right, top and bottom. We can
go entire range in this case, which is
| | 07:37 | B3 to G7, click that option. Deselect
to see what that looks like. There is our
| | 07:43 | border all the way around the outside.
| | 07:45 | Let's do a little bit more now. I am
going to go over here to word Total. This
| | 07:49 | is just a label. This is text. I am
going to change the font color, by going up
| | 07:54 | to my Formatting toolbar. I will click
the dropdown. Let's go to a bright red.
| | 07:58 | This is called Light red and when I
hover over it and let's bold that. That's
| | 08:02 | already bold so that's look good and
when I deselect I see the end result.
| | 08:07 | So lots of formatting options right
from the Formatting Toolbar of course you
| | 08:11 | can always to the Format menu and
select Cells to apply any or all of that
| | 08:17 | formatting from one
location here in OpenOffice Calc.
| | Collapse this transcript |
| Working with rows and columns| 00:00 | So far in this chapter we have been
focused on cell properties, cell contents,
| | 00:04 | in the last lesson we did a little
bit of formatting of cell contents and the
| | 00:08 | cells themselves. Now we are going to
shift our focus to working with rows and columns.
| | 00:13 | Sometimes you need to insert an
extra column or a row in between existing
| | 00:18 | columns and rows. Sometimes you want
to remove columns or rows. Hide the data
| | 00:22 | in those columns or rows. Even format
their widths and heights and that's kind
| | 00:27 | of thing we are going to do in this lesson.
| | 00:28 | Using our Revenues spreadsheet that
we have been working with in the last
| | 00:32 | couple of lessons. If you have been
following along you are ready to go. If you
| | 00:35 | have jumped to this lesson and you got
the exercise files and you would like to
| | 00:38 | follow along. Well, go to the Lesson
two folder, open up Revenues3 and you will
| | 00:44 | be all caught up and you will
see what I see here on my screen.
| | 00:47 | Let's say we want to add some data for
the year 2008. It looks like it needs to
| | 00:51 | go here in between column E and F. We
need to insert an extra column. Maybe we
| | 00:57 | want to increase the space between a
couple of rows. So we might want to insert
| | 01:02 | some rows. Let's start though with
our 2008 data that really belongs right
| | 01:07 | where our current column F appears.
| | 01:10 | Typically you would just click
anywhere in the column, go up to Insert Column
| | 01:14 | and you are done but I see a problem.
We have merged a number of cells here. So
| | 01:19 | from B1 across to G1, this is one
big cell. I can't insert a column where
| | 01:25 | merged cells exist. I can do that out
here. I can do it over here but not where
| | 01:31 | I have merged these cells.
| | 01:32 | So the first step is to unmerge those
cells. We can merge them back later after
| | 01:36 | we have inserted our new column. I am
going to click right here on Revenue
| | 01:39 | Growth and I am going to go to my
Formatting Toolbar, there is the Merge cells
| | 01:44 | button. It's already pressed in. When
I clicked on it, I unmerge those cells.
| | 01:49 | Don't be alarmed by the text, which is
centered on a very small cell now, kind of cut off.
| | 01:54 | Let's just change our alignment to left
align so we can at least see that.
| | 01:58 | We will deselect by clicking anywhere
in column F because when I go to insert
| | 02:02 | the column, it will be inserted by
default before my current location. Just to
| | 02:07 | the left of Column F, exactly what I
want. So let's go up to Insert, here you
| | 02:12 | will see things like Cells,
Rows and there it is Columns.
| | 02:17 | So we click on Columns and watch what
should happen. Brand new column, you can
| | 02:21 | see it's got the same formatting now
it's consistent. When we start typing in
| | 02:24 | numbers and values we will see
consistent formatting. The other thing that
| | 02:28 | happens is the column width it was
adjusted to coincide with the previous
| | 02:32 | column, which is our total.
It's the same width as this one.
| | 02:35 | We had a little bit of adjusting to do
after the fact and that's normal. For
| | 02:39 | now though let's click in cell F3 and
type in 2008 we need that data. I am
| | 02:44 | going to click down here in F4 and
let's just make up some numbers. I am going
| | 02:47 | to type in 58,000. Before I hit Enter
I wants you to watch what happens or
| | 02:53 | doesn't happen for my Total and my
Averages. When I hit Enter, nothing changes.
| | 02:59 | I have some formula
updating to do as well in a moment.
| | 03:02 | Let's get the other figures in here
though. I am going to type in 72,000. I am
| | 03:05 | going to type in 68,000 and 81,000. So
all of the formatting looks good it's
| | 03:17 | currency, no decimal places that's
perfect but my totals are not coinciding. If
| | 03:21 | I click on this one here in G4 and
look at my Input line, the range is C4 to
| | 03:27 | E4, it's missing F4.
| | 03:29 | So when I click here between the E and
the 4 I can backspace and type in F and
| | 03:36 | when I hit Enter, I have got my new
updated total. The easiest way to update
| | 03:40 | the other cells is not to go in there
individually but to click on the new
| | 03:43 | formula here in G4. Go to the bottom
right corner on the AutoFill button. When
| | 03:48 | I see the crosshair, we can drag straight
down to those other three cells and let go.
| | 03:53 | Now another option is to select the
range by dragging the border. I am going to
| | 03:57 | go to my average here, which is using
C4 to E4. I am going to click here where
| | 04:02 | it says E4. Notice that my range is
selected. So I can go to that little handle
| | 04:07 | in the bottom right corner. Just drag
across one extra cell the F4 is entered
| | 04:11 | for me. I hit Enter and there is new average.
| | 04:15 | Again using AutoFill, I am going to
click on cell H4 go down to the handle at
| | 04:20 | bottom right corner and click and drag
it down. Almost perfect. The numbers are
| | 04:25 | right but I lost some formatting
because I have copied the formula down. I have
| | 04:29 | got a cell with no border at the
bottom. That's why these two cells had no
| | 04:34 | border at the bottom.
| | 04:35 | Let's click and drag from G7 to H7. We
will go right up here to our Formatting
| | 04:41 | Toolbar and click the dropdown for
borders and choose this one which draws a
| | 04:45 | line at the bottom of the selected
cells. Now I can deselect and that looks
| | 04:49 | great. Another formula missing here
for the total for 2008 but it's here in
| | 04:54 | Column E. Let's click and drag
it over to Column F. Easy as pie.
| | 04:59 | The only thing now is the column width
and it wouldn't it be nice if all four
| | 05:03 | columns were the exact same width and
we knew for sure they were. Well let's
| | 05:07 | click and drag from C across to F
right here on the Column headings to select
| | 05:12 | all four columns and let's go up to
Format and down to Column. Over here on the
| | 05:19 | right we can choose a width. We can
use Optimal Widths. We can even Hide and
| | 05:24 | Show these columns. Right now
we want to adjust the Width.
| | 05:27 | Optimal width is going to figure out
the width for us. They might not be
| | 05:30 | identical. So let's go up to Width and
we will type in a value. I am going to
| | 05:34 | type in one inch. So I am going to
click and drag over what's there type in the
| | 05:38 | 1 and click OK. They are all adjusted,
they are all equal, when I deselect that
| | 05:45 | looks nice and consistent.
Everything fits I can see it all. I like that
| | 05:49 | formatting. So we have been inserted
our column. Let's go back up here to the
| | 05:53 | first row, click and drag from B1
across to now H1 and merged those cells again
| | 06:00 | and center whatever is in that
cell by clicking the Center button.
| | 06:05 | Next we are going to work with some
rows. If I want to insert a row. Let's go
| | 06:09 | to row 9 right here. We click on
the 9 and notice the whole row is now
| | 06:14 | selected. Well, I can go up to Insert
and choose Rows from here and a new row
| | 06:20 | is inserted or I can right click on
the number and choose Insert from there.
| | 06:25 | You can see each time I
am inserting some new rows.
| | 06:29 | Another option though would be just to
increase the row height to create that
| | 06:32 | extra spaces opposed to inserting rows.
So I am click and drag from 8 down to
| | 06:38 | 9, that's going to select the rows.
Now I am going to right click instead of
| | 06:41 | going up to a menu. Right-click, Ctrl+
Click with your one button mouse on the
| | 06:46 | Mac and you can see Delete Rows is
another option. So when we delete them they
| | 06:50 | are gone.
| | 06:51 | Now we can adjust the row height of
row 8 if we need more or less space and
| | 06:56 | when we go in between rows. We see a
double appear and we see that, we can
| | 07:00 | click and drag now and as we drag
we see the actual height that we are
| | 07:04 | creating. So I am going to go to
about 0.35 and let go. There is my extra
| | 07:09 | space, just the way I wanted it,
without inserting any extra rows.
| | 07:13 | Temporarily we might want to hide or
show data. Let's say the grand totals at
| | 07:17 | the bottom we want to hide them. Well
we go to row 9. Click on the 9 first to
| | 07:22 | select the row then right click. We saw
that from this pop-up menu an option is
| | 07:28 | to Hide. When we click Hide the entire
row is hidden. It also jumps from 8 now
| | 07:34 | down to 10. How do we get it back?
Click and drag from 8 down to 10 and right
| | 07:39 | click on 8 or 10 and choose Show the
hidden row in between those two is now visible.
| | 07:45 | Now we can do the same with the
columns if we wanted to clicking and dragging
| | 07:49 | from G to H. I can right click choose
Hide. They are gone. Now it jumps from F
| | 07:55 | to I and this is really handy by the way.
If you want people inputting data but
| | 08:00 | not seeing the results. They just
input their data. When you are ready to see
| | 08:03 | the results click and drag from F to I
right click and Show, and they are back.
| | 08:10 | So that's some row and column
modifications that you make here in Calc. Anytime
| | 08:15 | you need an extra row or a column it's
easy to insert, just as easy to delete
| | 08:20 | and of course, formatting is important
when you start inserting and removing
| | 08:24 | rows or columns.
| | Collapse this transcript |
| Working with sheets| 00:00 | One of the best things about
spreadsheet applications these days including
| | 00:04 | OpenOffice Calc is the amount of space
you have to work with. Spreadsheets can
| | 00:08 | get very, very large. You have got
lots and lots of rows, lots and lots of
| | 00:14 | columns and that's just one sheet. In
an entire spreadsheet file you can have
| | 00:18 | multiple sheets as well.
| | 00:20 | So in this lesson we are going to
talk about working with those sheets.
| | 00:24 | Not only are sheets handy if you run out
of space, but they are a great way to
| | 00:27 | stay organized as well. I have already
opened up a file that we are going to
| | 00:31 | work with in this lesson.
It's called DeptRevenues1.ods.
| | 00:36 | If you have got the exercise files,
you'll find it in the Lesson 2 folder. And
| | 00:39 | check it out down at the bottom we've
got three sheets. That's the default when
| | 00:43 | you create a new spreadsheet file.
Well here our sheets actually have data on
| | 00:48 | them, Sheet1 is our Client Services
2008 Revenue and you can see it's a grand
| | 00:53 | total from the various departments.
| | 00:55 | If I go down to Sheet2, there is our
Customer Service Revenue and you can see
| | 01:00 | it's broken down by month and we've
got different revenue streams here, and
| | 01:04 | over in the far right we have got
totals for each of those streams. We don't
| | 01:08 | have a grand total, but that's what's
going to show up on that very first sheet.
| | 01:12 | Sheet3 is our Training Services
Revenue. If I go back to Sheet1, the first
| | 01:17 | sheet, looks like we are missing one,
Consulting. We need the Consulting
| | 01:22 | Revenue as well. So the first thing
we are going to do, is look at ways to
| | 01:26 | insert a new sheet. One way is just to
go up to the Insert menu and come down
| | 01:32 | to Sheet.
| | 01:34 | When I click on Sheet, notice the
ellipses here, it's going to launch a dialog
| | 01:38 | box. I can insert the brand new sheet
before the current sheet, which would
| | 01:42 | make it the brand new number one,
after the current sheet. Of course, I can
| | 01:46 | choose the number of sheets I want to
insert, notice that the name by default
| | 01:50 | will become Sheet4.
| | 01:51 | I am going to click Cancel though.
Instead, I want one after Sheet3, so if I
| | 01:57 | click on Sheet3, another way to insert
a new sheet is to right click on that
| | 02:02 | last tab and go up to Insert Sheet.
Now if you are on a Mac with a single
| | 02:07 | button mouse, you know that you can
hold down Ctrl and click to get this same
| | 02:11 | pop-up menu.
| | 02:12 | Insert Sheet at the top also has an
ellipse after it, meaning the exact same
| | 02:16 | dialog box shows up. Well I want this
new sheet to go after the current sheet,
| | 02:21 | but I am going to leave it at Before,
just pretending that I forgot to choose
| | 02:25 | After current sheet. The No. of new
sheets, I am going to leave at 1, but again
| | 02:30 | I could put in a whole bunch if I
wanted to. I am going to do just the one,
| | 02:34 | that's all I need. I am
going to rename it on the fly.
| | 02:37 | Instead of renaming it after, let's do
it all in one step here. I am going to
| | 02:41 | click and drag over Sheet4 and I am
going to type in what it really should be,
| | 02:45 | which is going to be our Consulting
Sheet. So I am going to type in Consulting.
| | 02:51 | When I click OK, I get my brand new
sheet called Consulting. It's a blank
| | 02:56 | sheet; there is nothing on it and
notice that it did show up before Sheet3. I
| | 03:01 | really wanted it after Sheet3. So there
are a couple of different ways to move
| | 03:05 | sheets around as well.
| | 03:07 | One way is, my favorite way, just click
and drag the tab labeled Consulting and
| | 03:13 | with your mouse button pressed down
as you drag over Sheet3, you will see a
| | 03:16 | little arrow and eventually you will
see that arrow flipped to the other side
| | 03:20 | of Sheet3. That means it's okay to let
go and you have just changed the order,
| | 03:25 | easy as that.
| | 03:27 | Now there is another option, if you
right click on a tab, you'll notice
| | 03:31 | Move/Copy Sheets. When you click on
that, you get to choose the order here,
| | 03:36 | just by moving them up and down the
list. Using -move to end position- will
| | 03:40 | automatically move a sheet to the end.
I am going to click Cancel though; we
| | 03:43 | will come back to that a little bit later.
| | 03:46 | The other thing you might want to do is
remove sheets. Now notice that we have
| | 03:49 | got too many sheets now that we cant
see all four tabs. That's where these all
| | 03:52 | navigation buttons come in handy. We
can move to the right or to the left to
| | 03:57 | the end or the beginning. That
doesn't change us to those sheets. It just
| | 04:02 | allows us to see those sheets and
then we can click on them to move there.
| | 04:06 | I am going to show you a better way
to add a new sheet. I am going to right
| | 04:09 | click on Consulting and choose
Delete Sheet. Even though there is nothing
| | 04:15 | there, I am prompted to confirm I meant
to do that, I will click Yes. Now I am
| | 04:19 | back to my three sheets. Because we
have Sheet2 and Sheet3 looking almost
| | 04:24 | identical, just different tittles and
different labels, but the formatting is
| | 04:28 | the same, a better option would be
just to copy this sheet and then make the
| | 04:33 | simple changes to Consulting.
| | 04:35 | So that's what I am going to do now. I
am going to right click on Sheet3 and
| | 04:39 | not copy because that's just going to
copy the single cell that's currently
| | 04:43 | selected. It has nothing in it up here,
you can see that. What I really want is
| | 04:47 | Move/Copy sheet and we saw this a
moment ago, but in this case it's Sheet3 that
| | 04:53 | we want to copy. So we have to
click the checkbox next to Copy.
| | 04:58 | Now you can see it's going to be
inserted before Sheet3. I don't have the
| | 05:03 | ability to choose After. But that's okay,
as when I click OK I have got my new
| | 05:09 | sheet, which happens to be called Sheet3
_2, it's a second version of Sheet3. I
| | 05:14 | am going to click and drag it over to
the right, just like I did a moment ago
| | 05:18 | to change the order.
| | 05:20 | Now it's time to rename these, and
again we can do that from our right click
| | 05:25 | pop-up menu. I am going to right
click on Sheet3_2, I am going to go up to
| | 05:30 | Rename Sheet this time and call it
Consulting. Even though right now everything
| | 05:35 | appears to be Training Services,
remember it's a copy of our Training Services
| | 05:39 | sheet. So when I click OK, this one is
now called Consulting, meaning I should
| | 05:43 | go up to cell A1 where it says Training
Services and change it to Consulting Services.
| | 05:49 | I am going to go to my input line and
just double click on the word Training.
| | 05:51 | That selects it, so I can type in
Consulting right over it like it is not even
| | 05:55 | there. I am going to change my
streams as well. Instead of Instructor Led
| | 06:00 | Training Tutorials and Webinars, this
is Consulting. So I will do Phone, lot of
| | 06:04 | our consulting is done with a phone
package. We will do IP, In Person and we
| | 06:11 | will do miscellaneous, Misc.
| | 06:14 | Typically, people will come in here and
they will start deleting these values.
| | 06:18 | You could do that or you could just
simply type over them. If you are going to
| | 06:21 | remove the values, because these are
training values that we copy, click and
| | 06:25 | drag, in this case, from C7 all the way
to N9 and what we want to do is delete
| | 06:33 | just the values.
| | 06:35 | So when I hit my Delete key on the
keyboard, notice Delete All is selected for
| | 06:40 | me. I am going to deselect that so I
can pick and choose. So I don't want to
| | 06:44 | delete the Formats, I do want to
delete any Notes, Formulas, Date & times,
| | 06:48 | Numbers and Text, but I want to keep
the formatting. So when I click OK, looks
| | 06:54 | like it is empty, but now when I come
in here to enter some values, let's do
| | 06:57 | one for January for the Phone
revenues, let's type in 3,950 and hit Enter.
| | 07:04 | Notice that the format is still there;
it is still a currency format. In
| | 07:07 | Person, we are going to type in 15,995
and for Miscellaneous, let's do 2,950,
| | 07:15 | you got the idea. The totals are still
showing up over here because we didn't
| | 07:18 | delete the totals or the formulas
that give us the totals. We will continue
| | 07:22 | filling these in, but for the sake of
time, let's just go over to Sheet3 here,
| | 07:28 | which is labeled Training Services,
right click and rename that sheet.
| | 07:32 | I am just going to type in Training and
hit Enter. Sheet2, I am going to right
| | 07:37 | click on that and rename it. I am
going to do CustServ all one word and I am
| | 07:45 | going to go over to Sheet1, right click,
let's rename that one our Totals. Now
| | 07:52 | when I hit Enter that's locked in.
| | 07:53 | Now I have got them all named and
that's the beauty of working with multiple
| | 07:58 | sheets where it's well organized. We
know exactly what's on each of these
| | 08:01 | sheets by its tab and this going to
come in handy when we create these formulas
| | 08:05 | now for Customer Service,
Training and Consulting.
| | 08:08 | Let's go to the Customer Service field
here, which is going to be cell C7. We
| | 08:11 | are going to use a sum formula or
function. We will type in the equal sign,
| | 08:17 | =sum, that should be reviewed and open
round bracket, (. Now we just have to
| | 08:21 | select the range, but it's on a
different sheet, that's okay. We go to our
| | 08:25 | Customer Service tab, we click and
drag over these numbers, the totals to get
| | 08:30 | our grand total.
| | 08:32 | Notice the formula is being written up
here, =sum, not just the range but the
| | 08:37 | sheet CustServ and then the range. So
all we need is a closing round bracket
| | 08:42 | now to finish this off. We can click
the checkmark or hit Enter on keyboard and
| | 08:48 | look at that, it takes you back to the
sheet with a total. Now here is the real
| | 08:51 | test. We are going to auto fill
these next two. Will this work?
| | 08:57 | Well it is going to look like it worked.
When I click and drag the handle down
| | 09:01 | to the last cell, looks like I am
getting different values, it must be working,
| | 09:05 | not quite. When I click on the Training
cell, which happens to be C8, check out
| | 09:10 | the formula. It's using a different
range from the Customer Service sheet, so
| | 09:15 | that didn't quite work out.
| | 09:17 | We can select a different range
though, by clicking and dragging over the
| | 09:20 | existing range, as if we were going to
type over it. Then go to the Training
| | 09:25 | tab and select the appropriate range.
All we have to do is hit Enter. There is
| | 09:31 | our new total 578 and it's
coming from the Training tab.
| | 09:35 | So for Consulting, same thing, click
and drag over the entire range including
| | 09:41 | the name of the sheet, because we are
going to go to the Consulting tab. Even
| | 09:46 | though this is not complete we can
click and drag over the totals, hit Enter to
| | 09:50 | lock it in. Now as we start adding
more figures to the Consulting sheet, we
| | 09:55 | will see the grand total increase.
| | 09:58 | Now we will use one more formula, which
can be done very quickly, clicking here
| | 10:02 | in cell C11, all we have to do is
click on the Sum button here on our formula
| | 10:06 | bar, check out the range that's going
to be used. Yes, Calc is smart enough to
| | 10:10 | figure out there are numbers up here.
Let's use those in the Total when we hit
| | 10:14 | Enter, there is our grand total.
| | 10:17 | So thanks to being able to work with
multiple sheets. We can keep separate
| | 10:21 | departments apart from each other. So
we can track their own revenues here on
| | 10:25 | one nice neat little sheet. At the
very top we have got our grand totals, all
| | 10:29 | coming from different sheets.
| | 10:31 | I love the fact that we can use
multiple sheets in a spreadsheet here in
| | 10:34 | OpenOffice Calc. This will help us not
only stay organized, but ensure we will
| | 10:38 | always have enough room, enough
space to work with large amounts of data.
| | Collapse this transcript |
|
|
3. Formatting SpreadsheetsPage formatting | 00:00 | When I hear the word spreadsheet
certain words come to mind: boring, numbers
| | 00:05 | and calculations. Yes, spreadsheets
are all about calculating numbers and
| | 00:09 | arriving at answers, but it is also
important that you stay organized and keep
| | 00:13 | things looking nice, especially if you
plan on sharing your work with others.
| | 00:17 | So in this chapter we are going to
shift our focus over to formatting your
| | 00:20 | spreadsheets. In this lesson we will
begin with page formatting. If you plan on
| | 00:25 | printing out your spreadsheets,
page formatting can be very important.
| | 00:29 | So you can see I've already opened
up a file to work with, it is called
| | 00:32 | Expenses1.ods. So if you've got the
exercise files, go to the Lesson 3 folder
| | 00:37 | and open this one up, if you'd
like to follow along with me. It's a
| | 00:41 | one-sheeter. You can see I have got one
sheet down below and here I have got an
| | 00:45 | expense report, how this is
going to print I have no idea.
| | 00:49 | First of all, am I seeing all of the
information in this sheet? If I scroll
| | 00:53 | down you'll notice that down below
there are some more rows of data that I
| | 00:58 | previously did not see. It looks like
a Signature and a Manager Approval are
| | 01:02 | necessary. So this is definitely one
of those sheets I will be printing out.
| | 01:05 | There is a couple of different ways to
access the page formatting options. One
| | 01:10 | way is to go up to the Format menu.
| | 01:13 | When I come down to Page you'll notice
that the ellipses appears next to page
| | 01:17 | indicating I am about to launch some
kind of dialog box. So when I click on it,
| | 01:22 | notice that I get the Page Style
window. I have got tabs across the top,
| | 01:26 | Organizer, Page, Borders, Background,
there is Header and Footer, we will talk
| | 01:32 | about those later. There is a Sheet tab as well.
| | 01:35 | I am going to go back to Organizer.
Here is where we get to name our Page
| | 01:39 | Style. So if there is a certain style
we are going to use over and over again.
| | 01:43 | We are going to change all kinds of
settings. We can save it and then just
| | 01:46 | simply access the style from the
Category below; they've selected in this case,
| | 01:51 | Custom Styles.
| | 01:52 | So let's just give this a name as if we
were going to save this as our very own
| | 01:57 | Page Style for expense reports;
ExpenseReports, just like that, all one word.
| | 02:03 | Now I am going to go to the Page tab,
here is where I choose my page format,
| | 02:09 | Letter, 8.50 x 11, Portrait is
currently selected. Typically though this is
| | 02:13 | going to be Landscape, so I
am going to switch it over.
| | 02:15 | It stays Letter, but now it's 11 x 8.50.
Over here the Paper tray, if I have
| | 02:21 | several paper trays in my printer I can
select the exact tray that the paper is
| | 02:25 | going to come from. In my case, I have
just got a single tray. I can setup my
| | 02:29 | Margins here, notice that 0.75 and 0.
75 for my Left and Right margins and
| | 02:34 | almost an inch for the Top and the Bottom.
I am going to change these all to one inch.
| | 02:39 | So I am going to highlight the first,
type in 1, you will see a little preview
| | 02:43 | over here, a thumbnail representing
the Margins. I am going to hit my Tab key
| | 02:46 | now. I like hitting the Tab key because
it takes me to the next field but also
| | 02:50 | selects everything for me, so I can
type over it, type 1, Tab 1, Tab 1, there
| | 02:57 | we go. So I have got one
inch margins all the way around.
| | 02:59 | If I hit Tab again it goes to the
next field, which in this case is Page
| | 03:03 | Layout, and you can see Right and Left.
So if I was going to be printing out
| | 03:07 | multiple sheets, I have got Page
Layout options. You can see, I can print out
| | 03:12 | Right and Left, Mirrored, Only the Right,
Only the Left. So I have got certain
| | 03:16 | settings for the left or the right,
they can be mirrored, they can be different
| | 03:19 | for a right and left pages and so on,
but I am going to leave it at Right and
| | 03:23 | Left. It's a single page, so it
really doesn't matter in this case.
| | 03:26 | Let's go over to the Borders tab now.
Now the Borders tab without selecting
| | 03:31 | cells, like we did in a previous
lesson, are automatically applied to the
| | 03:35 | extremities of my worksheet here. So if
I wanted a border that goes all the way
| | 03:40 | around, all four borders, I can click
on this button under Line arrangement and
| | 03:44 | you can see I get a preview here.
| | 03:47 | Now I can choose the Line, Style and
thickness. So I am going to go to a nice
| | 03:50 | thick one, at 4 points. Look at the
preview. If I want to change the color I
| | 03:54 | can do that. I am going to go to dark
blue instead of black. I can even add a
| | 03:58 | shadow. I am going to put a shadow that
goes to the right and down a little bit.
| | 04:02 | Cast Shadow to Bottom Right, when I
click on that, you can see the distance for
| | 04:07 | that shadow is defaulted to 0.07. I
can increase that to give more of a 3D
| | 04:12 | effect. Then the color is gray, it is
not a pitch black which kind of makes it
| | 04:15 | more realistic looking. Let's go
up to Background here. How about the
| | 04:19 | background area of our sheet, of our
entire page? If we want to put in a color,
| | 04:24 | we could. You can see color is
selected up here in the dropdown but I could
| | 04:28 | also put a graphic in the
background if I wanted to do that.
| | 04:31 | Now I will leave it at Color, which
allows me to select from the color palette.
| | 04:36 | So if I went to a pale yellow for
example, this is a preview over here what
| | 04:40 | that's going to look like. We are going
to skip Header and Footer tabs, because
| | 04:44 | we have got a special lesson just for
Headers and Footers. We will go over to
| | 04:47 | Sheet. Here is where you can see Page Order.
| | 04:50 | Now we are going to make sure that
everything fits on one page. So the Page
| | 04:54 | Order doesn't really matter, but you
have got Top to bottom, then right or a
| | 04:57 | Left to right, then down. You can
see First page number to appear can be
| | 05:01 | selected. By default, it's 1 for the
first page but you can change that.
| | 05:05 | I don't need page numbering with a
single page and down below what is actually
| | 05:09 | going to print. If I have Charts and
drawings and Zero Values, other Objects
| | 05:14 | and Graphics, they are all going to
print. Do I want that Grid that I see in
| | 05:17 | the background to print?
Let's turn that on just for fun.
| | 05:21 | Any Notes you might add that are
aside from the spreadsheet, may also be
| | 05:24 | selected here by clicking this
checkbox. Column and row headers can also be
| | 05:29 | printed out. So see the 1s and the A, B,
Cs etcetera, I am going to leave that
| | 05:33 | off. Down below the Scaling, you can
see I have got mine Fit print range to
| | 05:38 | width/height and over here Width in
pages, Height in pages is both set to 1.
| | 05:42 | Other options though are to Reduce/
enlarge in selective percentage. So you have
| | 05:48 | got a scaling factor or you can
select a specific number of pages and I am
| | 05:52 | going to choose that one and make
sure 1 is selected. So when I click OK, I
| | 05:57 | don't see anything different here in
my work area, I am still working on my
| | 06:01 | spreadsheet on Sheet1. What's
it really going to look like?
| | 06:05 | Well, here's where we can use our
preview. So let's go up to the File menu and
| | 06:09 | down to Page Preview. The neat thing
about this is you can see things that you
| | 06:13 | have selected and go directly to the
page formatting from here as well. So here
| | 06:19 | you can see, it's taken the extremities
of my worksheet. There is the very last
| | 06:24 | cell to the very far right and there is
the very bottom row, Manager Approval.
| | 06:29 | There is my border, my background, I
don't need the full one inch margin over
| | 06:34 | here. I have got a little extra room,
so that's perfect and it's all fitting to one page.
| | 06:38 | You can see I'm on page 1 of 1 and I
had to reduce things to 74% to achieve
| | 06:42 | that effect. So I am currently in my
preview, but look at this I have got
| | 06:48 | Format Page available to me here from
this toolbar that appears next to the
| | 06:52 | Close Preview button. So I can go back
there and make some changes if I wanted
| | 06:56 | to. You can see I've still
got the Sheet tab selected here.
| | 07:00 | So if I didn't want the grid, I don't
like all those lines showing up, I can
| | 07:03 | turn that off. When I click OK I am
still in preview and that's what it looks
| | 07:08 | like without the grid, I like that
better. Now I can close my preview to return
| | 07:13 | back to my spreadsheet.
| | 07:15 | So those are some page formatting
options you can experiment with to get the
| | 07:19 | exact look and feel if you're after
with your specific project. Another thing
| | 07:23 | that we can do is work with those
Headers and Footers. That's coming up in the
| | 07:28 | next lesson.
| | Collapse this transcript |
| Using headers and footers| 00:00 | Continuing on the theme of formatting
your spreadsheets we are going to explore
| | 00:04 | a specific scenario this time. If you
need to print your spreadsheet out and
| | 00:08 | you know for a fact there is way too
much data to fit on to a single page,
| | 00:13 | you might want to consider
creating headers and footers.
| | 00:16 | A header is information that appears
at the top of every page. A footer,
| | 00:22 | information that appears at the bottom
of every page. Nice thing about headers
| | 00:25 | and footers when you create them
properly is you enter the data once and then
| | 00:30 | OpenOffice Calc takes over showing
that information at the top or bottom of
| | 00:35 | every page that prints.
| | 00:36 | So we are going to use this file I have
already opened up, very similar to the
| | 00:40 | one we were working with in the
previous lesson. This one though is called
| | 00:42 | Expenses2 and if you have got the
exercise files, you will find that in the
| | 00:46 | Lesson 3 folder, if you would like to
follow along. So as scroll down, you can
| | 00:51 | see I have got a little more data in
this file and down at the bottom I have
| | 00:55 | got a Signature area and
that's why I need to print this out.
| | 00:59 | So by printing it, I can actually get
those signatures and submit this to be
| | 01:03 | reimbursed for my expenses. You may
also see there is a darker border between
| | 01:08 | columns J and K here as well as between
rows 32 and 33. Those are actually page
| | 01:15 | break. So I can see it from this view
what it's going to look like when it
| | 01:18 | prints and where it gets cut off. I am
going to scroll back up. But a better
| | 01:22 | way would be to preview this and
that's just something I like to do before I
| | 01:26 | even start working with headers and footers.
| | 01:28 | Now we know we can go up to the File
and Page Preview or here is a shortcut. We
| | 01:34 | will click the Page Preview button
right here on the Standard toolbar, give
| | 01:37 | that a click. Look at that, we are
using Letter size paper, I can tell, 8.50 x
| | 01:41 | 11, but it's turned on its side landscape.
| | 01:44 | Look at the formatting; this is the
formatting we applied in the previous
| | 01:48 | lesson. So it's here in this file as
well. Down in the bottom left corner I can
| | 01:53 | see that there is actually more than
one page. I am currently on page 1 of 2.
| | 01:57 | If I hit my Page Down key, there is
the rest of my spreadsheet data. Now I am
| | 02:01 | going to press Page Up
again to go back up to the top.
| | 02:05 | Now let's say I want page numbering
or dates, for example, or any other
| | 02:09 | information for that matter to appear
on every page. In this case, I am going
| | 02:13 | to go to Format and I can do it from
the Format menu or as we learned in the
| | 02:18 | previous lesson here in our Page
Preview we have access to the Format options
| | 02:22 | to format our page.
| | 02:24 | So let's click the Format Page button
right here. These are the two tabs we
| | 02:28 | skipped over in the previous lesson,
Header and Footer. As I click on Header
| | 02:33 | and as I click on Footer, you can see
that neither of these is actually turned
| | 02:37 | on. We have to turn these on
and then make our adjustments.
| | 02:41 | So to turn the footer on, I click the
checkbox next to Footer on. Let's go over
| | 02:46 | to the Header tab, we will turn this
one on as well. The first checkbox you
| | 02:51 | will see right below is Same content
left/right. So if you are setting up left
| | 02:55 | and right or odd and even pages
differently, you can choose not to have the
| | 02:59 | same content on the left page as the
right. Maybe you want to put page numbers
| | 03:04 | in the bottom left corner of your odd
pages and bottom right corner of the even
| | 03:08 | pages. Well in that case you
want to turn this checkbox off.
| | 03:12 | But I do want this same thing on every
page whether it's the left or the right
| | 03:15 | side. I can adjust Margins for my
header, Left and Right margins, and I am
| | 03:20 | going to do that. I want it to match
my actual spreadsheet. So in this case I
| | 03:24 | am going to click and drag over the
Left margin values. Instead of using the up
| | 03:28 | and down arrows to change this I
can click and drag and just type 1 to
| | 03:31 | represent one inch. When I hit my Tab
key, check it out, one inch is in there.
| | 03:36 | I have selected the next field,
so I can just type 1 there as well.
| | 03:40 | Next we have Spacing, so if you want
to adjust the Spacing that's going to be
| | 03:44 | used in your header, you can do that
here. Zero is the default, so if you
| | 03:48 | wanted to spread it out a little more,
you can increase that. I am going to
| | 03:51 | leave it as it is. The Height,
although it shows up here as 0.1 inch, AutoFit
| | 03:56 | Height is also selected so as I start
entering data and formatting that data,
| | 04:01 | it's automatically going to increase
or decrease to fit the information I put
| | 04:06 | in the actual header.
| | 04:08 | So what is going to go in the header,
I am going to use the Edit button to do
| | 04:12 | that. Here I have got the Left, Center
and Right areas to choose from. I can
| | 04:17 | use any or all of these areas. So let's
start with the Left area, and remember,
| | 04:23 | this is at the top of every page. I
am going to type in DK Consulting.
| | 04:27 | Now you will notice that this is just a
couple of words that will appear at the
| | 04:34 | top of every page in the Center. I
can leave this blank and go over to the
| | 04:39 | Right area and start inserting some
custom items. Down below you can see here
| | 04:44 | next to Custom Header, I have
got buttons for adjusting text.
| | 04:48 | So right now I am just using the
default styles. Over here I can insert a Title
| | 04:53 | and Sheet Name. Now the Sheet Name is
going to be taken right from the file
| | 04:57 | name itself, so I don't have to type
anything in there. I have got some page
| | 05:01 | numbering options. I am going to
choose page number, but I want the word Page
| | 05:06 | there first.
| | 05:07 | So I am going to type in Page, leave a
space and then insert the number. Now I
| | 05:11 | don't type a number here, but a 1 does
appear because it will show up that way
| | 05:16 | on my first page. I am going to leave a
space, type the word of, leave another
| | 05:21 | space and put this guy in there,
which is the number of pages, total pages.
| | 05:26 | So Page 1 of 2 will appear in the top
right of page 1, Page 2 of 2 will appear
| | 05:32 | in the top right of the second page. I
have also got some other options here. I
| | 05:36 | have got Date and Time options. I am
going to save those for the footer. For
| | 05:41 | now I am just going to go back to my
Left area, DK Consulting, click and drag
| | 05:46 | over that and go to my Custom Header
section and click on Text Attributes. I am
| | 05:50 | just going to make this a little bit
smaller. I am going to go down to 8
| | 05:53 | points. I am going to keep the same
Font, the same Typeface, I do have access
| | 05:58 | to Font Effects,
positioning etcetera. I will click OK.
| | 06:03 | Now I am going to click OK to lock that
in. So I have edited my actual header,
| | 06:08 | it's turned on, so the same content is
going to appear on the left or the right
| | 06:11 | pages when this prints out using these
margins. I am going to click OK. I am
| | 06:17 | still in my Page Preview, that's a nice
thing and there is my DK Consulting, an
| | 06:22 | inch in from the left, Page 1 of 2 in
from the right hand side and if I hit my
| | 06:28 | Page Down key, check it out DK Consulting,
Page 2 of 2. Let's go back to Format Page.
| | 06:35 | Now there are some more options, but we
are going to look at those options with
| | 06:38 | the Footer tab now. So we turn the
footer on, I am going to leave the Left
| | 06:42 | margin at zero, Right at zero as well.
I am going to increase the Spacing here
| | 06:46 | though. Actually I want 0.5 so I am
going to take out whatever is there, 0.05.
| | 06:53 | AutoFit the Height, I am going to
leave that in there just like that. I am
| | 06:57 | going to go to Edit and in the Center
area this time, I am going to put in the
| | 07:02 | date and the time, so I always know
when it was printed. There is the date,
| | 07:07 | leave a space, time and I'm done.
| | 07:10 | I can format this as well. So I will
click and drag over to the Center area and
| | 07:14 | go to my Font attributes here and I am
just going to use a different font. You
| | 07:21 | can choose any font you like here.
I am going to go down to one that's
| | 07:24 | definitely going to look different,
Times New Roman, and get a preview of it
| | 07:29 | down here, Regular 10 points,
and I bump that up to 12.
| | 07:35 | I click OK and I have locked that in.
If I wanted to, I can use any of these
| | 07:41 | again if I needed to. I think it might
be good to have the Sheet Name down in
| | 07:45 | the bottom left, so I am going to
click there and put in the Sheet Name.
| | 07:47 | Now in this case, Sheet1 is the default
name and is the name of this sheet, but
| | 07:53 | if I change the Sheet Name it will
change here in my footer as well. Again I
| | 07:58 | could format that using my Custom
Footer button for Text Attributes, but I am
| | 08:02 | going to click OK. The only other
thing we are going to do now is play around
| | 08:07 | with the footer by clicking the More
button for more formatting options. Now in
| | 08:12 | this case I can add Borders and Backgrounds, j
ust like we saw when formatting the page.
| | 08:18 | So if I wanted it to match, I am going
to move this out of the way. My actual
| | 08:21 | spreadsheet area, I could use the
same background, and I could use the same
| | 08:26 | border that I did for my page. But I
don't want to do that exactly, I am going
| | 08:30 | to go over to Background here,
just choose a light gray.
| | 08:33 | So I click on the light gray and I
click OK. When I click OK again, I get my
| | 08:39 | preview. There it is, down at the
bottom of the page. I am going to press Page
| | 08:44 | Up to move up to the first page, I see
my header at the top. I see my footer,
| | 08:49 | notice the space, spacing has been
increased, and I am ready to close my
| | 08:54 | preview. I don't see the header and
footer here in this view, only in the Page
| | 08:58 | Preview, so at anytime as you make
changes you want to go back to Page Preview.
| | 09:03 | For example, if I come down to where it
says Sheet1 here and I right click. If
| | 09:09 | you are on a Mac that means hold down
Ctrl and click if you have got a single
| | 09:12 | button mouse. We'll select Rename Sheet.
I am going to type in Master. When I
| | 09:20 | click OK I have renamed the sheet,
which means if I go to my Page Preview and
| | 09:25 | check it out down there, now it
says Master. Close the Preview and that
| | 09:30 | concludes headers and footers.
| | Collapse this transcript |
| Conditional formatting| 00:00 | In this lesson we are going to
explore conditional formatting, that is,
| | 00:03 | formatting that will be applied to
cells only when certain conditions are met.
| | 00:08 | We set up those conditions. The other
part of conditional formatting is we are
| | 00:12 | going to set up some styles for cell
formatting. We are going to use a file,
| | 00:16 | I have already opened up here. It's
called RevMaster1. If you have got the
| | 00:20 | exercise files and if you would like
to follow along, go to your Lesson 3
| | 00:23 | folder. You will find that one there.
| | 00:26 | This is our Totals sheet that we are
looking at, and the totals that we see on
| | 00:30 | this sheet are based on data that's
entered in the other sheets, Customer
| | 00:34 | Service, Training and Consulting.
Notice we have also got in column E some
| | 00:39 | targets and if we look to the left,
some of these targets are being met, some
| | 00:45 | are getting close and some are way off.
| | 00:47 | So here's an ideal scenario for some
conditional formatting. We want, for
| | 00:51 | example, targets that are way off to
show up in bright red so they stand out or
| | 00:57 | wherever we have exceeded a target,
maybe that should stand out as well with
| | 01:00 | some different formatting. So the
first step is to setup that formatting and
| | 01:05 | then use it in the conditional
formatting that we set up, it will all make
| | 01:09 | sense in a second.
| | 01:10 | Now just click anywhere in an empty
cell so we are not formatting actual data.
| | 01:15 | I am here in cell A17, for example. I
am going to go up to the Format menu and
| | 01:20 | I am going to set up my styles for the
conditional formatting we are about to
| | 01:24 | set. So down here Styles and Formatting,
F11 is the keyboard shortcut. Here you
| | 01:30 | see Default, we have got different
headings and results. We are going to create
| | 01:34 | our own based on the Default style.
| | 01:37 | So up here in the top right corner we
have got a little paint bucket to fill
| | 01:42 | formatting modes, but this is the
one we want, New Style. We are going to
| | 01:45 | create a new style based on whatever
selected which happens to be default over
| | 01:50 | here. So I am going to click on that
and I am just going to give it a name for
| | 01:53 | now. Let's call it Exceeds. When I
click OK I have got a new style based on
| | 02:00 | Default, so it's no different from
the Default format at this point.
| | 02:04 | Let's add another one based on the
selection, in this case Exceeds, so I am
| | 02:08 | going to type in Below, and then
click OK. Let's add one more; this one is
| | 02:17 | going to be called On Target. So
those are the three scenarios for these
| | 02:24 | values, they are on target, above or
well below. Now that we have got them set
| | 02:30 | up, it's a matter of selecting the formatting.
| | 02:32 | So I am going to start at the top
with Below. I am actually going to right
| | 02:36 | click on this. If you are on a Mac, Ctrl
+Click if you have got a single button
| | 02:40 | mouse. Select from the little pop-up
menu, Modify. This opens up our Cell Style
| | 02:46 | dialog box and here for the Font, I
am going to click on the Font tab. I am
| | 02:52 | going to choose Bold, keep the same
font, the same size, but bold it. Under
| | 02:56 | Font Effects, remember this is Below. I
am going to change the color here, for
| | 03:00 | the Font color, to red. When I click OK,
I have now modified the Below style.
| | 03:08 | Now I am going to go down to Exceeds. I
am going to right click on that one and
| | 03:12 | choose Modify. Again, Font Effects,
I am going to change the color. Now
| | 03:17 | remember, this one is Exceeds, so let's
go to Green, back to the Font tab, and
| | 03:23 | Bold. Again, I click OK and let's go
to On Target. We'll right click on On
| | 03:30 | Target, choose Modify, and for this one
I am going to just keep it Bold but go
| | 03:36 | over to Font Effects. I am going to
switch this back to Black. Just scroll up
| | 03:41 | near the top, click on Black and click OK.
| | 03:45 | So, so far I have just set up the
styles that I am going to use. If I don't
| | 03:48 | have these styles, I won't be able to
pick them when I set up my conditional
| | 03:51 | formatting. The conditional formatting
will be applied to these three cells. So
| | 03:56 | I am going to close this up and I am
going to select those three cells. Just
| | 04:00 | click and drag from C7 to C9, go up to
Format and down to Conditional Formatting.
| | 04:09 | Now we can set up to three conditions,
Condition 1, if the Cell value is
| | 04:14 | greater than, so I am going to change
my dropdown from equals to, to greater
| | 04:19 | than. Over here I am going type in 600,
000, that's my target. If I move this
| | 04:26 | out of the way, you can see 600,000 is
the target for all three of those cells.
| | 04:31 | So Cell value is greater than 600,000
I want to use this Cell Style. This is
| | 04:36 | one is Exceeds.
| | 04:38 | Now if I hadn't set that up I wouldn't
be able to choose it from this dropdown
| | 04:41 | list. Condition 2, the Cell value is
between, I am going to change equals to,
| | 04:47 | to between. Let's say it has to be
between 500,000 and 600,000. So that's
| | 04:55 | exactly what I am going to type in
these two fields that appear, and the Cell
| | 04:59 | Style for that is going to be On Target.
Condition 3 is well below. So the Cell
| | 05:07 | value is, instead of equal to, let's
go to less than. Here I will choose
| | 05:14 | 500,000.
| | 05:14 | So now we have got all of those numbers
covered. Below 500,000, we want that to
| | 05:23 | be Below. If it's between 500,000 and
600,000, On Target, and if it's greater
| | 05:31 | than 600,000, Exceeds. Click OK.
Deselect by clicking on any cell and check out
| | 05:37 | the format in here.
| | 05:39 | I have got greens, reds and black but
bold. Conditional Formatting, now if I
| | 05:45 | was to go in here and see this number
lies above 500,000 it will automatically
| | 05:50 | turn black. If Training goes over 600,
000, it will automatically turn green.
| | 05:56 | That's all based on the numbers that I enter
from these other tabs here in my spreadsheet.
| | 06:01 | So if you are following along, I
encourage you to go into those spreadsheets,
| | 06:05 | change some values up, increase some
values, bring Training over 600,000 or
| | 06:10 | maybe bring it below 500,000 and watch
the formatting change. It is all based
| | 06:15 | on the conditions you set up and that's
why it's called Conditional Formatting.
| | Collapse this transcript |
|
|
4. Working with ChartsInserting a chart with the Chart Wizard| 00:00 | Have you ever heard the phrase,
"A picture is worth a 1,000 numbers"?
| | 00:04 | Probably not, because I just made that up.
But just as a picture can be worth a 1,000
| | 00:09 | words, here in a spreadsheet when
you are looking at numbers to really
| | 00:14 | understand those numbers, a
picture can be worth a 1,000 numbers.
| | 00:18 | So in this chapter we are going to
explore working with charts, graphical
| | 00:23 | representations of numbers. So if you
need to see contrast and trends, for
| | 00:29 | example, you might not be able to see
that just staring at numbers in rows and
| | 00:33 | columns. Put it into a graph and a
whole other meaning can present itself.
| | 00:38 | So here we are working with our
Revenue Master Sheet, it's called RevMaster2.
| | 00:44 | If you are following along and you
have got the exercise files, open this one
| | 00:47 | up from the Lesson 4 folder. This is
one that we have worked with in previous
| | 00:51 | lessons; you can see we have got tabs
for the various departments, which feed
| | 00:55 | our total sheet here. By department, we
have got three departments with totals.
| | 00:59 | We can even see last year's totals
for 2007 and the grand totals as well.
| | 01:05 | Now if we want to see if there are any
trends or if we want to compare one year
| | 01:09 | to the other, it might be best to put
this into some kind of graph. So in this
| | 01:14 | lesson, we are going to start by just
simply inserting a chart. Then as we move
| | 01:19 | through the lessons in this chapter,
we will look at how we can make
| | 01:22 | modifications and even
formatting changes to our chart.
| | 01:26 | But we are going to start by
inserting the chart, and there is a couple of
| | 01:29 | different ways to do that. First, you
can see I have clicked down here in cell
| | 01:33 | A15, just clicking in a general
vicinity of where you want the chart to show up
| | 01:37 | is a good idea, even
though you can move it later on.
| | 01:40 | Let's go up to the Insert menu, and
from here, you will notice down near the
| | 01:45 | bottom, Chart. Check out the icon,
looks like a pie chart. You will also see
| | 01:50 | that one up here in the Standard
toolbar, so the shortcut is just to click on
| | 01:53 | Chart right up here. So one click to
close up the menu, another click to start
| | 01:58 | the Chart Wizard. I am going to move
this over so you can see an actual chart
| | 02:03 | has started to be drawn in the general
vicinity of my selected cell. Now I can
| | 02:09 | choose a chart type, step one of the
Wizard. Is it going to be Column or Bar?
| | 02:14 | Very similar; one is
horizontal, the other is vertical.
| | 02:17 | Typically, if you are working with a
single Data Series, pie charts are pretty
| | 02:21 | good as our areas. You can see we have
got access to Line and XY charts. This
| | 02:26 | is going to let us see comparisons
between different sets of data. We have got
| | 02:31 | Net, Stock, Column and Line, so a
combination chart if we want to do as well.
| | 02:37 | I am going to go right up here to
Column and I am going to turn on my 3D Look.
| | 02:42 | Now I can select from different types
of charts, using the column style, here
| | 02:48 | you can see they are side by side,
this is called Normal and we have got
| | 02:51 | Stacked, we have got Percent Stacked,
so it always comes out to a 100%, but you
| | 02:56 | can see the different relationship
between the data. You can see we have got a
| | 03:01 | Deep, so it's overlapping as well.
Down below we can change the shape if we
| | 03:05 | wanted to, to Cylinders, Cones, and Pyramids.
| | 03:07 | So I am going to go back to Box, keep
the Normal one selected and move on to
| | 03:12 | the next step by clicking the Next
button. Now I get to choose a data range.
| | 03:17 | Now I could come in here and just
start typing it in, try and see it back
| | 03:20 | there, looks like it is going to be
this range in here. Or I can click this
| | 03:24 | little button on the right, which is
going to allow me to temporarily collapse
| | 03:28 | the Wizard. So let's give it a click
and go in here and click and drag over the area.
| | 03:33 | Now in this case, I want to keep the
labels, so I want to keep Department Total
| | 03:38 | 2007. Down here I want to keep
these labels as well for the different
| | 03:42 | departments. So I am going to click and
drag from D5 across and down to column
| | 03:47 | D row 8. When I let go, you can see
it inserts the Data Range for me. I am
| | 03:52 | going to just move this back up here, so
you can see I am previewing on the fly.
| | 03:57 | So here I have got Total in 2007, I
have got the different departments down
| | 04:02 | below, by default. That's because the
Data Series is using the columns. So in
| | 04:07 | this case Total in 2007, but rather use
the rows, which are the departments, I
| | 04:13 | can switch it over. Now I am
looking at Customer Service, Training and
| | 04:16 | Consulting to a different view, easily
change, just by clicking the appropriate
| | 04:21 | radio button. I am going to go back to
the Data series in columns and I do want
| | 04:25 | to make sure that the first row is
being used for labels. That's right at the
| | 04:28 | departments.
| | 04:29 | I also want to make sure that the first
column is being used as a label here as
| | 04:33 | well, Customer Service, Training and
Consulting up here down at the bottom. So
| | 04:37 | all I am getting are the Total column
and the year 2007 column. I am going to
| | 04:42 | click Next to move on to the Data Series.
| | 04:45 | If for some reasons you had extra
columns, blank columns to create space or
| | 04:50 | extra rows, and you don't want to
include them you can select the Data Series
| | 04:54 | here and simply remove them, or if you
are missing one, you can go to the Add
| | 04:59 | button and go back in here and select
another range. But we have got everything
| | 05:03 | we need so all we have to do is click Next.
| | 05:06 | Now the Chart Elements, we can start
adding to this. Let's give it a Title. I
| | 05:10 | am going to do that, Revenues, and
down for Subtitle 2007-2008. I can also
| | 05:21 | create an X and Y axis if I wanted to,
so labels for the left and across the
| | 05:26 | bottom here. For my Y-axis I am going
to do a USD, US Dollars. The X-axis down
| | 05:33 | across the bottom, these are the
Departments and I don't need the Z-axis at
| | 05:39 | all. So that will be drawn up for me
on the fly. I can move the legend around
| | 05:45 | or turn it right off if I don't need
it. You can see that creates a little
| | 05:48 | extra space here, but I do like the
legend and I do like it on the right.
| | 05:53 | If I choose Bottom, for example, looks
okay, but a little bit too cramped down
| | 05:58 | there so I am going to go back to the
Right. That looks great, I am going to
| | 06:01 | click Finish. I have got my chart,
notice the Chart has little handles around
| | 06:06 | the outside and as I move to the
border I see the four-sided arrow, so I can
| | 06:11 | click and drag to move this around. I
am going to stretch it out. Going to this
| | 06:16 | handle on the right side, I see the
double arrow, now I can stretch it.
| | 06:19 | Automatically the data stretches with
it. That looks pretty good. When I am
| | 06:24 | done I click off the
chart to see the end result.
| | 06:27 | Now I shouldn't say end result, because
if you want to go back and make changes
| | 06:30 | you can at any time. Changes can be
made to the data as well as to the
| | 06:35 | formatting. In the next lesson we are
going to focus on the actual chart and
| | 06:40 | the chart data itself.
| | Collapse this transcript |
| Modifying a chart| 00:00 | When you insert a chart into a
spreadsheet, like we did in the previous lesson
| | 00:04 | using the Chart Wizard, you may not be
totally satisfied with the end result.
| | 00:08 | Well, it's good to know you can always go
back and make a few adjustments. That's
| | 00:12 | what we are going to do in this lesson;
we are going to make some changes to
| | 00:14 | the chart itself as well as the data that's
generating the results we see in the chart.
| | 00:20 | We are still working with our Client
Services Revenue sheet here from the
| | 00:24 | previous lesson, so if you have been
following along, perfect. If you have
| | 00:27 | jumped to this lesson and you have got
the exercise files, no problem, you can
| | 00:31 | get all caught up by going to
Lesson 4 folder and opening up this one,
| | 00:34 | RevMaster3.ods. You will have
exactly what I have here. The first thing I
| | 00:41 | notice is that my legend over here is
using certain data on my spreadsheet to
| | 00:46 | show Total in 2007.
| | 00:48 | Well, I am not really comparing Totals
to 2007. I am comparing Totals for 2008
| | 00:53 | to Totals from 2007. So I would like
to change this to show 2008. The easiest
| | 00:59 | way to do that is just to change the
data and the chart will automatically
| | 01:03 | update itself. So if I come up here and
click in cell C5, where it says Total,
| | 01:08 | and just type in 2008 and hit Enter or
Return on my keyboard, you will see it's
| | 01:14 | updated automatically and
instantaneously here in the chart. Revenues 2007
| | 01:20 | to 2008, everything else looks good, except
for maybe the location, maybe the size.
| | 01:25 | So I am going to click anywhere on the
chart itself and you will notice those
| | 01:29 | handles that appear. Now when I move to
a handle with my mouse pointer, I will
| | 01:33 | see a double arrow meaning I can
click and drag to change both height and
| | 01:38 | width. If I go to the corner I
can do both at the same time.
| | 01:41 | So if I want to stretch this out a
little bit, I might go to the right handle
| | 01:44 | here on the right side in the middle
and just click and drag outwards, maybe
| | 01:48 | make it a little bit taller, I will
go to the bottom left corner. I get a
| | 01:51 | double diagonal where I can click and
drag both down and over to the left and right.
| | 01:58 | Once I have got the size I can also
move this. If I move anywhere inside the
| | 02:03 | chart itself, notice the four-sided
arrow, that means I can click and drag this
| | 02:07 | like an object and all I have to do is
select the cell where I wanted to go to.
| | 02:11 | So for example, if I let go up here
around cell F6 or so, you can see it's
| | 02:16 | overlapping my data. So I am going to
start over here in the left side and I am
| | 02:20 | going to click and drag over to column F.
That's a little too far, just drag it
| | 02:24 | over and right there is a good spot,
maybe up just slightly. Now I have got my
| | 02:30 | data to the left of the chart
that is generated from that data.
| | 02:35 | So it's really simple to click and drag
to move things around, resize them, and
| | 02:40 | of course, there is a lot of
formatting we can do to a chart as well.
| | 02:44 | That is coming up in the next lesson.
| | Collapse this transcript |
| Formatting a chart | 00:00 | In the previous lesson we made a
couple of modifications to our chart here in
| | 00:04 | OpenOffice Calc. We changed the size,
the location, we made a couple of changes
| | 00:09 | to the data by changing data in our
spreadsheet that's automatically updated we
| | 00:13 | know in the chart itself. Now it's
time to work on the look and feel of our chart.
| | 00:17 | We're going to talk about formatting
our charts in this lesson and we are going
| | 00:21 | to continue to use the same file we
have been using throughout this chapter.
| | 00:24 | If you've jumped to this lesson and you
have got the exercise files and you would
| | 00:27 | like to follow along, you can go
to the Lesson 4 folder and open up
| | 00:30 | RevMaster4.ods and you
will have what I have here.
| | 00:34 | The first step in formatting our chart
is to select it. So let's to click on
| | 00:39 | our chart and right away we see the
handles around the outside indicating it is
| | 00:43 | selected. The other thing that happens
is our formatting toolbar just change to
| | 00:47 | give us the tools we need when we need
them. So a lot of these apply to working
| | 00:51 | with an object like a chart, changing
some of the line properties, the fill
| | 00:55 | properties. We can rotate and
change the anchor properties.
| | 00:59 | If we've got multiple objects, maybe
we've got multiple charts or maybe we've
| | 01:03 | got some clip art photos and charts
in there, we can rearrange them so they
| | 01:06 | show up in the right order. We can also
change things like alignment, are from
| | 01:11 | the formatting toolbar. We can also
change formatting by going to the Format
| | 01:15 | menu or by right clicking on the
object. We are going to try all three.
| | 01:19 | You may have also noticed that we've
got this little anchor icon, you may have
| | 01:22 | seen that in the previous lesson
as well. When we create a chart it's
| | 01:26 | automatically anchored to the cell we
clicked on. Remember we clicked over here
| | 01:30 | in this area so that when we created
our chart it showed up in that general
| | 01:33 | vicinity.
| | 01:35 | That's because the anchoring is
defaulted to stay anchored to a cell, what does
| | 01:39 | that mean? Well, for example if I go
over here between columns D and E, you see
| | 01:44 | the double arrow up here and I want to
widen column D. If I click and drag to
| | 01:49 | the right, watch what happens to my
chart. It moves over to the right, because
| | 01:53 | it's anchored to one of these cells.
| | 01:55 | If I click Undo, it comes back, and
if I click on the object and change the
| | 02:01 | anchor to be anchored to the page as
opposed to a cell -- I am going to do that
| | 02:04 | by clicking the Change Anchor button.
Notice the anchor logo just disappeared.
| | 02:09 | The icon has gone. That means it's
automatically toggled over to Anchor to
| | 02:13 | Page, two options are page or cell.
| | 02:15 | Now, that means if I come up here
between columns D and E and click and drag
| | 02:19 | across to the right, I change the
column width, but my charts stays put, it
| | 02:24 | stays right where it is on the page
and now I can't see the data in behind,
| | 02:28 | because it's in behind my chart. I am
going to undo that to bring it back.
| | 02:33 | So, whether you want it anchored to a
spot on the page or to a specific cell,
| | 02:37 | you can use this little button up here,
just click on the object and change the
| | 02:41 | anchor. You could also do that if you
wanted to from the Format menu. Down here
| | 02:46 | you will see anchor and over here
there are those two choices, To Page or To Cell.
| | 02:51 | I am going to leave mine at To Page. I
can also change alignment of objects.
| | 02:55 | Now in this case we have only got
one selected, so there are no alignment
| | 02:59 | options. But if we go down to
Arrange you can see, these are some of the
| | 03:02 | options we saw up on the formatting
toolbar. We can flip objects around, not
| | 03:07 | the case with a chart but if we were
using clip art or photo, we can flip
| | 03:12 | vertically or horizontally. We can group
objects together if we've got multiple objects.
| | 03:17 | Down here where we've got graphic,
we can change Position and Size, Line
| | 03:21 | attributes, Area attributes also found
on the formatting toolbar, even Define
| | 03:26 | Text Attributes right from here. I am
not going to select any of these right
| | 03:29 | now, I am just going to click over
here on my chart, make sure it's still selected.
| | 03:33 | Now, I am going to right click so use
the other mouse button. If you are on a
| | 03:38 | Mac with a single button mouse, hold
down your Ctrl key and click to see this
| | 03:43 | little pop-up menu. There is Position
and Size, we've also got because this is
| | 03:47 | a chart, Description and Name
option so we can name our chart. There is
| | 03:51 | Arrange, Alignment, there is the Anchor
again. There is Grouping, Cut, Copy and Edit.
| | 03:57 | So let's go up here to position in size.
So here you can see the exact X and Y
| | 04:03 | position and you can see down below
the size. So if you wanted it to be an
| | 04:08 | exact Width and Height, for example, 5
x 3, you could come in here and take out
| | 04:14 | the 0.42 and come in to the Height down
here and take out the 0.09 and as long
| | 04:21 | as Keep ratio is not selected you will
be able to change both the Height and
| | 04:25 | the Width. I am just going to hit my
Tab key to move it to the next field and I
| | 04:29 | am going to see the results
over here when I click OK.
| | 04:33 | So that's the exact size I want. Let's
right click again on our object. This
| | 04:38 | time we'll go down to name. We go down
to name and we can name our chart. I am
| | 04:42 | going to type in Revenues. I'm going to
do 07/08 and when I click OK or hit the
| | 04:51 | Enter key I've saved in my chart
given it its own name, which is nice.
| | 04:55 | You may have multiple charts and you
want to be able to name them to keep them
| | 04:58 | organized. I'm going to right click and
go down to Description. Here I can give
| | 05:02 | a title and a description if I wanted to.
So not just the name of the chart but
| | 05:06 | a title and a description as well.
I don't need that, it's kind of
| | 05:10 | self-explanatory. So I'll click Cancel.
| | 05:13 | Let's right click again and let's go
down to Edit, down at the very bottom.
| | 05:18 | Now, when I click on Edit, I am
actually inside the chart. Now notice the
| | 05:21 | handles have changed. I have also got
my data selected over here. This is the
| | 05:25 | data that's being used in the chart,
and this is kind of like what I saw when I
| | 05:29 | created the chart in the very first place.
| | 05:32 | Up here I have got some new buttons to
change the Chart Type if I wanted to. I
| | 05:36 | could turn the Horizontal Grid on and
off right from here. Clicking it turns it
| | 05:40 | off. I've lost those lines. I like the
lines so the grid helps to me to figure
| | 05:44 | out exact values. Then I've got the
Legend, which can be turned off and on.
| | 05:49 | Notice when it's turned off everything
adjusts itself to fill in the space. I'm
| | 05:53 | going to leave it on.
| | 05:55 | Over here I can scale text. If I
click on this, you'll notice no difference
| | 05:59 | really. The text is already scaled to
fit properly and make it easy for me to
| | 06:04 | read. I've got another button for
Automatic Layout and if I click on that I
| | 06:09 | might see some adjustments, but I
really haven't changed the layout from the
| | 06:13 | original. So clicking this button does
nothing in this case, but if you have
| | 06:17 | done some manual manipulations to the
layout, Automatic Layout may just clean
| | 06:22 | things up for you.
| | 06:23 | Let's go over here to Chart Type. I
might want to change the Chart Type. I'm
| | 06:27 | going to give it a click, you can see
3D Look, and column is selected right
| | 06:31 | here, our Normal column, so I want to
change maybe the shape of the columns,
| | 06:36 | maybe to Cylinders instead. I am
going to keep Normal selected with 3D and
| | 06:42 | click OK. You can see the end result
here, using the same colors and so on.
| | 06:48 | If I click on a data point, in this
case, Data Point 1, data series 1 or I go
| | 06:53 | to the orange column, all three get
selected. That means I can do some
| | 06:58 | formatting of these as well. So, for
example, if I right click on an orange
| | 07:02 | column I can go to the Object
Properties right here, but I have also got Chart
| | 07:06 | Type, Data Ranges, 3D View
options, all from this pop-up menu.
| | 07:11 | I want to go to Object Properties, want
you to see that the data series has its
| | 07:15 | own borders and its own area. Notice
Chart 2, there is that orange color being
| | 07:20 | used. There is Transparency options;
currently no transparency. Characters or
| | 07:26 | Fonts can be selected from here, Font
Effects, Data Labels, as well as there is
| | 07:31 | that Layout option where Cylinder is selected.
| | 07:34 | I am going to go back over here to
Area and change the color. Maybe I want
| | 07:40 | Chart 3 yellow. When I click OK, you
can see the end result using that gradient
| | 07:46 | effect to make it look 3-dimensional.
So it's very easy to change the colors,
| | 07:50 | the Legend is also updated automatically for me.
| | 07:53 | Now I can go to any of these areas,
each of them you can see has handles when I
| | 07:59 | click on it, so I can make changes to
these as well. For example, if I go to
| | 08:02 | Department, that's the X-axis title
and I want to change something like the
| | 08:07 | Font Properties or just delete it. I am
going to click Delete or hit Delete on
| | 08:11 | my keyboard to remove that. Of course,
I could get that back, I could use my
| | 08:15 | Undo button or I can go into my
Chart Properties as well if I wanted to.
| | 08:20 | Let's go over here to Revenues at the
top and let's just change the look of our
| | 08:24 | main title. If I right click on this I
can go to Object Properties, there is
| | 08:28 | Position and Size as well. Data Ranges,
Cut, Copy and Paste available here.
| | 08:32 | Object Properties is going to take me
in here to my Title dialog box, I'm going
| | 08:37 | to go over to Characters and I can
just make that a little bit bigger and
| | 08:42 | bolder and click OK. There
is the end result in my chart.
| | 08:46 | I'll click anywhere inside the chart
to deselect the selected object, in this
| | 08:51 | case, my main title to see the end
result. I can do that with my Y-axis if I
| | 08:57 | want to change the look of that I've
got my Title over here on the Y-axis. But
| | 09:02 | when you done, it's just a matter of
clicking anywhere in your spreadsheet
| | 09:04 | outside the chart to see the end result.
That looks a lot better in my opinion.
| | 09:10 | So have some fun experimenting with
some of your chart properties. Remember,
| | 09:14 | you can edit the chart once you are
inside, right clicking on objects allows
| | 09:18 | you to go and change those properties.
You've got formatting toolbars that are
| | 09:21 | constantly being updated depending on
what you have selected. A lot of options,
| | 09:25 | a lot of experimentation to you arrive
at the results you are looking for.
| | 09:29 | So I encourage you to experiment.
| | Collapse this transcript |
|
|
5. Reviewing Your WorkSplitting and freezing rows and columns| 00:00 | In this upcoming chapter we are going
to examine some tips as well as some
| | 00:04 | tools for reviewing your spreadsheets
here in OpenOffice Calc. We will look at
| | 00:09 | Auto Correct and the Spell Checker
but before we do that we are going to
| | 00:13 | examine a very common scenario when
working with spreadsheet data. You have got
| | 00:18 | more columns or more rows than will
fit on one screen and when you start to
| | 00:22 | scroll out to the right or down
towards the bottom of the spreadsheet, you
| | 00:25 | start to lose track of what you are looking at.
| | 00:28 | Here is an example, I have got a file
open called TravelExpenses1. It's an .ods file.
| | 00:33 | If you have got the exercise
files you can follow along by opening this
| | 00:37 | one up from the Lesson 5 folder. And
you can see down below that I go as far as
| | 00:42 | August 31st and then I can't see what's
down below. So I can use my scroll bar
| | 00:47 | to scroll down, there is September and
now all of a sudden I can't keep track
| | 00:52 | of what it is I am looking at, I have
lost my labels up at the top, so I might
| | 00:55 | need to scroll up to see, okay, so it's
Date then Dept, that's what that empty
| | 01:00 | column is and doing this back and
forth can be very frustrating and very time
| | 01:05 | consuming. Also you can see I am
missing part of a column over here on the
| | 01:10 | right, so I can use a scroll bar at
the bottom to scroll over, there is my
| | 01:14 | Totals but now I have lost my Dates. So
there are a couple of tools that we can
| | 01:19 | use to keep track of columns and rows.
| | 01:22 | We are going to start by talking
about Freezing. If I really look at the
| | 01:26 | spreadsheet, I want to be able to see
from row 9 up, no matter how far down I
| | 01:30 | go. So I am going to click anywhere
here in row ten. Now I also want to be able
| | 01:35 | to see these Dates, so as long as I
click after column B which I have, here in
| | 01:41 | C10 and freeze right in that spot, I
will always be able to see the data above
| | 01:46 | and to the left of my currently
selected cell. So go ahead and click on C10
| | 01:51 | before you go up to Window, Window menu,
you will see Split and Freeze and we
| | 01:57 | are going to start with Freeze. So we
will give it a click and we do see some
| | 02:00 | borders here showing up between column
B and C, you also see this black line
| | 02:05 | showing up between rows 9 and 10.
| | 02:08 | So if I click over here for example in
the Description and start to scroll down
| | 02:13 | using my down arrow, watch what
happens when I go past the perimeter at the
| | 02:18 | bottom, I start scrolling, but the
scrolling always allows me to see these
| | 02:22 | labels. So no matter how far down I go,
I always know that these are Dates,
| | 02:28 | Dept, Descriptions and so on. Now the
same thing is going to happen when I hit
| | 02:32 | my right arrow on the keyboard and
start scrolling over to the right, as I go
| | 02:36 | past the Total, you can see I have
actually got some numbers that I wasn't
| | 02:40 | seeing earlier, but I can still see
my Dates, notice it goes A, B and then
| | 02:44 | column E for me and as I keep
scrolling I will always see columns A and B. I
| | 02:51 | will always see rows 1 through 9 as well.
| | 02:54 | Ctrl+Home on the keyboard is the
fastest way back up to cell A1 in the
| | 02:59 | spreadsheet. Now that's a Toggle, so we
can turn that on and off, let's go back
| | 03:02 | up to Window, we will go down
to Freeze and turn that off.
| | 03:06 | Now we are going to look at something
different, splitting. You can split your
| | 03:10 | spreadsheet in two sections
allowing you view different sections
| | 03:14 | simultaneously. So it's a little bit
different from freezing but if I click
| | 03:18 | here and let's say anywhere in row 10,
I am then going back to C10 again and
| | 03:22 | this time go up to Window and select
Split and when I click on Split, you can
| | 03:26 | see I get darker lines than I saw with
Freezing but it also means that I can
| | 03:31 | click in different sections here and be
able to scroll through this section, so
| | 03:35 | if I start hitting my down arrow, you
can see I am actually seeing two sections
| | 03:40 | of my spreadsheet at the same time.
| | 03:42 | Here I am going to scroll down to the
very bottom and now I am looking at rows
| | 03:47 | 9 through 17 as well as 19 through 40.
Same thing as I scroll over to the
| | 03:53 | right, I can click over here, scroll
over to the right to see data on the left
| | 03:58 | side of my screen as well as over
here on the right side of my screen.
| | 04:03 | So typically what people will do -- I
am going to back up to Window here and
| | 04:07 | turn Splitting off by clicking it again,
Ctrl+Home back to the top. Typically
| | 04:12 | what people will do is split either
horizontally or vertically, not usually
| | 04:16 | both at the same time like we just did.
So for example if I click here in A10
| | 04:22 | and do my split from there, Window,
then Split, I am in effect splitting it in
| | 04:28 | half, horizontally I have got a top
half and a bottom half and now I can scroll
| | 04:32 | through the different sections here
being able to see two different areas of my
| | 04:37 | spreadsheet at the same time.
| | 04:39 | As I move to the right notice
everything moves over with it. So I like
| | 04:43 | splitting either horizontally or let's
go back up to Window here and we will
| | 04:47 | choose Split again to turn that off and
let's say I want to click anywhere here
| | 04:53 | in column C, I am going to go right up
to the top, in cell C1 and do a Window
| | 04:59 | Split, this time I have split it into
two parts but I split it over here on the
| | 05:04 | left and on the right. So that means I
can come over here on the right, scroll
| | 05:09 | over, I am still seeing column A and
B but I can click in that section and
| | 05:14 | scroll as well, kind of cool. Let's
go back up to Window and turn splitting off.
| | 05:22 | So just a couple of tools to help
you stay organized. If you need to see
| | 05:26 | different areas of your spreadsheet
simultaneously splitting is excellent but
| | 05:30 | if you just want to be able to freeze
on those labels that we see either going
| | 05:34 | across our spreadsheet or down aside
like the left side of our screen then in
| | 05:40 | that case freezing is your best option.
So experiment with those two, as you
| | 05:44 | review your larger spreadsheets
containing more data than will fit on one screen.
| | Collapse this transcript |
| Auto-correct options| 00:00 | I have a question for you. Nave you
ever been entering data in any application,
| | 00:04 | not just OpenOffice Calc, and you know
you have made a typo, you go back to fix
| | 00:09 | and it's already been fixed for you?
Automatically corrected, let's say.
| | 00:13 | Well it's a feature commonly known as Auto
Correct and it's available to you here in
| | 00:18 | OpenOffice Calc and that's what we
are going to explore in this lesson.
| | 00:22 | I have already opened up the file
from the previous lesson here called
| | 00:25 | TravelExpenses1.ods, it's in the
Lesson 5 folder. If you have been following
| | 00:30 | along, we can use this file but any
file will do. We are just going to do a
| | 00:33 | little bit of experimenting here. For
example, I am going to click in cell F1
| | 00:38 | right here and I am going to type in a
word and misspell it on purpose, so I am
| | 00:43 | going to type in recieve, I am going
to ignore that i before e except after c
| | 00:51 | rule and when I hit my spacebar to
continue typing, watch what happens to the
| | 00:55 | word recieve. It gets fixed,
automatically corrected for me and that's because
| | 01:01 | this is one of the examples found in
the library. Let's Backspace to take that out.
| | 01:08 | Let's try another one. I am going to
do an opening round bracket, a capital
| | 01:11 | letter C and a closing round bracket,
quite often you will see people use this
| | 01:16 | set of three characters for copyright.
When I hit my spacebar, check it out,
| | 01:20 | the copyright symbol replaces those
three characters automatically and this is
| | 01:25 | the symbol I can't even find on my
keyboard, so very useful. I am going to take
| | 01:29 | that out with my Backspace key. And now
wouldn't it be nice if we could create
| | 01:34 | our own little shortcuts. For example,
DK Consulting Inc. maybe I type that
| | 01:38 | over and over, day after day, wouldn't
it be nice to be able to type in dkci,
| | 01:44 | hit the spacebar and automatically
see DK Consulting Inc. Well that didn't
| | 01:49 | happen but I can make it happen.
| | 01:51 | We are going to explore the Auto
Correct options now. Let's first take out what
| | 01:55 | we typed here. Next we go up to two
Tools and down to Auto Correct right there.
| | 02:00 | Here you will see a dialog box with
four tabs; there is that library of
| | 02:05 | replacements the Replace tab. We
have got Exceptions. Exceptions has two
| | 02:11 | sections, Abbreviations as well as
words you might want to type with two
| | 02:14 | initial capitals. We have also got the
Options that can be turned on, or off
| | 02:19 | for Auto Correct and Custom Quotes.
We are going to look at all of these
| | 02:23 | starting with the Replace Library here.
| | 02:25 | So up here you will see an alphabetical
list starting with some symbols, there
| | 02:28 | is our Copyright symbol, it's right
there, automatically turned on for us here
| | 02:32 | in Auto Correct ready to use. We have
got some arrows and then we get into that
| | 02:36 | alphabetical listing of
commonly misspelled words or typos.
| | 02:41 | So as I scroll down, I see way down
here in the Rs, the one that I just tried a
| | 02:47 | moment ago, receive, right there. As
well as receive spelled another way and
| | 02:53 | received and receiving. If there is
anything in here you don't like, the new
| | 02:57 | thing is you can click on it and hit
Delete. I am not going to do that, what I
| | 03:02 | would rather do is add some new ones.
Notice the New button available to me up
| | 03:06 | here. Well I can't click on it yet but
as soon as I enter a replacement, I can.
| | 03:11 | So I am going to scroll all the way to
the top in the Replace field up here, I
| | 03:15 | am going to click and drag over the
word the word that appears there now, it
| | 03:18 | doesn't matters what it is, just click
and drag over it and I am going to type
| | 03:21 | in this dkci. Now that does not exist
on this alphabetical list, what am I
| | 03:27 | going to replace that with? Well I
click over whatever is in the With field,
| | 03:31 | click and drag right over it so it's
highlighted, now I can type over it, I am
| | 03:35 | going to type in DK Consulting Inc.
| | 03:39 | I used to work for The Children's
Hospital of Eastern Ontario CHEO, when I type
| | 03:46 | that in the word would be replaced with
Children's Hospital of Eastern Ontario,
| | 03:49 | saved me a lot of time and a lot of
typing. So with that in there, all I have
| | 03:54 | to do to edit or create a new item on
the list is click the New button and when
| | 03:59 | I do that, it's thrown into the mix
in the alphabetical listing here right
| | 04:03 | before the word Documents.
| | 04:05 | Now before we test that out, let's go
up to Exceptions. By default you will see
| | 04:10 | in a moment in the Options tab here,
that whenever we hit the period,
| | 04:15 | automatically OpenOffice Calc, the
Auto Correct feature is going to try to
| | 04:20 | capitalize the next word assuming we
just hit the end of a sentence, but if we
| | 04:24 | use abbreviations, that's not
necessarily true. So there is a list of
| | 04:28 | abbreviations here. You can see there
is quite a long list. So if there is an
| | 04:35 | abbreviations you use that is not on
the list, you can add it right here. Let's
| | 04:39 | say, I wanted to use my initials. Well
let's say instead of Dr, I am going to
| | 04:45 | put in kr. Dr is short for Doctor and
it's already on the list, so I am using
| | 04:51 | kr. when I click New, I will be able
to type in kr. with the period knowing
| | 04:56 | that the next word will not
automatically be capitalized for me by Auto Correct.
| | 05:01 | Same thing goes for two initial
capitals, Auto Correct is very good at
| | 05:04 | recognizing, when you hold down the
Shift key a little bit too long and the
| | 05:07 | first two characters get capitalized,
when only the first one should be, well
| | 05:11 | it's automatically going to take that
second character and move it into a lower
| | 05:15 | case format except for these; CDs, GHz,
ICs, LPs, there is MHz down there as
| | 05:22 | well and you can see there is a fairly
good list. So you can add your own in
| | 05:27 | there if you wanted to just by clicking
in the field and hitting the New button.
| | 05:30 | Let's go over to those options, here
is where you will see, Use replacement
| | 05:35 | table, so everything we saw on the
replacement table is automatically corrected
| | 05:39 | unless we de-select that check box. I
am going to leave it on. Two initial
| | 05:44 | Capitals, we just talked about that
because it is turned on here, it is
| | 05:47 | automatically fixed, same thing for
first letter of every sentence. If I just
| | 05:51 | turn this of, all of those
exceptions don't matter anymore; it's not
| | 05:55 | automatically going to
capitalize the next letter after a period.
| | 05:59 | Here are some shortcuts for Bolding
and Underlining, I am going to turn that
| | 06:03 | one off too because I don't need to use
stars on either side of a word to bold,
| | 06:07 | I have got my Formatting toolbar, same
thing for Underlining. URLs will not be
| | 06:12 | recognized as mistakes, so there is
URL recognition built in here, there is
| | 06:16 | also a couple other replacements, 1st
and 1/2, if I type 1/2, I will see the
| | 06:21 | 1/2 symbol, if I type in 1st, I will
see 1st with the st in a subscript format,
| | 06:27 | we will try that out in a minute, dashes,
double spaces can also be customized.
| | 06:33 | Custom Quotes is another option. If I
want to replace those quotes whether they
| | 06:37 | are single or double quotes to Smart
Quotes, you will often hear that term,
| | 06:43 | that means that they are curved in
different directions on either side of a
| | 06:46 | word. So for example, the Start quote,
the Default here, if a click on it and
| | 06:50 | you can see it's rounded, almost
looks like 66. I will click OK and the End
| | 06:56 | quote is the opposite, looks like 99,
I'll click OK. I am going to turn both
| | 07:02 | of those on so we can test it, click
both those check boxes, now I am going to
| | 07:06 | click OK.
| | 07:07 | Let us do a little more testing now.
Let's start with 1/2 and leave a space,
| | 07:13 | there is the 1/2 symbol automatically
corrected for me. I am going to click in
| | 07:18 | cell F2, I am going to type in the
word excellent in double quotes, so Shift
| | 07:23 | and that quote, there is my rounded
or smart quote. I will type in the word
| | 07:28 | excellent and a closing, it's rounded
the other way. So that one worked as
| | 07:33 | well. And of course the one that's
really important to me, dkci, when I hit my
| | 07:39 | spacebar, DK Consulting Inc, it's using
the formatting of that cell that's why
| | 07:44 | it appears that way. I never have
to type DK Consulting Inc again.
| | 07:48 | Now if you don't like Auto Correct of
course the only way to turn it of is
| | 07:52 | de-select all of those check boxes we
saw under the Options tab, otherwise Auto
| | 07:56 | Correct automatically in there fixing
typos and replacing things on the fly,
| | 08:02 | not even asking you along the way
that's why it's called Auto Correct.
| | Collapse this transcript |
| Checking spelling| 00:00 | One very important step in the
review process for any kind of document
| | 00:04 | including a spreadsheet is to check
spelling. So in this lesson we are going to
| | 00:07 | explore the built in Spell Checker.
Now unless OpenOffice Calc is the very
| | 00:12 | first software application you have
ever used, you are probably familiar with
| | 00:16 | spell checking tools.
| | 00:18 | Well, the built-in Spell Checker here in
Calc and the other applications in the
| | 00:22 | suite is very similar to any other
Spell Checker out there, so you will have no
| | 00:27 | problem learning it if you have
used any of those other applications.
| | 00:30 | We are going to use this file that we
have been working with throughout this
| | 00:33 | chapter; it's called TravelExpenses1.ods.
Of you don't already have it open in
| | 00:38 | front of you, you will find it in the
Lesson 5 folder of the exercise files if
| | 00:42 | you have got them. You may have
noticed that there are a number of words that
| | 00:46 | have a red squiggly line under them.
Here I see one in cell D13. Then I go to
| | 00:52 | column F and I see the same word
repeated many, many times with that red line
| | 00:57 | under it. Here is another one over
here in H3. Well that's because another
| | 01:03 | feature, that's kind of part of spell
checker is turned on by default. If I go
| | 01:07 | up to my Standard tool bar, I see the
Spell Check button, but right next to it
| | 01:11 | is a button that appears to be
pushed in, it's called Auto Spell Check.
| | 01:16 | So if you want to be able to see those
errors highlighted for you right on the
| | 01:19 | spreadsheet, they don't print that way,
but here on your screen you will be
| | 01:23 | able to see then easily if this button
is pushed in. If you don't like that,
| | 01:26 | it's distracting, just click the
button to turn it off. With Auto Spell Check
| | 01:30 | turned off, I don't see the spelling
errors, they are still there, I just don't
| | 01:34 | see them. I am going to turn this back
on because with it turned on and those
| | 01:40 | red squiggly lines come back, I can
actually check spelling on the fly without
| | 01:44 | opening up the Spell Check application.
| | 01:47 | For example, if I come over here to
cell D13 and I right click on the word
| | 01:52 | Dinnner, right clicking means using
the other mouse button, if you have only
| | 01:56 | got one button on a Mac, then it's
hold down Ctrl and Click, you get this
| | 02:00 | little pop-up menu, which because I
have got a spelling error and the Auto
| | 02:04 | Spell Check turned on, I get a
suggested replacement, in this case, it is the
| | 02:09 | correct spelling of Dinner. I can
access spell checking right from here, I can
| | 02:14 | add this word to one of four
different dictionaries, I could also choose to
| | 02:19 | ignore it, all of the time in this
entire spreadsheet, this one time that I
| | 02:24 | check spelling. I can also add it Auto
Correct. So it will automatically get
| | 02:29 | corrected next time I type Dinner with three Ns.
| | 02:32 | Well I am just going to select the
suggested replacement here at the top which
| | 02:35 | is Dinner with one less N, and you can
see it's fixed up, the red line is gone
| | 02:41 | and I can move on to the next. Now I
might want to go up here to Manotick or I
| | 02:45 | might want to come over here to the
word Kokanee, but look at this, it's
| | 02:48 | repeated so many times. So probably
the best thing to do is to run our Spell
| | 02:52 | Checker now.
| | 02:53 | So I am going in cell A1 because by
default the Spell Checker starts at the top
| | 02:58 | and works it's way down and I am going
to click on Spell Check. You could also
| | 03:02 | go up to Tools and choose Spellcheck
at the top of this menu or if you prefer
| | 03:07 | using the keyboard F7, your choice, but
as soon as you launch the Spellchecker,
| | 03:11 | this should look familiar if you have
used other spell checking programs. Here
| | 03:16 | you can see I have got a word that is
highlighted, it's the word Kokanee. I
| | 03:19 | know it appears it many, many times
here. So let's check at the options.
| | 03:23 | This is not actually a spelling
error nor a typo. So in this case, the
| | 03:28 | suggestions down below don't apply. On
the right side though, I can choose to
| | 03:32 | ignore this occurrence of Kokanee once,
meaning I have to click this button a
| | 03:37 | whole bunch of times to ignore every
other one or if I choose Ignore All, it
| | 03:41 | won't stop at the word Kokanee again
in the spreadsheet during this routine,
| | 03:46 | next time I run the Spell Checker it will.
| | 03:48 | Even better might be to add this, this
is a proper name, if I go down to the
| | 03:53 | Add button, it's the same as right
clicking like we saw earlier and choosing
| | 03:57 | Add, there is the dictionaries, I
can add to the standard, the soffice
| | 04:01 | dictionary containing office terms,
there is even a sun dictionary with sun
| | 04:05 | vocabulary in there, I am going to
choose the standard dictionary, that's where
| | 04:08 | it belongs and when I add it to the
dictionary, it automatically skips over
| | 04:13 | Kokanee and goes to the next word not
recognized in the dictionary, it happens
| | 04:17 | to be another proper name Manotick.
| | 04:19 | Now for this one, I am actually going
to choose to ignore it, I am going to
| | 04:23 | choose Ignore Once, doesn't appears to
be any other spelling errors, it's been
| | 04:29 | completed. When I click OK, watch
what happens, Spell Checker is closed up,
| | 04:34 | watch the red squiggly line here under
the word Kokanee, we added this to the
| | 04:38 | dictionary, so that means those
lines should disappear. However the word
| | 04:44 | Manotick up here, I am going to click
in an empty cell, will always have it's
| | 04:49 | red squiggly line until I add it to
the dictionary, even though I chose to
| | 04:52 | ignore it, it's still a typo in the
mind of the Spell Checker. So the next time
| | 04:57 | I run the Spell Checker, it will stop
at this word, we just ignored it for that round.
| | 05:02 | So that's our Spell Checker. Now when
you add words to the dictionary, I am
| | 05:06 | going to go over here and right click
on the word Manotick this time, I am
| | 05:10 | going to come down to Add and I am
going that to the standard dictionary as
| | 05:15 | well. So the red squiggly line is gone,
just click in an empty cell to see the
| | 05:20 | end result. What if you want to take a
word out of the dictionary, you should
| | 05:24 | not have added it, maybe it was
spelled incorrectly even though you want the
| | 05:28 | word Manotick in there it was spelled
incorrectly here, how do you get it out
| | 05:32 | of the dictionary.
| | 05:33 | Well in that case, you go up to Tools,
and down to Options and here under
| | 05:40 | OpenOffice.org, you will see Load/
Save, then Language Settings and under
| | 05:44 | Language Settings is Writing Aids.
This is not specific to Calc; this is the
| | 05:50 | same set of dictionaries that are used
by all of the applications in the suite.
| | 05:54 | So same thing goes for Base, you
could see down here, got separate ones for
| | 05:58 | Calc and Base if we wanted to. These
are options for all of the applications in
| | 06:03 | the suite. So with Writing Aid
selected over here, you can see Language
| | 06:07 | Modules, user defined dictionaries,
here's standard, here's the four we saw,
| | 06:11 | the office, the sun, there is also
an Ignore All list and if I go to the
| | 06:15 | standard one, make sure it's selected
and go over to the right and click Edit,
| | 06:20 | you will notice the two words Kokanee
and Manotick in there. I am going to hit
| | 06:24 | the Delete button twice to remove both
of those and I am going to click Close,
| | 06:29 | click OK down at the bottom here of my
Options dialog, all those red squiggly
| | 06:34 | lines are back.
| | 06:35 | So that's the spell checking
functionality here in OpenOffice Calc, if you have
| | 06:40 | used other software applications that
have spell checking functionality built
| | 06:43 | in, it should look very similar,
very familiar and very easy to use.
| | Collapse this transcript |
|
|
6. Sharing Your SpreadsheetsPrinting a sheet| 00:00 | As you continue to work with
OpenOffice Calc, creating your own spreadsheets
| | 00:04 | over time, the need may arise to
share your work with others. So this last
| | 00:08 | chapter is dedicated to different
methods for sharing your spreadsheets with
| | 00:12 | other people. The old fashion way will
be covered in this lesson and that is to
| | 00:16 | print them out.
| | 00:17 | When you are working with multiple
sheets, there are a number of print options
| | 00:20 | to consider. So we are going to use
this spreadsheet that I have already opened
| | 00:24 | up. It's in the Lesson 6 folder of
your exercise files called RevenueMaster;
| | 00:28 | it's a .ods file.
| | 00:29 | So if you want to follow along and
you've got those exercise files, open this
| | 00:33 | one up and you can see there are
several tabs down here and the values and data
| | 00:37 | in those tabs feed our total sheet
here at the front. Let's say we are going
| | 00:42 | into a meeting with 10 people and we
want to print out our work to share with
| | 00:46 | others during that meeting.
| | 00:48 | Well, the first thing we need to do is
probably preview this, to see what it's
| | 00:52 | going to look like when it prints. As
you are working on your spreadsheets here
| | 00:56 | in our Default view, you don't really
have a good sense of what it is going to
| | 00:59 | look like on paper.
| | 01:01 | So I always like to go up to the File
menu and come down to Page Preview and
| | 01:06 | you will see it's right about Print
and Printer Settings. So let's click Page
| | 01:11 | Preview. You can see, by default,
here I am working with what looks like an
| | 01:15 | 8.50 x 11 sheet of paper, and it's
Portrait. So currently on this sheet, page
| | 01:21 | 1, everything seems to fit nicely. As I
hit my Page Down key, you can see now I
| | 01:25 | am on page 2 of 7. Now I know I've
only got four tabs. There are only four
| | 01:30 | sheets in this spreadsheet.
| | 01:33 | So why we have got 7 Pages is, if we
check it out page 3, when I hit Page Down,
| | 01:38 | is the rest of my Customer Service
sheet. I hit Page Down and I am under
| | 01:42 | Training and Training doesn't quite fit.
So as I hit Page Down on my keyboard
| | 01:46 | there is the rest of it and so on.
| | 01:49 | So the first thing I am going to do is
either close the preview and format this
| | 01:53 | spreadsheet to maybe be Landscape
where it will fit or I can go right over to
| | 01:57 | Format Page. We have seen
this before in a previous lesson.
| | 02:00 | When I do that I can go to the Page tab
now and I am going to leave it at 8.50
| | 02:05 | x 11, because that's the size of the
sheet of paper going through my printer,
| | 02:09 | but I am going to turn it on its side
by choosing Landscape. When I click OK,
| | 02:14 | let's see what happens down here.
| | 02:16 | Right now I am on page 6 of 7, looks
like this is Consulting. If I hit Page
| | 02:20 | Down it doesn't quite fit. So I have
got more of it showing up on a single
| | 02:24 | page, but I have still got 7 pages.
So I am going to go back to Format Page
| | 02:29 | now, and I might want to play around
with the Margins. For example, you can see
| | 02:33 | here the Left, Right, Top and
Bottom margins are set the same.
| | 02:37 | So if I wanted to, I could click and
drag over the Left Margin, I am going to
| | 02:41 | change it to 0.25, quarter of an inch.
I hit my Tab key, because that takes me
| | 02:46 | to the Right Margin field where I can
just type over what's already selected
| | 02:50 | for me, 0.25. I will hit Tab. I will
leave the Top and Bottom as it is and when
| | 02:54 | I click OK, let's see if that helps.
| | 02:56 | The Margin settings are out of the
print range, now depending on your printer
| | 02:59 | you may see this, you may not see
this. Do you still want to apply these
| | 03:02 | settings? I am going to say Yes.
You can see now I am at page 4 of 4,
| | 03:07 | everything seems to be fitting.
| | 03:09 | Now there are other formatting changes
we can make, for example, to the Font
| | 03:12 | Size that's been used. As I hit my Page
Up key, you can see all of these sheets
| | 03:18 | have all of their totals, all of
their figures fittings onto a single page
| | 03:21 | including our Totals here, which
is now turned on its side as well.
| | 03:25 | So I am going to close my preview.
Now I could go up to the File menu and
| | 03:29 | choose Print during this Page
Preview that I am in. I am going to Close
| | 03:33 | Preview. Now I am going to go up to
File and choose Print. I also want to show
| | 03:37 | you that we do have a Print button
right here and this is a quick print.
| | 03:42 | So if you know everything is okay and
you want to print out a copy, just click
| | 03:45 | this button and off it goes. You don't
get to choose any of the options. That's
| | 03:49 | why I like to go up to File and choose
Print or from the keyboard do a Ctrl+P
| | 03:52 | on my PC, Command+P on the Mac keyboard.
| | 03:56 | This opens up the Print dialog box
where I can choose my printer. If I have got
| | 04:00 | more than one selected, I click the
dropdown and choose the Printer of my
| | 04:04 | choice. Mine is the HP deskjet, so
that's the one I am going to leave selected.
| | 04:08 | Down below you can see I can print
All sheets, Selected sheets, or Selected
| | 04:14 | cells if I clicked and dragged over
certain cells. I want all the sheets printed.
| | 04:19 | This makes it in effect a four-page
document, which means down below the print
| | 04:24 | range where it says All pages, you can
see 1-4 shows up, it's faded here. If I
| | 04:28 | want to select specific pages, I could
do that. But with all pages selected, if
| | 04:33 | I want multiple copies, it's
important that Collate be selected.
| | 04:37 | Let's say there are five people in the
meeting, I am going to bump this up. I
| | 04:40 | could type in 5 if I wanted to. I
like the Up arrow. I want to make sure
| | 04:44 | Collate is turned on with 5 copies of
this 4-pager. I am going to get pages
| | 04:49 | 1-4, then pages 1-4
again and so on for 5 Copies.
| | 04:54 | If I didn't select Collate, you can
see I would actually get 5 page 1s and 5
| | 04:59 | page 2s and I would have to do the
collating myself. So that's a nice feature
| | 05:03 | to leave turned on. All I have to
do now to print this out is click OK.
| | 05:08 | Clicking OK sends it off to my printer.
You can see it's actually being sent to
| | 05:12 | the printer and I can
continue working, while it prints.
| | 05:15 | So that's just one way to share your
work with others. The old-fashioned way;
| | 05:19 | put it out on paper using the print
options that we have covered here. Next we
| | 05:23 | are going to move on to
some more electronic methods.
| | Collapse this transcript |
| Exporting to other formats| 00:00 | If you plan to share your spreadsheets
in an electronic format with others and
| | 00:05 | you created your spreadsheet here in
OpenOffice Calc using the default format,
| | 00:08 | which is an .ODS file, OpenDocument
file format, you may want to consider
| | 00:13 | exporting to other formats before
you hand off that electronic copy. Not
| | 00:19 | everybody is using the OpenOffice suite
of applications, at least not yet. So I
| | 00:23 | am going to show you in this lesson how
to export to formats that other people
| | 00:27 | may be using, including
formats that anyone can open.
| | 00:30 | So first thing you will need to do if
you have got the exercise files and you
| | 00:33 | wish to follow along, is to open this
one up from the Lesson 6 folder, it's
| | 00:36 | RevenueMaster.ods, we are going to go
up to the File menu once you have got
| | 00:41 | that open and we are going to go down
to first Save As. Save As has a keyboard
| | 00:47 | shortcut Ctrl+Shift and the letter S as
in Save. Command+Shift+S if you are on
| | 00:51 | the Mac keyboard. We will give it a
click. I just want you to see that I have
| | 00:55 | got the name RevenueMaster showing up
here. That's the file name and the Save
| | 01:00 | as type field is showing that default
format .ODS. So if I click the dropdown,
| | 01:05 | I have got some other formats I can save to.
| | 01:07 | Let's say I am going to handing this
off to someone who has Microsoft Excel.
| | 01:11 | Well in that case I might want to
choose one of the Microsoft Excel formats
| | 01:15 | depending on their version. The latest
that I can choose from here is Microsoft
| | 01:19 | Excel 97/2000/XP and that will create
an .XLS extension which can be opened by
| | 01:26 | the latest format as well. Notice I
have got dBASE in here, there are some
| | 01:30 | other OpenDocument options for
templates, StarCalc, I have got Text there it
| | 01:37 | is, CSV. Choosing this format means
no matter what the person is using for
| | 01:41 | spreadsheet application, they should be
able to open it up. What it creates is
| | 01:45 | a Comma Separated Value version meaning
it's almost like creating a plain text
| | 01:50 | version of a document. They will be
able to open that up and save it to
| | 01:53 | whatever format they want. I also
have some HTML options here as well.
| | 01:58 | I am going to click Cancel though. And
when I click Cancel, I am going to go
| | 02:02 | back up to the File menu here and down
to Export. Now you will notice that I
| | 02:07 | have got Export, which has ellipsis
after it. It's going to open up in Export
| | 02:11 | Dialog box. I have also got Export as
PDF. If that's the option, I want to go
| | 02:14 | for. Portable Document Format is going
to create a Read only version of my work
| | 02:19 | so if I don't want people going in and
editing the figures, I might want to in
| | 02:23 | effect, take a picture if my Spreadsheet,
saving it to PDF means I will be able
| | 02:28 | to open it up in Adobe reader, for
example, free application they should have
| | 02:32 | on your PC. If I am showing it to
someone who is using a Mac, they will be
| | 02:36 | using the Preview
application on the Mac to view the PDF.
| | 02:39 | So let's start with the Export option
right here first. Down below you can see
| | 02:44 | that the default file format is XHTML
and if I click this dropdown, I have got
| | 02:50 | PDF there as well. So really the
Export option is XHTML, it's a XML type
| | 02:57 | version, a HTML that people can view in
their web browser. I am going to click
| | 03:01 | Cancel and go back up to File and down
to Export as PDF and give that a click.
| | 03:08 | This takes me to the PDF option, so I
am not just selecting a PDF extension,
| | 03:12 | but here I get to choose the range,
All is the default, but I can select
| | 03:17 | certain pages if I wanted to or if I
clicked and dragged over an area, I can
| | 03:20 | choose Selection here which I haven't
done, so it's not available to me. I am
| | 03:24 | going to leave it at All. I want all
four of my Spreadsheets or my sheets in my
| | 03:28 | Spreadsheet to be selected.
| | 03:29 | What about the images? I do have a
chart in here. There's different compression
| | 03:33 | option. So if I choose Lossless for
example, or JPEG compression, I am going to
| | 03:38 | create different file sizes. So if
quality is really important to you, you can
| | 03:42 | choose a quality percentage. If I want
it to be 100%, I can use my Up arrow or
| | 03:47 | just click and drag over this and
choose 100. I could reduce image resolution
| | 03:52 | if I wanted to save some file space and
then choose the Dots Per Inch. I can go
| | 03:56 | as low as 75 or I can go as high as 1,
200 if image resolution is important to
| | 04:02 | me. I am going to leave it at 300. And
then down below under General, I have
| | 04:06 | got some other PDF options. You can see
PDF/A-1, a different format of PDF that
| | 04:12 | maybe useful, Tagged PDF, if you have
got tags in your document. I don't in my
| | 04:16 | spreadsheet, that's not important to me.
I can create a PDF Form from here even
| | 04:21 | if I wanted to where people could fill
out different fields, I am not going to
| | 04:24 | do that either. It's just
Spreadsheet data in this case.
| | 04:27 | I can export bookmarks if I have got
them and notes if I have created them,
| | 04:31 | which I have not. I am going to
deselect bookmarks, I don't have any and all I
| | 04:34 | have to do now is click Export to
export to the PDF format. But notice that I
| | 04:39 | have got some other tabs up here.
Initial View, Page only. I can do Bookmarks
| | 04:44 | and pages, Thumbnails and pages. I
like Thumbnails and pages. That way people
| | 04:48 | can jump to a specific page by a
clicking on a Thumbnail and I want the first
| | 04:52 | page to be the one that's visible as I
open this up. So Page 1 is the default.
| | 04:58 | Magnification I am going to leave as
default, but I can make it Fit in a
| | 05:01 | window, Fit in a width, visible, I
could choose a Zoom factor if I wanted to as
| | 05:05 | well. Page layout use as a Default.
If I want it all on a Single page or
| | 05:10 | Continuous like one long page,
Continuous Spacing. These are all Page layout
| | 05:14 | options if I plan on especially
printing this out if I needed to. User
| | 05:19 | Interface has some options. I have
got links. Now I haven't created any
| | 05:23 | bookmarks or links in this spreadsheet
so it doesn't really apply. Just so you
| | 05:26 | know though, you have got many, many
options when exporting to PDF including
| | 05:31 | Password protecting. So you can
encrypt the PDF document if you needed to and
| | 05:35 | then set the open password and you can
also set permission password. So if you
| | 05:40 | want people to be able to use an
application like Adobe Acrobat to make changes
| | 05:44 | to it, you could set permissions.
I am not going to do any of that.
| | 05:47 | I am just ready to export this. When I
choose Export, I just have to give it a
| | 05:52 | name, notice the file format. It's
selected for me, if I click the dropdown,
| | 05:56 | it's the only option and the file name,
all I have to do is select a file name.
| | 06:00 | I am going to call this Revenue2008_07
and I am going to save it to my Desktop
| | 06:11 | just like that. When I click Save,
it's done. I have exported it. Now I am
| | 06:17 | still working on my ODS file but now
I have got an electronic copy on my
| | 06:21 | desktop that I can send off to people.
I can save it to a Network drive. I can
| | 06:26 | email it as an attachment. Just so
happens that in the next lesson we are going
| | 06:30 | to talk about emailing your
spreadsheets off to people, but not before we talk
| | 06:35 | about password protecting them.
That's all coming up next.
| | Collapse this transcript |
| Protecting and sending a spreadsheet| 00:00 | Probably the most popular method for
sharing files with others these days is to
| | 00:05 | send them via email and that includes
your spreadsheet files created here in
| | 00:09 | OpenOffice Calc. A lot of people will
launch their email application, create
| | 00:13 | the message and use the Attach
feature to browse to the file they want to
| | 00:18 | attach, that could be time consuming
if they don't remember exactly where it
| | 00:21 | was stored, and then send it off.
| | 00:24 | Well OpenOffice Calc has made it very
easy for you. You can send emails using
| | 00:28 | your file as an attachment by default
right from within OpenOffice Calc and in
| | 00:33 | fact, if you need to convert the
file on the fly, you can do that too.
| | 00:37 | So that's what we are going to do in this
lesson using the same file we have been
| | 00:40 | working with throughout this chapter,
it's called RevenueMaster.ods, if you
| | 00:44 | have got the exercise files and you
want to follow along with this one, you can
| | 00:48 | find that in the Lesson 6 folder, but
really any file will do at this point.
| | 00:53 | So having anything open, we are going to
send this off via email. One thing that
| | 00:58 | worries me about sending electronic
copies of a file via email is it could fall
| | 01:03 | into the wrong hand. So just before
we send this off, we are going to talk
| | 01:06 | about protecting it. Creating
password protection for an entire document or
| | 01:11 | selected sheets is an option
you have here in OpenOffice Calc.
| | 01:15 | So here I am on Sheet 1, which is
called Totals. When I go up to my Tools
| | 01:19 | menu, you will notice that Protect
Document appears about half way down.
| | 01:24 | There's a couple of options here. I
can password protect just this Sheet
| | 01:28 | meaning whoever receives this email
attachment will be able to open it up and
| | 01:33 | if they want to view the contents of my
Total sheet, they will need to know the
| | 01:36 | password I set. Or I can set it up for
the entire document, all four sheets. So
| | 01:41 | I am going to choose Document. Either
way, I am going to see a Protect Document
| | 01:45 | dialog box with two fields where I
enter the password, I won't be able to see
| | 01:49 | what I am typing so I need to confirm
that by typing it in exactly the same
| | 01:53 | again in the Confirm field before I
click OK. So I am going to type one in
| | 01:57 | here. And I am going to click in the
Confirm field and type exactly that again.
| | 02:05 | When I click OK, if I have typed those
both in identically, it works, I don't
| | 02:10 | see an error message. I have now
created a password for my entire document. Of
| | 02:14 | course, I am going to need to save that.
So I am going to go up to File and I
| | 02:19 | am going to do Save As. This way I can
save it to a different location, I am
| | 02:23 | going to go to my desktop, I am going
to keep the same name, but when I click
| | 02:26 | the Save, it's going to be saved those
passwords. Now it still opens. So I am
| | 02:31 | ready to send this off now.
| | 02:32 | I go up to the File menu and down to
Send. Now watch this, over here on the
| | 02:39 | right, I can send this as a document
attached to an email message, it's going
| | 02:44 | to launch my default email application
and all I have to do is fill in who it
| | 02:49 | is going to a subject and a short
message if I so choose. But look at this. I
| | 02:53 | can also email it as an OpenDocument
Spreadsheet. So the default format here in
| | 02:58 | Calc. I could email it as a Microsoft
Excel spreadsheet or even as a PDF. The
| | 03:04 | conversion happens on the fly and the
appropriate format is attached to the
| | 03:08 | email in my default email application.
So let's choose a common format, E-mail
| | 03:13 | as Microsoft Excel, I am going to
give that a click. So it takes a second,
| | 03:17 | check it out. Here's my default email
application, which happens to be Outlook.
| | 03:21 | There's a file attached it's called
RevenueMaster.xls. It's an Excel file. I
| | 03:28 | can even see the size of it.
| | 03:29 | All I have to do is choose who I am
sending it now. I am going to send it to
| | 03:35 | drivers@lynda.com. If I wanted to Cc
anybody I could, type in a Subject, This
| | 03:45 | year's revenues and I can enter a
short message down below. I am going o type
| | 03:56 | in Password protected. Please
contact me directly for the password. DR.
| | 04:09 | So it's already attached, I don't
have to go browsing for it, that's really
| | 04:12 | nice, all I have to do is hit the Send
button and off it goes to the recipient.
| | 04:17 | If they have got Microsoft Excel,
they will have no problem opening up the
| | 04:20 | attachment. They will contact me for
the password and they will have full
| | 04:24 | access to the spreadsheet. That's all
there is to it. So now you know how to
| | 04:28 | send off your OpenOffice Calc
spreadsheets in any format directly from within
| | 04:34 | Calc and if you need to,
you can password protect them.
| | Collapse this transcript |
|
|
ConclusionGoodbye| 00:00 | Hey congratulations, you made it to the
end. You should now be feeling like you
| | 00:04 | have a pretty good handle on the core
features of Calc, the free spreadsheet
| | 00:08 | application that's included in the
OpenOffice.org suite of products. This is
| | 00:13 | David Rivers saying thanks for
watching and I hope to see you again soon in
| | 00:17 | another lynda.com title.
| | Collapse this transcript |
|
|
