Dan Stynes SPREADSHEET Primer- Excel 5.0Page # 1

Dan Stynes SPREADSHEET Primer- Excel 5.0Page # 1

I. Spreadsheet Techniques II. Charts

1. Introd. to Spreadsheets III. Database Functions
2. Spreadsheet Basics IV. What's new in Excel 5.0?
3. The Excel 5.0 Window
4. Spreadseert Procedures
5. Misc. Tips

A. Introduction to Spreadsheets and Excel 5.0 for Windows

1. Electronic spreadsheet programs, along with wordprocessing programs accounted for the bulk of the early applications of microcomputers. What wordprocessing packages do for "words", spreadsheets do for "numbers". Virtually any problem that can be set up as a series of calculations in a table format can be handled by a spreadsheet program. Balancing a checkbook, computing class grades, and carrying out mathematical calculations are simple problems that spreadsheets handle quickly and easily. More complex budgeting, financial analysis, trend analysis, forecasting, use estimation, and statistical analysis can also be accomplished with a spreadsheet. Spreadsheets can serve as databases or information systems and can also quickly generate a range of graphs and charts from numbers stored in the spreadsheet.

2. Early versions of spreadsheet programs were VisiCalc and SuperCalc. Lotus 1-2-3 captured much of the market during the late 1970's and was the dominant spreadsheet package throughout the 1980's. Since the mid-80's other spreadsheet programs have competed with Lotus, e.g. Quattro-Pro, and Excel . Since 1990 all of these programs have become available in Windows versions and each has added improved graphics, WYSIWYG, and capabilities of handling multiple spreadsheets at a time.

3. Spreadsheet programs are very similar to one another, although the specific commands/keystrokes and some of advanced features will vary slightly from package to package. Once you learn one program , it is fairly easy to shift to another. Bear in mind, however, that over time many advanced features have been added so that the most recent versions of Lotus, Excel, Quattro, and others have incorporated wordprocessing, database, statistical, and other functions. They have also added toolbars, right mouse button commands and other tricks to make programs more user friendly, as well as more powerful.

4. Just as wordprocessors don't substitute for good writing skills, spreadsheets don't replace good analytical and quantitative skills. Software helps you to organize and complete your work more quickly, but you still must have the ideas, know what you want to do, and be able to set up your analysis in a format that takes advantage of spreadsheet capabilities. You must outline or plan a spreadsheet just as you would outline a paper you might write on wordprocccessor.

LEARNING SPREADSHEET PROGRAMS. There are many ways to learn spreadsheet programs. There is no substitute, however, for spending time at the computer and learning as you work. Each student has their own learning style, but some combination of the following usually works best.

1.Buy a tutorial book and work through the exercises. These provide step by step procedures.

2.Work though the demos and tutorials in Excel - access these from Help command. These are good for an overview of what can be done, but most of you won't remember how to do it from tutorials - at least not the first time. Go back if you need a demonstration of a particular tool.

3.Systematically go through menus/commands to understand what each of basic commands do, where they are, and how they work. Don’t be afraid to try something and see what happens.

4.Prepare your own tip sheet of commands that you use frequently or have difficulty remembering.

5.Read, browse, and outline sections of the manual. Usually it is best to work with manuals and tutorial books while you carry out the instructions on the computer.

6. Use the on-line Help to get more information about particular commands as you need them

7. Take short course, ask someone to demonstrate or help you with a particular problem.

8.The best learning tool is usually to use the spreadsheet program to solve problems, going back to manuals, books, tip sheets, tutorials, or On-Line Help as you need it. Make up your own practice problems.

B. SPREADSHEET BASICS

A spreadsheet is a large electronic table (rows X columns) into which you may enter text, numbers and formulas. Cells of the table may be calculated from other cells to capture simple or complex patterns and relationships in a table. Rows are numbered 1,2,3,4, etc and columns are designated by letters A,B,C,D... You pinpoint a cell in the table by giving the column and row (the cell address); eg. A1 is the cell in the first column, first row; E2 is the cell in the fifth column, second row. Excel 5.0 can handle up to 256 columns and over 16,000 rows, although you seldom will need such a large table. A new feature in Excel 5.0 are multiple worksheets (pages) in a single notebook. Your notebook can have up to 256 pages, each a distinct spreadsheet. We will largely stick to one page (tabs at bottom of spreadsheet are to move between pages).

