MICROSOFT EXCEL—TUTORIAL #2



MICROSOFT EXCEL—TUTORIAL #2Open “TUTORIAL.xlsx” - the file you completed from TUTORIAL #1. (It is posted online if you were absent)BUILT-IN FUNCTIONSMove to cell C7.Type Average:Now enter the following:C8: Highest:C9: Lowest:C10: # of Tests:Excel provides built-in formulas called functions to perform special calculations (max, min, count, sum, average).In cell D7, type the formula: =average(d2:d5). Press Enter.In cell D8, type the formula: =max(d2:d5). Press Enter.NOTE: max finds the highest value in a range of cells.In cell D9, type the formula: =min(d2:d5).NOTE: min finds the lowest value in a range of cells.NOTE: instead of typing d2:d5, you can highlight these cells.In cell D10, type: =count( then highlight cells D2 to D5, then type )NOTE: The count function tells you the number of items. Here, it is the number of tests.NOTE: Use the AutoSum button on the home ribbon to quickly total numbers.Now, answer questions 1 to 4 on your question sheet.FIXING IT UP: The following six changes make the spreadsheet look better.CHANGING NUMBER FORMATHighlight cells D2 to D9.Select % from Number Group in the Home Ribbon.When you select % on the toolbar, it is automatically set to 0 decimal places.To change the number of decimal places, click the increase decimal button (increase it to 2)NOTE: You can also change the number format of a group of highlighted cells by right clicking and selecting format cells.NOTE: This is the same for $ (accounting number format) on the ribbon.NOTE: Selecting Number from the right click – Format Cells/Number menu allows you to set the number of decimal places when it is not percent or currency (i.e. 5.12).Now, answer questions 5 to 7 on your question sheet.FIXING UP THE FORMATTINGNotice that the titles are not right over the numbers.Highlight cells B1 to D1.Select bold (B) from the home ribbon.Highlight cells C7 to C10.Select right alignment and bold (B).Move to cell D7.Select bold (B) and italic (I).Select the Format Painter button from the toolbar.Highlight cells D7 to D9. Press Enter.Move to cell D10. Select bold (B) and italic (I).From now on, make sure titles are lined up with the cells in the column, and important information is in bold, italic or underline before printing.INSERTING ROWS AND COLUMNSMove to cell A1 (or any cell in row 1).From the Home ribbon, select the dropdown portion of Insert.Select “Insert Sheet Rows”Notice where the inserted row went relative to cell A1.Repeat this to add in another row (2 empty rows at the top).Move to cell A4 (or any cell in row 4)Insert a row.Move to cell A1 (or any cell in column A).From the Home ribbon, select the dropdown portion of Insert.Select “Insert Sheet Columns”Notice where the inserted column went relative to cell A1.In cell C1, type: Joe Solution’s Marks. Press Enter.NOTE: Text will continue to the next cell if it is empty.Answer questions 8 to 10 on your question sheet.ADDING A BORDERHighlight cells B1 to E13From the Home ribbon, in the cells group, click the dropdown portion of format, then select format cellsChoose the “Border” tab.For Style, select the double line. Click Outline.For Style, select the single line. Click Inside. Click OK.CHANGING COLUMN WIDTHSMove to cell E1 (or any cell in column E).From the Cells group in the home ribbon, select the dropdown portion of the format button.Select column Width.Type 3, then press Enter.### will appear because the column width isn’t big enough.Redo this, but set the width to 8.Position the mouse pointer on the column border between headings C and D. The pointer changes to a double-vertical line with arrows pointing left and right. Click the left mouse button and drag the border left to adjust the column width.Fix up the column widths of B,C,D,E so that their size is 8.NOTE: From now on, always fix up the column widths before printing your spreadsheet.Answer questions 11 to 13 on your question sheet.MERGING AND SHADING CELLSHighlight cells B1 to E1.Select the Merge and Centre button from the home ribbon.Right click the newly merged cell (B1)Click format cells, then chose the Fill tab.Select the colour yellow. Click OKEDITING CELLSMove to cell B1.Suppose that you wanted to put your name in the title. You could retype this cell or you could fix it.Let’s fix it.Press the F2 key.Notice that the insertion point shows up at the end of the entry (this allows you to edit the cell).Press the left arrow key until the insertion point is at the end of Joe Solution’s.Press the Backspace key to delete this name.Type your name. Press Enter.NOTE: Editing can also be done by double clicking in the cell, or by editing the cell in the formula box.Answer questions 14 and 15 on your question sheet.Now, the layout looks pretty good; it should look just like the following (except for the name, of course):-6667518415000*** SAVE YOUR FILE FOR LATER USE. ***SUBMITTING TWO PRINTOUTSYou would click on print to send this to the printer. (DO NOT DO THIS NOW).NOTE: This is the “VALUES” version of your spreadsheet.On the Formulas Ribbon, you can click the Show Formulas ButtonNOTE: Instead of the values, the formulas now appear. Also, column widths are enlarged to accommodate the formulas.NOTE: You can also show or hide the formulas by pressing CTRL + ~You would then click on print. (DO NOT DO THIS NOW).From now on, whenever you submit a spreadsheet, you should hand in two printouts:- values version- formulas versionPrint a formulas version and a values version to .PDF, and raise your hand and show the results to Mr. Meissner. Once you have showed Mrs. Stemmler, you may move on to the assignment.MICROSOFT EXCEL--TUTORIAL #2—Question SheetBuilt-in formulas are called ____________________.Explain what the following functions do:MAX_______________________________________________MIN_______________________________________________COUNT_______________________________________________SUM_______________________________________________AVERAGE_______________________________________________An example of a _____________ of cells would be (C4:E10).Another way to total numbers is to use the _______________ button on the home ribbon.When you select % on the ribbon, the number of decimal places is ______.After highlighting cells, how would you choose to format numbers?______________________________You can quickly change the number of decimal places by selecting the __________ or ______________ buttons on the home ribbon.Titles should be ________________ with the cells in the column.How should important information be formatted? ___________________________________Inserting rows places them _________ of the row where your active cell is. Inserting columns places them __________ of the column where your active cell is.To customize a border, choose ________ ________, and the ________ tab.When the column width isn’t big enough, _______________ appear in the cell.Using your mouse, how can you widen a column? __________________________________The quickest way to center information over your spreadsheet is to use the ______________ button on the home ribbon.Describe two ways you can edit cells. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download