Site hosted by Angelfire.com: Build your free website today!

INTRODUCTION TO MICROSOFT EXCEL

PREPARED BY DENTON SMITH

MODIFIED BY RICHARD NELSON

FOR SELF INSTRUCTION

08.03.01

 

START YOUR COMPUTER and MONITOR

 

            The ‘turn on’ button on the computers in the laboratory is a gray button located about half way down on the left side of the CPU. (Central Processing Unit) Press and release the button to start the computer.

            To turn on the monitor, press and release the button located on the right side of the bottom bar of the monitor frame.

            Please be aware that the ‘turn on buttons’ may be in different locations when other computers are used.

 

GETTING AQUAINTED WITH A FLASH DRIVE

 

            Flash drives, sometimes called a thumb drive or travel drive, provide an easy and convenient way to transfer files and folders from one computer to another. To use a flash drive insert it into a USB (universal serial bus) port and after a few seconds the contents of the drive will be displayed on the monitor screen. Any file or folder on the flash drive can then be opened by double clicking on it. To transfer a file or folder to another location left click on the desired item to highlight it then select the desired action from the menu on the left side of the monitor screen. A dialog box will appear where you can choose the destination of the selected file or folder.

            A more detailed description of using a flash drive can be found in Appendix A at the end of this outline.

 

START MICROSOFT EXCEL

 

            With the computer turned on, the displayed desktop may have an icon for Microsoft EXCEL. If there is an icon click on it and press the enter key to open the program. For some persons who are ‘mouse capable’ double clicking on the icon may be an easier way to open the program. A third way to open a file is to right click on the icon to display a menu and then left click on open.

            If there is no icon on the desktop, click on the Start button located in the lower left corner of the desktop then place the cursor on the All Programs button. A menu will appear that shows all the programs that are available. Find Microsoft Excel and click on it to open the program.

            Figure 1 shows the Excel window with a blank word processing document open. At the topmost part of the screen is the Title Bar. It identifies the document as belonging to Microsoft Excel and is helpful when you have other windows open on your desktop. Just below the title bar is the Menu Bar with the choices of File, Edit, and View etc. A left single click on any if these will cause a drop down menu to appear listing the functions that are available. Try it!! Below the menu bar is the Tool Bar. The Tool Bar makes a number of Excel features available with a single mouse click. When the cursor is placed over any Tool Bar icon, a text box will appear identifying its function. Try it!!

 

 

 

WHAT IS A SPREADSHEET??

            A spreadsheet is a computer program that displays vertical columns (up and down) and horizontal rows (across) and where they intersect is called a cell. The rows are numbered and the columns are alphabetized. Each cell can then be identified. For example, Cell E5 identifies the cell where column E intersects with row 5.

While a spreadsheet was designed for business applications it is a tool that can be used for record-keeping tasks by individuals such as expense records for income tax purposes, Christmas card sent and received lists, address lists, investment portfolio records and any record where text and numbers in columns and/or rows would be helpful.

Text Box: Figure 1. A blank spreadsheet with the Title, Menu and Formula Bars at the top            We will be using Microsoft’s Excel starting with the blank spreadsheet page as shown in Figure 1.

 

The EXCEL DESKTOP is now displayed.

The TITLE BAR is the top line on the screen- It shows the name of the software you are using and the title of the file you have displayed   

 

The MENU BAR is the second line on the screen and lists the choices of FILE, EDIT, VIEW, INSERT, etc. Clicking on any one of these will give you a menu of the functions available. Try it.

 

The TOOL BAR is the next bar and has a series of symbols or icons across the width of the screen. These are shortcuts to performing functions such as SAVE, PRINT, and SPELL CHECK etc. Place the curser (do not click) on any icon on the tool bar and a text box will appear telling you what function that ‘tool’ will do if you click on it. Try it.

 

The FORMULA BAR is next. We will use this bar primarily as an editing bar. An entry on the spreadsheet is replicated in the FORMULA BAR where the entry can be readily corrected or changed. We will cover this in more detail as the course progresses.

 

On the row below the Formula Bar you will see the vertical columns identified by the letters A, B, C etc.

