SQL for Data Analysis Questions

How I choose the lookup method

• XLOOKUP is my default in modern Excel because it is easier to read, can look left or right, and has built-in handling for missing values.
• INDEX/MATCH is my backup when I want maximum compatibility or a more flexible array-based setup.
• VLOOKUP I only use for legacy files or when the table is simple and the lookup column is the first column.

Exact vs. approximate match

• For business reports, I usually want exact match for IDs, names, and codes.
• I use approximate match only for bands or thresholds, like commission tiers or tax brackets, where the lookup table is sorted and the rule is "next smaller" or "closest match."

Left lookups and missing keys

• XLOOKUP and INDEX/MATCH both handle left lookups cleanly.
• If a key may be missing, I return a friendly default like "Not found" or 0 instead of an error.
• If duplicates are possible, I first decide whether the business wants the first match, last match, or all matches. If duplicates are not allowed, I flag them with COUNTIF or a duplicate check before trusting the result.

Example

=XLOOKUP(A2, Customers[CustomerID], Customers[Segment], "Not found")

This is my first choice for a report because it is clear, exact by default, and easier to audit than VLOOKUP.

Structure I would use

• Raw data tab: one Excel Table holding the source data only.
• Calc/helper tab: normalized fields, date groups, and any lightweight helper columns.
• Dashboard tab: KPI tiles, a few trend charts, and slicers/timelines.

Formulas and layout

• Use the raw table as the single source of truth.
• Build KPI tiles with SUMIFS, COUNTIFS, or AVERAGEIFS when the numbers need to react instantly to filters.
• If the workbook is large, I would summarize first in PivotTables and point the dashboard to those results.
• Use named ranges or table references instead of hard-coded cell addresses.

Performance choices

• Avoid volatile functions unless they are truly needed.
• Prefer table references over whole-column formulas.
• Keep chart series small and focused.
• Use one or two helper columns for grouping instead of many nested formulas.
• Reduce duplicated logic by calculating once and reusing the result.

Drill-down and usability

• Add slicers for the main dimensions.
• Add a timeline for date filtering.
• Keep labels plain-language and numbers formatted consistently.
• Hide technical sheets so stakeholders only see the dashboard and the raw source if needed.

My rule is to make the dashboard fast to open, easy to refresh, and simple enough that a non-technical user can filter it without breaking the workbook.

Situation: In a previous role, a recurring report was built by copying data into several sheets, manually fixing headers, and retyping formulas each week. It was slow, and small copy-paste mistakes caused mismatches in the final numbers.

Task: I was asked to make the process faster, easier to use, and more trustworthy for the wider team.

Action:

• I mapped the full workflow and identified where errors were happening.
• I turned the raw input into a structured Excel Table and moved the transformation logic into reusable formulas and a refreshable summary sheet.
• I added simple validation checks for blanks, duplicate keys, and out-of-range values.
• I documented the steps so someone else could refresh the report without knowing the original build logic.
• I ran the old and new versions in parallel for a couple of cycles and reconciled the outputs line by line.

Result: The new process removed most of the manual handling, reduced rework, and made the report easier for others to maintain. Just as important, the team gained confidence because the checks made issues visible before distribution.

Learning: I learned that a good reporting fix is not just automation; it is automation plus validation and documentation.

How I would investigate it in Excel

1) Validate the export

• Check that the CRM extract has the right date range, required columns, and no obvious duplicates.
• Confirm whether sales means booked revenue, closed-won deals, or another definition.

2) Compare the right time windows

• Look at last week versus the prior week, but also compare against the same days in previous weeks so day-of-week effects do not mislead us.
• Separate the drop into volume, average deal size, and conversion rate if the CRM has pipeline stages.

3) Segment the problem

• Build PivotTables by region, rep, product, channel, and deal stage.
• Check whether the decline is broad-based or isolated to one segment.
• Look for missing large deals, lower activity, or a stage where opportunities stalled.

4) Check for operational causes

• Lost deals, fewer new opportunities, delayed closes, or a sudden change in source mix.
• If the CRM has timestamps, I would also check whether the drop is just a timing issue from late-week logging.

5) Final answer

• I would summarize the biggest driver first, then give one or two supporting pivots or charts.
• If the root cause is unclear, I would be explicit about that and recommend the next data point to collect.

The key is to move from "sales are down" to a specific explanation like "the decline came mainly from one region and a few large deals slipping out of the close window."

How I would clean it without losing raw data

• Keep the original CSV untouched on a Raw sheet or, better, load it into Power Query as the source layer.
• Build a separate Clean output table for analysis.
• Never analyze directly from the raw import.

Cleaning steps

1. Dates: standardize them in Power Query or with helper columns so every date becomes a true Excel date.
2. Spaces and casing: use TRIM, CLEAN, and consistent case rules to normalize text.
3. Headers: unmerge cells in the header area, fill missing header labels, and promote a single clean header row.
4. Product names: create a mapping table for known variants, such as spelling differences or mixed casing, and convert them to one canonical name.
5. Structure: turn the cleaned output into an Excel Table so formulas and pivots can refresh reliably.

Why I prefer Power Query for this

• It preserves the source data.
• It makes the cleanup steps repeatable.
• It is easier to refresh when a new CSV arrives.

Example logic

• Raw product name: widget pro, Widget Pro, WIDGET PRO
• Cleaned product name: Widget Pro

If I had to do it manually, I would still keep the raw import separate, create a staging sheet, and only then publish the final analysis table. That protects the audit trail and makes the workbook easier to trust.