# spreadsheet concepts using microsoft excel

Post on 03-Jan-2017

228 views

Embed Size (px)

TRANSCRIPT

Spreadsheet ConceptsUsing Microsoft Excel

l a b

5

99

Objectives:Upon successful completion of Lab 5, you will be able to

Create and edit a simple spreadsheet document Describe the advantage of using formulas rather than entering values only Use copy, cut, and paste to copy and move data including formulas Use the built-in function sum View formulas Format data in cells including font, bold, underline, italic, and shading Print a spreadsheet and adjust the fit

Resources required: A computer running Excel 2007

Starter files: None

Prerequisite skills: General keyboarding skills; familiarity with editing keys such as Delete,

Backspace, Shift, Caps Lock, and Arrow keys Ability to find files using Windows Explorer or Windows search feature Ability to open and save a file in a Windows application

NRCs Top Ten Skills, Concepts, and Capabilities: Skills

Use a spreadsheet to model a simple process Data entry Formulas using relative cell references Formatting Printing Simple built-in functions (average, sum)

ConceptsModeling and abstraction

CapabilitiesEngage in sustained reasoningThink abstractly about Information Technologybuilding genericelectronic spreadsheet concepts

lab05_p99-124 8/22/07 9:29 AM Page 99

Lab Lesson

If youve ever had to manage a household budget, track your investments, or even manage avolunteer fundraiser, then you will see the advantages offered by an electronic spreadsheetprogram. A spreadsheet program can also be used for tasks that are not financial. It can be used toorganize any kind of list, such as listing names and phone numbers, tracking a sports teamsstatistics, or managing the volunteer schedule at a nursery school. Information that would beorganized into rows and columns can be managed easily using a spreadsheet program. We willuse the spreadsheet program Excel to create a household budget spreadsheet.

Open Excel.

The Ribbon can be minimized to provide more room in the Excel window. Lets minimize theRibbon and display it again. It may be minimized in your window by default.

Right-click on the menu to display the short-cut menu as shown in Figure 5.1.

The menu is shown in Figure 5.2.

Figure 5.1 Excel menu short-cut menu.

Click the menu item Minimize the Ribbon. If the Ribbon was visible before,it is now hidden. If the Ribbon was hidden, it is now visible.

Ensure the Ribbon is visible, as shown in Figure 5.2. If it is hidden, use theabove method to reveal it.

The Excel window contains a grid, toolbars, and sheet tabs as shown in Figure 5.2.

100 Computer Skills Workbook for Fluency with Information Technology, Third Edition

lab05_p99-124 8/22/07 9:29 AM Page 100

Figure 5.2 The Excel window.

The available worksheet area is quite large. The rectangular areas are called cells. There are1,048,576 rows and 16,384 columns of cells available in each sheet. We will use only part of onesheet. Lets scroll to get a sense of the size of the work area.

Click the left, right, up, and down scroll arrows on the right edge of thewindow as shown in Figure 5.2.

As you click the down scroll arrow you will notice the row numbers increasing. As you click theright scroll arrow, you will notice the column letters scrolling. After the alphabet has expired, thecounting begins again AA, AB, AC, . . . until the last column, XFD.

Press and hold the Ctrl key while you tap the Home key to return to cell A1.

Moving around the Worksheet

Move the mouse pointer to cell D5.Click the left mouse button.

Lab 5: Spreadsheet Concepts Using Microsoft Excel 101

lab05_p99-124 8/22/07 9:29 AM Page 101

Notice that the cell is now outlined in bold. Only one cell will be outlined in bold. This indicatesthe active cell. You have activated cell D5 by clicking it.

The sheet tabs at the bottom of the window indicate different worksheets of the Excel workbook.Each of these sheets also contains 1,048,576 rows and 16,384 columns of cells. To activate adifferent sheet, click the appropriate tab.

Click the tab labeled Sheet2.

Now you have activated the Sheet2 worksheet.

Click the Sheet1 tab to activate Sheet1 again.

Now, lets begin entering data. There are essentially three types of data: labels, values, andformulas. A label is text such as a title, the name of a month, or a street address. A value is anumber and a formula is some kind of calculation.

Data Entry

Activate cell A1.Type: Hello!

While you type this label, several things are happening on the screen. You may notice that thelabel seems to appear in two places simultaneously. It appears in cell A1 and it also appears onthe formula bar. Some symbols have also appeared on the formula bar as shown in Figure 5.3.

