Dashboard for comparative sales analysis by year in Excel

Comparing monthly metrics across different years helps identify trends faster and make informed decisions. Comparative analysis shows how much current results are ahead of or behind past periods. Excel makes this visual with an interactive dashboard that combines key metrics in one view.

Example of a dashboard for comparing sales metrics in Excel

Charts in Excel have specific settings for overlaying data so that you can conveniently compare them in a single template. Based on this principle, a complete dashboard was built where you can consistently compare various key metrics for the current and previous year. The dashboard includes interactive features to highlight selected important periods of change on the chart. You can download the ready-made template at the end of the article and adapt it to your needs.

You can learn to create such dashboards for comparative analysis yourself. This video tutorial will help you a lot, showing:

1. Designing the grid layout for placing dashboard visualization blocks.
2. Creating the first table for interactive visualization of current and previous data in a comparative analysis.
3. Organizing source data into Excel tables and pivot tables.
4. Dynamic formulas to extract source values from pivot tables on the fly.
5. A combined bar chart template for comparing current metrics with the previous period in Excel.
6. Adding dashboard controls and styling them to match the interface.
7. Developing a custom switch to compare current data with the previous period.
8. A visualization block for product sales quantity metrics.
9. A visualization block for monthly revenue trend changes.
10. Resource allocation across product categories considering imbalance.
11. A visualization block for lead-to-sale conversion.
12. Creating dynamic labels for KPI cards in the dashboard header.
13. A comparative analysis screen for sales volume vs. the previous year.
14. Functional design for comparing monthly revenue for the current and previous year.
15. A screen to analyze imbalance in resource allocation for main product categories.
16. A chart for comparing conversion rates in the current and previous reporting period.
17. Interactive navigation across a multi-screen dashboard in Excel — without using VBA macros.
18. Presentation of dashboard capabilities for comparing current vs. previous sales metrics.

Full description of each chart’s purpose on the dashboard:

How to compare monthly sales metrics by year in an Excel dashboard.

Using an interactive dashboard in Excel, you can easily analyze and compare metrics by year without extra tables or manual calculations. This approach saves time and lets you focus on conclusions rather than data preparation.

Download Dashboard for comparative sales analysis by year in Excel