How to analyse your sales in Excel: five tables every small shop should build
Put one row per sale with the date, product, category, customer, quantity and amount, then build five pivot tables: money by month, by product, by weekday, by customer, and each customer's last order date. Those five answer most of what a small seller needs to know: what is growing, what carries the shop, which day is slow, who the best buyers are and who has stopped coming.
Get the data into one sheet
One row per sale, or per line of a bill. Columns: Date, Order, Customer (a phone number works), Product, Category, Quantity, Amount. Make sure Date is a real date and Amount a number, not text.
1. Money by month
Insert, PivotTable. Date in Rows (group by Months and Years), Amount in Values. Is the shop growing, flat or seasonal?
2. Money by product and category
Product in Rows, Amount in Values, sorted largest first. Then the same by Category. Usually a handful of products bring in most of the money.
3. Money by weekday
Add a column with =TEXT(A2,"dddd") to get the weekday, then pivot on it. The slow day is where a small offer does the most good.
4. Money by customer
Customer in Rows, Amount and a count of Order in Values. Your top twenty customers are worth a personal thank-you.
5. Who has stopped coming
Customer in Rows, Date in Values set to Max. That is each customer's last order. Sort oldest first: regular buyers near the top have gone quiet, and they are the ones to win back.
When a spreadsheet stops being enough
When you rebuild these every week, or the file comes from three places. One Tap Manager reads the same file, builds all five and more in about two minutes, and writes the next step next to each finding.
Common questions
Can I analyse sales in Excel for free?
Yes. Pivot tables do most of it. The five above are a good start.
Which columns do I need?
Date, product and amount at the least. Customer, category and quantity make it far more useful.