Spreadsheets come in may shapes and sizes. The most popular spreadsheet package used today (Excel) is made by Microsoft, but there are plenty of competitors. For example, we have been experimenting with a web-based spreadsheet (Google Docs) over the last week.
All spreadsheets more or less function in the same way. Visually they are structured based on a grid that is made up of rows and columns. Each row is identified by a number and each column by a letter. The intersection of a row and column is called a “cell,” and cells have names based on their row/column location (i.e. the first cell on a spreadsheet is cell “A1″.)
Here’s a brief overview of how you can get started with using spreadsheets:
- Open up a new spreadsheet (either Excel or Google Docs) – the directions below are for Excel but the same general process should work in most spreadsheet packages.
- Take a look at the spreadsheet. Make note of the rows (numbers) and columns (letters)
- Let’s set up a simple gradebook for our students. Type the word “Students” in cell A1, and then type in the names of our students in this column. Here’s what your final spreadsheet should look like – use this image as a guide as you work through this tutorial:
Spreadsheet layout
- Type the words ‘Test 1′, ‘Test 2′ and ‘Test 3′ along row 1 in cells B1, C1 and D1.
- In cell E1 type the word ‘Max Points’
- In cell F1 type the word ‘Grade’
- In cell A7 type ‘Average Score’
- Fill in some sample grades for test #1 in cells B2, B3 and B4.
- To calculate the average for test #1, click inside cell B7. Then type the following “formula” inside the field (without the quotes): ”=AVERAGE(“
- Next, highlight the fields you wish to average. For example, to average cells B2 to B4, highlight these cells with your mouse. Then click the Enter button on your keyboard.
- If you did it right you should see the average grade appear in cell B7. You can change grades for students in cells B2 to B4 and watch as the average automatically updates itself.
- Repeat this process for tests #2 and #3
- Fill in the maximum number of points in cells E2 through E4. For example, if all three tests are worth 100 points, type 300 in each cell.
- In the grade field we will calculate another formula to calculate the student’s particular grade. Type the following in cell F2 (without the quotes): “=SUM(” and highlight the students grades. Next, type the closing parenthesis [")"] and then type the following without the quotes: ”/E2″. This will sum up the students grades and then divide the sum by the total number points possible.
- You can copy the F2 cell into cells F3 and F4 – the formula will automatically update for each student.
- You can set up “conditional formatting” on the cell to tell Excel to automatically change the color of the cell based on the content. For example, if you want top grades to show up as green, middle grades as yellow and low grades as red, you can do the following
- Highlight cells F2 through F5
- Click on the Format menu and then on Conditional Formatting
- A “rules” box appears. This lets you define what happens to this cell based on certain criteria. For example, if you want grades of 90% or higher to be listed as green, type the following. Note that I am using fractional values (0.9 through 1.0) since we are dealing with percentages.
Conditional Formatting
- Next, click the Format button and select ‘Pattern’ – I selected Green as the cell color for grades that are between 0.9 and 1.0.
- You can add in any number of “rules” to a conditionally formatted cell.
- You can also visually chart information on your spreadsheet. Here’s how:
- Let’s start by charting students vs. their test #1 score. To do this, highlight the student names as well as their test #1 score (cells A2 through cells B5)
- Click on Insert -> Chart
- Select a column chart
- You can also select ranges that are not adjacent to one another. For example, to chart tests vs. test averages, highlight cells B1 through D1. Then hit the Control key on your keyboard and highlight cells B7 through D7. Note that this kind of selection does not work on Google Docs (a known issue … they are looking into it)
Pivot Tables
Excel Templates
History
The following is an interesting video that covers the invention of the spreadsheet in 1979. As I mentioned in class, the first spreadsheet (Visicalc) was dubbed a “killer application,” which means that people would purchase an entire computer system just so they could have the opportunity to run it. Visicalc dramatically drove up sales for Apple because, at the time, it was the only computer that had the ability to run the software. As a consequence, the popularity of Visicalc helped to inspire IBM to enter into the PC market in the early 1980′s so they could compete directly with Apple for the future of the PC.
). You can then upload your photos to the site and watch as your own 3D walkthrough comes to life! Once finished, Photosynths can be embedded on your website or blog.