Excel: Beginner to Advanced with AI

Learning Objectives

• Convert a range into a named Table.

• Rename Tables for business meaning (no Table1, Table2).

• Write structured-reference formulas inside Tables.

• Frame GPT prompts using Table and column names.

Tasks

• 1. Open 00_Master_Sales_Practice.xlsx → Raw_Sales. Confirm the data is already a Table named SalesData (Table Design tab shows the name).

• 2. Add a new column GrossRevenue inside the Table. Use =[@Quantity]*[@UnitPrice]*(1-[@Discount]). Confirm Excel auto-fills the entire column.

• 3. Add a second column MarginPercent. Use =IF([@UnitPrice]=0, "", ([@UnitPrice]-[@Cost])/[@UnitPrice]). Confirm the blank protection works on any zero rows.

• 4. Open ChatGPT. Write a strong prompt referencing SalesData and request a formula that returns a flag 'HighMargin' when MarginPercent exceeds 40 percent.

• 5. Paste GPT's formula into a new column HighMarginFlag and verify it on three rows.

Success Criteria

• SalesData Table contains the three new columns auto-filled to every row.

• MarginPercent returns blank when UnitPrice is zero (no DIV/0!).

• GPT prompt used in the LearningLog includes the Table name and column names explicitly.

Stretch Challenge

Rename the Table to SalesData_2025 and update every dependent formula. Observe how structured references update automatically.

Learning Objectives

• Use Ctrl+Arrow, Ctrl+Shift+Arrow, and filters for fast navigation.

• Apply consistent number formats (currency, percent, date, text IDs).

• Set up data validation lists and numeric ranges to prevent invalid entries.

Tasks

• 1. Open 00_Master_Sales_Practice.xlsx → Inputs. Confirm the Selected Region and Selected Category dropdowns work.

• 2. On Raw_Sales, apply explicit number formats: Quantity as whole number, UnitPrice and Cost as Currency, Discount as Percent (1 decimal), OrderDate as yyyy-mm-dd.

• 3. Freeze the header row on Raw_Sales (View → Freeze Top Row).

• 4. Add a new Status column to the Inputs sheet with a Data Validation list: Open, In Progress, Closed. Test that typing a different value triggers an error.

• 5. Add a validation rule for Tax Rate that accepts only values between 0 percent and 30 percent.

Success Criteria

• All five formats applied consistently with no mixed display.

• Freeze pane active on Raw_Sales.

• Both validation rules reject invalid input with a clear error message.

Stretch Challenge

Design a one-page validation rulebook for any workbook you build at work — list at least five fields and the allowed values you'd enforce.

Learning Objectives

• Recognise where inputs, calculations, and outputs belong.

• Identify and redesign workbooks that mix the three.

• Frame GPT prompts using the three-zone vocabulary.

Tasks

• 1. Open 00_Master_Sales_Practice.xlsx. Identify which sheet plays each role: input, calculation, output.

• 2. On Calc_KPIs add a new metric: 'Revenue in Selected Region AND Category'. Reference both Inputs!B1 and Inputs!B2.

• 3. On Output_Dashboard add a new KPI card linked to the new metric.

• 4. Change the Inputs values (Region → East, Category → Apparel). Confirm Calc_KPIs and Output_Dashboard update without you touching them.

• 5. On the Notes sheet, document the three zones and the dependencies between them.

Success Criteria

• Changing inputs updates calculations and outputs automatically.

• No hard-coded region or category appears in any formula.

• Notes sheet clearly lists the three zones.

Stretch Challenge

Take any work-related workbook you've built recently and redesign it into the three zones. Save the before and after for your own records.

Learning Objectives

• Choose the correct reference type when building drag-down formulas.

• Express simple business rules with IF.

• Build conditional totals and counts with SUMIFS and COUNTIFS.

Tasks

• 1. Open 00_Master_Sales_Practice.xlsx. Create a new sheet named Region_Summary.

