Data Analysis with Excel

Spreadsheet Data Analysis

A common task for scientists and engineers is to analyze data from an external source that may be in a text or comma separated value (CSV) format. By importing the data into a spreadsheet program like Excel, data analysis such as statistics, trending, or calculations can be made to synthesize the information into relevant and actionable information. The following worksheet details some of the common data analysis functions within Excel.

This following tutorial demonstrates how to import data, perform a basic analysis, trend the results, and export the results to another text file.

This tutorial can also be completed with scripting programming languages like MATLAB and Python. Click on the appropriate link for additional information and source code.

Generative AI Learning

Use these prompts to test your understanding after completing the tutorial. Direct the AI - import and analyze the data yourself; use it to pressure-test your conclusions.

"Quiz me with 5 questions, one at a time, on analyzing measured data in a spreadsheet: what mean, median, and standard deviation each tell you and when the median is the better summary, how to spot an outlier and what you must decide before deleting one, why sample standard deviation (STDEV.S) differs from population (STDEV.P), what a histogram reveals that summary statistics hide, and what can silently go wrong when importing a CSV or text file (delimiters, dates, text-as-numbers). Grade my answers and list my misconceptions."
"Here are my summary statistics and my conclusion from this data set: {paste statistics and one-sentence conclusion}. Attack the conclusion as a skeptical senior engineer: what alternative explanations fit the same statistics, what plot would distinguish them, and what would you need to know about how the data was collected? Then ask me 2 questions to check I understand the difference between a trend and noise."

Modern data: The same import-summarize-trend workflow runs a 3D-printing lab: layer temperature, extrusion rate, and dimensional error logged for every part. A histogram of dimensional error and a trend of error vs nozzle temperature is exactly this tutorial applied to manufacturing process data - try it on any process data set you can export.

What to Turn In

Submit a short report (PDF, 1-2 pages) with your homework that curates your results into a demonstration of what you learned. You may use Generative AI to help write the report, but you must guide it to the correct visualizations, justifications, and assumptions. Answer these questions:

  1. Import a data set (your homework data, or a process/energy data set) and report mean, median, standard deviation, min, and max, with units. Verify the mean of one column with a hand calculation on a small subset.
  2. Include a histogram or trend plot and state one engineering conclusion the plot supports. Say what the plot shows that the summary statistics alone did not.
  3. Identify one suspicious point (outlier or import artifact). What did you decide to do with it, and on what basis?
  4. From the skeptic prompt: what alternative explanation did the AI raise, and how did (or would) you rule it out?
  5. From the quiz prompt: one question you missed and the corrected answer.

Course Information

Excel and VBA

Python

MATLAB

MathCAD

Related Courses

Admin