Microsoft Intermediate Excel Certification

In Goal Seek, which field represents the element of the formula that is a mystery?

By changing cell.

Solver is located on which tab of the Ribbon?

Data.

Which one of the following is an appropriate name for a macro?

Delete_2_Lines.

Which tab within the PivotTable Options dialog box contains the option to switch the Excel report to classic layout mode?

Display.

What must you do in order to see the supporting detail that makes up a number in your PivotTable report?

Double-click any value.

In Excel, where are the various function categories stored on the Ribbon?

Function Library

Which term describes a predefined formula in Excel?

Function.

Which tool should you use if you know the correct answer but you don't know the input value that you need to get there?

Goal Seek.

What symbol would you use to create an absolute cell reference?

$.

How many arguments (input fields) does the COUNTIF function require?

2.

What is the maximum number of text items that you can join into a single text item using the Concatenate function?

255.

What is the purpose of Sparklines?

Adds tiny charts that help illustrate trends in data.

Which feature allows you to ask Excel for a list of entries with the 10 largest or 10 smallest numbers?

AutoFilter.

Say you want to select two rows that aren't adjacent to one another. To avoid selecting all the rows in between, what key on your keyboard must you hold down as you click the two rows?

CTRL.

Which of the following is the preferred method for removing Sparklines?

Clear command.

What are the restrictions placed on Solver referred to?

Constraints.

Which Excel Ribbon command, located on the Formula tab, has a larger 'f' next to a little 'x' on it?

Insert function.

Which type of chart is typically used to offer the most likely explanation of how a series of measurements, taken over equal intervals of time, could have come into being?

Line.

What function searches for a specific value in a table and returns that value's relative position from the table, not the value itself?

MATCH.

What term is used to describe joining two or more functions into a "super" function?

Nesting.

Which right-click option of a PivotTable allows you to update the number formats of all the values at once?

Number Format.

What's the name of the custom toolbar where you can attach your own macro buttons?

Quick Access Toolbar.

What must you do in the PivotTable report to bring it up to date after you change the source information?

Refresh data.

Which option on the Chart Tools Format tab is your saving grace if you want a custom-formatted chart element to revert to a style that matches the other elements in your chart?

Reset to Match Style.

Which is a type of calculation that you can use to summarize data for a particular PivotTable field?

SUM.

Which of the following functions uses multiple criteria fields to find the answer?

SUMIFS.

What combination of actions makes creating a chart a simple task?

Select the numbers and labels you want to use in your chart, click the Quick Analysis option, and choose one of several recommended chart types.

Which vastly more powerful what-if analysis tool is similar in purpose to Goal Seek?

Solver.

What Sparkline formatting option creates a reference line?

Sparkline horizontal axis.

If a newly created chart appears to have the data on the vertical and horizontal axes switched, what charting feature can fix this mix-up?

Switch Row/Column.

What does Excel call a worksheet that contains contiguous rows and columns of data where the first row of each column serves as a label?

Table.

What do you use the range argument for in the SUMIF function?

To define the range of cells that the criteria will evaluate.

Which chart type is useful to show a hierarchal view of your data across different categories?

Treemap.

What step must you take in order to edit a macro stored in the Personal macro workbook?

Unhide the Personal.xlsb file.

Which chart type is useful when you want to show a running total as values are added and subtracted from a total on a financial statement?

Waterfall.

In AutoFilter, what are the '*' and '?' used for?

Wildcards.