Spreadsheets for Geniuses Introduction Spreadsheets make use of the great mathematical powers of the computer Simply put A Spreadsheet is a computerized ledger that can perform calculations on its data In addition to Microsoft s Excel there are many different spreadsheet programs While the functionality of each spreadsheet program is very similar the features and methods are quite different for performing the same task All examples in this section will be given using Microsoft Excel Important Note At first students may think the methods of creating a spreadsheet to perform calculations is cumbersome This is NOT TRUE Spreadsheets are easy to learn and extremely useful Like anything becoming proficient with a spreadsheet program takes a little practice Once a small amount of proficiency is gained you will begin to see how incredibly handy this tool can be Excel Overview The spreadsheet files in Excel are called workbooks and can hold several worksheets A worksheet is made up of a grid labeled with letters along the top and numbers down the side These labels are the gray buttons down the left side and along the top The intersection of a column and a row is called a cell The cell address is the column letter and the row number i e C5 These cells can contain text numbers or mathematical formulas The Standard Toolbar This toolbar is located just below the menu bar at the top of the screen and allows you to quickly access basic Excel commands New Select File New from the menu bar press CTRL N or click the New button to create a new workbook Open Click File Open from the menu bar press CTRL O or click the Open folder button to open an existing workbook Save The first time you save a workbook select File Save As and name the file After the file is named click File Save CTRL S or the Save button on the standard toolbar Print Click the Print button to print the worksheet Print Preview This feature will allow you to preview the worksheet before it prints Spell Check Use the spell checker to correct spelling errors on the worksheet Cut Copy Paste and Format Painter These actions are explained in the Modifying A Worksheet section Undo and Redo Click the backward Undo arrow to cancel the last action you performed whether it be entering data into a cell formatting a cell entering a function etc Click the forward Redo arrow to cancel the undo action Insert Hyperlink To insert a hyperlink to a web site on the Internet type the text into a cell you want to be the link that can be clicked with the mouse Then click the Insert Hyperlink button and enter the web address you want the text to link to and click OK Autosum Function Wizard and Sorting These features are discussed in detail in the Functions tutorial Zoom To change the size that the worksheet appears on the screen choose a different percentage from the Zoom menu 2 What s the big deal about a spreadsheet What makes a spreadsheet so useful So what a ledger on the computer By way of illustration I will demonstrate how useful a spreadsheet can be Let s calculate a Grade Point Average Gary is a student at Saddleback Community college with dreams of grandeur he wants to become a high paid programmer making millions of dollars a year To do this he wants to graduate from the University of California at Irvine But alas Gary hasn t done a great job as a student and needs to raise his GPA before admissions at UCI will even look at him He has been at Saddleback for one semester and his grades have been poor He figures that at then end of his fourth semester he will need at least a 3 25 GPA to be accepted Calculated Via Paper At first Gary hasn t had computer science 1A so he knows little about spreadsheets So he does everything on paper He writes out each of his existing grades substituting the appropriate grade point for the grades adds them up and divides them by the number of classes that he has taken thus far He comes up with a 2 25 Out of the 12 classes left he needs to raise his GPA but he doesn t know how many A s and B s he needs to get He guesses that he can get all B s and still get his 3 25 To verify this he writes down all of his current grades and the twelve B s that he thinks he needs He adds them all up and now divides by 16 the number of classes he will have taken by the end and finds out that erg Gary s new GPA will be 2 8125 This is not enough Now he assumes that he would have to get all A s So he erases all of the B s and substitutes A s Adds them all up by hand and then divides by 16 He sees that if he were to get all A s by the end of his stay at Saddleback he would have a GPA of 3 5625 That would surely get him the acceptance but realistically Gary likes to hang out with his friends he thinks he at least wants some life Gary reasons that a couple of B s will be ok but how many exactly let s calculate maximum slack factor Gary then begins the arduous process of erasing one A and replacing it with a B and then re calculating the GPA He does this erase write recalculate 5 times until the GPA is just right He adds up the number of A s and B s until he finds that he now needs 7 A s and 5 B s By the time he is finished making this calculation 23 years have passed and he has little time to graduate from UCI Gary needs a quicker method of calculation 3 Calculated Via Spreadsheet Had Mr Coleman used a spreadsheet to calculate the GPA the process would have been far less arduous Here is what he would have done 1 Open up Microsoft Excel and enter all of his classes in column B He separates the classes into Semesters just for show In Column D he puts his Grade Point for each grade that he has received thus far Grade Column 4 2 Next Gary would select a cell to calculate his grade point average He does this by clicking inside of the cell that we wants to be the Selected Cell In this example he selects D 27 Gary can see that the cell is selected because a Black Box surrounds the selected cell The selected cell 3 Now Gary wants to enter a Calculation specifically he wants to enter the Average function By selecting a function from the insert menu at the top of the program the results of the function will appear in the selected cell in Gary s case D 27 So Gary selects Insert from the menu and then selects Function 5 …