MS Excel: Formulas, Functions and Shortcuts

The correct answer is D, Shift + Space. Pressing Shift together with the spacebar selects the whole row that holds the active cell, from column A right across to the last column of the grid. The row number on the left turns highlighted, and any formatting, deletion or height change you apply next affects the entire row.

Option A, Ctrl + Shift + Space, selects the whole worksheet, which is the same thing Ctrl + A does when the active cell stands outside a data range. Option B, Ctrl + Home, does not select anything at all; it only moves the cursor back to cell A1 from wherever it happens to be. Option C, Ctrl + Space, is the companion of the right answer and selects the whole column instead of the whole row, which is exactly the swap the paper setter is hoping for. Remember the pair together: Shift for the row, Ctrl for the column.

The correct answer is A, Title bar. The title bar is the strip along the very top of the Excel window, and it carries the name of the open workbook followed by the name of the program, for example Book1 - Excel. The minimise, maximise and close buttons sit at its right end, and the Quick Access Toolbar at its left.

Option B, the tool bar, holds command buttons rather than names; in Excel 2007 and later it survives only as the small Quick Access Toolbar. Option C, the task bar, belongs to the Windows operating system and not to Excel; it runs along the bottom of the screen and shows the Start button and the programs that are open. Option D, the menu bar, was the row of word menus of Excel 2003 and earlier, replaced by the ribbon from Excel 2007; it listed commands such as File and Edit, never the file name. The cell address, by contrast, is shown in the Name Box beside the formula bar.

The correct answer is C, Text. LEN returns the number of characters in an entry, counting letters, digits, punctuation and spaces alike, so =LEN("Excel") gives 5. Because it works on text rather than on values, Excel files it under the Text category of the Formulas tab, along with LEFT, RIGHT, MID, UPPER, LOWER, TRIM and CONCATENATE.

Option A, Financial, holds the money functions such as PMT, RATE, NPV and FV, which work out instalments, interest rates and present values. Option B, Logical, holds IF, AND, OR, NOT and IFERROR, the functions that test a condition and return one result or another. Option D, Math and Trig, holds SUM, ROUND, ABS, SQRT, POWER and MOD, which calculate on numbers. LEN does return a number, and that is the trap that pulls candidates towards D, but a function is classified by what it works on, and LEN works on text.

The correct answer is C, Microsoft Excel. An electronic spreadsheet stores data in a grid of rows and columns and recalculates its formulas whenever a value changes, and Excel is the spreadsheet program of the Microsoft Office suite. Its files carry the .xlsx extension from Excel 2007 onwards.

Option A, Microsoft Word, is a word processor, used to type, edit and format text documents that are saved with the .docx extension. Option B, Microsoft PowerPoint, is presentation software, used to build slides for a talk and saved with the .pptx extension. Option D, Microsoft Access, is the database management program of the suite; it also shows tables in a grid, which makes it the strongest distractor, but a database stores records in related tables and is queried, while a spreadsheet calculates cell by cell. Remember the pairing of the four programs with their jobs and this whole family of questions becomes easy.

The correct answer is D, Ctrl + F2. Ctrl together with F2 opens the print area of the Backstage view in Excel, Word and PowerPoint, where the page is shown exactly as it will be printed and the printer, copies, orientation and margins can be set. In Office 2003 and earlier the same combination opened the older Print Preview window.

Option A, Alt + Ctrl + F2, opens a file rather than previewing one, working like Ctrl + O. Option B, Alt + F2, was the Save As shortcut of very old Office versions and does nothing useful today; the modern Save As key is F12. Option C, Shift + F2, adds or edits a comment on the active cell in Excel, which is a completely different job. Learning the F2 family together helps: F2 alone edits the active cell, Shift + F2 handles a comment, Ctrl + F2 previews the print, and Alt + equals inserts a SUM.

The correct answer is B, .xlsx. From Excel 2007 onwards the suite moved to the Office Open XML formats, and a plain workbook saves with the .xlsx extension. The x at the end stands for XML, the format in which the sheet contents are stored inside a compressed package, which is why these files are smaller and less prone to corruption than the older ones.

Option A, .xls, was the default extension of Excel 97 through Excel 2003; a 2016 workbook takes it only if the user deliberately saves in the Excel 97-2003 format. Option C, .xlsm, is the macro enabled workbook, used when the file has to carry VBA code, because a plain .xlsx refuses to store macros. Option D, .xltx, is an Excel template, a pattern file from which new workbooks are created rather than a workbook itself; the macro enabled template is .xltm. The exam expects .xlsx whenever the question names Excel 2007 or any later version.

