50 Excel Interview Questions (With Answers)

Excel interviews test your formula knowledge, data analysis skills, and ability to solve real business problems. This guide covers 50 of the most common Excel interview questions — with concise answers and practical examples for data analysts, accountants, financial analysts, and business analysts.

Quick reference

Lookup functions

1. What is VLOOKUP and how does it work?

VLOOKUP (Vertical Lookup) searches for a value in the first column of a range and returns a value from a specified column in the same row.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

-- Find salary for employee ID 1042
=VLOOKUP(1042, A2:D100, 3, FALSE)
-- FALSE = exact match, TRUE = approximate match

Parameters:

• lookup_value — the value to search for
• table_array — the range to search in (lookup column must be first)
• col_index_num — column number to return (1 = first column of range)
• range_lookup — FALSE for exact match (always use FALSE for IDs/codes)

Key limitation: VLOOKUP only looks left-to-right and requires the lookup column to be the first column of the range.

2. What is the difference between VLOOKUP and INDEX/MATCH?

-- VLOOKUP: find price in column C by product name in column A
=VLOOKUP("Widget", A2:D100, 3, FALSE)

-- INDEX/MATCH equivalent (can look right, left, or up/down)
=INDEX(C2:C100, MATCH("Widget", A2:A100, 0))

When to use INDEX/MATCH:

• The return column is to the LEFT of the lookup column
• You're inserting/deleting columns frequently
• Performance matters on large datasets

3. What is XLOOKUP and how does it improve on VLOOKUP?

XLOOKUP (Excel 365/2021+) is a modern replacement for VLOOKUP and INDEX/MATCH:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

-- Basic XLOOKUP
=XLOOKUP("Widget", A2:A100, C2:C100)

-- With default value if not found
=XLOOKUP("Widget", A2:A100, C2:C100, "Not found")

-- Wildcard match
=XLOOKUP("Wid*", A2:A100, C2:C100, , 2)

-- Return multiple columns
=XLOOKUP("Widget", A2:A100, B2:D100)  -- returns entire row

4. What is the difference between exact match and approximate match in VLOOKUP?

Exact match (FALSE or 0):

• Returns a value only if the lookup value is found exactly
• Use for IDs, names, codes, text
• Most common in practice

Approximate match (TRUE or 1):

• Finds the largest value less than or equal to the lookup value
• Requires the lookup column to be sorted in ascending order
• Use for tax brackets, grade scales, commission tiers

-- Grade calculator (approximate match)
-- A: 90+, B: 80-89, C: 70-79, D: 60-69, F: <60
-- Table must be sorted: 0,60,70,80,90 in first column
=VLOOKUP(85, GradeTable, 2, TRUE)   -- returns "B"

5. How do you do a two-way lookup (match both row and column)?

Use INDEX with two MATCH functions:

-- Find the value at intersection of a specific row and column
=INDEX(B2:E10, MATCH("Product A", A2:A10, 0), MATCH("Q3", B1:E1, 0))

Or with XLOOKUP (nesting):

=XLOOKUP("Product A", A2:A10, XLOOKUP("Q3", B1:E1, B2:E10))

IF and logical functions

6. How does the IF function work? What are nested IFs?

=IF(logical_test, value_if_true, value_if_false)

=IF(A2>100, "High", "Low")

Nested IF (up to 64 levels in Excel):

=IF(A2>=90, "A",
    IF(A2>=80, "B",
        IF(A2>=70, "C",
            IF(A2>=60, "D", "F"))))

Better alternatives:

• IFS (Excel 2019+) for multiple conditions
• SWITCH for equality checks
• XLOOKUP/VLOOKUP for grade tables

-- IFS (cleaner than nested IF)
=IFS(A2>=90,"A", A2>=80,"B", A2>=70,"C", A2>=60,"D", TRUE,"F")

7. What is the difference between IF, IFS, and SWITCH?

-- SWITCH example: day number to name
=SWITCH(WEEKDAY(A2), 1,"Sun", 2,"Mon", 3,"Tue", 4,"Wed", 5,"Thu", 6,"Fri", 7,"Sat")

8. How do AND and OR work with IF?

-- AND: all conditions must be true
=IF(AND(A2>50, B2="Active"), "Eligible", "Not eligible")

-- OR: at least one condition must be true
=IF(OR(A2="Manager", A2="Director"), "Senior", "Junior")

