Excel for data analysis: a practical workflow from raw export to answer

In short: you can answer most business questions in Excel with four groups of tools. Text functions and Remove Duplicates to clean, PivotTables and SUMIFS, COUNTIFS or AVERAGEIFS to summarize, FILTER, SORT and UNIQUE to narrow down, and a well-chosen chart to present.

The data analysis workflow in Excel

It is tempting to jump straight to a chart. In practice, most of the time goes into getting the data ready, and most mistakes happen there too. Working in a fixed order keeps you honest:

Step	Question	Excel tools
1. Clean	Is the data complete and consistent?	TRIM, SUBSTITUTE, Remove Duplicates, Format as Table
2. Summarize	How much, how many, how high on average?	PivotTable, SUMIFS, COUNTIFS, AVERAGEIFS
3. Narrow down	Which records matter for this question?	FILTER, SORT, UNIQUE, Sort and Filter
4. Present	How does someone see the answer at a glance?	Charts, conditional formatting

The practice case: orders from an online retailer

You work for a retailer that sells online and in its own stores. The shop system exports a list with order number, region, channel, city and amount. Management has straightforward questions: what is the average online order in the North region, and which cities are customers ordering from? Before you can answer, the data has to be trustworthy.

Step 1: Clean the data

Exported data is rarely tidy. Leading or trailing spaces, mixed capitalization and duplicate rows look harmless but quietly break every summary. To Excel, "North" and "North " with a trailing space are two different regions, and SUMIFS will miss the second one. The exercise uses names from a web form, but the same fix applies to regions, cities or product names.

Task These names come from a web form: some in lower case, some in capitals, with stray spaces. In B2, write a formula that returns a clean name with no extra spaces and each word capitalized (Jana Weber). The formula is filled down to B7.

	A	B	C
1	Name (raw)	Name cleaned	Department
2	jana weber		Sales
3	MURAT KAYA		Purchasing
4	lena hoffmann		Finance
5	Tom Schulz		Sales
6	sara KLEIN		HR
7	ali demir		Logistics

Once the helper column looks right, copy it and use Paste Values to replace the original. Duplicate rows go through Data, Remove Duplicates; the lesson on finding and removing duplicates shows how to check them first. TRIM and SUBSTITUTE handle most text problems. Finally, format the range as a table so formulas and PivotTables pick up new rows automatically.

Step 2: Summarize with PivotTables and formulas

For a quick overview, a PivotTable is hard to beat: drag Region into Rows and Amount into Values, and you have totals in seconds. Switch the value field to Count or Average with two clicks. For key figures that need to sit in a fixed place on a report, formulas work better. SUMIFS adds, COUNTIFS counts and AVERAGEIFS averages, each with as many conditions as you need.

Task What is the average order value in the region in G1 through the channel in G2? Calculate it in G3 with AVERAGEIFS and take the criteria from those cells.

	A	B	C	D	E	F	G
1	Order	Region	Channel	Amount $		Region	North
2	1001	North	Online	84		Channel	Online
3	1002	South	Online	120		Avg order $
4	1003	North	Store	46
5	1004	North	Online	132
6	1005	West	Online	95
7	1006	North	Online	57
8	1007	South	Store	210
9	1008	North	Store	73

One thing to keep in mind when you read the result: an average of a handful of values can swing a lot. Put =COUNTIFS(B2:B9,G1,C2:C9,G2) next to it so the reader sees how many orders it is based on. Three orders are a hint, not a trend.

Step 3: Narrow down with dynamic arrays

FILTER, SORT and UNIQUE let one formula return a whole list that spills into the cells below. When the data changes, the list follows. A typical first move is a clean list of categories to build a summary on.

Task To report by city you first need the list of cities, each one once and in alphabetical order. In E2, write a formula that builds this list from B2:B9.

	A	B	C	D	E
1	Order	City	Amount $		Cities
2	1001	Leipzig	84
3	1002	Bremen	120
4	1003	Leipzig	46
5	1004	Dresden	132
6	1005	Bremen	95
7	1006	Hamburg	57
8	1007	Dresden	210
9	1008	Leipzig	73

These functions require Excel 2021 or Microsoft 365. In older versions, run Remove Duplicates on a copy of the column or use the row labels of a PivotTable. To pull every order of 100 or more, write =FILTER(A2:D9,D2:D9>=100); wrap it in =SORT(…,4,-1) to show the largest first.

Step 4: Present the result

A good chart answers exactly one question. Columns compare categories such as regions, lines show change over time, and a pie chart only works for a few shares that add up to a whole. In tables you share, conditional formatting points readers to outliers so they do not have to scan every number.

Basic statistics in Excel

Many questions need only a few measures. AVERAGE gives the mean, MIN and MAX the range, COUNT the number of values. When a few very large values pull the average up, MEDIAN returns the middle value of the sorted list and is often the fairer number. For top lists and rankings there are LARGE, SMALL and RANK.

For more, Excel ships with the Analysis ToolPak add-in. Turn it on under File, Options, Add-ins, Manage: Excel Add-ins, Go. A Data Analysis button then appears on the Data tab with tools such as Descriptive Statistics, Histogram and Regression. Its output is static, though, so for reports you refresh every month, formulas and PivotTables are usually the better base.

Common mistakes in Excel data analysis

Numbers stored as text

Values that align to the left or show a small green triangle are text. SUM and PivotTables skip them. Data, Text to Columns, Finish usually converts a whole column, and VALUE works inside a formula.

Blank rows and subtotal rows

A blank row ends the list as far as many Excel features are concerned, and subtotal rows get counted twice. Keep raw data as one continuous block with headers in the first row.

Averaging averages

If regions have different numbers of orders, the mean of the regional averages is not the overall average. Always calculate from the individual records, with AVERAGEIFS or a PivotTable.

FAQ