How to Analyse Sales Data in Excel Without an Accountant

You have a spreadsheet full of sales data. Dates, amounts, product names, customer IDs — rows and rows of it. What you need is the story inside those numbers. Here is how to get it without waiting for your accountant.

What "Analysing Sales Data" Actually Means

Before opening a formula, be clear about the three questions every sales analysis should answer:

How much did we sell, and is it growing? — Total revenue, and the trend over time.
What is selling and what is not? — Revenue by product, category, or service line.
Who is buying? — Revenue by customer segment, region, or acquisition channel.

Everything else — average order value, conversion rates, seasonality — flows from these three foundations.

Step 1 – Clean the Data First (10 Minutes)

Raw export files are almost never analysis-ready. Spend 10 minutes on these checks before touching a formula:

Remove duplicate rows — Data > Remove Duplicates
Standardise date format — select the date column, Format Cells > Date, pick one consistent format (e.g. DD/MM/YYYY)
Fix blank cells — empty amounts should be 0, not blank, for SUM formulas to work correctly
Trim whitespace from text columns — product names like " Widget " and "Widget" look different to Excel. Use =TRIM(A2) on a helper column
Remove currency symbols from amount columns — Find & Replace £ with nothing so amounts are treated as numbers

Step 2 – Total Revenue (The Foundation)

In an empty cell:

=SUMIF(B:B, ">=2026-01-01", C:C)

Column B = transaction date
Column C = transaction amount
Adjust the date to filter by period

For total revenue across all dates: =SUM(C:C)

Step 3 – Revenue by Month (Trend Chart)

Create a summary table:

Month	Revenue
Jan 2026	`=SUMPRODUCT((MONTH(B2:B1000)=1)(YEAR(B2:B1000)=2026)C2:C1000)`
Feb 2026	`=SUMPRODUCT((MONTH(B2:B1000)=2)(YEAR(B2:B1000)=2026)C2:C1000)`
...	...

Select these two columns, insert a line chart. Now you have a revenue trend.

What to look for: Is revenue trending up month-on-month? If it dipped in one month, look at that period — did you lose a big client? Was there a public holiday? Fewer working days?

Step 4 – Revenue by Product or Category

If your spreadsheet has a product column (column D):

Select the whole dataset
Insert > PivotTable
Drag Product to Rows, Amount to Values (set to Sum)
Sort descending

You now have a ranked list of your best-selling products.

The 80/20 rule almost always holds: typically your top 20 % of products generate 80 % of revenue. If you have products in the bottom 20 % generating less than 2 % of revenue, they are candidates for cutting or repricing.

Step 5 – Average Order Value

=AVERAGE(C2:C1000)

Average order value (AOV) is one of the three levers for growing revenue (the others being number of orders and customer retention). Track it monthly. If AOV is falling, either high-value customers are churning or discounting is getting out of hand.

Step 6 – Identify Your Top Customers

If your spreadsheet has a customer column (column E):

Insert another PivotTable
Drag Customer to Rows, Amount to Values
Sort descending

Look at your top 10 customers by revenue. What percentage of total revenue do they represent? If your top customer accounts for more than 25 % of revenue, that is a concentration risk worth addressing.

Step 7 – Spot Anomalies Manually

Sort column C (amount) from largest to smallest. Look at the top 10 transactions — do they all make sense? An unusually large transaction might be correct, or it might be a data entry error or a one-off that is distorting your averages.

Sort by date and look for gaps. A week with no transactions in a business that normally does daily sales is worth investigating.

When Excel Becomes a Bottleneck

These formulas and pivot tables work well for small datasets (fewer than 10,000 rows) and one-off analysis. They become a bottleneck when:

You need to repeat this every month across multiple files
Your data lives in PDFs, not just Excel
You need charts and summaries ready to share with a business partner or investor in the next hour
You are not confident the formulas are correct

Sales analysis becomes more useful when it is reviewed alongside monthly small-business expenses, margin, and cash. Use a monthly management report template to keep those measures in one repeatable review.

Raavue financial analysis uses an Excel, PDF, or CSV export to prepare trend analysis, product breakdowns, anomaly review, and structured management commentary.

Start your free 7-day trial → No spreadsheet skills required.