CS11-Excel-EHO-001
12/05

Riverside Military Academy
Gainesville, Georgia

Basic Microsoft Excel

Instructor:  Major Salas

Requirement: All Computer Science1 students are required to know the concepts and principles presented in this document and in class.

Course Description: Microsoft Excel is the spreadsheet application tool in the Office Suite. Spreadsheets are reports that are used for business and financial applications that analyze data in a table format. Any data that needs analysis using formulas that can be arranged in a table should be in spreadsheet. .

Objectives: Upon completion of this course, you should be able to: 

1. Name the elements of the Excel worksheet screen and program.
2. Save, close or open an excel workbook
3. Efficiently add, delete, and move data in an Excel Workbook.
4. Use common formulas and function include finding the sum, average of totals number of items in a list, to perform calculation and analyze data in a worksheet.
5. Use formatting features that can help you change the appearance or a worksheet.  For example: Add borders, change the color of cells, use various fonts and change the alignment of data.
6. Prepare the worksheet, setup as needed, preview, and produce a paper copy of a worksheet you create.
7. Create colorful charts from worksheets data to visually display the data. 
8. Create graphics, such as auto shapes, text effects and text boxes, to enhance the appearance of a worksheet and illustrate important concepts. 
9. Create bar charts, pie charts, and plotted lines.
10. Use Excel tools that help you manage and analyze a large collection of data such as mailing list or product list. 
11. Sort or filter the data in a list.
12. Add subtotals to summarize the data.
13. Save a workbook you create as a web page. 
14. Add a hyperlink to workbook to connect the workbook to a web page. 

STUDENT HANDOUT

The following section is for your use in studying and practicing Excel. As you master and use the concepts taught in class or through self-discovery,  it's important to remember that practice, practice, practice is important to achieve the skill level many employers require you to have.  Prepare now for a bright future.

Note:  Some of the graphics in this document are over 600 pixels wide and you might have to scroll horizontally to see the entire image.  Some large graphics have also been scaled down to better fit the screen.  

This handout provides an introduction to Microsoft Excel, a popular spreadsheet application.  The document covers the Excel interface, opening and saving worksheets, entering and editing data, building formulas and functions, formatting, and printing your data.

Starting Excel

You can start Excel by:

1. Go to    then     and then  

2. You can also double-click on the icon of any existing Excel document. When you double-click an Excel document, Excel opens with the document already loaded.

Exploring the Excel Interface

Components of the Excel Window

Besides the usual window components (close box, title bar, scroll bars, etc.), an Excel window has several unique elements identified in the figure at the top of the next page.

Standard toolbar		The Standard toolbar, located beneath the menu bar, has buttons for commonly performed tasks like adding a column of numbers, printing, sorting, and other operations. Excel let’s you customize the toolbar or even display multiple toolbars at the same time. The Standard Excel 97 toolbar appears in the figure below.
Formatting toolbar		The Formatting toolbar, located beneath the Standard toolbar bar, has buttons for various formatting operations like changing text size or style, formatting numbers and placing borders around cells.
Formula bar		The formula bar is located beneath the toolbar at the top of the Excel worksheet. Use the formula bar to enter and edit worksheet data. The contents of the active cell always appear in the formula bar. When you click the mouse in the formula bar, an X and a check mark appear. You can click the check icon to confirm and completes editing, or the X to abandon editing.
Name box		The Name box displays the reference of the selected cells.
Row and column headings		Letters and numbers identify the rows and columns on an Excel spreadsheet. The intersection of a row and a column is called a cell. Use row and column headings to specify a cell's reference. For example, the cell located where column B and row 7 intersect is called B7.
Active cell		The active cell has a dark border around it to indicate your position in the worksheet. All text and numbers that you type are inserted into the active cell. Click the mouse on a cell to make it active.
Fill handle		The lower right corner of the active cell has a small box called a Fill Handle. Your mouse changes to a cross-hair when you are on the Fill Handle. The Fill Handle helps you copy data and create series of information. For example, if you type January in the active cell and then drag the Fill Handle over four cells, Excel automatically inserts February, March, April and May.
Worksheet tabs		An Excel workbook consists of multiple worksheets. Use the worksheet tabs at the bottom of the screen to navigate between worksheets within a workbook.

 

