Manipulating CSV Data Files in Excel

The comma-separated-value file that you just received can be easily manipulated in Microsoft Excel to produce lists and reports of the data you asked for. If you are using Outlook for Windows, double-click on the attached CSV file and your data file should open in Excel. If you are using Gmail, click Download next to the csv attachment and it should open in Excel. If your data file is extremely large, it may have been compressed into a zip file. In this case, you can double-click (Outlook) or click (Gmail) on the zip file. This should open an Explorer window showing you the original csv file. Double-click on the csv file to open it in Excel.

If you are using a Macintosh, your computer may not automatically load the CSV into Excel. After saving the file to your desktop, ctrl + left click (two-button mouse), select Open With, and choose Microsoft Excel. (If Excel is not listed as a choice, select Other and then show All Applications.) You may wish to change your default settings so that MS Excel will be the default application for all CSV files. If so, follow the following steps:

1. Save file to desktop

2. Click once to select the file

3. Option (or Apple key) + I to Get Info

4. Open with section: Click in drop down box

5. Select MS Excel (you may need to scroll)

6. Click Change All button

7. Choose MS Excel

Working with the spreadsheet in Excel 2010

In order to more easily view and manipulate the data, I suggest you begin with the following two steps:

1. Choose Data; Filter. This will turn on the column filters, making it easy for you to select particular groups of data.

2. Click on the small box in the upper left corner (above the 1, left of the A), of the spreadsheet to select the entire sheet. Then point at the small vertical line between the A and B column headings. When the mouse pointer changes to show a two-headed arrow, double-click to size all of the columns in the spreadsheet at once.

To select a particular set of records, you can now click on the drop-down menu on a column heading, uncheck Select All, and check the value or values you are interested in.

If there are students that you do not wish to include in your report, you may right-click on any of their data fields and Delete,Entire row. Alternatively, you may right-click on a row number and choose Delete. Similarly, you can delete columns that are not of interest to you. If you wish to temporarily hide columns, right-click on the column letter and choose Hide.

To sort the data, click on any cell in the column you want to sort by and then click either the A-Z or the Z-A button on the Data ribbon. You may also choose Sort to sort on multiple columns.

You may want to format some numeric data columns, such as the ID, that are imported in Excel as numbers without leading zeros. To change the way these columns look, right-click on the column letter, choose Format Cells, select the Custom Category, and enter a format in the Type blank with leading zeros, such as 000000000 or 000-00-0000 or 0000-0000. (Many data sets contain a column named EXCELID that contains extra characters in order to force Excel to display the leading zeros in an ID.)

Summary reports

To generate report using your data, you will probably want to use an Excel pivot table. Although there are many options available, here is a very quick summary to produce a simple table. For this example, a data file containing ExcelID, Class, and Sex values for Undergraduate Applicants was used. The goal was to see counts by class and sex.

Start by choosing Insert; Pivot Table. If you have selected on of the cells in your table, the Create PivotTable wizard will correctly identify the data range. Click OK. You will now see a blank Pivot table and a list of your column names. To create this example, drag Sex to the Column Labels, Class to the Row Labels, and ExcelID to the Values. On the Design ribbon, choose Report Layout, Show in Tabular Form:

Many options are available to create more complex reports. A great deal of information about creating Pivot Tables is available through Help in Excel. Click the ? button and type “pivot table” in the search box to learn more.