Data Storytelling with Excel - Titanic Dashboard Workshop

Module 1

Welcome & The Titanic Story

15 minutes

🚢 Setting the Scene

It's April 15, 1912. The "unsinkable" RMS Titanic has just struck an iceberg. In the chaos that follows, some passengers will survive while others won't. But who? And why?

You've been hired as a data analyst to investigate the factors that influenced survival. Your findings will be used to improve maritime safety regulations.

📊 Data Preview

Here's a sample of the Titanic dataset you'll be working with:

PassengerId	Survived	Pclass	Name	Sex	Age	SibSp	Ticket	Fare	Cabin	Embarked
1	0	3	Braund, Mr. Owen Harris	male	22	1	A/5 21171	7.25		S
2	1	1	Cumings, Mrs. John Bradley	female	38	1	PC 17599	71.28	C85	C
3	1	3	Heikkinen, Miss. Laina	female	26	0	STON/O2. 3101282	7.93		S
4	1	1	Futrelle, Mrs. Jacques Heath	female	35	1	113803	53.1	C123	S
5	0	3	Allen, Mr. William Henry	male		0	373450	8.05		S

📌 Note: Notice the missing values in Age (row 5) and Cabin (multiple rows) - you'll need to handle these!

🔍 Live Data Explorer NEW

Filter and sort the full 891-passenger dataset directly in your browser. This is what your Excel sheet will look like once cleaned — use it to spot patterns before you start.

📊 Understanding the Columns

Each column in our dataset tells part of the story:

PassengerId Unique identifier

Survived 0 = No, 1 = Yes

Pclass Ticket class (1, 2, 3)

Name Passenger name

Sex Gender

Age Age in years

SibSp Siblings/Spouses aboard

Parch Parents/Children aboard

Fare Ticket price

Embarked Port (C, Q, S)

💼 Business Context

This dataset structure mirrors real business data:

Sales Data: CustomerID, Purchase, Product Class, Demographics
HR Data: EmployeeID, Retained, Department, Age, Salary
Marketing Data: CampaignID, Converted, Channel, Budget

✅ Checkpoint 1: Module Complete!

Understood the dataset structure Downloaded the Titanic CSV file Ready to start data cleaning

Module 2

Data Detective Challenge

40 minutes

🔍 Your First Investigation

Before we clean the data, let's learn to spot issues independently. You have 5 minutes to explore the raw data.

⚠️ IMPORTANT FIRST STEP: Convert CSV to Excel

Open the downloaded titanic_raw.csv file:
- Double-click the CSV file (it will open in Excel)
- Or right-click → Open with → Excel
Immediately save as Excel format:
- Go to File → Save As
- Choose location (your workshop folder)
- Change "Save as type" to Excel Workbook (*.xlsx)
- Name it: titanic_workshop.xlsx
- Click Save
Why this matters:
- CSV files don't support multiple sheets (needed for pivot tables)
- Excel features like slicers require .xlsx format
- Formatting and formulas are better preserved in Excel format

🚨 Critical: If you skip this step, you won't be able to create pivot tables on new sheets or add slicers later! Always work in .xlsx format, not .csv.

Data Quality Checklist

Missing Values Found In:

Inconsistent Formats In:

Outliers or Odd Values:

Columns We Might Not Need:

🧹 Data Cleaning Workshop

Step 1

Remove Unnecessary Columns

Delete these columns that won't help our survival analysis:

Name - Column D - Too unique, no analytical value
Ticket - Column I (will be H after Name is deleted)
Cabin - Column K (will be I after previous deletions)

📋 Detailed Excel Steps:

Delete Name column first:
- Click on column header D (Name)
- Right-click → Select "Delete"
Delete Ticket column (now in position H):
- After deleting Name, Ticket shifts from column I to H
- Click column header H
- Right-click → Delete
Delete Cabin column (now in position I):
- After previous deletions, Cabin shifts to column I
- Click column header I
- Right-click → Delete
Verify your columns after deletion:
- You should now have: A(PassengerId), B(Survived), C(Pclass), D(Sex), E(Age), F(SibSp), G(Parch), H(Fare), I(Embarked)
- Total of 9 columns remaining

