Forum Discussion
Visualizing Year-over-Year Sales Data with Comparison Chart in Excel
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
This is no more elegant, even though it does manage to cram those three SORT(FILTER functions into a single HSTACK and do it all as "one formula" So I'll keep playing. Thank you for the opportunity.
=HSTACK(
SORT(FILTER($B$2:$C$19,$A$2:$A$19="Y-2022"),2,-1),
SORT(FILTER($B$2:$C$19,$A$2:$A$19="Y-2023"),2,-1),
SORT(FILTER($B$2:$C$19,$A$2:$A$19="Y-2024"),2,-1)
)
My suspicion is that the PIVOTBY function may be one that would work--maybe even with your raw data-- but that's only available currently in Beta.
Once again, all of the dynamic array functions are explained in this on-line resource.
- I express my sincere gratitude for your assistance in this matter.
I comprehend your explanation regarding the data transformation process. However, I kindly request a chart or visualization that aligns with the example provided in my initial inquiry.
ThanksI am going to have to defer to some of the other folks in this forum. I do apologize, if it was the visualization in particular that you were seeking help with. I was obviously focusing exclusively on the data side of things.
I just don't do much, haven't for years, with graphic representation of data. In fact, I would have to play around with the options available under the Insert tool bar icons, at least as much as you would have to, if not more.
Given your awareness of what can be done with Power BI, I suspect you're way ahead of me.
But perhaps one of the other Excel forum volunteers can chime in with the appropriate method. Best wishes.
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,