• 2. In column A, list each Region (North, South, East, West, Central).

• 3. In column B, write a SUMIFS formula that returns total Revenue for that Region from SalesData. Use Quantity × UnitPrice × (1-Discount) — you may need a helper column on Raw_Sales for Revenue.

• 4. In column C, write a COUNTIFS formula that returns the order count for that Region.

• 5. Add a Tax Rate cell at the top of Region_Summary. In column D, multiply column B by (1 + Tax Rate) using an absolute reference so the formula drags safely down all five regions.

Success Criteria

• All formulas drag down without breaking.

• Changing the Tax Rate updates every row in column D.

• No hard-coded region names appear in formulas — they reference column A.

Stretch Challenge

Extend with a second dimension: build a SUMIFS that filters by both Region and Category.

Learning Objectives

• Recognise #DIV/0!, #N/A, #REF!, #VALUE!, #NAME?

• Follow the five-step debugging sequence.

• Use GPT to explain root cause, not just patch the symptom.

Tasks

• 1. Open 01_Debug_Formulas.xlsx → Bugs_To_Fix. Each row contains a broken formula stored as text.

• 2. For each bug, copy the formula text into a fresh cell on the Data sheet to reproduce the error.

• 3. Apply the five-step debugging sequence: read, inspect references, check data types, test parts, then fix.

• 4. Write your corrected formula in column E of Bugs_To_Fix. Verify it returns the expected business answer (not just a non-error).

• 5. Open ChatGPT and use this prompt for bug 3: 'This formula returns N/A in Excel 365: XLOOKUP of Karim Nasr against Data B2:B9. Explain why it fails and show two corrected versions.' Compare its answer against the Solutions sheet.

Success Criteria

• All six bugs reproduced in live Excel cells.

• All six fixes produce correct business values, not just suppressed errors.

• GPT prompt log shows the reasoning request, not just a fix request.

Stretch Challenge

Create a seventh deliberate bug in any of your own workbooks, hand it to a colleague to debug, and observe their sequence.

Learning Objectives

• Use UNIQUE and SORT to derive clean lists dynamically.

• Use FILTER for inline exception lists.

• Use XLOOKUP with a clean 'not found' branch.

• Understand spill behavior and version dependencies.

Tasks

• 1. Open 00_Master_Sales_Practice.xlsx. Create a new sheet named DynamicArrays.

• 2. Cell A1: =SORT(UNIQUE(SalesData[Region])). Confirm five regions spill down.

• 3. Cell C1: =SORT(UNIQUE(SalesData[Category])). Confirm spill.

• 4. Cell E1: Use FILTER to spill all SalesData rows where Discount > 10 percent. Provide a 'No matches' value for the empty case.

• 5. Cell G1: Build an XLOOKUP that, given a Product entered in cell F1, returns the SalesRep of the first matching order. Show 'Product not found' when missing.

• 6. Add a row to SalesData with a new Region 'Online'. Confirm the UNIQUE spill in A1 updates automatically.

Success Criteria

• All four spills update when the source Table changes.

• FILTER returns at least one row when Discount > 10 percent.

• XLOOKUP handles missing values without #N/A.

Stretch Challenge

Replace at least one VLOOKUP in any work workbook with XLOOKUP — and document the difference in your LearningLog.

Learning Objectives

• Define what a valid value looks like in a column before touching the data.

• Apply TRIM and CLEAN to fix invisible text issues.

• Coerce text-dates into real dates and resolve the downstream failures they cause.

• Classify blanks (missing vs. not applicable vs. failed import) instead of ignoring them.

• Decide whether duplicates are errors or valid transactions based on context.

• Use GPT to draft a cleanup checklist while keeping execution and verification in Excel.

Tasks

• 1. Open 02_Messy_Data_Cleanup.xlsx and read the Cleanup_Checklist sheet.

• 2. In Cleanup_Workspace, apply TRIM and CLEAN to Customer and Region to produce clean columns.

