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

In this article, we will go through the different stages of making a dynamic sales tracker and its report. At the bottom is a free downloadable template.

Step 1 – Make Dataset of Products for Sales

First, let’s create a list of products with their IDs and Prices. We’ll assume that the products will be of a fixed price for a while. We will be using this dataset to lookup values from the product IDs, instead of typing them out again and again when making the original Sales Tracker.

Here is the dataset:

We’ll name the sheet “Product Lists” for ease of reference.

Read More: How to Keep Track of Customer Orders in Excel

Step 2 – Make Dynamic Sales Tracker for List of Products

Now we can make a sales tracker using the VLOOKUP function.

The IFERROR function will be used to eliminate all the errors for blank values, which will make our sales more visually attractive.

First, we’ll create the headers for the columns in the sales tracker:

• Enter the inputs of the Dates, Product ID, and Units column headers manually, as this can vary depending on the days and customers.
• For item details, select cell D5 and enter the following formula:

• Press Enter.

The item details will be filled automatically from the table we created in the previous step.

• Click and drag the Fill Handle icon down to fill the rest of the cells with this formula.
• Select cell F5 and enter the following formula:

• Press Enter.

• Click and drag the Fill Handle icon to the end of the list to fill the formula in the rest of the cells.
• Go to cell G5 and enter the following formula:

• Press Enter.

The Total Revenue is calculated.

• Select cell G5 and enter the following formula to import Cost/Unit values:

• Press Enter.

The Cost/Unit value is imported.

• Click and drag the Fill Handle icon to the end of the list to fill the rest of the column with the formula.
• Go to cell I5 and enter the following formula for the Total Cost value:

• Press Enter.

The Total Cost is in the cell.

• For the Profit values, select cell J5 and enter the following formula:

• Press Enter.
• Click and drag the Fill handle icon to fill the formula in the rest of the cells.

• 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:

Read More: How to Create a Task Tracker in Excel

Step 3 – Create Pivot Tables for Tracker

In our sales tracker dataset, there are so many parameters that can be compared, such as Dates, Product IDs, and item Names with Profits, Costs, Revenues, etc. For such comparisons, the Pivot Table is an excellent tool Microsoft Excel provides.

In this step, we will make a Pivot Table with the desired columns for a specific report on this dataset. Every time you want to create different Pivot Tables, just follow these steps.

• Select the whole dataset.

• Go to the Insert tab on your ribbon.
• Select PivotTable from the Tables group.

A box called PivotTable from table or range will appear.

• Select the option for New Worksheet as shown in the figure below and click on OK.

Thus, we have a new spreadsheet for the Pivot Table. On the right side of the spreadsheet are the PivotTable Fields.

• In the Choose fields to add to report option, select the parameters to base your report on. In this case, we select the Date, Product ID, and Profit for the Pivot Table.

We have the Pivot Tables with the parameters from the Sales Tracker made in Step 2.

Read More: How to Create Real Time Tracker in Excel

Step 4 – Generate Dynamic Report on Sales Tracker

To create dynamic reports, we’ll need specific Pivot Tables with specific parameters, depending on how we want to represent them. In this Sales Report, we will report on the Total Profit of each product each day, the Total Profit earned each day, and the Total Profit earned by each product overall.

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.

• Make a Pivot Table with Dates, Product IDs, and Total Profits as shown in step 3.
• Select the Pivot Table.

• Go to the Insert tab and select Recommended Charts in the Charts group.

The Insert Chart box will open up.

• Go to the All Charts tab if you have more than one tab.
• From the left, select Column and then select the type of bar chart you want.
• Click on OK.

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:

• Select the Pivot Table.

• Go to the Insert tab and select Recommended Charts from the Charts group.

• In the Insert Chart box, go to the All Charts tab if you have more than one tab.
• Select Line from the left of the box and on the right, select the type of line chart you want.
• Click on OK.

The line chart will be generated on the spreadsheet.

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

Create Bar Plot to Visualize Total Profit by Days

Suppose we want a Profit graph for the days irrespective of the products sold each day. To plot such a graph in a bar plot:

• First, make a Pivot Table as described in Step 3, but this time with only the Dates and Profit ticked in the fields.
• Then select the whole table.

• Go to the Insert tab and under the Charts group, select Recommended Charts.

• In the Insert Chart box that pops up, select the All Charts tab if you have more than one.
• From the left side, select Column.
• On the right side, select the type of column chart you want.
• Click on OK.

A chart is generated on the spreadsheet.

After some modifications to make it more presentable, we select the following look for the chart:

Create a Pie Chart to Visualize Profit by Products

We need a pie chart to visualize our Profits distribution. In this case, we’ll use a pie chart to plot the Total Profit distribution based on different products.

• Follow Step 3 to create a Pivot Table with the Products and Profit as its columns.
• Select the Pivot Table.

• Go to the Insert tab on your ribbon and select Recommended Charts from the Charts group.

An Insert Chart box pops up.

• Select the All Charts tab.
• On the left, select Pie.
• On the right, select the type of pie chart you want.
• Click on OK.

A pie chart will be generated.

After some modification, the chart will look like this:

• Once all of the graphs we need from the sales tracker have been created, move them to a different spreadsheet and rearrange them to make them more presentable and aesthetically pleasing.

These graphs are dynamic, so they will update automatically when you update values in your sales tracker in the same Excel workbook.

Read More: How to Keep Track of Clients in Excel

Download Template

Download the workbook containing all the sheets and dataset used for this demonstration from the link below.

Related Articles

• How to Keep Track of Invoices and Payments in Excel
• How to Keep Track of Customer Payments in Excel
• How to Create a Daily Task Sheet in Excel
• How to Create a Progress Tracker in Excel

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