MS Excel Basics: Cells, References and Formulas
In the syllabus of AR-IITD
Saved in this browser only. See all revisions due
Key points
- A workbook holds worksheets; a cell is a row and column meeting point
- Excel 2007 onwards: 1,048,576 rows and 16,384 columns, last column XFD
- .xlsx normal, .xlsm macro-enabled, .xltx template, .csv plain data only
- Relative A1 changes on copy, absolute $A$1 does not, F4 cycles the forms
- Negation is applied before the exponent, so =-2^2 gives 4
- #DIV/0! zero division, #N/A lookup failed, #NAME? misspelt function, #REF! deleted cell
- Ctrl+; inserts the date, Ctrl+Shift+; inserts the time
On this page
Basic structure
A workbook is the file. Inside it are worksheets. The meeting point of a row and a column is a cell, such as B5, and a group of cells is a range, such as A1:D10.
Key point
From Excel 2007 onwards a worksheet has 1,048,576 rows and 16,384 columns, the last column being XFD. The row figure is the usual distractor in column questions.
File types
- .xlsx: normal workbook
- .xlsm: macro-enabled workbook
- .xltx: template
- .csv: comma separated values, which keeps no formatting, no formulas and only one sheet
Formulas and operator precedence
Every formula begins with an equals sign. Operations are evaluated in this order:
Precedence
Brackets, then negation, then percentage, then exponent, then multiplication and division, then addition and subtraction, then text joining, then comparison.
Example
In Excel, =-2^2 gives 4 and not −4, because negation is applied before the exponent.
Cell references
| Type | Form | Behaviour when copied |
|---|---|---|
| Relative | A1 | Changes with the new position |
| Absolute | $A$1 | Never changes |
| Mixed | $A1 or A$1 | Only the unlocked part changes |
The F4 key cycles a reference through these four forms while you edit a formula.
Example
Cell C1 holds =A1*$B$1. Copied to C3, it becomes =A3*$B$1. The row moves two steps down for the relative part, while the absolute part stays fixed.
Error values
| Error | Cause |
|---|---|
| #DIV/0! | Division by zero or by a blank cell |
| #N/A | Lookup value not found |
| #NAME? | Function name misspelt |
| #REF! | A referenced cell has been deleted |
| #VALUE! | Wrong data type, such as text in arithmetic |
| ##### | Column too narrow to display the value |
Shortcuts
| Shortcut | Action |
|---|---|
| Ctrl+; | Insert current date |
| Ctrl+Shift+; | Insert current time |
| Alt+= | AutoSum |
| F2 | Edit the active cell |
| F4 | Toggle reference type |
| Alt+Enter | New line inside a cell |
| Ctrl+1 | Format Cells dialog |
| Ctrl+D / Ctrl+R | Fill down / fill right |
| Ctrl+Shift+L | Apply or remove filter |
| Shift+F11 | Insert a new worksheet |
Exam tip
Ctrl+; and Ctrl+Shift+; are a paired trap, as are Ctrl+Enter and Shift+Enter in Word. If you are not certain which is which, skip the question rather than guess under negative marking.
Practice questions
Answer all, then check. Explanations appear after checking.
Finished this topic? Tick it off.
Saved in this browser only. See all revisions due