18.3 Accenture

M26. Create a PivotTable of sales by region and product, and add a calculated field.

Reported for: Accenture [S2]

Insert › PivotTable from the Table › Region to Rows, Product to Columns (or below Region in Rows), Sales to Values. Then PivotTable Analyze › Calculations › Fields, Items & Sets › Calculated Field, e.g. Commission = Sales*0.05 or Profit = Sales-Cost. Remember calculated fields work on summed values, so ratios must be built from sums.

M27. Write a formula for a bonus: 10% of salary if sales exceed 1,00,000, otherwise 5%.

Reported for: Accenture [S2]

=IF(C2>100000,B2*10%,B2*5%) where B = Salary and C = Sales. Better practice: keep the threshold and rates in input cells and use absolute references, e.g. =B2*IF(C2>$G$1,$G$2,$G$3).

M28. What is VLOOKUP and what are its limitations?

Reported for: Accenture [S3] · also MathCo [S15]

VLOOKUP finds a value in the first column of a range and returns a value from a column number to the right. Limitations: cannot look left; hard-coded column index breaks when columns are inserted; approximate match by default if the last argument is omitted; returns only the first match; sensitive to extra spaces and text-number mismatches.

M29. What are PivotTables and how do you use them?

Reported for: Accenture [S3] · also Deloitte [S1], TCS [S4], Mu Sigma [S7], PwC [S9], MathCo [S15]

An interactive summary tool: drag fields to Rows, Columns, Values and Filters to aggregate large data in seconds. I use them for sales by city and month, % of total, month grouping, top-N filters and quick exploration before building a report. They refresh with one click when based on a Table.

M30. How do you handle large datasets efficiently in Excel?

Reported for: Accenture [S3] · also Amazon [S8]

Load only needed columns/rows through Power Query, use the Data Model for millions of rows, prefer pivots over thousands of lookup formulas, avoid volatile and whole-column formulas, use Manual calculation while editing, and save as .xlsb. For data beyond Excel's 1,048,576-row limit, keep it in the Data Model or a database.