Excel for Experts Trivia Quiz

Answer: =MAX(B2:B6)

The first argument in the SUBTOTAL function refers to another function. In this case 4 is for MAX. 1 is for AVERAGE. 5 is for MIN. 9 is for SUM. By linking a cell to the first argument, you could change the number in that cell and get SUBTOTAL to return different kinds of calculations. You could even use a dropdown list to pick a number from.

Answer: Display what is contained in cell E4

Starting at a reference point, OFFSET picks a cell by going down and across a specified amount. In the example, A1 is our starting point. It then moved down 3 rows and across 4 columns, bringing the cell reference to E4. To use this more productively, the second and third arguments can be linked to a cells.

There are two additional arguments for height and width, allowing you to select a range of cells. OFFSET is very useful for working with dynamic ranges for formulas and even charts.

Answer: Tell you the date of the last day of next month

The EOMONTH function is the End Of Month function. This can be useful in accounting spreadsheets for finding the last date of the current month or another month. It can also be used to do things like find out how many days are left in a month by finding out what the last date of the month is and subtracting today's date from it.

Answer: MATCH

VLOOKUP is a useful function, but has some limitations. Using INDEX and MATCH provides a more flexible way of searching for data in tables of data. Many Microsoft Excel users use INDEX and MATCH because of this.

Answer: A cell referred to by a formula

By using "Trace Precedents" you can have Microsoft Excel draw arrows from the cells referenced by the current cell. Using "Ctrl" and "[" you can select all of those cells.

Answer: Multiplan

Visicalc was the first widely successful spreadsheet program, launched in 1979. In order to compete with it, Microsoft launched Multiplan, with mixed success. It was eventually replaced by Microsoft Excel in 1985.

Answer: Advanced Filter

Advanced Filter allows you to extract data to a separate area or a separate sheet. It is usually done by adding criteria to correspond with headings in a table.

A data table allows you to set up formulas that use values from the top row and first column to get a table of results. You put a formula in the top left cell of the table, and then values along the resto of that row and down that column. If that formula adds values, you could set up a table that adds the first value in the first row to each value in the first column, giving you a list of totals. That process can then be repeated for each row and column very quickly.

Goal Seek allows you to automatically find a value that will make a formula give a particular result. This is good for trying to determine an unknown value. For example, finding what interest rate would be needed to earn a particular total of interest, when you know the amount you are investing, but not the rate that would be needed.

Scenario Manager is a complex and powerful tool, allowing you see how various combinations of data will effect a result. For a business you would need to analyse multiple things such as cost, supply, amount of sales etc. So you could analyse things like if the costs go up and the time to receive goods takes longer, how much do you have to order and sell in order to make a profit. It also allows you to save sets of combinations of data, so that you compare things. So if you have different shops you can compare how they do against each other, by swapping in a set of data for each shop.

Answer: Insert the current date

Using Ctrl and the colon/semi-colon key is a quick way of inserting the current date. If you also press the Shift key, it will insert the current time instead. Both of these are very useful features.

Answer: You have manual calculation switched on

Usually when you make any change of data in a spreadsheet, all the formulas automatically recalculate to reflect changes that your new data may have made. There are times where you want to change data, but not have results of formulas immediately recalculate, like when you want to observe what differences happen when you make a change. Changing to manual calculation allows you to make changes to values without the formulas recalculating.

When manual calculation is on, pressing the F9 key will recalculate all formulas.

Answer: Select the text box and on the formula bar type the equals sign and a cell reference with your formula

While many people think this can't be done, it is possible to put the result of a formula into a text box. You can't have the formula directly in the text box, as this will show the formula as text, but you can link the text box to a formula. So put the formula in a cell in your sheet and click on the text box and in the Formula Bar reference the cell the formula is in.

This can be useful for dynamic textboxes or labels that show results of formulas.