Excel Module 2, 3, and 4

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

Which formula can be applied for the given data in a cell A2: 01/01/2017 to get the serial number of the date as text?

=DATEVALUE(''01/01/2017'')

What is the correct formula to insert the date January 12, 1998 into a cell?

=Date(1998,1,12)

Ian needs to extract the day of the month from the date entered as a serial number in cell A1. Which formula can he use to do this?

=Day(A1)

Sheila wants to extract the hour from the time, 12:00:56, in cell A1. Which formula can she use to do this?

=HOUR(A1)

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

AVERAGE

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

Autofill Options button

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.

What will you do to explode a pie chart slice?

Click the pie chart, then double-click the slice you want to explode, and drag it away from the center of the chart.

Which of the following should you select as X in the following series of clicks to change the chart type to a clustered column chart: Chart > Chart Tools > Design tab > Type group > Change Chart Type > All Charts > X > Clustered Column > OK?

Column

Jacquan wants to use a cell style similar to the default Title style, but with a larger font size and different font color. What can he do?

Create a custom cell style.

Which of the following will you select as X in the following series of clicks/entries to insert a chart using the Quick Analysis tool: Range of cells > X > Charts > Select Chart Type you want?

Ctrl + Q

Which of the following keys will you press after clicking on a chart to remove it from an Excel sheet but keep the data intact?

Delete

To change conditional formatting that applies a red fill color to one that applies a green fill color, which of the following can you do?

Edit the conditional formatting rule.

Marianna wants to add a border to a legend. Which of the following should she select as X in the following series of clicks to do so?Legend > (right click) Format legend > Legend Options > X > Border > Solid Line

Fill & Line

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

A _______ refers to a collection of characters with a similar, specific design.

Font

To apply a cell style, you would use the Cell Styles command on the _____ tab.

Home

Which of the following is a type of logical function? (533064)

IF

You've copied a cell containing formula to the rows below it, and the results in the copied cells are all zeros. To find the problem, what should you check for in your original formula?

If it needs an absolute cell reference.

Which of the following Excel tools displays the syntax for a selected function?

Insert function dialog box

Which of the following will you select as X in the following series of clicks/entries to change the number format of data labels in a chart: (Double-click) Data labels > Format Data Labels > X > Number > Select choice in Category Box?

Label options

Which of the following will you select as X in the given series of clicks to add a legend to the bottom of a chart: Chart > + sign > X > Bottom?

Legend

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 have Excel enter the lowest price from a range of prices, which of the following would you use?

MIN function

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

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

Select Data Source dialog box

Betty wants to choose the list to create a custom number format. To do so, what should she choose after the following series of clicks? Home tab < Number group < Number Format list < More Number Formats < Format Cells dialog box < Category < Custom

Type

How can you remove data labels from a chart?

Uncheck the box beside Data Labels in Chart Elements.

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

When you underline cell content, you are using _____.

a font style

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

absolute cell reference

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.

For use as watermark, Alba wants to insert a picture as a worksheet background. For this, she clicks the desired worksheet, then selects option X from the Page Layout tab in the Page Setup group. Alba then selects the picture and then clicks Insert. What can X in her series of selections be?

background

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

change the color of the data series

To help you easily identify sheets in a workbook, you can add _____ to the sheet tab.

color

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

conditional formatting

If you see a cells with blue fills of varying lengths representing the values, the cells most likely have ________ applied to them.

data bars

Bertie wants to show data labels inside a text bubble shape. Which option can she click on after clicking on the arrow next to Data labels under Chart Elements?

data callout

To change the color of cell content, you use the ________ color list arrow.

font

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

format painter

Which of the following conditional formatting options can you use to highlight the top 10 values in a range of cells?

highlight cell rules

Which of the following can you do to make a chart title stand out on a chart?

increase the font size

Which of the following will you select as X in the given series of clicks to insert a 3-D pie chart: Insert > Charts group > X > 3-D Pie?

insert pie or doughnut chart

Which of the following will you select as X in the following series of clicks/entries to change the number format of a chart axis: (Double-click) Value Axis labels > Format Axis > Axis Options > X > Select choice in Category box?

number

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

The Clipboard task pane shows the contents of the _____.

office clipboard

To rotate cell contents to an exact value, you change its _____.

orientation

Igor wants to set 1-inch margins on the left and right and 0.5-inch margins on the top and bottom of a printed page. Which of the following dialog boxes should he use to change the margins?

page setup dialog box

Amari wants to paint the format of one object onto a target object. She selects the first object, then clicks on Format Painter, on the Clipboard group in the Home tab. She then observes the cursor change to a specific icon. Which of the following is the icon into which the cursor changed?

paintbrush

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

Which of the following chart types show how two numeric data series are related to each other?

scatter

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

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

waterfall

Which of the following will you select as X in the following series of clicks to change the chart layout for lines or bars that you want to change: Chart > Chart Tools > Layout tab > X > Layout option for any lines or bars that you want to add or change?

Analysis

To format a cell range so that values between 100 and 500 appear in red, which of the following can you use?

Highlight Cells Rules

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

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)

Annemarie lists 12 months of product sales data in the range A3:M7. The products are listed in the range A3:A7 and the monthly sales data in the range B3:M7. She wants to display a simple chart at the end of each row in column N to track the monthly sales for each product. What can she insert in the range N3:N7?

Line sparklines

Which of the following will you select as X in the following series of clicks to apply a shadow effect: (Double-click) Text box > Format Options > Text Options > X > Shadow?

Text effects

What will you select as X and Y in the following series of clicks/entries to insert a Clustered Column - Line Combination chart: Range of cells > Insert tab > Charts group > Insert Combo Chart symbol > Create Custom Combo Chart > Insert Chart dialog box > All Charts > Series 1 > X (Chart type) > Series 2 > Y (Chart type) > OK?

X - Clustered Column, Y - Line

Olivia wants to align a certain shape on her worksheet in a specific manner. To do so, she clicks on the shape, and then clicks on the Format tab under Drawing Tools. She then clicks on Align in the Arrange group. What should Olivia click on next to align the picture to the left?

align left

While working in Page Layout view, how can you enter your name so it appears in the lower-right corner of the printout?

click the footer section on the right and type your name.