Advanced Excel Exam 1

If the VLOOKUP function cannot find a match in the lookup table, what does it return?

#N/A error value

Your worksheet contains a price in cell A5, and many formulas refer to that price. How would you refer to that price in the formulas?

$A$5

A workbook template has which of the following file extensions?

.xltx

The score of a student is inserted in cell B2. The passing score for the subject is 60. Which of the following functions should you insert in cell C2 to check whether the student has passed or failed?

=IF(B2>=60, "Pass", "Fail")

Cell A5 contains the number 24.7835. In another cell, Ariane wants to enter the number from cell A5 but rounded to two decimal places. Which of these formulas can she use to do so?

=ROUND(A5,2)

Jason wants to average the sales in the range C3:C50, and then round the result to the nearest integer. Which of the following formulas should he use?

=ROUND(AVERAGE(C3:C50),0)

In the Expenses workbook, Cassie defined the range D10:G10 with the name RentExpenses. Which of the following formulas can replace the formula =SUM(Expenses!D10:G10)?

=SUM(RentExpenses)

Which of the following functions would you use to calculate the arithmetic mean of a price list?

AVERAGE

In the formula =B6*$B$2, which of the following describes $B$2?

Absolute cell reference

Calista wants to provide additional information about a hyperlink she is creating. Which of the following can she do?

Add a ScreenTip.

To use defined names in existing formulas, click the Define Name arrow, and then click _____.

Apply Names

Which of the following lets you extend any data pattern involving dates, times, numbers, or text?

AutoFill Options button

How can you distinguish between a manually added page break and an automatic page break in a worksheet?

Automatic page breaks appear as dashed lines while manual page breaks appear as solid lines.

What can you do to make data series easier to distinguish from one another?

Change the color of the data series.

After beginning a formula, what can you do instead of typing the syntax of a 3-D reference?

Click a sheet tab, click a cell range, and then press ENTER.

When Valerie used AutoFill to copy a formula, Excel copied the format and removed shading from some cells. What can she do to prevent the formatting change?

Click the AutoFill Options button and then click Fill Without Formatting.

Ricki has an Analysis workbook containing many worksheets of sales data that she wants to use in a new workbook. What is the easiest way for her to use the sales data in the new workbook?

Copy the worksheets from the Analysis workbook to the new workbook.

Which type of conditional formatting rule would you create to add a horizontal bar to a cell background, where the length of the bar reflects the value in the cell?

Data Bar

You have selected a cell with a formula. Which of the following can you use to copy that formula to an adjacent cell?

Fill handle

To copy a cell's formatting to another cell, which of the following can you use?

Format Painter

Joe wants to format several worksheets at the same time. What is the easiest way for him to perform this task?

Group the worksheets

A _____ is text or an image you click to open a webpage or file.

Hyperlink

Nathan wants a formula to return "Yes" if the value in cell A1 is less than the value in cell B1, and to return "No" otherwise. Which of the following functions should he use?

IF

Which of the following is a type of logical function?

IF

Which of the following is a simpler way to write the following formula: June!C5+July!C5+Aug!C5?

June:Aug!C5

In column D of a worksheet, Cal lists the number of hours each employee works. What function should he use to find the employee who worked the most hours?

MAX

To combine multiple cells into one combined cell, which of the following do you use?

Merge and Center button

Bree added data labels to a pie chart, where they appear on each slice. She wants the data labels to appear outside of the pie chart but close to each slice. Which Label Position option should she select for the data labels?

Outside End

To which of the following chart types can you not add axis titles?

Pie chart

Eleanor wants to repeat the first row of the worksheet on each printed page of a four-page worksheet. Which of the following options should she use in the Page Setup dialog box?

Print Titles

You can create conditional cell formats using either the Home tab or the _____.

Quick Analysis tool

If you want to change all occurrences of "Radio" to "TV" in a worksheet, you can use the _____ command .

Replace

Which of the following dialog boxes do you use to remove a data series from a chart?

Select Data Source dialog box

Before Barry inserts new formulas in the range A5:F5, he wants to clear the cell contents. How can he do so?

Select the range and then press DELETE.

Kathy wants to add the numbers entered in a range of selected cells. To do this, which tab will she click on the menu that appears after clicking on Totals in the Quick Analysis option?

Sum

Which of the following elements can you add to a chart to display a line representing the general direction in a data series?

Trendline

Arlo wants to use Goal Seek to answer a what-if question in a worksheet. Which button should he click on the Data tab to access the Goal Seek command?

What-If Analysis

Which type of sparklines display a green block for increased values and a red block for declining values?

Win/Loss

When you save a workbook as a template, which of the following file name extensions does Excel apply?

XLTX

What happens when you click the sheet tab of a worksheet not included in a worksheet group?

You ungroup the worksheets

Which of the following is an acceptable name for a range in Excel?

_TotalExpenses

When you insert a defined name into a formula, Excel treats the defined name as a(n) _____.

absolute cell reference

A(n) _____ is descriptive text that appears next to a chart's horizontal or vertical axis.

axis title

To format a range so that all values greater than $500 appear in red, which of the following can you use?

conditional formatting

What does the third argument (3) refer to in the following formula: VLOOKUP(10005, A1:C6, 3, FALSE)?

number of the column containing the return value

You double-click the Format Painter button when you want to _____.

paste the same format multiple times

When you arrange windows in a Vertical or Side by Side layout, you can use synchronized _____ to more easily compare the two worksheets.

scrolling

A(n) _____ chart organizes hierarchies of data in a series of concentric rings and is appropriate for showing groups within categories of information.

sunburst

To create a workbook containing text, formulas, macros, and formatting that you use repeatedly, you create a _____.

template

To paste a data range so that column data appears in rows and row data appears in columns, you can _____ the data using the Paste list arrow.

transpose

A _____ chart displays each category of data in a rectangle and smaller rectangles within them for subcategories.

treemap

A _____ chart tracks the addition and subtraction of values within a sum.

waterfall

Which type of chart would show increases in the value of a stock fund in green, decreases in gray, and the initial and final values in red?

waterfall