Pariksha SaathiLesson 7 of 10 · Computer Knowledge
MS Excel: Cell References, Formulas, Functions, Charts and Error Values
Drafted with AI assistance and fact-checked on 28 October 2026; not yet read line by line by a human editor. Spotted a mistake? Tell us. Editorial standards & corrections
Spreadsheets and Excel
A spreadsheet arranges data in rows and columns and recalculates results automatically whenever the data changes. VisiCalc (1979) was the first spreadsheet for personal computers and Lotus 1-2-3 followed in 1983; Microsoft Excel was released in 1985. An Excel file is a workbook, saved as .xlsx since Excel 2007 (.xls earlier, .xlsm with macros, .xltx as a template), and a workbook contains worksheets shown as tabs at the bottom. Excel 2010 and earlier versions opened a new workbook with three sheets; later versions open it with one.
Rows, columns and cells
Since Excel 2007 a worksheet has 1,048,576 rows numbered from 1 and 16,384 columns lettered from A to XFD; the older .xls format had 65,536 rows and 256 columns (A to IV). A cell is where a row and a column meet and is named by its column letter and row number, such as C5. The selected cell is the active cell; the Name Box shows its address and the Formula Bar shows its contents. A range of cells is written with a colon, as in A1:D10. By default text is aligned to the left of a cell and numbers to the right.
Formulas and references
A formula always begins with an equals sign, as in =A1+B1. The arithmetic operators are + - * / and ^ for power, worked out in the order brackets, power, multiplication and division, then addition and subtraction; & joins text and <> means not equal to. A relative reference (A1) changes when the formula is copied: =A1*B1 copied one row down becomes =A2*B2. An absolute reference ($A$1) stays fixed, which is useful for a constant such as a tax rate kept in one cell. A mixed reference fixes only the column ($A1) or only the row (A$1). Pressing F4 while editing a formula cycles a reference through these four forms.
Common functions
SUM adds a range, AVERAGE gives the mean, and MAX and MIN give the largest and smallest values. COUNT counts cells that contain numbers, COUNTA counts non-empty cells, COUNTBLANK counts empty cells, and COUNTIF and SUMIF count or add only the cells that meet a condition. IF tests a condition and returns one value if it is true and another if it is false, as in =IF(B2>=40,"Pass","Fail"). AND, OR and NOT combine conditions. VLOOKUP(lookup value, table, column number, FALSE) searches the first column of a table from top to bottom and returns the value from the given column of the matching row; FALSE asks for an exact match, and HLOOKUP does the same across the first row. Others often asked are ROUND, ABS, SQRT, MOD, LEN, UPPER, LOWER, CONCATENATE, TODAY (the current date) and NOW (the date and time). AutoSum (Alt+=) inserts SUM at once.
Charts, sorting and filtering
Charts are on the Insert tab. A column or bar chart compares values, a line chart shows a trend over time, a pie chart shows the parts of one whole (a single data series), and a scatter chart shows the relation between two sets of numbers. F11 creates a chart on a separate chart sheet and Alt+F1 an embedded chart on the same sheet. Sort (Data tab) arranges rows in ascending or descending order, and Filter (Ctrl+Shift+L) shows only the rows that meet a condition while hiding the rest, without deleting them. Freeze Panes (View tab) keeps headings visible while scrolling, Conditional Formatting (Home tab) colours cells by rules, and a PivotTable (Insert tab) summarises a large table. Goal Seek, under What-If Analysis on the Data tab, finds the input needed to reach a desired result.
Error values
#DIV/0! means division by zero. #N/A means a value is not available, as when VLOOKUP finds no match. #NAME? means Excel does not recognise text in the formula, such as a misspelt function name. #REF! means an invalid cell reference, often after cells have been deleted. #VALUE! means the wrong type of argument, such as adding text to a number. #NUM! means an invalid number and #NULL! a wrong range operator. A cell filled with ##### is not an error: the column is simply too narrow to show the number.
Shortcuts
F2 edits the active cell, Alt+Enter starts a new line inside a cell, Ctrl+; enters today's date and Ctrl+Shift+; the current time. Ctrl+Home goes to cell A1, Ctrl with an arrow key jumps to the edge of the data, and Ctrl+Page Down and Ctrl+Page Up move between sheets, while Shift+F11 inserts a new worksheet. Ctrl+1 opens Format Cells, Ctrl+D fills down and Ctrl+R fills right. Ctrl+Space selects a whole column, Shift+Space a whole row, and Ctrl+9 hides the selected rows.
Key facts
- Excel 2007 onward: 1,048,576 rows and 16,384 columns (A to XFD); the older .xls format: 65,536 rows and 256 columns (A to IV).
- Relative A1 changes when copied; absolute $A$1 stays fixed; mixed $A1 or A$1 fixes one part; F4 cycles through them.
- Every formula starts with =; ^ is the power operator, & joins text and <> means not equal to.
- COUNT counts numeric cells, COUNTA counts non-empty cells, COUNTBLANK counts empty cells and COUNTIF counts cells that meet a condition.
- =IF(B2>=40,"Pass","Fail") returns Pass when B2 is 40 or more, otherwise Fail.
- VLOOKUP searches the first column of a table vertically and returns a value from a chosen column; HLOOKUP searches the first row horizontally; #N/A means no match was found.
- #DIV/0! division by zero; #NAME? unrecognised name; #REF! invalid reference; #VALUE! wrong data type; ##### column too narrow.
- A pie chart shows the parts of one whole; a line chart shows a trend; F11 makes a chart on a new sheet and Alt+F1 an embedded chart.
- Filter (Ctrl+Shift+L) hides the rows that do not match but does not delete them; Freeze Panes keeps headings in view.
- F2 edit cell; Alt+Enter new line in a cell; Ctrl+; today's date; Alt+= AutoSum; Shift+F11 new worksheet.
Quick revision
Excel (1985): workbook .xlsx with worksheets; 1,048,576 rows and 16,384 columns (A to XFD); cell address C5, range A1:D10; formulas start with =; relative A1, absolute $A$1, mixed $A1 or A$1, F4 toggles; SUM, AVERAGE, COUNT, COUNTA, IF, VLOOKUP, HLOOKUP, COUNTIF; pie chart = parts of a whole; F11 chart sheet; Filter hides rows; errors #DIV/0!, #N/A, #NAME?, #REF!, #VALUE!; ##### = column too narrow.