Excel LABS 2016-17 MNG2200 PDF

Preview

Summary

Download Excel LABS 2016-17 MNG2200 PDF

Description

MS EXCEL LABS Please make sure you have a USB stick for the course. You are strongly encouraged to work ahead. IMPORTANT: IF YOU MISS A SCHEDULED LAB, IT IS YOUR RESPONSIBILITY TO CATCH UP! LABS WILL PROCEED AS PER THE WEEKS AND EXERCISES IN THIS HANDOUT.

EXERCISE I (WEEK 1) The figure below points out the main features of the Excel window.

The following list describes each of the features:     



Ribbon: This is the multi-tabbed toolbar system that contains buttons and other controls for issuing commands. Microsoft Office Button: Opens the only real "menu" in Excel 2007 -- the Office menu -- which contains commands for saving, opening, and printing files. Tabs: A tab is an individual page of the Ribbon. For example, the Home tab. Groups: Each Ribbon tab has named sections called groups. For example, the Home tab has the following groups: Clipboard, Font, Alignment etc. Quick Access Toolbar: This is a highly customizable portion of the interface; you can place shortcuts to your favourite buttons and commands here. By default, it contains Save, Undo, and Redo buttons. Insert Function button: You can click this button to get help creating functions.

 

Formula bar: This is where the contents of a cell appear and where you can type or edit those contents. Column headers: Each column's letter is a header for that column. You can click the column letter to select the entire column.

1



    

Microsoft Office Excel Help button: You can click this (?) button to open the Excel Help system, from which you can look up any topic. This is an invaluable tool, and you are encouraged to use it for problem solving and self-learning. Scroll bars: You can use these to scroll within the active worksheet. Zoom controls: These buttons, and the slider, control the magnification at which you view the active worksheet. Insert Worksheet tab: You can click this tab to insert another worksheet. Worksheet tabs: You can click one of these tabs to switch between worksheets. Row headers: Each row's number is a header for that row. You can click the row number to select the entire row.

Notice that the main part of the spreadsheet is composed of Rows (Labeled 1, 2, 3, 4, etc.) and Columns (Labeled A, B, C, D, etc.). There are hundreds of columns and thousands of rows in a spreadsheet. The intersection of each row and column is called a cell. In the image above, the cursor is on the “home” cell – A1. Notice Row 1 and Column A are bold, and colored orange. Notice right above cell A1, that A1 is displayed in a small box called the Name Box. This indicates what is called the “address of the cell. Whenever you click on a cell the address of that cell will be shown in the Name Box. If you have used previous versions of Microsoft Excel you will quickly notice that the above image is very different from what you are used to seeing. In Excel 2007 you will now use Tabs, Ribbons and Groups, as well as special Tabs/Ribbons. These replace the Menu Bar and Tool bars in older versions.

In this tutorial, whenever we indicate that you need to click the mouse, it will mean to click the left mouse button – unless it is indicated that you should click the RIGHT mouse button. So, always “left click” unless you are told otherwise. The general setup of the Excel window is similar to that of a WORD window. You will notice however, the new bar in the EXCEL window – namely the Formula bar, that was not there in the WORD window.

Using an Excel worksheet - Basic terminology Worksheets – An Excel file is called a workbook. An Excel workbook is made up of several sheets. Excel starts you off with three sheets. You can add more sheets when you have used up the first three.

Cells - Rectangles in a spreadsheet are called cells. Cells are designated by the column and row in which it is located. By default, the top left cell, A1, is highlighted.

You can move around the spreadsheet/cells by clicking your mouse on various cells, or by using the up, down, right and left arrow movement keys on the keyboard. Or, you can move up and down by using the scroll bars on the right and bottom of the spreadsheet.

Recognizing cursor styles - There are four common cursor styles used in Excel. 2

Click and drag to highlight multiple cells with this cursor, or click in a cell to select the single cell

Click and drag with this cursor to fill cell contents into cells below or to the right.

Click and drag the contents of the selected cell to any other cell.

Click to place the cursor into the Formula bar so that you can edit an equation or function.

