Chapter 5: MS Excel

What to remember

• Excel is a spreadsheet program. A file is a workbook (.xlsx) made of worksheets; each sheet has rows (numbers) and columns (letters), and a cell address looks like B3. Every formula starts with the equals sign (=).
• Key functions: SUM, AVERAGE, MAX, MIN, COUNT, IF, VLOOKUP, ROUND. Cell references can be relative (A1), absolute ($A$1) or mixed (A$1, $A1).
• Charts show data as pictures: column, bar, line, pie, scatter and others. The F4 key toggles reference types.

Spreadsheet basics

A spreadsheet is a grid used to store, calculate and analyse data. MS Excel is the spreadsheet program of the Microsoft Office suite. Its file is called a workbook and has the extension .xlsx (older versions: .xls). A new workbook opens with one or more worksheets, shown as tabs at the bottom (Sheet1, Sheet2). A workbook can hold many sheets, and sheets can be added, renamed, moved, copied or deleted.

Layout of a worksheet:

• Columns are named with letters A, B, C and so on. Rows are numbered 1, 2, 3 and so on.
• A cell is the box where a row and a column meet. Its cell address is the column letter followed by the row number, such as C5.
• The active cell is the cell that is selected now.
• A range is a group of cells, written with a colon, such as A1:A10 or B2:D5.
• The Name Box (left of the formula bar) shows the address of the active cell.
• The Formula Bar shows the content or formula of the active cell.
• The fill handle is the small square at the corner of the active cell; dragging it copies data or continues a series.