• 3. Use DATEVALUE (or Power Query if you prefer) to convert the text-formatted OrderDate column into real dates. Confirm by sorting ascending — dates should sort chronologically, not alphabetically.

• 4. Inspect the three blank Amount cells. Add a column called BlankReason and label each one Missing, NotApplicable, or FailedImport. Justify each label in one short sentence.

• 5. Add a COUNTIF column that flags duplicate OrderIDs. Decide which duplicates are legitimate (e.g., a second purchase same day) and which are erroneous (same row repeated). Mark each duplicate Keep or Remove.

• 6. Open ChatGPT. Paste the column headers only (no data). Ask: 'Suggest a step-by-step cleanup checklist for this sales file. Do not write formulas.' Save the reply in a new sheet named GPT_Plan.

• 7. Compare GPT's checklist with what you actually did. Note any step you skipped or added.

Success Criteria

• Clean columns contain no leading, trailing, or doubled spaces (verified visually or by LEN comparison).

• OrderDate column is real dates that sort chronologically.

• Every blank has a documented classification.

• Every duplicate has a Keep/Remove decision with reasoning.

• GPT plan is saved alongside the work for traceability.

Stretch Challenge

Rebuild the same cleanup using Power Query (preview only — you'll learn the full mechanics in V16). Compare effort and reusability against the formula approach.

Learning Objectives

• Answer the four lookup design questions before writing any formula.

• Build XLOOKUP with the if_not_found argument explicit, not assumed.

• Build the same lookup with INDEX/MATCH and read the structure clearly.

• Diagnose a failed lookup by checking both the formula and the key values.

• Use GPT to draft lookups while still validating the key relationship yourself.

Tasks

• 1. Open 03_Lookup_Design.xlsx. Read the Lookup_Design sheet — it lists the four design questions for this exercise.

• 2. On the Orders sheet, add a new column Category. Build an XLOOKUP that pulls Category from the Products Table by ProductCode, returning "Missing Product" if not found.

• 3. Add a second column Price. Use INDEX/MATCH to return Price for each ProductCode. Confirm it produces the same Category-row Price as XLOOKUP would.

• 4. Add a third column LineTotal = Quantity * Price.

• 5. Locate Order 17. Its ProductCode is P999 (missing on purpose). Confirm Category shows "Missing Product". Investigate whether the issue is a missing product in the master, or a key formatting issue.

• 6. Add a fourth column CodeLen = LEN(ProductCode). Any rows where length is unexpectedly large indicate a trailing space — fix one and re-run the lookup.

• 7. Open ChatGPT. Paste your two Table headers. Ask: 'Recommend a single XLOOKUP that returns Category, with explicit not-found behaviour, and explain why match_mode 0 is the safe default.' Compare to what you wrote.

Success Criteria

• Every row in Category and Price has a value (no #N/A).

• Order 17 displays "Missing Product" gracefully.

• LineTotal sums correctly across all rows.

• You can explain in one sentence why if_not_found should never be omitted.

• You can explain the structural difference between XLOOKUP and INDEX/MATCH.

Stretch Challenge

Add a fifth column that uses XLOOKUP in reverse-lookup mode (match_mode 0, search_mode -1) to find the most recent order per product. Document why search_mode matters.

Learning Objectives

• Translate a business rule into a clean nested IF or IFS formula.

• Choose between nested IF, IFS, and helper-column approaches based on maintainability.

• Build readable summary labels (e.g., 'West, Below target, Margin risk') from rule logic.

• Prompt GPT for two formula designs and explain the maintenance trade-off.

Tasks

• 1. Open 04_Business_Rules.xlsx. Read the Rule_Design sheet — it states each rule in plain English.

• 2. On SLA_Tracker, populate Status with a nested IF that returns Overdue / Due Soon / On Track using today's date. Verify with one ticket at each band.

• 3. Add a second Status column using IFS instead of nested IF. Confirm both produce identical results.

• 4. On Sales_Attainment, populate AttainmentBand with IFS using thresholds 90% and 100%.

• 5. Add a SummaryLabel column with TEXTJOIN to produce labels like 'North, Near Target, On margin'. Use the existing Region and a simple margin flag.

• 6. Open ChatGPT. Paste your headers and the rule in plain English. Ask: 'Give me two formula designs for this rule (nested IF and IFS) and explain which is easier to maintain.' Save the answer in a new sheet GPT_Comparison.

• 7. Write one sentence per design recording your own conclusion — which would you use, and why?

Success Criteria

• SLA Status correctly labels at least one Overdue, one Due Soon, and one On Track ticket.

• Nested IF and IFS columns produce identical values.

• AttainmentBand is consistent with the documented thresholds.

• SummaryLabel reads naturally and is no longer than ten words.

• GPT_Comparison sheet records both designs and your recommendation.

Stretch Challenge

Replace the nested IF with SWITCH where it makes sense. Document one scenario where SWITCH would be cleaner than IFS.

Learning Objectives

• State the business question in one sentence before placing any field.

• Build a baseline Pivot of revenue by region and category.

• Layer time filters, percentage of total, and target variance into the Pivot.

• Add slicers to support decision-making, not decoration.

• Sort by variance instead of alphabetical default.

• Use GPT for question framing before and pattern phrasing after — never for causes.

Tasks

• 1. Open 00_Master_Sales_Practice.xlsx. On a new sheet PT_Question_1, write your first question in cell A1 (e.g., 'Which region contributes most to total revenue, and by how much percentage?').

• 2. Insert a PivotTable from the SalesData Table. Rows: Region. Values: Revenue (sum) AND Revenue (% of Grand Total).

• 3. Sort the Pivot by Revenue descending. Confirm the top region is at the top, not the alphabetical first.

• 4. On a new sheet PT_Question_2, write a second question (e.g., 'Which product categories are underperforming target this quarter?'). Build the corresponding Pivot.

• 5. Insert one slicer on Month and connect it to both PivotTables (Slicer Settings → Report Connections).

• 6. Copy the values of your top Pivot into a clean range. Open ChatGPT and ask: 'Using only these verified numbers, summarise the pattern in two sentences. Do not infer causes.' Paste the reply into a sheet GPT_Pattern_Summary.

• 7. Read the reply critically. If it inferred any cause, rewrite it yourself in one sentence.

Success Criteria

• Both Pivots have a written business question in cell A1.

• Both are sorted by a meaningful metric, not alphabetically.

• One slicer is connected to both Pivots.

• GPT_Pattern_Summary contains your own rewrite if needed.

Stretch Challenge

Add a calculated field for Margin = Revenue * (1 − Discount). Build a third Pivot ranking categories by margin contribution.

Learning Objectives

• Place KPIs, trend, breakdown, and exceptions in a deliberate top-down reading order.

• Choose a chart type because it helps the audience, not because it looks advanced.

• Limit colour, decoration, and gridline noise on every chart.

• Use GPT to recommend a chart mix and to phrase chart titles — but own the final layout.

• Distinguish between a chart that shows data and a chart that tells a story.

Tasks

• 1. Open 00_Master_Sales_Practice.xlsx and go to Output_Dashboard.

• 2. Place three KPI cells in the top row: Total Revenue, Target Attainment % (use a stated target of 1,200,000), Average Margin (use 1 − Discount as proxy).

• 3. Below KPIs, insert a Line chart showing monthly Revenue trend from SalesData. Title it with the finding, not the field — e.g., 'Revenue trending up Q1 to Q2'.

• 4. Below the trend, insert a Clustered bar chart for Revenue by Region. Sort descending.

• 5. Below that, build an Exception list (use a small Pivot or FILTER): products where Discount > 12%. Title it 'Products under margin pressure'.

• 6. Apply a single accent colour throughout. Remove gridlines, legends, and chart titles that don't add value.

• 7. Open ChatGPT. Paste your four chart titles. Ask: 'Rewrite each in management language — focused on the finding, not the field. Keep each under 12 words.' Save the replies as a comment on each chart.

• 8. Compare and adopt or override each suggestion deliberately.

Success Criteria

• Dashboard reads top-down in the order KPIs → Trend → Breakdown → Exceptions.

• Every chart has a finding-based title, not a field-based one.

• Single accent colour across all charts.

• No legend, gridline, or label that doesn't serve the audience.

Stretch Challenge

Add a small 'How to read this dashboard' note in the bottom-left explaining the reading order — useful for a real management audience.

Learning Objectives

• Verify dashboard numbers before involving GPT.

• Write a strong, bounded summary prompt with audience, length, content boundaries, and tone.

• Compare each GPT statement against the source data.

• Reject inferred causes that aren't supported by the workbook.

• Use GPT for slide titles, dashboard subtitles, and action bullets — accelerating drafting, not judgment.

Tasks

• 1. Open 00_Master_Sales_Practice.xlsx. Confirm Output_Dashboard from V14 is complete and verified.

• 2. Add a sheet Verified_Metrics. List the figures your summary will rely on (Total Revenue, Attainment %, Top Region, Weakest Region, Margin pressure flag).

• 3. Open ChatGPT. Use this strong prompt: 'Using only these verified metrics, write a 90-word executive summary for a sales manager. Audience is operational, not technical. Mention overall trend, strongest region, weakest region, one action priority. Do not invent causes I have not provided.'

• 4. Paste your Verified_Metrics values into the prompt.

• 5. Read the reply line by line. For every statement, identify which metric supports it. Strike through anything inferred without support.

• 6. Edit the reply down to a clean 90-word version. Paste into Output_Dashboard as a 'Summary' text box at the bottom.

• 7. Ask GPT for three alternative chart titles in management language. Adopt or override each consciously.

Success Criteria

• Verified_Metrics lists every figure used in the summary.

• Final summary is approximately 90 words, no inferred causes.

• Every statement in the summary maps to a metric.

• Dashboard now carries the summary text box.

Stretch Challenge

Generate the same summary in two tones (operational and board-level). Compare and note which words changed.

Learning Objectives

• Recognise when manual cleanup should give way to Power Query.

• Connect to a folder or a workbook of monthly files.

• Apply transformation steps: change types, rename, remove, split, filter, append.

• Refresh and re-verify when new files arrive.

• Use GPT to design the transformation sequence in plain English before applying it.

Tasks

• 1. Open 05_Capstone_RetailSales.xlsx. Read the 00_Capstone_Brief sheet.

• 2. Data tab → Get Data → From Other Sources → Blank Query. In Power Query, use =Excel.CurrentWorkbook() to list all sheets. Filter to sheets whose names start with Sales_2025_.

• 3. Expand each sheet, promote headers, and confirm all three have the same column structure.

• 4. Apply transformation steps in this order: change data types (Date → date, Quantity and UnitPrice → number, Region and ProductCode → text), rename columns to a consistent convention, remove any 'Notes' or empty trailing column, filter out blank Date rows.

• 5. Append the three filtered queries into one combined query. Name it Sales_Combined. Close & Load to a new sheet.

• 6. Add a small new sheet Sales_2025_07 with five rows of fake July data using the same columns.

• 7. Data → Refresh All. Confirm Sales_Combined now includes the July rows.

• 8. Open ChatGPT. Describe the workflow in plain English and ask: 'What transformation sequence would you recommend for a recurring monthly sales workflow, and which steps are worth documenting in a checklist?' Save the answer in a sheet GPT_PQ_Plan.

Success Criteria

• Sales_Combined exists and contains rows from all included monthly sheets.

• Applied Steps panel shows a coherent, ordered sequence of transformations.

• Adding a new monthly sheet and refreshing extends the combined table automatically.

• GPT_PQ_Plan documents the sequence design conversation.

Stretch Challenge

Add a calculated column in Power Query for Revenue = Quantity * UnitPrice. Document why doing it in Power Query is better than in a worksheet formula for a recurring process.

Learning Objectives

• Define automation as process clarity before tooling.

• Ask 'what repeats?' as the first design question.

• Use GPT to convert a manual workflow into a numbered SOP with quality-control points.

• Distinguish steps suitable for automation from those requiring human judgment.

• Map your work onto a five-level automation ladder before reaching for code.

Tasks

• 1. Open 05_Capstone_RetailSales.xlsx. Add a sheet SOP_Monthly.

• 2. In plain English, list every step you (or a hypothetical analyst) take each month to refresh and ship this report. Aim for 6–12 steps, including manual ones.

• 3. Open ChatGPT. Paste the list and prompt: 'Turn this into a numbered SOP with quality-control points after each step. The audience is a junior analyst.'

• 4. Paste the SOP into SOP_Monthly. Annotate each step with one of the five ladder levels (1 = workbook structure, 5 = advanced automation).

• 5. Highlight every step you marked as level 4 or 5 — these are your real automation candidates.

• 6. Add a column 'Repeatable / Judgment'. Mark each step accordingly. Confirm at least two steps are judgment.

• 7. Add a column 'Tool' (Power Query, formula, manual, GPT) for each step. Look at the distribution — is it healthy?

Success Criteria

• SOP_Monthly contains 6–12 numbered steps with QC points.

• Every step is mapped to one of the five ladder levels.

• Every step is marked Repeatable or Judgment.

• Every step has a Tool assigned.

Stretch Challenge

Identify one step currently at level 3 that could be promoted to level 4. Write what would change in the workbook to make that promotion safe.

Learning Objectives

• Execute the analyst workflow in the right sequence: structure → clean → logic → analyse → present → summarise.

• Verify every GPT contribution with the five-question test.

• Deliver a single workbook that someone else can open, refresh, and read without help.

• Maintain the discipline that workbook establishes truth, GPT helps you communicate it.

Tasks

• 1. Step 1 — Structure. Add the five-sheet pattern: keep Raw_* sheets, create Clean (or Sales_Combined from Power Query), Calc, Output_Dashboard, Notes. Document the structure on the Notes sheet.

• 2. Step 2 — Clean. Run Power Query (from V16) to combine the three monthly sales sheets. Refresh and confirm row count.

• 3. Step 3 — Logic. On Calc, add columns for Attainment % (vs. Targets), Margin proxy (1 − Discount or similar), AttainmentBand (Exceeded / Near / Below), and an exception flag.

• 4. Step 4 — Analyse. Build at least two PivotTables answering specific written questions (e.g., 'Which region is furthest from target?', 'Which product line carries the highest margin risk?').

• 5. Step 5 — Dashboard. On Output_Dashboard, lay out KPIs → Trend → Breakdown → Exceptions, with finding-based chart titles.

• 6. Step 6 — Summary. Use the strong GPT prompt from V15 to generate a 90-word executive summary from your verified metrics. Edit, place into the dashboard.

• 7. Step 7 — Verify. For every GPT contribution in this build (formula suggestions, summary, chart titles), run the five-question test and record one sentence per question in a Verification sheet.

• 8. Step 8 — Deliver. Save as Capstone_Final_<YourName>.xlsx. Open a fresh Excel window, open the file from scratch, and read your own dashboard as if you were the audience. Note one thing you would tighten.

Success Criteria

• Workbook opens cleanly with five logical sheets.

• Power Query refresh works end-to-end on the combined data.

• Dashboard reads top-down following the four-zone order.

• Executive summary is approximately 90 words, no inferred causes.

• Verification sheet covers every GPT contribution with the five questions.

Stretch Challenge

Add a second Output sheet 'Output_Board' designed for an executive (not operational) audience. Reuse the same data but reduce to three KPIs, one chart, and a four-line summary.