ac) M ( l e c MS Ex ICT Training June 2002 Contents Job Aid Prepared by Sandrine Ceurstemont Introduction to Excel for Mac In this workshop, you will learn the basics of Microsoft Excel, an electronic spreadsheet program. We will go through the basic functions immediately followed by an activity where you will create a spreadsheet for your students’ marks. How do I open Excel? Microsoft Excel should be found with the rest of your program files. Go to the hard drive, select the ‘Applications’ folder and locate the ‘Microsoft Office’ folder. Double click to open this folder, then double click on the ‘Word’ icon. Basics of a spreadsheet What is a spreadsheet? The terms ‘spreadsheet’ and ‘worksheet’ are generally used interchangeably. To remain consistent with Microsoft and other publishers, the term worksheet refers to the row-and-column matrix sheet on which you work upon and the term spreadsheet refers to this type of computer application. In addition, the term workbook will refer to the book of pages that is the standard Excel document. The workbook can contain worksheets, chart sheets, or macro modules. CSLP — MS Excel (Mac) Basics of a spreadsheet 1 Types of data 4 Formatting text 5 Constants Formulas Sums 6 Averages 7 Subtracting 7 Multiplying Dividing 8 Formatting numbers 8 Copying formulas 9 Inserting columns & rows 10 Charts & graphs 11 Activities 12 Trainer Notes 19 Page 1 At the bottom of your screen in Excel, you will see tabs marked ‘Sheet1’, ‘Sheet2’ and ‘Sheet3’. By clicking on these tabs, you can access the different worksheets in your document. The Excel worksheet can contain up to 256 sheets, labeled Sheet 1 through Sheet 256. The initial number of sheets in a workbook, which can be changed by the user, is 16. Column Headings Row Headings Cells Formula Bar Active Cell Columns The columns in your worksheet make up the vertical space, as illustrated below. Columns are denoted by letters at the top of the screen. The Excel worksheet contains 256 columns that extend across the worksheet, lettered A through Z, AA through AZ, BA through BZ, and continuing to IA through IZ. CSLP — MS Excel (Mac) Page 2 Rows The rows are the horizontal space on your worksheet. In Excel, numbers on the left hand side of your screen are used to identify rows, as demonstrated below. The Excel worksheet contains 16,384 rows that extend down the worksheet, numbered 1 through 16,384. Cells Cells are the intersection of a column and a row, as illustrated below. A cell is denoted by a letter and a number corresponding to the column and the row that intersect. The cell highlighted in the screen shot below corresponds to the cell B8. CSLP — MS Excel (Mac) Page 3 Column width You will often want to change the width of the columns in your spreadsheet depending on the amount of text or numbers that you want in a cell. To increase the column width: 1. Follow the border to the right of your cell to the top of worksheet where the letters are written. 2. Once your cursor is on the line between the two letters, your cursor will turn into a double-sided arrow. Click down on your mouse and drag the line to the right until the cell is the appropriate width. To decrease the column width: 1. Follow the border to the right of your cell to the top of the worksheet where the letters are written. 2. Once your cursor is on the line between the two letters, your cursor will turn into a double-sided arrow. Click down on your mouse and drag the line to the left until the cell is the appropriate width. Types of data There are three types of data that you can enter in an Excel worksheet: 1) text, 2) numbers/constants, and 3) formulas. Text Text is usually used to label the numerical data in your spreadsheet and to add titles or annotations. The picture above shows some examples of labels and titles and an example of how you can organize the data you are entering in your worksheet. CSLP — MS Excel (Mac) Page 4 Formatting text You can choose different fonts, font sizes and styles for your text in Excel, just like you would in Word. The easiest way to do this is to use your formatting pallet, which may appear either at the top or at the side of your screen. If the formatting pallet doesn’t appear on your screen, you can go to the ‘View’ menu and select ‘Formatting’ pallet, or select ‘View’ then ‘Toolbars’ then ‘Formatting’ (depending on the version of Excel that you are using). If you want to format text that you have already typed, click on the cell with the text that you want to format. You will see that the text in this cell will also appear at the top of the screen in the formula bar. Select the text in the formula bar with your mouse. Now you can change the font, font size, style or other options on the formatting pallet. Merging Cells You will notice that if you type text in a cell which is longer than the size of the cell, the full text will appear in the formula bar at the top of the screen, but it will appear truncated in the cell. By merging the cell with some of its surrounding cells, the full text will appear in the worksheet screen. To merge cells, select the cells you want to merge with your mouse. Go to the ‘Format’ menu and select ‘Cells…’. Now click on the tab marked ‘Alignment’ and checkmark the box next to ‘Merge cells’. CSLP — MS Excel (Mac) Page 5 Now the complete string of text that you typed should appear on your worksheet as well as in your formula bar. If it does not, then you may need to merge more cells. Constants Constants are numbers, or fixed values, in contrast to formulas. For example, if you enter a numerical mark, an age, or an amount of money, you are entering constants. When you enter constants in Excel, you may want to use these numbers when calculating formulas in other cells such as totals or averages. Formulas In addition to entering numbers/constants in Excel, you can also enter formulas. You can plug in constants that you entered in other cells into the formula to calculate values. Here are some simple mathematical operations and formulas that you should find useful. Sums Before you enter a formula in Excel, you must always type an equal sign first. This tells Excel that you are about to enter a formula. To calculate a sum, you must first select the cell where you want to enter the formula. 1. Select the cell where you want the result of your formula displayed. 2. In the formula bar, type =sum( 3. Next you must select the range of values in your spreadsheet that you want to add together. These values should normally be arranged in a column or a row. CSLP — MS Excel (Mac) Page 6 4. Place your cursor on the first value of the column or row and click on the mouse. Drag your cursor down the row or column until all the values are selected. The range of values you selected will appear in the cell after the open bracket symbol you typed, for example, (B3:B8). 5. Type ) to close the bracket. 6. Press ‘Return’. A numerical value will appear in the cell. This is the sum of the numbers in the range you selected. Averages You can calculate averages in a similar way as calculating sums. 1. Select the cell where you want the result of your formula displayed. 2. In the formula bar, type =average( 3. Next you must select the range of values in your spreadsheet that you want to add together. These values should normally be arranged in a column or a row. Place your cursor on the first value of the column or row and click on the mouse button. Drag your cursor down the row or column until all the values are selected. The range of values you selected will appear in the cell after the open bracket symbol you typed. 4. Type ) to close the bracket. 5. Press ‘Return’. A numerical value will appear in the cell. This is the average of the numbers in the range you selected. Subtracting If you want to subtract values, either constants or values in your cells, you simply need to use the minus sign. 1. First you must select the cell where you want the result from the subtraction to be displayed. 2. Next type the subtraction in the cell. • If you want to subtract 2 numbers, type the subtraction in the cell. For example, typing =5-3 and then pressing ‘Return’ will display the number 2 in the cell. • You can also subtract the numbers that you entered in other cells. For example, typing =D3-B2 followed by ‘Return’ will display the subtraction of the numbers in cells D3 and B2 in the cell. CSLP — MS Excel (Mac) Page 7 Multiplying You can multiply numbers in a similar way to subtracting numbers. The multiplication sign in Excel is the asterisk (*). 1. Select the cell where you want the result from the multiplication to be displayed. 2. Next type the multiplication in the cell. • If you want to multiply 2 numbers, type the multiplication in the cell. For example, typing =6*2 and then pressing ‘Return’ will display the number 12 in the cell. • You can also multiply constants you have entered in other cells. For example, typing =A5*C8 followed by ‘Return’ will display the result of the multiplication of the numbers in cells A5 and C8. Dividing You can do divisions in Excel in the same way as subtractions and multiplications. The operator for divisions in Excel is the forward slash sign (/). 1. Select the cell where you want the result from the division to be displayed. 2. Next type the division in the cell. • If you want to divide 2 numbers, type the division in the cell where you want the result to be displayed. For example, typing =12/3 and then pressing ‘Return’ will display the number 4 in the cell. • You can also divide numbers you have entered in other cells. For example, typing =C2/B3 followed by ‘Return’ will display the result of dividing the numbers in cells C2 and B3. Formatting numbers You can format numbers in Excel according to the number of decimal places you want to display, if the number represents dollars, the date, time etc. To format the number in a cell, you must first select the cell with your mouse. You can also select a range of cells if you want to format more than one cell. 1. Go to the ‘Format’ menu and select ‘Cells…’ 2. Select the ‘Number’ tab. 3. You will see many options in the ‘Category’ window. By clicking on the different options, you will see the different options you have for formatting. CSLP — MS Excel (Mac) Page 8 Decimal places To change the number of decimal places, click on ‘Number’ in the ‘Category’ window. You will see a window with the label ‘Decimal places’. You can change the number of decimal places by typing the number of decimal places you want in this window. Currency To change numbers to currency, click on ‘Currency’ in the ‘Category’ window. Here you can choose the number of decimal places and the type of currency that you want. Date Click on the ‘Date’ option to select date format. You can choose from different ways of displaying the date. Time Click on ‘Time’ to change to time format. You can choose from different formats for displaying time. Fractions To display numbers as fractions, click on the ‘Fraction’ option. Here you can choose the type of fraction you want. Copying formulas When you want to use the same formula in many places in your spreadsheet, you can copy and paste the formula instead of retyping it over and over again. 1. Select the cell where you have already typed the formula. 2. Go to the ‘Edit’ menu and select ‘Copy’. 3. Select the cell or cells where you want the same formula to appear with your mouse. 4. Go to the ‘Edit’ menu and select ‘Paste’. The formula should now appear in the cells you selected. You will notice that Excel changes the range of numbers for which it calculates the formula. For example, if you copy and paste a formula that calculated the sum of a column, the formula will now calculate the sum for the numbers in the column where you pasted the formula. CSLP — MS Excel (Mac) Page 9 Fill down An even easier way to copy formulas is by using the ‘Fill’ options. To copy a formula down the cells in a column: 1. Select all the cells in the column where you want the formula to be copied including the cell with the formula. 2. Go to the ‘Edit’ menu and select ‘Fill’ then ‘Down’. The formula should now be copied to all the selected cells, and the values in the formula will be changed accordingly for each row. Fill right You can use also use the ‘Fill’ option to copy a formula across a row. 1. Select all the cells in the row where you want the formula to be copied including the cell with the formula. 2. Go to the ‘Edit’ menu and select ‘Fill’ then ‘Right’. The formula should now be copied to all the selected cells, and the values in the formula will be changed accordingly for each column. Inserting columns and rows While you are making a spreadsheet, you may sometimes want to modify it by adding extra columns or rows. You can easily insert columns and rows anywhere in your spreadsheet by using the Insert column and Insert row functions. Inserting a column 1. Select a cell in the column to the right of where you want to insert the new column. 2. Go to the ‘Insert’ menu and select ‘Columns’. A new column should be inserted to the left of the column you had selected. Inserting a row 1. Select a cell in the row below the place where you want to insert the new row. 2. Go to the ‘Insert’ menu and select ‘Rows’. A new row should now be inserted above the row you had selected. CSLP — MS Excel (Mac) Page 10 Charts and graphs Excel allows you to create charts and graphs for the data you entered in your spreadsheet. Some of the more common types of graphs and charts you can produce are bar graphs, line graphs and pie charts. First, you have to select the data in your spreadsheet that you want to graph with your mouse. For the purpose of this workshop, lines and bar graphs will be discussed. Line and Bar Graphs 1. After you have selected the data you want to graph, go to the ‘Insert’ menu and select ‘Chart…’ Under ‘Chart type:’ click on ‘Line’. In the window under ‘Chart subtype:’ click on the type of chart or graph that you want to create. 2. Now click on the ‘Next >’ box. 3. If you have selected the right data range you can simply click on ‘Next >’. If you want to make changes to the data you want to graph, you can type in a different data range in the ‘Data range:’ box. Click on ‘Next >’. 4. You will now get to select different chart options. Under the ‘Titles’ tab, you can type in a title for your chart as well as labels for your axes. Under the ‘Gridlines’ tab, you can choose if you want gridlines to appear on your chart. Under the ‘Legend’ tab, you can decide if you want a legend to accompany your chart and where you want it to appear on your graph. When you are done selecting the options you want, click on ‘Next >’. 5. Now you can decide if you want your chart to be displayed in the same worksheet you are working on or if you want it to appear in a separate worksheet in your workbook. You can scroll through the different sheets in your document by clicking on the tabs at the bottom of your screen labeled ‘Sheet1’, ‘Sheet2’, etc... Select the option you prefer (‘As new sheet:’ or ‘As object in:’) and then click on ‘Finish’. Your chart should appear either in your current worksheet or on a new sheet, depending on the option you selected. 6. To edit the options you chose for your chart, you can left click on your chart and select ‘Chart Options’. CSLP — MS Excel (Mac) Page 11 c) el (Ma c x E S M Activities Activity 1: Entering text in Excel You will now start the first step in creating your class marks spreadsheet in Excel. 1. Open Excel according to the instructions at the beginning of this job aid. If it is already open, then go the ‘File’/‘New’ and click ‘OK’. You will want to type a title near the top of the worksheet, for example Marks for Grade 6. Format the title by making the font size bigger and by bolding the text. You may also want to merge some of the cells as explained on page 5. 2. Next you will want to add labels to identify the different assignments/projects for which you will enter marks. You should have four different assignments/projects. You will want to put these titles in the same row. The picture below shows what your worksheet may look like. Do not forget that you can modify the column width if your text does not fit in the cells. 3. Next you will enter the names of your students in the column to the left of the assignment labels. Type in the name of six students. Here is an example of what your spreadsheet might look like: 4. You should now save your spreadsheet. Saving your work 1. Go to the ‘File’ menu and select ‘Save’ or simply click on the floppy disk icon on the standard toolbar at the top of your screen. 2. Choose the location where you want to save your document in the box next to ‘Save in:’ (save your file on your floppy disk if you have one, otherwise you may want to save your document on the desktop) 3. Type in a name for your document in the box next to ‘File name’. 4. Click on the ‘Save’ button. CSLP — MS Excel (Mac) Page 13 Activity 2: Entering constants and calculating sums To practice using the sum function in Excel, you will first add another column to your spreadsheet. 1. Add another label to your spreadsheet by typing Books Borrowed at the end of the row with your titles. Enter the amounts as illustrated below. Your spreadsheet should now look something like this: 2. To calculate the total number of books borrowed by all the students, select the next cell in the column. Type the formula for calculating sums, selecting the numbers in the Books Borrowed column as the range of values you want for the sum. Follow the instructions described for the sum function described on page 6 & 7. You will put the total in the cell below your last entry in the Books Borrowed column. 3. Remember to save your work. CSLP — MS Excel (Mac) Page 14 Activity 3: Calculating averages Now you want to calculate the class average for each assignment/ project. First you have to enter the marks for the assignments. 1. In the first column, enter marks out of 10 for the first assignment. 2. In the second column, enter marks out of 20 for the second assignment. 3. In the third column, enter marks out of 30 for the third assignment. 4. In the fourth column, enter marks out of 30 for the fourth assignment. 5. Now you want to calculate the average class mark for the first assignment. In the next free cell in the first column that contains numbers, type the average formula as described in the procedure for averages on page 7. Select the numbers in the first column as the range. When you are done, your spreadsheet should look something like this: 5. Remember to save your work. CSLP — MS Excel (Mac) Page 15 Activity 4: Copying formulas & formatting numbers You have already entered the formula for calculating an average into your spreadsheet and you have calculated the class average for the first assignment. Now you want to calculate the average for the other assignments, and the easiest way to do this is to copy and paste the formulas or to use the ‘Fill’ option. 1. First you will practice copying and pasting the formula. Select the cell where you entered the average formula for the first assignment. Use the method for copying and pasting a formula described on page 9 and copy this formula for the column with the marks for the second assignment. 2. Next you will practice using the ‘Fill right’ function. Select the cell where you calculated the average for the second assignment along with the two cells to the right of it where you want the average for the third and fourth assignment to be displayed. Use the procedure for the ‘Fill right’ function described on page 10 to copy the average formula. 3. You may notice that some of the averages calculated involve decimals. You will want to format these cells so that only two decimal places are visible. Use the procedure for formatting numbers described on page 10 to format any cells with decimals to two decimal places. Your spreadsheet should now look something like this: 4. Remember to save your work. CSLP — MS Excel (Mac) Page 16 Activity 5: Inserting columns and rows You will now practice inserting columns and rows. You forgot one of the students in your class and want to add a row for that student. 1. Add a row for the student you forgot after the first student you listed. Use the procedure for inserting rows described on page 10. 2. Now, fill in the row for the new student by typing the student’s name, marks for the four assignments and books borrowed. You will notice that the values for the formulas have changed to account for the new data you just entered. Your spreadsheet should now look something like this: 3. You realize that you have forgotten one of the assignments you did with the class. Insert a column after the fourth assignment in your spreadsheet by using the method for inserting a column described on page 10. 4. Type the name of the assignment and enter marks for the assignments. Using one of the methods for copying a formula, copy the average formula at the end of this column to calculate the class average for this assignment. Your spreadsheet should now look something like this: CSLP — MS Excel (Mac) Page 17 Activity 6: Creating a line graph To practice creating graphs, you will create a graph or chart to visually illustrate the spread of marks in your class on their first assignment. 1. The first step is to select the column in your spreadsheet where you typed in the class marks for the first assignment. 2. Go to the ‘Insert’ menu and select ‘Chart…’ 3. Follow the instructions for creating a line graph described on page 11. Type in a name for the chart and label the axes. Display the chart as an object in your worksheet. Your line graph should look something like this: 4. Remember to save your work. CONGRATULATIONS! You have now completed the Excel workshop. You should now be able to use the basic functions of Excel. Keep this handout and the spreadsheet you made as they could be helpful for future reference. CSLP — MS Excel (Mac) Page 18 ac) M ( l e c MS Ex Trainer Notes The key to any workshop’s success is to prepare in advance. Be sure to introduce only those Excel elements with which you are comfortable to you class. You can always add more features to your to do list as the comfort level of the users increases. What you must do: • • • • Familiarize yourself with MS Excel and practice using its features. Determine why you want to use Excel, what the final product will be (a class list, grades, record of money paid for fieldtrips, math quiz progress charts). Teach your students basic record keeping with Excel so they can keep track of their own progress. Be willing to make mistakes and try new features! Things you will need for your workshop: • • • Handouts for all participants Projector, screen and a computer for the presenter Computers for all the participants with MS Excel installed Resources An Excel workshop (Rhonda Roark and Agnes Marcum) http://170.183.222.114/lms/ExclWrk.htm Microsoft Excel page—see the section at the bottom about tools to help you work smarter. http://www.microsoft.com/office/excel/default.asp Another tutorial that looks pretty good from the University of South Dakota http://www.usd.edu/trio/tut/excel/ CSLP — MS Excel (Mac) Page 19

© Copyright 2018