Advanced Excel Skills

UNIT I: WORKBOOK, DATA & FORMATTING

EXPERIMENT 1: Workbook Navigation & Basics
Objective: Understand Excel interface and file operations
Dataset: (No dataset needed)
Steps:
1. Open Excel → Blank Workbook
2. Use Ribbon tabs (Home, Insert, Data)
3. Try keyboard navigation: Ctrl+Arrow, Ctrl+S
4. File → Save As → Save file
5. File → Print → Preview
Output: File saved and interface understood
Mistakes: Students ignore keyboard shortcuts
Practice:
• Save file in different location
• Use only keyboard for navigation

EXPERIMENT 2: Worksheet Management

Objective: Manage sheets
Steps:
1. Right-click sheet → Rename
2. Click + → Add sheet
3. Drag sheet → Move
4. Right-click → Hide/Unhide
Practice:
• Create 5 sheets and organize them

EXPERIMENT 3: Protect Workbook

Objective: Apply security
Steps:
1. Review → Protect Sheet
2. Enter password
3. Lock specific cells
Mistakes: Forgetting password

EXPERIMENT 4: Import & Export Data

Objective: Work with external data
Steps:
 1. Data → Get Data → From Text/CSV
2. Load dataset
3. File → Save As → CSV/PDF
Practice:
• Import a CSV and export as PDF

EXPERIMENT 5: Data Entry & Editing

Objective: Efficient data handling
Dataset:
Name | Marks
A | 56
B | 78
Steps:
1. Enter data
2. Use Ctrl+C, Ctrl+V
3. Use Paste Special → Values

EXPERIMENT 6: AutoFill & Series

Objective: Generate patterns
Steps:
1. Enter 1,2 → drag
2. Enter date → drag
3. File → Options → Custom Lists
Practice:
• Create list: Mon–Sun

EXPERIMENT 7: Formatting
Objective: Improve appearance
Steps:
1. Select cells → Bold, Center
2. Format Cells → Number, Currency
3. Use Format Painter

EXPERIMENT 8: Conditional Formatting

Objective: Highlight data
Steps:
1. Select marks
2. Home → Conditional Formatting → >75
Mistakes: Wrong range selection
EXPERIMENT 9: Data Validation
Objective: Control input
Steps:
1. Data → Validation
2. Set range 0–100
3. Add error message

EXPERIMENT 10: Sorting & Filtering

Objective: Organize data
Steps:
1. Data → Sort (A–Z)
2. Data → Filter → >60

EXPERIMENT 11: Tables

Objective: Structured data
Steps:
1. Select data → Ctrl+T
2. Apply table style

UNIT II: FORMULAS & FUNCTIONS

EXPERIMENT 12: Basic Formulas
Objective: Calculate total and average marks
Dataset:
Name | M1 | M2 | M3
A | 50 | 60 | 70
Steps:
1. Enter data in columns B, C, D
2. Click cell E2 → type:
=SUM(B2:D2)
3. Press Enter → drag down
4. Click F2 → type:
=AVERAGE(B2:D2)
Output: Total and average calculated
Mistakes:
• Wrong range selection
Practice:
• Add 5 students and compute total
EXPERIMENT 13: Cell Referencing
Objective: Use absolute reference
Dataset:
Marks | Multiplier
50 | 2
Steps:
1. Enter multiplier in F1
2. In B2 type:
=A2*$F$1
3. Press Enter
4. Drag formula down
Output: All values multiplied by same constant
Mistakes:
• Missing $ symbol changes result

EXPERIMENT 14: Logical Functions

Objective: Pass/Fail system
Dataset:
Marks
45
30
Steps:
1. In B2 type:
=IF(A2>=40,"Pass","Fail")
2. Press Enter
3. Drag down
Nested Example:
=IF(A2>=75,"A",IF(A2>=60,"B","C"))

EXPERIMENT 15: Text Functions

Objective: Combine and modify text
Dataset:
First | Last
John | Doe
Steps:
1. In C2 type:
=CONCAT(A2," ",B2)
 2. Use:
=LEFT(A2,2)
=LEN(A2)
Output: Full name and text length

EXPERIMENT 16: Lookup Functions

Objective: Search values
Dataset:
Roll | Marks
1 | 80
Steps:
1. In another table enter roll number
2. In B2 type:
=VLOOKUP(A2,A:B,2,FALSE)
3. Press Enter
XLOOKUP:
=XLOOKUP(A2,A:A,B:B)
Mistakes:
• Wrong column index

EXPERIMENT 17: INDEX & MATCH

Objective: Advanced lookup
Dataset:
Roll | Marks
1 | 80
Steps:
1. In B2 type:
=MATCH(A2,A:A,0)
2. Then:
=INDEX(B:B,MATCH(A2,A:A,0))
Output: Correct value returned

EXPERIMENT 18: Date Functions

Objective: Work with date
Steps:
1. In A1 type:
=TODAY()
2. In A2:
=NOW()
EXPERIMENT 19: Math Functions
Objective: Mathematical operations
Dataset:
Value
25
Steps:
1. In B2 type:
=SQRT(A2)
2. In C2:
=ROUND(A2,1)

EXPERIMENT 20: Error Handling

Objective: Handle division errors
Dataset:
A|B
10 | 0
Steps:
1. In C2 type:
=A2/B2 (shows error)
2. Replace with:
=IFERROR(A2/B2,"Error")

EXPERIMENT 21: Formula Auditing

Objective: Debug formulas
Steps:
1. Click formula cell
2. Go to Formulas tab
3. Click Trace Precedents
4. Click Evaluate Formula

UNIT III: CHARTS & DATA ANALYSIS

