Ez Computerहिन्दीOpen app

Advanced Add-on 5 — MS Excel in Depth

Preview Part L: Advanced Add-on · Chapter 53 · 25 min read · 40 practice questions

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

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.

What the full chapter covers

The full chapter has these sections:

  1. 53.1 Ribbon Map: Tab, Group and Old Menu
  2. 53.2 Limits, Versions and Date Storage
  3. 53.3 Operators: Predict the Output
  4. 53.4 References, Names and Circular Formulas
  5. 53.5 Error Values and Information Functions
  6. 53.6 Text Functions: Predict the Output
  7. 53.7 Maths and Rounding: Predict the Output
  8. 53.8 Counting and Statistics: Predict the Output
  9. 53.9 Logical and Lookup: Predict the Output
  10. 53.10 Date and Financial Functions: Predict the Output
  11. 53.11 Arrays, Number Formats and Fill
  12. 53.12 Data Tools, PivotTables, Formatting Rules and Charts
  13. 53.13 Printing, Protection, Files and Shortcuts
  14. 53.14 Statement Traps
  15. Chapter Quick Revision
  16. Most Important Terms
  17. Common Confusions
  18. Rapid Recall

Sample questions from this chapter

1. Reference that means the whole spill range starting in cell A2?

  1. A2:
  2. A2#
  3. @A2
  4. A2!
Show answer

Answer: B. A2#

The # after a cell address refers to the whole spill range that starts there.

2. Excel result of =1+2&3?

  1. 123
  2. 33
  3. 15
  4. 6
Show answer

Answer: B. 33

+ comes before &, so 1+2 = 3 is joined with 3 to give the text 33.

3. Excel result of =SUM("5",15,TRUE)?

  1. 21
  2. 15
  3. 20
  4. #VALUE!
Show answer

Answer: A. 21

Typed arguments "5" and TRUE are counted: 5 + 15 + 1 = 21.

4. Excel shortcut to select visible cells only?

  1. Alt + ;
  2. Alt + =
  3. Ctrl + ;
  4. Ctrl + Shift + O
Show answer

Answer: A. Alt + ;

Alt + ; selects only the visible cells, so copying skips hidden rows.

5. Excel result of =REPLACE("banana",2,3,"XY")?

  1. bXYana
  2. XYnana
  3. bananaXY
  4. bXYna
Show answer

Answer: D. bXYna

REPLACE removes 3 characters from position 2 (ana) and puts XY there: b + XY + na.