Entering and Editing Data

Entering Data

You can enter text, numbers and dates in an Excel worksheet. In Excel’s terminology, numbers and dates are called values and text is referred to as a label. To enter data of any type, click on the cell you want to contain that data, and then type the information you want in the cell. When you begin typing, your data also appears in the formula bar. When you have finished typing the data for the active cell, press the Return or Enter key.

Editing Data

The easiest way to edit the contents of a cell is to select the cell and then retype the entry. The new entry replaces the old contents. For example, to change the number in cell B6 to 199, select cell B6, type 199 and press Return. This method works well with numbers, but is more difficult when editing long text labels or formulas.

The formula bar gives you more flexibility while editing. When the mouse pointer moves into the formula bar, it changes shape to an I-beam, signifying that you can enter or edit text. Text in the active cell appears in the formula bar and you can edit it there. Use the mouse to select the text you want to change in the formula bar and then type the new text. Excel automatically replaces what is selected. Don't forget to press the Return key when you finish editing a cell.

You can also edit labels and values directly in cells. Double-click on the cell you want to edit. This puts an insertion point in the cell. Edit the contents of that cell the same way you would using the formula bar.

Moving the Active Cell

Cell selection and movement around the worksheet are similar operations in Excel. To select a given cell or make it active, simply click on that cell. Use the mouse or the arrow keys to move around the worksheet. For example, if you press the right arrow key twice you move two cells to the right. Refer to the table below for additional information on using the keyboard to navigate a worksheet.

 

Go To Command

A quick way to move a large distance on a worksheet is with the Go To command on the Edit menu. When you select the Go To command, a dialog box prompts you to identify the cell. Enter the cell reference and click OK or press Return.

Clearing Cell Contents

To clear the contents of a cell choose Clear from the Edit menu. Then, select what you want to clear from the cell: All, Formats, Contents, or Notes. Most frequently you will want to clear the Contents of a cell. Pressing the Delete key also clears the contents of cells.

Undoing Mistakes

If you make a terrible mistake—you accidentally delete important data, for example—use the Undo command on the Edit menu or the Undo tool immediately.

Copying and Moving Data

Copy selected data from one cell to another with the Copy and Paste commands, or with the Drag and Drop procedure. If you want to move data instead, use the Cut and Paste commands, or the Drag and Drop procedure. These commands and procedures are described below.

Cut

The Cut command extracts the selected data and puts it on the Clipboard, a temporary storage area. The contents of the Clipboard are overwritten with each copy or cut.

Copy

The Copy command puts a copy of the selected data on the Clipboard.

Paste

The Paste command inserts the Clipboard’s contents into the active cell. Selected data is replaced by pasted text.

Drag and Drop

This procedure does not involve the Clipboard and works best when moving data a short distance. To move cells, position the mouse on the cell borders. Wait until the mouse changes to a left-pointing arrow. When the mouse is this shape, press and drag the data to the new location.

Working with Excel documents

Opening and Closing Documents

To open a file, select Open from the File menu. In the Open dialog box, select the file you want and then click the Open button. To close a document, select Close from the File menu.

Saving a Document

Changes you make to a document are not saved to disk until you issue a save command. Saving is quick and easy so you should save often to minimize the loss of your work. Excel has two save commands— Save and Save As — that work similarly. Both commands are on the File menu.

Save

When you save a new worksheet for the first time, Excel displays a dialog box similar to the Open dialog. Select the disk in which to save the worksheet and specify a name for the file. When you save an existing worksheet that you have been editing, the newly saved version is written over the older version.

Save As.

This command always displays a dialog box where you can choose a document name and disk. Use the Save As command whenever you want to save a copy of the current document with a different name or in a different folder or disk. The newly saved copy becomes the active document.

