• Home
• News & Information
• Ocean Observatories
• Research & Development
• Data & Images
• Marine Operations

 

Marine Technology

Buoys main page

Graphing Data in Excel

Note: These directions provide step-by-step instructions for creating graphs in Excel 2003. Procedures for other versions of Excel will be similar, but menu items may be located in different places. Click here for instructions in Excel 2007.

Importing the data into Excel

1. Open Excel and select File>Open
2. In the “Open” dialog box, select “All files” in the “Files of type” field 
3. Locate your Nitrate document and select it, then click “Open”



4. Click on the bubble “Delimited”, then click “Next”
5. Add the “Comma” as a delimiter by clicking on the box next to it, then click “Finish”



6. Remove the cells with the units in them:
   1. Select row 2 (click on the row number to the left of the row)
   2. Click Home>Delete
   
   
   
7. You need to remove the label “\par” from the pressure column
   1. Select Edit>Replace...
   2. Type “\par” in the “Find what” box
   3. Leave the “Replace with” box blank
   4. Click “Replace All,” then click OK, then click “Close”


8. Now, sort your data according to depth:
   1. Select one of the boxes in the “press” column
   2. Select Data>Sort
   3. Make sure the button next to “Header Row” is checked
   4. Under “Sort by,” choose “press” and click OK
9. Be sure to save the file as an Excel Workbook by selecting File>Save As...
10. Repeat this procedure for Phosphate data; save as an Excel file
11. Repeat steps 1–5 for the BATS data
12. Before the data can be used, the depths without nutrient data need to be removed. 
    (When no nutrient data is available, BATS reports it as “-9.99”)
    1. Click on Data>Filter>Auto Filter
    2. Click on the arrow next to “PO4”
    3. In the drop-down menu, select “-9.99”
    
    
    
    4. Delete rows 2 through 149:
    1. Click on the number 2 on the left-hand side and drag the cursor down to highlight all rows below
    2. Right click on the row labels (the numbers on the left-hand side)
    3. Select “Delete Rows”
    
    
    
    5. Click on Data>Filter>Auto Filter
13. Next, sort the data by depth:
1. Click on Data>Sort
2. In the “Sort by” drop box, click on “Depth;” click on OK

Preparing data for graphing

1. Open a new Excel worksheet.  Save it as “Nitrate”
2. Return to your HOT Nitrate spreadsheet.  Copy the following columns:  “nit” ( Nitrate+Nitrite) and “press\par” (Depth):
   1. Select both columns by placing your cursor over the letter at the top of column F, then click and drag to highlight both columns F and G
   2. Copy the columns (CTRL+C) , then move to your new spreadsheet, select the first two columns, and paste the data (CTRL+V)



3. Rename the columns “HOT” and “Pressure”
4. Return to your BATS spreadsheet.  Copy the following column: “NO3” (Nitrate+Nitrite): 
   1. Select the column by placing your cursor over the letter at the top of column D
   2. Copy the column (CTRL+C) , then move to your new spreadsheet, select column C, and paste the data (CTRL+V)
5. Rename the column “BATS”
6. Copy the “Depth” column (column C in your BATS spreadsheet) and paste into column D in your Nitrate spreadsheet



7. Open a new Excel worksheet.  Save it as “Phosphate”
8. Repeat steps 2 through 7 for the phosphate “phos,” “press,” “PO3,” and “depth” columns in your HOT and BATS phosphate files

Creating a graph

1. Select columns A and B (the HOT data) and select Insert>Chart...
   1. In the Chart Wizard, select the Chart Type “XY (Scatter)”
   2. Choose the Chart sub-type “Scatter with data points connected by lines without markers”
   3. Click “Next,” then “Finish”



2. Your graph should look like this:



3. In order for the y-axis to show depth from top to bottom, you’ll need to reverse the order of the values:
   1. Click on the y-axis and select Format>Selected Axis...
   2. Check the box in front of “Values in reverse order” and click OK
4. Now, you need to add the BATS data to your graph:
   1. Select Chart>Source Data...
   2. Under the “Series” box, click “Add”
   3. In the “Series Name” box, type “BATS”
   4. In the “Series X Values” box, select the icon with the red arrow, then go to your spreadsheet and click and drag to select cells C2-C59; click on the arrow icon again
   5. Repeat this procedure for the “Series Y Values” box, selecting cells D2-D59; click OK
5. While you have the “Source Data” box open, you can change the name of your HOT data series :
   1. In the “Series” box, click on the “Pressure” series
   2. In the “Series Name” box, type “HOT” and click OK; click OK again
6. Your graph should now look something like this:



7. Now, you have to give the BATS data its own axis:
   1. Click on the pink BATS line, then right click to bring up the Data Series menu; select “Format data Series...”
   2. Click on the “Axis” tab
   3. Click the button in front of “Secondary Axis” and click OK
8. Use the procedure in step 3 to reverse the values on the Secondary Vertical Axis
9. Your graph should now look like this:



10. Finally, use the Chart Layout tools to add chart and axis titles.
    1. Chart title: Nitrate vs. Depth
    2. Value (X) Axis: Nitrate (µmol/kg)
    3. Value (Y) Axis: Pressure (dbar)
    4. Second value (Y) Axis: Depth (ft)
11. Your final graph should look something like this:



12. Use these same procedures to create a graph comparing HOT and BATS phosphate data.

---

Back to top