On the left side of the screen the horizontal rows identified numerically as 1,2and 3 etc.

 

CELL MANAGEMENT (MAKING A CELL ACTIVE)

When you open Excel software, Cell A1 is outlined with a heavy black line. This is the active cell. The ACTIVE CELL is ALWAYS OUTLINED WITH A HEAVY BLACK LINE.

 

There are several ways to make a different cell active.

 

The first is with the mouse. Move the curser to cell F10 and left click. Cell F10 is now active.

 

The second is to use the ARROW KEYS on the keyboard located just left of the Number Pad. Press the right arrow key once and the active cell will move one cell to the right. Hold the arrow key down and the active cell will continue to move until the key is released. The direction of the arrow tells you which direction the active cell will be moved and how far depending on when the key is released.

 

Pressing the ‘ENTER’ key will move the active cell DOWN one space.

 

Pressing the ‘TAB KEY will move the active cell one space to the RIGHT.

 

There are many situations when a group of cells need to be active. To make a group of cells active place the curser on the active cell. Make cell F3 active and depress the left mouse button, hold it down and drag the curser down to cell F10. Cells F3 to F10 are now colored blue indicating that they are active and can be formatted to whatever is needed i.e. date, currency etc.

 

Moving the cursor to the edge of the group where the cursor will change to an arrow can move a group of cells. With the arrow displayed, hold the left mouse button down and drag the group of cells to the desired position.

 

To make an entire row or column active left click on the row number or column letter. The row or column will be colored blue indicating that it is now active. Try it! Click on any cell to clear the active group.

 

An ENTRY into an active cell is COMPLETED when another cell is made active or by clicking on the GREEN CHECK MARK on the FORMULA BAR. Clicking on the RED ‘X’ will erase the entry.

 

COLUMNS AND ROWS CAN BE ADJUSTED IN WIDTH AND/OR HEIGHT

Move the cursor to the line between the column headings ‘D’ and ‘E’ and it will change to a vertical line with arrows pointing both to the left and right. Hold the left mouse button down and move the cursor in the direction desired to adjust the width of the column.

Another way of adjusting the width of the column is to double click on the line between the column headings will adjust the column width to accommodate the widest entry in that column.

 

Use the same procedure to adjust the height of a row. The cursor will change to a horizontal line with arrows pointing both up and down when placed on the line separating any of the numbered rows.

 

To put the above exercise to a practical application, the following procedure will build a record of purchases over a period of time. This example will use prescription drugs as the purchased items however the method by which the result was obtained is the primary objective.

 

KEYBOARD USAGE

 

Using the NUMBER PAD on the right side of the keyboard is an easy way of entering numbers and symbols. This is essentially a calculator keyboard and can be used directly without the need of using the shift key for some functions. In order for the NUMBER PAD to operate the NUM LOCK LIGHT must be on. The light is in the upper right corner of your keyboard and is turned on and off by pressing the NUM LOCK key. Figure 2 identifies the various key functions. You will note that there are two ENTER KEYS on the keyboard. Either can be used at the operator’s discretion.

Figure 2. The numerical keypad with the function of the various keys identified.

 

 

ENTERING THE BASIC INFORMATION FOR A PRESCRIPTION MANAGEMENT SPREAD SHEET

Save Row 1and Row 2  for a future title and spacing – so start on Row 3

Click on Cell A-3 to make it active and type ‘DATE’.

Note that the word date also appears in the Formula Bar. Remember that all data or text entered into the active cell will appear in the Formula Bar. On the left of the Formula Bar there is a red ‘X’ and a green check mark. Click on the green check mark and the entry is completed. (Clicking on the red ‘X’ cancels the entry.)

 

Make cell B-3 active and type ‘PRESCRIPTIONS’.

            The word ‘prescriptions’ being to long for the width of the cell spills over into the next cell if it is empty.

 

Make Cell C-3 to make it active and type ‘FOR’ (the part of the word prescriptions in Cell C-1 is not lost)

Make Cell D-3 active and type in PURCHASED FROM.

