Tasks you might do:
1. Clean the data: Fix inconsistent name formats, remove blanks.
2. Merge datasets using a common key (like Student ID).
3. Split full names into first and last names for sorting.
4. Create a pivot table to show average marks by attendance category.
5. Make a pivot chart to show trends or comparisons.
Would you like a step-by-step walkthrough using Excel/Google Sheets, or would you
prefer to work through an example task or file together?
You said:
can we do the first option first then followed by the example option
ChatGPT said:
Absolutely — let’s go step-by-step starting with the first option: Understanding each skill
clearly, then we’ll move to a hands-on example task afterward.
Part 1: Understanding Each Skill in Detail
1. Transforming and Cleaning Data
This is the first step in any data analysis. You make messy or raw data usable.
Common Tasks:
Task Description How in Excel / Sheets
Remove Duplicates under Data
Remove duplicates Deletes repeated rows.
tab
Fix inconsistent Standardize things like "Y" and
Use Find and Replace or IF()
formatting "Yes"
Trim whitespace Removes spaces =TRIM(A2)
Split combined data Breaks one column into parts Text to Columns or SPLIT()
Combine fields Merge multiple fields into one =A2 & " " & B2 or TEXTJOIN()
Fill blanks with values or remove IF(ISBLANK(A2), "Missing",
Handle missing values A2)
them
2. Getting Data from Different Sources
This involves pulling data from multiple places and combining them for analysis.
a. Compare and Consolidate Data
Example: Combine sales data from two branches into one report.
Tool:
• Excel: Use Power Query or manually copy/paste into one sheet and use formulas like
SUMIFS, VLOOKUP, etc.
b. Split Data into Discrete Fields
Example:
From "John Smith" →
• First Name: John
• Last Name: Smith
Tool:
• Excel: Text to Columns, or =LEFT(), =RIGHT(), =MID()
• Google Sheets: =SPLIT(A2, " ")
c. Merge and Combine Data
Example:
Combine First Name and Last Name → "John Smith"
Tool:
• =A2 & " " & B2
• =TEXTJOIN(" ", TRUE, A2, B2)
3. Displaying Data Visually
This is how you turn raw numbers into meaningful visuals and summaries.
a. Pivot Table Reports
• Used to summarize large data sets easily.
• Can group, filter, and calculate metrics like:
o Total sales
o Average score
o Count of items
In Excel or Google Sheets:
