Webbo3 Data Analytics · Cohort 2 · Excel Track · Week 5, Virtual 2
Final Pod Review and Submission Preparation
The last class of the Excel track. Your pod goes through the Verdant Foods workbook one tab at a time, checks every figure that can be checked, and gets it ready to hand in.
By the end of this class you can
- Check every tab of the workbook against the brief
- Verify the figures that must agree across tabs
- Make every formula visible and every chart title a finding
- Submit a complete, polished, end-to-end analytics workbook
Five weeks ago this was a raw file with four kinds of problem in it. Today it should be a workbook a stranger could open, follow from raw data to decision, and trust. This class is the last read-through before it leaves your hands.
What you do in this class
- Open the shared workbook together (one person sharing their screen is easiest).
- Go through Parts 1 to 5 in order, one tab each, ticking the checks.
- Fix anything that fails, and log any data change in the Cleaning Log.
- Do the final checks in Part 6 and submit, as described on the Week 5 pod task page.
Hand in: The complete Verdant Foods workbook, all five tabs, in the shared folder.
The Workbook at a Glance
Part 1: Raw Data
| Check | How | Must show |
|---|---|---|
| Untouched | Select column A, read Count in the status bar | 471 |
| No formulas, no helper columns | Press Ctrl + End: where does it land? | J471, the last cell of the original data |
Part 2: Cleaning Log
| Check | Must be true |
|---|---|
| Four issues logged | Duplicates, dates, Category, blank Quantity |
| Every row has a count | 15, 10, 30 and 20 |
| Every row has a method | Specific enough that a stranger could repeat it |
| Any later change logged too | If you fixed anything in Weeks 3 to 5, it is here |
Part 3: Cleaned Data
| Check | How | Must show |
|---|---|---|
| Rows | Count on column A | 456 |
| Dates | =ISTEXT on column B, or sort it | No text; every date in 2025, shown yyyy-mm-dd |
| Categories | Filter list on F | Four values |
| Quantity_Flag | Filter K for Missing | 20 of 455 |
| Revenue | =SUM(L2:L456) and =COUNT(L2:L456) | N84,828,800 and 435 |
Part 4: Analysis
| Check | Must be true |
|---|---|
| All eight questions answered | Q1 to Q8, each in its own labelled block |
| Formulas, not typed numbers | Click any answer: the formula bar shows a formula |
| Formulas visible | A FORMULATEXT column next to every answer |
| Blocks add up | Regions, categories and products each total N84,828,800; reps total 455 |
| Eight meaning sentences | Each compares, explains and, where it can, suggests |
Press Ctrl + ` (the key left of 1) to switch the whole sheet to formulas. Scan for any answer cell that shows a plain number instead of a formula: that is a typed answer, and it must become a formula. Press Ctrl + ` again to switch back.
Part 5: Dashboard
| Check | Must be true |
|---|---|
| KPI row | Three formula boxes that match Q1, the transaction count and the overall average revenue per transaction |
| Three charts | Region (bar, sorted, biggest on top), Category (bar), Month (line, January to December) |
| Titles | Every title states a finding, with a number |
| Colour | Navy plus one accent, no 3D, no rainbow |
| Slicer | Customer_Type, connected to all three PivotTables (click it and watch) |
| Insights | Three, one sentence each, at the bottom |
| Names and date | Every pod member's name and the submission date, on the Dashboard tab |
| Week 5 feedback | At least two improvements from your tutor's and your peer's feedback are in |
Part 6: The Final Checks
- Refresh everything: Data, Refresh All. Every PivotTable must still match the Analysis tab afterwards.
- Click each tab and press Ctrl + Home so the file opens at the top left of every sheet.
- Finish on the Dashboard tab, then save. The file opens on the tab that was active when it was saved.
- Close the file and open it again from the shared folder, to be sure the saved version is the finished one.
Picture a pilot's pre-flight checklist. The pilot has flown hundreds of times and still reads every line aloud, because the one line skipped is the one that matters.
Checkpoint
Q1. Ctrl + End on Raw Data lands on column M. What does that tell you?
Something was added to Raw Data, which must be untouched. Remove it.
Q2. Show Formulas reveals a plain number in an answer cell. What do you do?
Replace it with the formula that calculates it.
Q3. Why open the file again from the shared folder after saving?
To be certain the saved, shared version is the finished one, not an older copy.
Tick Before You Move On
- ☐ Raw Data untouched
- ☐ Cleaning Log complete, with counts and methods
- ☐ Cleaned Data: 455 rows, four categories, real dates, 20 Missing, Revenue N84,828,800
- ☐ Analysis: eight answers, formulas visible, blocks that add up, eight meaning sentences
- ☐ Dashboard: KPIs, three charts, finding titles, one connected slicer, three insights, names and date
- ☐ Refreshed, tidied, saved on the Dashboard tab, and reopened from the shared folder
Quick Recap
Check tab by tab, in the order the work was done. Figures must agree across tabs: that agreement is the proof the work is right. Formulas visible, titles that state findings, names and the date on the Dashboard.
Next: Pod Task Week 5, Final Submission. What exactly to hand in, and how it is checked.