Forget Sysklit Directory: Create Interactive Data Dashboard in Excel in minutes

Photo for Editor | Elder

While the Python's based tools are famous for creating data Dashboards, Excel always is one of the most widely available platforms to create effective data recognition. Using Excel's built-in Excel's features, you can create a dashboard that communicates that the famous web apps.

In this lesson, we will show how to build Interactive Data Science Dashboard in Excel in minutes without being decreased. We will show using a simple sales of the e-commerce dataset.

Step 1: Preparing Your Dataset

We will divide this step into subcoments and deal with each other.

Set your data

To set the Excel operating book We will use, follow these steps:

1. Open a new workbook
2. Import your data into Excel
3. Go to Writing TBO:> Select Get data >> Select your file type
4. Make any data cleaning or correction that may be required

Convert on Excel Table

Next, let's change our details on the Excel table. Tables make it easy to build formulas, pvoting, and dynamic reasons.

1. Choose all your information
2. Go to Invest TBO:> Select Table (or press CTRL + T)
3. Ensure My table has articles tested
4. Click Okay

5. Say your Tauldata Name:
   • Click anywhere on the table
   • Go to The construction of the table TBO:> Select Table Name Start >> Type Merchant

Step 2: Create active pivot tables

Create a pivot table:

• Choose any cell on the Saltata table.
• Go to Invest TBO:> Select Ignore.
• Select Location: New Workspaper.
• Click Okay.

Month monthly:

• In PivotTable Fieldsthe Framework:
  • Lines: Date (team on months).
  • Jewelry: The amount of sales.

District performance:

• Add another pivottable.
• In PivotTable Fieldsthe Framework:
  • Lines: Region.
  • Jewelry: The amount of sales, units sold.
  • Format: Money for sales value.

Product Class analysis:

• Add another pivottable.
• In PivotTable Fieldsthe Framework:
  • Lines: The category.
  • Jewelry: The amount of sales.
  • Select: Dropping with the price of sales.

KPIS Pivot Table:

• Add another pivottable.
• In PivotTable Fieldsthe Framework:
• Heritage:
  • The sum of the sales value.
  • The sum of the units sold.
  • The total amount of cost.
  • Counting of the sales amount (calculation calculation).
  • Don't add any lines or columns (this gives us prices.

Step 3: Create dynamic charts

Revenue Trend Line Chart:

• Choose a pivot amount of moon pivot.
• Go to Pivottable analyze TBO:> Select Pivot chart >> Select Chart.
• Insert a chart:
  • Chart's title: Month month custom.
  • Add data labels: Expand Chart items >> Click Data labels.

The District Colume Chart

• Choose a regional pivot table.
• Go to Pivottable analyze TBO:> Select Pivot chart >> Select Collaborated Column.
• Format:
  • Topic: Sales in a region.
  • Different colors in each region.
  • Data labels over the columns.

The chart of a pie product class

• Select the pivots pivot table.
• Go to Pivottable analyze TBO:> Select Pivot chart >> Select Pie chart.
• Format:
  • Topic: Revenue with a product class.
  • Show Percentage.
  • Use different colors.

Step 4: Add active slicers

Add slicers:

• Click on any pivot table.
• Go to Pivottable analyze TBO:> Select Add Slicer.
• Select these fields:
• Click Okay.

Enter the timeline:

• Click on any pivot table.
• Go to Pivottable analyze TBO:> Select Put the timeline.
• Designate Palm tree.
• Click Okay.

Connect slicers to all pivot tables:

• Right-click any slicer >> Select Report Connection.

• Check entire pivot tables.
• Click Okay.

Repeat each slicer to ensure that all controlling all the charts.

Step 5: Create Ki Dating Cards

You can count exactly the metric cage directly to the dashboard or later in the Dashboard paper.

Now create KPIS referring to the pivot table:

Complete sales:

• Select the cell and enter the next formula.

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

The number of the intermediate order:

• Select the cell and enter the next 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)

Complete units sold:

• Select the cell and enter the next formula.

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

Margin Margin%:

• Select the cell and enter the next 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 the cell and enter the next formula.

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

Formate KPI cards:

• Add boundaries and alignment.
• Numbers of the genre:
  • Net worth: Money format.
  • Percentage: Part of a large Formata with 2 decimals.
• Bold labels and add background color.

Step 6: Create a Dashboard Building

• Create a new sheet with an IT Dashboard name.
• Hide GRIVINES:
  • Go to Vision TBO:> Select Demonstrate >> Do Not Check Games.
• Enter the Dashboard title.
• Enter the KPI Metrics up.
• Add slicers and timeline.
• Put the charts down.
• Insert the data table if needed.

Update and activate by car: Right-click Pivottottie / chararts >> Select Refresh.

Step 7: Check your Dashboard

Test Examination:

• Choose the book + of the North Region + Bob Saboserson from Slicers.
• Choose Jan 2025 from time line.
  • Make sure all the charts update at the same time.
  • Check that KPIs are well repeated.
  • Make sure let's appear.

Problems Common Problems

• Charts can be updated: Check the missing connection (right-click slicer> Report connection). Make sure all pivot tables are selected.
• Formula errors: #Ref! or #frue! KPIS errors. See table references (Make sure that the Salesdata table name is correct).
• Aposticinal problems: Dashboard slowly moving:
  • Reduce the number of pivot tables.
  • Simple complex formulas.
  • Use the hand count (Formules> Count options> Manual).

Store

By following the above steps, you can create an Interactive Data Excel Data Excel in minutes. These steps will help you create appboators that provide for real business value without a single Python code line. The best part of your participants can communicate and change the dashboard themselves, make it a worker with a co-operation tool.

Shamima Sultana It works as an Exceldemi project administrator, where they research it in Microsoft Excel and write the risks related to his work. Shamima holds BSC in the computer science and engineering engineering and you are very interested in research and development. Shamima likes to learn new things, and she tries to provide satisfactory content for the enhanced in relation.