• 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 2007. Procedures for other versions of Excel will be similar, but menu items may be located in different places. Click here for instructions in Excel 2003.

Importing the data into Excel

1. Open Excel and select “Open” from the Office Button drop-down menu
2. In the “Open” dialog box, select “All files” in the drop-down menu next to the file name box 
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. Now, sort your data according to depth:
   1. Select one of the boxes in the “press” column
   2. Select Home>Sort and Filter>Custom sort...
   3. Select the check box by “My data has headers”
   4. Under “Sort by,” choose “press” and click OK
8. Be sure to save the file as an Excel Workbook by selecting File>Save As...



9. Repeat this procedure for Phosphate data; save as an Excel file
10. Repeat steps 1–5 for the BATS data
11. 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 Home>Sort and Filter> Filter
    2. Click on the arrow next to “PO4”
    3. In the dialog box, click on “Select All,” then select “-9.99;” click OK
    
    
    
    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 Row”
    
    
    
    5. Click on Home>Sort and Filter> Filter
12. Next, sort the data by depth:
1. Click on Home>Sort and Filter>Custom 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” (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 columns A and B, and paste the data (CTRL+V)
3. Rename the new columns “HOT” and “Pressure,” respectively
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>Scatter>Scatter with Straight Lines. Your graph should look like this:



2. In order for the y-axis to show depth from top to bottom, you'll need to reverse the order of the values:
   1. Select Layout>Axes>Primary Vertical Axis>More Primary Vertical Axis Options...
   2. Check the box in front of “Values in reverse order” and click OK
3. Now, you need to add the BATS data to your graph:
   1. Select Design>Select Data
   2. Under the “Legend Entries (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
4. While you have the “Select Data Source” box open, you can change the name of your HOT data series :
   1. Under the “Legend Entries (Series)” box, click on the “Pressure” series and click “Edit”
   2. In the “Series Name” box, type “HOT” and click OK; click OK again
5. Your graph should now look something like this:



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



9. 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)
10. Your final graph should look something like this:



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

---

Back to top