How to Create a Year-over-Year Product Sales Comparison Chart in Excel
Question details
The user wants to construct a chart that compares product sales performance across multiple years, using stacked columns or advanced visualizations.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Visualizing multi-year product sales categorized by year to effectively present year-over-year growth and product contributions.
- Observed behavior
- Excel natively supports stacked column charts for this visualization, but users often look for Power BI's Ribbon Chart, which Excel does not feature natively without third-party add-ins.
Ensure your raw sales data is well-organized in a tabular format, with products listed in rows and respective sales years arranged in distinct columns.
Use a Stacked Column Chart for Year-over-Year Comparisons
A stacked column chart is the easiest and most effective native method in Excel to visualize how individual products contribute to overall sales across different years.
By utilizing Excel's built-in charting features, you can plot years on the horizontal axis and stack product sales vertically, immediately showing both total sales per year and the proportional breakdown of each product.
Highlight your entire data range, making sure to include both the product names and the year headers.
Navigate to the 'Insert' tab on the Excel ribbon, locate the Charts group, and click 'Insert Column or Bar Chart'.
From the dropdown menu, select the 'Stacked Column' option to instantly generate your year-over-year comparison chart.
Create Ribbon-Style Charts Using Third-Party Add-ins
Since Excel lacks the native Ribbon Chart found in Power BI, you will need a third-party add-in to visualize flowing, ribbon-like connections between product sales over time.
Summarize Raw Data Using a PivotChart
If your raw data is listed transactionally rather than summarized by year, use a PivotTable and PivotChart to automatically consolidate and visualize the sales.
Create Stunning Year-over-Year Sales Charts in WPS Spreadsheet
WPS Spreadsheet provides a robust and intuitive charting engine, allowing you to easily build stacked column charts, PivotCharts, and advanced visual summaries for your sales data without complicated setups.
- 1. Open Your Data File: Launch WPS Spreadsheet and open your existing Excel workbook containing the multi-year sales data.
- 2. Highlight Data: Select the cells containing your year headers, product names, and corresponding sales figures.
- 3. Insert Chart: Go to the Insert tab on the top ribbon, click Chart, and choose the Stacked Column format.
- 4. Customize Appearance: Use the quick chart tools on the right panel to modify labels, colors, and the legend for a professional finish.
Frequently Asked Questions
Does Excel have a native Ribbon Chart like Power BI?
No, Microsoft Excel does not natively feature the Ribbon Chart visualization found in Power BI. To achieve that specific flowing layout, you must use a third-party add-in or migrate the data to Power BI.
Why are my years showing as chart series instead of the horizontal axis?
Excel may misinterpret year headers (like 2022, 2023) as data values because they are numbers. To correct this, select your chart, open the Chart Design tab, and click 'Switch Row/Column'.
Can I use dynamic arrays to prepare data for a comparison chart?
Yes. In modern versions of Excel, you can use functions such as UNIQUE, SORT, and FILTER to dynamically summarize raw transactional data into a neat table, which can then be charted easily.
