1.10 Sampling and data: using spreadsheets to view and summarize  (Page 2/6)

Basic elements of Excel: Ribbon, cell address, formatting cells, columns, rows, wrap text, etc: Let’s take an in depth look at the Excel and Google Spreadsheet. When we talk about a cell address we are identifying the row and column of the cell. If you click on column C row 3 in a spreadsheet, the row and column will be highlighted and in Excel just to the left of the formula bar you will see the cell address (row and column identity)

Creating worksheets: When we are working with data, one of the first steps will be creating a “worksheet.” Worksheets allow users to organize their data and create a clear record of the data analysis process. You will begin by labeling the “Original Data” as original data. To do this, place your curser over the tab at the bottom of the Excel or Google Spreadsheet file and right click on the tab marked “Sheet 1.” Of the many options (which you can explore on your own time) is the option “Rename.” This option will allow you to give the tab a more descriptive name. For our first exercise, label this first tab, “Original Data”. Then, create a new tab by clicking on the + tab before or after the sheet tab. When you click on the + symbol a new sheet will appear. You can now name that worksheet “Working Data.” It is always a “best practice to never change you original data (even if it contains errors) but to make of “working” copy of your data to “clean” or modify. Once you have created a new tab labeled “Working Data,” you will copy all your data from the Original data sheet and paste it to the working data sheet.

Copy and Paste: There are many ways to copy and paste in Excel and Google Spreadsheet. One of the easiest ways to copy all your data from the original data sheet to place it on your new “working data” sheet is to place your cursor in the box above the row numbers and to the left of the column alphabet, then click. Once you click all the data will be highlighted. With the data highlighted you can proceed to copy and then to paste on to the new worksheet you created. To copy in Excel you will use the clipboard on the home tab of the ribbon. The picture below the scissors is the copy function. Place your cursor on the picture (looks like duplicate sheets) and click. Then move your cursor to the “working data” tab you created, click on that tab and it will be highlighted. Proceed to the ribbon at the top of Excel again, place your cursor over the paste sign which is to the left of the scissors and the copy function. Click on the paste sign and your data is copied. To copy in Google docs, proceed with your cursor to the ribbon and left click on the “Edit” tab, you will then see the copy function. Left click on copy, proceed to your “working data” tab and then left click on the “Edit” tab again, you will see the paste function. Left click on the paste function and your data will appear. You can also use these principles to copy data from Google Spreadsheet to Excel or vice versa.

“Cleaning” your Data: Often times, even when directions are clear, data will include unwanted labels or data that was entered into a field of the spreadsheet that was in a format unrecognized by Excel or Google Spreadsheet (such as, 5-10 hours which the spreadsheet may read as 5-10 or May 10 ^th ). Once you have created the “Working Data” sheet you can change the data so that it is functional as either categorical or quantitative data. Data that you wish to use as quantitative data cannot have any words in the cell. The cells in Excel or Google Spreadsheet must only contain numbers if you want it to treat the column as quantitative. Of course, the name of the column will be in the first row, but other than that, all the cells below the column name must be numbers only. If you have a cell with inches and feet, a range from 2-5, or a unit that is not specified or consistent with the data label then you will need to change the cell to represent the unit specified. If you were given a range for an answer rather than a specific number, you will need to decide if you will use consistently the lowest number, the middle number, or the largest number from the range. As long as you are consistent throughout the data file, and note the changes you have made in the narrative of your data collection or analysis process, then you can proceed. Remember you are only making changes to the working data sheet. If there is a question, you can always refer back to the original data sheet. To clean the data, simply, proceed to the column and row or cell in question with your cursor, left click on the cell and remove the marks that will interfere with summarizing the data, make sure that only one unit is consistently used for quantitative data. In the example you were provided, you will need to change all the height data to inches only and remove the inches and feet symbols. Fraction or decimal numbers are appropriate as long as they are consistent with the unit specified. You will also need to remove any other units specified within a cell. Units can only be in the column headings. If there are misspellings or different spellings in categorical columns for one item which is intended to be the same, you would also need to make those corrections. As we proceed to summarize data, you will not be able to do that if the summary contains the same item more than once, you may need to go back to your working data to fix the error. Such as if gender had female and Female in the same column, when you summarized the data, Female would be counted and summarized separately from female. If something like this occurs during data summary, then come back to this step to “clean” the data