This video series, example data files, and tips sheets (see attachments in video descriptions) is a compilation of principles and excel procedures useful for tribal environmental agencies who manage data, whether in a short term project involving only a few dozen data values and locations, to programs gathering data every minute from many monitoring stations.
It is loosely organized into the operations that most environmental data management systems must use: planning, importing (from websites, pdf reports), cleaning, charting, sorting, filtering, merging, flagging, appending, using datetimes, summarizing, automating data flow, and using a self-documenting system so that there is a clear and transparent trail of operations and decisions from each raw data point and QC check result.
For more information on this series, contact NEIEN@nau.edu.
It is loosely organized into the operations that most environmental data management systems must use: planning, importing (from websites, pdf reports), cleaning, charting, sorting, filtering, merging, flagging, appending, using datetimes, summarizing, automating data flow, and using a self-documenting system so that there is a clear and transparent trail of operations and decisions from each raw data point and QC check result.
For more information on this series, contact NEIEN@nau.edu.
-
Demonstration of importing sensor minute data (follow along using the raw comma separated values .csv data file attached to this video) into an excel file using the…
Excel 1 Import Data Using Legacy Wizard
-
Demonstration of the import of the same sensor data into excel as was demonstrated in the legacy import video, but this import is done using the self-documenting excel…
Excel 2 Import Anything Using Power Query
-
Very brief demonstration of how to name a range of data (and if this range is in an excel Table it will automatically expand to include more rows), which allows you to…
Excel 3 Use Named Ranges as Brain Savers
-
This video demonstrates using the same sensor data that has been used in the importing videos, and excel’s ability to understand geographic identifiers and…
Excel 4 Fun: Excel Map from Lat Long
-
Demonstration of how to summarize minute sensor data by average and max, by hour and day, using a pivot table. Caution: check for gaps in your data, which may not be…
Excel 5 Pivot Table for Summary Statistics
-
Continuing to use the minute sensor data, this demonstration is of inserting a pivot chart that can be collapsed and expanded to generate summary statistics by hour,…
Excel 6 Pivot Chart to Present Data
-
Demonstration of reproducibly importing one table from a multi page adobe report (NCAI.pdf, attached), to generate summary statistics, and a calculated field (population…
Excel 7A Excel Table From an Adobe File
-
This very short demonstration shows how to create a query that automatically goes to the EPA AQS air data codes database online, queries their website and filters…
Excel A: Introduction to Power Query by Importing…
-
This demonstration shows how to use MS Excel Power Query (with a user interface) to go from two excel files of 5 minute data, append the files, remove duplicates, filter…
Excel B: Multiple 5-min Files Cleaned Appended…
-
This video provides an overview of one way (free to anyone) to set up a form that can be accessed on a smartphone in the field, enabling users to type in the results of…
QC Data Sheets Pt 1-Field QC Data on Paper and…
-
Demonstration of establishing a free outlook.com account and setting up a form for smartphone or computer, for gathering field data that must be hand entered, such as…
QC Data Sheets Pt 2-Setting up the QC Form and…
-
Demonstration of how to query a RAW folder to find 1-only excel files, then 2-only the most recent excel file, and then 3- (following from the previous video) append…
QC Data Sheets Pt 3-Import Newest xlsx in Folder
-
Last video in a series of four, demonstrating how to:set up a query that uses a previous query as a source (the RAW data), generate calculated fields (% difference from…
QC Data Sheets Pt 4-Query To Control Chart
Search for ""