Earth, Environmental and Geographic Sciences Excel Tutorial

Excel is a powerful tool for scientists, researchers, and students working in Earth, environmental, and geographic fields. This tutorial guides you through the most useful functions, datavisualisation techniques, and workflow tips that can turn raw field or laboratory data into clear, publishready results.

Why Excel Matters in Earth & Environmental Sciences

• Data handling: Easy import of CSV, TXT, and GISgenerated tables.
• Statistical analysis: Builtin functions for descriptive statistics, regression, and hypothesis testing.
• Mapping & spatial visualization: Quick creation of choropleth maps, scatterplots with geographic coordinates, and heat maps.
• Reproducibility: All steps can be recorded with Excel macros or saved as Power Query queries.

1. Getting Started Importing Your Data

Most Earth science datasets come as CSV files (e.g., climate stations, water quality logs, or mineral assay tables). Use Data Get & Transform From Text/CSV to load the data. In the preview window:

1. Check that the delimiter matches (commonly a comma or semicolon).
2. Set the correct data type for each column (date, number, text).
3. Click Load to place the table on a new worksheet.

2. Cleaning and Organizing Data

Cleaning steps are often repetitive, so you can record them as a macro or use Power Query.

Removing blanks and duplicates

=FILTER(A2:D1000, (A2:A1000<>"") * (COUNTIF(A2:A1000, A2:A1000)=1))

Standardizing units

If your temperature column is a mix of Celsius and Fahrenheit, add a helper column:

=IF(E2="F", (C2-32)*5/9, C2)

Copy the formula down, then replace the original column with the new values (Copy PasteValues).

3. Basic Statistical Tools

Excels statistical functions are sufficient for exploratory analysis.

4. Visualising Spatial Data

While GIS software is ideal for detailed mapping, Excel can quickly produce locationbased plots.

Scatter Plot with Geographic Coordinates

1. Select the latitude and longitude columns.
2. Insert Scatter Scatter with Straight Lines.
3. Format the series: add data labels, change marker size, and use a map background image (Insert Pictures) set behind the chart.

Creating a Choropleth Map with Conditional Formatting

Assume you have a table of average annual precipitation for each U.S. state.

1. Insert a shape (rectangle) for each state and assign a name (e.g., TX for Texas).
2. Link each shapes fill colour to a cell using a VLOOKUP and Conditional Formatting rule.
3. Update the data, and the map recolours automatically.

For more sophisticated maps, export the cleaned data to a GIS program (QGIS, ArcGIS) after the Excel preprocessing stage.

5. TimeSeries Analysis

Environmental monitoring often involves long time series (e.g., daily river discharge). Excels Data Forecast Sheet creates simple trend forecasts, while the Analysis ToolPak offers moving averages and exponential smoothing.

Moving Average Example

=AVERAGE(OFFSET(B2,0,0,7,1))

This calculates a 7day rolling mean for the series in columnB. Drag the formula down and chart the result alongside the raw data.

6. Automating Repetitive Tasks with Macros

Recording a macro captures every click. To start:

1. View Macros Record Macro.
2. Give it a name like CleanEnvData.
3. Perform the cleaning steps (remove blanks, convert units, apply formatting).
4. Stop recording.

The generated VBA code can be edited for flexibility. Example snippet that converts a temperature column:

Sub ConvertTemp()    Dim rng As Range, cell As Range    Set rng = Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)    For Each cell In rng        If cell.Offset(0, 1).Value = "F" Then            cell.Value = (cell.Value - 32) * 5 / 9        End If    Next cellEnd Sub    

7. Sharing and Publishing Results

• PDF export: File Export Create PDF/XPS.
• Interactive dashboards: Use slicers and timelines on PivotTables to let viewers explore different time spans or variable subsets.
• Collaboration: Save the workbook to OneDrive or SharePoint and coauthor in real time.

8. Frequently Asked Questions

Can Excel handle large raster datasets?

Not directly. Raster data should be processed in GIS or programming environments (Python, R). Excel excels at handling tabular datause it for attribute tables and summary statistics.

Is there a way to import shapefiles?

Yes via the Power Query From File From Folder option after exporting the shapefile attributes to CSV, or by using the free Excel4GIS addin.

How accurate are Excels statistical tests?

For simple descriptive statistics and linear regression they are reliable. For more complex models (e.g., mixedeffects or Bayesian analysis) consider R or Python.

9. Next Steps Extending Your Skills

Once comfortable with basic Excel techniques, explore:

• Power Query: Automate data extraction from web services, APIs, and databases.
• Power Pivot: Work with millions of rows using the DAX language.
• Python integration: Use xlwings or openpyxl to run Python scripts on Excel data for advanced modeling.
• Geospatial addins: Tools like Mapline, GIS for Excel, or ArcGIS Maps for Office add true GIS capabilities.

By mastering these Excel techniques, you will be able to clean, analyse, visualise, and share Earth, environmental, and geographic data efficientlysaving time for the scientific interpretation that truly matters.