Complete Excel Instructions and Formulas Guide
■ Beginner Level
This section introduces basic Excel operations and formulas.
1. Basic Excel Instructions:
- Entering data: Click a cell and type your value, then press Enter.
- Editing data: Double-click a cell or select and type to replace contents.
- Saving workbook: Click File → Save As → choose location.
- Basic formatting: Use Home tab for font, color, and border styling.
2. Common Beginner Formulas:
- =SUM(A1:A5): Adds numbers from cell A1 to A5.
- =AVERAGE(B1:B10): Finds the mean of numbers in B1 to B10.
- =MAX(C1:C10): Returns the largest number.
- =MIN(C1:C10): Returns the smallest number.
- =COUNT(D1:D10): Counts numeric cells.
■ Intermediate Level
1. Key Concepts:
- Cell referencing: Absolute ($A$1), relative (A1), and mixed ($A1 or A$1).
- Data sorting and filtering using the Data tab.
- Conditional formatting to highlight important data.
2. Intermediate Formulas:
- =IF(A1>50, "Pass", "Fail"): Returns Pass if A1 > 50.
- =VLOOKUP(B2, A2:C10, 3, FALSE): Looks up a value in a table.
- =HLOOKUP(B2, A1:H5, 3, FALSE): Horizontal lookup.
- =CONCATENATE(A1, " ", B1): Joins text from two cells.
- =LEFT(A1, 3): Extracts first 3 characters of a text.
- =RIGHT(A1, 2): Extracts last 2 characters.
■ Advanced Level
1. Advanced Concepts:
- Using Pivot Tables to summarize and analyze data.
- Data validation for restricting inputs.
- Using charts for data visualization (Insert → Chart).
2. Advanced Formulas:
- =INDEX(A1:C10, 2, 3): Returns value at 2nd row, 3rd column of range.
- =MATCH("John", A1:A10, 0): Finds the position of "John" in a list.
- =INDEX(A1:B10, MATCH("John", A1:A10, 0), 2): Combines INDEX and MATCH.
- =SUMIF(A1:A10, ">100", B1:B10): Adds values if criteria are met.
- =COUNTIF(A1:A10, "Yes"): Counts cells that contain "Yes".
- =IFERROR(A1/B1, "Error"): Handles division by zero errors.
3. Array and Dynamic Formulas:
- =TRANSPOSE(A1:B2): Converts rows to columns.
- =FILTER(A1:C20, B1:B20="Sales"): Filters data by criteria.
- =UNIQUE(A1:A20): Returns unique values only.
■ Practical Examples
Example 1: Calculate total sales
=SUM(B2:B10)
Example 2: Find average marks for students
=AVERAGE(C2:C15)
Example 3: Check who passed using IF
=IF(D2>=50, "Pass", "Fail")
Example 4: Lookup student grade
=VLOOKUP(A2, A2:E20, 5, FALSE)
Example 5: Handle missing data
=IFERROR(VLOOKUP(A2, B2:D20, 3, FALSE), "Not Found")
End of Guide — Created by ChatGPT (GPT■5)