Entering data - Move to the cell where you want to enter data and enter words or numbers. If data is already in the cell it will be replaced without having to cut or delete the previous data. Editing entered data - If incorrect information has been entered into a cell, one easy way to edit that is to click on the cell and enter the correct information. You do not need to highlight or delete. Typing replaces whatever is in the cell. If you do not want to replace the data, but simply need to correct some part of it, move your cursor into the Formula bar

When your cursor turns into an I-Beam, click and edit within the formula bar. Excel’s Delete vs. Clear Options Excel provides you with two options that are similar but differ in an important way: “Delete” and “Clear”. If you select a cell and press the delete key, it will erase the contents of the cell. However, any formatting options that you may have applied to the cell are NOT erased. For eg., shading, borders, percentage, bold etc. If you wish to remove the formatting options you have applied to the cell, click on Clear – Clear Formats from the Editing group on the Home tab of the Ribbon. Formulas Formulas are equations that perform calculations on values in your worksheet. A formula starts with an equal sign (=). For example, the following formula multiplies 2 by 3 and then adds 5 to the result. =5+2*3 A formula can also contain any or all of the following: functions, cell addresses, constants and operators. A function is a built-in formula that takes a value or values, performs an operation, and returns a value or values. A constant is a value that does not change. An operator specifies the type of calculation to perform within an expression. There are mathematical, comparison, and logical operators.

Working with multiple Worksheets A single data file (a workbook) can have many tabbed sheets in it. Each sheet is its own separate row-and-column grid.

Changing the name of the worksheets - The name of the sheet may be changed by doubleclicking the name of the sheet in the tab at the bottom, and typing the new name. Renaming the worksheet can also be accomplished by right-clicking the tab containing the sheet name and clicking on Rename.

Inserting additional worksheets - If you need additional worksheets, you can insert a worksheet by right-clicking on a sheet tab and selecting Insert...

3

You can also add a new worksheet by clicking the Insert Worksheet tab to the right of the existing sheet tabs.

Deleting worksheets - To delete a sheet, right-click its tab, and then select Delete. If anything's on that sheet, a warning box appears; if the sheet was empty, it just goes away. (NOTE: Undo does NOT work on sheet deletions.) Scrolling through Sheet tabs – When you have a lot of worksheets, you may not be able to see all the worksheet tabs at once. You can use the arrow buttons at the left of the tabs to scroll through the worksheet tabs. Hiding and Redisplaying Sheets - To hide a sheet, right-click its tab, and then select Hide. To redisplay a hidden sheet, right-click any of the displayed tabs, and then select Unhide. In the Unhide dialog box, select the sheet to unhide, and then click OK.

Changing a sheet tab color - All tabs start out white by default; however, you can color-code your workbook by changing the tab colors. To select a tab color, right-click the tab, and then select Tab Color. On the palette of colors that appears, select the color you want.

Copying and Moving Sheets - To rearrange the sheet order, drag a sheet's tab to the right or left. If you want to copy a sheet rather than move it, hold down the Ctrl key as you drag it. Another way to move or copy a sheet is with the Move or Copy command. It's more powerful than the drag-and-drop method because you can use it to move or copy a sheet from one workbook to another if desired. To use this command, just select one or more sheets, right-click a selected sheet tab, and then select Move or Copy from the shortcut menu. (To select multiple sheets, hold down the Ctrl key as you click each tab, or click the first tab in a contiguous range, and then hold down Shift while clicking the last tab.) The Move or Copy dialog box opens, in which you can select a destination workbook and a sheet position within that workbook.

Exercise 1a: Sheet Handling 1. 2. 3. 4. 5. 6. 7.

Start a new blank workbook. Save the workbook as File1. Rename the sheets as Sales, Marketing, and Accounting. Add a new sheet at the end. Name this new sheet as Operations. Delete the Marketing sheet. Hide the Operations sheet and the Accounting sheet. Redisplay the Operations sheet and the Accounting sheet. Change the Operations sheet's tab color to red. Set the Sales tab to yellow and set the Accounting tab to blue. 8. Use the drag-and-drop method to move the Operations sheet to the leftmost position as the very first sheet. 9. Use the Move or Copy feature to create another copy of the Sales sheet to the right of the original, and then rename the copy to Marketing. Change the color of the new copy's tab to green. 10. Use the Move or Copy feature to copy the Accounting sheet into a new workbook. 11. Save the new workbook as File2. 12. Use the Move or Copy feature to move the Sales sheet from File 1 to the File 2 workbook. 13. Click the Insert Sheet tab to add 10 new worksheets to the File 2 workbook. Practice scrolling through the worksheets using the arrow buttons to the left of the sheet tabs. 14. Save and close the workbooks.

