Indzara

Sales Report Excel Template – Top 10 & Bottom 10 Products by Sales

This Sales Report Excel template is designed to help identify the best and worst performing products easily. Enter your sales data and instantly identify the top selling products. Compare Week over Week, Month over Month and Year over Year.

If you are a business owner, you can find which products are selling the most (Top 10 products) so that you can continue to have more of them in stock and promote them. By knowing which products are in the ‘Bottom 10’, you can either stop purchasing more of them or find innovative ways to promote them to your customers.

If you are a data analyst in a company, it is quite likely that you have had the need to analyze product sales from this perspective. Just download this template, paste your data and see the top 10 and bottom products by sales instantly.

Sales Report - Top 10 & Bottom 10 Products - Excel Template
Sales Report – Top 10 & Bottom 10 Products – Excel Template

FEATURES OF SALES REPORT

  • Automated report with a lot of customization options
  • Easy to use – Just 2 quick steps to enter data
  • Customize date parameters (Month, Week, Day, Custom) of the report
  • Compare a period’s sales versus previous period or previous year or custom dates
  • Choose to sort by Sales, Change in Sales Amount or % Change in Sales
  • Control the thresholds for alerts

DOWNLOAD SALES REPORT

VIDEO DEMO

HOW TO ENTER DATA IN SALES REPORT TEMPLATE

With all the templates published on indzara.com, the goal is to have a simple and effective solution to a problem. Keeping up with the same theme, this template is very simple to use. Just 2 steps.

STEP 1: Enter a list of products in HOME sheet. (If you are new to Excel tables, please see article on how to enter data in Excel tables).

Enter List of Products for Sales Report
Enter List of Products for Sales Report

STEP 2: Enter Sales data in SALES_DATA sheet.

Enter Product Sales Data for Sales Report - Date, Product ans Sales Quantity or Amount
Enter Product Sales Data for Sales Report – Date, Product ans Sales Quantity or Amount

There are only three columns of data. DATE of sale, PRODUCT sold and Quantity or Amount of SALES. You can just paste this data (in the same order of columns).

Tip: Generally, it is recommended to paste special as values.

Click on cell A2. Right Click and choose the Values shortcut.

How to Paste data - Paste Special as Values
How to Paste data – Paste Special as Values

That’s it. We are ready to see the report. How easy is that? 🙂

The report is a single page but it is packed with options to customize.
On the left of the sheet, we have user control options. There are three components (Date parameters, Sort Metric and Thresholds).

Input Options for Sales Report - Date parameters, Sort Metric and Thresholds
Input Options for Sales Report – Date parameters, Sort Metric and Thresholds

CHOOSE DATE PARAMETERS

First is the ‘Date Parameters’. Using the date parameters, we can inform the template for the date range for which we want to see the sales report for. We can choose to view from a Monthly/Weekly/Daily perspective or choose Custom to enter custom dates.

Sales Report - Date Parameters - Options
Sales Report – Date Parameters – Options

Let’s look at each of the options.

Day

When we choose Day, we see the following options for date parameters.

Sales Report - Data Parameters with Day Option
Sales Report – Data Parameters with Day Option

We can choose Today or Yesterday or Day Before Yesterday. The template will then calculate the sales for that specific day. It can automatically determine today’s date from our computers and use in calculations.

We can also set comparison periods. In this template, we can not only calculate sales for a specific date range, we can also compare with another period easily. For example, we can compare today’s sales with ‘Previous Day’ or ‘Same Day Last week’. You may have seen something similar in Google Analytics.

It is better to compare this Monday’s sales with Last Monday’s, instead of Sunday (as sales patterns for most businesses vary over the weekend).

Week

When we choose Week, we have the options for ‘Last 7 days’, ‘This Week’ or ‘Last week’. We can set comparison period as ‘Previous Week’ or ‘4 Weeks ago’.

Sales Report - Date Parameters with Week Option
Sales Report – Date Parameters with Week Option

Month

Month option works similar to Week. If we choose Month, we have the options for ‘Last 30 days’, ‘This Month’ or ‘Last Month’. We can set comparison period as ‘Previous Week’ or ‘4 Weeks ago’.

Sales Report - Date Parameters with Month Option
Sales Report – Date Parameters with Month Option

Custom

We also have the option of ‘Custom’ where we can enter any date range for current period and any date range for Comparison period.

Sales Report - Date Parameters with Custom Option
Sales Report – Date Parameters with Custom Option

We can use this to do Year over Year, or Quarter over Quarter comparison.

CHOOSE METRIC

After we set the ‘Date Parameters’, we can decide which sales metric we want to use on the report. The three options are ‘Sales Amount’, ‘Change in Sales Amount’ and ‘% Change in Sales Amount’.

Sort Sales Report by Sales Amount, Change in Sales Amount or % Change in Sales
Sort Sales Report by Sales Amount, Change in Sales Amount or % Change in Sales

METRIC 1: SALES AMOUNT