Spreadsheet table cells may contain labels (text), numbers, formulas, or dates. You enter information in a cell by moving to the cell (using cursor controls or mouse pointer), typing an entry, and pressing the ENTER key (you can also click the checkmark on input line or simply move or point to new cell to enter what you have typed in the active cell.

When you change any entry in the spreadsheet, it automatically recalculates all other cells that depend on the cell you changed. This lets you correct information without having to redo all of your calculations. It also lets you answer a host of "What IF" questions, by altering one or more cells and observing their effect on intermediate or final results. For example, how would my house payment change if the interest rate dropped to 7%? What would be the effect on my overall grade if I 4.0 pointed this class? Very complex relationships may be captured in a spreadsheet .

Once data are entered and stored in a spreadsheet, they can be used for many different purposes and the spreadsheet can be adapted to different problems and situations without re-entering all of the same basic information. Information may be passed between spreadsheets, wordprocessing, database, and statistical programs and easily sent from one place to another in electronic form. On a spreadsheet, you can also quickly correct errors, change formatting, or update the information without having to recompute everything else that depends on the cells you have changed.

Modern spreadsheets have many advanced features, which can make them somewhat overwhelming for the beginner. Stay away from advanced features until you learn basics. The more basic features are more likely to be available on the spreadsheet you will use at home or on the job and even these are very powerful tools.

Basic procedures:

Starting and Stopping Excel

Moving Around

Entering Information

Selecting Ranges and Cells

Copying, cutting & pasting

Saving, Opening & Closing Files

Entering simple formulas like Sums and Averages

• Formatting
• Printing

Before trying these basic procedures, familiarize yourself with the Excel worksheet window including menu items and toolbars. Click through menus to see where various commands are located. Frequently used commands are also available on toolbar and others may be accessed via right mouse button procedures.

C. AREAS OF THE Excel 5.0 SPREADSHEET WINDOW (See Figure 1).

1. Overview

Application Title bar - Application name.

Control button at left to resize, move, max, min, close window

Close (X), Minimize (-) & Restore or Maximize buttons at right end of title bar

Menu Bar : Excel command menus- -click on these and pulldown menu appears

Toolbar : Standard Toolbar for quick access to frequently used commands

Formatting toolbar for quick formatting.

Cell reference area (active cell) and formula/editing area

Document/Worksheet Window:

Title bar & control buttons for a particular worksheet at top

Upper left Corner of grid: click here to select entire worksheet

Column labels A,B,C....;Row labels at left, 1,2,3,...

Scroll bars and arrows in three other corners

Split bars: to split window into horozontal & vertical panes, or 4 quadrants.

Main area : cells of spreadsheet

• Sheet tabs at bottom to move between pages (worksheets) in a multipage notebook. The scroll bars at left are to scroll across pages.

Status bar at bottom

2. THE MAIN MENU - EXCEL COMMANDS

FILE : To open, close, retrieve and save files, preview printing. You also exit from here.

EDIT : Undo, repeat, cut, copy, paste, fill, find & replace, move, goto

VIEW: Toolbars, zoom, full screen view

• INSERT: add columns, rows, pages, range names, functions, charts, macros,..Toolbars

FORMAT: Formats and styles affecting look of spreadsheet, number formats, fonts, etc,

• TOOLS: spellcheck, macros, optimizers & add ins, global options

DATA: Database functions, sort, filter, forms, subtotals, tables

WINDOW: change/choose windows

HELP: help, demos

3 STANDARD TOOLBAR - click the icon on toolbar to execute a given command. In most cases you must first select the block of cells that the procedure will be applied to. Holding mouse over a tool yields description of it in message box and on status bar.

NEW FILE: To start new spreadsheet.

OPEN A FILE: Open (Retrieve) an existing workbook or file

SAVE: save a spreadsheet

PRINT: Print the spreadsheet. Simply click printer to print.

SPELLCHECK : checks spelling on marked sections

CUT, COPY, PASTE - also available with right mouse button.

FORMAT PAINTER: mark cells with desired format, click here, drag mouse over other cells to reformat.

UNDO and REPEAT will undo last action or repeat it.

SUM: Select cell(s) at bottom or right of range, then  to sum row or column.

Function Wizard: To paste functions into a cell

Quick sort, ascending and descending.

Chart Wizard: Mark area to graph and click chart wizard.

Text box, Drawing tools, Zoom %, Tip Wizard

HELP: to get help or see demos

4. FORMATING TOOLBAR: FONT in drop down list box & Font size, Bold, Italics, underline, Left align, center, right align, center across columns, $,%,,increase or decrease decimals,borders, background color, text color. To format a cell or range, mark it first, then left click the tool on toolbar.

D. BASIC SPREADSHEET PROCEDURES

1. STARTING AND STOPPING.

To load Excel from Windows, In Windows 95, you load Excel from the Start button (path is likely Programs, Office 97, Excel) or you double click on an Excel icon on desktop (if one is there). You may also load Excel by double clicking a file with an XLS extension in Windows Explorer (Beware - doing this may load multiple copies of Excel, which will eventually bog your machine down. Better to load multiple files in a single running copy of Excel and use Window item on menu to select them) When you load Excel, you will see a blank spreadsheet with menu and toolbars at the top. You may then proceed to enter information or retrieve (File, open) an existing spreadsheet. You minmize, maximize, close, and rearrange Excel windows the same as for any Windows 95 application.

To end an Excel session, click the File command on menu bar, then Exit (keyboard command is ALT, F, X). Easier is to click the control button (X) at right end of the Excel title bar to Close Excel. Clicking the control button (X) on a worksheet window will close just that worksheet. If you have entered information or changed a previous worksheet file, you will be asked if you want to save your document before exiting (see saving files below) . Click the appropriate box.

2. MOVING AROUND

MOUSE: Click on a cell to make it the active cell; Click scroll bars or arrows to move the window up, down, right, or left.

KEYBOARD: You may move around one cell at a time with cursor keypad (up, down, right, left) or by larger leaps with PgUp, PgDown, Home and End keys. CTRL HOME to move to begining of worksheet (cell A1). You may also jump to any cell by pressing the function key F5 and entering the address you want to go to (then ENTER). e.g Press F5, then type G5 and press ENTER key to jump to cell G5.

3. ENTERING INFORMATION. Select the cell where you want to put the information (make it the active cell), type the information, press ENTER. While entering you may edit with backspace, insert and delete keys, ESC to start over, information isn't entered into the cell until you press the ENTER key.

EDITING. To edit an existing cell, select it and press F2 (edit key), make changes and press ENTER (or click checkbox) to replace the contents. Note editing occurs up in the formula/ editing area at top of document window. In Excel 5.0 you can also edit within a cell by double clicking the cell.

There are four types of Information you can put in any cell:

a.Numbers : begin with a number or + or - sign, If you include a $ or a % sign, Excel will adjust the format accordingly.

b.Text : begins with a letter or other symbol. Label prefixes on text indicate alignment within the cell: ' for left aligned (default), " for right aligned, ^ for centered. It is easier to choose alignments from toolbar - select cells to change alignment and click button on toolbar (left, center, right, across columns).

c.Formulas: Formulas must begin with an = sign. Entering =+A1+A2 in cell A3 sets the cell equal to the sum of the two cells above it. Excel includes all the standard mathematical functions you would have on a calculator and many more. Special built in functions can be chosen from the Formula command on menu bar or function wizard on toolbar.
Functions available in Excel include (1) mathematical functions like sin, cos, exp, log, sqrt, tan; (2) statistical functions like sum, avg, std, var, min, max; (3) string functions that apply to text entries, (4) date and time functions, (5) financial functions like discounting formulas, interest rate fcts, IRR, net present value; and (6) others.

d.Dates. There are several acceptable date formats, eg 04-OCT-92.

4. SELECTING A RANGE OR BLOCK OF CELLS. A contiguous block of cells is indicated by specifying two corners separated by a colon. For example A1:A25 indicates the first 25 cells in column A, A1:H1 covers the first row from column A through H. To select a cell or range of cells:

• SINGLE CELL: To select a single cell, click with mouse or move to it with cursors.

RANGE OF CELLS -MOUSE: Click on upper left corner, hold and drag to lower right corner of a range and release. Hold CTRL key down to select non-contiguous cells/ranges, clicking and dragging as many times as necessary to choose the range. Hold Shift key down to extend a selected range. Select entire rows (or columns) by clicking on the row or column labels at left or top.

SELECTING RANGE WITH KEYBOARD: Move to one corner, press F8, use cursors to move to opposite corner. Press ESC to start over. Use SHIFT F8 to add another range to existing one.

Ranges of selected cells are highlighted with the active cell in upper left corner bordered. You may also NAME a selected range with Insert, Name command. You can then use name in formulas to specify the given range or select/jump to that range with GOTO (F5) command

5. COPYING, CUTTING AND PASTING

USING MENUS OR TOOLBAR USING RIGHT MOUSE BUTTON

Select range of cellsSelect Range of cells. With mouse over this range,

click on Edit, Cut (or Copy)Press Right button, choose cut (or Copy)

Select upper left corner of destinationSelect destination, upper left cell

Click on Edit, Paste or paste toolPress Right button, choose paste

6. SAVING, OPENING AND CLOSING FILES. In the file menu, you OPEN a file to retrieve it (specify or point to the drive, directory and file name). Save replaces existing files on disk with current file of same name; Save As lets you specify a new name and path. Excel files have an XLS extension. Excel can read formats of many other spreadsheet and database programs or save in these formats. File New opens new blank spreadsheet. File Close, closes a file. You can have multiple files open at the same time. To switch between these, use WINDOW command on main menu- choose wndow you want displayed.

7. FORMULAS AND FUNCTIONS . The power of spreadsheets is the ability to enter formulas and functions that link cells in a spreadsheet.

Use +, -, *, / to enter standard mathematical expressions, e.g. =(A1+3*A4)/ B10 computes value for a cell based on values in cells A1, A4, and B10.

SUM : select cell, type =SUM( range of cells to sum), use sum tool on toolbar for shortcut.

Use same format for AVERAGE, MAX, MIN, STDEVP, COUNT, & other functions The general format for functions is =FCTNAME(arguments) where the arguments are usually cell addresses or ranges or numbers . e.g. =SUM(A1:B5) would sum cells A1,A2,A3,A4,A5,B1,B2,B3,B4 and B5. The Function Wizard on toolbar helps you paste functions into formulas or cells.