Microsoft Excel 97 Basics - PDF by pqp12144

VIEWS: 9 PAGES: 11

									                                      Microsoft Excel 97
                                                             Basics




Trainer: ________________________________________________________________

Phone: ________________________________________________________________

Date: __________________________________________________________________



Last Revision: 01/15/97 2:39 PM
Overview of Microsoft Excel 97
Microsoft Excel is a spreadsheet package. It is best used for numeric calculations and
“number crunching”. Anything that you can do on a calculator, you can do in Excel and
more! In addition to manipulating numeric data, Excel can also graph that data as well as
perform database functions.

The Excel Screen
At first glance, the Excel screen looks quite similar to the Word for Windows screen.
There is a Menu, two Toolbars, a status bar and scroll bars.

Menu
The Excel Menu is identical to the Word for Windows menu with one exception; Excel
has the DATA menu in place of the TABLE menu. This should help you if you have never
used Excel before.

Toolbars
As in Word for Windows, the Standard and the Formatting Toolbars are displayed by
default. You can display additional toolbars as well as customize them. The tools will be
dimmed out if they can not be used in the current context.

       Tip: To display additional toolbars using the menu: VIEW, TOOLBARS
       Tip: To customize toolbars using the menu: VIEW, TOOLBARS, CUSTOMIZE
       OR you can click the right mouse button on any displayed toolbars to display
       additional toolbars or customize them.

Formula Bar, etc.
The Formula Bar displays the contents of an individual cell. The Name Box is to the left of
the Formula Bar as well as the Edit Formula button.

Scroll Bars
Vertical and horizontal scroll bars are displayed on the active sheet. Labels will appear as
you scroll to other parts of your screen.

Columns
There are 256 columns per sheet. Columns are labeled alphabetically starting with “A, B,
C,…AA, AB, AC,…IV”.

Rows
There are 65,536 rows per sheet. Rows are labeled numerically starting with “1”.
Notes: _______________________________________________________
____________________________________________________________
____________________________________________________________



                                                                                     Page 2
Cell
A cell is the intersection of a Column and a Row. The active cell is indicated in the Name
Area to the left of the Formula Bar. Each cell can contain 32,000 characters. You may
need to increase the column width so that it will all print. A cell is referenced by the
combination of its Column label and its Row number. “Cell A1” would reference the cell
in the upper-left side of the sheet.

Workbook
Each Workbook is a file.

Sheets
There are three sheets per Workbook by default. You may add or remove sheets to your
Workbook as well as change the default number of sheets for a new Workbook. The
maximum is 255 sheets per workbook.

Office Assistant
Office 97 includes improvements in Help. By default Clippit, the Office Assistant appears
on your screen when you start an Office Application. The Office Assistant will signal you
when there was an alternative to a command that you just performed. A yellow light bulb
will appear in the Office Assistant window when there’s a tip for you to read. Click on the
light bulb to read the tip.

You can click anywhere on the Office Assistant to ask for help. Just type your question in
the dialog box.
        Tip: You can change your assistant by clicking your right mouse button on the
        assistant window and click on Choose Assistant.

Selecting Cells
When you are ready to type information into your Workbook, you need to select the cell
first. You can select a cell by using either the mouse or the keyboard.

Mouse
To select a cell with the mouse, place the mouse cursor over the cell of your choice
making sure that the cursor is in the shape of the white cross and click the left mouse
button once.

Keyboard
To select a cell with the keyboard, just press the arrow keys as many times as necessary.

Entering information
Once you have finished typing the information for a particular cell you should complete it
by either pressing the ENTER key on your keyboard or by using the mouse and clicking
on the Green Check mark on the left side of the formula bar. It’s not recommended, but
you could click on another cell to enter information into a cell and move at the same time.
This method WILL CAUSE PROBLEMS when you are entering formulas and functions.


                                                                                    Page 3
Types of information

Text
Text is any combination of numbers, spaces, and nonnumeric characters. All text is left-
aligned in a cell.

Numbers
Numbers that you type in a cell are entered as constant values and are right-aligned in a
cell. Numbers can contain only the following characters:
        0123456789+-(),/$%.Ee