When we choose Sales Amount, the report would appear like this.

Sales Report - Top 10 and Bottom 10 Products by Sales Amount
Sales Report – Top 10 and Bottom 10 Products by Sales Amount

The header section has the total sales for the current period and comparison period.
The Top 10 Products table will list the top 10 products by Sales in the Current period. Comparison period does not matter in this setting. Similarly, the bottom 10 products table will list the products with the least sales amount.

METRIC 2: CHANGE IN SALES AMOUNT

If we choose metric as ‘Change in Sales Amount’, the report will show the products which had the top 10 sales increases from the comparison period to the current period, and the top 10 sales declines.
We can also set thresholds for change.

Sort by Change in Sales Amount - Set thresholds
Sort by Change in Sales Amount – Set thresholds

In the image above, we have set thresholds of 250 and -150. Let’s see how it works.
In the report, the Top 10 Products table shows the products which had the top 10 sales increases from the comparison period to the current period.

Top 10 Products by Change in Sales Amount
Top 10 Products by Change in Sales Amount

The top 10 products with increases in sales by 250 or greater display a green upward arrow.
The Bottom 10 Products table shows the products which had the lowest sales increases (or highest declines) from the comparison period to the current period.

Bottom 10 Products by Change in Sales Amount
Bottom 10 Products by Change in Sales Amount

Products with decline in sales amount of 150 or greater will have a red downward arrow.

METRIC 3: % CHANGE IN SALES AMOUNT

Similarly, we can also use a metric of ‘% Change in Sales Amount’.

Sort by % Change in Sales Amount - Set thresholds
Sort by % Change in Sales Amount – Set thresholds

Now, when we set thresholds, we have to enter % values and we do that by entering decimal values. For example, we can enter 20% as 0.2 and -15% as -0.15.

In the report, the Top 10 Products table shows the products which had the top 10 sales increases (as %) from the comparison period to the current period.

Top 10 products by % Change in Sales Amount
Top 10 products by % Change in Sales Amount

The products with increases in sales by 35% or greater display a green upward arrow.
The Bottom 10 Products table shows the 10 products which had the lowest sales increases (as %) from the comparison period to the current period.

Bottom 10 Products by % Change in Sales Amount
Bottom 10 Products by % Change in Sales Amount

The products with decline in sales by 15% or greater display a red downward arrow.

HOW TO EXTEND THE SALES REPORT TEMPLATE

How to expand to more than 100 products

There is no limit on size of the sales data set. The larger the data set, when we change the settings, Excel might think for a few seconds before refreshing the report.

However, the template is set up to handle 100 products in order to keep the file small and fast. If you have more than 100 products, you can follow the steps below.

Right Click on any sheet name and choose Unhide.

Right Click on a sheet name and choose Unhide
Right Click on a sheet name and choose Unhide

Choose H_PRODUCTS sheet to unhide.

Choose H_PRODUCTS sheet to unhide
Choose H_PRODUCTS sheet to unhide

Now in the H_PRODUCTS sheet, go to cell E102.

Expanding the table of calculations to handle more than 100 products
Expanding the table of calculations to handle more than 100 products
  • First, click outside the table. For example, click in cell F105.
  • Then, click the little arrow in cell E102 and drag down to expand the table to more products (as many as you need).
  • The report will now reflect more than 100 products. Easy.

CHANGING TODAY

The template calculates TODAY’s date using a formula and then uses that date in calculations such as ‘Today’, ‘This Week’, ‘Last 7 Days’, ‘Last Week’, ‘Last 30 Days’, ‘This Month’ and ‘Last Month’.

Change Report Date from Today to Any date
Change Report Date from Today to Any date

If there is a need to create a report that is a snapshot of the past (how the report would have looked if we had exported on any date in the past), we can easily do that by replacing the formula with a specific date.

PRINT OR EXPORT

You can print the report sheet or export to PDF using Excel’s standard settings.

CHANGE CONDITIONAL FORMATTING

If you prefer to change the arrow styles or colors, it is easy to do.
First, click on Conditional Formatting in the Home ribbon and select ‘Manage Rules’.

Choose Conditional Formatting - Manage Rules
Choose Conditional Formatting – Manage Rules

Then, select ‘This Worksheet’ from the drop down menu so that we can see all the rules applied.

Select 'This Worksheet' in the Conditional Formatting drop down menu
Select ‘This Worksheet’ in the Conditional Formatting drop down menu

Now, scroll down and see the last rule where we have applied the arrows/icons.

Select the specific rule and click on Edit Rule
Select the specific rule and click on Edit Rule

When we click on the rule and then click on Edit rule, we see the following window.

Conditional Formatting rule used for arrows
Conditional Formatting rule used for arrows

We can change the icons by clicking on the arrow next to the icon.

Change the icon as preferred
Change the icon as preferred

Once you choose the icon you prefer, just click on OK in the dialog boxes that appear.  This is how you can modify the icons.

17 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *