Forum Discussion
Visualizing Year-over-Year Sales Data with Comparison Chart in Excel
Hello everyone,
I have sales data for different products over multiple years, and I'm looking for the best way to visualize this data in Excel. Specifically, I want to create a comparison chart that displays:
- Year-wise sales in columns: Each year should be represented by a column.
- Product sales in rows or stacks within columns: The rows, boxes, or stacks should be scaled based on the sales figures for each product in that specific year.
- Linking products across years: There should be a visual connection (like a ribbon or thick link) between the same products across different years, helping to illustrate the increase or decrease in sales.
I created a visual mock-up in MS Paint to help convey my idea of how I want to visualize the data in Excel.
I'm aware that Power BI offers a Ribbon Chart that might achieve a similar effect. However, I'm looking for a solution within Excel.
Could anyone suggest the best approach or chart type to accomplish this in Excel? If possible, please provide a step-by-step guide or any tips to achieve this visualization effectively.
Thank you!
Hi Qaiser_j
While Excel doesn't natively offer a "Ribbon Chart" like Power BI, you can still create an effective comparison chart that meets your requirements with some creativity.
You can create a Stacked Column Chart to represent year-wise sales with products stacked within the columns, follow these steps:
Select your data range (including product names and year-wise sales).
Go to the Insert tab in Excel --> Charts group --> click on Insert Column or Bar Chart --> select Stacked Column.
This will create a chart where each year is represented by a column, and the sales of each product are stacked within those columns.
If you’re looking for something more advanced than Excel's native charts, consider using an 3rd party data visualization add-on. They can help and offer many advanced charts, including ribbon-like visualizations that can easily showcase year-over-year product comparisons.Suggestion from my experience:
To create similar comparison chart in Excel
Watch this tutorial to get clear understanding.You've had a LOT of views without anybody attempting a reply. Given that there are many folks here on these boards far more knowledgeable about the advanced features of Excel than I, I'm a bit wary of even responding. But, let's give it a try.
First of all, since you have NOT shared any example of how your raw data are arranged, it's kind of difficult to know what function or method (Pivot Table?) could be used to first summarize the data to serve as a basis for the visualization itself. So one thing you could do to help us help you would be to post EITHER the actual spreadsheet you have of sales data over these several years OR (if the actual contains confidential or proprietary data, which is entirely likely) a reasonable mockup that conceals the specifics but does represent accurately how the raw data is arranged. A spreadsheet can usually be posted with a reply, using the area at the bottom of the box where you write your text.
If you're not able to attach directly to your post, you could put a copy on OneDrive or GoogleDrive (or equivalent) with a link pasted here that grants access.
All of that having been said, if for whatever reason you're not willing or able to post such a spreadsheet, I wonder if you're familiar with the dynamic array functions, which are amazingly powerful tools that could be used in combination to produce such things as a custom pivot table, each column being sorted by product with largest sales for the year, and so on.
Himathetes,
Thank you for your reply and willingness to help. I've attached an Excel file with a sample spreadsheet that closely resembles my actual data. I've ensured that the file is in the standard .xlsx format and have removed any sensitive information.
Please let me know if you have any trouble accessing or opening the file. I appreciate your time and look forward to your suggestions or methods that may be helpful for my data visualization.
Thanks,
Qaiser
Let's start with this. I'm hoping you'll be able to take it to the level of the graphs. What I've given you is a formula that sorts into descending order for each of the three years.
=SORT(FILTER($B$2:$C$19,$A$2:$A$19=I1),2,-1)
I use the formula three times, under the heading in row 1 that supplies the relevant year. It looks like this. See also the attached file.
I'm going to continue to play with this, to see if I can come up with a single formula that does the sorting vertically by volume of each product AND arrays the three years' results horizontally. I'm quite sure it's possible and it's a fun intellectual challenge and opportunity to learn another dynamic array functions,
