How to Use a Data Spreadsheet: Excel
One does not necessarily have special statistical software to perform statistical analyses. Microsoft Office Excel can be used to run statistical procedures. Although in some respects Excel is not as preferable for data analyses as IBM SPSS, it is very userfriendly with simpler statistical procedures. This appendix describes how to use Excel to execute basic statistical calculations. Data from the 2004 version of the General Social Survey (GSS) is used for examples. This Appendix is based on Excel 2007 version, which differs in certain aspects from Excel’s previous versions. The most notable change that affects the exercises presented in this appendix concerns the pivot table feature.
To start Excel using Windows, click on the Start button at the bottom left corner of the screen. Under Programs locate and click the Microsoft Excel icon. The layout of the Excel program has changed substantially for the Microsoft Office 2007 edition compared to its predecessors. Commands are now grouped in ribbons that are accessed by clicking on a specific tab. Thus, the Home tab grants access to a ribbon of several command groups: Clipboard, Font, Alignment, Number, Styles, Cells, and Editing.
Once the program is started you will see a Worksheet Area that consists of cells forming columns and rows. Rows are identified by numbers, and columns are identified by letters. Consequently, each cell has its own unique address – a combination of letters and numbers. For example, cell C6 is in column C, row6. The dark rim around a cell means that the cell is highlighted or active. You can highlight a range of cells by clicking and dragging the cursor across several adjacent cells.
At the bottom left of the screen you will find worksheet tabs labeled Sheet 1, Sheet 2, etc. You can rename these worksheets, add additional ones or delete ones you do not need.
The data you need to use might be saved in a format other than Excel (file.xls). The data file we will be using in this tutorial is saved in SPSS format (file.sav). This is not a
problem. Open the data file in SPSS. Click File – Save As – choose Excel 97 and later (.xls) in the Save as Type drop down menu. The file is ready to be open in Excel. To do so – either double-click on the icon of the saved Excel file or, if Excel program is already open, click Office button at the top left of the screen - Open – locate the data file you need to work with.
Looking at the Data:
Once the data is open you will discover that the view is somewhat different than what you saw in SPSS program. Variable names are displayed in the first row (row A). Unfortunately, there is no variable view worksheet in Excel, and transferring the file from SPSS to Excel results in a loss of variable labels and value labels. Keep the codebook for the data on hand!
As you are exploring the data you might want to take advantage of the Sorting tool, which allows you to sort data by two or more variables in ascending or descending order. To sort your data, click the Data tab, find the Sort and Filter command group, and click on the Sort icon. A dialog window will open where you will specify the variables and order you want your data to be sorted by. If the names of variables (variable labels) are entered in the first row of your data make sure you check the “My data has headers” box at the top right of the dialog window. You can sort numeric data as well as text (In A to Z order). If you want to sort by more than one variable click Add Level button at the top of the dialog window. Click OK.
If you have converted an SPSS file to be used in Excel, be mindful of how missing data was coded in the original file. If missing data was entered as blanks, you have nothing to worry about. However, if missing data in the original data file was entered as a certain numerical expression (e.g. zeroes or 99)...
Please join StudyMode to read the full document