Leading plus (+) signs are ignored, and a single period is treated as a decimal. All other
combinations of numbers and nonnumeric characters are treated as text. Precede negative
numbers with a negative sign (-) or enclose the numbers in parentheses ( ).

Dates and Times
Use either a slash or a hyphen to enter dates. To display the time using the 12-hour clock,
type a or p after the time; you must include a space between the time and the letter.
Otherwise, the time is based on the 24-hour clock.

       Tip: To enter today’s date, press CTRL ;
       Tip: To enter today’s time press CTRL SHIFT :

AutoCorrect
AutoCorrect automatically corrects many of the most commonly mistyped words. For
example if you accidentally typed “teh” instead of “the”, AutoCorrect would correct your
error as soon as you press the spacebar. There are many words already set up to be
corrected and you can add your own. In addition, you can enter exceptions. These would
be abbreviations that you do not want capitalized.

There are other features of AutoCorrect such as Capitalizing the first character of
sentences and correcting the accidental use of the CAPS LOCK key that you can either
activate or deactivate. To customize AutoCorrect, click on the TOOLS menu, choose
AutoCorrect.

AutoComplete
AutoComplete attempts to finish your entry into a cell. It searches the current column for
text similar to what you are typing. You can press entry to accept the suggested text or
continue typing your text.




                                                                                    Page 4
AutoCalculate
This feature allows you to quickly view a sum of a range by merely highlighting the range.
The sum will appear on the Status Bar. You can change the calculation by clicking your
right mouse button on the Status Bar and click on the preferred function.

AutoFill
AutoFill is a quick way to automatically fill in types of series by dragging on your
worksheet over a cell or a range or by using the menu and choosing EDIT FILL. AutoFill
uses what you have entered in the selected cell or range as a pattern and continues for the
selected range. The types of series you can fill in include Time, Linear, Growth, and
AutoFill.

Workbook maintenance

Saving a Workbook
You should save your document frequently so that you won’t have to re-type the whole
thing again. The first time you save your document it does not matter which method you
use to reach the Save As dialog box. You can click on the Save Button on the standard
toolbar, you can use the FILE menu and choose either SAVE or SAVE AS. You should
choose the Drive and Directory that you want to save it in and then type in your filename.
Excel automatically assigns a three character extension of XLS. It is recommended that
you let this happen so that you will save yourself a few steps each time you open that file
in the future.

Save
Generally used to save modifications to a file. The file keeps the same filename and is
stored in the same location.

Save As
Generally used to save a file in a different location or to save the modifications with a new
filename, leaving the original file unchanged since the last time the file was saved.

Workbook Views
Excel has different views for your Workbook. They are described in the following
paragraphs.

Normal
Normal is the default view used for entering, editing, and formatting data.

Full Screen
Hides most screen elements so that you can view more of your document.


Page Break


                                                                                     Page 5
This is an editing view that displays your worksheet as it will print. You can move page
breaks by dragging them left, right, up, or down. Excel automatically scales the worksheet
to fit the columns and rows to the page.

Starting a New Workbook
Click on either, the FILE menu, NEW or click on the NEW tool.

Closing a Workbook
When you are finished with your workbook it is a good idea to close it. If you have not
saved your workbook or have not saved your changes, you will be prompted to save the
workbook. You can close your workbook using any of the following methods:
• FILE CLOSE on the menu
• Double-click on the workbook’s control menu
• CTRL F4

Closing all Workbooks
If you have a group of workbooks open and you’d like to close them all but you want to
stay in Excel, you can do that by holding down the SHIFT key on your keyboard while
you click on the FILE menu. The close option will have changed to CLOSE ALL. Again,
if you have not saved your changes, you will be prompted file by file to save.

Opening an Existing Workbook
Whenever you want to work with a workbook that already exists but is closed, you must
open it. You may either click on the FILE menu and then choose OPEN or you can click
on the Open Button on the Standard toolbar.

You can open more than one workbook at a time by selecting more than one file. You
can either click and drag on the filenames if they happen to be in sequence alphabetically
or you can hold down the CTRL key while you select the additional files.




