Excel Skills Workbook — Step by Step

Open the data & save as a workbook

1. Download both files above. Double-click deals.csv to open it in Excel.
2. Click File → Save As, set the format to Excel Workbook (.xlsx), and name it Whetstone_Excel_Skills.xlsx.a .csv can't store formulas, pivots, or formatting — you must save as .xlsx first.
3. At the bottom, double-click the sheet tab and rename it Deals.
4. Click the + next to the tabs to add a new sheet; rename it Agents. Open agents.csv, press Ctrl+A then Ctrl+C to copy everything, click cell A1 on the Agents sheet, and press Ctrl+V.

Format dates & remove duplicate rows (Deals sheet)

1. Format the dates. Click the G column header to select the whole column, then hold Ctrl and click the H and I headers too. Press Ctrl+1, choose Date, pick a format, click OK.
2. Remove duplicates. Click any cell in the data. Go to Data → Remove Duplicates. In the box, click Unselect All, then check only DealID, and click OK.the file has 3 exact duplicate deals. De-duping on the unique ID (DealID) removes them — a real first step on any messy export.

Four helper columns (Deals sheet)

Each one is a new column at the right end. Put the header in row 1, the formula in row 2, then fill it down.

1. Clean agent names — column K. In K1 type Agent (clean). In K2 type =TRIM(PROPER(B2)), press Enter, then fill down.the Agent column (B) is messy — random spaces and capitalization. TRIM strips extra spaces, PROPER fixes the capitalization. Now the names exactly match the Agents table, which the lookup in step 3 needs.
2. Weighted $ — column L. In L1 type Weighted $. In L2 type =E2*F2, fill down. Then select L2 to the bottom and click the $ (Currency) button on the Home tab.Value × Probability = the realistic, risk-adjusted dollar amount of each deal. Analysts weight pipelines this way instead of trusting the full sticker value.
3. Team — column M. In M1 type Team. In M2 type =XLOOKUP(K2, Agents!A:A, Agents!B:B, "—"), fill down.XLOOKUP takes the clean agent name (K2), finds it in the Agents sheet's column A, and returns that agent's team from column B. The "—" is what shows if no match is found.
4. Priority — column N. In N1 type Priority. In N2 type =IFS(F2>=0.7,"High", F2>=0.4,"Medium", TRUE,"Low"), fill down.IFS checks conditions in order: 70%+ → High, else 40%+ → Medium, else Low. The final TRUE is the catch-all "otherwise."

Aggregate with SUMIFS / COUNTIFS / AVERAGEIFS

1. Add a new sheet and name it Summary.
2. Weighted pipeline by stage. In A1 type Stage, in B1 type Weighted Pipeline. In A2 down to A8, type the 7 stages: Lead, Qualified, Showing, Offer, Under Contract, Closed Won, Closed Lost. In B2 type =SUMIFS(Deals!L:L, Deals!D:D, A2), press Enter, fill down to B8, then format B2:B8 as Currency.reads as: "add up column L (Weighted $) on the Deals sheet, but only rows where column D (Stage) equals the stage in A2."
3. Deals per agent. In D1 type Agent, E1 type Deals. In D2:D7 type the 6 agent names (copy them from the Agents sheet). In E2 type =COUNTIFS(Deals!K:K, D2), fill down.
4. Average deal size per agent. In F1 type Avg Value. In F2 type =AVERAGEIFS(Deals!E:E, Deals!K:K, D2), fill down, format Currency.

PivotTable + PivotChart

1. Go to the Deals sheet and click any cell inside the data. Click Insert → PivotTable → OK (it defaults to a new sheet). Rename that new sheet Pivot.
2. A PivotTable Fields panel opens on the right, listing your column names. Drag: Stage into the Rows box · Team into Columns · Weighted $ into Values (it should read "Sum of Weighted $").a PivotTable summarizes thousands of rows into a small table in seconds — here, weighted pipeline broken out by stage and team, with no formulas at all.
3. Click inside the pivot, then PivotTable Analyze → PivotChart → Clustered Column → OK.

Conditional formatting (Deals sheet)

1. Heat-map probability. Click F2, press Ctrl+Shift+↓ to select the column. Home → Conditional Formatting → Color Scales → pick the green-white-red one.
2. Bars on value. Select E2 to the bottom the same way. Conditional Formatting → Data Bars → pick a fill.
3. Flag stale deals (no activity in 30+ days). Select the data block from A2 across and down to the last row (through column N). Conditional Formatting → New Rule → "Use a formula to determine which cells to format." In the box type =(TODAY()-$I2)>30. Click Format → Fill, pick red, OK, OK.TODAY() is the current date; column I is LastActivityDate. The $I locks it to that column while the row number moves down each row, so every row is tested against its own date.

One clean chart (Summary sheet)

1. On Summary, select A1:B8 (Stage + Weighted Pipeline). Click Insert → 2-D Column chart.
2. Click the chart title and type Weighted Pipeline by Stage. Remove the legend if it's not adding anything. Keep it clean — no 3-D, no clutter.

Save & push to GitHub

1. Save (Ctrl+S). Sanity check: click a few summary numbers and confirm each shows a formula in the formula bar — nothing should be typed in by hand.
2. Upload the .xlsx to your github.com/benwhetstone/data repo, in an excel/ folder. The repo README already lists it as a portfolio artifact.
3. Tell Claude when it's up and we'll link it on your portfolio page.