ExamShortcut
🔍Log in

MS Excel

🔒 Log in to track
high importance⚡ 10 shortcuts4 subtopics
OverviewLearn (4)Formulas (0)Examples (5)Practice (83)
All subtopics·Subtopic 1 of 4

Workbook, cells and references

🔒 Log in to track
⏱ 4 min read🧩 5 question types🎯 18 practice Q
The idea in one minute

MS Excel is a spreadsheet program. Its work area is a grid of rows and columns. Where a row and a column meet is a cell, named like B3. A file is a workbook, and each page inside it is a worksheet. A formula always begins with an equals sign.

01

What Excel does

Excel is a spreadsheet program. It stores numbers in a grid and calculates with them. People use it for marks, budgets, bills and charts. A file is a workbook. Each page inside it is a worksheet.

02

Rows, columns and cells

  • Rows run across and have numbers: 1, 2, 3 and so on.
  • Columns run down and have letters: A, B, C, and after Z comes AA.
  • A cell is the box where a row and a column cross. Its name is the cell address, such as B3. The column letter comes first.
  • A range is a block of cells, written like A1:C5.
  • The active cell is the one that is selected now. Its address shows in the Name Box.
  • The Formula Bar shows what is inside the active cell.
03

Size of a worksheet

VersionRowsColumnsLast column
Excel 2007 and later1,048,57616,384XFD
Excel 97-200365,536256IV

The new sheet has 1,048,576 rows and 16,384 columns. That gives 17,179,869,184 cells in one sheet. A new workbook opens with one sheet in current versions (Sheet1). Older versions opened with three.

Tip: 1,048,576 is 2 to the power 20. 16,384 is 2 to the power 14.

04

Formulas start with =

Every formula begins with an equals sign. Without it, Excel treats your entry as plain text. For example, =A1+B1 adds two cells. =SUM(A1:A5) adds five cells.

The operators are + (add), - (subtract), * (multiply), / (divide) and ^ (power). Excel calculates in the usual order: brackets, power, multiply and divide, then add and subtract.

Rule: Text typed in a cell leans left. Numbers lean right.

05

Cell references

TypeExampleBehaviour when copied
RelativeA1Changes with the new position
Absolute$A$1Never changes
Mixed$A1 or A$1Only the part without $ changes

This matters when you copy a formula down a column or across a row. Press F4 while editing a formula. It cycles A1, $A$1, A$1 and $A1.

Watch: A dollar sign locks what comes right after it. In \$A1 the column is locked. In A\$1 the row is locked.

06

Counting functions

  • COUNT counts cells that hold numbers.
  • COUNTA counts all cells that are not empty.
  • COUNTBLANK counts empty cells.
  • COUNTIF counts cells that meet one condition.
07

Files

Data is saved under a file name in a folder, like any other file. The default extension is .xlsx (2007 and later). The older format is .xls. A macro file is .xlsm. A comma-separated text file is .csv. A template is .xltx.

08

Question types you will see

Each type: how to recognise it, the method step by step, and one question to try.

Type 1very common4 practice Q

Worksheet size and last cell

How to spot it:

The question asks the number of rows or columns, or the last column name.

Method

  1. Excel 2007 and later has 1,048,576 rows.

  2. It has 16,384 columns and the last one is XFD.

  3. Excel 97-2003 has 65,536 rows, 256 columns and last column IV.

Why it works:

Both limits are round powers of two, and the exam asks for the version.

Try this

The maximum number of columns in an Excel 2016 worksheet is:

Show solution

  1. Excel 2016 is newer than 2007.

  2. So the modern limit applies.

  3. The count is 16,384 and it ends at XFD.

Answer

16,384

Type 2very common4 practice Q

Cell address and range

How to spot it:

The question gives an address or range and asks what it means or how many cells it holds.

Method

  1. Read the column letter first, then the row number.

  2. A range uses a colon: start cell to end cell.

  3. Cells in a range = columns wide times rows tall.

Why it works:

The address system is fixed, so the count is simple multiplication.

Try this

