You can do every week’s data analysis without writing code. Start with Excel to understand the calculations, then use Orange in the second half of the semester to build spectral models by dragging and dropping widgets. If you want to go further, run Python in Colab or Jupyter. All three tools use the same data and should give the same answers.
Beginner · Every student
Excel
One practice workbook per week for W1–W8. The formulas are already written, with a note beside every cell; paste in the class data and everything recalculates.
Mean, SD, confidence interval, t-test, ANOVA
Calibration curve, LOD/LOQ, z-score, control chart
Beginner · Main tool for the second half
Orange
Analyze by dragging widgets and connecting them, no coding needed. Ready-made workflow files cover spectral preprocessing, PCA, and PLS.
A Python notebook for each week. They can do what Excel and Orange cannot: VIP, PLS-DA, T² and Q, NNLS, simulation.
Colab: opens in the browser, no installation
Jupyter: download to your own computer, runs offline
Weekly overview
Click Excel to download the practice workbook. The Orange workflow files are all in one zip file (data included). Colab opens in your browser; .ipynb downloads the notebook for Jupyter.
Week
Topic
Beginner: Excel
Beginner: Orange
Advanced: Python
Everything built in is simulated teaching data. With the built-in data, the instructions sheet (sheet 0) of each Excel workbook has an answer-check table that should show ✔ in every row, meaning your Excel and Colab give the same results.
How to use the Excel practice workbooks
Every workbook has the same layout
Sheet 0 (instructions): learning goals, steps, and the answer-check table.
Sheet 1 (raw data): the same long format as Colab (group, student, item, rep, value…).
Sheets 2, 3…: one sheet per step, with a note beside each formula saying what the function does.
Practice questions: write your answers in the yellow cells and hand them in with your report.
Yellow type or paste · Green formulas, do not edit · Orange results to copy into your report
Switching to the class data
Open the class Google Sheet, select the data (without the header row) → copy.
In the workbook’s raw-data sheet (sheet 1), click A2 → paste. The column order must match the header.
If the class has fewer rows than the example, delete the entire extra rows of old data.
If the student list or number of groups is different, edit the yellow column at the far left of each step sheet.
Quick reference: common functions
What you want to do
Function
First appears
Mean, sample standard deviation
AVERAGE, STDEV.S (not STDEV.P)
W1
Half-width of the 95% confidence interval
T.INV.2T(0.05, n−1) * SD / SQRT(n)
W1
Conditional mean, count, sum
AVERAGEIFS, COUNTIFS, SUMIFS
W1
Conditional SD (array formula)
{=STDEV.S(IF(condition, values))}; in older Excel press Ctrl+Shift+Enter
W1
p-value of a t-test
T.DIST.2T(|t|, df), or T.TEST(range1, range2, 2, 1) (paired)
W1, W2
Find where the maximum is (equivalence point)
MAX + MATCH + INDEX
W4
Calibration curve
SLOPE, INTERCEPT, RSQ, STEYX, LINEST
W7
F-test, ANOVA p-value
F.DIST.RT(F, df1, df2), F.INV.RT(α, df1, df2)
W7, W8
Expanded uncertainty
SQRT(SUMSQ(each u)) × k
W8
Cross-check with the Analysis ToolPak (t-test, ANOVA, regression)
The workbooks calculate everything with formulas so you can follow each step. To cross-check with Excel’s built-in tools:
Windows: File → Options → Add-ins → at the bottom, “Manage: Excel Add-ins” → Go → check “Analysis ToolPak”. Afterward, “Data Analysis” appears at the far right of the Data tab.
Open Orange → Options → Add-ons → check Spectroscopy → OK, then restart Orange. Required for the spectroscopy weeks (from W11).
Download orange_workflows.zip and extract it into one folder (keep the .ows workflow files and the .csv data together).
Orange → File → Open → choose that week’s .ows.
The workflows were made and tested with Orange 3.40 and Spectroscopy 0.9; please use these or newer versions.
Switching to your class data
Double-click the leftmost File widget → click the folder icon → choose your CSV.
In the column table below File, double-click “Role” to set each column: set the Y value (e.g., acidity_pct, oil) to target; set text or ID columns such as sample_id, group, rep to meta; leave the spectral wavelength columns as feature.
Click Apply, and the widgets downstream recalculate automatically.
Tip: if the CSV headers are written as c#acidity_pct and m#sample_id, Orange automatically recognizes them as target and meta. All the example data on this page are set up this way.
The three most common mistakes: ① In the PCA widget, uncheck “Normalize variables” (spectra are only mean-centered); ② connect the score plot to the PCA Data output so you can color points by class; ③ in Test and Score, use “Cross validation by feature → sample_id” so replicates of the same sample are not split apart, otherwise RMSECV is too optimistic.
Colab and Jupyter (advanced)
Colab: no installation
In the weekly overview, click Colab and sign in with your Google account.
Runtime → Run all. The notebook has built-in simulated data, so it runs right away.
To use the class data: follow the “How to use” section in the notebook, either publish the Google Sheet as a CSV link or upload the file.
To keep your own changes: File → Save a copy in Drive.
Download all notebooks + data (zip) and extract it; or click .ipynb in the overview to download a single notebook.
Open JupyterLab, go to the extracted folder → open a notebook in notebooks/ → Run → Run All Cells.
All 13 notebooks were tested in a standard Jupyter environment, so they run completely without Colab.
Study tips for students and teachers
Excel first, then Orange, then Python. W1–W8 require only Excel; from W11 only Orange is required; Colab is an advanced option that earns bonus points (see the assessment page).
Check your answers with a second tool. Run the same data in two tools; you are done only when the numbers agree. For example, get the RMSEP in Orange for W13, then confirm it in Colab NB11. Being able to explain why the two agree matters more than just pressing buttons.
Look at the plot before the numbers. Glance at the box plot, residual plot, and score plot first, then read the p-value and RMSE.
One “tool tutor” per group. Take turns; the tutor helps teammates solve software problems, so the teacher only needs to answer the tutor in class.
5-minute warm-up before class. At the start of each week, have students answer one question using last week’s file (e.g., “Why is the CI for G1_S3 especially large?”), which also checks that everyone can open the files.
Errors are teaching material too. The example data deliberately include recording errors, outliers, and spectra from the wrong instrument, so students can practice finding them.