// VLOOKUP — searches vertically (down a column)
=VLOOKUP(lookup_value, table_array, col_index, [range_lookup])

// Example: Find employee salary by employee ID
=VLOOKUP(A2, Employees!A:D, 4, FALSE)
// A2 = employee ID to search for
// Employees!A:D = table to search in
// 4 = return value from 4th column (salary)
// FALSE = exact match (ALWAYS use FALSE)

// HLOOKUP — searches horizontally (across a row)
=HLOOKUP(lookup_value, table_array, row_index, [range_lookup])

// HLOOKUP is rarely used — only when data is arranged in rows
// Example: monthly data where months are column headers

// Key rule: ALWAYS use FALSE for exact match
// TRUE (or omitting) does approximate match — causes wrong results
// This is the most common VLOOKUP mistake in interviews

// RELATIVE reference: A1
// Changes when formula is copied
// Copy from B1 to B2: A1 becomes A2

// ABSOLUTE reference: $A$1
// Stays fixed when formula is copied
// Copy anywhere: still points to $A$1

// MIXED references: $A1 or A$1
// $A1 = column is fixed, row changes
// A$1 = row is fixed, column changes

// Practical example: Calculating tax
// Tax rate is in cell E1 (10%)
// Sales data is in column A (A2:A100)

// In B2: =A2 * $E$1
// Copy B2 down to B100:
// B3 becomes =A3 * $E$1 (A changes, E1 stays fixed)
// B4 becomes =A4 * $E$1

// Without $: =A2 * E1 copied to B3 becomes =A3 * E2 (WRONG!)
// The $ sign locks the reference to the tax rate cell

// Method 1: Remove Duplicates (quick but destructive)
// Data tab → Remove Duplicates → Select columns → OK
// WARNING: This permanently deletes duplicate rows
// ALWAYS work on a copy of your data

// Method 2: COUNTIF to flag duplicates first (safer)
// Add a helper column with:
=COUNTIF($A$2:A2, A2)
// If result > 1, it is a duplicate
// Then filter for duplicates and review before deleting

// Method 3: Conditional Formatting to highlight duplicates
// Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values
// This highlights duplicates without deleting anything
// Review highlighted cells, then decide what to remove

// Method 4: Advanced Filter for unique values
// Data → Advanced → Copy to another location → Unique records only
// Creates a clean list without modifying the original data

// Interview tip: Always mention that you work on a copy
// and verify duplicates before deleting — this shows data awareness

// VLOOKUP approach:
=VLOOKUP(A2, Sheet2!A:C, 3, FALSE)
// Searches A2 in first column of Sheet2!A:C
// Returns value from 3rd column
// Limitation: can ONLY look right (search column must be leftmost)

// INDEX-MATCH approach (better):
=INDEX(Sheet2!C:C, MATCH(A2, Sheet2!A:A, 0))
// MATCH finds the row position of A2 in column A
// INDEX returns the value from column C at that row position

// Why INDEX-MATCH is better:
// 1. Can look LEFT — VLOOKUP cannot
// 2. Does not break when columns are inserted/deleted
//    (VLOOKUP uses column number which shifts)
// 3. Faster on large datasets (searches only the lookup column)
// 4. More flexible — can return from any column

// Example: Find employee name (column B) by employee ID (column D)
// VLOOKUP CANNOT do this (ID is to the right of name)
// INDEX-MATCH can:
=INDEX(B:B, MATCH(D2, D:D, 0))

// Interview tip: Always say INDEX-MATCH is superior
// but acknowledge VLOOKUP is simpler for basic lookups

// SUMIFS — Sum with multiple criteria
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

// Example: Total sales where Region = "North" AND Product = "Laptop"
=SUMIFS(D:D, B:B, "North", C:C, "Laptop")
// D:D = sales amount column (what to sum)
// B:B = region column, "North" = criteria
// C:C = product column, "Laptop" = criteria

// COUNTIFS — Count with multiple criteria
=COUNTIFS(B:B, "North", C:C, "Laptop")
// Counts rows where Region = "North" AND Product = "Laptop"

// AVERAGEIFS — Average with multiple criteria
=AVERAGEIFS(D:D, B:B, "North", C:C, "Laptop")
// Average sales for North region Laptop sales

// Using operators in criteria:
=SUMIFS(D:D, E:E, ">1000", B:B, "North")
// Sum of sales > 1000 in North region