Notes: _______________________________________________________
____________________________________________________________
____________________________________________________________
____________________________________________________________
____________________________________________________________




                                                                                    Page 6
Performing Calculations

Formulas
You can enter formulas into a spreadsheet using numbers just as you would into a
calculator. You can also enter formulas using cell references. There are appropriate times
to use both methods and even a combination of both within one formula.

One of the primary advantages of a spreadsheet over a calculator is that you can perform
mathematical operations just as you can on a calculator, but the calculations remain in
your spreadsheet. If your figures changed and you were using a calculator, you would
have to recalculate everything. With a spreadsheet, you would just adjust the figure that
was wrong. Any formulas that reference that particular cell would reflect the change.

A formula must begin with an “=” sign and can contain numeric values, cell references,
and math operators.

Examples:
      =50+300+700
      =B9+B10+B11
      =G3-G4
      =January+February
      Note: The last example uses “natural language.”

Functions
Functions are predefined mathematical calculations. There are over 400 (last time I
counted) already defined in Excel. You can use the functions instead of entering in your
own formula. Two of the most commonly used functions are SUM and AVERAGE.
They are described below.

SUM – totals a range of cells.
Example:
      =SUM(B9:B15)

AVERAGE – averages a range of cells.
Example:
      =AVERAGE(B9:B15)

AutoSum
The AutoSum button will enter the SUM function in a cell or cells depending upon what
you have selected.
Notes: _______________________________________________________
____________________________________________________________
____________________________________________________________


                                                                                   Page 7
Column Width
If you have entered a numeric value that is too wide for a cell you will see # signs across
the width of the cell. If you have entered text that is too wide for a cell the text will
appear to be cut off if there is an entry in the next cell. In both instances the cell contents
are intact but can not be fully displayed. You will need to increase the width of the column
so that the cell contents will print and display correctly.

•   Menu – FORMAT, COLUMN, WIDTH
•   Mouse – Place your mouse cursor between the column headings on the right hand side
    of the column you would like to affect. When the mouse cursor is in the shape of a
    vertical line with a double-headed arrow, CLICK and DRAG.

       Tip: Use the mouse and DOUBLE CLICK for “Best Fit”.
       Tip: Select multiple columns to change them to the same column width at the same
       time.

Row Height
For the most part you will not have to change the height of your rows because the height
of the rows will increase and decrease automatically as you increase and decrease the
point size of your font. However, if you would like to give the appearance of extra space
between your rows you can do so by increasing the height of a row or two.

•   Menu – FORMAT, ROW, HEIGHT
•   Mouse – Place your mouse cursor between the row headings on the bottom edge of
    the row you would like to affect. When the mouse cursor is in the shape of a
    horizontal line with a double-headed arrow, CLICK and DRAG.

       Tip: Use the mouse and DOUBLE CLICK for “Best Fit”.
       Tip: Select multiple rows to change them to the same row height at the same time.




Notes: _______________________________________________________
____________________________________________________________
____________________________________________________________
____________________________________________________________
____________________________________________________________




                                                                                       Page 8
Changing information

Undo
As with the other Microsoft applications you can undo the last thing that you have done to
your workbook. Just as in Word, you can UNDO multiple levels and REDO multiple
levels. Be aware that you only have 16 levels of UNDO.

•   Toolbar -- UNDO button(s)
•   Menu -- EDIT UNDO
•   Keyboard -- CTRL Z.

Typeover
To replace the contents of a cell completely, just select the cell and start typing.

Editing
There are times when you will want to modify the contents of a cell only slightly instead of
completely replacing the contents of a cell. You can edit a cell by one of three methods.
• Mouse: Double-click on the cell
• Mouse: Click up on the Formula Bar
• Keyboard: Press the F2 key on your keyboard.

The Right Mouse Button
With many of the following commands you can use the Quick Menu. The Quick Menu is
accessed by clicking with the Right mouse button over the cell or range that you need to
work with. Depending upon what you have selected, different options will appear on the
Quick Menu. For example, if you have highlighted a few cells some of the options you
would have would include; CUT, COPY, PASTE, CLEAR CONTENTS. If you had
selected a Column you will see additional options like COLUMN WIDTH, HIDE, and
UNHIDE. When using the Quick Menus it is important to click when the mouse cursor is
pointing on top of the cell or range that you’d like to affect. If you select a range and then
point to another area of your sheet you will deselect your initial range and not affect it.
Using the Right Mouse button to access the Quick Menu is an option for the commands
described below.
Notes: _______________________________________________________
____________________________________________________________
____________________________________________________________
____________________________________________________________
____________________________________________________________




                                                                                       Page 9
