Beginning Excel Tutorial
All of these materials can be found on the ITRT website. http://salem.k12.va.us/ITRT Click on Tutorials, then on Excel.
Creating a Gradebook
A spreadsheet is a grid of rows going across (1,2,3…) and columns going down (A,B,C....) The intersection of a row and a column is a cell, and this is where you enter labels, numbers and formulas. The top left cell is cell A1. There are 65,536 rows and 256 columns on one sheet! Clicking in the blank cell in the top left corner will select every single one of the cells on the entire sheet, but it’s unlikely that you will need to do that.
To create a label, click in cell A1 and type Names. Press the Enter key. Formatting text works the same as in Word. Simply click in the cell to select the text, and click on the desired style (bold, italics, color) in the formatting toolbar at the top of the screen.
Click in cell A2, type a student name, and press Enter. Continue clicking in cells A3, A4, etc. until you have 5 names entered. Text is accepted by pressing the Enter key, clicking on the checkmark, or clicking in a different cell. Once the text has been entered, you may format it.
In cell B1, type in the name of an assignment. I made mine Bold and Align Right. Remember that you can format the text any way you want to. Underneath this heading, create some grades for your 5 students and type these into cells B2, B3, etc. Create a different assignment in cell C2 and create grades for that assignment.
1
Working with Formulas
Now the fun begins. This is where Excel shines! We are going to tell Excel to average the grades for us. First, we’ll give the column a heading. Let’s label it by clicking in cell D1 and typing Average. Then click in cell D2 and type =(B2+C2)/2. When you press the Enter key, Excel will add the content of B1 to C1 and then divide the sum by 2.
Click and drag down from D2 toD6 to select those 5 cells. Now go the Edit in the menu bar, click on Fill, and then Down. Excel will copy the formula to the other rows and will average those grades also. Pretty cool, huh?
To add more grades later on, the simplest thing to do is to insert columns as you need them. Click anywhere in column D, then go to Insert in the menu bar and click on Column. Since we divided by 2 the first time, our formula for averaging grades will no longer work correctly. Here’s the easy solution: Select the first cell in which you need an average. In our example above, that was D2. Yours will be different, depending on how many columns you have inserted. You could just recreate the formula =(A2+A3+A4)/3 or you could let Excel do the work for you. In the toolbar, click on the dropdown arrow beside the AutoSum icon. This will give you many choices of pre-made formulas or functions. Select Average. Excel will create a formula that calculates the average of all of the cell in the row you’ve selected. Now go to Edit>Fill>Down again to calculate the averages for each student.
A quick way to fill down is to move the mouse to the lower right hand corner of a cell until the cursor becomes a black crosshair. Drag this crosshair down, and the formula will drag right along with it!
2
Charts and Graphs
Let’s play with charts and graphs. Select all of the student grades by clicking in cell A2 and dragging diagonally to cell E6 (or whatever your last cell is.) Then click the Chart Wizards Icon in the toolbar.
Select the chart of your choice, and then click the Next button. You will have the opportunity to enter a chart title and labels for both the X and Y axis. In this example, type in Names for X and Grades for Y. Click Finish.
Printing Your Spreadsheet
To print your spreadsheet, you should first set the print area. First, click and drag to select all of the cells you would like to print. Then click File > Print Area > Set Print Area. A dashed line will appear around the print area on the spreadsheet. Click on Print Preview (or File > Print Preview) to see how your final product will look. If you want your printout to look exactly like your spreadsheet, click File>Page Setup and choose the Sheet tab. Click to check the boxes next to Gridlines and Row/Column Headings. Click OK.
3
Working with a Simple Budget
Look at the bottom of the spreadsheet you just created. Do you see Sheet1, Sheet2, and Sheet3? Let’s rename Sheet1 “Gradebook.” Just double click on the word Sheet1 and type in your new name. Now click on Sheet2 and change that name to “Budget.” We’ll set up some headings first. Type in Item in A1, Quantity in A2, Unit Cost in A3, Total in A4, and Date in A6. (We might want to keep track of when something was ordered at some point in the future.)
Under items, type in 3 or 4 things. In column B, enter a quantity for each item in your spreadsheet. Before we enter prices, we need to format the cells so Excel will know it’s dealing with money. Select cell C2 and click Format>Cells.
Formatting for Money
In the Format Cells window, select Currency and take the other default settings. Click OK.
4
Now cell C2 is formatted for money, so let’s use our fill down methods to make the rest of the cells in that row the correct format. Hold the mouse in the lower right corner of C2 until you see the + sign, and then drag down as far as you need. Now you may enter the prices into the cells. They should show up with dollar sign and decimal points. Now we need a formula for our Totals column. In cell D2, type in =B2*C2 and press Enter. Remember that all formulas must begin with an equals (=) sign. Now grab that little plus sign in the lower right hand corner and drag down the column. Excel not only calculates the totals for you, it also knows to change the format to currency! You could also create a grade total by selecting all of the totals in column D and clicking on the AutoSum icon. You’ll see something like this:
Formatting Dates
Click in cell F2 and click Format > Cells again. Select Date, then decide which of the date formats you would like to use. Once again, you need to fill down the rest of column F so all of those cells will show dates correctly. Try entering in dates in other formats and see if Excel can recognize and reformat them for you.
5