Learn Excel VBA Basics

Excel Basics Review – Complete Guide

Microsoft Excel is a powerful tool for data analysis, management, and visualization. Whether you're a student, data analyst, administrator, or small business owner, mastering Excel's basic tools can dramatically increase your efficiency.

---

1. Lookup Formulas: VLOOKUP, INDEX/MATCH, XLOOKUP

Basic Overview

• VLOOKUP: Searches a value in the first column and returns a value from a specified column.
• INDEX & MATCH: A flexible combination that allows reverse lookups.
• XLOOKUP: Modern replacement for both, easier and more powerful.

Detailed Example

Dataset Example:

Name	Department	Salary
John	HR	3000
Mary	IT	4000
Steve	Finance	4500
Grace	HR	3200
Daniel	IT	4100

A. VLOOKUP

=VLOOKUP("Mary", A2:C6, 3, FALSE)

Result: 4000

B. INDEX and MATCH

=INDEX(C2:C6, MATCH("Mary", A2:A6, 0))

Result: 4000

C. XLOOKUP

=XLOOKUP("Mary", A2:A6, C2:C6, "Not Found")

Result: 4000

---

2. Data Validation, Conditional Formatting, and Tables

Basic Overview

• Data Validation: Restrict user input with rules.
• Conditional Formatting: Highlight data based on logic.
• Tables: Organize and format structured data.

Detailed Example

A. Data Validation

Steps to restrict Salary (C2:C6) between 1000 and 5000:

1. Select C2:C6
2. Go to Data → Data Validation
3. Allow: Whole Number, Minimum: 1000, Maximum: 5000

B. Conditional Formatting

Steps to highlight salaries above 4000:

1. Select C2:C6
2. Go to Home → Conditional Formatting → New Rule
3. Use Formula: =C2>4000
4. Set Fill Color to Green

C. Tables

Steps to convert range A1:C6 into a Table:

1. Select the range
2. Click Insert → Table
3. Check "My table has headers"

---

3. PivotTables and Charts

Basic Overview

• PivotTables: Summarize large datasets without formulas.
• Charts: Create visuals to interpret data trends and relationships.

Detailed Example

A. PivotTables

Steps to summarize salary by department:

1. Select A1:C6
2. Go to Insert → PivotTable
3. Drag Department to Rows
4. Drag Salary to Values

B. Charts

Steps to insert a column chart based on the PivotTable:

1. Click inside the PivotTable
2. Go to Insert → Column Chart
3. Select a style (e.g., clustered column)

---

Conclusion

Tool	Use Case
VLOOKUP/INDEX/XLOOKUP	Find data fast based on lookup criteria
Data Validation	Control user input
Conditional Formatting	Make patterns visible
Tables	Add structure and automation
PivotTables & Charts	Summarize and visualize insights

Tip: Practice with real data to get more comfortable with each tool.