Sales data is one of the most common datasets people are asked to make sense of. The good news is that Excel already has everything you need to analyze it. This guide walks through a repeatable, five-step process using a small online store as an example.
Step 1: Start with a question
Before touching a formula, write down what you want to know. For example: Which products and months drove revenue last quarter, and where did sales slow down? A clear question stops you from building charts nobody needs.
Step 2: Check and clean the data
A typical export has columns like Order ID, Date, Product, Category, Quantity, Unit Price and Region. Check for:
- Blank rows or duplicated orders (use Data → Remove Duplicates carefully).
- Dates stored as text. They will not sort or group correctly.
- Inconsistent spellings like "Tote Bag" and "tote bag".
- A missing Revenue column. Add one:
=E2*F2(Quantity × Unit Price).
Convert the range into a table with Ctrl+T. Tables expand automatically and make formulas easier to read.
Step 3: Summarize with core formulas
| Question | Formula |
|---|---|
| Total revenue | =SUM(Sales[Revenue]) |
| Number of orders | =COUNTA(Sales[Order ID]) |
| Average order value | =SUM(Sales[Revenue])/COUNTA(Sales[Order ID]) |
| Revenue for one category | =SUMIF(Sales[Category],"Home",Sales[Revenue]) |
| Orders from one region | =COUNTIF(Sales[Region],"West") |
For more formulas, see essential spreadsheet formulas for business.
Step 4: Break it down with a pivot table
Select your table and choose Insert → PivotTable. Put Product in Rows, Revenue in Values, and Month in Columns. In a few seconds you can see which products rise and fall over time. Our guide to pivot tables for non-technical professionals explains this in more depth.
Step 5: Visualize and explain
Choose the chart that fits the question:
- Line chart for revenue over time.
- Bar chart for comparing products or regions.
- Avoid pie charts with more than four or five slices.
Then write a short summary: "Revenue grew 9% in Q2, driven mostly by mugs in the West region. Notebook sales declined for the third month in a row and may need a price or promotion review." That sentence is what your manager will remember.
Common pitfalls
- Mixing refunds into revenue without labeling them.
- Comparing months with different numbers of days without noting it.
- Hard-coding numbers inside formulas, which breaks when data changes.
Week 1 of our course, Spreadsheet Fundamentals for Business, uses a practice dataset very similar to this one.