-- Combining AND and OR
=IF(AND(A2>50, OR(B2="Active", B2="Pending")), "Process", "Skip")

9. What is IFERROR and when should you use it?

IFERROR traps any error and returns a custom value:

=IFERROR(value, value_if_error)

-- Hide #N/A from failed VLOOKUP
=IFERROR(VLOOKUP(A2, Products, 3, FALSE), "Not found")

-- Return 0 on division error
=IFERROR(A2/B2, 0)

IFNA (Excel 2013+) only catches #N/A:

=IFNA(VLOOKUP(A2, Products, 3, FALSE), "Product not found")

Use IFNA when you only expect #N/A — it won't hide other bugs like #REF! or #DIV/0!.

SUMIF, COUNTIF, and aggregation

10. What is SUMIF and how does it differ from SUMIFS?

SUMIF — sum cells matching one condition:

=SUMIF(range, criteria, [sum_range])

-- Sum sales where region is "North"
=SUMIF(B2:B100, "North", C2:C100)

SUMIFS — sum cells matching multiple conditions:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])

-- Sum sales where region is "North" AND product is "Widget"
=SUMIFS(C2:C100, B2:B100, "North", D2:D100, "Widget")

Criteria operators:

=SUMIF(C2:C100, ">1000", C2:C100)      -- greater than
=SUMIF(C2:C100, "<>"&A2, C2:C100)      -- not equal (cell reference)
=SUMIF(A2:A100, "Wid*", C2:C100)       -- wildcard

11. How do COUNTIF and COUNTIFS work?

-- Count cells where value = "Active"
=COUNTIF(B2:B100, "Active")

-- Count cells with value > 1000
=COUNTIF(C2:C100, ">1000")

-- Count with multiple conditions
=COUNTIFS(B2:B100, "Active", C2:C100, ">1000")

-- Count unique values (array formula in older Excel)
=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))

12. What is SUMPRODUCT and what makes it powerful?

SUMPRODUCT multiplies arrays element-by-element and sums the results. It's extremely versatile because it doesn't require Ctrl+Shift+Enter (unlike traditional array formulas).

-- Basic: dot product
=SUMPRODUCT(A2:A5, B2:B5)
-- = A2*B2 + A3*B3 + A4*B4 + A5*B5

-- Weighted average
=SUMPRODUCT(C2:C10, D2:D10) / SUM(D2:D10)

-- Conditional sum (alternative to SUMIFS)
=SUMPRODUCT((B2:B100="North") * (D2:D100="Widget") * C2:C100)

-- Count distinct values
=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))

Text functions

13. What are the most important text functions in Excel?

14. How do you extract first name and last name from a full name?

-- Full name in A2: "John Smith"

-- First name
=LEFT(A2, FIND(" ", A2) - 1)

-- Last name
=MID(A2, FIND(" ", A2) + 1, LEN(A2))

-- Or with TEXTBEFORE / TEXTAFTER (Excel 365)
=TEXTBEFORE(A2, " ")   -- first name
=TEXTAFTER(A2, " ")    -- last name

15. How do you remove duplicates in Excel?

Method 1: Data ribbon → Data → Remove Duplicates → select columns

Method 2: Formula (Excel 365+):

=UNIQUE(A2:A100)  -- returns unique values as spill array

Method 3: Advanced Filter: Data → Advanced → Copy to another location → Unique records only

