Bottom line: Create a summary report that counts transactions by a category when the data contains multiple rows per transaction.
Skill level: Intermediate
Download the Excel File
You can download the Excel file that contains the source data for the challenge below.
Data Analysis Challenge.xlsx (16.1 KB)
This data analysis challenge is based on a great question from Rob, a member of our Elevate Excel Training Program.
He has sales pipeline (CRM) data and wants to create a summary report of deal count by sales stage. The problem is that each deal/transaction has multiple rows in the source data. The data includes rows for each product in the deal.
However, Rob only wants to count each deal one time in his summary report by sales stage. Here is an image of what the end result should look like.
Your mission, should you choose to accept it, is to use any of the tools or features in Excel to create the summary report.
I have two additional challenges for you. These are not required, but do force us to think about making the report flexible and dynamic for future updates.
1. Account for New Data
Is it easy to include the new data in your results?
Your solution should be able to easily recalculate the results when new data is added to the source range. I put some additional data on the New Data tab in the workbook. You can copy/paste this below the original source data to test your solution.
We don't want to have to completely recreate the report every time the data is updated.
2. Account for New Sales Stages
What if the stage names change, or new stages are added to the system?
Currently there are three stages (Win, Loss, Prospect). The data on the New Data sheet includes a new stage, Proposal. Your solution should also be able to automatically include this new stage in the results. Or include it without too much additional work.
Share Your Solution
I love this question from Rob because there are A LOT of different ways to solve this problem in Excel. And of course, pros & cons to each.
However, I want you to give it a try! Don't worry about it being the “perfect” solution. That probably doesn't exist anyways…
You can leave a comment below or on the YouTube video with a brief description of the tools & techniques you used to solve the challenge. You can also upload your file to any cloud service like OneDrive, Google Drive, or Dropbox and post the share link in your comment.
I will follow-up with a video(s) explaining the most popular techniques and the pros & cons of each. We look forward to seeing your solutions and learning from everyone that participates. Thanks! 🙂