How to Create an Interactive Sales Dashboard in Excel | Step-by-Step Excel Dashboard Tutorial, by Excel Learn and Apply on YouTube. 8 min 53 s.
- Video length
- 8 min 53 s
- Time to do it
- 45 minutes
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Clean your sales sheet first. Put one record per row, keep a single header row, remove blank rows and make sure date, sales and profit columns hold real numbers, not text.
Select the data range and press Ctrl+T to make it a table. Give the table a clear name in Table Design so new rows flow into your Pivot Tables.
Insert a Pivot Table on a new sheet. Drag Sales, Profit and Order ID into Values to get Total Revenue, Total Profit and Total Orders.
Add a calculated field for profit margin. Use Profit divided by Sales, then format the result as a percentage.
Build the KPI cards. Copy each Pivot Table value into a shaped box on the dashboard sheet, or link a cell with a formula, then add a label under each number.
Create a Pivot Table with Date in Rows and Sales in Values, group the dates by month, and insert a line chart for the monthly sales trend.
Make Pivot Tables for Sales by Region, Profit by Region and Sales by Category, then insert a bar or column chart for each one.
Sort a customer Pivot Table by Sales in descending order and filter to the top 5. Add a chart or a small table for Top 5 Customers.
Add a Pivot Table with Product in Rows and Sales plus Profit Margin in Values, then chart it to compare products.
Insert slicers for Year, Segment and City. Right-click each slicer and use Report Connections to link it to every Pivot Table on the dashboard.
Tips and common mistakes
Keep all Pivot Tables on hidden helper sheets and place only charts and KPI cards on the dashboard sheet.
Use Report Connections on every slicer, or some charts will not update when you click a filter.
Pick one colour palette and the same font size for all KPI cards so the dashboard looks consistent.
Refresh the data with Ctrl+Alt+F5 after adding new rows to the sales table.
Questions
Do I need Power Pivot or macros?
No. Standard Pivot Tables, charts and slicers are enough for this dashboard. Power Pivot only helps if your data is very large.
Why does my slicer not change all the charts?
The slicer is probably connected to only one Pivot Table. Right-click it, choose Report Connections and tick every Pivot Table used in the dashboard.
Can I update the dashboard with new sales data?
Yes. Paste new rows inside the source table, then refresh each Pivot Table and the charts and KPI cards will update.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