Exercise 1b:

In a new workbook, set up the spreadsheet as shown below (DO NOT type the letters A, B,…. They are there to indicate the column headings under which data is to be entered). Use the Arrow Keys on your keyboard to move through the spreadsheet. Note that the Label entries are by default aligned with the left margin of the cell, while the numeric entries are by default aligned with the right margin of the cell. 4

A NAME John Henry Kathy Simon Berry

B TEST 1

C TEST 2 25 20 22 25 25

D TEST 3 30 40 45 25 30

E TOTAL 12 15 18 20 15

Now follow the given instructions: 1. Move your mouse pointer to cell A3 of your worksheet and select it by single clicking using the left button of your mouse. Note how the cell A3 becomes the active cell, indicated by the dark border around it. Note also that the contents of the cell appear in the Formula bar, in this case, Henry. The address of the cell, A3, appears in the Name Box to the left of the Formula Bar. NOTE: Selecting a cell involves clicking the mouse button on the desired cell or by moving to the desired cell using the arrow keys on your keyboard. Once a cell is selected, it becomes the active cell and (you should see the dark border around it) you can then type in some data into the cell or make modifications to the existing data in the cell. 2. Now move your mouse pointer to cell C6 and select it. Note the new contents of the Formula Bar and the Name Box. 3. Select the range of cells C1:C4 by dragging the mouse pointer across these cells. 4. Select the range of cells D2:D5. 5. Now select the range of cells B3:D5. So you see how you can select either single cells or a group of cells, called a range. 6. Change the label entry TOTAL to TOTAL MARKS. You can do this in one of 2 ways: a) Double click in the cell E1. You will see the cursor in the cell, indicating that you can edit the contents. Now, as in WORD, simply position the cursor after the word TOTAL and type in the required text. You can erase characters by using the Delete or Backspace keys on your keyboard, or insert text in the position shown by the cursor. Press the ENTER key when you have finished editing the cell. OR b) Single click the cell E1. Click in the Formula Bar, and position the cursor in the appropriate location for editing. Press the ENTER key when you have finished making the changes. 7. Increase the width of the column to accommodate the change in a). To do this move the mouse pointer to the right vertical edge of the column heading for the column whose width you want to change. You will notice that the pointer changes shape to a double line with double arrows. Drag the pointer either left or right to decrease or increase the column width respectively. 8. Put in a formula =B2+C2+D2 for TOTAL MARKS in cell E2. Note how the formula shows up in the Formula bar, and the result of the calculation appears in the cell. NOTE: You must use the = sign in front of a formula. If you forget to do this, the entry will be treated as a Label entry, and you will not see the calculated result in the cell. (You are NOT to manually calculate the totals and type the results into the cells) Similarly you can put in formulas for TOTAL MARKS in cells E3 through to E6, carefully noting the cell addresses. However, instead of typing in the formulas for each row, which can be very tedious, we will learn to copy the formula to the other cells. Of course, this can be done only if the basic structure of the formula is the same for all the other rows. Before copying the formula to the other cells, you should verify the result in the cell where you have typed in the formula. Since this is the formula that you are going to be copying to the other

cells, if there is an error in this formula, this error will be carried across to all the other cells to which you are copying it. (GIGO – Garbage In Garbage Out) Having satisfied yourself that the original formula is correct, you can now proceed to copy it to the other cells. You can do this in one of 2 ways. Try both the ways given. Do it the first way, 5