Make Cell E-3 active and type COST.

 

ADJUSTING THE WIDTH OF THE COLUMN –THREE WAYS

Columns ‘B’ and ‘D’ are not wide enough and need to be adjusted. There are several ways to adjust the width of a column.

The first method was covered earlier. Place the cursor on the line between the letters ‘B’ and ‘C’ and while holding the left mouse button down move the cursor to the left until the desired width is obtained. This procedure can be repeated as often as is necessary.

The second method of adjusting the column width utilizes the FORMAT menu.

 

Left click on the Column Heading letter ‘D’. Column ‘D’ is now active.

Left click on FORMAT in the Menu Bar and then slide down to Column a new menu will pop out.

Left Click on ‘AUTO FIT SELECTION’ and the column is now just wide enough to contain the words ‘PURCHASED FROM’.

 

The third way of adjusting the width of the column is to double click on the line between the column headings – that will adjust the column width to accommodate the widest entry in that column.

 

All text starts on the left edge of the cell and all the headings are aligned that way. You may want to center the headings for better appearance.

 

To center the text (column headings) highlight row A-1 through E-1 and click on the center align button on the TOOL BAR.

 

Make Cell A-4 active and type 1/7/07

 

You can choose the way that you want the date displayed.

 

 To do this Highlight cells A-3 to A-20

Click on FORMAT in the MENU BAR

Click on CELLS and a box with a menu will appear.

Click on your choice (Center) then on OK and all entries made in the area that is highlighted will be the same.

 

Make Cell B-4 active and type ‘Premarin 0.625mg’.

Make Cell C-4 active and type ‘Jane’.

Make Cell D-4 active and type Shell Point Pharmacy.

Make Cell E-4 Active and type 92.57

 

Using the same procedure:

In row 5, enter 1/15/07, Lipitor 20mg, Dick, Walgreen and 310.07

In row 6, enter 2/5/07, Levoxyl, Jane, Shell Point Pharmacy and 32.47

In row 7, enter 2/18/07, Tagamet, Dick, Costco and 86.09

 

SAVING YOUR DOCUMENT FOR THE FIRST TIME

 

It is important to periodically save your work. To save your document the first time

Click on FILE in the MENU BAR.

Then Click on SAVE AS

A box (Figure 3) will appear on the screen and near the bottom there is a line labeled FILE NAME with Book 1 highlighted in the text box.

Type ‘Prescriptions’ and the file now has a name. (Note; because ‘Book 1’ was highlighted any entry would automatically delete the highlighted text.)

There is a SAVE IN line at the top of the box.

Click on the DOWN ARROW at the right end of the line and click on where you want to save the file which is the DOCUMENTS.

Click on SAVE and the file has now been saved in the DOCUMENTS folder

The location of the file has now been established so that subsequent entries can be saved by clicking on the SAVE ICON on the TOOL BAR, or holding down the Ctrl Key and pecking the S key.

 

Figure 3.  When saving a file for the first time it is necessary to tell the computer where to save the file.

Clicking on ‘FILE’ then ‘SAVE AS’ will display the box shown above. The file can be named by typing your choice of name in the area adjacent to ‘FILE NAME’. At the top of the box you will see the words ‘SAVE IN’ with an arrow located at the right end of the box. Clicking on the down arrow will reveal a drop down menu where you can choose the location where the file can be saved. After choosing the destination, click on the ‘SAVE’ button located in the lower right corner of the box.

 

FORMATTING A GROUP OF CELLS

The numbers in Column ‘E’ need to be identified as dollars. There are at least two ways to do it.

Make cells E-3 to E-20 active. They are now highlighted in blue.

LEFT click on FORMAT in the MENU BAR.

Left click on CELLS and a box will appear listing a number of choices.

Left click on Number and then on Currency

Left click on OK.

That will put $ signs in from of the numbers

 

The second (and easier) method is to highlight the group of cells, in this example cells E3:E24 and then locate dollar sign ($) on the TOOL BAR and click on it. The highlighted cells are now formatted as currency but in the accountant’s format.

 

TO OBTAIN A COLUMN (or Row) TOTAL

 

