Excel & Spreadsheets for Analysts — Formulas, PivotTables & Cleaning

Excel & Spreadsheets for Analysts: Formulas, PivotTables & Cleaning

2 min readPublished 29 Jul 2026

Spreadsheets are still the most-used analytics tool on earth — and strong Excel skills are the fastest way to become immediately useful in a data role. Master these and you can deliver value on day one.

Essential formulas

Aggregation: SUM, AVERAGE, COUNT, COUNTA, MIN, MAX.
Conditional aggregation: SUMIF(S), COUNTIF(S), AVERAGEIF(S) — totals by category/criteria.
Logic: IF, IFS, AND, OR, IFERROR (handle errors gracefully).
Text: TRIM, LEFT/RIGHT/MID, CONCAT, TEXTSPLIT — cleaning and reshaping. Understand absolute vs relative references ($A$1 vs A1) — the classic beginner trap when copying formulas.

Lookups — joining data

VLOOKUP (and the modern, more flexible XLOOKUP) and INDEX/MATCH let you pull matching values from another table — the spreadsheet equivalent of a SQL join. This is one of the most-tested practical skills in analyst assessments.

PivotTables — analysis in seconds

PivotTables summarise large tables instantly: drag fields into Rows, Columns, Values and Filters to get totals, averages and breakdowns by any dimension — no formulas needed. They're the single highest-leverage Excel skill for analysis. Add a PivotChart to visualise the same summary.

Cleaning data

Real data is messy. Core techniques: Remove Duplicates, Text to Columns, Find & Replace, TRIM/CLEAN for stray spaces, converting text-to-numbers/dates, and flagging blanks. Power Query (Get & Transform) lets you build repeatable cleaning steps that refresh automatically — a big step up from manual edits.

Charts done well

Pick the chart that fits the message (see Visualisation Principles): line for trends over time, bar/column for comparisons, and avoid pie charts with many slices. Keep them clean and labelled.

Put it to work

Take a raw dataset, clean it, build a PivotTable to answer a question, and add one clear chart. Then move up to SQL for larger data.

Interview Intelligence

How this topic actually shows up in interviews — and how to demonstrate you understand it.

Why employers ask about this

Spreadsheet skills are tested in almost every analyst assessment because they're used daily.

Technical questions

How would you summarise sales by region and month in Excel?+

Use a PivotTable with Region in Rows, Month in Columns and Sum of Sales in Values — or SUMIFS.

How do you combine data from two tables in a spreadsheet?+

Use XLOOKUP (or VLOOKUP / INDEX-MATCH) on a shared key — the spreadsheet equivalent of a join.

Behavioural questions

Tell me about a report or analysis you built in a spreadsheet.+

Use STAR: the question, how you cleaned the data, the PivotTable/formulas you used and the insight/decision it supported.

Real-world scenarios

“You're given a 50,000-row export with duplicates and inconsistent formats.”+

Expected answer: Clean with Remove Duplicates and Power Query, standardise formats/types, then summarise with a PivotTable to answer the question.

Practise in AI Interview™Rehearse these questions, scored.Plan with Career Coach™A personalised path to your goal.Evidence it in Career Passport™Prove this skill to employers.

Employability Intelligence

Where this knowledge takes you — the jobs, skills and certifications it feeds into.

Relevant roles

Reporting AnalystMI AnalystData Analyst

Skills you're proving

Excel formulasLookupsPivotTablesData cleaning

Recommended certifications

Microsoft Power BI (PL-300)Google Data Analytics

Career progression

The fastest route to your first data role.

What employers expect

That you can clean, analyse and visualise real data, write SQL, and turn findings into clear, actionable recommendations for the business.

Frequently asked questions

What is a PivotTable used for?

Summarising large tables instantly — totals, averages and breakdowns by any dimension — without writing formulas. It's the highest-leverage Excel skill for analysis.

What is the difference between VLOOKUP and XLOOKUP?

Both pull matching values from another table; XLOOKUP is newer, more flexible (can look left, handles errors) and generally preferred.

How do you clean data in Excel?

Remove Duplicates, Text to Columns, Find & Replace, TRIM/CLEAN, fix data types, and use Power Query for repeatable, refreshable cleaning.

Practise what you've learned

Turn this guide into real, evidenced progress

Missiora helps you measure, improve and evidence the capabilities employers actually value — start with the tools best suited to this topic.

Published by

Missiora

Missiora is an AI Employability Intelligence platform. Our resources are researched and reviewed by the Missiora team to help you measure, improve and prove your career readiness.