How to Make a Sales Tracker in Excel (Download Free Template)

Excel as a spreadsheet program can be used as an excellent tracking tool. If you want to make a sales tracker in Excel, this guide will help you. This article will discuss every step of the process in detail so that you can find it easy regardless of your expertise in the software.

How to Make a Sales Tracker in Excel: Step-by-Step Procedure

In the following section, we will go over the different stages of making a dynamic sales tracker and its report. Each step is described in its sub-section.

Step 1: Make Dataset of Products for Sales

First, let’s create a list of products with their IDs and prices. We are assuming the products will be of a fixed price for a while. Also, we will be using this dataset to lookup values from IDs, instead of typing it out again and again when making the original Sales Tracker in Excel.

We are taking the following dataset for the demonstration.

Let’s name the sheet, say “Product Lists”, for better future references.

Step 2: Make Dynamic Sales Tracker for List of Products

Now it is time to make the original sales tracker. We will be using the VLOOKUP function for that to lookup values from the previous dataset we have created. This will ensure that we do not have to enter the repetitive values every time we enter a row in the tracker.

The IFERROR function will be helpful to eliminate all the errors for the blank values, which will make our sales tracker a bit more attractive. Follow these steps for a detailed guide on how you can make a sales tracker in Excel.

First, let’s create the headers for the columns in the sales tracker.

In this case, we will be entering the inputs of Dates, Product ID, and Units columns manually. As this can vary depending on the days and customers.
For item details, select cell D5 and write down the following formula.

=IFERROR(VLOOKUP(C5,'Product Lists'!$B$5:$E$8,2),"-")

Then press Enter on your keyboard. As a result, the item details will be filled up automatically from the table we have created in the previous step.

Now, click and drag the fill handle icon to fill the rest of the cells with this formula.
Next, select cell F5 and write down the following formula.

=IFERROR(VLOOKUP(C5,'Product Lists'!$B$5:$E$8,3),0)

Now press Enter on your keyboard.

After that click and drag the fill handle icon bar to the end of your supposed list to fill out the formula.
Then go to cell G5 and write down the following formula.

F5*E5

Now press Enter. As a result, you will have the total revenue calculated.

Then select cell G5 and write down the following formula to import cost/unit values.

=IFERROR(VLOOKUP(C5,'Product Lists'!$B$5:$E$8,4),"0")

After that, press Enter on your keyboard. Consequently, the cost/unit value will be imported.

Now, click and drag the fill handle icon bar to the end of the supposed list to fill the rest of the column with the formula.
Next, go to cell I5 and write down the following formula for total cost values.

=H5*E5

Then press Enter. And you will have the total cost in the cell.

Finally, for the profit values, select cell J5 and write down the following formula.

=G5-I5

Now press Enter. And then click and drag the fill handle icon like the rest of the cells where formulas were used.

Finally, fill up the rest of the rows with the selling date, sold products, and units. The final sales tracker list will look something like this.

🔍 Breakdown of the Formula

👉 VLOOKUP(C5,’Product Lists’!$B$5:$E$8,4) searches for the value in cell C5 in the array of B5:E8 in the spreadsheet called Product Lists. It returns the value of the 4th column from the row of the array, where the value of C5 matched.

👉 IFERROR(VLOOKUP(C5,’Product Lists’!$B$5:$E$8,4),”0″) returns 0 in case the previous function returns an error.

Step 3: Create Pivot Tables for Tracker

In our sales tracker dataset, there are so many parameters you can compare products with. Such as dates, product ID, item name with profits, cost, revenues, etc. For comparisons like this, the Pivot table is an excellent tool Microsoft Excel provides. In this step, we will focus on how to make a pivot table with the desired columns we want for a specific report in this dataset. Every time you want to create different pivot tables, just follow these steps.

First, select the whole dataset.

Then go to the Insert tab on your ribbon. Next, select PivotTable from the Tables group.

As a result, a box called PivotTable from table or range will appear. At this instant, select the option for New Worksheet as shown in the figure and click on OK.

Thus, we will have a new spreadsheet for the pivot table. On the right side of the spreadsheet, you will find PivotTable Fields. In the Choose fields to add to report option select the parameters, you want to base your report on. In this case, we are selecting the Date, Product ID, and Profit for the pivot table.

Finally, you will have the pivot tables with the parameters from the sales tracker made in step 2.

Step 4: Generate Dynamic Report on Sales Tracker

To create dynamic reports, you need to have specific pivot tables with specific parameters, depending on how you want to represent them. In this sales report, we are going to make reports on the total profit of products each day, the total profit earned each day, and the total profit earned by each product.

Create a Bar Plot for Total Profit of the Sales Tracker

First, we will make a bar plot and a line plot for visualizing the total profits earned by each product daily.

First of all, make a pivot table with dates, product IDs, and total profits as shown in step 3. Then select the pivot table.

After that, go to the Insert tab and select Recommended Charts in the Charts group.

As a result, the Insert Chart box will open up. Go to the All Charts tab in it, if you have more than one tab. Then from the left, select Column and then select the type of bar chart you want. Finally, click on OK.

Consequently, a column chart will appear.

After removing the legends and changing the chart style, it will look something like this.

Create a Line Chart for Total Profit of the Sales Tracker

To add a line graph from the pivot table follow these steps.

First of all, select the pivot table.

Now go to the Insert tab and select Recommended Charts from the Charts group.

Then in the Insert Chart box, go to the All Charts tab if you have more than one tab. Now select Line from the left of the box and on the right, select the type of line chart you want. Finally, click on OK.

As a result, the line chart will emerge on the spreadsheet.

After some modifications, the chart will look something like this.

Create Bar Plot to Visualize Total Profit by Days

Let’s say we want a profit graph for the days irrespective of the products sold each day. To plot such graphs in a bar plot, follow these steps.

First, you need to make a pivot table as described in step 3, but this time with only the dates and profit ticked in the fields. Once you have done that, select the whole table.

Then go to the Insert tab and under the Charts group, select Recommended Charts.

Now in the Insert Chart box that popped up select the All Charts tab if you have more than one. Then from the left side, select Column. Next, on the right side of the window, select the type of column chart you want. After that, click on OK.

As a result, a chart will emerge on the spreadsheet.

After some modifications to make it more presentable, we are selecting the following look for the chart.

Create a Pie Chart to Visualize Profit by Products

We need a pie chart to visualize our profits distributions. In this case, we are using a pie chart to plot the total profit distribution based on different products. Follow these steps to see how.

First, we need a pivot table with the products and profit as its column. Follow step 3 to make a pivot table, but this time check the products and profit in the final fields to check. Once you have that, select the pivot table.

Then, go to the Insert tab on your ribbon and select Recommended Charts from the Charts group.

Consecutively, an Insert Chart box will open up. Then select the All Charts tab from it. Now, on the left, select Pie. On the right, select the type of pie chart you want. Once done, click on OK.

As a result of the previous steps, a pie chart will pop up.

After some modification, the chart will look like this.

Once you have done all of the graphs you need from the sales tracker, move them to a different spreadsheet and rearrange them. It should make them more presentable and pleasing to visualize. Keep in mind that these graphs are dynamic, they will update automatically when you update values to your sales tracker in the same Excel workbook.

Download Template

You can download the workbook with all the sheets used for this demonstration from the link below. The datasets are included in it, try it yourself while you go through the steps.

Sales Tracker.xlsx

Conclusion

These were the steps you can implement to make a sales tracker and a dynamic report of it in Excel. Hope you have found this guide helpful and informative. If you have any questions or suggestions let us know below.

<< Go Back to Create a Tracker in Excel | Tracker in Excel | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!