How many cells are in the range A1:C5?

Show solution

  1. Columns A to C make 3 columns.

  2. Rows 1 to 5 make 5 rows.

  3. 3 times 5 is 15.

Answer

15

Type 3very common4 practice Q

Relative, absolute and mixed references

How to spot it:

The question copies a formula and asks what changes, or asks what a dollar sign does.

Method

  1. Find the dollar signs in the formula.

  2. A locked letter or number never moves when copied.

  3. An unlocked part shifts with the destination.

  4. F4 cycles the four styles.

Why it works:

A copied formula follows how far it moved, unless a dollar sign locks the part.

Try this

A formula =A1 in cell B1 is copied to B2. What does B2 show?

Show solution

  1. The formula moved one row down.

  2. A relative reference moves too.

  3. A1 becomes A2.

Answer

=A2

Type 4common4 practice Q

Formula rules and counting functions

How to spot it:

The question asks how a formula starts, or which COUNT function fits a situation.

Method

  1. Every formula starts with =.

  2. Use COUNT for numbers and COUNTA for any filled cell.

  3. Use COUNTBLANK for empty cells and COUNTIF for one condition.

Why it works:

The four COUNT functions differ only in what they count.

Try this

Which function counts all non-empty cells in a range?

Show solution

  1. COUNT would skip text.

  2. COUNTA counts anything that is filled in.

Answer

COUNTA

Type 5common

Workbook, worksheet and file types

How to spot it:

The question asks the name of the file, the page, or the default extension.

Method

  1. A file is a workbook. A page inside it is a worksheet.

  2. The default extension is .xlsx.

  3. Old format .xls, macro .xlsm, text .csv, template .xltx.

Why it works:

These names are used in every Excel question.

Try this

The default file extension of an Excel 2019 workbook is:

Show solution

  1. 2019 is after 2007.

  2. The X format applies.

Answer

.xlsx

09

Shortcuts that save time

⚡ Dollar locks what follows

A dollar sign freezes the letter or number right after it. $A$1 is frozen both ways. $A1 freezes the column only. A$1 freezes the row only.

⚡ Count family

COUNT wants numbers only. COUNTA counts anything filled in. COUNTBLANK counts the empty ones. COUNTIF needs a condition.

Example

A1:A5 holds 10, 20, text, empty, 30. What does COUNT(A1:A5) give?

Show solution

  1. COUNT counts only numbers.

  2. The numbers are 10, 20 and 30.

  3. Text and empty cells are skipped.

Answer

3

10

Mistakes to avoid

Where most students lose marks on this subtopic.

Mistake 01

Typing 5+3 in a cell to add.

Start with an equals sign: =5+3.

Mistake 02

Reading cell address B3 as row B, column 3.

The column letter comes first. B3 is column B, row 3.

Mistake 03

Saying Excel has 256 columns in modern versions.

Excel 2007 and later has 16,384 columns. 256 is the 97-2003 limit.

Mistake 04

Using COUNT to count text entries.

COUNT counts numbers only. COUNTA counts all non-empty cells.

Mistake 05

Thinking $A1 locks the row.

In $A1 only the column is locked.

11

Quick revision

Read this the night before the exam.

  • Excel is a spreadsheet program. A file is a workbook and a page is a worksheet.

  • Cell address = column letter + row number, such as B3. A range looks like A1:C5.

  • Modern sheet: 1,048,576 rows and 16,384 columns (last column XFD). Old: 65,536 rows and 256 columns (IV).

  • Every formula starts with =. Text leans left. Numbers lean right.

  • Relative A1, absolute $A$1, mixed $A1 or A$1. F4 cycles them.

  • COUNT numbers, COUNTA non-empty, COUNTBLANK empty, COUNTIF with a condition.

  • Files: .xlsx default, .xls old, .xlsm macro, .csv text, .xltx template.

12

Practice: 18 questions

Sets of 10, mixed across the question types above. Every answer has a step-by-step explanation.

Topic test · 10 questions

Suggested time 5 min · wrong answers go to your mistake notebook automatically.

Continue learning