CSCI 1100L: Topics in Computing Lab

Lab4: Introduction to Microsoft Excel and

Modeling with Spreadsheets

Purpose: The Xerox Star computer… it was really a rock star of a computer back in its day (the 1980’s), but it didn’t get much respect due to its price tag and that it lacked a key piece of software … the spreadsheet. Over this week and the next, we’re going to give you lots of experience of seeing what you can do with a typical spreadsheet program, Microsoft Excel.

Today’s lab consists of:

Learn how to do basic formatting with Excel. Learn basic chart functions in Excel. Learn how to type in a function in Excel.

By completing this lab, you should feel more comfortable using spreadsheets in your daily lives.

Part 1: Introduction to Microsoft Excel

Download the excel_example1.xls file from labs area to your I: drive. Open up the excel_example1.xls from your I: drive. If you see a message at the top of Excel asking to Enable Editing, then go ahead and click Enable Editing.

From the File menu ( ), choose Save As, and make sure that your file is saved in your I: drive. This is to double check that Enable Editing has been selected. As you work through the exercises this week, save your work to your I: drive every few minutes.

When you open up an excel file, you’ll notice that it can contain multiple sheets. The

sheets are located at the bottom of the excel program, and they look like

. Let’s start with Sheet1, so make sure it is selected. Sheet1 totals up a customer’s receipt for Fred's Casual Grocery. (Fred is too cheap to buy a cash register.) Let's customize this sheet to your own name. Click on the cell containing “Fred's Casual Groceries.”

Notice that the heading for Column A is highlighted at the top, and so is the heading for Row 2 on the left side. The rectangular area that contains “Fred's Casual Grocery,” is called a cell. Notice that the cell address, A2, shows up in the address box near the top left of the sheet.

The cell contents, “Fred's Casual Grocery,” shows in the formula bar near the top of the page. Click here

and replace Fred's name with your own (unless your

name happens to be 'Fred'). As you begin to change the cell contents, an X and a check mark will appear. Try clicking on the X – this will undo your changes and restore the original cell contents.

Return to the formula bar and change 'Fred' to your own name. This time click the check mark. This will commit the changes and return your mouse pointer to cell A2. You can also commit a change by hitting the enter key on the keyboard.

Important: when you edit a cell, be sure to either click the check mark or press the enter key when you are done.

Now let’s explore the Excel toolbars. Click the Page Layout tab at the top of your screen.

In the Sheet Options group, note that Gridlines and Headings are turned on. Try clicking off each item back on.

Click on cell A1, “CSCI 1100.” Click the Home tab and use the Font group to change this header to 16-point, Comic Sans MS font. Note

that the small down-pointing arrows indicate drop down boxes for changing the selected font and point size.

Now find the Bold, Italic, and Underline buttons Change the text

in cell A1 to italic underlined text. Find the Font Color button, near the right end of the Font group. Choose a red color or your favorite color for cell A1. Use the Font options to change cell A2 to 12-point green Courier New font that is in bold.

Although Excel is primarily a calculation tool, it is important to provide proper labeling so that we can read and understand our work.

In row 4, enter some labels for the columns below. First click in cell A4 and type “Item”. Press the Tab key to move sideways to cell B4. Type “Unit Price” here. Tab over to enter “Quantity” in cell C4 and “Extended Price” in cell D4.

(This is a standard accounting term meaning the total cost of a quantity of similar items).

Now we would like to format all of these four cells. Click inside cell A4 and drag your mouse to the right, until you have selected cells A4, B4, C4, and D4. Then release the mouse. These four cells form a range, one of the most important concepts in Excel because it allows us to format and apply a formula to entire range of data.

The range you have selected is named A4:D4 in Excel language. Find the toolbar button in the Alignment group under the Home tab to center the text. Then boldface the range and color it red. Note that each formatting change applies simultaneously to all the cells in the selected range.

In the Font group you will see the Border tool . Find a border style with a solid bottom border and apply it to the four cells you have selected.

Notice that column D is not wide enough to hold the label in cell D4. With your mouse, grab the little crease between the column D header and the column E header at the top. Your mouse pointer should change to a special cross-shaped cursor. Now you can drag and drop to widen column D so that the label has room to display.

