After this chapter you will be able to
- locate Excel commands by tab and group, including contextual tabs and old Excel 2003 menus
- quote Excel limits and version differences that Chapter 22 does not list
- predict the exact result of text, maths, statistical, logical, lookup, date and financial formulas
- rewrite a copied formula and read R1C1 and structured references
- write custom number format codes and pick the right data tool or protection level
- spot the trap in look-alike statements about Excel
📌 MUST KNOW - This is extra, harder material that adds to the main book. Read Chapter 22 first. The main chapters answer most exam questions; this part is for the hardest ones, so do not start here.
53.1 Ribbon Map: Tab, Group and Old Menu
Chapter 22 lists what each tab holds. Harder papers ask for the group inside the tab, or the Excel 2003 menu. Group names are those of Excel 2013 onward.
| Command | Tab › Group | Excel 2003 menu |
|---|---|---|
| Wrap Text, Merge & Center, Orientation | Home › Alignment | Format › Cells |
| Conditional Formatting, Format as Table, Cell Styles | Home › Styles | Format › Conditional Formatting |
| Row height, Hide & Unhide | Home › Cells › Format | Format › Row / Column |
| PivotTable, Recommended PivotTables, Table | Insert › Tables | Data › PivotTable and PivotChart Report |
| Slicer, Timeline | Insert › Filters | Not available |
| Print Area, Breaks, Background, Print Titles | Page Layout › Page Setup | File › Page Setup |
| Gridlines and Headings (View / Print) | Page Layout › Sheet Options | File › Page Setup › Sheet |
| Name Manager, Define Name, Create from Selection | Formulas › Defined Names | Insert › Name |
| Trace Precedents, Evaluate Formula, Watch Window | Formulas › Formula Auditing | Tools › Formula Auditing |
| Calculation Options, Calculate Now | Formulas › Calculation | Tools › Options |
| Sort, Filter, Advanced, Reapply | Data › Sort & Filter | Data › Sort / Filter |
| Text to Columns, Flash Fill, Remove Duplicates, Data Validation, Consolidate | Data › Data Tools | Data menu |
| What-If Analysis, Forecast Sheet | Data › Forecast (2016 onward) | Tools › Goal Seek / Scenarios |
| Group, Ungroup, Subtotal | Data › Outline | Data › Group and Outline |
| Solver, Data Analysis (after the add-in is loaded) | Data › Analyze | Tools menu |
| Protect Sheet, Protect Workbook | Review › Protect (365); Review › Changes (2010–2016) | Tools › Protection |
| Freeze Panes, Split, New Window, View Side by Side | View › Window | Window menu |
| Visual Basic, Record Macro, Macro Security | Developer › Code | Tools › Macro |
- The Developer tab is hidden by default. Turn it on in File › Options › Customize Ribbon.
- Flash Fill sits in two places: Data › Data Tools and Home › Editing › Fill.
Contextual tabs (shown only when the object is selected)
| Object | Excel 2007 / 2010 | Excel 2013 / 2016 | Microsoft 365 |
|---|---|---|---|
| Chart | Design, Layout, Format | Design, Format | Chart Design, Format |
| PivotTable | Options, Design | Analyze, Design | PivotTable Analyze, Design |
| Table | Table Tools Design | Table Tools Design | Table Design |
★ EXAM POINT - The chart Layout tab existed only in Excel 2007 and 2010. From 2013 its work moved to the Chart Elements (+) button beside the chart, along with Chart Styles (brush) and Chart Filters (funnel).
53.2 Limits, Versions and Date Storage
Chapter 22 gives rows, columns, width, height, zoom, 32,767 characters and 64 nesting levels. The rest:
| Item | Excel 2007 onward | Excel 2003 |
|---|---|---|
| Formula length | 8,192 characters | 1,024 |
| Arguments in one function | 255 | 30 |
| Nesting levels | 64 | 7 |
| Sort levels in one sort | 64 | 3 |
| Undo levels | 100 | 16 |
| Colours | 16 million | 56 |
| Conditional formatting conditions | Limited by memory | 3 |
| Item | Value |
|---|---|
| Sheet name | Up to 31 characters; not / ? * [ ] : |
| Sheets you can set for a new workbook | 1 to 255 (File › Options › General) |
| Number precision | 15 significant digits |
| Latest date | 31 December 9999 = serial 2,958,465 |
| Characters in a header or footer | 255 |
| Items in a filter drop-down | 10,000 |
| Changing cells in one scenario | 32 |
| Solver decision variables | 200 |
⚡ QUICK FACT - At least one sheet must stay visible. Excel will not hide the last visible sheet.
Feature arrival by version
| Version | Features to remember |
|---|---|
| 2010 | Backstage view, Sparklines, Slicers (PivotTables), AGGREGATE, RANK.EQ |
| 2013 | Flash Fill, Quick Analysis (Ctrl + Q), Recommended Charts, Timeline, one sheet by default, XOR, IFNA, DAYS |
| 2016 | Get & Transform (Power Query) built in, Treemap, Sunburst, Histogram, Waterfall, Forecast Sheet |
| 2019 | CONCAT, TEXTJOIN, IFS, SWITCH, MAXIFS, MINIFS, Funnel and Map charts |
| 2021 | Dynamic arrays, XLOOKUP, FILTER, SORT, UNIQUE, SEQUENCE, LET |
| Microsoft 365 | Threaded comments and Notes, LAMBDA, TEXTSPLIT, VSTACK, Python in Excel |
Dates, times and precision
A date is stored as a whole number of days and a time as a fraction of a day: 1 Jan 1900 = 1, 1 Jan 2000 = 36,526, 26 Jan 2026 = 46,048; noon = 0.5, 6:00 PM = 0.75, 1 hour = 1/24 ≈ 0.041667. So 26 Jan 2026, 6:00 PM = 46,048.75.
- 1900 leap-year quirk: Excel treats 1900 as a leap year, so serial 60 = 29 February 1900, a date that never existed. It was kept for compatibility with Lotus 1-2-3.
- 1904 date system (old Mac default; File › Options › Advanced): serial 0 = 1 January 1904. Dates shift by 1,462 days.
- Dates before 1900 are stored as text. A negative time shows #####.
- Worked: hours from 9:30 AM to 6:00 PM = (0.75 − 0.395833) × 24 = 8.5. Formula: =(B1−A1)*24.
- 15 digits: type 4111111111111111 and Excel keeps 4111111111111110. Store long IDs as text (start with an apostrophe).