Download Spreadsheet Basics: Using Microsoft Excel for Data Organization and Calculations and more Lab Reports Computer Science in PDF only on Docsity!
Lab 3.
Spreadsheet Basics
Spreadsheets and databases are very similar applications. They are both useful for the organized storage of information. However, spreadsheets are simpler than databases and are typically used for different purposes, so this lab will discuss when spreadsheets are used and the basics of how to use them. We will use the popular Microsoft Excel spreadsheet application in this lab, but these concepts and procedures will be useful for using any spreadsheet application. At the end of this lab, you should not only be able to use a spreadsheet, but also be able to recognize problems that a spreadsheet can be useful for solving. Finally, working with spreadsheets will prepare you for databases, the focus of the next labs.
Vocabulary
All key vocabulary used in this lab are listed below, with closely related words listed together:
worksheet cell row column data type formula, parameter
Post-Lab Questions
Write your answers after completing the lab, but read them carefully now and keep them in mind during the lab.
- Suppose you want to start writing some notes on a book you are reading, and you want to store them on your computer. Which would be a better suited program to use for this purpose: a word processor or a spreadsheet? (You may answer that both are equally well/badly suited, if you wish.) Explain your answer.
- Spreadsheets allow us to present charts, graphs, and other visualizations of data. Describe one reason why the ability to visualize data can be valuable.
Discussion and Procedure
Part 1. What are spreadsheets good for?
Structuring data. Spreadsheets are most commonly used to store and perform calculations on numeric data. Examples of data like this include a checkbook record, business transaction records, data from scientific experiments (e.g., daily rainfall), results of a survey. The first advantage of spreadsheets is that data can be stored in a structured way, making it easy to retrieve and present the data in different ways. Imagine, for example, keeping track of your expenses in a notebook, where you might list your expenses like this:
17 Jan 2002, phone bill, $32. electric bill, 25 Jan, $18. rent, Feb 1, $ Feb 12, cable TV, $ Feb 18, phone, $52. Feb 26, electric bill, $21. (etc.)
Storing the same data in a spreadsheet has several advantages over handwritten a record, many of which come from the structure that spreadsheets put on data. A spreadsheet is organized like a table, with cells arranged in columns and rows. This overall structure keeps the format of the entries consistent and makes the data more readable.
In addition to the overall structure provided by rows and columns of cells, you can specify the format (e.g., bold, italics…just like in a word processor) and data type (e.g., date, text, integer, real number, monetary amount) of a cell. In the example spreadsheet shown above, the cells in the first column are formatted as dates, so they are all displayed with the same month/day/year format. Similarly, the cells in the third column are formatted as currency amounts (in dollars), so they are all displayed with two decimal places and the dollar sign.
- Enter column names. Editing a cell is as easy as clicking the cell or using arrow keys to move to the cell you wish to edit, then typing the data value that you want in the cell. As shown below, set up column names in the first cells of Row 1 as shown below, using the names of the activities you chose in Step 1 above.
- Format the columns according to data type. Select the “date” column by clicking the gray “A” column label. From the Format menu, select Cells…. This should bring up the Format Cells dialog, as shown below. Under Category , select Date , and in the Type list that appears on the right side, select one that includes the year, in addition to the month and day. (Any one of these types will do.) Click OK when you are done and format your two activities columns to use Category “Number” with 0 decimal places, making the assumption that your timings will just be rounded to the nearest hour.
- Enter a row of timing data. Begin by entering today’s date in Row 2’s “date” column. Note that because this column is formatted to store dates, you can enter the date in a variety of different ways and the spreadsheet will automatically display it in the format you selected. For instance, try entering “1/4/02”, “1-4- 02”, “jan 4, 02”, or “4 jan 2002”. Excel will interpret what you enter as a date and convert it to the format you selected. For the other columns, for the purposes of this lab, just make up numbers for the number of hours for each of your activities.
Assuming you intend to record data on a daily basis, you will have to fill in each day’s date in the “date” column. Typing these in by hand is not only tedious, but error-prone,
so we will use a feature called “fill” to automatically enter a series of dates in this column.
- Select about seven cells in the “date” column, starting from the cell containing the date you just entered in the last step. To select a group of cells, use the mouse and drag over the cells you want to select. You can select any rectangular region of cells this way.
ALTERNATIVE: First, decide which rectangular region of cells you want to select. Click the cell in the upper left corner of the region you want to select, then while holding down Shift , click the lower right corner of the region. (You can actually do this with any two opposite corners of the region you want to select.)
Before you go on to the next step, make sure that you have the cell with the date you just entered and several cells directly below it selected.
- Fill the selected region. From the Edit menu, select Fill \ Series… , and make sure Series in Columns , Type Date , Date unit Day , and Step value 1 are selected. These options specify that (1) the series you want runs down a column, as opposed to across a row, (2) that the data type in these cells is Date, and that (3) the dates in this series should go up by one day per cell.
- Enter timing data for four more days. Again, for the sake of this lab, just fill in estimates.
- Delete the date values for the remaining rows. For now, we’ll just work with the rows of data that are completely filled, so clear the date values for the remaining rows, if any. Just select the cells you want to clear and press Delete.
- Set up a total time cell for your second activity by copying and pasting the formula. Although you could follow the same instructions as above to set up your second activity’s total time cell, there is an easier way. Select the first activity’s total time cell and copy it by clicking the toolbar Copy button ( ), selecting Edit \ Copy or pressing Ctrl-C. Select the cell where you want the second activity’s total time and paste by clicking the toolbar Paste button ( ), selecting Edit \ Paste or pressing Ctrl-V.
Note that the contents of the new total time cell has a formula that is similar but not identical to the original. Excel guesses that you want the new total time cell’s formula to work on the column of cells above it, rather than be an exact copy of the first total time cell.
- Set up an average daily time cell for your first activity. Select the cell where you want the average daily time for your first activity to be displayed. This time, instead of clicking the AutoSum button, click the Paste function button ( ). This should bring up the Paste Function dialog box, in which you should select Function category Statistical and Function name AVERAGE. Click OK when you are done.
- Select the range of cells the average should be calculated over. When you click OK , you should see the dialog box below, which you should use to specify which cells’ values you want to average. (You can drag this dialog box out of the way so that you can see your data and the dialog box at the same time.)
Excel has guessed for you that cells B2 through B7 are the ones you wish to average, but this probably includes one more cell than you really want, i.e., the total time cell you created earlier. You can correct this by typing in the Number entry box and replacing B7 with B6, or you can select the contents of the Number1 entry box and select the cells you want (B2 through B6) on the worksheet.
If you want to go back and change the range of cells for this formula later, you can select the cell and click the = button in the formula bar.
- Reformat the average time cell to show two decimal places. Recall that the rest of this column is formatted to show no decimal places. Since an average is likely to have a decimal part, you should format this cell to show at least two decimal places. Select the cell and select Format \ Cells… or press Ctrl-1 ( Ctrl-one ) to open the Format Cells dialog box.
- Create a properly formatted average time cell for your second activity. Just as in Step 14, the copy-and-paste method is fastest.
Part 4. Charts for Visualizing Data
Visualizing data in charts can often make it easier to interpret. In this part, you will use Excel to create a chart showing daily time spent on your activities, like the one shown below.
Part 5. Sorting Data
The way you have entered your timing data, the rows are ordered by date. You might, however, be interested in seeing which days you spent the most time on a certain activity. More generally, you might be interested in sorting your rows by a column other than date.
- Select the region of cells whose rows you wish to sort. Select the same region of cells you selected for the chart in Step 19, i.e., all non-empty cells except those in the total and average rows. Make sure you include the first row of column names in your selected region.
- Specify the sorting order you want. Select Data \ Sort… and use the drop-down lists to select the column(s) whose values you wish to sort by. In this case, choose your first activity under Sort by , and, if you want ties to be broken by sorting by a value in another column, select it in the first Then by list. Click OK when you are ready to sort.
Note that the rows in the selected region are in the specified order, but your chart does not change. This is because your sorting did not change data in each row; it just reordered the rows. The chart itself is not affected by the row order.
Optional Additional Activities
- Create a pie chart showing how your total time splits across the activities you chose.
Further Reading
- Do a web search for “spreadsheet history” to find more information about spreadsheets, including the history of the application, which was first introduced in 1978 by Robert Frankston and Dan Bricklin as the application Visicalc.