Method 4: Count occurrences (don't delete, flag):

=COUNTIF($A$2:A2, A2)  -- drag down; values > 1 are duplicates

Date and time functions

16. What are the key date functions in Excel?

-- Age in years
=DATEDIF(BirthDate, TODAY(), "Y")

-- Days until deadline
=A2 - TODAY()

-- Quarter of year
=ROUNDUP(MONTH(A2)/3, 0)

17. How do you calculate the number of business days between two dates?

-- Business days (excluding weekends)
=NETWORKDAYS(A2, B2)

-- Business days (excluding weekends AND holidays)
=NETWORKDAYS(A2, B2, Holidays)  -- Holidays is a range of holiday dates

-- Add business days to a date
=WORKDAY(A2, 10)           -- 10 business days later
=WORKDAY(A2, 10, Holidays) -- excluding holidays

Pivot tables

18. What is a Pivot Table and when would you use one?

A Pivot Table is an interactive summarisation tool that lets you rearrange, group, count, sum, and analyse large datasets without formulas.

Use pivot tables when you need to:

• Summarise thousands of rows by category
• Count or sum by multiple dimensions
• Compare data across periods or groups
• Create cross-tabulation (cross-tab) reports

How to create:

1. Click anywhere in your data
2. Insert → PivotTable
3. Choose where to place it
4. Drag fields to Rows, Columns, Values, Filters

19. What is a Calculated Field in a Pivot Table?

A Calculated Field lets you add a custom formula using existing Pivot Table fields:

1. Click inside the Pivot Table
2. PivotTable Analyze → Fields, Items & Sets → Calculated Field
3. Enter a name and formula using field names

-- Profit Margin calculated field
= Revenue - Cost

-- Margin % calculated field
= (Revenue - Cost) / Revenue

Limitation: Calculated fields always operate on the sum of the fields, not individual row values — this can cause incorrect percentages when data is filtered.

20. How do you refresh a Pivot Table?

• Right-click inside the Pivot Table → Refresh
• PivotTable Analyze → Refresh (or Refresh All)
• Automatic on open: PivotTable Analyze → Options → Data → Refresh data when opening the file

If you add new rows to the source data, you may also need to update the data source range: PivotTable Analyze → Change Data Source

Best practice: Convert source data to an Excel Table (Ctrl+T) — the Pivot Table data source then expands automatically when rows are added.

21. What is the difference between a Slicer and a Filter in Pivot Tables?

Slicers are preferred for dashboards — they are more intuitive and can control multiple Pivot Tables simultaneously.

Formatting and data validation

22. What is Conditional Formatting and how do you use it?

Conditional Formatting changes cell appearance based on rules.

Common uses:

Home → Conditional Formatting:
- Highlight Cell Rules (greater than, less than, duplicates)
- Top/Bottom Rules (top 10%, above average)
- Data Bars, Color Scales, Icon Sets
- New Rule (custom formula)

Custom formula examples:

-- Highlight row if status is "Overdue"
=$C2="Overdue"      -- apply to entire row range $A2:$F2

-- Highlight duplicate rows
=COUNTIF($A$2:$A2,A2)>1

-- Alternating row colours (banding)
=MOD(ROW(),2)=0

-- Traffic light based on value
>90  → green
>=70 → yellow
<70  → red

23. What is Data Validation in Excel?

Data Validation restricts what users can enter in a cell.

Data → Data Validation → Settings:
- Whole number (between, not between, equal to...)
- Decimal
- List (dropdown from a range or typed list)
- Date / Time
- Text length
- Custom (formula-based)

-- Custom validation: only allow positive numbers
=A2>0

-- Only allow unique entries
=COUNTIF($A$2:$A$100,A2)=1

-- Only allow specific text patterns (email check)
=ISNUMBER(MATCH("*@*.?*",A2,0))

Add Input Message (tooltip) and Error Alert (message when violated).

Charts and visualisation

24. When would you use each chart type?

Avoid pie charts with more than 5-6 slices — a bar chart is always clearer.

25. How do you create a dynamic chart that updates automatically?

Method 1: Excel Table as source Convert data to a Table (Ctrl+T) before creating the chart — the chart expands automatically as data is added.

Method 2: Named ranges with OFFSET

-- Dynamic named range (define via Formulas → Name Manager)
=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)

Method 3: Power Query Use Power Query to load and refresh data — charts based on Power Query output update on refresh.

Named ranges and data tools

26. What are Named Ranges and why are they useful?

A Named Range assigns a meaningful name to a cell or range:

Formulas → Name Manager → New
Or: Type name in the Name Box (left of formula bar)

Benefits:

• Formulas become readable: =VLOOKUP(A2, ProductTable, 2, FALSE) instead of =VLOOKUP(A2, $D$2:$F$500, 2, FALSE)
• Names work across sheets
• Easier to maintain (update range in one place)

-- Use in formulas
=SUM(AnnualSales)
=COUNTIF(StatusList, "Active")

Table names (Excel Tables created with Ctrl+T) are a better alternative for data ranges — they expand automatically and support structured references.

27. What is the difference between absolute and relative references?

-- Relative: drag down → A2, A3, A4...
=A2 * B2

-- Mix absolute/relative for multiplication table
=B$1 * $A2    -- row 1 fixed for B, column A fixed

