MICROSOFT EXCEL 5.0 INTRODUCTION..................................................................................................................1 OBJECTIVES..........................................................................................................................1 THE WORKSHEET WINDOW ..............................................................................................2 OPENING A WORKSHEET........................................................................................................3 SAVING A WORKSHEET..........................................................................................................3 SCROLLING THROUGH A DOCUMENT WINDOW ......................................................................4 CHANGING THE ACTIVE CELL ................................................................................................5 ENTERING TEXT ON A WORKSHEET ........................................................................................6 WORKING IN THE FORMULA BAR ...........................................................................................8 CLOSING A WORKSHEET WINDOW.........................................................................................9 QUITTING MICROSOFT EXCEL ............................................................................................... 10 ENTERING NUMBERS AND FORMULAS...........................................................................10 CREATING A SERIES OF DATES ............................................................................................. 11 ENTERING DATA IN WORKSHEET CELLS .............................................................................. 12 ENTERING SIMPLE FORMULAS ............................................................................................. 12 FORMATTING A WORKSHEET ..........................................................................................17 SELECTING CELLS AND RANGES ..........................................................................................17 CHANGING COLUMN WIDTH................................................................................................ 18 CHANGING ROW HEIGHT .....................................................................................................19 REPEATING A FORMAT.........................................................................................................20 FORMATTING NUMBERS ......................................................................................................20 APPLYING RANGE FORMATS AUTOMATICALLY.................................................................... 21 DISPLAYING AND HIDING TOOLBARS ................................................................................... 22 ALIGNING CELL ENTRIES .....................................................................................................23 COPYING AND MOVING CELL DATA................................................................................ 25 FILLING ADJACENT CELLS ................................................................................................... 25 INSERTING ROWS, COLUMNS, AND CELLS ............................................................................26 DELETING ROWS, COLUMNS, AND CELLS.............................................................................27 MOVING CELL DATA............................................................................................................27 CLEARING CELL ENTRIES .....................................................................................................29 CREATING A NEW DOCUMENT............................................................................................. 29 SWITCHING BETWEEN WINDOWS.........................................................................................29 LISTS, CHARTS, ADVANCED FEATURES..........................................................................30 CREATING A CHART .............................................................................................................30
Microsoft Excel
Revised 3/08/95
i
Introduction Microsoft Excel is the spreadsheet component of Microsoft Office, which includes Word (word processing), Excel (spreadsheet), and Powerpoint (graphics and presentations). Excel provides: *
Workbooks (which contain worksheets)
*
Lists of Data (for record/field-type database tasks)
*
Charts (for displaying data graphically)
*
Advanced features for data acquisition and manipulation
Microsoft Excel also offers on-line tutorials, on-line help, and user’s guides. [HINT: For additional practice, Chapter 2 of the Excel User’s Guide (pages 10 through 25) offers an excellent set of exercises for the concepts covered in this guide. Each exercises is indexed to the chapters and sections of the User’s Guide that contain additional information and details. ]
Objectives •
to open, manipulate, save, and close workbooks and worksheets
•
to enter, format, and manipulate labels, data, and formulae
•
to create data lists and charts
Microsoft Excel
Revised 3/08/95
1
The Workbook Window Worksheet
Column Heading
Menu Bar Tool Bars Formula Bar
Active Cell
Row Heading
Status Bar
Menu Bar Standard Toolbar Format Toolbar Formula bar
display menus, which contain commands. open files, edit text, print, create charts, tables, etc. change cell and text format, alignment, and display. enter and edit cell data
Worksheet
256 columns, labeled A through IV, and 16,384 rows. A workbook contains up to 16 worksheets.
Status Bar
displays messages from Microsoft Excel. In the diagram, Microsoft Excel is “Ready” for a command.
Scroll Bars
move the window to a new area of the worksheet.
Row/ Column Headings
2
Scroll Bars
specify the position of cells on the worksheet.
Virginia Tech Administrative Client Project
Spring 1995
Opening a Workbook From the File Menu select Open... to open a workbook and display it in a window. You can open a Microsoft Excel workbook, worksheet, chart, macro sheet, or any of a number of different types of files. To Open... a folder, select a folder name in the Select-a-document dialog box, and then choose the Open... button. To move to a different folder, point to the folder name at the top of the file list box, and then drag to select a folder. To change disk drives, choose the Drive button, or you may choose the Desktop.
Open a workbook 1. 2. 3. 4.
From the File menu, choose Open.... In the Select a Document box, double-click PRACTICE to switch to the PRACTICE folder. Select LESSON1. Select the Open... button.
You can quickly enlarge a window to fill the entire workspace. Click the zoom box or drag the size box until the window fills the entire workspace. You can also quickly restore the window to its previous size. Click the zoom box. Sheet1 is active when you first open a workbook. To switch to another worksheet in the workbook, scroll to and/or click its tab at the window bottom.
Saving a Workbook Use the Save As… command when you need to name a workbook that you are saving for the first time, or when you want to save under a different name a copy of the currently open workbook. Type the name in the dialog box that appears. You also use Save As… to save a copy of the current workbook in a different file format. After you've named your workbook with the Save As… command, you can click the Save File tool on the toolbar whenever you want to save your changes. The current version of your workbook replaces the previous version on the disk. If you click the Save File tool before you've named a
Microsoft Excel
Revised 3/08/95
3
workbook, Microsoft Excel displays the Save As… dialog box and prompts you for a name.
Save a workbook 1. 2. 3.
From the File menu, choose Save As…. In the Save As box, type BUDGET. Select the Save button.
"BUDGET" now appears in the title bar of the active window.
Scrolling Through a Worksheet Window When a worksheet is too large to fit within a window, you can scroll through the worksheet window to see other parts of the worksheet. Scrolling means moving cells across the screen to bring the cells that are currently above, below, or to the side of the window into view. You use the scroll arrows and scroll box located on the scroll bars to move the worksheet cells through the window.
up scroll arrow scroll box scroll bar down scroll arrow
Scroll through the worksheet 1.
Click the down scroll arrow. The window moves down one row.
2.
Drag the scroll box down to the bottom of the scroll bar. The window moves down to display the end of the worksheet.
3.
Click the up scroll arrow above the scroll box until the scroll box is at the top of the scroll bar. The window moves up to display the beginning of the worksheet.
4
Virginia Tech Administrative Client Project
Spring 1995
Changing the Active Cell Cell A1 of sheet1 is the active cell when you first open the workbook. Data you enter is stored in the active cell. You can identify the active cell by its dark border. When you select another cell, it becomes the active cell. You can select cells with the mouse or the keyboard. Using the mouse to make another cell the active cell: Click the cell to which you wish to move. If you cannot see the cell you want in the window, use the scroll bars to scroll through the window, then click the cell. Using the keyboard to make another cell active: The keys you can use to select cells in Microsoft Excel are listed in the following table.
To Move: Left one cell Right one cell Up one cell Down one cell Up one screen Down one screen To start of row To end of row To start of worksheet To end of worksheet
Microsoft Excel
Press: LEFT ARROW RIGHT ARROW UP ARROW DOWN ARROW PAGE UP PAGE DOWN CTRL+LEFT ARROW CTRL+RIGHT ARROW CTRL+HOME CTRL+END
Revised 3/08/95
5
Entering Text in a Worksheet Your exercise worksheet will be a projected budget for the Department of Underwater Basketweaving. You will label various worksheet areas with descriptions and labels. When you document your worksheets, you and others will better understand them and they will be easier to use.
Select Cell A1 1. 2.
3.
Place the mouse pointer over the cell in column A and row 1. Click the mouse button. A1 becomes the active cell. You see a border appear around cell A1, and "A1" appears in the reference area to the left of the formula bar. Enter “Title” in cell A1
What you type appears in both the active cell and the formula bar. The cancel box and enter box appear in the formula bar, and the message in the status bar changes from "Ready" to "Enter. cancel box
reference area
enter box
active cell
formula bar
You can store your data in the active cell by clicking the enter box in the formula bar or by pressing ENTER. You can cancel the entry by clicking the cancel box in the formula bar. [If the active cell changes when you press RETURN, from the Tools menu, select Options..., then select Edit. Then click on “Move Selection after Return” so that the check box is empty.]
6
Virginia Tech Administrative Client Project
Spring 1995
If you make a mistake while typing in a cell, use the DELETE key to backspace and erase text, or click in the text to position the insertion point. Then, backspace to erase, or type to insert new text.
Enter additional headings and labels As you type row titles and labels, long entries will appear to spill across onto other columns, even though they are actually only stored in column B. Do not worry about this. You will learn to change column width in a later chapter. 1. 2. 3. 4. 5. 6.
Select cell B1. Enter: Department of Underwater Basket Weaving Fiscal Year 1994 Select cell A3. Enter: Created by Select cell B3. Enter your name. Select cell A5. Type Fiscal Model Area Select cell B7. Type Gross Revenue Click in cell B8.
The top portion of your worksheet should now look like this:
Select a cell range and enter the row titles You can save time entering data in a range of cells if you select all of the cells in the range first. As you enter data in each cell, the next selected cell becomes the active cell in a top-to-bottom, left to right order. To select a cell range, drag the mouse pointer from the first cell to the last cell in the range. Start by selecting cells B8 through B17, where you will enter the titles for Gross Revenue and Cost of Goods Sold. The first cell you select remains the active cell. After you select the cells, enter the data shown in the following
Microsoft Excel
Revised 3/08/95
7
illustration. If you make a typing mistake and want to move backward through the selection, hold down the SHIFT key and press RETURN. 1.
Drag from cell B8 to cell B17
2.
Type the remaining entries, as shown in the following illustration. Press return after each entry. When you reach the last selected cell and press RETURN, the first selected cell becomes the active cell again.
Editing in the Formula Bar You can always change a cell entry by selecting the cell and typing a new entry over the old one. If you want to edit the existing entry without typing from scratch, you can edit the entry in the formula bar which displays the contents of the active cell. You may do any of the following: 1. 2. 3.
8
Click in the formula bar to insert new information at that point. Double click in any word you wish to replace. The word will highlight and what you type will replace it. Click at the beginning and drag to the end of a section you wish to replace. The section will be highlighted and what you type will replace it.
Virginia Tech Administrative Client Project
Spring 1995
Press RETURN , or click in another cell on the worksheet to enter the change, or Press ESC to leave the formula bar without making any changes. 4.
[HINT: You may activate the cursor keys for use in the formula bar by pressing control-u. The arrow keys will move one character at a time, the HOME key moves the insertion point to the start of the entry, and the END key moves the insertion point to the end of the entry.]
Editing a cell entry 1.
Select cell A5.
2.
In the formula bar, double-click "Fiscal".
3.
Type Budget. Press RETURN.
Closing a Workbook Window You can choose the Close command from the File menu to close the active workbook window. You can also click the close box. If you have made any changes to the workbook that you have not saved, Microsoft Excel displays a dialog box asking whether you want to save any changes.
Close the Workbook 1.
From the File menu, choose Close.
2.
Select the No button to cancel changes to the workbook.
All workbook windows are closed, but Microsoft Excel is still running in your computer with the menu bar displayed.
Microsoft Excel
Revised 3/08/95
9
Quitting Microsoft Excel To quit Microsoft Excel, from the File menu choose the Quit command. If you have any workbook windows with unsaved changes, Microsoft Excel displays a dialog box for each of them, asking whether you want to save changes.
Quit Microsoft Excel 1.
Choose Quit from the File menu.
Entering Numbers and Formulas First, double-click the Microsoft Excel icon to start Microsoft Excel.
Open Lesson 2 1.
Open LESSON2.
2.
Save the workbook as BUDGET.
3.
Choose the Yes button to replace the previous BUDGET workbook.
Enlarge the workbook window 1.
Close any other open windows.
2.
Click the zoom box.
10
Virginia Tech Administrative Client Project
Spring 1995
Creating a Series of Dates You may enter a date simply by selecting a cell and entering the date in any standard format (e.g. 2/3/95, January 95, Feb-95, etc.). By dragging the fill handle, you can create many kinds of series and copy values to adjacent cells. The fill handle is the small square in the lower-right corner of the active cell or range, as shown in the following illustration.
fill handle
Create a series of dates 1.
Select cell C6.
2.
Type Jul-93.
3.
Drag the fill handle from C6 to N6. Look at the reference area of the formula bar as you drag the mouse. The value to be placed in the cell that the mouse pointer is over changes as you drag the fill handle.
4.
Scroll back to the left edge of the worksheet. Your worksheet should look like the following illustration.
Microsoft Excel
Revised 3/08/95
11
Entering Data in Worksheet Cells Enter the budget figures for July You will save time by selecting the range of cells where you want to enter data and then pressing RETURN after each entry. 1.
Drag from cell C8 to cell C16.
2.
Enter the data for July so that your worksheet looks like the following illustration. Press RETURN after each entry.
July data
Entering Simple Formulas A formula can consist of numbers, arithmetic operators, cell references, and functions. You can create a formula by typing, by pointing to cells with the keyboard or the mouse, or by pasting names and functions. To tell Microsoft Excel that you are entering a formula in a cell, begin the entry with an equal sign (=). You can also type +, -, or @ to begin a formula; Microsoft Excel converts these to equal signs. References in formulas: A reference in a formula can be the address of a cell or cell range. To indicate a range of cells, you use a colon (:) between the first and last cells; for example, B7:B9 refers to cells B7 through B9. To indicate a
12
Virginia Tech Administrative Client Project
Spring 1995
list of cells, you use a comma; for example, B7, D3, H9 refers to cells B7, D3, and H9. Functions in formulas: Microsoft Excel has hundreds of worksheet functions to help you perform specialized calculations easily. A worksheet function is a special built-in formula that performs an operation on the values you provide. For example, the formula =SUM(C22:C26) uses the SUM function to add the values in the cell range C22:C26. It returns the same result as the formula =C22+C23+C24+C25+C26, which adds the values individually. Functions can be used alone or nested within other functions. You can enter a function by typing it or by using the Function Wizard on the standard toolbar.
Function Wizard icon Arguments in functions: The arguments in a function tell Microsoft Excel how you want the function carried out. For example, when you use the SUM function, you need to specify which numbers or cells you want summed. The arguments appear in parentheses after the function name. Individual arguments are separated by commas.
Type a formula Create a formula in cell C10 to find the sum of the gross revenue items in cells C8 and C9. Remember that you begin a formula by typing an equal sign (=) and that you indicate a cell range by using a colon (:). 1.
Select cell C10
2.
Type =sum(c8:c9). Press RETURN. The total of Sales and Shipping (32,450) appears in cell C10.
The Function Wizard: By clicking the Function Wizard icon on the Standard toolbar, you may select the function you want from a list of all the available worksheet functions. If you begin a formula by using the Function Wizard, Microsoft Excel adds the equal sign to the beginning of your formula automatically.
Create a formula by pointing Add references to formulas by pointing: Using the function wizard, you can use the mouse or the keyboard to point to cells whose references you want to Microsoft Excel
Revised 3/08/95
13
use in the formula. The cells you point to are surrounded by a dotted line called the moving border. Create a formula in cell C17 to sum the items under Cost of Goods Sold. You will use the function wizard and the pointing method to create the formula. 1.
Select cell C17.
2.
Select the Function Wizard. The Function Wizard (step 1 of 2) dialog box appears.
3.
In the Function Category box, select Math & Trig.
4.
In the Function Name box, scroll down and select SUM.
6.
Click the NEXT> button. The Function Wizard (step 2 of 2) dialog box appears. (Click and drag the title bar of this box and move it out of the way, if necessary.)
7.
Drag from cell C13 to cell C16. Press RETURN or click Finish>. The total Cost of Goods Sold (19,316) appears in cell C17.
14
Virginia Tech Administrative Client Project
Spring 1995
Create another formula by pointing Cell C19 will contain your gross profit. You'll enter a formula to calculate the differences between the Gross Revenue and Cost of Goods Sold. This formula will contain only arithmetic operators and references. 1.
Select cell C19.
2.
Type an equal sign (=).
3.
Select cell C10.
4.
Type a minus sign (-).
5.
Select cell C17.
6.
Press RETURN. The difference between Gross Revenues and Cost of Goods Sold (13,134) appears in cell C19.
Using the AutoSum tool
Auto Sum icon
The AutoSum tool: Click the AutoSum icon on the Standard toolbar to paste the SUM function and a proposed cell range into the active cell. Click the tool again to accept the proposed range and cancel the moving border. 1.
Select cell C33.
2.
Click the AutoSum tool on the toolbar. Microsoft Excel places the SUM function and a proposed cell range in cell C33. The moving border encloses cells C22:C32.
3.
Click the AutoSum tool again or press RETURN to accept the proposed range and enter the formula in the cell. The total of Expenses (11,085) appears in cell C33.
Microsoft Excel
Revised 3/08/95
15
Enter the last formula 1.
In cell C35, type =c19-c33
2.
Press RETURN The difference between Gross Profits and Total Expenses (2049) appears in cell C35, as shown in the following illustration.
Save and close your workbook 1.
From the File menu, choose Close.
2.
Click Yes to save changes to the workbook.
16
Virginia Tech Administrative Client Project
Spring 1995
Formatting a Worksheet Formatting functions may be found in the Format menu and its submenus (Row, Column, and Sheet).
submenu
menu
Selecting Cells and Ranges Select cells first: When you format the worksheet, whether a single cell or the entire worksheet, you first select the cells you wish to format and then choose a formatting command or function. You will save memory and disk space by formatting only the cells and cell ranges you need, rather than the entire worksheet. The following table lists the procedures for selecting: To Select: A single cell One row or column Multiple adjacent cells
Multiple adjacent rows or columns An entire worksheet
Microsoft Excel
Do This: Click in the cell. Click the row or column heading. Select a cell. Hold down the SHIFT and double-click in the cell that borders on the selected cell in the direction you want to extend the selection. Drag across the column headings or row headings. Click the blank box in the upper-left corner of the worksheet.
Revised 3/08/95
17
Changing Column Width Column Width: In general, select a section of the worksheet; then in one motion, click and hold on the Format menu, drag down to Column, drag right to Width..., then release the mouse button. The Column Width box will appear. The standard width for a new worksheet is 10 but you can define a width from 1 to 255 characters. AutoFit: When you choose an AutoFit option from a Format sub-menus, Excel widens the selected columns so that the longest string of numbers or text in each column is fully displayed and does not spill over into other columns.
Change the Column width for the entire worksheet Change the width of all the columns in the worksheet to 12 characters. 1.
First, click the upper left corner of the worksheet (the empty box where row and column headers meet). The entire worksheet is selected.
2.
With one motion, click and hold on the Format menu, drag down to Column, drag right to Width..., then release the mouse button.
3.
In the Column Width box, type 12.
4.
Click OK.
5.
Click any cell to cancel the selection of the entire worksheet.
AutoFit the width of column B 1.
Select cells B7:B35.
2.
With one motion, click and hold on the Format menu, drag down to Column, drag right to Width..., drag down to AutoFit Selection, then release the mouse button.
Changing Row Height
18
Virginia Tech Administrative Client Project
Spring 1995
You can also change row height much as you change column width. The standard row height of a new worksheet is set to accommodate the normal worksheet font. When you change the size of a font in a cell, Microsoft Excel adjusts row height automatically. Row height is measured in points instead of characters. One inch equals 72 points. In general, use the Row submenu exactly as described above for the Column sub-menu.
Change the row height for row 11 You want row 11 to act as a border. Make row 11 about half as high as the other rows. 1
Select any cell in row 11.
2
With one motion, click and hold on the Format menu, drag down to Row, drag right to Height..., then release the mouse button.
3
In the Row Height box, type 6
4
Click OK. Your worksheet should look like the following illustration.
Repeating a Format The Repeat command: You can repeat your most recent action. The name of the Repeat command in the Edit menu changes to reflect your last action; for
Microsoft Excel
Revised 3/08/95
19
example, Repeat Column Width or Repeat Row Height. To use the Repeat command, the original command must have been chosen from a menu.
Repeat the row height for row 18 You want to set rows 18 to the same height as row 11. Since you just changed the row height for row 11, you'll see Repeat Row Height in the Edit menu. 1. 2.
Select a cell in row 18. From the Edit menu, select Repeat Row Height.
Formatting Numbers The Formatting Toolbar : With a selection and a click of an icon you can apply various formats to the information in your spreadsheet.
fonts & font size
bold, italic, & underline
text alignment
currency & numbers
borders & colors
Format a cell range for dollars Format the Budget Model Area to display dollar values in whole numbers, with dollar signs, commas separating thousands, and negative numbers in parentheses. 1.
Select cells C7:C35.
2.
On the Formatting Toolbar, click the Currency Style ($).
3.
Click twice on the Decrease Decimal tool to eliminate the trailing zeros.
Applying Range Formats Automatically Microsoft Excel has a number of built-in combinations of professionally designed formats that you can apply to any selected range. These formats are combinations of number formats, alignments, fonts, borders, colors, shading, and column widths and row heights. You can adjust any built-in format to
20
Virginia Tech Administrative Client Project
Spring 1995
retain any formatting you have already applied, such as column widths and number formats.
Apply a range format You will apply a range format designed for financial data. Since you have already applied the column widths, row heights, and number formats you want, you will choose not to apply those formats as part of the range format. 1.
Select cells B6:N35.
2.
From the Format menu, choose AutoFormat....
3.
Select Accounting 2.
4.
Click the Options button.
5.
Under Formats to Apply, clear both the Number and Width/Height check boxes.
6.
Click OK. Your worksheet should look like the following illustration.
Microsoft Excel
Revised 3/08/95
21
Displaying and Hiding Toolbars Microsoft Excel has a number of built-in toolbars. Each toolbar contains tools that perform a command or action when clicked. For example, you can save your workbook by clicking the Save tool on the Standard Toolbar and you have formatted cells using tools on the Formatting Toolbar To display other toolbars: 1.
From the View menu select Toolbars...
2.
Click those you wish to see (an X should appear next to them).
22
Virginia Tech Administrative Client Project
Spring 1995
3.
Click OK.
To find out what each tool does, point to it with the mouse and wait a second. A small box will appear with its name and a brief description of its purpose will appear in the Status Bar at the bottom of the screen.
Aligning Cell Entries The alignment tools on the Standard Toolbar are commonly used to format the contents of cells. left center right
center across columns
Center the month titles 1.
Select cells C6:N6.
2.
Click the Center Align tool on the Formatting toolbar.
Right-align text in the worksheet area labels Microsoft Excel
Revised 3/08/95
23
1.
Select cells A1:A5.
2.
Click the Right Align tool on the Standard toolbar.
3.
Select the column A heading boundary. Select AutoFit Selection from the Format and Column menus). Your worksheet should look like the following:
24
Virginia Tech Administrative Client Project
Spring 1995
Copying and Moving Cell Data Filling Adjacent Cells To copy into adjacent cells, you can drag the fill handle in the lower-right corner of the selection, as you did in an earlier lesson, to create a series of months, or use the Fill commands on the Edit menu. The fill handle and the Fill commands copy formulas, formats, or values from a range in a single row or column into an adjacent range with any number of rows or columns. You can copy in any direction.
Copy the figures from July to the other months 1.
Select cells C7:C35.
2.
Drag the fill handle to the right to cell N35.
3.
Scroll back to the native cell.
Your worksheet should look like the following illustration.
The formatting was also copied and the formulas with relative references are adjusted to the data for each month.
Microsoft Excel
Revised 3/08/95
25
Inserting Rows, Columns, and Cells You can insert an entire row or column by dragging the fill handle. You can also use the Insert command on the Edit menu to insert a cell, a row, or a column. The area you select before you choose the command determines what is inserted. Insert by dragging: When you select an entire row or column, a fill handle appears next to the row or column heading. To insert a row or column, press SHIFT and drag the fill handle down for rows or toward the right for columns, and select the number of rows or columns you want to insert.
Insert a column at column B 1.
Select column A. Remember, you can select a column by clicking the column heading or by selecting a cell in that column and pressing COMMAND+SPACEBAR.
2.
Press SHIFT and drag the fill handle toward the right to include column B. A new column is inserted at column B, shifting the other columns to the right. Your worksheet should look like the following illustration.
Deleting Rows, Columns, and Cells
26
Virginia Tech Administrative Client Project
Spring 1995
You can delete an entire row or column by dragging the fill handle. You can also use the Delete command on the Edit menu to delete a cell, row, or column. As with the Insert command, the area you select before you choose the command determines what is deleted. Remember that the Insert and Delete commands physically move cells. If you insert or delete a partial row or column, cells could become separated from their source or dependent data.
Delete Row 2 You want to move the figures closer to the worksheet title. Delete row 2 to move the rest of the worksheet up one row. 1.
Select row 2.
2.
Press SHIFT and drag the fill handle up until row 2 appears dimmed. All Cells below row 2 shift up one row.
Moving Cell Data Moving Cell data by dragging: You can move cells to another area of a worksheet by dragging the border surrounding the selection. The pointer changes to an arrow when it is positioned over the border. When you drag the border, the border moves to indicate the size and position of the selection.
Move the worksheet title and author one cell to the left Now move the worksheet title and author back to column B by dragging cells C1:C2. 1.
Select cells C1:C2.
2.
Drag the border of the selection to cells B1:B2.
Your worksheet should look like the following illustration.
Microsoft Excel
Revised 3/08/95
27
Move a formula Moving a formula is different from copying a formula. When you move a formula, the cell references still refer to the original cells. When you copy a formula, relative cell references adjust to the new location. 1.
Select cell D9.
2.
Drag the cell by the border to cell D4. The value of the formula appears in the cell. The formula, as displayed in the formula bar, did not change. It is still =SUM(D7:D8).
The Undo command: If you make a mistake or change your mind, you can reverse most commands and actions by choosing the Undo command from the Edit menu. The name of the Undo command changes to reflect your last action. For example, if you find that you incorrectly dragged data in the worksheet, you can choose the Undo Drag and Drop command, because dragging the data was the last action you performed.
Undo the last action To reverse your last editing change, you can use the Undo Drag and Drop command on the Edit menu. 1.
28
From the Edit menu, choose Undo Drag and Drop.
Virginia Tech Administrative Client Project
Spring 1995
Clearing Cell Entries The Clear command: With the Clear command, you can selectively clear specific cell attributes, such as formatting, or you can clear everything from the cell at once. To clear a cell, first select the cells, then select Clear from the Edit menu and drag to the sub-menu function you need.
Creating a New Workbook When you create a new workbook in Microsoft Excel, it is displayed in its own window. You can have many workbook windows open at the same time. The New command: You can use the new command from the File menu to create a new workbook. You will name the workbook when you save it. The New Workbook tool: You can also create a new workbook by clicking the New Workbook tool at the very left of the standard toolbar. 1.
Click the New tool or from the File menu select New.
Switching Between Windows You can only work on the workbook in the active window. You can tell which workbook is active by the dark title bar and the dark border. When you have more than one workbook open, you can switch to another workbook by choosing the workbook name from the bottom of the Window menu. All open windows are listed alphabetically and numbered at the bottom of the Window menu. The active window has a check mark next to its name. You can also switch between windows by using the mouse or the keyboard. With the mouse, move and size windows until the one you want is visible, and then click anywhere in the window. You can also press COMMAND+F6 to switch to the next window and COMMAND+SHIFT+F6 to switch to the previous window.
Switch to the BUDGET workbook window
Microsoft Excel
Revised 3/08/95
29
1.
From the Window menu, choose 2 BUDGET. The BUDGET window is now active. When workbook windows overlap, the active window is in front.
Lists, Charts, and Advanced Features With Excel you can create a row-by-column list of data that can be used like a database in which • •
each row is a record and each cell is a field in a record.
From the list you can create charts that display data graphically. Advanced functions (not covered here) like Pivot Tables allow more sophisticated manipulation and display of data and information.
Open the Lesson 1.
Open the LESSON3 workbook.
2.
Save the workbook as SALEHIST.
Your will create a chart that compares annual sales for the company with the sales of the entire industry from 1982 to 1992.
Creating a Chart You can embed a chart in your worksheet. To create a new chart, you first select the worksheet data you want to display in the chart, and then you create a new chart by clicking the ChartWizard tool or by choosing the New command from the File menu. The ChartWizard Tool: The ChartWizard tool displays the ChartWizard, an on-line assistant that guides you through the steps needed to embed a chart in your worksheet. Embedded charts are saved and printed with the worksheet and always reflect the latest worksheet data. You must have a mouse to use the ChartWizard tool.
Create a chart with the ChartWizard tool
30
Virginia Tech Administrative Client Project
Spring 1995
With your worksheet data selected, use the ChartWizard tool to create an embedded chart. 1.
Select the data for the chart by clicking in cell C10 and dragging to cell D20. Your screen should look like this:
2.
Click the ChartWizard tool. The mouse pointer turns into a cross hair with a little bar graph.
3.
Point to cell A22. Click and Drag (a dotted box will form) to cell G40. When you release the mouse button, Microsoft Excel displays the ChartWizard (step 1 of 5).
4.
The range of data that you selected is already entered, so choose the Next button to move to the next step.
The ChartWizard looks like the following illustration.
Microsoft Excel
Revised 3/08/95
31
Microsoft Excel has 9 two-dimensional (2-D) chart types and 6 threedimensional (3-D) chart types. 2-D chart types
3-D chart types
Area Bar Column Line Pie Doughnut Radar XY (scatter) Combination
3-D area 3-D bar 3-D column 3-D line 3-D pie 3-D surface
When you choose a chart type, a gallery appears showing the available formats for that chart type. Combination charts: Combination charts have two value axes so that you can compare trends. Pie Charts and xy charts: Use a pie chart when you have only one data series. An xy (scatter) chart is used to plot values when you expect to see a 32
Virginia Tech Administrative Client Project
Spring 1995
relationship between values in one data series and values in another data series. An xy chart has no categories. 3-D charts: Use three-dimensional (3-D) charts to show relationships between two categories of information and one set of values. A 3-D chart has an additional axis. The z-axis is the horizontal axis, the category labels are plotted along the x-axis, and the series names are plotted along the y-axis.
Change to a combination chart You can use a combination of two chart types to compare the trends in your data. 1.
Select the Combination chart type.
2.
Choose the Next button.
3.
With the combination chart gallery displayed, select format 2.
4.
Choose the Next button. Your chart appears in the Sample Chart box.
Data series and categories : When you create a new chart with the ChartWizard tool, you can classify your selected data into data series and categories. The categories are the labels that appear along the x-axis. The bars or markers of the same color or pattern represent a data series. In general, Microsoft Excel defines data series and categories according to the number of rows and columns in your worksheet selection. Unless you specify otherwise, Microsoft Excel assumes that you want fewer data series than categories. In this case, you will define the data in column D as the "Company" data series and the data in column E as the "Industry" data series. The selected years in column C will be categories.
Specify data series and categories 1.
Under Data Series In, select Rows option button. The sample chart now plots the differences among the years between company and industry. The legend changes to list the row labels.
Microsoft Excel
Revised 3/08/95
33
2.
Under Data Series In, select the Columns option button. The sample chart now plots the differences between company and industry over the years. The legend changes to list the column labels.
3.
Choose the Next button
Adding a legend: When you add a legend to your chart, Microsoft Excel automatically uses your data series names for the legend. You can add a legend while creating a chart with the ChartWizard. You can also add a legend with the Legend tool on the Chart toolbar or the Add Legend command on the Chart menu. By default, the legend appears to the right of the plot area. Deleting a legend: You can have only one chart legend. After you add a legend, the Add Legend command changes to the Delete Legend command. When you choose Delete Legend from the Chart menu to remove the legend, Add Legend appears in the menu again. Adding a chart title : You can add a title while creating the chart with the ChartWizard or by choosing the Attach Text command. In addition to chart titles, you can use this command to add titles to the axes and the data points. After you add the text, you can edit it in the formula bar. When you add a chart title or an axis title, the text is attached to a specific part of the chart and cannot be moved.
Add titles to the chart 1.
In the Chart Title box, type Company vs. Industry Wide Sales.
2.
In the Category (X) box, type Year.
3.
In the Value (Y) box, type Company.
4.
Click Finish.
Your chart should look like the following:
34
Virginia Tech Administrative Client Project
Spring 1995
Adding Gridlines The Chart toolbar: When an embedded chart is selected or a chart window is active, the Chart toolbar is displayed automatically. You can use the Chart toolbar to edit an existing chart or to create an embedded chart on the active worksheet. The chart toolbar contains tools for the most commonly used formats for each chart type and for common formatting tasks, such as adding a legend. The Horizontal Gridlines tool: With the Horizontal Gridlines tool on the Chart toolbar, you can change the display of major gridlines for the y-axis.
Add major gridlines to the y-axis 1.
Click the Horizontal Gridlines tool on the Chart toolbar.
Your chart should look like the following illustration.
Microsoft Excel
Revised 3/08/95
35
You can control the display of major and minor gridlines for both axes with the Gridlines command on the Chart menu.
36
Virginia Tech Administrative Client Project
Spring 1995
