How to Track Sales in a Small Business Using Excel

Imagine running a food business where customers come in every day, orders keep coming, and money enters your account regularly.

From the outside, business looks good.

But at the end of the month, you ask yourself a simple question:

“If I have been selling this much, why don't I have enough money left?”

If you have ever asked yourself this question, you are not alone.

Many small business owners record sales in notebooks, WhatsApp messages, POS receipts or their bank transaction history. Some only check how much money is in their account at the end of the day.

The problem is that money received is not the same as profit.

Without proper sales records, it becomes difficult to know which products sell the most, which days bring more customers, how much you spend to run the business and where your money goes.

The good news? You do not need expensive software to get started. You can begin with Microsoft Excel or Google Sheets.

In this guide, I will show you how to track sales in a small business and turn those records into useful business insights.

1. Why tracking sales matters

Sales tracking is more than writing down how much customers paid you.

It helps you answer questions such as:

• How much did my business sell today?

• Which products are selling the fastest?

• Which days generate the most revenue?

• How much do customers owe me?

• Are my sales increasing or decreasing?

• How much money remains after the cost of the products sold?

For example, imagine you sell jollof rice, fried rice and grilled chicken.

You may assume jollof rice is your most profitable product because customers order it frequently.

But what if fried rice generates more revenue per plate? What if grilled chicken has a higher selling price but also costs significantly more to prepare?

Without records, you are guessing. With data, you can investigate.

2. Create a simple sales tracking spreadsheet

Open Excel or Google Sheets and create a worksheet called Daily Sales.

Use the following columns:

Date	Product	Quantity	Unit Price (₦)	Total Sales (₦)	Payment Method
01/10/2026	Jollof Rice	10	3,000	30,000	Transfer
01/10/2026	Fried Rice	6	3,500	21,000	Cash
01/10/2026	Grilled Chicken	4	4,000	16,000	POS

These are illustrative figures, not actual business results.

Each row represents a sale or a clearly defined sales transaction.

In the Total Sales column, enter this Excel formula in cell E2:

=C2*D2

This multiplies the quantity sold by the unit selling price.

For the example above, total sales would be ₦67,000.

Copy the formula down the column so that every new transaction calculates automatically.

Important: Record actual completed sales and distinguish them from cancelled orders, refunds and unpaid credit sales. Otherwise, your report may overstate revenue.

3. Track expenses separately

Here is where many businesses make a costly mistake.

They record sales but forget to record the costs associated with running the business.

Create another worksheet called Expenses.

Date	Expense Category	Description	Amount (₦)
01/10/2026	Ingredients	Rice, tomatoes and oil	18,000
01/10/2026	Transport	Ingredient delivery	3,000
01/10/2026	Utilities	Electricity	2,000

These are illustrative figures.

Tracking expenses helps you understand where your money goes. However, remember that expenses may include different categories, such as ingredients, rent, salaries, delivery fees and electricity.

Do not simply subtract the day's expenses from sales and call the result your final profit without accounting for inventory, unpaid bills and other relevant costs.

4. Calculate your sales automatically

Suppose your sales amounts are in cells E2 to E100.

To calculate total recorded sales, use:

=SUM(E2:E100)

To calculate sales for a specific product, use:

=SUMIF(B2:B100,"Jollof Rice",E2:E100)

These formulas save time and reduce the need to calculate everything manually.

As your business grows, you can use pivot tables to summarize sales by product, date, location or payment method.

5. Turn your sales records into business insights

Recording data is only the first step. The real value comes from asking the right questions.

Insight 1: Identify your best-selling products

Compare the quantity sold for each product.

If jollof rice sells 300 portions in a month while another product sells 45, you know that demand differs between the two products.

But high sales volume does not automatically mean high profitability. You also need to consider costs.

Insight 2: Identify your busiest days

Summarize sales by day of the week.

If Friday and Saturday consistently generate more sales, you might plan inventory and staffing around those patterns.

Use several weeks or months of data before concluding that a pattern is consistent.

Insight 3: Monitor payment methods

Track cash, transfers, POS payments and credit sales separately.

This can help you reconcile your records against bank statements and payment receipts.

Insight 4: Compare sales over time

Compare this week's sales with the previous week, or this month's sales with the previous month.

If sales fall, investigate possible reasons such as fewer customers, stock shortages, changing prices or seasonal demand rather than immediately assuming the business is failing.

6. Build a simple sales dashboard

Once your records are consistent, you can create a dashboard showing:

• Total sales

• Number of transactions

• Average transaction value

• Best-selling products

• Daily or monthly sales trends

• Sales by payment method

Excel is sufficient for many small businesses. As reporting needs grow, tools such as Power BI can provide more interactive dashboards and allow you to analyze larger datasets.

The tool is not the starting point. Good records are.

7. Common sales tracking mistakes to avoid

Mixing personal and business transactions: Keep business records separate from personal spending.

Recording only daily totals: Transaction-level records help you understand what generated those totals.

Ignoring refunds and credit sales: Record these separately so your figures reflect what actually happened.

Confusing revenue with profit: Sales show how much you earned from transactions; profit depends on the relevant costs and expenses.

Entering data inconsistently: Use consistent product names, dates and payment categories.

Never reviewing the records: Data is useful when you use it to make decisions, not when it sits untouched in a spreadsheet.

Final thoughts

You do not need to be a large company to make data-driven decisions.

A simple spreadsheet can help you understand your sales, monitor business activity and identify opportunities to improve.

Start by recording every transaction consistently. Then review your numbers weekly and investigate what is changing.

As your business grows, you can move from manual records to automated reports and dashboards.

At The Gifted Analyst, I help businesses turn raw data into practical insights through data analysis, dashboards and customer intelligence.

If your business has sales records but you are struggling to understand what the numbers mean, get in touch with The Gifted Analyst to discuss a data health check and identify opportunities to improve your reporting.