20 Create and format charts, manage data and tables in Excel. Calculate and sort data, transform data format and modify content.
V E S R N TECHNOLOGIES
Create and format charts, manage data and tables in Excel. Calculate and sort data, transform data format and modify content.
Create & Format Charts, Manage Tables, Calculate, Sort and Transform Data in ExcelAim
To create and format charts, manage tables, perform calculations, sort data, and transform data format in MS Excel / LibreOffice Calc.
Requirements
Computer system
Spreadsheet software (MS Excel / LibreOffice Calc)
Part – 1 : Enter and Manage Data (Table Creation)
Procedure
Open Excel → New Workbook
Enter the following data:
Roll No Name Trade Theory Practical
101 Raju Electrician 68 75
102 Kumar Fitter 74 70
103 Ravi Welder 55 80
104 Suresh Turner 62 60
105 Mahesh COPA 90 95
Select entire data → Insert → Table → OK
Result: Data converted into a table with filter option.
Part – 2 : Perform Calculations
Total Marks
In column F enter heading Total
Formula:
=D2+E2
Drag down to all rows.
Average Marks
In column G enter heading Average
Formula:
=F2/2
Result (Pass/Fail)
In column H enter heading Result
Formula:
=IF(F2>=70,"PASS","FAIL")
Part – 3 : Sorting and Filtering Data
Sorting
Select table
Go to Data → Sort
Sort by Total → Largest to Smallest
Result: Students arranged by highest marks.
Filtering
Click filter arrow on Trade
Select only Electrician
Result: Only Electrician student data displayed.
Part – 4 : Create and Format Charts
Create Chart
Select Name and Total columns
Click Insert → Column Chart
Result: Marks comparison chart created.
Format Chart
Add Chart Title → Student Performance
Change Chart Color
Add Data Labels
Resize chart
Part – 5 : Transform Data Format
Number Format
Select marks columns → Format as Number (0 decimal)
Text Transform
Use formulas:
Formula Purpose
=UPPER(B2) Convert name to capital
=LOWER(B2) Convert name to small letters
=PROPER(B2) First letter capital
Part – 6 : Modify Content
Find & Replace
Press Ctrl + H
Find: COPA
Replace: Computer Operator
Insert/Delete Row
Right click row → Insert / Delete
Result
Charts were created and formatted successfully. Data table managed, calculations performed, sorted, filtered and data format transformed.
Precautions
Always check formula cell references
Use table format for easy sorting/filtering
Keep numeric columns in number format
I practiced all under my trainer Venugopal Sir.