The simplest method to obtain the sum of a column (or row) of numbers is to; first activate the cell where you want the sum to appear, in this example cell E-20, and then click on the SUMMATION symbol (the Roman sigma) in the TOOL BAR. This method can be used only when the numbers are in a single column or row.

CHECKING TO SEE WHAT YOU HAVE DONE USING COLUMN SUMMATION

Highlight cell E-20. There is a formula in the cell that says =Sum(E4:E19)

 

To illustrate the power of a formatted column make cell E-15 active and type 78.80 and then complete the entry by clicking on either the green check mark, enter or one of the arrow keys. The number is entered as currency and the sum has changed to $600.00. To remove the last entry (78.8) make cell E-15 active and press the delete button on the keyboard.

 

There are others ways to obtain the sum of numbers entered on the sheet.

The sum can be made to appear in any cell that is not in the same column or row as the data.

 

To illustrate make Cell G-20 active (or any other unused cell) and remember that

 

EVERY FORMULA BEGINS WITH AN EQUALS SIGN.

Press the equals sign on the keyboard and then, using the FORMULA BAR, type SUM (E2:E15) note that the equals sign is already there and complete the entry by clicking on the green check mark.

Delete the contents of the active cell. (Press the delete key on the keyboard)

 

Another method that is useful when the numbers are NOT IN A COLUMN OR ROW is to press the equals sign and then, in this example, click on E-4 then on the plus sign on the number pad then click on E-5 then plus, click on E-6 then plus, click on E-7 then on the green check mark or the enter key to complete the entry. While you are doing this operation notice what is happening in the FORMUAL BAR

 

LABELING YOUR DOCUMENT

At the bottom of the document is a tab labeled SHEET 1. RIGHT CLICK on the tab and a menu will appear. Scroll to RENAME and type PRESCRIPTIONS (or any name of your choice) then click any where on the screen to complete the entry.

 

PUTTING A TITLE ON YOUR DOCUMENT

Now it would be desirable to  put a title in a row above the column headings. Earlier you saved Rows 1 and 2 for this purpose

In Row 1 type in the title FAMILY PRESCRIPTIONS FOR THE YEAR 2007 and click the green check

Be sure to save your document – a short cut way is to hold down the Ctrl key and peck the S key at the same time

 

ADDING ROWS OR COLUMNS

If you forgot to save the first Row all is not lost. To make room for a title on the page it will be necessary to INSERT A ROW above row 1. To do this click on the number 1 and row one will be highlighted in blue. Click on INSERT in the MENU BAR and then ROW. A new row is inserted above the highlighted row. Click anywhere on the screen to complete the entry. Rows can be added anywhere on your document using the same procedure. Just click on the number of the row and follow the above instructions and a new row will be inserted above the highlighted row

 

The same procedure can be used to insert a column and the new column will be inserted on the left side of the highlighted column.

 

To delete a row or column, first highlight the selected row or column then click on EDIT then on DELETE and on either row or column.

 

ADDING DATA IN THE MIDDLE OF YOUR DOCUMENT

After completing the above document you find a prescription that you missed on your credit card monthly statement. It was for Cardura 2MG on 1/27/07 from Walgreens $128.93 for Dick.

Therefore you must insert a row above Levoxyl entry on 2/5/07. Click on 6 to highlight Row 6, then CLICK on INSERT in the MENU BAR and then ROW. A new row is inserted above the highlighted row. Now type in the new data in that row Notice that when the amount is typed in, the total is automatically updated. . 

 

Close your document and Exit EXCEL.

 

OPENING A FILE THAT HAS BEEN SAVED

There is more than one way to open a saved file. If you know that the file is located in DOCUMENTS click on START then DOCUMENTS to display the files and/or folders that have been saved. Left click on the desired document (Prescriptions) and then on the OPEN button located in the lower right corner of the dialog box and the file will appear on the screen.

 

 If the desired file has been saved to disk or a flash drive, click on START then MY COMPUTER to display the drives on your computer. Open the desired drive to reveal its contents and then click on the selected file and then on the open button located in the lower corner of the dialog box.

 

