Many people are used to Microsoft Excel to do various tasks.
In fact, we can make dashboard sales data using Microsoft Excel.
This project I got from MySkill Course with sales data. In the dashboard it shows total sales, total profit total quantity, and profit margin, trend of sales & profit margin, sales in each segment, and top 10 product based on sales. To make it easier to see the analysis, I added a filter using slicer.
I worked on this project using Microsoft Excel 2016. In sales data, there are 23 columns. There are Order ID, Order Date, Ship Date, Ship Mode, Customer ID, Customer Name, Segment, Country, City, State, Postal Code, Region, Product ID, Category, Sub-Category, Product Name, Sales, Quantity, Discount, Profit, Calendar, Month, and Year.
Pivot Table
The pivot table I make are pivot table sum sales, profit, quantity, and profit margin.
To vizualize the results, I used textbox and rectangle shape for background.
Pivot Chart
Before I made pivot chart, I made pivot table for trend of sales &profit margin, sales in each segment, and top 10 product based on sales.
For trend of sales and profit margin, I used combo chart. For sum of sales I used column chart and for sum of profit margin, I used line chart for easier to see the chart.
Slicer
I used slicer to easier to filter the sales analysis. I made slicer for year, calendar, segment, region, and state. Before I made month filter, I made table pivot for month, but I just showed table pivot in dashboard. If I made pivot table month without slicer month, the filter didn’t work.
Conclusion
Microsoft Excel can be use to make sales dashboard. Before we made pivot chart and slicer, we must make table pivot. It’s easier to see result with pivot chart than to use pivot table. To see more specific result, we can use filter and slicer features.
