Determine best/fastest-selling products from list of sales records in Excel

WeThotUWasAToad
WeThotUWasAToad used Ask the Experts™
on
Hello,

What Excel method(s) are most useful or valid for analyzing the relative sales success of a number of products?

That is a much less-specific question than I would like to be asking but suppose you have a sales report with the following information:

        Products: 200 SKU's
        Date Range: 3 years
        Number of Sales: 11,000+

...and containing the following data (file attached):

 2017-09-05c_EE.png
I had in mind creating a chart with the [# of items sold] as the vertical axis vs [time elapsed since product introduced] as the horizontal axis. However, that would be quite cluttered with 200 products so I'm interested in other approaches.

Thanks
Sales-report_2017-09-05_EE.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

Commented:
'fastest' can be interpreted several ways.  You should nail down this definition.

You can create a pivot table by SKU and order by sales volume.  That would be one approach.

You might use the DSum() function in a formula and order by that column.  Another approach.
Sr. System Analyst
Commented:
have a look at this pivot...
I added 3 columns in pivot

max number of units sold
total sales
total units sold

per product... it is a summary of what you have...
Sales-report_2017-09-05_EE.xlsm
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You may insert a Pivot Table and filter it to show top N items by sold units and that would give you an idea about the best selling products.
In the attached, you will find a Pivot Table showing the top 25 best selling products on Summary Sheet along with a Clustered Column Pivot Chart to visualize the top N products.
See if this is something you can work with.
Sales-report_2017-09-05_EE-2.xlsm

Author

Commented:
Thanks for the insightful responses and attached examples.

I have been seeing references to Pivot Tables for years and based on your comments, I think it's high time I dig in and really start to understand them.

I googled the question,"What is a pivot table?" and came across a few definitions:

    1) A pivot table allows you to extract the significance from a large, detailed data set.
    2) A pivot table is a program that allows you to reorganize and summarize selected columns and rows of data in the spreadsheet or database table to obtain a desired report.
    3) A pivot table is a special Excel tool that allows you to summarize and explore data interactively.
    4) A Pivot Table gathers all the data in an Excel spreadsheet (or range of a spreadsheet) and presents a summary of this data in a table. This table allows you to see, at a glance, information such as:
        ◊ The number of items of each type;
        ◊ The sum of a data column, broken down into data types;
        ◊ The average of a data column, broken down into data types.
    5) A pivot table is a table that summarizes data in another table, and is made by applying an operation such as sorting, averaging, or summing to data in the first table, typically including grouping of the data. (Wikipedia)

Could you comment re which definition(s) you prefer and also add any other thoughts that come to mind?

Also, perhaps the most helpful thing I found, was a video (with description #3 at ExcelJet.net) in which you are able to watch as he creates and modifies the pivot table:
        ◊ https://exceljet.net/tips/what-is-a-pivot-table

For the two of you who attached Pivot Tables, could you describe the specific steps you followed to create your Pivot Tables?

It would would be very helpful if I could start from scratch and create workbooks identical to what you each attached.

Thanks

Author

Commented:
Thanks for the comments. I've learned a lot about PivotTables since opening this thread.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial