<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>Quick Reference Guide

⌨️ Essential Excel Shortcuts

Action	Windows	Mac
Select All Data	`Ctrl + A`	`Cmd + A`
Copy	`Ctrl + C`	`Cmd + C`
Paste	`Ctrl + V`	`Cmd + V`
Paste Special (Values)	`Alt + E + S + V`	`Cmd + Ctrl + V`
Fill Down	`Ctrl + D`	`Cmd + D`
Create Table	`Ctrl + T`	`Cmd + T`
Apply Filter	`Ctrl + Shift + L`	`Cmd + Shift + F`
Create Pivot Table	`Alt + N + V`	`Cmd + Option + P`
Insert Chart	`Alt + F1`	`Fn + Option + F1`
Refresh Pivot Table	`Alt + F5`	`Cmd + Option + R`
Format Cells	`Ctrl + 1`	`Cmd + 1`
Find	`Ctrl + F`	`Cmd + F`
Replace	`Ctrl + H`	`Cmd + H`
Save	`Ctrl + S`	`Cmd + S`

📐 Essential Formulas

Data Cleaning Formulas

Check for Missing Values

=IF(ISBLANK(A2),"Missing","Present")

Use this to identify blank cells in your dataset

Fill Missing Ages with Average

=IF(ISBLANK(E2),AVERAGE(E:E),E2)

Replace blank age values with the column average

Create Age Groups

=IF(E2<18,"Child",IF(E2<60,"Adult","Senior"))

Categorize ages into Child, Adult, or Senior groups

Convert Binary to Text

=IF(B2=1,"Survived","Did Not Survive")

Convert 0/1 values to readable text

Convert Port Codes to Names

=IF(I2="C","Cherbourg",IF(I2="S","Southampton","Queenstown"))

Transform port codes (C/S/Q) into full names

Analysis Formulas

Count with Multiple Criteria

=COUNTIFS(B:B,1,D:D,"female")

Count survivors who are female

Calculate Survival Rate

=SUMIF(D:D,"male",B:B)/COUNTIF(D:D,"male")

Calculate survival rate for males

VLOOKUP Example

=VLOOKUP(C2,ClassTable,2,FALSE)

Look up class descriptions from a reference table

Handle Errors

=IFERROR(your_formula,0)

Prevent error messages from appearing

📊 Pivot Table Quick Guide

Creating a Pivot Table

Select your data range (including headers)
Go to Insert → PivotTable
Choose New Worksheet
Click OK

Pivot Table Field Areas

Area	Purpose	Example
Rows	Categories to analyze	Sex, Pclass, Age_Group
Columns	Data to compare across	Survival_Status
Values	Numbers to calculate	Count of PassengerId
Filters	Top-level filters	Pclass, Embarked

Show Values As Options

% of Row Total: Compare within categories
% of Column Total: Compare down columns
% of Grand Total: Show proportion of whole

💡 Pro Tip: To show percentages, right-click on any value in the pivot table → Show Values As → % of Row Total

📈 Chart Selection Guide

Chart Type	Best For	When to Use
Column Chart	Comparison	Compare values across categories (Gender, Class)
Bar Chart	Comparison (horizontal)	Long category names or many categories
Stacked Column/Bar	Part-to-whole comparison	Show survival vs death within each group
Line Chart	Trends over time	Show changes across time periods
Pie Chart	Proportions	Show parts of a whole (max 5-6 slices)
Scatter Plot	Relationships	Show correlation between two variables

Chart Formatting Checklist

Add clear, descriptive title
Remove unnecessary gridlines
Add data labels for key values
Use consistent colors across charts
Format axes with appropriate units
Remove legend if only one data series
Ensure text is readable (min 10pt font)

🎨 Dashboard Design Guide

Layout Structure

Header Zone: Title, subtitle, and key metrics
Slicer Zone: Interactive filters on the left
Chart Zone: Main visualizations in a grid
Insights Zone: Text box with findings
Footer Zone: Data source and author info

Adding Slicers

Click on any pivot table
Go to PivotTable Analyze → Insert Slicer
Select fields to add as slicers
Right-click slicer → Report Connections
Connect to all relevant pivot tables

Professional Touches

Hide gridlines: View → Uncheck Gridlines
Use consistent color scheme
Add white space between elements
Include key metrics at the top
Add data source citation
Include your name and date
Test all slicer connections

💡 Color Scheme Suggestion: Primary (#2E86AB), Secondary (#A23B72), Accent (#F18F01), Success (#4CAF50), Danger (#C73E1D)

🔧 Common Issues & Solutions

Problem	Solution
Pivot table not updating	Right-click pivot → Refresh or press `Alt + F5`
Slicer not affecting charts	Right-click slicer → Report Connections → Check all pivot tables
Formula shows as text	Check cell format (should be General, not Text). Remove leading apostrophe.
#DIV/0! error	Wrap formula in IFERROR: `=IFERROR(formula,0)`
Chart labels overlapping	Right-click axis → Format Axis → Adjust text angle or reduce font size
Can't create pivot on new sheet	Save as .xlsx (not .csv). CSV doesn't support multiple sheets.
Missing data in pivot	Check your data range. Refresh pivot table.

📋 Final Column Structure

After completing all data cleaning steps, your dataset should have these columns:

Column	Name	Data Type	Description
A	PassengerId	Number	Unique identifier
B	Survived	0 or 1	0 = No, 1 = Yes
C	Pclass	1, 2, or 3	Ticket class
D	Sex	male/female	Gender
E	Age	Number	Age in years (no blanks)
F	SibSp	Number	Siblings/Spouses aboard
G	Parch	Number	Parents/Children aboard
H	Fare	Decimal	Ticket price
I	Embarked	C, Q, or S	Port of embarkation
J	Age_Group	Text	Child/Adult/Senior
K	Survival_Status	Text	Survived/Did Not Survive
L	Port	Text	Cherbourg/Southampton/Queenstown

✅ Final Check: You should have 12 columns and approximately 889 rows after cleaning.

📊 Quick Reference Guide