-- Full absolute for totals
=D2 / $D$100  -- always divide by D100

Press F4 to toggle between reference types while editing a formula.

28. How do you use Flash Fill?

Flash Fill (Ctrl+E) automatically fills a pattern detected from examples.

Use cases:

• Split "John Smith" into "John" and "Smith"
• Extract numbers from mixed text: "Inv-10045" → "10045"
• Reformat dates: "20250115" → "01/15/2025"
• Combine columns: "John" + "Smith" → "Smith, John"

Type 1-2 examples in adjacent column → Ctrl+E (or Data → Flash Fill).

Formulas and functions — advanced

29. What are Array Formulas? What is the difference between Ctrl+Shift+Enter and dynamic arrays?

Traditional array formulas (pre-365):

• Entered with Ctrl+Shift+Enter (shows curly braces {=...})
• Calculate across an array but return a single value or require selecting a range first

Dynamic Arrays (Excel 365/2021+):

• Functions like FILTER, SORT, UNIQUE, SEQUENCE return arrays automatically
• Results spill into adjacent cells
• No Ctrl+Shift+Enter needed

-- Old array formula: sum of squares (Ctrl+Shift+Enter)
{=SUM(A2:A10^2)}

-- Dynamic array: get unique values
=UNIQUE(A2:A100)

-- Filter rows where status = "Active"
=FILTER(A2:C100, C2:C100="Active", "No results")

-- Sort by column 2 descending
=SORT(A2:C100, 2, -1)

30. What is the OFFSET function and when is it used?

OFFSET returns a reference offset from a starting cell:

=OFFSET(reference, rows, cols, [height], [width])

-- Value 3 rows below and 2 columns right of A1
=OFFSET(A1, 3, 2)

-- Dynamic sum: last 3 months of data in column B
=SUM(OFFSET(B1, COUNT(B:B)-2, 0, 3, 1))

Common uses:

• Dynamic named ranges that expand as data grows
• Rolling averages
• Dashboard KPIs that reference the last N rows

Note: OFFSET is volatile (recalculates on any change). In Excel 365, prefer FILTER/SPILL patterns.

31. What is INDIRECT and why is it useful (and dangerous)?

INDIRECT converts a text string into a cell reference:

=INDIRECT("A1")              -- same as =A1
=INDIRECT("Sheet"&A2&"!B1")  -- dynamic sheet reference
=INDIRECT(A2)                -- A2 contains "B5", returns value of B5

Use case: Create formulas that reference different sheets based on a dropdown:

=SUM(INDIRECT("'"&SheetName&"'!A1:A100"))

Danger: INDIRECT is volatile and slow on large workbooks. It also breaks if sheet names change.

32. How does CHOOSE work?

CHOOSE returns a value from a list based on an index number:

=CHOOSE(index_num, value1, value2, ...)

=CHOOSE(2, "Red", "Green", "Blue")  -- returns "Green"

-- Quarter name from month number
=CHOOSE(ROUNDUP(MONTH(A2)/3,0), "Q1","Q2","Q3","Q4")

Data analysis

33. What is Power Query (Get & Transform)?

Power Query is Excel's ETL (Extract, Transform, Load) tool built into the Data tab.

Use Power Query to:

• Import data from files (CSV, Excel, JSON, XML, databases, web)
• Clean and shape data (rename columns, change types, filter rows)
• Merge and append multiple tables
• Unpivot wide data to long format
• Create repeatable data transformation pipelines

Data → Get Data → From File / From Database / From Web

Advantages over manual cleanup:

• Transformations are recorded as steps
• Click Refresh to apply same transformations to new data
• No formulas — transformations are stored in the query
• Can handle millions of rows (unlike worksheet limits)

34. What is Power Pivot and when do you use it?

Power Pivot (available in Excel 2016 Pro+/365) adds a data model and the DAX formula language:

• Load multiple tables and define relationships between them
• Handle millions of rows efficiently
• Write DAX measures (more powerful than standard formulas)
• Create KPIs and hierarchies for Pivot Tables

-- DAX measure example: Year-over-Year growth
YoY Growth % = 
DIVIDE(
    [Total Revenue] - CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Calendar[Date])),
    CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Calendar[Date]))
)

Use Power Pivot when your data model has multiple related tables or when standard Pivot Tables can't handle the volume.

35. What are Goal Seek and Solver used for?