Creating a New Workbook

If you are already in Excel and you want to create a new workbook, choose New from the File menu.

Managing Multiple Worksheets

Excel enables you to have more than one workbook open simultaneously. Each open workbook appears on the Window menu. The document with the check next to it is the active document. To switch to another document, simply choose that document from the Window menu.

To navigate between worksheets within a workbook, click the worksheet tab you want to activate. Double-click a worksheet tab to change its name.

Getting Help

To activate the Excel Help System, select Contents and Index from the Help menu. At the top of the Help dialog box, click either Contents, Index, or Find to search the Help System. To read the information about a highlighted topic, double-click on it. The Help system automatically switches to information about the selected topic. If subtopics are available, they are highlighted also.

 

 

Help on the Web

Choose Microsoft on the Web from the Help menu to find additional help, FAQs, and free downloads for Microsoft Excel.

Formulas and Functions

Formulas and functions that perform calculations are the true power of spreadsheets. This section describes how to construct formulas and functions on an Excel worksheet.

Formulas

To build a formula, first select the cell in which you want the results to appear. In Excel, all formulas start with the = sign. After the = sign, type the cells you want to add or subtract along with the mathematical operation you wish to perform. For example, to add the January sales in the worksheet below, the formula would be =B6+B7+B8. If you want to subtract February Widget Sales from January Widget sales, the formula would be =C6-B6.

 

Building many formulas can quickly become tedious. Fortunately, the Copy command described earlier also works with formulas.

Copying Formulas

The easiest way to copy a formula is with the Fill Handle in the lower right corner of the cell. Create your initial formula and then position the mouse on the Fill Handle. When the mouse changes shape to a cross-hair, press and drag over the adjacent cells you want to copy the formula to.

Auto Sum Button

In Excel, the standard toolbar has a button that automatically adds a column or row of numbers. The Auto Sum button, which looks like the Greek letter Sigma, (shown at right) automatically creates a SUM( ) function. When you click the Auto Sum button, Excel creates a sum function for the column of numbers directly above or the row of numbers to the left of the active cell. Excel pastes the SUM( ) function and the range to sum into the formula bar. If the range is not correct, simply select the proper range with your mouse on the worksheet. When you have entered the correct range, press the Return key to complete the function.

Functions

As you saw above, formulas can be very useful on a worksheet. However, what if you want to add up a column of 10 numbers? Do you have to click on ten cells or type ten cell references in a formula? Excel has a more efficient means of dealing with this situation by using functions.