Figure 5.3 Excel formula bar.

Click the Cancel button on the Formula Bar as shown in Figure 5.3.

Notice that the input Hello! has disappeared. You can cancel any input before it has beenentered by clicking the Cancel button.

Type: Hello!Click the Enter button on the Formula Bar as shown in Figure 5.3.

This button enters the data in the cell. Notice that the buttons on the Formula bar havedisappeared. Similarly, you could have simply pressed the Enter key. The Insert Function buttonis used to select a formula; we will examine this later.

Lets assume the cell entry is incorrect and we wish to erase it from the cell.

Make sure that A1 is the active cell. If not, click cell A1 to activate it.

102 Computer Skills Workbook for Fluency with Information Technology, Third Edition

lab05_p99-124 8/22/07 9:29 AM Page 102

Press the Delete key on the keyboard. Notice that the cell entry has beenerased. There are other methods of deleting cell contents, but pressing the Deletekey is quick and intuitive.

We can also make changes to the contents of a cell after it has been entered.

Activate cell A1 if it is not already active.Type: Welcome to spreadsheet computing!Press the Enter key on the keyboard.

The text should be entered into the cell as shown in Figure 5.4.

Figure 5.4 Excel text entered.

You should notice a few things. First, since this label is longer than the width of the column, itscrolls onto adjacent blank cells. Column widths can be enlarged to accommodate data so cellsB1, C1, and D1 could still contain data later and column A could be enlarged. Second, afterpressing the Enter key, the active cell is now A2. After data is entered, the new active cell willusually be the cell below the entry.

Lets edit the contents of cell A1.

Double-click cell A1. Notice that the flashing insertion point appears in cellA1. You can edit the contents of a cell directly in the cell or in the Formula Bar.Lets replace the words spreadsheet computing with Microsoft Excel.

Use your editing skills to delete the words spreadsheet computing andreplace it with Microsoft Excel.

Press the Enter key or click the Enter button on the Formula Bar to completethe cell entry.

Entering values is as easy as entering labels.

Activate cell A2.Type: 3,456.78Press the Enter key or click the Enter button on the Formula Bar.

Notice that the comma separator has been accepted and the value is right-aligned in the cell.

Activate cell A3 if it is not already active.Type: 123.456Press the Enter key or click the Enter button on the Formula Bar.

Notice that the value has been entered in the cell. Notice also that the decimal places are differentand not aligned. We will see later how formatting the cells can improve this situation.

Lab 5: Spreadsheet Concepts Using Microsoft Excel 103

lab05_p99-124 8/22/07 9:29 AM Page 103

Entering a Formula

The power of the spreadsheet application is the ability to perform calculations using formulas.Lets create a formula that adds the contents of cells A2 and A3.

Activate cell A4 if it is not already active.Type: =A2+A3

Notice that the formula appears in the cell and in the Formula Bar as shown in Figure 5.5. Also,the cells are color coded corresponding to the formula in cell A4.

Figure 5.5 Excel entering a formula.

Press the Enter key or click the Enter button on the Formula Bar.Activate cell A4.

Notice that the formula appears in the Formula Bar, but the result of the formula appears in cellA5, as shown in Figure 5.6.

Figure 5.6 Excel formula results.

The power of a formula is in the cell references. Because the cell references are used in theformula, Excel updates the results when contents of these cells change.

Activate cell A3.Type: 1,000Press the Enter key or click the Enter button on the Formula Bar.

Notice that the results in cell A4 have now changed to reflect the new data. Excel formulas alwaysbegin with an equals = sign. In our example, =A2+A3, the plus sign is called an operator. Excelformulas can contain the following operators:

104 Computer Skills Workbook for Fluency with Information Technology, Third Edition

lab05_p99-124 8/22/07 9:29 AM Page 104

Operator Description

^ (Caret symbol) Exponentiation

* (Asterisk symbol) Multiplication

/ (Slash symbol) Division

+ (Plus sign) Addition

(Dash or minus sign) Subtraction

When we create a more complicated formula, we can use these operators and we can also use theparentheses ( ). Excel will follow the order of operations for mathematics when calculatingformulas.

Lets start by entering some of the data into specific cells, as shown in Figure 5.7.

Delete the contents of cells A1, A2, A3, and A4. You can select each cell andpress the Delete key to delete the contents.

Enter the data shown in Figure 5.7.