Complete Guide to Microsoft Excel: Basics, Formulas & Features
1 Excel Basics
1️⃣
🔹 Key Components of Excel
- Worksheet: A single spreadsheet containing rows and columns.
- Workbook: A file that contains one or more worksheets.
- Cell: An individual box in a worksheet, identified by a column letter and row number (e.g.,
A1, B2).
- Formula Bar: Displays the content of the selected cell, including formulas.
- Ribbon: The menu at the top containing tools for formatting, formulas, and data
manipulation.
🔹 Basic Operations
- Entering Data: Click on a cell and start typing. Press Enter to move down or Tab to move
right.
- Copy & Paste: Use Ctrl + C (Copy) and Ctrl + V (Paste).
- Fill Handle: Drag the small square at the bottom right of a cell to copy content or extend a
series.
- Sorting & Filtering: Use Data → Sort & Filter to organize data.
2️⃣Essential Excel Formulas & Functions
📌 Basic Math & Arithmetic Formulas
• SUM(A1:A5): Adds up numbers in a range
Example: If A1 = 10, A2 = 20, A3 = 30, then =SUM(A1:A3) gives 60
• AVERAGE(A1:A5): Finds the mean of numbers
Example: If A1=10, A2=20, A3=30, then =AVERAGE(A1:A3) gives 20
• PRODUCT(A1:A5): Multiplies numbers
Example: If A1=2, A2=3, then =PRODUCT(A1:A2) gives 6
📌 Logical & Conditional Formulas
• IF(A1>10, 'High', 'Low'): Returns different values based on conditions
Example: If A1=15, result is 'High'; if A1=5, result is 'Low'
• AND(A1>10, B1<5): Returns TRUE if all conditions are met
Example: If A1=15, B1=3, result is TRUE
• IFERROR(A1/B1, 'Error'): Avoids errors
Example: If B1=0, instead of an error, it returns 'Error'
📌 Text Functions
• LEFT(A1,3): Extracts first 3 characters
Example: If A1='Excel', result is 'Exc'
• RIGHT(A1,3): Extracts last 3 characters
Example: If A1='Excel', result is 'cel'
• MID(A1,2,3): Extracts 3 characters starting from position 2
Example: If A1='Excel', result is 'xce'
📌 Lookup & Reference Functions
• VLOOKUP(1001, A2:C10, 2, FALSE): Searches for 1001 in column A and returns value from
column 2
Example: If 1001 is found in A5, returns corresponding value from B5
📌 Date & Time Functions
• TODAY(): Returns the current date
• NOW(): Returns the current date and time
📌 Financial & Math Functions
• ROUND(A1,2): Rounds A1 to 2 decimal places
Example: If A1=3.14159, result is 3.14
3️⃣Advanced Excel Features
📊 Data Analysis & Visualization:
- Pivot Tables: Summarize large data sets dynamically.
- Conditional Formatting: Highlights cells based on conditions.
- Charts & Graphs: Create bar, line, or pie charts.
- Data Validation: Restricts user input to specific values (e.g., dropdown lists).
📌 Automation & Productivity:
- Macros (VBA): Automate repetitive tasks.
- Power Query: Clean and transform data.
- Goal Seek & Solver: Perform complex calculations.
4️⃣Excel Shortcuts (Must-Know!)
• Select entire column: Ctrl + Space
• Select entire row: Shift + Space
• Copy/Paste: Ctrl + C / Ctrl + V
• Undo/Redo: Ctrl + Z / Ctrl + Y
• Insert new row/column: Ctrl + Shift + +
• Delete row/column: Ctrl + -
• AutoSum (SUM formula): Alt + =
• Open Find & Replace: Ctrl + H
• Move to next sheet: Ctrl + Page Down
• Move to previous sheet: Ctrl + Page Up
Conclusion
This guide covers almost all essential Excel formulas, features, and shortcuts. With practice,
you'll be able to analyze data, automate tasks, and improve efficiency.
Would you like a deeper explanation of any formula or feature?