How do you track annual team or product sales in Excel? Want a simple sales dashboard? I explain all in this MS Excel video run-through. You can also download a unique, simple and sleek Excel team sales tracker template to use straight away.
In this blog I explain what the template covers, outline some key Excel features used in its creation, then provide the associated video explainer and template itself if you’d like to download. You are welcome to use this template for your own data, or maybe just notice the features used (such as the tables, conditional formatting, functions, sparklines, insert shapes, and rankings) as a prompt for ideas on your next Excel spreadsheet project. As always, let me know in the comments if I can improve this basic template.
For every minute spent organising, an hour is earned.Benjamin Franklin
Track Team or Product Sales in Excel
If you’re a small business, branch of a larger business, or a sales team manager, you might want a simple Excel spreadsheet to collate annual sales over several years. If you don’t have a team or want to use another way, you can list products instead of people. Simply change the table of names and amounts, then everything should fall into place; let me know in the comments if I can improve this! I made this template in MS Excel on my Office 365 account for Mac. However, it is equally-compatible with Excel for Windows.
Here’s a summary of the key features I think you’ll find helpful:
- Simple summary dashboard: Totalling and charting data entered within the more detailed table.
- Top person or product: Lookup of the top ranked sales person or product, as listed in the table for the latest year’s total.
- Ranking mechanism: Ranking and associated conditional formatting of entries made in the table.
- Sparkline summaries: Summary minigraph (‘sparkline’) for each entry in your detailed table, with highs and lows identified.
- Conditional formatting: To identify the higher and lower sales values in the tables and ranking mechanism.
- 1-Page, ready to print / PDF: The page margins are all set for you to print or save to PDF neatly on a single page.
Preview what the Excel team sales tracker entails below, and how it looks when printed or exported to PDF. Click on the image to download a copy if you want to try it out. I’ve used a made-up team; comment below if you recognise the names! Though of course, you can change the names and figures in the table as suits you. If working in other currencies like $ rather than £, change the format accordingly in the tables to ensure this gets picked up in the line graph axis. The year titles in the tables can also easily be changed to suit your reporting requirements.
If you would like to download the template, here’s the spreadsheet link:
Video Explainer on Annual Sales Template
In the following video, I provide explanation and guidance on how I created this Excel template. This includes insights and descriptions of some key features of MS Excel I used along the way. For example, how to merge cells, format text, use formulas (‘SUM’, ‘RANDBETWEEN’, ‘RANK’, and ‘IF’), and conditional formatting. I even introduce the use of sparklines to create micrographs:
I hope you find this Excel template useful to managing and reporting on your annual team and/or product sales; let me know how you get on, and I welcome any feedback either in the comments or my contact form.
If you found this MS Excel how-to and video helpful, please like and share with others it may help. If you like what I do and are so inclined, you can support my work. Want me to create fantastic, time-saving templates just for you and your business? Then please get in touch to arrange a bespoke quote. Finally, feel free to leave your feedback below and/or comment what you’d like tutorials and templates about.
Kind Regards, Adrian