Practical 20 – Creating an Interactive Dashboard in Excel

1. Objective

The primary objective of this practical is to enable students to design and create a functional and interactive data dashboard in Excel. Students will learn to:

·         Integrate multiple Excel features to build a cohesive dashboard.

·         Design an intuitive layout for dashboard elements.

·         Develop key performance indicators to summarize critical data.

·         Create dynamic charts that respond to user selections.

·         Implement interactive controls to filter dashboard data.

·         Enhance data visualization and reporting skills for decision-making.

2. Introduction

Building on the theoretical understanding gained in Practical 19 ("Study of Dashboard") and the practical skills acquired in previous sessions (especially PivotTables and Charts), this practical focuses on the hands-on creation of an Excel dashboard. A dashboard serves as a central hub for monitoring, analyzing, and visualizing key data points, transforming raw information into actionable insights. You will learn to bring together various Excel elements to create a dynamic, single-screen view of your data, allowing users to interact with it and explore different scenarios.

3. Pre-requisites

·         Successful completion of Practical 3.

·         Successful completion of Practical 5.

·         Successful completion of Practical 16.

·         Successful completion of Practical 19.

·         Basic understanding of Excel formulas and functions (e.g., SUM, AVERAGE, COUNT).

·         Familiarity with data manipulation and presentation.

4. Software Required

·         Microsoft Excel

5. Procedure: Practical Steps with Examples

We will primarily use your marksheet data from Practical 3 to create an academic performance dashboard.

Step 5.1: Preparing Your Data

It’s crucial to have your raw data in a clean, tabular format.

1.     Open Marksheet: Open the Excel workbook containing your Marksheet from Practical 3.

2.     Convert to Excel Table: If you haven’t already, convert your marksheet data into an Excel Table.

o    Select any cell within your data.

o    Go to Insert tab > Table. Ensure "My table has headers" is checked.

o    Rename your table (e.g., Marksheet_Data) from the Table Design tab > Table Name. This makes it easier to reference.

3.     Create a Dashboard Sheet: Insert a new worksheet and rename it Dashboard. This will be your main canvas.

4.     Create a Supporting Calculations Sheet: Insert another new worksheet and rename it Calcs_PT. This sheet will hold all your PivotTables and any helper calculations, keeping your Dashboard sheet clean.

Step 5.2: Setting Up Key Performance Indicators on Calcs_PT

KPIs are summary metrics that provide quick insights.

1.     Total Students:

o    On Calcs_PT, in cell A1, type Total Students.

o    In cell B1, type =COUNTA(Marksheet_Data[Student Name]) and press Enter. This counts the number of student names in your table.

2.     Average Percentage:

o    In A2, type Avg. Percentage.

o    In B2, type =AVERAGE(Marksheet_Data[Percentage]) and press Enter.

o    Format B2 as Percentage with 1 decimal place.

3.     Pass Rate:

o    In A3, type Pass Rate.

o    In B3, type =COUNTIF(Marksheet_Data[Result],"Pass")/COUNTA(Marksheet_Data[Result]) and press Enter.

o    Format B3 as Percentage with 1 decimal place.

Step 5.3: Creating PivotTables on Calcs_PT

PivotTables will be the data source for your dynamic charts and KPIs.

1.     PivotTable 1: Grades Distribution

o    Click any cell in your Marksheet_Data table.

o    Go to Insert tab > PivotTable.

o    Choose Existing Worksheet, and select cell A5 on your Calcs_PT sheet. Click OK.

o    In the PivotTable Fields pane:

§  Drag Grade to ROWS.

§  Drag Student Name to VALUES (it will default to Count of Student Name).

2.     PivotTable 2: Subject-wise Average Scores

o    Click any cell in your Marksheet_Data table.

o    Go to Insert tab > PivotTable.

o    Choose Existing Worksheet, and select a cell below the first PivotTable, e.g., A12 on Calcs_PT sheet. Click OK.

o    In the PivotTable Fields pane:

§  Drag Student Name to FILTERS.

§  Drag Subject 1, Subject 2, Subject 3, Subject 4, Subject 5 to VALUES.

§  For each "Sum of Subject X," click the dropdown, select Value Field Settings, and change Summarize value field by to Average.

§  You can rename the fields (e.g., Avg of Subject 1).

Step 5.4: Creating Dynamic Charts on Calcs_PT

Now, let’s turn these PivotTables into PivotCharts.

1.     Chart 1: Grade Distribution

o    Select any cell within PivotTable 1.

o    Go to PivotTable Analyze tab > Tools group > PivotChart.

o    Select Pie chart > 2-D Pie. Click OK.

o    Right-click on the chart > Move Chart... > New Sheet > OK. (We will move it to the Dashboard later). For now, it’s fine to keep it on Calcs_PT or a new sheet.

o    Formatting:

§  Add Chart Title (e.g., Grade Distribution).

