Graphing Instructions

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


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


  1. 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
  2. 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
  3. Be sure to save the file as an Excel Workbook by selecting File>Save As…


  1. Repeat this procedure for Phosphate data; save as an Excel file
  2. Repeat steps 1–5 for the BATS data
  3. 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


      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


    1. Click on Home>Sort and Filter> Filter
  4. 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:


  1. 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
  2. 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
  3. 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
  4. Your graph should now look something like this:


  1. 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”
  2. Use the procedure in step 3 to reverse the values on the Secondary Vertical Axis
  3. Your graph should now look like this:


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


  1. Use these same procedures to create a graph comparing HOT and BATS phosphate data.
  2. Return to the BATS & HOT DOGS Lesson Plan


Data repository
Data policy
Deep-Sea Guide
What is happening in Monterey Bay today?
Central and Northern California Ocean Observing System
Chemical data
Ocean float data
Slough data
Mooring ISUS measurements
Southern Ocean Data
Mooring data
M1 Mooring Summary Data
M1 Asimet
M1 download Info
M1 EMeter
Molecular and genomics data
ESP Web Portal
Seafloor mapping
Soundscape Listening Room
Upper ocean data
Spatial Temporal Oceanographic Query System (STOQS) Data
Image gallery
Video library
Creature feature
Deep-sea wallpapers
Previous seminars
David Packard Distinguished Lecturers
Research software
Video Annotation and Reference System
System overview
Data Use Policy
Video Tape User Guide
Video File User Guide
Annotation Glossary
Query Interface
Basic User Guide
Advanced User Guide
Query Glossary
VARS publications
VARS datasets used in publications
Oceanographic Decision Support System
MB-System seafloor mapping software
How to download and install MB-System
MB-System Documentation
MB-System Announcements
MB-System Announcements (Archive)
MB-System FAQ
MB-System Discussion Lists
MB-System YouTube Tutorials
Matlab scripts: Linear regressions
Introduction to Model I and Model II linear regressions
A brief history of Model II regression analysis
Index of downloadable files
Summary of modifications
Regression rules of thumb
Results for Model I and Model II regressions
Graphs of the Model I and Model II regressions
Which regression: Model I or Model II?
Matlab scripts: Oceanographic calculations
Matlab scripts: Sound velocity
Visual Basic for Excel: Oceanographic calculations
Educational resources
Navigating STEM careers
MBARI Summer Internship Program
2017 Summer Interns Blog
Education and Research: Testing Hypotheses (EARTH)
EARTH workshops
2016—New Brunswick, NJ
2015—Newport, Oregon
2016 Satellite workshop—Pensacola, FL
2016 Satellite workshop—Beaufort, NC
EARTH resources
EARTH lesson plans
Lesson plans—published
Lesson plans—development
Lesson drafts—2015
Lesson drafts—2016 Pensacola
Adopt-A-Float Program
Center for Microbial Oceanography: Research and Education (C-MORE) Science Kits
Science at home: Curriculum and resources
Sample archive
SciComm Resources