Theory
5000 rows and a fussy boss
Aryan's chain-wide sheet now has 5000 rows. Scrolling to the bottom with the mouse wheel takes forever. And Meera wants quantities shown as '45 units' and losses in red brackets, but the stored numbers must stay plain so formulas still work.
Two power-user skills solve this: keyboard navigation that leaps across huge sheets in one keystroke, and custom number formatting that dresses a value any way you like without changing it.
Both separate someone who uses Excel from someone who commands it.
At a glance
Navigation shortcuts for big sheets
| Shortcut | Jumps to | Use |
|---|---|---|
| Ctrl+Arrow | Edge of the data block | Leap across 5000 rows instantly |
| Ctrl+Home | Cell A1 | Back to the top-left |
| Ctrl+End | Last used cell | Find the true data extent |
| Ctrl+Page Up/Down | Previous/next sheet | Switch tabs by keyboard |
| Name Box + Enter | Any typed address | Jump to Z500 directly |
Theory
Saving: the format matters
Save (Ctrl+S) overwrites; Save As (F12) makes a copy or changes format. The format you choose has consequences:
- .xlsx: the normal workbook. Cannot store macros.
- .xlsm: macro-enabled workbook, needed once you record macros (Unit 3).
- .csv: plain data only, loses formulas and formatting (BCA105).
- .pdf: a frozen, shareable snapshot.
A subtle trap awaits in Unit 3: save a workbook containing a macro as plain .xlsx and the macro is silently stripped. Match the format to what the file holds.
Theory
Custom number formatting: the codes
Beyond the built-in formats, Format Cells (Ctrl+1) > Custom lets you write your own display code. The building blocks:
- `0` = a mandatory digit (shows 0 even if empty).
- `#` = an optional digit (shows nothing if empty).
- `,` = thousands separator:
#,##0shows 1200000 as 12,00,000. - text in quotes is appended:
0" units"shows 45 as45 units.
And a full code can have up to four sections separated by ;, for positive;negative;zero;text, letting you show losses in red brackets automatically. Crucially: the cell still holds the plain number; only its display changes.
Quiz
Aryan applies the custom format 0" units" to a cell and types 45. What shows on screen, and what value does SUM see?
- Shows '45 units'; SUM still sees the number 45
- Shows '45 units'; SUM sees text and returns 0
- Shows '45'; the text is ignored
- Shows 'units 45'; SUM sees 45
Show the answer
Shows '45 units'; SUM still sees the number 45
The custom format appends the quoted text to the display, so the cell shows '45 units', but the stored value is still the plain number 45, fully usable in SUM. This is the display-versus-value principle again: custom formatting is a costume on the number, formulas see the real value underneath. That is exactly why you format rather than typing '45 units' as text (which would break math).
Think first
0 or # ?
Aryan wants product codes always shown as 4 digits, so 7 should display as 0007. Should he use the code 0000 or ####? Reason from what each placeholder does.
Show the answer
0000. The 0 placeholder is mandatory, it forces a digit position, padding with zeros, so 7 becomes 0007. #### uses the optional #, which shows nothing for empty positions, so 7 would just display as 7. Rule: use 0 when you want a digit shown even if it is a leading zero; use # when empty positions should stay blank. This 0-vs-# distinction is the core of custom-format questions.
Watch out
Where marks leak
Confusing the 0 placeholder (mandatory, pads with zeros) and # (optional, hides empties). Thinking custom formatting changes the value, it only changes display (SUM still sees the real number). Saving a macro workbook as .xlsx and losing the macro (needs .xlsm). And not knowing Ctrl+Arrow/Ctrl+Home navigation, examiners of practical Excel love keyboard-efficiency questions. Format codes and shortcuts are dense with easy marks.
Theory
Speed is a skill
Every second Aryan saves navigating and formatting adds up across a 5000-row monthly report. Keyboard leaps and reusable format codes are why a pro finishes in minutes what takes a beginner an hour. Next lesson bundles look-and-layout mastery: cell styles and themes (save a whole look), freeze panes and split (from BCA105, deeper), and page-layout for clean printing. First, keep practising Ctrl+Arrow, it becomes muscle memory.
Summary
Key takeaways
- Navigate big sheets by keyboard: Ctrl+Arrow (edge of data), Ctrl+Home (A1), Ctrl+End (last cell), Name Box (jump anywhere).
- Save formats matter: .xlsx (normal), .xlsm (macros), .csv (plain data), .pdf (snapshot).
- Custom number formatting (Ctrl+1 > Custom) changes display only, never the value.
- 0 is a mandatory digit (pads with zeros); # is optional (hides empty positions); quoted text is appended.
- A four-section code sets positive;negative;zero;text formats (e.g. red brackets for losses).
- Memory hook: 0 forces a digit, # hides an empty one; format is a costume, formulas see the number.