The repeatable workflow analysts use: clean once in Power Query, model properly, summarise, then present one clear answer.
Analysis in Excel goes wrong at the start far more often than at the end. A clean, repeatable pipeline matters more than any single clever formula.
Step 1 — Question first
Write the question in one sentence before opening the file: 'Which branches missed the collection target last quarter, and by how much?' Everything downstream is judged against that sentence.
Step 2 — Clean in Power Query, not by hand
- Get Data → From File, then do every fix as a query step
- Set data types explicitly, especially dates and amounts
- Trim, remove blank rows, unpivot cross-tab layouts into one row per record
- Keep the raw file untouched, so next month is a refresh rather than a rebuild
Step 3 — Model
Separate fact data (transactions) from lookup data (branches, products, targets). Relate them rather than pasting lookups into the transaction table. This keeps totals correct as the data grows.
Step 4 — Summarise
PivotTables answer most business questions. Use Show Values As for shares and variances, and grouping for time periods. Build the summary before the chart.
Step 5 — Present
- One screen, the headline answer at the top left
- Three to five supporting visuals, no decoration
- Consistent number formatting and a stated data-as-of date
- Slicers for the two or three filters people genuinely ask for
Checks before you send it
- Does the grand total match the source system?
- Are there blanks or an '(blank)' category hiding unmatched records?
- Does the chart's message match the number?
- Would this refresh correctly with next month's file?
Frequently asked questions
- Is Excel enough for data analysis, or do I need Python?
- For most business reporting, Excel with Power Query and PivotTables is sufficient and far easier to hand over. Python becomes relevant at larger data volumes or for statistical and repeatable engineering work.
- What's the difference between an Excel report and an analysis?
- A report shows numbers. An analysis answers a question and states what changed and why it matters.
Learn this properly, with a trainer
Advanced Excel with Raj Singh — classroom in Vasai or live online. Batch timings are confirmed when you enquire.
See course options & fees