A third method is to open EXCEL then on the MENU BAR click on FILE then OPEN. A dialog box will appear where the location of the desired file or folder can be selected and when highlighted can be opened by clicking on the open button in the lower right corner of the dialog box.

 

ADDING DATA OR EDITING THE DOCUMENT ok

With the file open you can add data or change existing data. To illustrate, Jane has her prescription for Premarin refilled, so make Cell A8 active and type in a new date 3/7/07. Copy the remaining data from the January entry into cells B, C. D and E8to complete the record. Any cell can be changed or edited by making the cell active and typing in the corrected information.

 

You will note that the content of the active cell is displayed in the FORMULA BAR. When the curser is moved to the entry on the formula bar it will change to a vertical line and the content, either text or numbers, can be corrected in the same manner as a word processing program.

 

USING THE COPY FUNCTION FROM THE MENU BAR ok

Many times it is more convenient to copy existing information than to retype it. To do this, highlight the area that is to be copied, in this example Cells A4-E4 then click on EDIT on the Menu Bar. Now click on COPY and the highlighted area will be outlined with a blinking dotted line. Open a new document male cell A2 active by clicking on its tab at the bottom of the page then click on cell A-2. The active cell becomes the point of origin of the area being copied. Click on Edit on the Menu bar and then on PASTE Essentially this step puts the titles on a new sheet that can be used for the next year’s record. The COPY and PASTE action can also be done from both the TOOL BAR and under EDIT on the MENU BAR.

 

PRINTING THE DOCUMENT (PRINT PREVIEW) ok

Excel will print all of the data that is entered into the file. To see what will be printed it may be desirable to look at a ‘print preview’. To do this click on FILE them PRINT PREVIEW and the document will appear on the screen. If it is OK then click on the PRINT button on the menu bar of the Print Screen display. If the document needs to be revised, then click on the CLOSE button to return to the original display.

It may be helpful to know that about 50 rows and 9 columns (column I) that will be printed on a single page in the portrait format if no changes are made to their height and width. When the landscape format is desired, then the number of rows is reduced to about 37 and the number of columns increased to about 13 (Column M).

 

++++++

SETTING UP THE PAGE TO YOUR NEEDS ok

Go to the menu bar, click on FILE and scroll down to and click on PAGE SETUP.  (Figure 4)  A box will appear with 4 tabs at the top labeled PAGE, MARGINS, HEADER/FOOTER and SHEET. The PAGE tab allows you to choose either the portrait or landscape format for your document.

 

Click on MARGINS and here you can change the margins if needed. No change is necessary for this exercise.

 

Click on Header/Footer and a new menu appears. If you want to put a title on the document then click on CUSTOM HEADER. Click on the center section white area and type ‘COST OF PRESCRIPTIONS’ then ENTER then type 2007. Click on OK.

 

Click on SHEET and here you can add gridlines to the sheet so click on the dot beside the word ‘Gridlines’.

 

Click the OK Button to return to your document and then again click on PRINT PREVIEW to see your document, as it will be printed. You can, if, desired, return to the PAGE SETUP as often as is needed to make the end result fit your needs.

 

SETTING THE PRINT AREA ok

Excel will print only the data on the sheet unless instructed otherwise. Excel makes it possible to select the area of the document that you want to print. To select the area to be printed, highlight it by holding down the left mouse button and dragging the cursor over the area to be printed. The area to be printed will be shaded blue. Go to FILE on the MENU BAR and scroll down to PRINT AREA, then click on it then click on SET PRINT AREA and then click on the screen. The selected area will be outlined with a dotted line. It is usually desirable to check the result by going to PRINT PREVIEW to see the result of the selection.

 

To make a change in the print area, go to MENU then PRINT AREA then click on CLEAR PRINT AREA. The dotted line will disappear and a new print area can be established by following the procedure previously described.

 

 

USING THE SORT FUNCTION OF EXCEL ok

Open EXCEL and then the file labeled SOMEWHERE BY TEL#. This is a listing of the residents of SOMEWHERE  and arranged according to the unit number. The objective is to rearrange this list to have the last names listed alphabetically.

 