💡 Important: Column positions shift left as you delete! Always delete from left to right to avoid confusion. After all deletions, Age should be in column E.

Step 2

Handle Missing Ages

Replace missing ages with the average age:

📋 Detailed Excel Steps:

First, calculate the average age:
- Click on any empty cell (e.g., cell K2)
- Type: =AVERAGE(E:E) (Age is now in column E after deletions)
- Press Enter - note this number (≈29.7)
Create a helper column with filled ages:
- Click on cell J1 (first empty column after Embarked)
- Type: Age_Filled as header
- Click on cell J2
- Enter formula: =IF(ISBLANK(E2),29.7,E2)
- Or use dynamic average: =IF(ISBLANK(E2),AVERAGE(E:E),E2)
Copy formula down:
- Click cell J2
- Copy (Ctrl+C)
- Select range J3:J891
- Paste (Ctrl+V)
- Shortcut: Double-click the fill handle (small square at bottom-right of J2)
Replace original Age column:
- Select column J (Age_Filled)
- Copy (Ctrl+C)
- Click column E (Age)
- Paste Special → Values (Alt+E+S+V)
- Delete column J

💡 Pro Tip: After deletions, Age is in column E, not F! Always verify your column positions after making changes.

Step 3

Create Age Groups

Add a new column "Age_Group" with categories:

📋 Detailed Excel Steps:

Add column header:
- Click cell J1 (first empty column after the 9 data columns)
- Type: Age_Group
- Press Enter
Enter the IF formula:
- Click cell J2
- Type exactly: =IF(E2<18,"Child",IF(E2<60,"Adult","Senior"))
- Note: Using E2 because Age is now in column E
- Press Enter
Understanding the formula:
- If Age (E2) < 18 → "Child"
- Else if Age (E2) < 60 → "Adult"
- Else → "Senior"
Apply to all rows:
- Click cell J2
- Move cursor to bottom-right corner (fill handle)
- When cursor becomes a + sign, double-click
- Formula auto-fills to last row with data

Step 4

Create Survival Labels

Transform 0/1 into readable text:

📋 Detailed Excel Steps:

Add new column:
- Click cell K1 (next to Age_Group)
- Type: Survival_Status
Create formula:
- Click cell K2
- Type: =IF(B2=1,"Survived","Did Not Survive")
- Note: Survived column remains in B
- Press Enter
Fill down:
- Double-click the fill handle in K2
- Or select K2:K891 and press Ctrl+D

Step 5

Create Ports of Embarkation

Transform C/S/Q into readable text:

📋 Detailed Excel Steps:

Add new column:
- Click cell L1 (next to Survival_Status)
- Type: Port
Create formula:
- Click cell L2
- Type: =IF(I2="C","Cherbourg",IF(I2="S","Southampton","Queenstown"))
- Note: Embarked column remains in I
- Press Enter
Fill down:
- Double-click the fill handle in L2
- Or select L2:L891 and press Ctrl+D

Step 6

Remove Empty Rows (Embarked)

Find and remove rows with missing Embarked values:

📋 Detailed Excel Steps:

Apply filter:
- Select all data (Ctrl+A)
- Data tab → Filter (or Ctrl+Shift+L)
Filter for blanks in Embarked column:
- Click dropdown arrow in column I (Embarked)
- Note: Embarked is now in column I after deletions
- Uncheck "Select All"
- Check only "(Blanks)"
- Click OK
Delete blank rows:
- Select all visible rows (the filtered blanks)
- Right-click on row numbers → Delete Row
Remove filter:
- Data → Clear (or click Filter button again)

Final Check

Verify Your Column Structure

After all cleaning steps, your columns should be:

Column	Content	Data Type
A	PassengerId	Number
B	Survived	0 or 1
C	Pclass	1, 2, or 3
D	Sex	male/female
E	Age	Number (no blanks)
F	SibSp	Number
G	Parch	Number
H	Fare	Decimal
I	Embarked	C, Q, or S
J	Age_Group	Child/Adult/Senior
K	Survival_Status	Survived/Did Not Survive
L	Port	Cherbourg/Southampton/Queenstown