The correct answer is C, 1,048,576. From Excel 2007 the grid of one worksheet was enlarged to 1,048,576 rows and 16,384 columns, so the very last cell of the sheet is XFD1048576. The figure is a power of two, two raised to the twentieth, which is why it looks so odd at first sight and is easy to recall once noticed.

Option A, 65,536, is the number of rows in Excel 2003 and earlier, and it is the answer only when the question names one of those old versions. Option B, 16,384, is the number of columns in Excel 2007 and later, not the number of rows, and swapping the two is the commonest mistake in this question. Option D, 2,097,152, is simply double the correct figure and has no place in Excel at all. Fix the pair in memory as rows just over ten lakh and columns just over sixteen thousand.

The correct answer is B, the equals sign. Excel treats whatever follows an equals sign as something to be calculated, so =A1+B1 adds the two cells and =SUM(A1:A10) totals a range. Without that sign the same text is stored as plain text and no calculation happens, which is why a formula that shows itself in the cell instead of a result has usually lost its equals sign.

Option A, the hash sign, begins an error value such as #DIV/0! or #NAME? and is written by Excel, never by the user. Option C, the asterisk, is the multiplication operator used inside a formula, as in =A1*B1, and a formula may also be started with a plus or a minus sign in Excel for the convenience of old Lotus users, but never with an asterisk. Option D, the colon, is the range operator that joins the first and last cells of a block, as in A1:A10. Each of these three has a real job in Excel, just not the job of starting a formula.

The correct answer is C, absolute reference. A dollar sign locks the part of the address that follows it, so $A$1 has both the column and the row locked and keeps pointing at cell A1 however far the formula is copied. It is used when one fixed cell, such as a rate of interest or a conversion factor, must be applied to a whole column of values.

Option A, a relative reference, is written without dollar signs, as A1, and shifts with the copy: dragged one row down it becomes A2. Option B, a mixed reference, carries a single dollar sign, either $A1, which locks only the column, or A$1, which locks only the row. Option D, a circular reference, is not a form of address at all but an error in which a formula refers back to its own cell, directly or through a chain, and Excel warns about it instead of producing an answer. The F4 key cycles a reference being edited through the relative, absolute and two mixed forms.

The correct answer is B, a number is divided by zero or by an empty cell. Division by zero has no answer in arithmetic, so Excel refuses to invent one and writes #DIV/0! instead. An empty cell used as the divisor counts as zero, which is why the error often appears in a template before any data has been typed; wrapping the formula in IFERROR or in an IF test hides it neatly.

Option A describes #NAME?, the error Excel writes when it cannot recognise a function name or a defined range name, for example if SUM is typed as SUMM. Option C describes the row of hash marks that fills a cell whose column is too narrow; that is only a display problem and not an error value, and widening the column restores the number. Option D describes #REF!, which appears when the row, column or sheet a formula pointed at has been deleted, so the reference no longer exists. Each error value names its own cause, and learning the four together is the whole of this topic.

The correct answer is C, COUNT. COUNT walks through the range and counts only the cells that hold a numeric value, which includes dates and times because Excel stores them as serial numbers. Text entries, logical values and empty cells are all ignored, so =COUNT(A1:A10) on a column of names returns zero.

Option A, COUNTA, counts every cell that is not empty, so text and numbers alike are included; the A stands for all, and this is the distractor the question is built around. Option B, COUNTBLANK, does the opposite and reports how many cells in the range are empty. Option D, SUM, does not count cells at all but adds the numbers it finds in them, so a column holding 5 and 5 gives a COUNT of 2 and a SUM of 10. COUNTIF, a fifth member of the family, counts only the cells that satisfy a stated condition.

The correct answer is B, XFD. Columns are lettered A to Z, then AA to AZ, BA to BZ and so on, and the 16,384th column of the enlarged grid falls at XFD. With 1,048,576 rows in the sheet, the last cell of a worksheet is therefore XFD1048576, and pressing Ctrl together with the right arrow key from an empty row takes the cursor straight to that column.

Option A, IV, was the last column of Excel 2003 and earlier, when a sheet had only 256 columns, and it is correct only for those versions. Option C, ZZZ, would be a valid looking three letter name but lies far beyond the 16,384 column limit, so Excel never reaches it. Option D, XL, is the abbreviation Microsoft uses in the file extensions .xlsx and .xlsm, borrowed from the Roman numeral for forty, and it is an ordinary column name early in the double letter run, nowhere near the end of the grid.