Data types: text (labels, left-aligned by default), numbers (right-aligned by default), dates and times (stored as numbers), and formulas. A cell that is too narrow for a number shows hash marks (#####); widening the column fixes this. Excel can hold more than a million rows and sixteen thousand columns in modern versions, but exact limits are not needed for exams.

Formulas and operators

A formula is an instruction that calculates a value. It must start with = (or + or -). Example: =A1+B1. Excel formulas update by themselves when cell values change.

Arithmetic operators: + (add), - (subtract), * (multiply), / (divide), ^ (power), % (percent). Order of operations follows BODMAS: brackets first, then powers, then multiplication and division, then addition and subtraction. Example: =2+3*4 gives 14, and =(2+3)*4 gives 20. The comparison operators are =, >, <, >=, <= and <>. The & operator joins text. The ampersand in ="Ram"&" Kumar" gives Ram Kumar.

Functions

A function is a built-in formula. Its form is =NAME(arguments). The Insert Function button (fx) helps to choose one. The AutoSum button adds a column or row quickly (Alt+=).

Notes: The last argument of VLOOKUP, FALSE, asks for an exact match. In the IF function, the first argument is the test, the second is the result if true, and the third is the result if false. Nested IF puts one IF inside another. A function can have text in double quotes. AND, OR and NOT give TRUE or FALSE and are often used inside IF. Range-based statistical functions skip empty cells. MEDIAN gives the middle value; MODE gives the most repeated value.

Cell referencing

A cell reference tells a formula which cell to use.

If the formula =A1*2 in cell B1 is copied to B2, it becomes =A2*2 because the reference is relative. If the formula is =$A$1*2, it stays =$A$1*2 wherever it is copied. Press F4 while editing a reference to cycle through A1, $A$1, A$1 and $A1. A reference to another sheet is written with an exclamation mark, such as Sheet2!A1. Named ranges give a name to a range so that formulas are easy to read.

Charts and data tools

A chart turns numbers into a picture. Insert a chart from the Insert tab after selecting the data. The data items are the data series; the horizontal axis is the category (X) axis and the vertical axis is the value (Y) axis. A chart also has a title, legend (key to colours) and data labels.

A sparkline is a tiny chart inside one cell. Conditional formatting changes the look of cells by a rule, such as red for marks below 35. Sort arranges data in ascending or descending order. Filter shows only the rows that match a condition. PivotTable summarises large data by grouping and totals. Data Validation limits what can be typed in a cell (for example a drop-down list). Freeze Panes keeps rows or columns visible while scrolling. Merge and Center joins cells into one. Wrap Text shows long text in many lines in a cell. Goal Seek finds the input needed to get a result. Macros record and replay a series of steps.

Printing and protecting: set the print area, use Page Layout and Print Preview, and use Protect Sheet or a password to prevent changes.

Shortcut keys

Exam traps

• Excel formulas start with =; text typed without = is not calculated.
• A workbook is the whole file; a worksheet is a single sheet.
• COUNT counts only numbers; COUNTA counts all non-empty cells.
• Relative references change on copying; absolute references ($A$1) do not.
• F4 toggles reference type while editing a formula; F2 edits the cell.
• Pie charts show one data series; line charts show trends over time.
• Text is left-aligned and numbers are right-aligned by default.
• ##### in a cell means the column is too narrow, not that the formula is wrong.
• In VLOOKUP, the lookup value must be in the first column of the table.
• Ctrl+Home goes to A1; Ctrl+End goes to the last used cell.

One-liners

• 1. The Excel file is a workbook with extension .xlsx.
• 2. A cell address is column letter plus row number, such as D7.
• 3. A range of cells is written as A1:B5.
• 4. Every formula begins with the = sign.
• 5. =SUM(A1:A3) adds the cells A1, A2 and A3.
• 6. The fill handle copies data or fills a series.
• 7. F4 changes a cell reference between relative and absolute.
• 8. A pie chart shows parts of a whole.
• 9. =TODAY() returns the current date.
• 10. A PivotTable summarises large data quickly.
• 11. Freeze Panes keeps headings visible when scrolling.
• 12. The Name Box shows the address of the selected cell.

Practice questions

1. MS Excel is an example of

   1. spreadsheet software
   2. word processor
   3. presentation software
   4. operating system
   Answer

   A. spreadsheet software

   Excel stores and calculates data in rows and columns.

2. A complete Excel file is called a

   1. worksheet
   2. workbook
   3. range
   4. template only
   Answer

   B. workbook

   A workbook holds one or more worksheets.

3. The default extension of an Excel 2016 file is

   1. docx
   2. pptx
   3. txt
   4. xlsx
   Answer

   D. xlsx

   Excel 2007 and later save as .xlsx.

4. Every Excel formula must begin with

   1. $
   2. #
   3. =
   4. @
   Answer

   C. =

   The equals sign tells Excel that a formula follows.

5. The intersection of a row and a column is called a

   1. cell
   2. sheet
   3. range
   4. field
   Answer

   A. cell

   A cell is the basic unit of a worksheet.

6. Which of the following is a valid cell address?

   1. C-5
   2. CC5C
   3. C5
   4. 5C
   Answer

   C. C5

   A cell address is column letter then row number.

7. A range of cells from A1 to A10 is written as

   1. A1-A10
   2. A1:A10
   3. A1;A10
   4. A1..A10
   Answer

   B. A1:A10

   A colon separates the first and last cells of a range.

8. Which function adds the numbers in a range?

   1. MAX
   2. ROUND
   3. COUNT
   4. SUM
   Answer

   D. SUM

   SUM adds all numbers in the range.

9. Which function gives the largest value in a range?

   1. MAX
   2. MIN
   3. AVERAGE
   4. SUM
   Answer

   A. MAX

   MAX returns the biggest number.

10. What does =AVERAGE(A1:A4) calculate for the values 10, 20, 30 and 40?

    1. 40
    2. 100
    3. 25
    4. 20
    Answer

    C. 25

    Sum 100 divided by 4 gives 25.

11. The shortcut key to toggle between relative and absolute references is

    1. F5
    2. F2
    3. F7
    4. F4
    Answer

    D. F4

    F4 cycles A1, $A$1, A$1 and $A1.

12. Which reference does NOT change when the formula is copied?

    1. A$1
    2. $A$1
    3. A1
    4. $A1
    Answer

    B. $A$1

    $A$1 is a fully absolute reference.

13. If =A1*2 in cell B1 is copied to B3, the formula becomes

    1. =A2*2
    2. =A3*2
    3. =$A$3*2
    4. =A1*2
    Answer

    B. =A3*2

    A relative reference moves with the new row.

14. What is the result of =2+3*4?

    1. 9
    2. 24
    3. 14
    4. 20
    Answer

    C. 14

    Multiplication is done first: 3 x 4 = 12, then 2 + 12 = 14.

15. Which function counts only the cells that contain numbers?

    1. COUNTA
    2. COUNTBLANK
    3. LEN
    4. COUNT
    Answer

    D. COUNT

    COUNTA counts all non-empty cells.

16. Which function counts all non-empty cells?

    1. COUNTA
    2. MIN
    3. COUNT
    4. SUMIF
    Answer

    A. COUNTA

    COUNTA counts text and numbers.

17. The result of =IF(40>=35,"Pass","Fail") is

    1. Fail
    2. Pass
    3. 40
    4. TRUE
    Answer

    B. Pass

    The test is true so the second argument is returned.

18. In =VLOOKUP(E2,A2:C9,3,FALSE), the number 3 refers to

    1. the column from which the result is returned
    2. the number of rows to skip
    3. the row number
    4. the number of matches
    Answer

    A. the column from which the result is returned

    The third column of the table gives the result.

19. The last argument FALSE in VLOOKUP asks for

    1. an approximate match
    2. a sorted result
    3. a text result only
    4. an exact match
    Answer

    D. an exact match

    FALSE means exact match.

20. The result of =ROUND(12.567,1) is

    1. 12.57
    2. 12.5
    3. 12.6
    4. 13
    Answer

    C. 12.6

    One decimal place: 12.567 becomes 12.6.

21. What is the result of =LEN("Andhra")?

    1. 5
    2. 6
    3. 7
    4. 3
    Answer

    B. 6

    The word Andhra has six letters.

22. Which function returns the current date?

    1. COUNT
    2. MEDIAN
    3. TODAY
    4. DATEDIF only
    Answer

    C. TODAY

    =TODAY() returns the system date.

23. Which chart is best for showing parts of a whole?

    1. Scatter chart
    2. Line chart
    3. Column chart for trends
    4. Pie chart
    Answer

    D. Pie chart

    A pie shows each part as a share of the whole.

24. Which chart is best for showing change over time?

    1. Line chart
    2. Pie chart
    3. Doughnut chart
    4. Bubble chart only
    Answer

    A. Line chart

    A line connects values over time.

25. The small square at the corner of the active cell used to copy data is the

    1. sparkline
    2. name box
    3. anchor
    4. fill handle
    Answer

    D. fill handle

    Dragging the fill handle copies or extends a series.

26. The box on the left of the formula bar that shows the cell address is the

    1. Cell Box
    2. Title Box
    3. Name Box
    4. Fill Box
    Answer

    C. Name Box

    The Name Box displays the active cell address.

27. When a number is too wide for the cell, Excel shows

    1. #NAME?
    2. #####
    3. #DIV/0!
    4. #REF!
    Answer

    B. #####

    Widening the column shows the number.

28. The error #DIV/0! appears when

    1. a number is divided by zero
    2. a name is misspelt
    3. the column is narrow
    4. a cell is deleted
    Answer

    A. a number is divided by zero

    Division by zero gives #DIV/0!.

29. Which shortcut selects the entire column in Excel?

    1. Ctrl+Space
    2. Alt+Space
    3. Ctrl+A
    4. Shift+Space
    Answer

    A. Ctrl+Space

    Shift+Space selects the entire row.

30. Which shortcut moves the cursor to cell A1?

    1. Ctrl+PgUp
    2. Ctrl+Home
    3. Home
    4. Ctrl+End
    Answer

    B. Ctrl+Home

    Ctrl+Home goes to A1.

31. Which shortcut puts the AutoSum formula in a cell?

    1. Alt+S
    2. Ctrl+=
    3. Shift+=
    4. Alt+=
    Answer

    D. Alt+=

    Alt+= inserts SUM for adjacent cells.

32. Which feature keeps heading rows visible while scrolling?

    1. Merge and Center
    2. Wrap Text
    3. Freeze Panes
    4. Sort
    Answer

    C. Freeze Panes

    Freeze Panes locks selected rows or columns.

33. Which feature shows only rows that match a condition?

    1. Filter
    2. Format Painter
    3. Sort
    4. Freeze Panes
    Answer

    A. Filter

    Filter hides non-matching rows.

34. Which tool summarises large data by grouping and totals?

    1. Sparkline
    2. Goal Seek
    3. PivotTable
    4. Data Bar only
    Answer

    C. PivotTable

    A PivotTable summarises data in a new layout.

35. A tiny chart that fits inside one cell is a

    1. PivotChart
    2. sparkline
    3. data label
    4. legend
    Answer

    B. sparkline

    Sparklines show trends inside a cell.

36. Which feature changes the look of a cell by a rule, such as red for low marks?

    1. Data validation
    2. Goal Seek
    3. Protect Sheet
    4. Conditional formatting
    Answer

    D. Conditional formatting

    The rule decides the formatting.

37. In the IF function =IF(test, A, B), B is returned when

    1. the test is true
    2. the test is false
    3. there is an error
    4. the cell is blank only
    Answer

    B. the test is false

    The third argument is for a false result.

38. Statements: 1. A workbook may contain many worksheets. 2. A worksheet is the whole Excel file. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    A. 1 only

    The workbook is the whole file.

39. Statements: 1. COUNT counts cells with numbers. 2. COUNTA counts non-empty cells. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    C. Both 1 and 2

    Both are right.

40. Statements: 1. A relative reference changes on copying. 2. An absolute reference uses the $ sign. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    C. Both 1 and 2

    Both describe the reference types correctly.

41. Statements: 1. A pie chart can show many data series at once. 2. A line chart shows trends. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    B. 2 only

    A pie chart shows one series.

42. Statements: 1. Excel text is left-aligned by default. 2. Formulas can start without the = sign. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    A. 1 only

    A formula needs =, + or - at the start.

43. Statements: 1. F2 edits the active cell. 2. F4 edits the active cell. Which is/are correct?

    1. 1 only
    2. 2 only
    3. Both 1 and 2
    4. Neither 1 nor 2
    Answer

    A. 1 only

    F4 repeats the last action or changes reference type.

44. Match the pair: which pair is correct?

    1. Ctrl+Home - last used cell
    2. Shift+Space - select column
    3. Ctrl+Space - select row
    4. Ctrl+End - last used cell
    Answer

    D. Ctrl+End - last used cell

    Ctrl+Home goes to A1; Shift+Space selects a row; Ctrl+Space selects a column.