Goal Seek (Data → What-If Analysis → Goal Seek):

• Finds the INPUT value needed to reach a TARGET output
• Solves one variable at a time

-- Example: what interest rate gives a monthly payment of $500?
-- Set: PMT cell to -500
-- By changing: interest rate cell

Solver (Data → Solver — requires add-in):

• Optimises an objective (maximise/minimise) by changing multiple variables
• Can apply constraints

Use cases: minimise cost, maximise profit, find optimal resource allocation.

VBA and Macros

36. What is a Macro in Excel and how do you record one?

A Macro is a recorded sequence of actions that can be replayed. Recorded macros are stored as VBA code.

View → Macros → Record Macro
(or Developer tab → Record Macro)

Steps:

1. Start recording, assign a name and shortcut key
2. Perform actions (formatting, data entry, etc.)
3. Stop recording
4. Run with the shortcut key or from the Macros dialog

Important: Save as .xlsm (macro-enabled) or .xlsb to preserve macros.

37. What is VBA and how is it different from recording a macro?

VBA (Visual Basic for Applications) is the programming language behind Excel macros.

Recorded macros generate VBA automatically, but hand-written VBA can:

• Use variables, loops, conditionals
• Respond to events (worksheet change, workbook open)
• Interact with other Office apps
• Build custom user forms (UserForms)
• Handle errors

' Simple VBA macro: highlight cells > 1000
Sub HighlightLargeValues()
    Dim cell As Range
    For Each cell In Selection
        If cell.Value > 1000 Then
            cell.Interior.Color = RGB(255, 255, 0)
        End If
    Next cell
End Sub

Access VBA editor: Alt+F11

38. What are some common VBA operations?

' Reference cells
Cells(2, 1).Value = "Hello"      ' row 2, column 1
Range("A2").Value = "Hello"
Range("A2:A10").Value = 0

' Loop through range
Dim cell As Range
For Each cell In Range("A2:A100")
    If cell.Value > 100 Then cell.Font.Bold = True
Next cell

' Loop with For...Next
Dim i As Long
For i = 1 To 10
    Cells(i, 1).Value = i * 2
Next i

' If statement
If Range("A1").Value > 0 Then
    MsgBox "Positive"
ElseIf Range("A1").Value < 0 Then
    MsgBox "Negative"
Else
    MsgBox "Zero"
End If

' With block (efficient formatting)
With Range("A1:D1")
    .Font.Bold = True
    .Interior.Color = RGB(0, 70, 127)
    .Font.Color = RGB(255, 255, 255)
End With

' Error handling
On Error GoTo ErrorHandler
    ' code that might fail
    Exit Sub
ErrorHandler:
    MsgBox "Error: " & Err.Description

39. What are Excel Events in VBA?

Events are actions that trigger VBA code automatically:

' In Sheet module: auto-timestamp when column B changes
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 And Target.Row > 1 Then
        Application.EnableEvents = False   ' prevent infinite loop
        Target.Offset(0, 1).Value = Now()  ' timestamp in column C
        Application.EnableEvents = True
    End If
End Sub

Performance and best practices

40. What are common reasons Excel files become slow?

41. How do you protect cells, sheets, and workbooks?

Protect specific cells:

1. Select ALL cells → Format Cells → Protection → uncheck "Locked"
2. Select cells to protect → check "Locked"
3. Review → Protect Sheet → set password

Protect sheet (Review → Protect Sheet):

• Prevents editing locked cells
• Can allow specific actions (sorting, filtering, selecting)

Protect workbook (Review → Protect Workbook):

• Prevents adding/deleting sheets and changing window layout

Password-protect file (File → Info → Protect Workbook → Encrypt with Password)

Keyboard shortcuts

42. What are the most important Excel keyboard shortcuts?

Common interview scenarios

43. How would you find the second largest value in a list?

-- Second largest
=LARGE(A2:A100, 2)

-- Nth largest
=LARGE(A2:A100, N)

-- Second largest excluding duplicates
=LARGE(IF(MATCH(A2:A100, A2:A100, 0)=ROW(A2:A100)-ROW(A2)+1, A2:A100), 2)

44. How do you count cells containing specific text?

-- Exact match
=COUNTIF(A2:A100, "Active")

-- Partial match (contains)
=COUNTIF(A2:A100, "*error*")

