We listen to our customers, and many of our customers have told us that sometimes they
need to know the older version as well as the newer version of popular software programs.
So to help you stay both flexible and up-to-date, we've designed quick study videos to be used
with the latest versions as well as the earlier versions of your software programs.
This could be a lifesaver in a tight situation.
Where major differences exist between the versions, the keystroke sheet shows you both
methods.
Any suggestions you may have to improve our packages would be appreciated.
In this video, we'll show how to use the menus in Lotus, how to move around in the worksheet,
how to enter information into the cells, how to insert columns and rows, and how to move
things around on the screen.
We'll show how to use the at some and other at functions, how to get help on the screen,
how to create graphs.
We'll show how to make macros and how to use macros.
We'll also show how to use the database features of Lotus.
When we first open the Lotus 123 program, we have a blank worksheet on the screen.
The worksheet is made up of cells where we can enter information.
The cells are designated by a cell address.
This cell that's highlighted here is A1.
Notice the cell address here at the top.
This is worksheet A, and cell A1 is currently highlighted.
If we move down to the next cell, this is cell A2.
Notice A2 here.
All the cells in the worksheet can be identified using letters and numbers.
The letters represent the columns, and the numbers represent the rows.
You can have hundreds of columns and thousands of rows in a Lotus worksheet.
We're moving the highlight, highlighting each cell using the arrow keys.
Use the down arrow to move down, and the up arrow to move up, and the left and right arrows
to move from side to side.
We can move to a specific cell in the worksheet using the go to command.
This is done by pressing the F5 key.
Then we type in an address to go to.
Notice it currently says A1.
All we have to do is type on the keyboard a different cell address.
Let's type C7.
Press a C on the keyboard, and then the number 7, and then press the enter key.
Notice now that cell C7 is highlighted, and the cell address is here at the top.
A quick way to get back to cell A1 from anywhere in the worksheet is to use the home key.
Press home, and cell A1 is highlighted again, returned to the beginning of the worksheet.
Notice that there are 20 rows visible on the screen.
We can move up and down in the worksheet a screen full at a time using the page up and
page down keys.
Press page down.
Now we're at row 21.
Press page down again.
We're at 41.
We're skipping down 20 rows each time.
Page up moves back up 20 rows at a time.
When cell A1 is, when any cell is highlighted as cell A1 currently is, we can type on the
keyboard to enter information into the cell.
Let's type a number into this cell.
Type the number, including decimals if you wish, and then press enter.
The number appears here at the top and also in the cell.
Let's move to another cell and type another number in.
And press enter.
We may also type words or letters into the cells.
These are known in MODIS as labels.
Let's move to the right and up one.
Type the word income and press enter.
Notice the word income appears here and also in the cell.
To change the information in a cell, we use the F2 key.
First highlight the cell, then press F2.
This brings the information in the cell back to this edit line on the screen where we can
edit.
Let's use our backspace key and backspace to remove this and then type in a new word.
We can do the same with the numbers.
Use the left arrow to move to the left to highlight this cell, then press F2 again and
the number is brought back to the edit line where we can edit it.
Backspace and then type a new number and press enter.
And when you press enter, the number is actually replaced in the cell.
Now we'll show how to access the menu where we can perform many of the commands in LOTUS
123.
We access the menu using the slash key.
Press a slash and the menu appears at the top of the screen.
The commands in the menu can be selected by highlighting them with the arrow keys.
We're moving with the right arrow to the right and highlighting each one of these menu commands
in turn.
They go all the way across the top of the screen.
We'll talk about many of these commands during the course of this video.
Once a command is highlighted, you may press enter to choose that command.
These commands may also be activated by typing the first letter of the command.
The first letter of the commands in the first line will cause that command to be performed.
If we move to the file command, we can see that we have a submenu of other commands that
are part of the file command.
And then if we press enter while file is selected, now those subcommands have been moved to the
top.
And the second line gives an explanation of what each command is.
Save means to replace the current file with a file from the disk.
Save means copy a file from memory to a file on disk and so forth.
This gives an explanation of what the commands are.
Then we move back to the previous menu using the escape key on the keyboard.
Press escape and we move back to the original menu and then press escape again to turn off
the menus.
Let's bring up the menu again by pressing slash.
Then of course you can also choose the different menu commands by typing the first letter of
the command.
Let's go to the file menu again by typing the letter F on the keyboard.
Type the letter F. Now we have the file commands, the submenu for file.
Now we can choose items from this menu by typing the first letter again.
If we want to retrieve a file, for instance, we can type the letter R. Now we get a list
of files that we can retrieve from the disk.
We'll talk more about retrieving files in a few minutes.
If you ever need help with any of the commands in Lotus, you can use the help menu.
We access the help menu using the F1 key.
Press F1 to bring up the help index.
Then use the arrow keys to highlight the information you need.
To get information about function keys, highlight that topic and then press enter.
Now we have a screen full of information about function keys.
We have a list of the different function keys and what they do.
Leave the help screen and return to the normal screen, press escape.
Just press the escape key and now we're back to the normal Lotus screen.
To quit Lotus or exit the program, press slash to activate the menu.
Then you can press the letter Q for quit.
Quit is over on the far right hand side of the menu.
Or you can press the left arrow.
When you do that, it moves the highlight one to the left, which wraps around back to the
other side.
And now quit is highlighted.
So you can type, you can press a letter Q to quit or you can highlight the command like
this and then press enter.
Notice when we highlight quit, we have a description of what quit means.
It means to end the 123 session, it says here.
Press enter and we have the choice of no currently highlighted.
That means do not end the 123 session, return to the ready mode.
Or if we highlight yes or press Y, this means to end the 123 session.
Now if we press enter again, we get a warning that the things we've done in our worksheet
haven't been saved.
Do we want to end 123 anyway?
If the answer is no, then we'll be given the opportunity to save the worksheet.
If we want to end 123 anyway, we highlight yes or press the letter Y and then exit.
Of course the real power of a program, a spreadsheet program like Lotus, is the ability to do calculations.
Here in this worksheet we've entered some labels and also some numbers representing
May, June, and July totals for rental sales and service.
We like to figure the totals for these categories in each month and also figure a quarter total
for all three months.
What we need to do is enter formulas into these cells.
Formulas will calculate the values in the cells and place those values in the total
cell.
Let's move to this cell and enter a formula.
Formula must begin with either a plus sign, a minus sign, or a parenthesis.
What we want to do is add cells C2, C3, and C4 and place the total in this cell.
Plus, hold down shift and type this key for plus, then type in the cell address for the
first cell, which is C2, then type a plus again and then C3, then type another plus
and C4.
This is a formula.
Now all we have to do is press enter and the total of the three cells above appears in
the total cell.
Let's move to the right and do another one.
Press plus D2, it's D2 this time, plus D3 plus D4 and press enter and the total for
that column appears in that cell.
Now we've entered the formulas and we're ready to save this worksheet on the disk.
To save, you press the slash key, choose F for file.
Now we can enter the name of the file.
Here we have the disk drive and the directory and this is currently showing all of the files
that are within this directory.
We can type in a new name for our file by pressing escape and the cursor now is here
and we can type in the name for our file.
Let's just call it income and press enter.
Now it's been saved on the disk.
Once we've given our file or our worksheet a name, then when we want to save it again
all we have to do is press slash to activate the menu, then press F for file and S for
save.
And it already has the name there.
Then all we have to do is press enter.
And then it asks us if we want to replace or make a backup copy or to cancel the command.
Let's choose replace by typing in R and it automatically replaces the file.
Now we'll show how to print a worksheet.
Print a worksheet, press the slash key to activate the menu, then press the letter P
for print.
Press printer is highlighted, which means send print output directly to a printer.
This is what we want to do, we can just type P for printer.
Now we need to specify a range to print.
What part of the worksheet we want to have printed?
Press enter again and now we can see the worksheet, we need to specify a range.
To specify a range for print or many of the other operations in Lotus, first press the
period key to anchor the range at the current location of the highlight, the current highlighted
cell.
This will anchor the beginning of the range here where the cell is highlighted, notice
it's A1.
Then you just use the arrow keys to move down and across, let's just highlight the top portion
of this worksheet for printing, we could highlight the entire worksheet if we wanted to.
Now notice the range is identified as A1 through F6 and this will change until we press enter,
we can move this anywhere we want.
Now this is the area we want to print, this is the range, so once we've identified the
range like this we can just press the enter key.
And now we're back to the print menu, we've already specified the range, over on the right
you'll notice we have the command GO.
To print you just press a G for GO, then if you have a laser printer you also need to
follow that with PAGE, so you press G for GO and then P for PAGE.
Let's press G, remember of course that we can also use the arrow keys and highlight
G and then press enter, it's the same as pressing a G.
Then we'll highlight PAGE and press enter or we could just press a P on the keyboard.
And PAGE is necessary for a laser printer in order for it to advance the paper out of
the printer.
For a dot matrix printer all you have to do is press GO or the G.
Next we'll show how to use the undo feature in Lotus.
Undo will allow us to recover from a mistake, anything that was deleted can be brought back,
it will allow us to undo the last operation.
But in order for it to work you have to turn the feature on, you have to enable the feature
first and you have to do this before you make the mistake or before you need to undo something.
To turn on undo, press slash, then W for worksheet, G for global, D for default, O for other,
then U for undo.
Then you'll notice that enable says turn undo on, it's E for enable and then finally U for
update.
Let's highlight update first and see what it says, it says save new default settings
in the configuration file.
Let's set the default, undo will be on and we just press U for update or enter when it's
highlighted.
Now it's been updated and remember to get back out of all these menus, we continually
press the escape key and then we're back to the ready mode.
Now let's delete something just to show how we can use undo.
We'll delete the information in this cell that's highlighted.
Now this cell is empty.
Then to use undo, hold down alt and press F4.
No is highlighted, which means do not undo, but we want yes, which means to undo.
We can press a Y or highlight it and it says undo most recent modifications to the worksheet.
Now that yes is highlighted, we just press enter and watch the cell, the empty cell and
it comes back.
So it recovers from the most recent modification to the worksheet, but you must have undo turned
on first before the modification is made in order to be able to undo it.
Now next we'll show how to split the screen, how to split the screen so we can look at
two different parts of the same worksheet at the same time.
Here we have a worksheet that has a section here and then as we scroll down, we see there's
another section that's further down.
To be able to view both of these at once, let's go back up to the top, holding down
the arrow key will move the highlighted cell faster.
This is where we'll make the split.
Then to do that, we press slash, then worksheet, then W for window, there's W for window and
then we choose whether or not to split it horizontally or vertically.
Let's split it horizontally, type H and it splits it right at the current location, the
current highlighted cell.
Now we can use the arrow keys to move up and down in this part of the worksheet and we
can again move to any part of the worksheet in this window.
Then to switch to the other view, we press F6, that puts our highlight or our cursor
in the other window.
Then in that window we can scroll down until the part that we need comes up.
We can of course use page down or page up to move more quickly.
Now we can work on two different parts of the worksheet at the same time and we can
use the F6 key to switch from one window to the other.
Then to go back to the normal view, press slash, W for worksheet and W again for window
and then C for clear.
Then we're back to the original view, the full page view with just one window.
Now we'd like to change the column widths in the entire worksheet.
To do this, press slash to bring up the menu.
Then press W for worksheet, notice that's this choice here, worksheet.
Press W, then G for global, press a G and the choice is column width, which is this
one here.
Of course you can always use the arrow keys to move over to that choice and then press
enter.
That does the same thing as pressing the first letter.
Now that we've highlighted this, let's press enter on the keyboard.
Now it asks us to enter a global column width.
Notice that the current column width is 8.
We just need to type in a new column width, we just type directly on the keyboard.
Let's choose a narrower column, let's choose 6.
Type the number 6 on the keyboard, notice it changes here, then press enter.
Notice now that all of the columns have gotten narrower.
Next we'll show how to insert a column into our worksheet.
To do this we need to first highlight the column where we want to insert and the new
column will be inserted to the left of whatever column you highlight.
You only need to highlight one cell in the column.
So we want to insert a new column to the left of the may column.
Now on the keyboard press slash to bring up the menu.
Then type a W for worksheet and now an I for insert and then C for column.
Now we get to enter the column insert range.
What this is basically telling Lotus is how many columns we want to insert.
Since the range is just one column indicated here, it will only insert one column.
If you wanted to insert more than one column you can expand this range by using the arrow
keys.
Move the arrow key once to the right and now since two columns are indicated here it will
actually insert two columns to the left.
We only want to insert one column so go back and then press enter.
Now one new column is inserted to the left and we can go ahead and type our information
in there if we wanted to create a column for April for instance and fill in the items there.
Now we'll show how to delete a column.
This is very similar.
We're going to go ahead and delete this column that we created.
You could delete any column just by highlighting the column first.
We'll leave the highlight here where it is and press slash then W for worksheet then
D for delete.
This is delete here.
Press D and then C for column.
And enter range of columns to delete.
Here we could use the arrow keys and choose more columns to be deleted if we wanted to.
We could go either direction.
Let's leave it right there and press enter.
And now the column is deleted and it's back to the way it was.
Next we'll show how to insert and delete a row.
It's very similar to inserting and deleting a column.
Let's move down to the next row down and we're going to insert a new row above this row.
Rows are always inserted above the highlighted cell.
Columns are inserted to the left.
Now we're ready to insert.
Press slash, W for worksheet, I for insert, and now type R for row.
This is the choice here, R for row.
Press R and again we can choose how many rows we want to insert.
Let's insert two this time.
So we change the insert range by pressing the down arrow.
This means we're going to insert two rows.
Notice there's two different cells, C2 and C3, selected here.
Now press enter.
And two rows are inserted.
Now we delete the rows using the same method we used for deleting columns.
We have the highlight positioned here.
Then we choose, we type slash for the menu and W for worksheet and then D for delete.
Then R for row.
Enter the range of rows to delete, select those two rows, and again you can delete any
row including rows that have information in them.
Highlight those and then press enter.
And those two rows then are deleted and it goes back to the original appearance of the
worksheet.
Next we'll show how to delete, move, and copy data in our worksheet.
When you're about to perform an operation such as deleting or moving, et cetera, you
can choose, you can set your highlight, you can highlight the cell where you want to begin
first or you can do it afterwards.
The operation varies slightly depending on whether you do it first or later.
Let's say first we want to delete a range of data.
We want to delete this range here, these last totals in these three columns.
Press the slash key and then R for range and E for erase.
Now this cell is highlighted but we want to move down to these cells.
There's two ways we can do that.
We could just type in the cell addresses.
Let's do it this way first.
Just begin typing.
We want to start with cell D5.
So we type D5 on the keyboard, the letter D and the number 5.
Then we need to press a period twice, this indicates a range.
And then the final cell will be F5.
So we just type an F and then a 5.
And this specifies this range of cells, these three cells.
And then we just press enter.
And we've erased those three cells and the data that was in those three cells.
Now we'll show how to move a range of data.
Let's say we want to move this part of the worksheet further down in the worksheet.
And so we'll press slash for the menu and then press M for move.
Notice now it has this range starting out in the corner with A1.
We want to change the range to move down here.
Notice in the upper right hand corner, whenever we're at this point, we're in the point mode.
It's waiting for us to point to a particular cell on the screen.
And we do that, of course, by moving the arrow keys to that cell.
What we're going to do, notice if we use the arrow key to move down now, it starts to block
out all of this area.
And it's choosing that as the range.
But we want to reestablish the beginning of the range right here.
And we do that by typing a period on the keyboard.
You press the period key twice.
And now notice that the first cell listed here is A7.
That anchors the range there.
Then when you press the arrow keys, let's just move the down arrow, it moves this range
on down, and now you can highlight the entire range.
We're just using the arrow keys, moving down and then moving to the right.
So we've highlighted this range.
Now we need to move down and pick a new range.
First, notice at the top it says enter the range to move from.
We've got this range highlighted.
Now once that range is highlighted, we just press enter.
Now it wants to know the range to move to.
And we need to move our cursor down.
This time we just move it down and notice that it changes here as we move it.
Now all we need to do is highlight the corner of where the range will be placed.
We don't have to specify the entire range.
Just highlight the corner and then press enter.
And the block of data is moved, the range of data is moved down to that new location.
That upper left hand corner that we specified and it placed it all in the same location
relative to that.
Now we're going to show how to erase part of a worksheet.
Let's just erase this block of data here.
Press the slash key and the slash key brings the menu back up to the top of the screen.
And then we're going to perform a copy.
So press C for copy and it wants to know the range to copy from.
We want to copy from C5.
So just type in a C and 5 and press enter.
And range to copy 2.
Let's move this down to this one cell here and just copy it to that cell.
And all we need to do then is notice that it says D5 and then we just press enter.
It copies that formula.
And whenever you copy a formula, notice this formula was adding these cells here.
Now this formula adds the cells directly above it.
This is the normal way formulas refer to cells.
It's just the cells that have a certain position relative to that cell.
And we'll talk more about relative and absolute referencing in a minute.
Now we need to show how to copy a cell to a range of cells.
Let's move down.
This time let's highlight this cell first.
We'll highlight this particular cell then press slash and then C for copy.
Notice that the cell range is already specified because we had highlighted it first.
Then press enter.
Now the range to copy 2, let's move to the right and this time we want to specify this
range using a period.
Press the period key on the keyboard.
That creates a range.
It anchors the range at that point.
And then we just move the arrow with the arrow key to the right and now it says E5 to F5
and then we press enter.
And this cell which contains a formula, this formula here, is copied to these two cells.
And of course the numbers are different because now the formula is adding, in this cell the
formula is adding E2 through E4.
And moving to the right, this formula is adding F2 through F4.
So when you copy a formula, the cells that are being referenced in the formula change
depending on where the formula is located.
Now we'll show how to copy an entire range of data and place it somewhere else in the
worksheet.
Similar to when we moved something, but we're also going to leave a copy in the original
place.
So let's press the slash key and then C for copy.
And now let's choose the range.
Let's just go ahead and select it from right where we are.
Move up.
Now we've selected the range.
It goes from F5 back up to A1 and the range can be specified in either direction.
Then press enter and now the range to copy to is what we need to specify next.
Let's move down and place our highlighted cell here.
And when you specify a range to copy to, all you need to really specify is the upper left
hand corner of where you want the range to be.
So choose this and then the area that data will be placed in this area here.
So press enter and now this data is copied down to this area.
Next we'll show how to refer to cells in a formula using relative or absolute referencing.
And we'll show what happens when you copy formulas from one cell to another.
Notice this cell that's highlighted here has this formula C2 plus C3 plus C4.
It's adding these three cells and this is the result.
As we mentioned earlier, when we copy this cell to another cell, the formula will no
longer refer to the cells directly above it as it does here.
It will refer to the ones above it here.
Let's try this again.
Let's press slash, C for copy, and range to copy from, that's the current cell C5.
Press enter and range to copy to, let's just move to the right and copy to D5 and press
enter.
Now when you move your arrow key to the right, you'll notice that this number is different
of this one than this one of course, and it's also, up here you'll notice that it's the
sum of D2, D3, and D4.
When a formula refers to cells such as these, it really refers to cells that have a certain
relationship to the formula cell.
This says C2, C3, and C4, but internally it really knows it as the third cell above, the
second cell above, and the first cell above, and then it takes the sum of those.
So when you copy the formula to another cell, it just duplicates that idea, and then the
cells have the appropriate cell address, in this case it's D2, D3, and D4.
If we copied this over to here, it would be the same thing.
The formula would automatically say E2, E3, E4.
Now there is a way to make a formula refer to a specific cell, in other words to reference
certain cells as opposed to this relative position kind of reference, and we'll show
how to do this.
Let's copy, let's just use this cell that we've got here.
Now remember to edit the information in a cell, you just use the F2 key.
Press F2, and that brings up the formula here at the top, this is the current contents of
that cell, and now we can make changes.
The way to make a cell or formula refer to a specific cell is to put a dollar sign before
the letter and a dollar sign before the number in the cell address.
Let's go back and type a dollar sign here, and then move to the next one and type a dollar
sign there, and we'll do that same thing all the way across.
Then press enter.
Now notice the formula has the dollar signs in it, now the number is still the same here
because it's adding these cells, but now when we copy this formula to a new location, you'll
notice something a little bit different.
Let's copy it, press slash, C for copy, the range to copy from is the current cell, and
the range to copy to, let's move to the next one over, and press enter.
Now you'll notice that in this cell, the number is the same here, even though these numbers
are different.
That's because really, we're not adding these numbers, we're still adding these three.
Because we put in what's called an absolute reference.
These are absolute cell addresses.
That means this formula will always take its value from these cells, and there are times
when you want the formula to do that, usually not in a situation like this, because you
want this number to add these three.
But there are situations where you want to refer to a specific cell, and this is how
you do it.
Use the dollar sign before the letter and before the number in the cell address.
Now let's go back and change these to the appropriate formulas.
Let's move back to the left, and we'll copy this formula, press C for copy, and the current
range, this is the current selected cell, press enter.
Now I'll move to here, and we'll copy that to two cells, press period to anchor there,
and then press the right arrow to choose both of these cells.
This is the range, these two cells, D5 and E5.
And then press enter.
Now this formula, with its relative addressing, has been copied over to those cells.
Now they give the result of the addition of the cells directly above them.
First we're going to show how to automatically add cells using one of the at functions called
at sum.
Then we'll show how to average a group of cells using another at function called at
AVG, or average.
Let's move to this cell here, and we want to place a function here, which is just a
pre-programmed formula, to add these three cells and place the result here.
Now of course before we typed in plus C2, plus C3, and so forth.
This time we want to place in an automatic function that does the same thing.
To place a function in a cell, first we type the at sign.
The at sign is the little sign here on the 2 key.
You perform, you type this by typing shift and the number 2.
So hold down the shift key and type this, and that places an at sign over here on the
left.
That's the beginning of our at sum function.
Then we can type in the at sum function, which is just sum, and then parentheses, and then
all we need to put in is the range of cells that we want to sum.
And remember that our range is indicated by typing the first cell, which is C2 in this
case, C2, and then two dots, and then the last cell, in this case it will be C4, since we're
summing C2, C3, and C4.
Press C4 and parentheses.
Now when we hit enter, we'll get the sum of those three cells.
Press enter on the keyboard, and there we see the sum.
We see the at function appearing up here.
This can be used for any cells anywhere.
They don't have to be right next to the cell.
You can place an at sum formula anywhere that refers to any cells on the worksheet.
Now another at function that we'll show is at AVG, which performs an average.
Let's move the highlight over to this cell, let's highlight this cell, and we're going
to place the at AVG function here, which will sum, which will average all of these values
in these three cells.
And so first of all, press shift and two for the at sign, then type in, you can type in
AVG, or there's another way, you can bring up a list of all of the functions that come
with Lotus, and this is by pressing the F3 key.
So press F3 after typing the at sign, and we get a list of names of functions.
Let's just use the down arrow key to scroll down through the list.
Notice there'll be four items listed at the top here, and there's the one we want, AVG.
Let's go ahead and scroll through this list so we can see how this works.
It shows four, this is just like when you retrieve something, we're using the down arrow
key to go down four names at a time, or you can use the right arrow to move to the right,
and the left arrow to move back.
Now we're going to use the up arrow to move back up to the AVG value.
This is just a way of going through the entire list of at functions that are available.
We'll highlight the AVG function and then press enter.
Now up here, notice on the left, it has entered the AVG letters and the first or left parentheses,
and now we just need to type in the cell addresses for this function.
Remember we want to average C2, D2, and E2.
So type in C2, two dots, and then E2, and this is the range.
It doesn't matter whether the letters are in upper case or lower case, it doesn't make
any difference.
Then press the close parentheses, and then press enter.
Now notice here on the right, the average of these three cells is in this cell.
It's the at AVG function.
Let's try copying this as we did earlier, slash C for copy, we're going to copy from
this highlighted cell, and then let's move down to these two cells below.
We'll anchor the range there using the period, and then move down to select these two cells,
and then press enter.
And now that formula has been copied to these two cells.
Of course the values are different because it uses the normal relative cell reference.
Notice in this cell it's averaging C3 through E3, and then C4 through E4 in this cell.
So the same cell referencing applies whether using normal formulas or at functions.
Next we'll show the at PMT function, which will allow us to figure a monthly payment
given the loan amount, the interest rate, and the term of the loan.
This is just another at function that Lotus offers.
We type in an at sign first, we're going to place the formula actually in this cell right
here.
And we type an at sign by holding down the shift key, holding down shift, and pressing
this two.
Now we'll press F3 to bring up a list of the at functions.
Then scroll down through the list using the down arrow until we get to the area where
it would be located, and there it is, the PMT function, and press enter.
That places the PMT after the at and on the first parenthesis, and then we need to type
in three values.
Now the three values that you need in an at PMT function are again loan amount, interest
rate, and term.
We have to put those three values into the formula or the function in the right order.
If we don't remember what the right order is, you can always use the help key, which
is F1.
When you're at this point in the middle of a formula or trying to enter something, you
just press F1 there, and it gives you the screen that refers to that formula.
Now we see at PMT, and there's the order, principal, interest, and term, the three values
that we need, separated by commas.
These can be actual numerical values, we can type in an actual number, or these can be
cell addresses, and this tells you a little bit about what that function does.
So once we've got that, make a note of that, principal, interest, term, separated by commas.
Then use escape, press escape on the keyboard to go back to the normal screen.
Now we'll type in the rest of this formula.
We need the principal, which is the loan amount, and since we're going to actually use cell
addresses for this, we'll type in cell addresses in the formula.
The loan amount will be in the cell below this title, which is C2, so we'll type in
that for the principal, C2, then a comma, then we'll type the interest rate, which will
be in cell D2, right here, and so we'll type D2, and then another comma, and then finally
the term.
Another term will be in this cell down here, which is C5, looking back over to the left
there, C5, and close parentheses, and then press enter.
Notice that it says error.
That's because we don't have any values typed in here.
If we already had the values in, then it would give us the answer.
So let's type in some values for our loan amount, our interest rate, and our term.
Move to the loan amount cell, which is C2, and let's type in 120,000.
Over here on the left, notice this is where we're typing.
We don't use any commas in the number itself.
This is 120,000.
We're going to buy a house.
Then press enter, and now we need to type in an interest rate.
Move over to the right, and let's type 8.
Notice it appears on the left, 8, and then use the percent key, which is shift 5, 8 percent,
and then press enter.
Notice it appears up here as 0.08.
Then for term, let's move our highlight down to the cell below term, and we'll type in
the term, which is 30 years, 30 for 30 years, and press enter.
Now we have a monthly payment here, but it doesn't seem to be quite right, and that's
because of the way we've entered in the interest rate and the term.
The interest rate always has to be expressed as a monthly, or has an interest rate for
the specified term.
This is set up as if this were annual interest, then this would be 30 years, then this would
be actually an annual payment, not a monthly payment.
To get a monthly payment, you need to divide the interest rate by 12 to get a monthly interest
rate, and you need to multiply the term 30 by 12 to get the number of months in 30 years.
So the way we multiply and divide in a formula is to use symbols.
Let's go back to the formula cell, and what we need to do is we need to divide the interest
rate by 12.
So we need to edit this formula, and remember we edit a cell, edit a formula, by using the
F2 key.
Press F2.
Now we'll go back into the formula, and the way we indicate divide, we need to divide
the number in cell D2 by 12.
So we just type a slash, which means divide, and then 12.
Now in the formula, this means take the number that's in D2 and divide it by 12, and that's
the number that will be used in the formula.
Then we need to also multiply the number in C5 by 30.
So move to there, and type an asterisk, which means multiply, and then 12.
So we're taking the term, which we have as 30, and multiplying it by 12 to get the actual
months, because we don't want to figure out annual payments.
We want to figure out monthly payments, and this would give us the number of months in
our term.
Now, of course, all of these cell addresses could be just values.
We could type in numbers here if we wanted to.
Just type in the numbers instead of the cell address, and then just figure out a payment
really quickly.
Where we're using a little kind of a table or a worksheet set up here, we want to use
cell addresses.
That way we can go back and change, for instance, if we want to change the loan amount, we can
change it right here in the cell, and it will automatically be changed in the formula.
And we can figure out the payments for any loan amount.
We can also change the interest rate or the term by just typing a new number in one of
these cells.
Now that we've got this formula set up, let's go ahead and press enter.
And now we see a more realistic, correct monthly payment here.
This is the actual monthly payment for a loan amount of $120,000 at 8% interest for a term
of 30 years.
And again, we had to put the 12s in there because we want to figure monthly payments,
so we have to divide the interest rate, which is an annual figure, by 12, and we multiply
the term by 12 to get the correct number of months.
Now let's try figuring out a different loan amount.
Go to the loan amount and press F2.
We're going to edit the loan amount.
And notice over on the left, loan amount appears.
Let's go back and change this to 150,000.
We'll delete the 2 and put in a 5.
Press enter.
Now watch the monthly payment figure, and it changes.
There's the new monthly payment for $150,000.
Let's also try changing the interest rate, see how that affects our monthly payment.
Move to the right to highlight that cell.
Then press F2 to edit the information in the cell, and over on the left, now you can see
the interest rate appears.
Let's change it to 9 percent.
Just backspace to erase the 8 and put a 9 there.
And press enter.
Now watch the monthly payment.
It's recomputed at the different interest rate.
Now we'll show how to link worksheets together in a formula.
We're going to place a formula in these cells that will take a figure from two other worksheets
and add it together to have a total.
We have worksheets that have information for the quarter, quarterly totals, and we'll sum
two of those quarterly totals together to get a six-month total for each of these.
This is our first quarterly worksheet, and we're going to take these values here and
place them in the other worksheet.
Use them in the formula.
Notice the name of this worksheet here at the bottom is qtr1.wk3, quarter one.
It's important for us to remember this.
We need to know the name of the worksheet, and we also need to know the cell address
of these cells.
These are cells F2 through F4.
And here's the other quarterly file.
Notice it's for a different quarter, and we'll also be using these cells in the formula.
Notice the name of this file is qtr2.wk3, quarter two.
We'll need to remember that, and these cell addresses F2, 3, and 4.
Now we've gone back to our worksheet for the six-month totals.
We need to enter our formula in here.
We begin typing in the formula by typing a plus.
Formas always begin with plus or.
And then to indicate the name of the other file, we type the angle brackets.
This is just above the comma.
This is a shift comma.
And then the name of the other file, which is qtr1.wk3.
And then the angle bracket is just above the period key, shift period.
And then the name, or then the actual cell address, this is F2, F2.
And then we need to do a plus, type of plus.
And then the name of the next worksheet, the quarterly worksheet, which is shift and comma
to put the angle brackets.
And then qtr and 2, then dot wk3, and then the close angle brackets, which is shift period.
And then the name from the cell address from that one, which is F2 also, F2.
So we're adding together F2 from this worksheet plus F2 from this worksheet.
And that will be placed in this cell.
Now when we have it all typed in, press enter on the keyboard.
And the total appears in this block.
This is the total from those two cells.
Notice when you actually press enter and it goes up here, it expresses the cell addresses
as a range.
And you could put a range of cells in there if you wanted to.
This is expressed as a range, it's the same cell.
Now we've got that formula in this cell.
And this six month total is the total of the amounts in the two worksheets.
And if you were to go back and change the amounts, if there were some changes that had
to be made in those worksheets, the change would be reflected here also.
If you did make some changes, you would need to use what they call link refresh in order
to update those changes.
You do this by pressing the slash key and then choosing F for file and then A for admin.
It's way over on the right, admin.
Press the letter A. And then back over on the left, L for link refresh.
This will update the link between the things.
Now there won't be any change right now.
But if you had changed the totals on these other worksheets, then that would update the
total in your linked worksheet.
Now if you want, now in order to put the same formula into these other cells, all you need
to do is copy as we did before.
And because of the relative addressing, the proper cell addresses will be placed in there.
Let's try this.
Press slash and then C for copy.
Range to copy from is this current cell and press enter.
Now let's move down to this cell.
We're selecting a range to copy to.
So we'll press a period to anchor the range there.
And then press the down arrow to specify this range, C3 and C4.
And press enter.
And notice that these cells have numbers in them.
Now remember the original cell had a reference of F2 on those other worksheets.
Let's move down to the next cell.
Now you can see that it's automatically referenced the F3.
It's automatically assumed that we want to take the next one down and it's changed the
cell addresses to agree with that.
And moving on down to the bottom one, it's automatically placed the F4 cell address in
there.
Next we'll show how to create a graph quickly from some of the data in our worksheet.
To create a graph, press slash and then press G for graph.
Enter in the menu at the top, choose T for type and then choose B for bar.
Then choose G for group.
And moving over to the right, to the left, we need to choose the group range.
We're going to highlight the range of data that we want to use in our graph.
This is anchored here so we need to move down and set our anchor there and highlight this
area like this.
We're going to use the rental sales and service as the information for our X axis and then
we'll divide the data by columns.
In the graph we'll indicate the numbers for May, June, and July according to these settings
on the X axis, these labels.
Now when you've entered that range, press enter and now as we said we're using the columns
as the data ranges.
So we want to choose column wise by pressing C or enter and then finally we want to choose
V for view.
Press a V and now we have our full screen graph on the screen, it fills the entire screen.
It uses different patterns to represent the data.
The numerical values are on the Y axis on the left and then here at the bottom we have
the titles of the different categories.
Let's move in for a closer look.
We have rentals here and sales and service.
We can add other items to our graph such as titles and legends.
Let's see how to do this.
Press escape to clear the graph from the screen.
Then to do the legend choose options, press the letter O. Now over on the left we want
to add a legend first of all.
So press an L for legend.
Now we want to assign the legends and we could choose each one of these data ranges and type
in the actual legend we want to use.
We would type in May for A and June and July but we can also take this information from
the graph itself.
So let's choose range, so R for range.
Then we need to specify the range.
So let's move over to May, this is the range for our legend, we want May, June and July
since the data columns are, these are the columns that are in the graph and we want
to put the legend to show what those different patterns stand for.
So we'll press period to anchor that there and choose June and July and press enter.
Now that we've set the legend, now let's press escape and move back to this menu and press
V for view.
Now at the bottom of the graph we have the legend and it shows what the different patterns
stand for.
This one is May, this one is June and this is July, this pattern.
Now we'll add a title to our graph.
To clear the graph from the screen press escape and we go back to this graph setting screen.
Then choose O for options and T for titles, press T. Now the first title is the title
that appears at the top of the screen.
Whatever title we want to have appear we can just type in here, press enter and this is
the first line of the graph title, this is the actual title of the graph.
So let's type the word income and press enter.
Notice now that this, when we press titles again we go to the graph legends and titles
screen.
These are the legends that we had set earlier and on this screen we also have the titles
listed here.
This is the graph legends and titles screen.
Now we can choose, let's choose a title for the X axis.
We could also put a second line in the main title at the top of the screen, that would
be the second line there.
We'll go ahead and skip that one and let's choose an X axis title.
Since the X axis was the category we'll choose, we'll call it department, let's press enter
for X axis and the X axis title will be department and press enter.
Then we can choose T for titles again and now choose a Y axis title and the Y axis would
be thousands of dollars or dollars.
Let's press enter for Y and then type in dollars and press enter.
Now we can also add a grid to our graph.
Let's press a G for grid and we can choose horizontal or vertical or both.
For a bar graph it's probably best to have just horizontal lines for a vertical bar graph.
Let's have horizontal lines and press enter for that because it's already highlighted
or you can press H.
Then press escape to clear this one and go back to this one and we'll choose V for view.
Press V. Now we see the title at the top, the main title and down at the bottom we have
department, this is rentals, sales and service, the different departments and then over on
the left we have the Y axis which is dollars and then it says thousands which was already
there and we have the legend at the bottom.
Let's take a closer look at the Y axis label or the Y axis title, it says dollars, thousands
of dollars and the department is at the bottom along with the legend.
Now we'll show how to print the graph.
First press escape to clear this graph from the screen.
That gives us the graph setting screen again.
Press escape to clear this.
Now let's take a closer look at the menu while we make the following choices.
Choose P for print, then we want to send print output directly to a printer so that's P for
printer.
Now we want to send an image to the printer.
Over to the right we choose I for image and it says now it says at the top print the current
graph which is this choice current or if you had a different graph you could name the graph.
That's what we want.
We want to print the current graph and then we press C for current and press A for a line
over the right there and press A for a line, press A. This is to align it on the paper
if necessary for your printer and if you don't need to do that then all you have to do is
press G for go, G for go, print the specified graph or range and it's printing.
It sends it to the printer immediately and in just a few moments you'll have the printed
output of your graph.
And this is the final output from a laser printer.
Next we'll show how to use macros and how to create macros.
A macro is simply a series of steps for some kind of task you want to perform in Lotus
and you record the series of steps in a macro instruction and then use one key, one or two
keys to activate that macro.
For instance we're going to create a macro that will automatically add a column and we'll
also create one that will automatically erase a cell and then we'll do another one that
will automatically save an existing file on the disk and you can create macros for any
kind of task or combination of tasks just by simply typing in the commands into the
macro list.
We create a macro by going to a blank area of the worksheet and typing in first the macro
name.
We need to, ahead of time, decide on a name and what commands we're going to use in the
macro and then some kind of description.
And we, to create a macro, to type in the name, the first thing you do is type an apostrophe.
That lets Lotus know that this is going to be a macro.
So you type the apostrophe, notice it appears in the upper left hand corner as we're typing
it in and then the next thing is to actually type the name of the macro which will be,
we use a backslash for that, backslash and then we're going to use the letter C for this
one.
C on the keyboard and now we have the macro name so then use a, press the right arrow
to move to the next cell.
Now that leaves, that leaves the macro name here and if you wanted to you could put a
cell above this that says name so you know what this is and then in this cell we're going
to actually record the steps for the macro.
This will be just like pressing the keys that activate the different menu items.
So we need to actually enter in the different codes that will be performed in our macro
and these are just the normal things that you use to active, that you choose from the
menus.
Of course to start off with, when we're going to add a column, so the steps that we would
go through to add a column would be first to type the slash key, so that's the first
thing we want to enter but of course to identify this as a macro we're going to type an apostrophe
first so type an apostrophe and notice up here in the upper left hand corner on the
entry line you see the apostrophe there and then we type in, the next thing we type in
is a slash and that's the first step in performing this command, we would type a slash and that
activates the menu and then we choose from the menu we choose W, so let's type a W and
then the next command would be for worksheet and the next command would be I for insert
and then we would type a C for column so these are the commands that would actually add a
column in our worksheet and finally we need to somehow indicate the pressing of an enter
key in the formula or in the steps so the symbol we use to represent pressing the enter
key in a macro is a tilde symbol which is right here so we just need to hold down shift
and press this key to enter the tilde now we're finished entering the commands for this
macro press you can press enter on the keyboard or you can also press the use the arrow key
to move to the right to move to the next cell to the right now looking back down to the
cell we have the title the name of the macro and then the actual steps that will be performed
when we perform the macro now here we can add a description type in a description of
the macro we don't really need this but this will help us to remember what the macro does
in the future so let's just type in add column we'll move back up here to the left so we
can see it being typed in just type a dd add column and then just press enter and now
notice that's down here in the cell now we have our macro in our worksheet and when we
save the worksheet that'll be saved with it okay so now what we need to do is name tell
lotus that this is the actual name of the macro this is these are the macro instructions
here in this cell we'll identify this is the name and so let's go back to that cell we'll
highlight that cell and then press slash and r for range then n for name and then l for
labels so what we're doing here is we're naming we're giving a label or a name to a range
of cells in this case it's just the one cell this cell here is the actual macro instruction
and we're going to identify this cell as the name of this cell so this tells us when right
is highlighted here this means that this cell will be the name for the cell on its right
if we were to use down then this cell would be the name for the cell below it and you
could put this macro information in the cell below and identify this as the label for the
cell below or on the left or the or even above from now let's choose right we'll just press
an r for right and now we can actually enter the label range and if we had more than one
cell in the range we could identify we could identify that by moving the arrow keys but
we just need this one cell d8 is this the actual label or the name of that for that
macro and press enter now this has been identified and recorded as the name for this macro now
we can go back we can actually use it by pressing alt and typing the letter c which is what
this means so let's try that let's move our cursor back up into the into the worksheet
area here what we'll do is we use the macro to insert a new column here so we need to
use the alt key for this hold down the alt key and press the letter c and that in that
performs the macro which inserts the column here and all it did was was perform these
steps slash and wic and then the little tilde key represents enter it just performed those
steps and just did it all automatically now we're going to create another macro and we'll
put it down below this this one and this time we're going to give it a name it's more of
a descriptive name and then you'll we'll show how to choose that how to run that macro let's
move down here and highlight we need to leave a blank row between macros in the worksheet
and let's create a new one here this one will be one that we can use to save an existing
file on the disk so we're going to call it we're going to give it a name save unless
we've we've we've typed it in here in the upper left hand corner and now when you press
enter then that that's in the cell there move to the cell to the right and here we'll add
enter the actual steps for the macro okay first let's review what the steps would be
for saving a worksheet we'll just just briefly demonstrate these we would type a slash and
then of course you would type file and then you would type and ask for ask for save and
then it asks you to enter the name of the file to save and it's the current file this
is the current name so this would come up and then you would just press enter to to
save that so we'll press enter now and then it always asks you if you want to cancel or
to replace the existing file it's an existing file on the disk so do you want to replace
it or do you want to make a backup copy of it and then you would just choose R for replace
so those are the steps it's slash then F for file S for save and then press enter to accept
that the name of the file and then R for replace so let's now let's go just press escape several
times to back out of these this menu and now we'll go and actually enter those in here
and when you're doing this you may want to step through those and then write the commands
down so that you can remember what they are and what order they go in and then you can
just type them in when you're creating your macro so let's type in the first thing we
need to type in when we're not going to we're not going to start with a with a letter we're
going to start with a slash first so we need to type in a an apostrophe so it knows that
it's that it's a label so type an apostrophe on the keyboard and we'll we'll look at take
a closer look at these entries over the upper left hand corner you have an apostrophe and
then the next step was slash and then F for file and then S for save and it doesn't matter
if these are lowercase or uppercase letters and then we type the tilde remember the tilde
was the shift and then the upper left hand corner key the tilde key on most keyboards
is next to the one and then that was to that was for enter to choose the name to accept
the name of the file and then next thing we need to choose R for replace so we type in
R and then this is all we need in our in our macro so press enter to to enter that into
the cell and then notice that it's here in the cell and then we can add a description
this is what we do to to save an existing file so type that in here save
save existing file and that that just means to save the file that's currently on the screen
press enter and now now we have that information down here now we need to go back over and
and record this cell as the name of the macro so remember we did that by pressing slash
and choosing R for range we're identifying a range a range of cells and then N for name
we're we're picking a name for the range of cells in this case is just one cell and
then L for labels we're going to choose a label from the worksheet itself as the name
of the range so L for labels and then it will be R for right and again this means that this
label this cell will be the name of the cell to the right of it and so press R for right
and then the label range would be the currently highlighted cell we could if we had if we
if our highlighted cell was somewhere else then at this point we'd have to move the cursor
to this cell and then identify that as the label range and press enter and now that that
has been saved now you can't type in you can't activate this macro with a shortcut key like
we did before like alt-c you have to choose it from a list so okay stop there to activate
this macro we need to use the run command and for run you just hold down alt oops hold
down alt and press f3 this is for run and then it gives you a list of the macros that
are available to run notice we have the one that we the first one that we did it's that
you can run it this way too by just selecting it here and then then running it now this
one's highlighted you could highlight either one of these using the arrow keys and then
press enter to run the macro we've highlighted the one we've created called save let's go
ahead and press enter and it performs the save automatically you saw the menu flashed
why there quickly it goes ahead and does the slash file save enter are automatically now
it's saved that file quickly on the disk without you having to push all those keystrokes
next we'll show how to sort information in a table in lotus we have some information
here some names department salary this can be any kind of data that you have organized
in a table and we can sort this information by pressing the slash key and then choosing
data over on the right you'll find the data and then that's d for data and then s for
sort and then data range that's d for data range now we want me to enter the data range
and the actual data is all we need we don't want to put the headings in there just the
actual data go ahead and highlight we need to of course we need to anchor that right
there or the period and then highlight all of those cells the actual data and press enter
then from this list choose primary key this is the we're going to choose the item we want
to sort by whether it's the net last name or first name or whatever that's the primary
key so type of p for primary and the primary sort key we're just going to use this column
which is the last name column to sort by so let's just choose enter and over on the right
you see we also can choose the sort order a or d a is for ascending d is for descending
we wanted an ascending alphabetical order in other words starting from a and going to
z so let's change this d to an a we just type it we just type an a there type an a on the
keyboard and then press enter and then to do the actual sort we need to choose go go
is over here on the right just press the letter g for go now watch this information here as
we press it watch how it changes it'll re sort it in alphabetical order press a g for
go and notice it's all been re sorted now in alphabetical order and all the appropriate
information in these rows has stayed with the the right person you know it's the name
the names are still the right names and the other information it's sorted everything by
rows okay now we can also use two sorting keys we you notice we just we just show chose
one key we only sorted just by this we can sort by other data also we could sort by last
name and salary or last name and first name let's do that let's sort by last name first
and then by first name okay to do that we of course we don't really need to do that
in this case because the only the only place it would make any difference is where we have
two people with the same last name it would sort their names in alphabetical order their
first names these are already sorted in alphabetical order but or if we had a lot of people with
the same name that were that had the different salaries we could sort by the by the name
and then by the salary or you could make a salary your primary key and sort by that first
and then a lot of people who had the same salary you could sort them alphabetically
let's choose let's just do the last name and the first name just to show how to set up
a secondary key let's press slash and then d for data and then it will be s for sort
and d for data range and then we choose the data range again now it's since we already
chose it it still has that highlighted so we go ahead and press enter now we want to
choose the primary key p for primary and this is what we want to use as the primary key
you want to sort by the last name first press press enter and then over here a or d a is
already there ascending press enter and then to choose the secondary key we can just type
an s for secondary key and then choose the secondary sort let's move to this one and
all we have to do is highlight just one of the cells in that column and it will use that
column for the secondary key press enter and then you have the same choice here ascending
or descending type in an a for ascending and then press enter and then press go to actually
do the sort press go or g for go and it will sort this information now it doesn't look
any different because it was already sorted these two names but if we had many people
for instance named Smith their names would all be sorted in alphabetical order by first
name you can always set up two keys to sort the data by next we'll show how to use the
query feature to find information in our data list what we basically have here is a database
contained in a worksheet in lotus you can of course have as much data in here as a worksheet
can hold it can have hundreds and hundreds of records individual files and probably dozens
or hundreds of fields you can store whatever kind of data you want and basically what you
do is define this area as a database and then you can do you can use some of the query features
to find information in the database and then also to extract information we'll show how
to do this to perform a database query we need to define to lotus what our database
is what area of our worksheet it constitutes the database and then we also need to define
what's called a criteria range this is a small area of the worksheet where we specify what
information we're looking for let's create let's first create a criteria range over here
to we'll do it over here to the side you can place it anywhere in your worksheet it's best
to have it away from the worksheet database data let's put it over here to the side we'll
move our cursor over and highlight a cell over here what we're going to do we could
we could use the query to find any information in any one of these categories we're going
to find use it to find someone by name and we'll also show how to use some of the other
ones so let's type let's type the name let's type the word name in here type it on the
keyboard and it appears above and then when you press enter it appears here and it's important
that the name that this title here be spelled exactly the same as one of these headings
it has to be the same as one of these one of these headings at the top so we're choosing
the name heading and below that below that let's press arrow key and let's type in a
name that we want to search for let's look for someone named Hansen so it looks for the
name the last name Hansen we've typed in Hansen over on the left press enter and the name
appears here now we need to actually define actually do the query the search press slash
to bring up the menu and then we're going to do you do a data data feature so let's
press D for data and the data operation is query so press Q for query now we need to
do two things we need to define an input range we need to let Lotus know that this is the
area that we want to search and of course if it was a long a large database we'd need
to define the entire area so press I for input we're going to specify and let's move now
let's highlight the input range that's the entire database including the headings at
the top now let's move our cursor over to this upper left hand corner and press a period
for anchor to anchor it there and then we'll move over and select the entire database and
you can also of course you can also type in these values you can type in a one E eight
if you if it's a long database if it's a large one you may not want to scroll all the way
down to it if you know where the last cell is then you can type that in that into the
range let's press enter now to define this as our input range now we need to define a
criteria range under press C for criteria and now we need to choose the criteria range
and that would be the the name the word name the label here and the actual name that we're
searching for let's press a period to anchor that and then move up to select the entire
range and this is the criteria range now we press enter okay now to perform the the query
we choose fine in this case we're just trying to find the name Hansen in our database and
so we just press an F for find and then what it does is it highlights that information
that line it finds the name Hansen and it highlights that entire row in the database
and then you use your arrow keys to move through all of the to any other possibilities you
can see that there is another name Hansen if we had many people named Hansen we could
move down using the arrow keys and it would highlight everybody named Hansen press down
arrow and it will highlight the next one and now if we press it down arrow again we get
a warning that there's no more there are no more Hansens and we go back up and it's the
same thing when we get up to the top there are no more Hansen these are all the Hansens
you can find now we could also do the same thing we could set up our criteria range with
a different a different name we could type a different a different person's name into
here once these have been set just type a different person's name and do a find again
and it would find that name or we could choose a different criteria here and look search
for it let's do a search for under salary for all of the people who have a salary greater
than a certain amount you can also do that you can do put a kind of a formula in here
to find certain values let's go back and let's to notice up in the upper right hand corner
it still says find to escape from this find feature just press the escape key on the keyboard
now we're back to the normal menu and let's press escape a couple more times to back out
of all these menus now we're back to the normal ready mode let's change this criteria here
instead of name we want to look for salary so let's just let's just delete this by pressing
let's just press f2 to edit this this information then use backspace and then we'll type in
salary and make sure you have the spelling right it has to match the heading the actual
heading exactly and then press enter now we need to go through and we need to put what
we're looking for so let's press f2 again to edit and this time we'll backspace and
clear it out and this time we're going to put in a value we wanted to search for everyone
who makes a more than a certain amount like such as greater than two thousand so let's
try this let's type in greater than two thousand the greater than key is the shift period key
and then two thousand and then press enter now over on the right we can see that we have
salary two thousand now we've already defined this little block these two cells as our criteria
range so we don't have to do that again whatever criteria heading is here and whatever is here
we'll use that for the criteria for the search all we have to do is go back and do the find
operation again so press slash then press d for data the letter d and then q for query
and then f for find and here it's automatically done the find operation it has found the first
record that where the salary is more than two thousand dollars and as we use our arrow
keys use your arrow keys and move down through it highlighted this one also because this
one is also more than two thousand dollars and watch the next one this one is not more
than two thousand it's exactly two thousand so let's see what happens when we go move
the arrow key down it skipped over that one because it doesn't satisfy the criteria we
set up the criteria over here of greater than two thousand it went to the next one that
was greater than and as we go down further in the in the list it finds this one and then
let's press the arrow key one more time the down arrow and we get an error it won't go
any further because this one does not satisfy the criteria so using the arrow keys we just
can see the ones that satisfy that criteria and of course again if you had a long list
a long database you would be able to go down through and it would highlight each one that
satisfied the criteria and help you find that that one now we'll show how to not only find
data but extract data from the database and place it somewhere else in the worksheet to
do that we not only need to define an input range which we've already done here and a
criteria range but we also need to define an output range somewhere where the information
will be brought out and will be listed and we should find an area where there's plenty
of room underneath it because if we find if we find several different records that match
the criteria then we need to have room for all of those to be listed let's move over
to the right and of course this could be anywhere in your worksheet but we're going to place
it here on the right and first of all we need to escape out of this find mode that we're
in by just pressing escape and then move and pressing escape repeatedly till we get back
to the ready mode then let's create our output range let's move down here and we'll put
in a heading what we want to do we want to find everybody who makes more than two thousand
dollars and we want to place their name their last name place their last name and their
department in in these areas here let's type in name over on the left we can see where
it's typing in press enter and again make sure it's it's exactly the same as the actual
heading and then move to the right and type department press enter now this will be the
output the output headings name and the department then below that will will will be the actual
output range now we need to define this as the output range we do that by going back
and using slash D for data then Q for query and then O for output to define the output
range now we'll define this output range here and what you do need to do is you can anchor
the heading anchor the range there and then move to the left now if we knew if we only
wanted to be able to show a few rows we could move down and select just a small block but
if we just select the headings then the the output range will automatically be everything
below that so you want to make sure there's nothing below that any data any important
data because if you have end up with a lot of information being extracted it will fill
up and erase those other any other cells below this that have information in them now this
is our output range let's press enter now instead of find we want to use extract we're
going to extract information from the database and place it here and again this could be
anywhere in the worksheet so press E for extract and there we have a list of all of the employees
whose names are here and who make more than two thousand dollars and this is their department
and you could use any of the headings in this extract area and any criteria you could look
for years of service you could look for any other criteria that might be in your in your
database now one more thing will show and that's how to use a wild cards in your search
criteria let's go back up and change let's first move escape from this mode that we're
in by pressing escape and we're back to the ready mode and now let's go back up and change
our search criteria let's change this back to to name we're going to look for somebody
by name again and let's type let's press an F2 to edit that cell and we'll change that
to name press enter now it's name again and then in the one below we'll enter the criteria
press F2 to edit and backspace now what we want to do is we want to search for everybody
whose name begins with H so all we have to do is type in an H and then an asterisk an
H asterisk tells the computer to search for H and then anything after it in the name category
so press enter and now that's the criteria that we've set in here is H asterisk it will
search for all the names beginning with H you could use an asterisk before the letter
to find names that end with H or end with some other letter so this becomes the criteria
now we've already identified this as the criteria range we don't need to do that again we've
just put in a different heading here and there's a different criteria to use for the search
now we're going to do an extract this time we could do just a find if we wanted to we'll
do an extract and we'll actually extract the name and the department of all the people
whose names begin with H so let's go back and press slash and D for data and then Q
for query and then finally E for extract press E and now in our extract range on the right
we have all the names that begin with H and their departments the video you just watched
contains intermediate features but you and your co-workers can gain powerful computer
skills from our advanced video package for only $149.50 you'll get advanced videos on
Windows Microsoft Word for Windows Excel Word Perfect Lotus and DOS in two hours or less
per video you can learn dozens of powerful time-saving skills sometimes a single feature
can save hundreds of hours of labor and if you or someone you know needs a solid foundation
in the basics of using computer software we have the answer for only $119.95 you'll get
beginning videos on Windows Microsoft Word for Windows Excel Word Perfect Lotus and DOS
that's $119.95 for beginning videos on Windows Microsoft Word Excel Word Perfect Lotus and
DOS for those who need a solid foundation in the basics of using computer software we
ship all orders the day we receive them you'll get your tapes in seven to ten working days
for another eight dollars we'll rush your order for delivery in three working days to
order the advanced package all six videos for just $149.50 just call this number 1-800-999-3279
order now to order the beginning package all six videos for just $119.95 just call this
number 1-800-999-3279