Ranking Best-Selling Items by Month in Excel with Formulas and VBA

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical

Have you ever needed to rank your best-selling items by month, but struggled with the complexity of Excel formulas or the limitations of manual sorting? You’re not alone. This is a common challenge in data analysis and reporting. In this article, we’ll explore why ranking best-selling items can be difficult, walk through step-by-step solutions using both formula-based methods and VBA for automation, and highlight advanced variations that make your life easier.

Why Ranking Items by Month Can Be Challenging

The primary challenge in ranking best-selling items by month lies in the need to dynamically update rankings based on changing data. Manual sorting is time-consuming and error-prone, while complex formulas can be difficult to maintain. Additionally, many users struggle with understanding how to use Excel’s built-in functions like RANK or LARGE effectively.

While you can do this manually, CelTools automates this entire process through its advanced data analysis features. This tool can save hours of manual work and provides a more reliable solution for frequent users.

Step-by-Step Solution: Ranking Items with Formulas

Let’s break down the steps to rank your best-selling items by month using Excel formulas:

Step 1: Prepare Your Data

Ensure your data is organized in a table format, with columns for date, item name, and sales quantity. Here’s an example:

Step 2: Extract Sales by Month and Item

Create a PivotTable to summarize sales data by month and item:

1. Select your data range.
2. Go to the “Insert” tab and click on “PivotTable”.
3. Drag “Date” to Rows, “Item Name” to Columns, and “Sales Quantity” to Values (summed).
4. Group dates by month in the PivotTable.

Step 3: Rank Items Using Formulas

To rank items within each month, we can use a combination of IF, RANK.EQ, and SUMIFS functions. Assuming your summarized data starts at cell B2:

=IF(SUMIFS($C$2:$C$100,B$2:B$100,$B2,A$2:A$100,"Product A")>0,
    RANK.EQ(SUMIFS($C$2:$C$100,B$2:B$100,$B2,A$2:A$100,"Product A"),
            SUMIFS($C$2:$C$100,B$2:B$100,$B2,A$2:A$100)>0,
            "A"&"Product A"),
    ""
)

Step 4: Display Top 5 Items for Each Month

Use the RANK function to filter and display only the top 5 items for each month:

=IF(AND(B2=B1, RANK.EQ(SUMIFS($C$2:$C$100,B$2:B$100,$B2,A$2:A$100), SUMIFS($C$2:$C$100,B$2:B$100,$B2,A$2:A$100)>0) <= 5),
    A2, "")

This formula checks if the item’s rank is within the top 5 and displays it accordingly.

Step 5: Automate with VBA (Optional)

For advanced users or those dealing with large datasets, using VBA can streamline this process:

Sub RankItemsByMonth()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Add headers for ranked items
    ws.Cells(1, 5).Value = "Ranked Item"
    ws.Cells(1, 6).Value = "Sales Quantity"

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    Dim month As String
    Dim prevMonth As String
    Dim rankCounter As Integer

    For i = 2 To lastRow
        month = Format(ws.Cells(i, 1).Value, "YYYY-MM")
        If month  prevMonth Then
            rankCounter = 0
        End If

        rankCounter = rankCounter + 1
        ws.Cells(i, 5).Value = ws.Cells(i, 2).Value & " - Rank: " & rankCounter
        ws.Cells(i, 6).Value = Application.WorksheetFunction.SumIfs(ws.Range("C:C"), ws.Range("B:B"), ws.Cells(i, 2).Value)

        prevMonth = month
    Next i

    ' Filter to show top 5 items per month (add your filtering logic here)
End Sub

Advanced Variation: Using CelTools for Enhanced Analysis

For those looking to go beyond the basics, CelTools offers powerful features that can analyze and rank data with a single click. This tool is particularly useful for complex datasets or when you need to perform this analysis regularly.

Common Mistakes and Misconceptions

• Ignoring PivotTables: Many users try to rank items directly from raw data, which is inefficient. Using PivotTables simplifies data aggregation.
• Incorrect Rank Formulas: The RANK.EQ function needs the right reference range and criteria; ensure you’re using SUMIFS correctly within it.
• Avoiding VBA for Automation: For repetitive tasks, automating with VBA or tools like CelTools can save significant time and reduce errors.

Technical Conclusion: The Power of Combining Manual Skills with Specialized Tools

Ranking best-selling items by month in Excel is a task that combines both manual formula work and, for advanced users, automation through VBA or specialized tools. While manual methods provide flexibility and control, tools like CelTools offer enhanced capabilities for efficiency and accuracy.

The approach you choose depends on your specific needs – whether it’s a one-time analysis or recurring task that requires automation. By combining these techniques, you can ensure robust, reliable data insights every time.