It is desirable to delete all empty rows when working with a list.. To delete a row, click on the number of the row to highlight the row, then EDIT on the MENU BAR and then DELETE.

 

To sort all of the residents listed.

Highlight Cells from A-2 through C-23. Do not highlight the Column Heading.

Click on Data on the Menu bar, and then on Sort. Select what you want to sort on  

 

 

USING THE GRAPHING CAPABILITY OF EXCEL ok

Excel provides a method of graphing your data giving the user a choice of several graph formats. Use the following data to obtain a ‘pie chart’ of the following data. Open Chart 1 on the disk provided.

            M/M H. A. Vealot’s portfolio has the following allocation. (000 omitted)

 

            Financial                                  $71,977.00

            Energy                                      $62,456.00

            Utilities                         $54,625.00

            Consumer Staples                     $38,597.00

            Industrial                                  $25,493.00

            Health Care                              $17,298.00

            Cash                                        $12,739.00

                                                           

 

To make any graph the data must be highlighted. On the disk provided, the area is A3:A9 and B3:B9 so highlight this area in blue. Go to the tool bar and click on the Chart Wizard icon. (It is the icon with the colored bars and usually is located left of the question mark.) A box will appear on the screen giving the operator a choice of the different types of graphs that are available. Click on ‘PIE’ and then on the sub-type desired. Click on NEXT and a new box appears and provides the operator the opportunity to change the data range if desired,  and none is so click on NEXT and a new box appears with three tabs labeled,

 

TITLES, LEGEND AND DATA LABELS.

            Click on the white area after Title and type in ‘Equity Allocation’.

            Click on the ‘LEGEND’ tab. On this box is the opportunity to show or eliminate the legend associated with the graph and if the legend is shown, where it can be placed. Click on the various choices to see what the result will be. You can always return to this tab to make changes if desired.

            Click on the tab DATA LABELS and a new box will appear that gives you the opportunity to assign values to the areas of the PIE chart. Click on the choices given and decide which one is best suited to you needs. You may want to return to the LEGENDS tab and remove the legends box. When ready

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

click on NEXT. A new box appears that allows you to choose the destination of the finished chart. No change is needed. Click on FINISH and the chart appears on the same sheet as the highlighted data.

            The chart has an outline at the edge with black squares (handles) in the corners and in the center of each side. The outline with the ‘handles’ indicate that the chart is active. When the cursor is placed on any one of these handles the cursor will change to an arrow and when the left mouse button is depressed the chart can be resized to suit the needs of the document.

            To move the chart to the desired location place the cursor on any blank area of the active chart area and depress the left mouse button, the cursor will change to a four-sided arrow and the chart can be dragged to any place on the sheet.

            To make CHANGES in the TITLE, LEGEND or DATA LABELS the chart must be active, and then click on the CHART WIZARD box on the tool bar and then make any changes that are

 

 

USING EXCEL TO MAKE A SIGN-IN SHEET OR A RECORD OF EVENTS

Open EXCEL and then the File ‘XMAS LIST’ located on the disk provided. (Figure 6) This format can be used to record any event whether it is Christmas cards, other correspondence or billings sent and received. Remember, EXCEL can and must be formatted to fit the needs of the user. Close the FILE.

 

Figure 8 Illustrates how Excel can be used to create a record of events.

 

 

Another example using EXCEL is the sign-in sheet used in the computer labs. Look at Figure 9 and you will note that it is simply an adjustment of column widths and putting appropriate headings on the columns. The adjustment of column widths has been covered earlier but when a sign-up sheet is used it may be desirable to increase the row height to accommodate handwriting. Open EXCEL to a blank page.

 

 

Figure 9 illustrates the use of Excel to record usage of the Computer Lab. Note that the height of the rows is increased.

 

 

To make a series of rows higher, and for this example, click on Cell A-1 and, while holding the left mouse button down, drag the curser down to cell A-25. Click on FORMAT then ROW then ROW HEIGHT.

A Box will appear with a number (12.75) that is the row height in ‘points’. Type a new number that is higher, i.e. 18, then OK. The designated rows are now higher. This procedure can be repeated as often as is necessary.

 

 

 

USING THE DRAWING AND BORDER FUNCTIONS OF EXCEL    

 

To add a line or a border to an area of EXCEL, first highlight the row to be underlined or the area selected to receive a border then, in the TOOL BAR click on the BORDERS ICON. Click on the down arrow beside the icon to reveal the choices available. Clicking on your choice will place the line or border on the area that was outlined. Complete the entry by clicking anyplace on the desktop.

Clicking on the DRAWING ICON will reveal a second tool bar at the bottom of the DESKTOP. There are many choices available. Click on the LINE BUTTON and move the cursor (do NOT depress the mouse button) to the desk top where it will change to a plus (+) shape. At the point where you want the line to begin, depress the left mouse button and while holding it down drag the cursor to the endpoint of the line and release the mouse button. The line now behaves as an active cell and can be moved in any direction by placing the cursor on the line and while holding the left mouse button down and dragging the line to the new position. The line can be rotated by placing the cursor on the circle at one end of the line and holding the left mouse button down move the end of the line to the desired position.

Other icons on the drawing tool bar behave in a similar manner and participants are encouraged to experiment and explore their use.

To REMOVE the DRAWING TOOL BAR click on the DRAWING ICON in the TOOL BAR at the TOP OF THE DESKTOP.

 

GRAPHING DATA

Excel provides a method of graphing your data giving the user a choice of several graph formats. Use the following data to obtain a ‘pie chart’ of the following data. Open Chart 1 on the disk provided.

            M/M H. A. Vealot’s portfolio has the following allocation. (000 omitted)

 

            Financial                                  $71,977.00

            Energy                                      $62,456.00

            Utilities                         $54,625.00

            Consumer Staples                     $38,597.00

            Industrial                                  $25,493.00

            Health Care                              $17,298.00

            Cash                                        $12,739.00

                                                           

 

To make any graph the data must be highlighted. On the disk provided, the area is A3:A9 and B3:B9 so highlight this area in blue. Go to the tool bar and click on the Chart Wizard icon. (It is the icon with the colored bars and usually is located left of the question mark.) A box will appear on the screen giving the operator a choice of the different types of graphs that are available. Click on ‘PIE’ and then on the sub-type desired. Click on NEXT and a new box appears and provides the operator the opportunity to change the data range if desired,  and none is so click on NEXT and a new box appears with three tabs labeled,

 

TITLES, LEGEND AND DATA LABELS.

            Click on the white area after Title and type in ‘Equity Allocation’.

            Click on the ‘LEGEND’ tab. On this box is the opportunity to show or eliminate the legend associated with the graph and if the legend is shown, where it can be placed. Click on the various choices to see what the result will be. You can always return to this tab to make changes if desired.

            Click on the tab DATA LABELS and a new box will appear that gives you the opportunity to assign values to the areas of the PIE chart. Click on the choices given and decide which one is best suited to you needs. You may want to return to the LEGENDS tab and remove the legends box. When ready

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

click on NEXT. A new box appears that allows you to choose the destination of the finished chart. No change is needed. Click on FINISH and the chart appears on the same sheet as the highlighted data.

            The chart has an outline at the edge with black squares (handles) in the corners and in the center of each side. The outline with the ‘handles’ indicate that the chart is active. When the cursor is placed on any one of these handles the cursor will change to an arrow and when the left mouse button is depressed the chart can be resized to suit the needs of the document.

            To move the chart to the desired location place the cursor on any blank area of the active chart area and depress the left mouse button, the cursor will change to a four-sided arrow and the chart can be dragged to any place on the sheet.

            To make CHANGES in the TITLE, LEGEND or DATA LABELS the chart must be active, and then click on the CHART WIZARD box on the tool bar and then make any changes that are needed.

 

COPING AND PASTING A CHART TO WORD

Right click in the middle of the charted display. Left click on copy. That will put a copy of the chart on your clipboard.