-- Case-insensitive (COUNTIF is already case-insensitive)
=COUNTIF(A2:A100, "apple")

-- Count non-blank
=COUNTA(A2:A100)

-- Count blank
=COUNTBLANK(A2:A100)

45. How do you concatenate cells with a delimiter?

-- Old way
=A2&", "&B2&", "&C2

-- TEXTJOIN (Excel 2019+/365)
=TEXTJOIN(", ", TRUE, A2:A10)  -- TRUE = ignore empty cells

-- CONCAT (like CONCATENATE but accepts ranges)
=CONCAT(A2:A5)  -- no delimiter

46. How do you transpose rows to columns?

Method 1: Paste Special Copy → Paste Special (Ctrl+Alt+V) → Transpose

Method 2: TRANSPOSE function

-- Select output range first (rows × cols transposed)
{=TRANSPOSE(A1:D4)}  -- Ctrl+Shift+Enter in older Excel

-- Dynamic (365)
=TRANSPOSE(A1:D4)    -- spills automatically

47. What is the difference between COUNT, COUNTA, COUNTBLANK, and COUNTIF?

=COUNT(A2:A100)        -- numbers only
=COUNTA(A2:A100)       -- anything non-empty
=COUNTBLANK(A2:A100)   -- empty
=COUNTIF(A2:A100, ">0") -- positive numbers

48. How do you find and fix #N/A, #REF!, #DIV/0! and other errors?

Trace errors: Formulas → Error Checking → Trace Error shows which cell caused the error.

49. How do you use MATCH alone (without INDEX)?

MATCH returns the position of a value in a range:

=MATCH(lookup_value, lookup_array, [match_type])

=MATCH("Widget", A2:A100, 0)   -- exact match, returns row number
=MATCH(MAX(A2:A100), A2:A100, 0)  -- position of maximum value

Match types:

• 0 = exact match
• 1 = less than or equal (array must be sorted ascending)
• -1 = greater than or equal (array must be sorted descending)

Use MATCH with INDEX for two-way lookups, or alone when you need a position number (e.g. to reference a chart series or range).

50. What is the SEQUENCE function and how is it used?

SEQUENCE (Excel 365+) generates a series of sequential numbers:

=SEQUENCE(rows, [cols], [start], [step])

=SEQUENCE(10)               -- 1 to 10 in a column
=SEQUENCE(1, 12, 1, 1)     -- 1 to 12 in a row (months)
=SEQUENCE(5, 3)             -- 5×3 grid: 1,2,3 / 4,5,6 / ...
=SEQUENCE(12, 1, DATE(2025,1,1), 1)  -- dates Jan 1–12, 2025

Combined with other functions:

-- Generate a calendar header row (abbreviated months)
=TEXT(SEQUENCE(1,12,DATE(2025,1,1),31),"mmm")

Common mistakes

Excel vs alternatives

FAQ

Q: What is the maximum number of rows in Excel? Excel supports 1,048,576 rows × 16,384 columns (XFD) per worksheet. For larger datasets, use Power Query or Power Pivot.

Q: What is the difference between a workbook and a worksheet? A workbook is the entire Excel file (.xlsx). A worksheet (sheet/tab) is a single spreadsheet within the workbook. One workbook can have up to 255 worksheets (limited by available memory).

Q: When should I use a Table (Ctrl+T) instead of a plain range? Almost always for data. Tables auto-expand, have structured references (TableName[ColumnName]), work great with Pivot Tables, auto-format, and play well with Power Query.

Q: How is SUMPRODUCT different from an array formula with SUM? Both calculate array operations, but SUMPRODUCT doesn't require Ctrl+Shift+Enter and is slightly easier to read. Performance is similar. In Excel 365, dynamic arrays with FILTER/SUM are often cleaner.

Q: What is the difference between .xlsx and .xlsb? .xlsx is XML-based (human-readable, widely compatible). .xlsb is binary — faster to open/save and smaller file size for large workbooks, but less compatible with non-Microsoft tools. Use .xlsb for very large files where performance matters.

Q: How do you link data between sheets? Use =SheetName!CellRef:

=Sales!B5             -- value from cell B5 on Sales sheet
=Summary!$A$2         -- absolute reference
='Q1 Data'!A2         -- sheet name with spaces needs quotes

For cross-workbook links: ='[Workbook.xlsx]Sheet1'!A1