MS Excel: Formulas, Functions and Shortcuts

MS Excel is the spreadsheet program of the Microsoft Office suite. A spreadsheet is a grid of rows and columns in which numbers, text and formulas are stored, so that every calculation updates by itself the moment the data behind it changes. Excel was released by Microsoft in 1985, and in Computer Awareness papers it is the second most asked application after MS Word. The questions come from a narrow set: the size of a worksheet, how a cell is addressed, which symbol starts a formula, what a named function returns, what an error value means, and which shortcut key does what. Learn those six groups and the section is scoring.

Workbook, worksheet and cell

An Excel file is called a workbook, and a workbook holds one or more worksheets. A worksheet is the single grid, and its tab sits at the bottom of the window; the first sheet of a new workbook is named Sheet1. Columns are named with letters and rows with numbers, so the meeting point of a column and a row is a cell, addressed by its column letter followed by its row number: A1, C5, AB120. The cell you are typing in is the active cell, and its address is shown in the Name Box at the left end of the formula bar. The title bar at the very top of the window carries both the name of the workbook and the name of the application.

From Excel 2007 onwards a single worksheet has 16,384 columns, named A to XFD, and 1,048,576 rows, so the last cell of the grid is XFD1048576. In Excel 2003 and earlier the grid was much smaller: 256 columns, A to IV, and 65,536 rows. Both pairs of numbers are asked directly, so both are worth memorising.

Formulas, operators and cell references

Every formula in Excel begins with an equals sign. Typing =A1+B1 in cell C1 adds the two cells and shows the answer in C1, while the formula itself stays visible in the formula bar. A function is a ready made formula that carries a name, for example =SUM(A1:A10). The colon is the range operator, so A1:A10 means every cell from A1 down to A10; the comma is the union operator and separates ranges or arguments. The arithmetic operators are plus, minus, asterisk for multiplication and slash for division, the caret for a power, the ampersand for joining text, and the comparison operators equal to, greater than, less than and not equal to. Excel works through a formula in a fixed order: brackets first, then powers, then multiplication and division, and addition and subtraction last.

A reference can be relative, absolute or mixed, and the difference decides what happens when the formula is copied to another cell. A1 is relative and shifts along with the copy. $A$1 is absolute: the dollar signs lock the column and the row, so the reference stays pinned to that one cell however far the formula is copied. $A1 and A$1 are mixed references, locking only the column or only the row. The F4 key cycles a reference through these four forms while a formula is being edited, which is why F4 is asked so often.

The functions asked most

The Formulas tab groups functions by category, and which category a named function belongs to is a favourite one line question. LEN, LEFT, RIGHT, MID, UPPER, LOWER and CONCATENATE are Text functions. IF, AND, OR and NOT are Logical. SUM, ROUND, SQRT and ABS are Math and Trig. VLOOKUP and HLOOKUP are Lookup and Reference. TODAY and NOW are Date and Time, and PMT, RATE and NPV are Financial.

The error values

When Excel cannot produce an answer it writes an error value in the cell, and each one names its own cause. #DIV/0! means a division by zero or by an empty cell. #NAME? means a function or a range name has been misspelt. #VALUE! means an argument of the wrong type, such as text where a number was needed. #REF! means the formula points at a cell that has been deleted. #NUM! means an impossible number, such as the square root of a negative value. #N/A means a lookup found nothing. A row of hash marks filling the cell is not an error at all: the column is simply too narrow to display the number, and widening it restores the value.

Charts, sorting and the view

A chart turns numbers into a picture and is inserted from the Insert tab. Column, bar, line, pie, area, scatter and radar are the standard types. A pie chart shows each value as a share of the whole and can plot only one data series, while a line chart is used for a trend over time. A sparkline is a tiny chart drawn inside one cell. Sorting rearranges whole rows by the values of a chosen column, filtering hides the rows that do not match a condition without deleting them, and Freeze Panes keeps the heading row on screen while the rest of the sheet scrolls.

Shortcut keys worth memorising