Clearing information
You can clear the contents of a cell or range by selecting the cell or range first. Then use
on of the following methods.
• Menu: EDIT CLEAR.
• Keyboard: the DELETE key.
• Mouse: Highlight the range and place the mouse cursor over the fill handle, click and
    drag backwards.

       WARNING: DO NOT press the spacebar to clear the contents of a cell. Although
       the cell looks empty and it will print correctly, you will have entered the space
       character into the cell. There are some commands and functions that are set to
       calculate based on a cell having ANYTHING in the cell, spaces included.

Moving information
You can move the contents of a cell or range by one of the following methods:
• Mouse: Highlight the range, point to an edge of the range, when your mouse cursor is
   in the shape of the white arrow pointing to the left, click and drag, release the mouse
   cursor when at the desired location.
• Toolbar: Highlight the cell or range and click on the CUT button. Click once to select
   the upper left-hand side of the location where you’d like to place the range and then
   click on the PASTE button.
• Menu: Highlight the cell or range and click on EDIT CUT. Click once to select the
   upper left-hand side of the location where you’d like to place the range and then click
   on EDIT PASTE.

Copying information
You can copy the contents of a cell or range by one of the following methods:
• Mouse: Highlight the range, hold down the CTRL key on your keyboard, point to an
   edge of the range, when your mouse cursor is in the shape of the white arrow with a
   plus sign, pointing to the left, click and drag, release the mouse cursor when at the
   desired location then release the CTRL key.
• Toolbar: Highlight the cell or range and click on the COPY button. Click once to
   select the upper left-hand side of the location where you’d like to place the range and
   then click on the PASTE button.
• Menu: Highlight the cell or range and click on EDIT COPY. Click once to select the
   upper left-hand side of the location where you’d like to place the range and then click
   on EDIT PASTE.
Notes: _______________________________________________________
____________________________________________________________
____________________________________________________________
____________________________________________________________
____________________________________________________________


                                                                                   Page 10
Deleting ranges
There are occasions where you would rather delete a cell, a range or even a row or
column instead of clearing your data. Highlight the cells, rows or column that you would
like to delete and then click on the EDIT menu, choose DELETE. Your options are:
Cells, Row, and Column.

       WARNING: This option should only be used if you are absolutely sure that you
       don’t have any information in a part of your Worksheet that is not visible at that
       moment in time.

Inserting ranges
There are occasions where you would rather insert a cell, a range or even a row or column
instead of moving your data. Highlight the area where you would like the new cells, rows
or column to be and then click on the INSERT menu. Your options are: Cells, Row, and
Column. You may have more choices based on which you choose.

       WARNING: This option should only be used if you are absolutely sure that you
       don’t have any information in a part of your Worksheet that is not visible at that
       moment in time.
Notes: _______________________________________________________
____________________________________________________________
____________________________________________________________
____________________________________________________________
____________________________________________________________

Extras

Number Formats using the toolbar
You can quickly format a cell or a range using the formatting tools on the toolbar. The
basic tools as well as descriptions are below.
• Currency – Includes a dollar sign, two places after the decimal point and commas if
    applicable.
• Percentage – Applies a Percent sign, no decimal places, rounds if applicable.
• Comma – Similar to the Currency style without the dollar sign.
• Increase Decimal – Adds a decimal if necessary and a decimal place for each time the
    button is clicked.
• Decrease Decimal – deletes a decimal place for each time the button is clicked and the
    decimal if necessary.

Spell checking
You should spell check your Worksheet before you print it. In addition to clicking on the
Spelling button, alternative methods for starting the spell checker are either choosing
TOOLS, SPELLING on the menu or by pressing the F7 key on your keyboard.



                                                                                  Page 11

								
To top