Excel Data Analysis for Beginners: A Practical Start

"Data analysis" sounds like a job title, but the first step is almost always ordinary: you have a spreadsheet and a few questions. Which product sells best? Is this month up or down on last month? Which customers haven't ordered in a while? You don't need statistics or a programming language to answer those. You need a well-shaped table and about five Excel skills.

This guide covers those five skills in the order you'll use them: shaping the data, sorting and filtering, conditional totals with SUMIFS and COUNTIFS, pivot tables, and charts. Then it walks through a starter workflow you can run on any new file in about half an hour, and explains when it's faster to ask an AI tool the question directly.

Step 1: Shape the data as one row per record

Most analysis headaches come from the table, not the formulas. Before anything else, check that your sheet follows these rules:

• One header row. Not a merged title plus two rows of sub-headers.
• One row per record. One order, one transaction, one employee, one survey response.
• One kind of value per column. The Date column holds dates, the Amount column holds numbers. Don't write "$1,200 (incl. tax)" in a single cell.
• No merged cells. They break sorting, filtering and pivots.
• No subtotal rows or blank rows in the middle of the data. Totals belong in formulas and pivots.
• Consistent spelling. "New York", "NY" and "New York " (with a trailing space) are three different values to Excel.

A quick test: click any cell in the data and press Ctrl+T to turn the range into an Excel Table. If Excel detects the right range and the right headers, your structure is fine. Tables also expand formulas and formatting automatically as you add rows, and they come with filter buttons built in.

If your data is messier than that, start with the guide to cleaning messy Excel data: trimming spaces, fixing numbers stored as text, and removing duplicates.

Step 2: Sort and filter to see the data

Before writing a single formula, spend five minutes just looking.

• Sort the amount column largest to smallest. You'll see your biggest transactions and spot obvious errors, like an extra zero.
• Filter to one region, one month or one category to get a feel for how the data is distributed.
• Use the status bar. Select a column of numbers and the bottom-right of the Excel window shows Sum, Average and Count, with no formula required.

The goal is orientation. How many rows are there? What date range? Which categories exist? Any big blanks? Knowing this keeps the rest of the analysis honest.

One warning: always sort the whole table, never a single selected column. Sorting one column on its own scrambles it against every other column, and there's no reliable way to undo that once you've saved.

Step 3: Conditional totals with SUMIFS and COUNTIFS

The two functions worth learning first:

• SUMIFS adds up values that meet conditions: "East region sales in March".
• COUNTIFS counts rows that meet conditions: "East region orders in March".

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)

With dates in A, category in B, region in C and amount in D:

=SUMIFS(D:D, B:B, "Electronics")
=SUMIFS(D:D, C:C, "East", A:A, ">=2026-03-01", A:A, "<2026-04-01")
=COUNTIFS(C:C, "East", D:D, ">1000")

Common beginner mistakes:

• Text criteria must match exactly; a stray space means no match.
• Date criteria are comparisons in quotes, as above.
• If the amount column holds numbers stored as text (left-aligned, often with a small green triangle), SUMIFS treats them as zero and your total comes out low.

AVERAGEIFS works the same way for averages. The Excel formulas for data analysis guide covers the next dozen functions worth knowing.

Step 4: Pivot tables for the whole picture

SUMIFS is great for one number. When you want every combination at once, say every category by every month, a pivot table is faster:

1. Click any cell in the data, then Insert → PivotTable.
2. Drag the category field to Rows.
3. Drag the month to Columns. If you only have dates, right-click a date in the pivot and choose Group by month.
4. Drag the amount to Values; it sums by default.
5. For an order count, drag the order ID into Values too and switch it to Count.

Pivots need no formulas, let you rearrange the view in seconds, and stay fast on large data. The catch is that they don't update on their own: after the source data changes, click Refresh.

When to use which? Use SUMIFS for a fixed report layout that other cells reference; use a pivot to explore. Many people find the answer with a pivot, then rebuild it with SUMIFS for the monthly report. The pivot table tutorial goes further, and SUMIFS vs pivot table compares the two in detail.

Step 5: Pick the right chart

Charts exist so someone else understands your conclusion in two seconds. For a beginner, four rules cover most cases:

Habits that make charts clearer: sort bars from largest to smallest; write the conclusion as the title ("Electronics drives nearly 40% of sales" rather than "Sales chart"); drop 3D effects, shadows and heavy gridlines; and when there are many categories, keep the top few and group the rest as "Other". A pivot chart (Insert → PivotChart from a pivot) updates as you filter the pivot, which makes it the quickest way to chart a summary. For more, see making charts from Excel data.

Going one step further: joining two tables

Real questions rarely live in one table. Orders have a customer ID, but the customer's city is in another sheet; sales have a product code, but cost is in a purchasing sheet. To bring that information across, use a lookup:

=XLOOKUP(lookup_value, lookup_column, return_column, "Not found")

For example, =XLOOKUP(B2, Customers!A:A, Customers!C:C, "Not found") pulls each order's city from the Customers sheet. Then a pivot by city answers "which city buys the most?" Filter for "Not found" afterward: those rows usually reveal typos or missing records. Older Excel versions without XLOOKUP can use VLOOKUP or INDEX/MATCH; see XLOOKUP vs VLOOKUP.

A starter workflow you can run on any file

Give a new spreadsheet about 30 minutes, in this order:

1. Check the structure (5 min). One header row? One record per row? Merged cells?
2. Reconcile the total (5 min). How many rows, and what's the grand total? Does it match a number you already know, like the monthly figure from finance?
3. Sort and scan (5 min). Largest amounts, date range, anything odd. Note it down.
4. Answer the first question (5 min). A pivot of totals by category. What's the biggest?
5. Look at the trend (5 min). A pivot by month and a line chart.
6. Write one sentence (5 min). For example: "Electronics is 38.7% of sales and has grown every month since March."

That last sentence is the point of the whole exercise. Formulas are only how you get there.

When to ask AI instead

With the skills above you can answer most everyday questions yourself. Some questions are still faster to ask directly:

• You know the question but not the function, for example "how many days between each customer's first and last order?"
• The answer takes several steps, like repeat customer rate, or comparing two lists to find new and lost customers.
• There are many sheets or files, such as twelve monthly exports to combine.
• You want to learn while you work, getting the answer and the formula that produces it.

ChatExcel is built for that. Upload an Excel or CSV file and ask in plain language. It reads every sheet, computes answers from the whole file, and returns bar, line, area, pie or scatter charts and sortable tables. Ask, and it gives you the Excel formula so you can rebuild the result yourself next time. It doesn't replace learning Excel; it gets you an answer while you're still learning. ChatExcel is paid only, starting at $29/month on Plus, with no free plan.

Five beginner mistakes to avoid

1. Editing the raw data. Keep an untouched copy and work on a duplicate.
2. Typing totals by hand. Typed numbers don't update when the data changes.
3. Ignoring numbers stored as text. They look right and sum to zero.
4. Sorting a single column. It misaligns every row.
5. Sharing a number without checking it. Recompute it another way or compare it to a known figure first.

Avoid those five and your analysis will already be more trustworthy than plenty of complicated-looking reports.