Excel basic concepts
1. VLOOKUP (Vertical Lookup)
What it does:
VLOOKUP is used to search for a value in the first column of a table and return a value
from another column in the same row.
Common Use Cases:
• Find the salary of an employee by their ID
• Match employee names with performance ratings
• Retrieve department info from a master sheet
Practice with Excel File:
Example Task:
“Find the Monthly Salary of Employee ID 1015”
Steps:
1. Select a blank cell (e.g., H2)
2. Enter the formula:
=VLOOKUP(1015, A2:G501, 6, FALSE)
3. This looks for Employee ID 1015 in column A and returns the 6th column
(MonthlySalary)
Pro Tip: You can replace 1015 with a cell reference like H1 to make it dynamic.
2. Pivot Tables
What it does:
Pivot Tables help you summarize large data sets by dragging and dropping fields — great for
analyzing patterns, trends, or totals without writing formulas.
Common Use Cases:
• Total salary per department
• Count of employees in each region
• Average salary by performance rating
Practice with Excel File:
Example Task:
“Summarize total and average salary per Department”
Steps:
1. Select any cell in the dataset
2. Go to Insert > Pivot Table
3. In the PivotTable field pane:
o Drag Department to Rows
o Drag MonthlySalary to Values (twice)
o Change one to Sum, and the other to Average
4. You’ll now see the total and average salary by department
Explore Further:
• Drag Region to Columns to break it down by Region
• Use PerformanceRating in Rows to evaluate staff quality
3. Conditional Formatting
What it does:
Conditional Formatting visually highlights cells based on rules — colors, icons, or data bars
make your data easier to scan and interpret.
Common Use Cases:
• Highlight high salaries
• Flag poor performers
• Use color scales to show performance trends
Practice with Excel File:
Example Task 1:
“Highlight salaries above ₹80,000 in green”
Steps:
1. Select MonthlySalary column (Column F)
2. Go to Home > Conditional Formatting > Highlight Cell Rules > Greater Than
3. Enter 80000, choose green fill
Example Task 2:
“Color-code Performance Ratings”
Steps:
1. Select PerformanceRating column (Column G)
2. Go to Conditional Formatting > New Rule > Format only cells that contain
3. Set rules like:
o Text = "Excellent" → Green
o Text = "Below Average" → Red
Summary Table
Feature What It Does How to Practice in the File
Look up a value from another Find salary or department by
VLOOKUP
column EmployeeID
Summarize and analyze large
Pivot Table Group salary by department or region
datasets
Conditional Highlight top salaries or flag poor
Visualize patterns using color
Formatting performance
