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. 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.
Let’s format the column headers to make them stand out: select all of them, then click
bold, and let’s choose a green background. 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. We go to the font menu, and
apply borders to the section. Let’s make the text fit the columns nicely,
by selecting the whole tab, and double-clicking between columns A and B.
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, which is
an Olympic barbell. We sold 6 units, at a price of $125 per unit. Let’s pre-format
the whole column, by selecting all the cells, and applying the US dollar format.
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. Let’s pre-format the whole column, by selecting all the cells, and applying the
percentage format. 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 pre-format
the whole column, by selecting all the cells, and applying the percentage format.
Let’s make the text fit the columns nicely, by selecting the whole tab, and double-clicking
between columns A and B. Let’s get our Excel sales tracker 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. This will cause the rest
of the column to display “#N/A”, as for those rows there is no value in column C yet
for which an item name can be looked up. If the “#N/A”s bother you, we can fix that
by going back to cell D2, and inserting IFERROR( in front of the VLOOKUP formula, and behind
it inserting a comma and the text “Please enter item code in column C”, close parentheses.
If you copy this formula down the column, the error disappears.
Another place where we can use VLOOKUP to make things easer in the Excel sales tracker
is the cost per unit column. Instead of having to look this up manually every time, which
is prone to errors, we could have a formula look up the cost per unit for us. We are going
to delete the input in cell H2, and input a formula instead: VLOOKUP, lookup value cell
C2 (item code), table array cost per unit tab cells A2 through C11, lock the rows with
a $ sign, column index number 3 (third column from the left). Just like with column D, let’s
prepare the rest of column H for VLOOKUP without errors, by inserting IFERROR( in front of
the VLOOKUP, and just a comma and ) at the end. Copy this down the column, and if no
item code is available for the row yet then a zero is shown.
While we are at it, let’s copy down the revenue formula, COGS formula, and the Gross
Profit formula. If we copy the Gross Profit percentage formula, we get an error again
as you’re not supposed to divide by zero, so we insert IFERROR( at the front and ,) at
the end. Gross Profit after shipping can be copied down, while the related percentage
once again needs the IFERROR treatment. Every column in the sales tracker that uses
a formula has now been populated for future use. What I will do now is to highlight the
columns where we expect user input into the sales tracker, in yellow, to make them easy
to recognize. That would be date, sales order, item code, units, price per unit, and shipping.
We can also add sum totals to the number of units, the revenue, COGS, gross profit, shipping
cost, and gross profit after shipping. Let’s highlight these in bold.
Let’s input some more sales line items to see if the file works correctly. On August
9, sales order 514, item code A008. We see the item name automatically popping up in
column D, and you can recognize why it’s nice for the sales tracker spreadsheet to
do that work for you. 12 units, at $79.99 each. Let’s check that the cost per unit
is correctly retrieved from the cost per unit tab, which is the case. $20 shipping cost
for this order. August 10, sales order 515, item code A003,
2 units, $120 each, no shipping cost as they were picked up by the customer. The sales
tracker file works nicely, all formulas and lookups operate correctly.
We can add a formula to calculate the average gross profit margin percentage: this is equal
to the sum of the gross profit $ divided by the revenue $. Let’s highlight this in bold,
and no, Excel, this is not an error. You can’t just take the average of the gross profit
percentages in column K, but have to calculate the weighted average from the sums. Same story
for the gross profit % after shipping. With only three line items, the data is easy
to review and analyze so far, but if you sell more and more, you may want a summary of revenue
and margin by item code. Let’s build a summary table! We copy over the “Cost per unit”
tab: select the tab by clicking and holding your left mouse button, press the CTRL key
on your keyboard, and place the copied tab on the right. Then rename the tab to “Summary”.
We delete the data in cells C2 through C11, and rename column C to “Revenue $”.
What we want to do here is to calculate the total revenue per item code. Once again the
item code is the linking pin between the different tabs! We go to Functions – More Functions,
and type SUMIF. We want to evaluate whether in the range of the “Sales and margin”
tab cells C2 through C16 (let’s lock both columns and rows with a $ sign) any cells
match the criteria in cell A2 (lock the column with a $ sign). In other words, is the item
code the same in the summary tab as it is in the sales and margin tab. If that is the
case, we want our sales tracker to sum the values in cells G2 through G16 revenue (lock
both columns and rows with a $ sign). For cell C2 that is not the case, as we did not
sell item A001 yet. However, if we copy the formula down the column, we see $990 in cell
C4 for item code A003, which is the sum of $750 in cell G2 and $240 in cell G4 of the
sales and margin tab. In cell C9, we see the same number as in cell G3 of the sales and
margin tab. Let’s add a sum total at the bottom, and verify that this is the same number
as the sum total in the sales and margin tab. Let’s use the same process to sum the gross
profit by item code. Copy over column C, and paste into column D. Rename the column header
to Gross Profit $, and update the formula in cell D2. Range is fine, criteria is fine,
but sum range needs to be changed to column J. Copy over the formula down the rest of
column D. Let’s quickly check that the summed values are correct. Once that is the case,
let’s calculate Gross Profit % in column E. Cell E2 =IFERROR(D2/C2,). Apply percentage
formatting, and copy down the column. This summary tab will serve us well when the business
grows and grows, and we quickly want to get insight into the revenue, gross profit and
gross profit margin % by item code. That’s how simple it is to build a sales
tracker in Excel! I hope this is useful for you, let me know if you have any questions
or comments!