💡 Total: You should have 12 columns and approximately 889 rows (after removing blanks).

✅ Checkpoint 2: Data Cleaned!

Removed 3 unnecessary columns Filled missing ages Created Age_Group column Created Survival_Status column Created Port of Origin column

🧪 Excel Formula Sandbox NEW

Test what your Excel formulas will do before you type them in Excel. Try AVERAGE, COUNTIF, IF, ISBLANK and more — all against the real 891-row Titanic dataset.

💼 Business Context

In Sales Analytics: You'd clean product names, standardize categories, handle missing prices

In HR Analytics: You'd categorize experience levels, standardize job titles, fill missing departments

Module 3

Myth Busters: Exploratory Analysis

30 minutes

🎭 Time to Bust Some Myths!

Remember those assumptions from the beginning? Let's use pivot tables to find the truth.

Myth #1: "More men survived than women"

Your Investigation:

📋 Detailed Steps to Create Your First Pivot Table:

Select your data:
- Click anywhere in your cleaned data
- Press Ctrl+A to select all data
- Or manually select range A1:K889
Insert Pivot Table:
- Go to Insert tab
- Click PivotTable (far left)
- In dialog box, choose "New Worksheet"
- Click OK
- Rename it "Gender"
Build the Pivot:
- In PivotTable Fields panel (right side):
- Drag Sex to Rows area
- Drag Survival_Status to Columns area
- Drag PassengerId to Values area
- Make sure you are using Count of PassengerId in the field settings
- It should show "Count of PassengerId"
Add Survival Rate Calculation:
- Right-click any value in the pivot table
- Select "Show Values As"
- Choose "% of Row Total"
- This shows survival percentage by gender
Alternative: Manual Calculation:
- Note the counts for each gender
- In a cell beside the pivot, calculate:
- =Survived_Count/(Survived_Count+Not_Survived_Count)*100

Myth #2: "First-class passengers had the highest survival rate"

Your Investigation:

📋 Create Your Second Pivot Table:

Create new pivot:
- Go back to your cleaned data sheet
- Select your data range again
- Insert → PivotTable → New Worksheet
- Click OK
- Rename it "Class"
Configure for Class Analysis:
- Drag Pclass to Rows
- Drag Survival_Status to Columns
- Drag PassengerId to Values
- Make sure you are using Count of PassengerId in the field settings
Format the Results:
- Right-click on values → Show Values As → % of Row Total
- Or create a calculated field for survival rate

Myth #3: "Age was the strongest predictor of survival"

Your Investigation:

📋 Create Age Group Analysis:

New pivot table:
- Insert → PivotTable → New Worksheet
- Click OK
- Rename it "Age Group"
Use Age Groups:
- Drag Age_Group to Rows
- Drag Survival_Status to Columns
- Drag PassengerId to Values
- Make sure you are using Count of PassengerId in the field settings
Calculate percentages:
- Show Values As → % of Row Total
- Compare survival rates across age groups

Myth #4: "Passengers from Cherbourg had better chances of survival"

Your Investigation:

📋 Create Port Origin Analysis:

New pivot table:
- Insert → PivotTable → New Worksheet
- Click OK
- Rename it "Origin"
Use Port:
- Drag Port to Rows
- Drag Survival_Status to Columns
- Drag PassengerId to Values
- Make sure you are using Count of PassengerId in the field settings
Calculate percentages:
- Show Values As → % of Row Total
- Compare survival rates across ports

✅ Checkpoint 3: Pivot Tables Mastered!

Created Gender survival pivot table Created Class survival pivot table Created Age Group pivot table Created Port Origin pivot table Calculated survival percentages

Module 5

Dashboard Assembly Workshop

60 minutes

🎨 Creating Your Professional Dashboard

Time to combine everything into an interactive story!

Setup

Create Dashboard Sheet

Create new sheet named "Dashboard"
Hide gridlines: View → Uncheck Gridlines
Set zoom to 80% for more space

Layout

Design Your Layout

Suggested zones:

Top: Title and Key Metrics
Left: Slicers
Center: Main charts
Bottom: Supporting tables/insights (optional)

Slicers

Add Interactive Filters

Make your dashboard interactive:

