Difference between revisions of "Tutorials:10 Using Microsoft� Excel� to format GSSHA� data"

Latest revision as of 18:24, 9 March 2009

Disclaimer: GSSHA tutorial exercises do not represent real world conditions

Splitting an Excel® column

In this example you will copy a text file from Notepad and paste it into Microsoft® Excel® where it can be manipulated and formatted. Specifically, you will take information from a single column and split it into multiple columns using two different methods.

1. Use Notepad to open the file titled “date1.txt”, located in the Excel_Tutorial folder.
2. Highlight all the data starting with the first row down to the final row.
3. Copy this data by right-clicking on the highlighted data and selecting “copy”.
4. Open a Microsoft® Excel® worksheet and select the entire first column by clicking on the “A” at the top of the column.
5. Right-click on the “A” column and select “Format Cells”.
6. In the “Category” box pick text, and then click “OK”.
7. Select cell “A1”, then right-click on the cell, and select “paste”.



You will notice that the data has been pasted so that the year, month, and day values are contained in a single column. To make it so that each value has its own column, you will use the “Text to Columns…” command.



8. Select the entire column by clicking on the letter “A” directly above the column.
9. Select the “Data” menu and click on the “Text to Columns…” command (this will bring up the “Convert Text to Columns Wizard”).
10. Because the data is separated by the character “/”, toggle “Delimited” and select “Next”.
11. Check the box titled “Other” and type the “/”character (without quotation marks) into the box provided to the right of the “Other” delimiter.
12. Click “Finish” to exit the wizard.



You should now see three columns that represent values for year, month, and day.

The preceding example shows how data can be separated based on a delimiter such as a comma, space, or other character. However, not all data has a distinct character that divides values. In cases such as these it becomes necessary to divide data based on width. The following steps illustrate how this may be done.



13. Use Notepad to open the file titled “date2.txt”, located in the Excel_Tutorial folder.
14. Repeat steps 2 – 9 (above.)
15. Toggle “Fixed width” and click “Next” (because the data is not separated by any distinct characters, we divide the column based on width.)
16. In the “Data preview” window, create a break line between the year and the month by clicking between the 2 and the 1 on any row, and then make another break line between month and day by clicking between the 1 and the 0.
17. Click “Finish” to exit the wizard.



You should now see three columns that represent values for year, month, and day.

Note

When copying and pasting data from Notepad into Excel®, it is useful to format the destination column to be “text” before you paste—the text format will not alter a number or delete zeros that precede a number. Also, make sure the cells to the right of the column you are dividing are empty.

Using the AutoFilter

In this exercise you will be utilizing the AutoFilter option to locate various cells in an Excel® spreadsheet and modify them.

1. Open the Excel® file “filter.xls”.
2. Select column “C” by clicking on the “C” at the top of the column.
3. Click on the “Data” menu and select “Filter”, then select “Auto-Filter”.
4. You will see a small box with a black arrow inside (the AutoFilter box) appear in the first cell of the column. Click on the AutoFilter box.



The dropdown menu that appears under the AutoFilter box shows the unique values of all the cells below the AutoFilter box. By selecting one of the values you are determining which rows will be displayed, and which rows will be hidden.



5. Scroll to the bottom of the AutoFilter dropdown menu and select “(Blanks)” to display the blanks cells in column “C”.



All rows that do not have blank values in column “C” have been hidden. The row numbers for the remaining rows have been changed to blue in-stead of black.



6. Enter the number “0” in the first blank cell of Column “C”.
7. Repeat step 6 for each cell in column “C” that has a row number written in blue (you can use the AutoFill capabilities of Excel® if you wish).
8. Click on the AutoFilter box again and scroll to the top of the menu, then select “(All)” to show all rows.



Now there are no blank cells in column “C”.

The AutoFilter can be used in a variety of ways, and the following exercise will walk you through additional uses. Let’s say we are only interested in looking at the precipitation values that are larger than 0.1. The following example shows how we can customize the display options of the Auto-Filter.



9. Click on the AutoFilter box in the first cell of column “C”.
10. In the AutoFilter dropdown menu select “(Custom…)” to modify which cells you would like to display.
11. Click the top-left drop down box and select “is greater than or equal to” for the AutoFilter to display all rows that are greater than or equal to the value you specify.
12. In the next box over (to the right) type “0.1” (without quotation marks.)
13. Select “OK”.



Now you can view all the cells in column “C” that have values of 0.1 or lar-ger. If you want to delete the cells smaller than 0.1 you would have to change the custom filter to display the cells less than 0.1, then you could select the displayed rows and delete them.

Find And Replace

The find and replace command can be very useful when formatting data. This tool can be used to delete, replace, or modify cell values. The following exercise will show you how to use the replace command, as well as how it can be applied to formatting data.

1. Open the Excel® file “replace.xls”.
2. Select column “C” by clicking on the “C” at the top of the column.



In column “C” you will notice that the values are hours of the day, followed by a “z” which stands for “Zulu” or Greenwich time. If we want to use these numbers in GSSHA™ we will have to remove the “z” from each cell. To do this we could split the column, however, using the replace command works equally as well.



3. Select the “Edit” menu, and then click “Replace…”
4. In the text box below “Find what:” type “z” (without quotation marks.)
5. Leave the text box below “Replace with:” empty, which means that the “z” in each cell will be replaced with nothing.
6. Select “Replace All” to replace all the z’s in column “C” with noth-ing.



Instead of replacing the z’s with nothing, you could have replaced it with a letter or a number. If you wish, try repeating the exercise, and at step 5 enter in a number or word or whatever you would like. The replace com-mand can be used to replace words, letters, or numbers, in rows or in col-umns.

General Information

The following commands can be useful when formatting data in Excel®

• Saving an Excel® file as a text file
  • Make your active sheet the one you would like to save as a text file.
  • Select the “File” menu, and pick “Save As…”
  • Enter a new name for the file in the box next to “File name”.
  • Select the drop down box for the “Save as type” and pick: “Text (Tab delimited) (*.txt)”.
  • Then click “Save”.
  • Click “OK” at the prompt.
  • Select “Yes” at the next prompt.
  • The text file will be saved in whatever folder you specified.

• Moving Columns
  • To move an entire column, click on the letter at the top of the column.
  • The entire column will be selected, with two heavier black lines on either side of the column.
  • Put the pointer over one of these heavier dark lines, then when the cursor changes to an arrow, click on the black line.
  • While holding down on the left mouse button, drag the column to the destination of your choice.

GSSHA Tutorials

GSSHA Tutorial Download Website