Now let's do some calculation. The extended price in cell D5 should be the product of the

unit price in cell B5 and the quantity in cell C5. This requires an Excel formula, and you create this as follows.

Click in cell D5 and type the equals sign “=”. Click in cell B5 and note that the

formula bar now says “=B5. Press the “*” key (shift + 8) – this represents multiplication in Excel. Click cell C5 to add C5 to the formula to get something that looks like: Finish by clicking the check mark. You should now see “2.49” in cell D5.

Just for practice, use the same technique to enter the formula in cell D6 using values from B6 and C6. The Extended Price should be 2.29.

We could continue down the column, but this gets boring and is error-prone. One of Excel's most powerful features is its ability to copy formulas.

Select cell D6, and notice that a small square “handle” appears at the

bottom right of the cell. Grab the “handle” and drag it down, covering the range down to D11. When you release the mouse, the formula has been copied to the entire range.

Click in cell D7 and examine the formula bar – it says “=B7*C7”. Click in cell D8 and the formula says “=B8*C8”, which is the correct formula for 16 packs of Ramen noodles.

Excel has 'intelligently' altered the formulas to adapt to the changing context.

Now it is time to find the subtotal (total before taxes) of the Extended Price column.

Click in cell D12. We could compose a formula, but Excel has a special feature. Click

the Formulas tab and find the Σ button

. This Greek letter, “sigma,” is the mathematical symbol for “Sum”. When you click the Sum button, Excel will automatically attempt to guess the numbers you wish to add up. In this case, Excel will correctly draw a line around all the numbers in column D. Since this is what we want, click the green check to accept Excel's guess. The answer should be 23.21.

Carefully examine the formula in the formula bar: =SUM (D5:D11). Recall that D5:D11 is Excel notation for the range of cells from D5 through D11. The parentheses around this range indicate that SUM is a function – a mathematical process that transforms input into a single output. In this case the inputs are the 7 individual values, and the function's output is the sum. In general, a function can have any number of inputs, but always produces a single output.

The numbers in columns B and D represent currency, and

would look better if we formatted them correctly. Use your mouse to select cells B5 through B11 (click in B5 and drag down, then release – note the difference between selecting and using the fill handle). Click the Home tab. See the big drop-down box at the top of the Number group (see the image to the right)? Use that box to select Accounting style. You will see the dollar signs lined up on the left hand of each cell.

Now select cells D5 through D14. As we did previously, change the format, however this time select the Currency format.

OK, now it is time to add the sales tax on our groceries. To do this we need the sales tax rate, which is not yet on the sheet. The tax rate is an important part of the model, and needs to be displayed and labeled up above the columns of grocery prices. Unfortunately, we did not leave room for this. That's OK, because we can make room!

Click in the box to the left of row 4. This will select the entire row 4. Drag down until you have selected rows 4 and 5, then release. Still in the Home tab, click the Insert button in the Cells group. Because you selected two rows, two new rows will appear. Notice that this changes all cell addresses below, but the formulas in column D have all been modified to adjust to the new cell addresses.

Now enter the text “Sales Tax Rate” in cell A4 and 7% in cell B4. To highlight this important information, select these two cells. Use the Border tool to surround them

with a solid border. Use the Fill color tool to fill in the area with a light yellow background.

Click in cell D15. The tax amount should be the product of the subtotal in cell D14 and the tax rate in cell B4. Enter an Excel formula to do this calculation (remember that the formula must start with an equals sign, use cell references instead of values, i.e. D14 instead of “23.21”, and the “*” character represents multiplication). The sales tax in cell D15 should equal $1.62.

If you can’t get the exact formula, ask your TA for help. It’s crucial to understand and apply Excel formulas correctly. You will see the difference in the next step…

It's important that you always use cell references in Excel formulas instead of copying numbers by hand.

To see this, change the tax rate in cell B4 to 5%. The tax should change to $1.16,

tracking the change in tax rate. Use the Undo button to restore the tax rate to 7%. Now click in cell D15 and change the formula to =D14*7%. You will still get the right answer, $1.62.

