How to track your sales in Excel. Let me show you step-by-step how to build a simple sales tracker spreadsheet for your business.

⏱️TIMESTAMPS⏱️
00:00 Excel sales tracker structure
01:41 Recording sales in Excel template
02:42 Revenue - COGS - Gross Profit
03:27 Tracking shipment charges
04:14 Automatic lookups in sales tracker
05:58 Fixing N/A errors in the spreadsheet
07:32 Fixing DIV/0! error
09:42 Tracking margins on your sales
10:10 Sales and margin summary

Let’s start with a blank tab, double-click on the name of the tab at the bottom, and rename it from “Sheet 1” to “Sales and margin”. We are first going to put the structure of the #Excel sales tracker in place. Let’s start with the column headers: Date, Sales order, Item code, Item name, Units, Price/unit, Revenue $, Cost/unit, Cost Of Goods Sold $, Gross Profit $, Gross Profit %, Shipping, Gross Profit $ after shipping, Gross Profit After Shipping %. I am abbreviating some of these, in order to get all my columns to fit onto one screen.

Now let’s make some room for the input of the sales line items. For now, we use an area where we can input 15 sales line items, but you can obviously go to one hundred, one thousand, or even more.

Let’s input the first sales in row 2. On August 6, 2021, we created sales order number 513. I have chosen not to put any further information about the customer (like customer name, customer address, and contact information) into this sales tracker spreadsheet, but if you want this in the file you can simply insert more columns to the right of the sales order column. Select column C, and press CTRL and “+” at the same time.

Item code is a unique product identifier (a “primary key”) that plays a very important role in the Excel sales tracker. On sales order 513, we sold item code A003. We sold 6 units, at a price of $125 per unit.

Here comes the first formula. Revenue equals number of units times price per unit. This is automatically formatted in dollars. The cost per unit is $75. Let’s pre-format this whole column as well, by selecting all the cells, and applying the US dollar format. Cost of goods sold is number of units times cost per unit. Gross profit is revenue minus cost of goods sold. Gross profit percentage is gross profit dollars divided by revenue dollars.

In the shipping column, we can input outgoing shipment charges, in other words shipping costs from us to the customer, if we pay for them. $40 for the sales items in this line. Let’s pre-format the whole column, by selecting all the cells, and applying the US dollar format. We can now calculate the gross profit after shipping costs, by taking gross profit and deducting the shipping cost. The last column is gross profit after shipping cost as percentage of revenue.

Let’s get our Excel #salestracker ready for some efficiency, by putting in a few lookup formulas, so we don’t have to retype certain input in each and every line. This is where our unique product identifier, the item code, comes in! Instead of typing each item name for each of the sales order lines, we can look up the item name in the product list table that I prepared ahead of time. Let me show you what that looks like. The tab called “Product list” has item code, item name, item description, item category, supplier, and item status. Currently, only 10 product codes have been assigned, but there is room for more at the bottom.

We are going to delete the input in cell D2 of the sales and margin tab of the Excel sales tracker, and input a formula instead. We will insert a formula in the item name column to retrieve the item name from the product list. We will do that with a very useful function called VLOOKUP in Excel. On the Home tab, I like to go to Functions – More Functions, type VLOOKUP, and get to the dialog box that helps you with the function arguments. We want to look up cell C2 (with value A003) in the table array of the product list tab cells A2 through F21, and then give us the value of the second column from the left. See, here’s the Olympic barbell in the preview. Let’s lock the row numbers in the table array, by putting $ signs in front of the 2 and the 21. Press enter. We can now drag the formula down to pre-fill the column with the VLOOKUP formula.

Philip de Vroe (The Finance Storyteller) aims to make accounting, finance and investing enjoyable and easier to understand. Learn the business and accounting vocabulary to join the conversation with your CEO at your company. Understand how financial statements work in order to make better investing decisions. Philip delivers finance training in various formats: YouTube videos, livestreams, classroom sessions, and webinars. Connect with me through Linked In!

Want to get access to bonus content, the #Exceltemplate for this video, and/or express your gratitude by buying me a cup of tea? Join my channel as a member through https://www.youtube.com/channel/UCQQJnyU8fALcOqqpyyIN4sg/join