Ez Computerहिन्दीOpen app

Microsoft Excel

Preview Part F: Office and Productivity · Chapter 22 · 46 min read · 166 practice questions

After this chapter you will be able to

  • identify the parts of the Excel window and its limits
  • enter data, formulas and functions correctly
  • use relative, absolute and mixed references
  • recognise Excel error values and fix them
  • use the most-asked functions (SUM, COUNT family, IF, VLOOKUP, XLOOKUP…)
  • use charts, sorting, filtering, conditional formatting, pivot tables and what-if tools
Microsoft Excel Basics and limits Rows: 1,048,576 Last column: XFD Zoom: 10% to 400% Formulas Starts with = ^ before × ÷ F4 changes $ style Errors #DIV/0!: divide by 0 #REF!: deleted cell #N/A: no match Functions SUM, AVERAGE, COUNT IF, VLOOKUP XLOOKUP (new Excel) Data tools Filter: Ctrl+Shift+L Freeze Panes: View Goal Seek: Data tab Charts Pie: one series only Line: trend over time Sparkline: mini chart
Chapter 22 at a glance Recall Excel limits, formula rules, error values and the most-used tools.

22.1 About MS Excel

Microsoft Excel is a spreadsheet program for calculation, data analysis and charts.

22.2 Basic Terms

Book1 - ExcelSearchFileHomeInsertPage LayoutFormulasDataReviewViewBIUNumberΣSortB5fx=SUM(B2:B4)ABCDEFG12345678ItemQtyRateAmountPens1210120Files715105Pads925225Total28Sheet1Sheet2+Average: 9.3 Count: 3 Sum: 28123456871Name Box2Formula bar3Column letters4Row numbers5Active cell6Cell range (B2:B4)7Fill handle8Sheet tabs
Excel window The Name Box, formula bar, column letters and row numbers, the active cell with its fill handle, a cell range, and the sheet tabs.
Term Meaning
Workbook An Excel file (Book1.xlsx) containing worksheets
Worksheet (sheet) One grid of rows and columns (Sheet1)
Row Horizontal line of cells, numbered 1, 2, 3…
Column Vertical line of cells, lettered A, B, C … Z, AA … XFD
Cell Intersection of a row and a column
Cell address / reference Column letter + row number, e.g., B5
Active cell The selected cell (thick border); its address shows in the Name Box
Active worksheet The sheet you are working on; its tab stays highlighted at the bottom
Range A group of cells, e.g., A1:C5 (15 cells)
Anchor cell The first highlighted cell of a range, where the selection began; typing goes to the active cell
Name Box Shows the active cell address or a named range
Formula bar (fx) Shows/edits the content or formula of the active cell
Fill handle Small square at the bottom-right of a selection — drag to copy or continue a series
Sheet tabs Names of sheets at the bottom
Chart sheet A sheet that holds only a chart
Merged cell Two or more cells combined into one larger cell (Merge & Centre)
Argument Value that a function works on, written inside brackets, e.g., A1:A5 in =SUM(A1:A5)
FigureExcel Window
  1. Name Boxshows active cell, e.g. B5
  2. Formula barfx =SUM(B2:B4)
  3. Column headingsA, B, C …
  4. Row headings1, 2, 3 …
  5. Active cellthick border
  6. Fill handlesmall square at cell corner
  7. Sheet tabsSheet1, +
  8. Status barAverage · Count · Sum
  9. Ribbon tabsFile, Home, Insert, Page Layout, Formulas, Data, Review, View

What the full chapter covers

The full chapter has these sections:

  1. 22.1 About MS Excel
  2. 22.2 Basic Terms
  3. 22.3 Excel Limits and Defaults
  4. 22.4 Types of Data
  5. 22.5 Formulas and Operators
  6. 22.6 Cell References
  7. 22.7 Error Values
  8. 22.8 Most Important Functions
  9. 22.9 Ribbon Tabs
  10. 22.10 Data Tools
  11. 22.11 Charts
  12. 22.12 Most-Asked Excel Shortcuts
  13. 22.13 File Formats
  14. 22.14 Rounding Functions
  15. 22.15 Chart Parts and Pivot Terms
  16. 22.16 Worked Formula Examples
  17. 22.17 More Exam Facts
  18. Chapter Quick Revision
  19. Most Important Terms
  20. Common Confusions
  21. Rapid Recall

Sample questions from this chapter

1. Every formula starts with?

  1. = sign
  2. # (hash) sign
  3. ' (apostrophe) sign
  4. % sign
Show answer

Answer: A. = sign

Every Excel formula begins with an equal sign, e.g. =A1+B1.

2. Address of column J, row 7?

  1. 7J
  2. J17
  3. J7
  4. K7
Show answer

Answer: C. J7

Column letter comes first, then the row number: column J, row 7 is J7.

3. Result of =4^2^3?

  1. 16
  2. 4096
  3. 64
  4. 65,536
Show answer

Answer: B. 4096

The power operator also works left to right: (4^2)^3 = 16^3 = 4096.

4. Which is not an Excel function: SUMMINUS, SUMIF, SUMPRODUCT?

  1. SUMIFS
  2. SUMSQ
  3. SUBTOTAL
  4. SUMMINUS
Show answer

Answer: D. SUMMINUS

SUMMINUS is not an Excel function; the real ones are SUM, SUMIF and SUMPRODUCT.

5. Function that swaps rows and columns?

  1. MATCH
  2. COLUMNS
  3. TRANSPOSE
  4. ROWS
Show answer

Answer: C. TRANSPOSE

TRANSPOSE swaps the rows and columns of a range.