Click on the Gender pivot table/chart
PivotTable Analyze → Insert Slicer
Add slicers for:
- Gender
- Class
- Age Group
- Embarked Port
Format slicers: Right-click → Size and Properties
On each slicer connect to all pivot tables: Right-click → Report Connections
Select all slicers Ctrl+Click
Copy Ctrl+C
Go to your Dashboard
Paste the slicers Ctrl+V
Select each one of the charts created, copy and paste on your dashboard
Change size, format, position, etc.
On the left panel insert a TextBox for the Mortality Rate Did Not Survive
Link it to your Rates Pivot Table =Rates!$B$4
On the left panel insert a TextBox for the Survival Rate Survived
Link it to your Rates Pivot Table =Rates!$B$5

Magic Moment: When you click a slicer, ALL connected charts update instantly!

Polish

Professional Touches

Consistent Color Scheme:

Primary

Accent

Warning

Danger

Background

Add title: "Titanic Survival Analysis Dashboard"
Include subtitle with your name and date
Add key metrics boxes (Total Passengers, Survival Rate, etc.)
Insert text box with 3 key insights
Add data source citation

🚀 Advanced Challenges (Optional)

Dynamic Title

Create a cell formula that changes based on slicer selection

Hint

=CONCATENATE("Analysis for ", GETPIVOTDATA(...))

Survival Predictor

Build a formula that predicts survival based on multiple inputs

Hint

=IF(AND(Gender="Female", Class=1), "95% chance", ...)

Sparklines

Add mini trend charts showing survival by age

Hint

Insert → Sparklines → Line

✅ Checkpoint 5: Dashboard Complete!

Created dashboard sheet Added all charts Added slicers Connected slicers to all pivot tables Added key metrics

Module 6

Data Storytelling & Business Applications

20 minutes

📖 Crafting Your Data Story

Transform your dashboard into a compelling narrative using the Story Arc:

1. Setup

"On that fateful night, 2,224 souls were aboard..."

2. Conflict

"Only enough lifeboats for 1,178 people..."

3. Investigation

"Our analysis reveals three critical factors..."

4. Climax

"The data shows systematic inequality..."

5. Resolution

"Recommendations for future safety..."

🎯 Your Final Data Story

Compile Your Headlines:

💼 Apply This to Your Business

Sales Dashboard

Replace "Survived" with "Converted"
Replace "Class" with "Product Tier"
Replace "Gender" with "Customer Segment"

Find which customer segments convert best

HR Dashboard

Replace "Survived" with "Retained"
Replace "Class" with "Department"
Replace "Age" with "Tenure"

Identify retention patterns

Marketing Dashboard

Replace "Survived" with "Engaged"
Replace "Fare" with "Campaign Spend"
Replace "Embarked" with "Channel"

Optimize campaign targeting

✅ Checkpoint 6: Workshop Complete!

Identified key insights Created data story Understood business applications Ready to apply to own work

📊 Your Workshop Analytics

Your personal performance dashboard — quiz scores, time per module, and earned achievements. All data stays in your browser.

⏱️ Time Per Module

🏆 Achievements

🚀 Your Next Steps

Today

Share your dashboard on LinkedIn
Tag #DataStorytelling #ExcelDashboard
Save your work and templates

This Week

Apply to your own work data
Try one of the practice datasets
Teach this to a colleague

30-Day Challenge

Build 3 dashboards
Learn Power Query
Explore Power BI basics

📚 Additional Resources

📊

Practice Dataset

Titanic CSV data file

📖

Quick Reference Guide

Excel shortcuts and formulas

📘

50 Excel Formulas

Must-know formulas guide

🏆

Certificate of Completion

Complete all modules to unlock

🎉 Congratulations!

You've just built your first interactive Excel dashboard! You've learned skills that will immediately impact your work. Remember: every dataset has a story. Your job is to find it and tell it well.

🎯 Opening Challenge: Test Your Assumptions

Statement 1: "More men survived than women"

Statement 2: "First-class passengers had the highest survival rate"

Statement 3: "Age was the strongest predictor of survival"

🎯 Learning Objectives

Data Cleaning

Pivot Mastery

Dashboard Design