✦Template Printable Ideas Insights

Mastering Excel: Create Your Own Sales Tracker

By

Keeping track of sales performance is a critical aspect of any business. While there are numerous sophisticated software solutions available, sometimes a simple, old-school tool like Microsoft Excel can do the trick just as well. Today, we're going to guide you through creating a sales tracker in Excel, from scratch. By the end of this guide, you'll have a fully functional sales tracker to monitor and analyze your sales performance.

Before we dive into the step-by-step process, make sure you have Microsoft Excel installed on your computer. For this guide, we'll use Excel 2016, but the process is similar in other versions. Let's start by creating a new workbook and naming it "Sales Tracker".

Setting Up the Sales Tracker

Our sales tracker will have several sheets to keep data organized. Let's create the main sheets we'll need:

How to Make a Sales Tracker in Excel (Download Free Template)
How to Make a Sales Tracker in Excel (Download Free Template)
Editable Sales Lead Tracker • Excel CRM Template
Editable Sales Lead Tracker • Excel CRM Template
how to make a delivery tracker
how to make a delivery tracker

1. Data: This sheet is where we'll input our sales data. Each row will represent a sale, and columns will represent different data points about that sale.

Making the Data Sheet

Here's how to set up the Data sheet:

Have you ever wondered how to create a delivery tracker in Excel?
Have you ever wondered how to create a delivery tracker in Excel?
Excel Weekly Sales Tracking | Templates at allbusinesstemplates.com
Excel Weekly Sales Tracking | Templates at allbusinesstemplates.com
Transaction Record - How to Create a Progress Tracker in Excel (Fast!) 🖊️
Transaction Record - How to Create a Progress Tracker in Excel (Fast!) 🖊️

1. In the first row (A1 to H1), enter the following headers: 'Sale Date', 'Product', 'Quantity', 'Price per Unit', 'Total', 'Customer', 'Region', 'Salesperson'.

2. Below these headers (A2 to H2), input some sample data to get started. You can replace these with your own data later.

Creating the Dashboard

The Dashboard sheet will display key metrics and visualizations for quick reference:

the sales commission tracker is displayed in this screenshot
the sales commission tracker is displayed in this screenshot
Automated Inventory Tracker in Excel | Excel templates
Automated Inventory Tracker in Excel | Excel templates
Customer Sales Tracking Excel | Templates at allbusinesstemplates.com
Customer Sales Tracking Excel | Templates at allbusinesstemplates.com
Track inventory
Track inventory
Goal Tracker in Excel That Feels Illegal to Know! | Smart Productivity Hack | Excel templates
Goal Tracker in Excel That Feels Illegal to Know! | Smart Productivity Hack | Excel templates
Free Monthly Expense Tracker - Google Sheets Template
Free Monthly Expense Tracker - Google Sheets Template

1. To create a new sheet, right-click on the workbook tab at the bottom, select 'Insert', then 'Sheet'. Rename it to 'Dashboard'.

2. Now, we'll create the metrics. In the first row (A1 to H1), enter the following metrics: 'Total Sales', 'Average Order Value', 'Sales by Region', 'Sales by Month', 'Sales by Salesperson', 'Top 5 Performing Products'.

Formulas and Structures

Now that we have our data and dashboard set up, let's create the formulas and structures to make them dynamic:

Progress Tracker in Excel‼️ #excel
Progress Tracker in Excel‼️ #excel
How to Create a Professional Sales Report in Excel
How to Create a Professional Sales Report in Excel
My simple and easy method for tracking product inventory using Excel spreadsheets
My simple and easy method for tracking product inventory using Excel spreadsheets

Setting up Automatic Totals

To make your life easier, let's set up Excel to automatically calculate the total sales for each month:

1. In cell B2 of the Dashboard sheet, enter the formula '=SUMIFS(Data!C2:H2,Data!B2:B2,"<="&A2)' without the quotation marks. This will sum the Total column for the month in the Data sheet.

Creating Pivot Tables and Charts

To analyze sales data by various factors, we'll need to create pivot tables and charts:

1. Select the data in the Data sheet (A1 to H100, assuming you'll input around 100 sales). Go to the 'Insert' tab, then 'PivotTable'. Choose where you want to place it and click 'OK'.

2. In the 'PivotTable Fields' pane, drag 'Region' and 'Salesperson' to the 'Rows' section, and 'Total' to the 'Values' section. This will give you a summary of sales by region and salesperson.

Pro tip: Customize your pivot table by right-clicking on it and selecting 'PivotTable Options'. You can adjust the layout, format, and other options here.

Creating Graphs and Charts

To visualize your sales data, you can create charts and graphs:

1. Select your pivot table, go to the 'Insert' tab, and choose the type of chart you want (bar, line, pie, etc.).

2. Customize your chart by giving it a title, labeling axes, and formatting it to match your workbook's style.

Formatting and Styling

The last step is to make your sales tracker look professional. We'll apply some formatting and styling techniques:

Applying Conditional Formatting

To highlight important data, like high sales figures or overdue payments, apply conditional formatting:

1. Select the cell range you want to format. Go to the 'Home' tab, then 'Conditional Formatting', and choose the formatting rule you want to apply.

Creating a Style Sheet

To keep your workbook consistent and easy to update, create a style sheet:

1. In the 'Home' tab, click on 'Cells' in the 'Format' group, then 'Format as Table'. Choose a table style and check 'My table has headers'.

2. Right-click on any cell in the table, select 'Format Table', and customize the table style. Click 'OK'.

Now that you've created a comprehensive sales tracker in Excel, you're ready to start inputting your sales data and monitoring your performance. As your data grows, your sales tracker will provide valuable insights to help you make informed business decisions. Keep updating your tracker regularly to ensure your data remains accurate and up-to-date. Happy tracking!