Open Microsoft Word and peck the Enter Key twice. That will leave room for a Title. Now click Edit on the Menu bar. The chart will appear on the sheet. Move the cursor up and type in a title 

 

 

Open Chart 2

The data listed in the table on Chart 2  can be graphed either individually or collectively. The procedure is the same as that previously described when making a PIE chart with the exception that this data lends itself to making a line or bar graph. The data to be graphed must first be highlighted. Highlighting adjacent columns has been described previously and is accomplished by dragging (with the left mouse button depressed) over the area desired.

To highlight or to make active non-adjacent columns (or rows), drag the cursor, with the left mouse button depressed over the first column area desired. Release the mouse button and then place the cursor on the top cell of the second column to be highlighted and DEPRESS THE CONTROL (CTRL) KEY and hold it down while you press the left mouse button and drag the cursor over the second column area to be made active. In this manner data from different areas of the spreadsheet can be assembled and put into graphic form.

It is suggested that the data listed in the table be used to experiment with the different forms of the graphs that can be made using Excel.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Listed is the actual price of 3 stocks over the

 

        dates

listed

 

 

 

 

 

Date

Allstate

Citigroup

GE

7/30/04

$47.08

$44.09

$33.25

8/6/04

$46.22

$43.19

$31.52

8/13/04

$45.93

$44.01

$31.89

8/20/04

$47.52

$45.95

$32.65

8/27/04

$47.69

$46.72

$32.77

9/3/04

$47.02

$46.84

$32.84

9/10/04

$46.92

$47.24

$33.88

9/17/04

$48.84

$46.95

$34.22

9/24/04

$48.25

$44.15

$33.41

10/1/04

$48.47

$44.87

$33.97

10/8/04

$47.85

$44.80

$34.02

10/15/04

$47.39

$43.94

$33.55

10/22/04

$46.83

$42.56

$32.95

10/29/04

$48.04

$44.37

$34.12

11/5/04

$48.34

$46.13

$35.19

11/12/04

$50.74

$47.07

$36.25

11/19/04

$50.28

$45.15

$36.27

11/26/04

$51.00

$45.42

$35.44

12/3/04

$51.35

$45.65

$35.83

12/10/04

$50.76

$45.91

$36.69

12/17/04

$50.05

$46.62

$36.75

12/24/04

$51.72

$48.18

$36.50

12/31/04

$51.12

$48.08

$36.58

 

 

Open Chart 2

The data listed in the table on Chart 2  can be graphed either individually or collectively. The procedure is the same as that previously described when making a PIE chart with the exception that this data lends itself to making a line or bar graph. The data to be graphed must first be highlighted. Highlighting adjacent columns has been described previously and is accomplished by dragging (with the left mouse button depressed) over the area desired.

To highlight or to make active non-adjacent columns (or rows), drag the cursor, with the left mouse button depressed over the first column area desired. Release the mouse button and then place the cursor on the top cell of the second column to be highlighted and DEPRESS THE CONTROL (CTRL) KEY and hold it down while you press the left mouse button and drag the cursor over the second column area to be made active. In this manner data from different areas of the spreadsheet can be assembled and put into graphic form.

It is suggested that the data listed in the table be used to experiment with the different forms of the graphs that can be made using Excel.

 

 
ENJOY USING EXCEL

 

Denton L Smith and Richard R Nelson

 

 

SOME THINGS TO REMEMBER

To save your work often using the SAVE AS command for the first time.

Subsequent SAVES can be made using the SAVE button on the TOOL BAR.

That an entry made in the active cell is replicated in the FORMULA BAR.

That the work space in the FORMULA BAR behaves the same as a WORD PROCESSING program.

That an entry made in the FORMULA BAR is also entered into the active cell.

That every formula begins with an EQUALS SIGN.

That text will always start on the left side of the active cell and continue into the adjacent cells if they are empty.

That numbers will align on the right side of the active cell and will not spill into the adjacent cells.

That the width of a column or the height of a row can be changed to fit your needs.

That a display of ##### will appear indicating that the number is too long to fit in the cell. Make the column wider.

 

To explore and to try the many options the MENU and TOOL BARS have to offer and