=COUNTIFS(E:E, ">"&F1, B:B, "North")
// Count where sales > value in F1 AND region is North

// Date criteria:
=SUMIFS(D:D, A:A, ">="&DATE(2024,1,1), A:A, "<="&DATE(2024,12,31))
// Sum of sales for a specific date range

// Array Formula — operates on multiple values at once

// Example 1: Find the most common value without a helper column
// Legacy (Ctrl+Shift+Enter):
{=INDEX(A2:A100, MATCH(MAX(COUNTIF(A2:A100, A2:A100)), COUNTIF(A2:A100, A2:A100), 0))}
// The curly braces {} appear automatically when you press Ctrl+Shift+Enter

// Example 2: Sum of products (multiply two arrays and sum)
{=SUM(B2:B10 * C2:C10)}
// Multiplies each B value by corresponding C value, then sums all
// Equivalent to SUMPRODUCT(B2:B10, C2:C10)

// Example 3: Count unique values
{=SUM(1/COUNTIF(A2:A100, A2:A100))}
// Counts how many unique values exist in the range

// Dynamic Arrays (newer Excel — no Ctrl+Shift+Enter needed):
// UNIQUE — returns unique values
=UNIQUE(A2:A100)

// SORT — returns sorted array
=SORT(A2:D100, 3, -1)  // Sort by 3rd column, descending

// FILTER — returns filtered array
=FILTER(A2:D100, B2:B100="North")
// Returns all rows where region is North

// Interview tip: Mention both legacy and dynamic array approaches
// Many Indian companies still use older Excel versions

// Creating a Pivot Table — Step by Step:

// 1. Select your data (including headers)
//    Tip: Use Ctrl+Shift+End to select all data quickly

// 2. Insert → PivotTable → New Worksheet (or Existing)

// 3. Drag fields to areas:
//    ROWS: Region, Product (creates row groupings)
//    COLUMNS: Quarter (creates column groupings)
//    VALUES: Revenue (Sum of Revenue)
//    FILTERS: Year (filter the entire pivot table)

// Result: Sales summary by Region and Product for each Quarter

// Advanced Pivot Table features interviewers test:
// - Calculated Fields: Insert → Fields, Items & Sets → Calculated Field
//   Example: Profit Margin = Revenue - Cost / Revenue
// - Grouping Dates: Right-click date field → Group → Months/Quarters
// - Value Field Settings: Change from Sum to Average, Count, Max
// - Show Values As: % of Grand Total, % of Row Total, Running Total
// - Slicers: Insert → Slicer (visual filters for the pivot table)

// Interview tip: Practice creating pivot tables in under 2 minutes
// The live test gives you 30-60 minutes for multiple tasks
// Slow pivot table creation eats into time for harder questions

// Conditional Formatting — Types:

// 1. Data Bars: Visual bars inside cells showing relative values
//    Home → Conditional Formatting → Data Bars
//    Great for comparing sales across regions at a glance

// 2. Color Scales: Gradient colors (green-yellow-red)
//    Home → Conditional Formatting → Color Scales
//    Shows high/medium/low values instantly

// 3. Icon Sets: Arrows, traffic lights, stars
//    Home → Conditional Formatting → Icon Sets
//    Good for KPI dashboards (up arrow = good, down = bad)

// 4. Custom Rules with Formulas (most tested):
//    Home → Conditional Formatting → New Rule → Use a formula

// Example: Highlight rows where sales dropped > 10% from previous month
// Formula: =(B2-B1)/B1 < -0.1
// Apply to: $A$2:$D$100

// Example: Highlight duplicate values in a column
// Formula: =COUNTIF($A:$A, A1) > 1

// Example: Highlight the entire row if status is "Overdue"
// Formula: =$E1="Overdue"
// Apply to: $A$1:$F$100 (note: $E1 locks column, not row)

// Interview tip: Formula-based conditional formatting
// is what separates basic users from advanced users
// Always mention it even if the interviewer asks about simple rules

// Macros: Record repetitive actions and replay them
// View → Macros → Record Macro → perform actions → Stop Recording

// VBA: The programming language behind macros
// Alt + F11 opens the VBA editor