The SUM( ) function is probably the most common function in Excel. It adds a range of numbers. To build a SUM( ) function, begin by typing =SUM(. Next, tell Excel which cells to sum. Using the mouse, press and drag over the range of cells you wish to add. A dotted outline appears around the cells, and Excel displays the cell range in the formula bar. When you have the correct cells selected, release the mouse button, close the parenthesis, and press the Return key.

If you do not want to use the mouse, type in the cells you want Excel to sum. For example, to sum cells B6 through B8, type =SUM(B6:B8). Excel interprets B6:B8 as the range of cells from B6 to B8.

Using the Paste Function tool

Excel has many more functions besides the SUM() function described above. For example, you might want to calculate the average of a column of numbers, or count how many entries are in a row. You can get help with these functions and hundreds more by using Excel’s Paste Function tool.

The Paste Function tool is located on the Standard toolbar. Click the tool (shown at right) to activate the dialog box. First, choose the Function Category you are interested in and then select the function you want in that category. When you have selected the proper function click OK to move to Step 2. In second dialog box, specify the cells the function will operate on, called its arguments. Select the cells with the mouse and click OK. Notice the creation of the function in the formula bar.

 

Formatting Data

Excel has a Formatting toolbar to simplify basic formatting tasks. The Formatting toolbar appears in the figure below.

Changing Numeric Formatting

To change the format of a number, choose the Cells command from the Format menu. In the Format Cells dialog box, Excel displays different tabs for various formatting types. To change numeric formats, click the Number tab. Select the category you want and then the actual format. For example, to display numbers as currency with two decimal places, select the Currency category, enter 2 for the number of decimal places, and select the appropriate currency symbol. Or to display a number as a percentage, choose the Percentage category and select the number of decimals you want to display.

Changing Text Style and Fonts

You can easily change text fonts and styles on the worksheet also. Use buttons on the toolbar or the Font tab in the Format Cells dialog box to make changes. The Preview area in the dialog box shows a sample of the selected font and style before you click the OK button.

Alignment

By default, Excel left aligns labels and right aligns values in a worksheet. You can change cell alignment to enhance the readability of a worksheet.

The toolbar has text alignment icons next to the bold and italic icons . You can left align, right align, or center text within a cell using these buttons. Select the cell(s) you want to align and click the appropriate alignment button on the toolbar.

The toolbar also has a button (shown at right) that will center a label over a range of cells, for example centering a title over a report. To center data over a range of cells, select the cell you want to center, and the columns you want to center it over, and click the Center over Cells button.

Placing Borders around Cells

Use the Borders button on the toolbar to place borders around cells. The Border tab in the Format Cells dialog box provides greater flexibility in creating borders.

Changing Column Widths

Change column widths by dragging column borders with the mouse. Move the mouse pointer to the right border of a column heading until the mouse pointer changes shape to a left and right pointing arrow. When the mouse pointer changes shape, click and drag the mouse to adjust the column width. Note that when you are adjusting the width in this way, a numeric width indicator appears in the upper left part of the formula bar.

Page Setup, Previewing and Printing

Before you actually print a worksheet, you should provide Excel information about margins, headers, footers, and page orientation. You change these settings using the Page Setup option on the File menu.

Page Setup

The Page Setup dialog box, shown below, has controls for margins, page orientation, headers and footers and whether gridlines and row and column headings should be printed.

Setting Page Margins

To change your margins, click the Margins tab, select the correct margin entry box and enter the measurement for the margin in inches.

Changing Page Orientation

If you want to change the orientation of the page from portrait (vertical) to landscape (horizontal) click the appropriate button in the Orientation section of the Page tab.

Header and Footers

A header is text that prints at the top of each page. A footer is text that prints at the bottom of each page. Headers and footers often include the name of the file being printed or an alternative title, the date, page number, or additional descriptive information. To change the header or footer, click on the Header/Footer tab in the Page Setup dialog box. To change the header, click Custom Header and enter the desired text in each section of the header. You can create a custom footer similarly.

Turning Off Gridlines and Row and Column Headings

Although row and column headings and gridlines are helpful on the screen, these additions are rarely necessary in printed output and may even detract from it. Choose the Sheet tab and click the check boxes for Row and Column Headings and Gridlines to turn these features off.

Previewing

Before printing, preview your output by selecting Print Preview from the File menu. When in Print Preview, Excel displays how the document will print on the page, but it is difficult to actually read the text. Notice that the mouse pointer takes the shape of a magnifying glass. You can enlarge the printed image by clicking the Zoom button or by using the magnifying glass. Simply click the magnifying glass on a part of the page you want to enlarge.

The Print Preview screen also has several buttons at the top of the screen that enable you to make adjustments. For example, the Setup button opens the Page Setup dialog box and the Margins button lets you change page margins and column widths to fit more information on one page.

If you are satisfied with the appearance of your document in the Print Preview screen, the Print button lets you send your output directly to the printer.

Excel also has Page Break Preview on the View menu that lets you see your page breaks and change them by dragging borders. Choose View and Page Break Preview to display this mode.

Printing

To print your worksheet, choose Print from the File menu, or click the Print button from the Print Preview screen. This displays a dialog box that lets you change print settings and specify the number of copies to print. You should also indicate whether you want to print the active worksheet, the selected cells only, or the whole workbook.

 

Review and Summary

 

Major Hector Salas

Riverside Military Academy