The quickest way to get a weekly sales tracking template in Excel is to use the built-in “Sales Record” or “Weekly Sales” templates from File > New, or to build a simple table yourself with columns for Date, Product, Quantity, Unit Price, and Total. For most users, the built-in templates are sufficient, but a custom template gives you full control over metrics like profit margins, salesperson performance, or inventory levels.
Why Track Sales Weekly Instead of Daily or Monthly?
Weekly tracking hits a sweet spot for many small businesses and sales teams. Daily tracking can be noisy—one slow Monday doesn’t mean the week is lost—and monthly tracking can hide problems until it’s too late to react. A weekly view smooths out daily fluctuations while still letting you spot trends early enough to adjust marketing, staffing, or inventory.
For example, a retail store might notice that sales dip every Tuesday. With weekly tracking, they can compare one Tuesday to the next, but also see whether a promotion on Wednesday lifts the whole week. Weekly data also aligns with payroll cycles and supplier ordering schedules, making it practical for operational decisions.
What a Basic Weekly Sales Template Should Include
A functional weekly sales tracker doesn’t need to be complicated. At minimum, it should capture the essential details of each sale or transaction. Here’s a standard layout:
- Date – the day of the sale
- Product or Service – what was sold
- Quantity – number of units
- Unit Price – price per unit
- Total – quantity × unit price (use a formula)
- Salesperson – if you need to track performance
- Customer or Invoice # – for reference
You can add a column for “Cost” to calculate profit, or “Payment Method” to see which channels are most used. The key is to keep it flexible—you can always add columns later, but you don’t want to start with 30 columns and overwhelm yourself.
How to Create a Weekly Sales Template from Scratch
If you prefer to build your own, follow these steps in Excel for Microsoft 365 (Windows). The steps are similar on Mac, but the ribbon menu names are slightly different.
- Open a blank workbook. In cell A1, type “Weekly Sales Tracker” and format it as a heading.
- In row 3, enter your column headers: Date, Product, Quantity, Unit Price, Total, and any others you need.
- Select the header row and apply bold, and use Home > Format as Table to turn the range into an Excel Table. This gives you automatic filtering and easy formula copying.
- In the first data row (row 4), enter a sample date, product, quantity, and price. In cell E4 (assuming Total is in column E), type the formula
=C4*D4and press Enter. - Drag the fill handle down to copy the formula to other rows. Excel Table will auto-fill the formula for new rows.
- Add a summary section below the table. For example, in cell A20 type “Weekly Total”, and in B20 use
=SUM(E4:E100)(adjust the range to cover your data). - To highlight sales above a certain amount, select the Total column, go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than, and enter a value like 500.
On Mac, the ribbon is the same but the key combinations differ—for example, Ctrl+C becomes Command+C. The formulas and features work identically.
Using Built-in Excel Templates for Weekly Sales
Excel includes several ready-made templates that can be adapted for weekly use. To find them:
- Click File > New.
- In the search box, type “sales” and press Enter.
- You’ll see options like “Sales Record”, “Weekly Sales”, or “Sales Report”. Choose one that fits.
- Once opened, save a copy to your own folder, then customize the column headers and formulas as needed.
Be aware that some built-in templates are designed for monthly or quarterly tracking, so you may need to adjust the date columns. Also, templates downloaded from the web may contain macros—if you don’t trust the source, avoid enabling macros.
Common Problems and How to Fix Them
Even a simple template can cause headaches. Here are frequent issues and solutions:
- Formulas not calculating: If you see the formula text instead of a result, check that the cell is formatted as General (not Text) and that you started with an equals sign. Also, ensure that “Show Formulas” is off (Ctrl+` on Windows, Control+` on Mac).
- Dates showing as numbers: Select the date column, right-click > Format Cells > Date, and choose a format like “3/14/2021”.
- Total column not updating: If you added rows below the table, Excel Tables expand automatically, but if you used a fixed range in your SUM formula, extend it. For example, change
=SUM(E4:E100)to=SUM(E4:E200). - Accidentally sorting the summary row: Keep your summary section separate from the table—don’t include it in the table range. Or use the Table Design tab to turn off the total row if you don’t need it.
Comparing Weekly Tracking with Google Sheets
If you’re considering Google Sheets instead of Excel, the core functions are the same. You can build the same table and use formulas like =C4*D4 and =SUM(E4:E100). Google Sheets also has built-in templates (click Template Gallery in the top-right). The main differences are:
- Google Sheets is free and cloud-based, so multiple people can edit simultaneously.
- Excel has more advanced features like Power Query and PivotTables, which are useful for large datasets.
- Excel’s desktop version works offline without any internet connection.
If you collaborate with a team, Google Sheets might be easier. If you need complex analysis, Excel is stronger.
Costs and Safety Considerations
Excel is part of Microsoft 365, which costs about $6 to $10 per user per month for business plans, or a one-time purchase for a standalone version. Google Sheets is free for personal use, but business plans start around $6 per user per month. There are also free alternatives like LibreOffice Calc, but they may lack some Excel features.
When using any template, be mindful of data safety. If you store customer names, contact details, or payment information, you should protect the file with a password (File > Info > Protect Workbook > Encrypt with Password). Also, avoid storing sensitive data in cloud templates unless you trust the provider’s security. Always keep a backup copy of your sales data—either on a separate drive or in a cloud service.
Frequently Asked Questions
Can I use a weekly sales template for multiple weeks? Yes. You can either create a new sheet for each week (right-click the tab > Move or Copy > Create a copy) or add a “Week” column and filter by that. The latter is better for long-term analysis.
How do I calculate profit in a weekly sales template? Add a “Cost” column and a “Profit” column. In the Profit column, use a formula like =E4-F4 (assuming Total is in E and Cost in F). Then sum the Profit column for the week.
What if I sell services, not products? The same template works—just replace “Product” with “Service” and “Quantity” with “Hours” or “Units”. For hourly services, the “Unit Price” becomes your hourly rate.
Can I automate the weekly total? Yes. If you use an Excel Table, you can enable the Total Row from the Table Design tab, and choose “Sum” from the dropdown. This updates automatically as you add rows.
Is it safe to download a free template from the internet? It can be, but only from reputable sites. Templates with macros (.xlsm files) can contain viruses. Always scan downloaded files with your antivirus software and enable macros only if you trust the source.
How do I share my weekly sales template with my team? Save the file to OneDrive or SharePoint and share the link. For simultaneous editing, use the “Share” button in Excel. Alternatively, export to Google Sheets if your team prefers that.
Related
- Excel Sales Tracking Template by Customer: How to Build One
- Excel Sales Tracking Template by Product: How to Build One
- Excel Reverse Mortgage Calculator Spreadsheet
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Remove a Section Break in Word Without Wrecking LayoutTo remove a section break in Microsoft Word for Microsoft 365 on Windows, choose Home > ¶ to…
- Wedding Budget Spreadsheet for Beginners: A Simple SetupA beginner wedding budget spreadsheet should show your total spending limit, planned costs, final or contracted costs, and…
- Meal Planning Excel Sheet: Build a Weekly PlannerYes—you can make a meal planning Excel sheet with a weekly meal grid and a separate grocery list.…
- Yes or No Drop-Down in Excel: How to Set It Up FastTo add a Yes or No drop-down in Excel for Microsoft 365 on Windows, select the cell or…
- Data Validation in Word: Build Forms and Drop-Down ListsMicrosoft Word does not have an Excel-style Data Validation command. To guide people entering information, use Word’s Developer…
- How to Keep Track of Expenses in ExcelTo keep track of expenses in Excel, create a table with one row per purchase, record the date,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.