// Example: Loop through rows and format cells based on value
Sub FormatSalesData()
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    Dim i As Long
    For i = 2 To lastRow
        ' Highlight sales above 100000 in green
        If Cells(i, 4).Value > 100000 Then
            Cells(i, 4).Interior.Color = RGB(144, 238, 144)
        ' Highlight sales below 50000 in red
        ElseIf Cells(i, 4).Value < 50000 Then
            Cells(i, 4).Interior.Color = RGB(255, 182, 182)
        End If
    Next i
End Sub

// When to use macros/VBA:
// - Automating monthly report generation
// - Formatting 50+ sheets identically
// - Sending automated emails from Excel
// - Data validation that formulas cannot handle
// - Combining data from multiple workbooks

// Interview tip: You do not need to be a VBA expert
// Know what it can do and show a simple example
// Most interviewers just want to see awareness, not mastery

// POWER QUERY — ETL tool (Extract, Transform, Load)
// Data → Get Data → From various sources

// What Power Query does:
// 1. Import data from multiple sources (CSV, databases, APIs, web)
// 2. Clean data (remove nulls, fix types, split columns)
// 3. Transform data (pivot, unpivot, merge, append)
// 4. Load clean data into Excel or Power Pivot

// Example workflow:
// Import 12 monthly CSV files → combine into one table
// → remove duplicates → fix date formats → load to Excel
// All steps are recorded and repeatable (click Refresh)

// POWER PIVOT — Data modeling and DAX formulas
// What Power Pivot does:
// 1. Create relationships between multiple tables
// 2. Handle millions of rows (regular Excel limit: ~1M rows)
// 3. Write DAX formulas for complex calculations

// DAX example:
// Total Revenue = SUM(Sales[Amount])
// YoY Growth = DIVIDE(
//     [Total Revenue] - CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Calendar[Date])),
//     CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Calendar[Date]))
// )

// Interview tip: If you know Power Query and Power Pivot,
// mention them early — it immediately signals advanced Excel skills
// Most candidates only know formulas and pivot tables

// Step-by-step data cleaning approach:

// Step 1: Make a copy of the original data (ALWAYS)
// Never clean the original — you might need to go back

// Step 2: Remove duplicates
// Data → Remove Duplicates (after reviewing with COUNTIF)

// Step 3: Handle blank cells
// Ctrl+G → Special → Blanks → decide: delete row or fill
// For numeric columns: fill with 0 or average
// For text columns: fill with "Unknown" or delete row

// Step 4: Fix data types
// Dates stored as text: Text to Columns → Date format
// Numbers stored as text: Multiply by 1 or use VALUE()
// =VALUE(A2) converts text "1234" to number 1234

// Step 5: Standardize text
// =TRIM(A2)    — removes extra spaces
// =PROPER(A2)  — capitalizes first letter of each word
// =CLEAN(A2)   — removes non-printable characters
// =UPPER(A2)   — converts to uppercase (for consistency)

// Step 6: Validate with COUNTIF/SUMIF
// =COUNTIF(A:A, A2) — check for remaining duplicates
// =SUMIF(B:B, "North", D:D) — verify totals make sense

// Step 7: Convert to Table (Ctrl+T)
// Tables auto-expand, have structured references,
// and work better with pivot tables

// Interview tip: Explain your approach BEFORE you start
// Interviewers evaluate your process, not just the result

// Building an automated monthly sales report:

// Step 1: Structure the data as a Table (Ctrl+T)
// Tables auto-expand when new rows are added
// This means pivot tables and charts update automatically

// Step 2: Create a Pivot Table for summary
// Rows: Region, Product Category
// Columns: Month (group dates by month)
// Values: Sum of Revenue, Count of Orders
// Add calculated field: Average Order Value = Revenue / Orders

// Step 3: Add Charts for visualization
// Bar chart: Revenue by region (comparison)
// Line chart: Monthly trend (shows growth/decline)
// Pie chart: Revenue share by product category
// Tip: Link charts to pivot table — they update together

// Step 4: Add Slicers for interactivity
// Insert → Slicer → Region, Product, Month
// CEO can click to filter the entire report instantly

// Step 5: Apply Conditional Formatting
// Color scale on revenue cells (green = high, red = low)
// Icon sets on growth column (up arrow, down arrow)
// Data bars on market share percentages

// Step 6: Create a Dashboard sheet
// Move charts and slicers to a clean dashboard sheet
// Hide the data sheets (right-click → Hide)
// Protect the dashboard (Review → Protect Sheet)

// Key principle: Use Tables, not static ranges
// Static ranges break when data grows
// Tables expand automatically — the report stays current