EXPERIMENT 22: Column Chart
Objective: Compare student marks visually
Dataset:
Name | Marks
A | 60
B | 80
C | 70
 Steps:
1. Select data (A1:B4)
2. Go to Insert → Column Chart → 2D Column
3. Chart appears
4. Click Chart Title → rename to “Marks Comparison”
5. Add Data Labels (Chart Elements → Data Labels)
Output: Vertical bars showing marks comparison
Mistakes:
• Not selecting headers
• Wrong chart type
Practice:
• Create chart for 10 students

EXPERIMENT 23: Line Chart

Objective: Show trend over time
Dataset:
Month | Sales
Jan | 100
Feb | 120
Mar | 150
Steps:
1. Select data
2. Insert → Line Chart
3. Add markers and title
Output: Line showing increasing/decreasing trend
Mistakes:
• Using non-sequential data

EXPERIMENT 24: Pie Chart

Objective: Show percentage distribution
Dataset:
Subject | Marks
Math | 40
Physics | 30
Chemistry | 30
Steps:
1. Select data
 2. Insert → Pie Chart
3. Add Data Labels → Percentage
Output: Circle divided into proportions
Mistakes:
• Total not meaningful

EXPERIMENT 25: Bar Chart

Objective: Horizontal comparison
Dataset: Same as column chart
Steps: Insert → Bar Chart

EXPERIMENT 26: Bubble Chart

Objective: Represent 3 variables
Dataset:
X | Y | Size
10 | 20 | 5
15 | 25 | 10
Steps:
1. Select all three columns
2. Insert → Bubble Chart
Output: Bubbles with different sizes
Mistakes:
• Missing size column

EXPERIMENT 27: Histogram

Objective: Frequency distribution
Dataset:
Marks: 10,20,30,40,50,60,70,80
Steps:
1. Select data
2. Insert → Statistical Chart → Histogram
3. Adjust bin width
Output: Frequency bars
Mistakes:
• Non-numeric data

EXPERIMENT 28: Pivot Table

 Objective: Summarize large data
Dataset:
Name | Subject | Marks
A | Math | 60
A | Physics | 70
Steps:
1. Select full table
2. Insert → Pivot Table
3. Drag:
o Rows → Name
o Columns → Subject
o Values → Marks
Output: Summary table
Mistakes:
• Not selecting full dataset

EXPERIMENT 29: Pivot Chart

Objective: Visualize pivot data
Steps:
1. Click Pivot Table
2. Insert → Pivot Chart
3. Choose chart type

EXPERIMENT 30: What-If Analysis (Goal Seek)

Objective: Find required value
Dataset:
Marks1 | Marks2 | Avg
50 | 60 | ?
Steps:
1. Create formula: =AVERAGE(A2:B2)
2. Go to Data → What-if → Goal Seek
3. Set cell: Avg cell
4. To value: 70
5. By changing: Marks2
Output: Required marks shown
Mistakes:
• Wrong cell selection
EXPERIMENT 31: Scenario Manager
Objective: Compare scenarios
Steps:
1. Data → What-if → Scenario Manager
2. Add scenarios (Best/Worst)
3. Show results

EXPERIMENT 32: Data Table

Objective: Sensitivity analysis
Dataset:
Principal | Rate | Interest
Steps:
1. Create formula
2. Data → What-if → Data Table

Task 33: Creation of a Matrix in Excel

Objective: To create and represent a matrix using Excel cells.
Procedure:
1. Open MS Excel.
2. Enter numerical values in rows and columns to form a matrix.
3. Each cell represents an element of the matrix.
Result: A matrix is successfully created in Excel.
Errors/Precautions:
- Enter only numeric values; text entries cause errors in calculations.
- Ensure consistent row and column alignment.

Task 34: Matrix Addition

Objective: To perform addition of two matrices.
Procedure:
1. Enter two matrices of the same order.
2. Select the output range.
3. Use formula =(select array1/matrix1) + (select array2/matrix2).
Result: Matrix addition is obtained.
Errors/Precautions:
- If matrix orders differ, Excel displays #VALUE! error.
- Output range must be equal to matrix order.
Task 35: Matrix Multiplication
Objective: To multiply two matrices.
Procedure:
1. Enter two compatible matrices.
2. Select the output range.
3. Use =MMULT(array1,array2).
Result: Product matrix is obtained.
Errors/Precautions:
- Number of columns in first matrix must equal number of rows in second.
- If compatibility is not satisfied, #VALUE! error appears.
- Forgetting Ctrl+Shift+Enter gives incorrect results.

Task 36: Transpose of a Matrix

Objective: To find transpose of a matrix.
Procedure:
1. Select output range.
2. Use =TRANSPOSE(array).
Result: Transpose matrix is displayed.
Errors/Precautions:
- Output range must be selected before applying transpose.
- Incorrect range selection leads to partial output.

Task 37: Inverse of a Matrix

Objective: To find inverse of a matrix.
Procedure:
1. Enter a square matrix.
2. Use =MINVERSE(array).
Result: Inverse matrix is obtained.
Errors/Precautions:
- Matrix must be square.
- If determinant is zero, Excel shows #NUM! error.
- Array formula must be applied correctly.

Task 38: Determinant of a Matrix

Objective: To calculate determinant.
Procedure:
1. Enter square matrix.
2. Use =MDETERM(array).
Result: Determinant value is displayed.
Errors/Precautions:
- Matrix must be square.
- Incorrect range selection gives wrong determinant value.

Task 39: Solving Linear Equations

Objective: to solve solution vector of linear equation.
Formula:
X = MMULT(MINVERSE(A), B)
Procedure:
1. Enter matrices A and B.
2. Use MINVERSE() and MMULT() together.
Result: Solution vector X is obtained.