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
22.1 About MS Excel
Microsoft Excel is a spreadsheet program for calculation, data analysis and charts.
- A spreadsheet is a grid of rows and columns used to organise numbers and do calculations.
- The first electronic spreadsheet was VisiCalc (1979) by Dan Bricklin and Bob Frankston. Lotus 1-2-3 (1983) dominated the 1980s.
- Excel was first released in 1985 (for Mac) and 1987 for Windows.
- Other spreadsheets: Google Sheets, LibreOffice Calc, Apple Numbers, WPS Spreadsheets, Corel Quattro Pro.
- Excel for the web is a free, lightweight, browser-based version of Excel, part of Office on the web.
- Lotus 1-2-3 was a popular DOS-based spreadsheet; its worksheet files used the .wk1 format.
22.2 Basic Terms
| 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) |
- Inserting a row pushes the existing rows down; inserting a column pushes the existing columns to the right. Existing data moves, it is not overwritten.
- The formula bar sits above the sheet, right of the Name Box. Its three buttons are ✕ Cancel (discards what you are typing), ✓ Enter (accepts it) and fx (Insert Function).
- The horizontal scroll bar runs along the bottom of the window and the vertical scroll bar along the right edge. The status bar at the very bottom shows the sum, count and average of the selected cells.
- Navigation buttons at the lower left of the sheet tabs move to the first, previous, next or last sheet.
- Sheets: Ctrl + click selects sheets that are not next to each other; Shift + click selects a continuous run of sheets. To rename a sheet, double-click its tab or right-click it and choose Rename.
- Cells: hold Ctrl and click each cell or range to select cells that are not next to each other. A hidden row or column shows as a double line between the headings, not a single line.
- Auto-fit: double-click the boundary between two column headings (or row headings) to fit the width (or height) to the content. A single click does nothing.
- Name Boxshows active cell, e.g. B5
- Formula barfx =SUM(B2:B4)
- Column headingsA, B, C …
- Row headings1, 2, 3 …
- Active cellthick border
- Fill handlesmall square at cell corner
- Sheet tabsSheet1, +
- Status barAverage · Count · Sum
- Ribbon tabsFile, Home, Insert, Page Layout, Formulas, Data, Review, View