But change the tax rate in cell B4 to 5% again, and the value in D15 will no longer track it. In other words, copying numbers by hand leads to errors when you change your numbers later on. Go back and change B4 to 7% and D15 to =D14*B4.

In cell D16, we need a formula for the total bill, which is the subtotal plus sales tax.

Click in D16 and find the Autosum button again, which is in the top right part of the Home tab as well as in the Formulas tab. Click this button.

Uh-oh! Excel makes the wrong guess as to which cells you wanted to

add.

Drag with your mouse to select the correct cells D14 and D15, then click the check mark. The total bill should be $24.83.

Now let's try calculating the sales tax for each item.

In cell E6, put a label Sales Tax. Use the Format Paintbrush on

left-side top under HOME tab to copy the formatting from cell D6 to cell E6, so E6 looks like the other cells in that row.

In cell E7, put a formula for the sales tax on the first item. This will be the tax rate from cell B4 times the extended price in cell D7. The result should be $0.17.

Remember how we copied the formulas in column D? Let's try that trick again. Select cell E7. Grab the 'fill handle' and drag down to cover cells E8 through E13. Release the mouse to copy the formulas. Ouch! The results are clearly wrong.

To see what happened, click in cell E8 and examine the formula in the formula bar. You will see that the extended price in D8 has been multiplied by B5, not B4. When we copied the formula from E7, we wanted D7 changed to D8, but we did not want B4

changed to B5. Click the Undo button to remove the bad formulas.

Fortunately, there is a way to tell Excel not to change an address when a formula is copied.

Go back to cell E7 and change the formula there to =$B$4*D7. The dollar signs create an absolute address or absolute cell reference. Note that the formula value has not changed.

Now grab the fill handle again and copy the formula from cell E7 down through row 13. This time, the results will be correct. Click in cell E8 and examine the formula bar. You will see that this time, Excel did not change the address B4 when you copied the formula down.

Click in cell E15 and use the Sum tool to add up all the individual item sales tax values in column E. The value should agree with the sales tax value in cell D15.

Now suppose you'd like a graphical representation of this grocery list. (They don't provide this at Kroger, do they?) A good tool for this purpose is the pie chart, which will show how your grocery dollar is divided among your various food items

Start by selecting the range D7:D13, which contains the cost of all the items in our

grocery basket. Choose the Insert tab and find the Charts group. You will be able to select from several different varieties of charts; for this example, select the Pie in 3-D chart type as highlighted below.

The result is colorful, but not yet especially useful! The little colored dots “Corn flakes”, “tooth paste”, etc. are called the legend of the chart. Note the label “Chart tools” at the top of your window when you click on a chart. This means Excel is now giving you a whole set of tabs for charts. Now the legend isn’t too useful for this example so we’ll turn it off: click the + sign next to the plot, and unclick the Legend checkbox.

We still need to label the pie slices. Click the Design tab. In the Data group, you will find the “Select Data” button. Click the Select Data button. In the right side of the window that pops up, you will see “Horizontal (Category) Axis Labels”. Here is where we tell Excel that our values have labels, but they’re in Column A. Select the Edit button on the Horizontal (Category) Axis Labels side of the window. A small window pops up. With your mouse, select the range A7:A13 from your Excel sheet, then click OK, then OK one more time.

Hmm, something happened underneath the covers in Excel, but we can’t tell at this point.

Because we turned off the Legend, nothing is showing up on our Pie Chart. However, if

we add data labels to our chart, then what we did in the last paragraph will show up. In the Format tab, choose “Series 1 Data Labels” and click the Format Selection.

In the Format Data Labels window, uncheck everything, then check the Category Name, Percentage, and Best Fit, then click Reset Label Text. Then click Close. The Category Names are taken from the first column of your table, and Excel will do a good job of formatting the labels to look good.

In the Label Options selection, choose the Chart Title menu. Excel provides a default title text “Chart Title.” Select this text and type over it the new chart title, “Grocery Bill.”

Using your mouse, adjust the size of the chart until you like how it looks. It is easy to copy a chart like this into a Word document or a PowerPoint presentation.