and then, delete cells E3:E6, using the Delete key on your keyboard, and then do it the second way. Method #1: Select cell E2 – that is, the cell containing the formula that you are going to copy to the other cells. Click on the Copy button in the Standard toolbar. Next, select the range of cells that you want to copy the formula to – E3:E6. Click on the Paste button in the Standard toolbar. (This method is useful when the location you are copying to is not adjacent to the cell containing the original formula). Press the Esc key on your keyboard to get out of the Copy operation. Method #2: Select cell E2. Move your mouse pointer to the lower right hand corner of the cell. You will see your mouse pointer change to the + shape. At this point, drag your mouse pointer down the cells that you are copying to – E3:E6. Release the mouse button. Presto! The results appear as if by magic in all the cells that you selected! (This method is useful when the location you are copying to is adjacent (vertically/horizontally) to the cell containing the original formula). 9. Now change Simon’s TEST 2 mark to 40 and note that the TOTAL MARKS for Simon are automatically recalculated. (You can delete the contents of a cell by simply selecting the cell and typing in the new contents (this will overwrite the previous contents of the cell), OR by selecting the cell and erasing the contents of the cell by pressing the DELETE key, and then typing in the new contents.) 10. Name the worksheet tab Ex1. Do this by double clicking the left mouse button on the sheet tab that is highlighted and typing in the name you are giving to the worksheet. Press the ENTER key on your keyboard when you are done. NOTE that naming a worksheet is different from naming a workbook. A workbook is the file that can hold any number of worksheets, each of which has a different name. However, a workbook has only one name – the file name given to it that appears in the Title Bar. 11. Color the Tab. To do this, right click on the tab for the desired worksheet. Click Tab Color. Choose the desired color. Click OK. 12. Save (using SAVE AS) your workbook (file) to your storage medium under the filename YOUR NAME EXCEL LABS. NOTE: During the course of this training, you should type each Exercise that you do in a separate worksheet, but in the same workbook EXCEL LABS. Therefore, you should have only one file YOUR NAME EXCEL LABS, and you would access the different exercises in the different worksheets by simply clicking on the appropriate sheet tabs. *************************************

EXERCISE 2 (WEEK 1) Set up the worksheet in sheet 2 of your workbook YOUR NAME EXCEL LABS as shown below. A EMPLOYEE NAME

B GROSS PAY

C TAXES

D INSURANCE

Gary Smith

23000

5000

1000

Karen King

35000

7000

1400

John Adams

28000

5300

1200

Quincy Jones

40000

7500

1650

Carla Edwards

30000

6500

1100

Now follow the instructions given:

E NET PAY

1. Type in the formula for Net Pay for Gary Smith. Hint: NET PAY = GROSS PAY – (the sum of TAXES and INSURANCE.). NOTE the proper use of brackets in the formula. 2. Copy the formula in cell E2 to cells E3:E6. 6

3. Let us now format the number cells to display the $ sign in front of the numbers. Let us start with cell B2. Make this cell the active cell. Then, on the Home tab of the Ribbon, click on the $ sign on the Number group. You should now see $ 23,000.00 displayed in your cell. Increase or decrease the number of decimal places displayed by clicking on the Increase decimal or Decrease decimal button on the Number group. In this exercise, we will format the cell to show no decimal places. 4. As in Step 2 above, format cells C2 and D2 to show the $ sign with no decimal places. 5. You can see that formatting each cell in the worksheet like this can be quite tedious work. We can do it more efficiently by selecting at one go all the cells that we want to apply this format to, and then applying the desired formats. To do this, select cells B2:E6, and then apply the $ format with 0 decimal places. Presto! All the cells have been set to the desired format at one go! If you have large numbers that are too wide for the current column width, you will see some ######## in the cells where these numbers are located. If this occurs in your spreadsheet, go ahead and widen the columns. 6. GROSS PAY We will wrap the text GROSS PAY in cell B1 so that it appears as shown above. To do this, first decrease the column width for the cell so that it is less than what is required for the cell entry. Then, from the Home tab, use the Wrap Text feature in the Alignment group to display the label GROSS PAY as shown above. NOTE: this feature will not work if your column is wide enough to hold the text. 7. We will now insert a new row in between the rows for Karen King and John Adams. To do this, first click in cell A4 corresponding to the row for John Adams. Then, from the Cells group of the Home tab, click on Insert – Insert Sheet Rows. You will notice that the row for John Adams moves down one, becoming the 5th row, and a new 4th row is inserted above it. 8. Insert the following data in the new row. Michel Parry 30000 Pay for Michel.

6500 1100. Calculate the Net

9. Now format all the Label entries in the first row to Bold. Again, you can either do this the hard way, by selecting each cell individually, and making it Bold, or by selecting the range of cells, and then making them Bold...