18.4 TCS

M31. Explain absolute and relative references.

Reported for: TCS [S4] · also Amazon [S8]

Relative (A2) adjusts when copied; absolute ($A$2) stays fixed. Example: commission =G2*$K$1 copied down keeps pointing to the rate in K1. Mixed references fix only the row or column – useful in grids.

M32. How do you use conditional formatting?

Reported for: TCS [S4] · also Infosys [S12], MathCo [S15]

Home › Styles › Conditional Formatting: Highlight Cells Rules (greater than, duplicates), Top/Bottom rules, Data Bars, Color Scales, Icon Sets, and formula-based rules. Example: highlight orders slower than 12 minutes with =$H2>12 applied to the whole row. Manage rules via Manage Rules to check order and ranges.

M33. What are filters in Excel and how do you apply them?

Reported for: TCS [S5] · also KPMG [S10], Capgemini [S11]

Filters show only rows that meet criteria. Data › Sort & Filter › Filter (Ctrl + Shift + L) adds drop-downs; choose values, Text/Number/Date Filters (e.g. Amount greater than 500) or filter by colour. Use SUBTOTAL/AGGREGATE to total only visible rows, and Advanced filter for complex OR criteria or copying results elsewhere.

M34. Which Excel shortcuts do you use regularly?

Reported for: TCS [S5]

Ctrl + T (Table), Ctrl + Shift + L (filter), Ctrl + Arrow / Ctrl + Shift + Arrow (navigate and select), F4 (absolute references), Alt + = (AutoSum), Ctrl + E (Flash Fill), Ctrl + 1 (Format Cells), Ctrl + ; (today's date), Alt + F11 (VBA Editor), Ctrl + Alt + F5 (Refresh All). See Module 19.