Forget Streamlit: Create an interactive data science dashboard in Excel in minutes

Photo by Editor | Midgorn

Although stimulatory data such as stimulatory data are popular to create dashboards, Excel is one of the most accessible and powerful platforms for building interactive data visualization. Using the Built -in Excel features, you can create an interactive dashboard that competitors popular data science web apps.

In this tutorial, we will show how to make interactive data science dashboards in Excel without a series. We will demonstrate using a simple e -commerce sales dataset.

Step 1: Preparing your dataset

We will divide this step into sub -ingredients and deal with each by one.

Set your data

We will use to configure Excel workbook, follow these steps:

1. Open a new Excel workbook
2. Import your data into Excel
3. Go to Data Tab >> select Get data >> Select your file type
4. Perform any dataset cleaning or maintenance that requires

Change to Excel table

Next, let’s turn your data into an Excel table. Tables make it easier to build formulas, pyrotables and dynamic boundaries.

1. Select your entire Dataset
2. Go to Insert Tab >> select Schedule (Or press Ctrl+T)
3. Be sure There are headers in my table Have been checked
4. Click Okay

5. Name your table sales data:
   • Click anywhere in the table
   • Go to Table design Tab >> select The name of the table >> Type Sales data

Step 2: Create interactive axis table

Create Axis Table:

• Select any cell in the sales data table.
• Go to Insert Tab >> select Pivottable.
• Choose Location: New worksheet.
• Click Okay.

Tackle up to the month:

• I Puteable Field List:
  • Rows: History (group through months)
  • Values: The amount of sales

Regional Performance:

• Insert another pivotable.
• I Puteable Field List:
  • Rows: Area.
  • Values: Sale amount, units sold.
  • Graph: Currency for sale money.

Product category analysis:

• Insert another pivotable.
• I Puteable Field List:
  • Rows: Category
  • Values: The amount of sales
  • Sort: Get out of the sales amount.

KPis Pivot Table:

• Insert another pivotable.
• I Puteable Field List:
• Values:
  • Collection of sales money.
  • A collection of units was sold.
  • Cost collection
  • Counting of sales (for average calculation).
  • Do not add a row or column (this gives us overall).

Step 3: Create an dynamic chart

The trend line chart of taxes:

• Choose the monthly revenue axis table.
• Go to Pivottable analysis Tab >> select Axis chart >> Select Line chart.
• Chart format:
  • Chart Title: Monthly revenue trend.
  • Add data label: Extend Chart elements >> Click Data label.

Regional Performance Column Chart

• Choose regional axis table.
• Go to Pivottable analysis Tab >> select Axis chart >> Select Clusted column.
• Figure:
  • Title: Sales in terms of region.
  • Different colors for each region.
  • Data label on the upper part of the columns.

Product Category Pie Chart

• Select the axis table of the product category.
• Go to Pivottable analysis Tab >> select Axis chart >> Select Pi chart.
• Figure:
  • Title: Revenue through the product category.
  • Show percentage.
  • Use different colors.

Step 4: Add Interactive Slacear

Enter the slicer:

• Click on any axis table.
• Go to Pivottable analysis Tab >> select Insert the slicer.
• Select these fields:
• Click Okay.

Insert the timeline:

• Click on any axis table.
• Go to Pivottable analysis Tab >> select Insert the timeline.
• Select History.
• Click Okay.

Connect to Slacars with all axis tables:

• Right -click on any slicer >> select Report contacts.

• Check out All Axis tables.
• Click Okay.

Repeat for each slick to ensure that they can overcome all the chart.

Step 5: Create Dynamic PI card

You can directly calculate the KPI matrix in the dashboard or later put it in a dashboard sheet.

Create now KPIs that refer to this axis table:

Total Sale:

• Select a cell and insert the following formula.

=GETPIVOTDATA("Sum of Sales Amount",'KPIs from Pivot Table Data'!$A$3)

Average Order Price:

• Select a cell and insert the following formula.

=GETPIVOTDATA("Sum of Sales Amount",'KPIs from Pivot Table Data'!$A$3)/GETPIVOTDATA("Count of Sales Amount",'KPIs from Pivot Table Data'!$A$3)

Total units were sold:

• Select a cell and insert the following formula.

=GETPIVOTDATA("Sum of Units Sold",'KPIs from Pivot Table Data'!$A$3)

The profit margin %:

• Select a cell and insert the following formula.

=(GETPIVOTDATA("Sum of Sales Amount",'KPIs from Pivot Table Data'!$A$3)-GETPIVOTDATA("Sum of Cost",'KPIs from Pivot Table Data'!$A$3))/GETPIVOTDATA("Sum of Sales Amount",'KPIs from Pivot Table Data'!$A$3)

Total order:

• Select a cell and insert the following formula.

=GETPIVOTDATA("Count of Sales Amount",'KPIs from Pivot Table Data'!$A$3)

Format’s PI cards:

• Apply borders and align.
• Format Number:
  • Tackle: Currency Graph
  • Percentage: Percentage Format with 2 decimal.
• Bow the label and add the background color.

Step 6: Make a dashboard structure

• Make a new sheet and name it dashboard.
• Hide grid lines:
  • Go to See Tab >> select Show >> Check non -check Grid lines.
• Insert the title of the dashboard.
• Keep the KPI matrix on top.
• Insert slices and a timeline.
• Place the chart below.
• Insert the data table if needed.

Refresh and automatic: Click right Pauteable/Chart >> Select Refresh.

Step 7: Check your dashboard

Test of functionality:

• Choose books category + North Region + Bob Selppers from slices.
• Select the January 2025 from the timeline.
  • Confirm that all charts update simultaneously.
  • Check that KPI has been re -counted properly.
  • Make sure no mistakes are made.

Repeat ordinary problems

• Charts are not updating: Check the slice connection (right -click slicer> Report connection). Make sure all axes are selected.
• Formula’s errors: #Ref! Or #Value! Mistakes in KPI. Check table references (make sure the sales data table name is correct).
• Performance Problems: Dashboard is slow to update:
  • Reduce the number of tables.
  • Easy to complex formulas.
  • Use a manual calculation (formula> calculation options> manual).

Conclusion

By following the aforementioned steps, you can create an interactive data science dashboard in Excel in minutes. These steps will help you create sophisticated dashboards that provide real business costs without touching a line of codes. The best thing is that your stakeholders can communicate and edit with the dashboard themselves, which can really cooperate with the business intelligence tool.

Shemima Sultana Accelel works as a project manager, where she researchs Microsoft Excel and writes articles on her work. Shemima has a BSc degree in computer science and engineering and is interested in research and development. Shamima likes to learn new things, and is trying to provide enriched quality content in Excel, while always trying to collect knowledge from different sources and make modern solutions.