Notice that our Pie Chart visually represents our data model, which in this case is our grocery bill. By just glancing at the pie chart, we can see the proportions of our grocery bill without having to look at particular numbers or doing calculations on our own. Data modeling and visualization is extremely important in science and many disciplines since it allow us to see trends in our data that we may miss by just looking at numbers. Excel is an excellent tool for both data modeling and visualization. Once you calculate something, it is easy to create a chart in Excel to visualize your data.

We are almost finished with this sheet, but we need to add a few things. Excel spreadsheets should have a nice label that spans the columns in the top row(s). We have labels at the top of our sheet already, but they are only in the first cell. So let’s make these titles span the columns of the spreadsheet so they stand out more and look better.

Click the Home tab. Select cells A1 through E1, and click the Merge & Center button

. You should see that CS1100 is in a single cell that spans columns A through E.

.

Now, select cells A2 through E2, and Merge & Center those cells as well. You can click the Merge & Center button to return the cells back to normal.

The worksheet tab at the bottom of the sheet says “Sheet 1”, which is not very informative.

To change this to something more helpful, click on the worksheet tab with the right mouse button. Choose Rename, and type “Casual Grocery” to replace “Sheet 1”

. The term for this type of menu is a “contextual menu” because what menu you get, depends on where you’re clicking. Exception, Macs don’t typically have a right mouse button (but you can press the control key while clicking to simulate this).

Save your work to your I: drive now, and remember to save every-so-often when you work on labs.

Page 10

Part 2a: Using Excel to Calculate a Course Grade Creating a grade worksheet will make it easy for you to answer the eternal question, “What grade do I need on the final exam to get an A in the course?” Plus, it is an excellent way to practice your Excel skills.

Remember, knowing a spreadsheet is the single most useful application software program for business. Don’t forget to mention your Excel skills on your resume!

1. Start by downloading the Student_Grade_Template.xls spreadsheet from the labs page to your I: drive. Open the spreadsheet from your I: drive. You may notice a button at the top for Enable Editing, so go ahead and click Enable Editing if asked to. Make sure you are on the sheet CSCI_1100_grade by checking on the Sheet tabs in bottom.

a. Important note: This spreadsheet may not match the grading system found on our course syllabus since our course changes from semester to semester. After you complete this lab, you may go back and update the spreadsheet based on our current course syllabus to help you calculate your grade in our course.

The numbers in cells B4 through B10 are the point values for the various parts of the class (note: these values may be different than what’s on the class syllabus, but you can easily update them if necessary). Today, you will estimate your lab quiz and exam grades you don’t have yet. First, we will put in made up values, but at the end you can update and estimate your own values. As your actual grades come in during the semester, you can go back and update the sheet. By the end of the term, this should accurately predict your grade. Please use blue colored text to denote your score (or estimated future score) for this sheet.

2. Let’s “guesstimate” your lab quiz scores first. Enter 22, 18, 25, 25, 13, and 0 for “Your Score” for lab quiz 1 through 6 respectively. Note: these are estimates, so just color them blue so they stand out.

3. Find the total of your lab quiz scores. You can use a formula to do this or use the AutoSum button. Your total lab quiz scores should be 103.

Pa

4. Insert a new row to the lab quizzes part of the spreadsheet. To do this, select the last row of lab quizzes (under the Total), and right click to bring up the context menu, and click insert and select Entire Row.

5. In this new row, put Average in column A under the Total. Insert two more rows under Average, and put Min, and Max under Average in column A, and you should get something similar to below.

6. Now we can start using functions in excel to calculate the average, min, and max of our lab quiz scores. Click on the cell for the average of the “Your Score” for lab quizzes, and then click the insert functions button.

Click on AVERAGE in the Insert Function window,

and click OK. If average doesn’t appear, just use

the search box to find it. Now a function arguments window

should pop up. Make sure you click on Number1, and check

that the range is selected if C14:C19 and hit OK.

Page 10

You should see the average lab quiz grade of 17.1666667.

7. Now repeat the last step but use the function min and max to fill in those cells in the table. You may need to use the search box in the insert function window to find these functions. Your values should like those below.

