Advanced Excel Last-Minute Revision

30 quick revision notes for Advanced Excel

1. Press F4 while editing a reference to cycle between relative A1, absolute $A$1 and the two mixed forms.
2. Excel stores dates as serial numbers from 1 January 1900 and times as fractions of a day.
3. An Excel Table expands automatically, so formulas, charts and pivots based on it pick up new rows.
4. SUMIF puts the sum range last, while SUMIFS puts the sum range first followed by criteria pairs.
5. IFNA catches only #N/A, which is safer around lookups than IFERROR because real errors stay visible.
6. VLOOKUP defaults to approximate match, so always set the last argument to FALSE for IDs and codes.
7. XLOOKUP defaults to exact match, can look left, and has a built-in if_not_found argument.
8. Approximate match lookups and binary search need the lookup column sorted ascending or results are wrong.
9. Volatile functions such as OFFSET, INDIRECT, TODAY and RAND recalculate on every change and slow large files.
10. A #SPILL! error means a dynamic array formula's output area is blocked by data or merged cells.
11. In COUNTIFS and FILTER, multiplying conditions acts as AND while adding conditions acts as OR.
12. TEXTBEFORE and TEXTAFTER with instance -1 split text at the last occurrence of a delimiter.
13. NETWORKDAYS.INTL with weekend code 11 treats only Sunday as a weekend, suiting six-day weeks.
14. For the Indian financial year, a month of April or later belongs to the FY starting in that year.
15. Pivot tables read from a pivot cache, so changes in source data appear only after a refresh.
16. Pivots default to Count instead of Sum when the value column contains blanks or text.
17. Distinct Count in a pivot requires adding the data to the Data Model when creating it.
18. Show Values As % of Parent Row Total gives each item's share within its own group.
19. A calculated field works on summed values, so ratios like margin are correct at every level.
20. Slicers can control several pivots through Report Connections only if they share a cache or model.
21. Charts skip #N/A values, which is why NA() is used in helper series to highlight points.
22. Column chart value axes should start at zero, otherwise small differences look exaggerated.
23. Power Query records each transformation as an Applied Step that reruns on every refresh.
24. Append stacks tables with the same columns; Merge joins tables side by side on a key.
25. Left Anti and Right Anti merges return only unmatched rows, ideal for reconciliation.
26. Use Unpivot Other Columns so new month columns are included automatically on refresh.
27. Change Type Using Locale with English (India) reads dd/mm/yyyy dates correctly on any computer.
28. A DAX measure is evaluated per pivot cell under its filter context; a calculated column is stored per row.
29. TOTALYTD with year-end "3/31" gives April to March financial-year totals and needs a marked Date table.
30. In VBA, turn off ScreenUpdating, automatic calculation and events for speed, and restore them in an error handler.

Night-before checklist

• Revise your saved questions on this page once, aloud.
• Read the quick notes and mark any you cannot explain in one line.
• Rehearse a two-minute self-introduction and two project stories.
• Keep CV, ID, laptop charger and interview link ready; sleep on time.