§  Add Data Labels.

§  Hide all field buttons on the chart (Right-click a button > Hide All Field Buttons on Chart).

2.     Chart 2: Subject-wise Average Scores

o    Select any cell within PivotTable 2.

o    Go to PivotTable Analyze tab > Tools group > PivotChart.

o    Select Column chart > Clustered Column. Click OK.

o    Right-click on the chart > Move Chart... > New Sheet > OK.

o    Formatting:

§  Add Chart Title (e.g., Subject-wise Average Scores).

§  Add Axis Titles (e.g., Subject and Average Marks).

§  Hide all field buttons on the chart.

Step 5.5: Designing Your Dashboard Layout

Go to your Dashboard sheet. This is where you’ll arrange everything visually.

1.     Set Up Grid: You can slightly adjust row heights and column widths to create a visual grid.

2.     Background Color: Select all cells (Ctrl+A), then use the Fill Color on the Home tab to give your dashboard a light background color.

3.     Place KPIs:

o    Copy the KPI cells from Calcs_PT (e.g., A1:B3).

o    On Dashboard sheet, right-click where you want to place them > Paste Special > Linked Picture or Link to Cells. This ensures they update automatically.

o    Format these KPI boxes with larger font, bold text, and a distinct fill color.

4.     Place Charts:

o    Cut and paste (Ctrl+X then Ctrl+V) your Grade Distribution and Subject-wise Average Scores charts from their respective sheets onto the Dashboard sheet.

o    Arrange them neatly. Resize as necessary.

Step 5.6: Adding Interactivity with Slicers

Slicers are visual filters that allow users to interact with PivotTables and, by extension, PivotCharts.

1.     Insert Slicer for Student Name:

o    Go to your Calcs_PT sheet. Select any cell in PivotTable 1.

o    Go to PivotTable Analyze tab > Filter group > Insert Slicer.

o    Check Student Name. Click OK.

2.     Insert Slicer for Grade:

o    Repeat the process, inserting a slicer for Grade.

3.     Connect Slicers to Multiple PivotTables:

o    Right-click on the Student Name slicer > Report Connections....

o    Check all PivotTables you want this slicer to control (e.g., PivotTable1 and PivotTable2 on Calcs_PT). Click OK.

o    Repeat for the Grade slicer.

o    Observation: Now, when you filter by student name, both PivotTables and their associated charts will update.

4.     Move Slicers to Dashboard: Cut and paste the Student Name and Grade slicers from Calcs_PT to your Dashboard sheet. Arrange them logically (e.g., along the side).

5.     Format Slicers: Select a slicer. Go to the Slicer tab on the ribbon. You can change colors, column count, and size for better integration with your dashboard design.

Step 5.7: Enhancing the Dashboard

1.     Titles and Headers: Add clear titles to your dashboard and each section (e.g., "Academic Performance Overview," "Key Metrics," "Grade Breakdown").

2.     Instructions: Consider adding a small text box with instructions on how to use the slicers.

3.     Hiding Calcs_PT: Once your dashboard is complete and fully functional, you can hide the Calcs_PT sheet to keep the workbook clean. Right-click on the sheet tab > Hide.

6. Exercises / Tasks

Apply the concepts learned to complete the following tasks using your marksheet or expanded datasets:

1.     Add a Result Slicer:

o    Insert a Slicer for the Result column (Pass/Fail).

o    Connect it to both PivotTables.

o    Place and format it on your dashboard.

2.     Create a New KPI:

o    On your Calcs_PT sheet, create a KPI for Number of Students Passed.

o    Link it to your dashboard and format it.

3.     New Chart: Student Total Marks:

o    Create a new PivotTable summarizing Student Name and Total Marks.

o    Create a Bar Chart from this PivotTable.

o    Place it on your dashboard.

o    Connect it to the Student Name and Grade slicers.

4.     Conditional Formatting for KPIs:

o    On the Dashboard sheet, apply conditional formatting to your Avg. Percentage KPI so that if it falls below a certain threshold (e.g., 60%), it turns red..

5.     Clean Up and Refine:

o    Ensure all charts and slicers are neatly aligned.

o    Use consistent font styles and colors throughout.

o    Hide any unnecessary sheet tabs (like Calcs_PT).

o    Test all slicers to ensure they dynamically update all connected charts and KPIs.

7. Assessment / Deliverables

·         Submit the completed Excel workbook (.xlsx or .xlsm if you add any macros) containing your Marksheet, Calcs_PT (hidden), and the final Dashboard sheet.

·         Ensure the dashboard is fully interactive, with working slicers that filter all relevant charts and KPIs.

·         Verify that all charts are appropriately formatted with titles, labels, and hidden field buttons.

·         Demonstrate clear and concise KPIs.

·         Be prepared to present your dashboard to your instructor, explaining its components, how it functions, and the insights it provides into the academic performance data.