8. We have our estimated lab quiz scores. Notice that since you used formulas and functions in excel that refer to cells, you can change the values of “Your Score”, and the total, average, min, and max will update accordingly.

9. Go ahead and put estimations of 100, 123, 156 for “Your Score” for Exam 1, Exam 2, and Final Exam under Exams. Calculate your exams total using a formula or function. Insert rows for Average, Min, and Max, and calculate those values like you did for the lab quiz grades. You should end up with a spreadsheet that looks like below. Make sure you add or remove rows if necessary so your spreadsheet looks exactly like the one below (the columns and row numbers should match below in order to continue).

Page 11

10. Once you have your sheet looking like the one above, we can move on to estimating your overall class score.

In cell C8, put a cell reference to the total of “Your Score” for the lab quizzes by typing =C20 in the cell.

In C9, put a formula to add up cells C26 and C27, your exam grades. In cell C10, put a reference to cell C28 by typing =C28 in the cell, your estimated final exam

grade. After that put in estimates of 50, 30, 62, and 45 for “Your Score” for Attendance, Policies

Quiz, Group Note-taking Exercises, and Web page respectively.

Page 12

11. Total up “Your Scores” for rows C4 through C10 in C11 by Sum or AutoSum method.

12. Insert a row below row 11. And call this row, My Overall Grade. And in Cell C12 compute your overall grade which is C11 (Your total score) divided by B11 (the total points possible).

Note: The formula for this would be C11/B11, where / is the division symbol. Go ahead and format C12 to a percentage. If the percentage appears 77% at first time, then increase its decimal by 2 near the percentage symbol. Your table should look like the one below.

13. So with these scores, we end up with a 77.34% in the class, which is around a C+. But which grades hurt us the most. To answer this let’s, calculate the weights. Here we will define weights as the percentage (or fraction) of the points we earned towards our final course grade out of the total possible points.

14. To compute the weight for Attendance, click on D4, then input the formula =C4 divided by $B$11. Note that we will need an absolute reference to B11, since it contains the total points possible. You should see that our score 50 for attendance contributed about 5.8% of our total grade. If you don’t see a percentage, go ahead and format the weight column for percentages.

Page 13

15. Calculate the weights for D5 through D10 in the similar manners. Now, you’ll see how

much each score contributed to the overall grade. Also calculate the total for D4 through D10 in the Total’s row.

You should see the same numbers as below.

16. Now let’s do a quick data visualization. Select the data as shown below, click on the Insert tab, and insert a column chart (the first one under column chart is fine). Add a tittle to the top of the chart, “Your Score vs Possible Points”.

17. From the chart, you can now see how your scores compare to the possible point values, and you can see that the exam grades and lab quiz grades caused the greatest loss of points that effected your overall grade. When you are finished looking at your chart, move it to the bottom of the spreadsheet and out of your way.

18. One question we always get asked is what I need to score on the final exam to get a certain grade. Once all grades are inputted except for the final exam grade, we can use Goal Seek in excel to calculate a value for Final Exam to give us a certain grade.

19. Delete cell C10, the final exam grade. And click on C12, my overall grade. Click on the

Data tab, click What-If Analysis , and click Goal Seek. Now, let’s ask excel to compute our final exam grade to get a final grade of 82%.

Type in the following values into the goal seek box shown in the pic on the right. After hitting Ok, you’ll see Excel fill in a grade of 196.3 for the Final Exam to end up with an overall grade of 82%. So this student would need to make 196.3 out of 200 points on their final to get an 82% in the class.

Page 14

Go ahead and play with the goal seek command some more to see what score

you would need to get on the final exam to get an overall grade of 75%, 80%, 85%, or 90%? Are some of these scores for the final exam impossible to get?

20. Change C10 back to referencing C29. Now, go in and input your own grades or guess scores that you will make throughout the semester. Watch as the weight values and the chart changes as you update values. This is the power of excel. Once our formulas are in place, we can change values whenever we want, and Excel will update our data model and visualizations automatically. As the semester goes on, you should come back and update these score to keep up with your own class grade.

21. Save this file to your I: drive (of course you’ve been doing this all along haven’t you?).

Part 2b: Using Excel to Calculate Your GPA

1. Click on the GPA worksheet in your Student_Grade_Template.xls file and examine the entries there. There is one line for your previous semesters' GPA and several lines for courses you are taking this semester. Note we are assuming you will get an A in this course! If you would like this worksheet to be relevant to your actual situation, then at the end of the lab, please replace these entries or add entries for your actual GPA and your actual courses for this semester.

2. Widen column A so that the label in cell A18 fits inside.

3. Fill in cell B16 with a formula for the number of hours you are taking this semester. Be sure to use a formula rather than calculating by hand.

4. Fill in cell B18 with a formula for the total number of hours you will have after this semester. This includes the hours from this semester plus the previous semesters' hours.

5. In order to calculate your current semester GPA, we need to translate the letter grades in column C into their numeric equivalents. Examine the table in cells I3:J11. Oops! We left out the row for “B”. Here’s how to fix it:

a. Select cells I6:J6

b. From the Home tab menu, in the Cells group, click Insert, then Insert Cells. The existing cells need to go somewhere, so select Shift cells down.

c. Type in the new values B and 3 into cells I6 and J6 respectively.

6. Select all the cells I3 through J12. Right click on the highlighted cells and select Define Name… and give this table a name, gradevalues, and click OK.

7. Now we will use the gradevalues table to translate the letter grades in column D to

numbers. Click in cell D11 and click the ‘insert function’ tool . Select the VLOOKUP function. This should be under “Recently Used Functions,” but if not search for it. This "vertical lookup" function will translate the letter grades "A", "A-", etc. into their numeric equivalents by looking them up in the table of values. Input the following into the function arguments for the VLOOKUP:

a. The value being looked up is the course grade, “A”, in C11. b. The lookup table is the table you named earlier. c. The lookup value is in column 2 of the lookup table. d. You want to find an exact match to the value being looked up.

Make sure all the values entered are correct.

8. The result of this function should be a “4” in D11. If you have this working, copy the formula down using the Fill Handle to cover the rest of your courses. Check by looking at the numeric grade values in the table. If the values are off, check that your VLOOKUP matches what is above (Range_lookup must be FALSE).

9. Now we will use the traditional method of calculating GPA by "quality points". The Quality points (QP) for a course are given by multiplying the numeric grade by the number of credit hours. Enter formulas in cell E8 and cells E11:E14 for the quality points for your previous terms and each course for this term. Remember, you only need to specify the formula once, and you can copy or fill in the remainder of the cells in that column.

10. Now enter a formula in cell E16 for your total quality points for this semester.

11. Enter a formula in cell D16 for this semester's GPA. How? You get this by dividing total quality points by total hours. Be sure to use cell references rather than entering the numbers yourself.

12. Now we’d like a precise notion of how well we need to do this term to raise our overall GPA to 3.0 (or keep it there)! For this, we will use the same procedure to estimate our GPA after this term. First click in cell B18 and enter a formula for the total hours you will have after this semester if you haven’t already done so. (i.e. add together your previous hours and this semester's hours)

13. Next click in cell E18 and enter a formula for your total quality points after this term, which is E8 + E16 (QPs from previous semesters plus this semester’s QPs).

14. Finally, click in D18 and enter a formula for GPA there. (remember, GPA= QP / Total Hours) When you get this number, change it to red boldfaced text since it is the punch line of this entire model.

15. You may be interested in what you need to do to preserve a 3.0 GPA. It turns out that Goal Seek will not work for this problem. This is partly because there are too many inputs (this semester’s grades in each of your courses) and partly because the grades cannot be smoothly adjusted to any value – they must take on the discrete values A, A-, B+, and so forth.

16. At any rate, Goal Seek will not work here. Try manually tweaking the individual course grades in cells C11:C14 to see what combinations will give you the GPA you want.

17. If you would like to calculate your own GPA, you can go back and adjust this spreadsheet with your own classes and grades. However, this step is optional.

18. Save your work to your I drive.

Congratulations! You have finished the Excel labs for this week. We covered a lot this week, but you should have a better understanding of how to use Excel to calculate, model, and visualize your data.